Edit Dolt on the Web Using SQL
DoltHub is a place on the internet to share, discover, and collaborate on Dolt databases. Last week we released a new feature on our edit on the web roadmap: edit data using the SQL Console.
About a year ago when we released SQL queries on the
web we supported read-only
show statements. We recently extended that functionality to include statements that
will mutate data, such as
delete, and DDL statements. We created
"workspaces" to support this, which are a temporary staging area on the web for you to
make changes to a repository before merging them back into your branch.
How it works
ended a few weeks ago. Some participants filed
for inconsistencies they found while working on it. We advised them to wait until the
bounty was over to resolve the issues so they didn't get credit for other participants'
contributions. Now that the bounty is over, we can use the SQL Console to close some of
these issues from the web!
As an example, I'm going to work on fixing this issue to correct some inconsistent office names. All the office names should have the value "US PRESIDENT".
Making these changes using Dolt requires a few steps (assuming you've installed Dolt):
# Clone the repository $ dolt clone dolthub/us-president-precinct-results # Checkout a new feature branch $ dolt checkout -b taylor/update-office # Run a query to update the appropriate fields $ dolt sql -q "UPDATE vote_tallies SET office='US PRESIDENT' WHERE office != 'US PRESIDENT'" # Add and commit changes $ dolt add vote_tallies $ dolt commit -m "Update vote_tallies office fields to 'US PRESIDENT' per issue #81" # Push feature branch $ dolt push origin taylor/update-office
This can now be done directly from DoltHub. To show off some features of workspaces for
this example, I'm going to update each inconsistent
office value with its own query
instead of the one above.
First, I'm going to use the SQL Console to run a query that will change any "PRESIDENT"
value in the
office column in the
vote_tallies to "US PRESIDENT":
Running a query from the SQL Console that mutates data will automatically create a workspace. A workspace is a way to stage and view changes on DoltHub before creating a new branch or merging your changes. It looks like this:
You can see that 9,784 rows were changed by this query. To inspect further, you can also look at both the cumulative diff for all queries in the workspace and the current diff for the most recent changes.
I can run more queries within the workspace to update the rest of the cells in the
office column to "US PRESIDENT":
And now our cumulative diff includes changes from all queries (modifying 15,870 rows total):
You can also view the current diff of the last query (converting "US PRESIDENTIAL" to "US PRESIDENT"):
Once I've reviewed my changes I can run the same select query from the issue again within the workspace to make sure everything looks right:
As you can see, all
office fields are "US PRESIDENT" now. If I decided I didn't want to
save these changes, I could click on the trash button to discard the workspace and none of
these changes would be applied to this repository. However, I'm satisfied with my changes
so I want to create a pull request for the repository owner to review:
You can view the resulting pull request here. Once reviewed and merged, we can close issue #81, all from work done on the web.
Editing data from the SQL Console lowers the barrier to entry for those who may not be as familiar with the command line and makes it easier to make quick changes using SQL to databases on DoltHub.
Our next big "edit on the web" feature will be editing data on DoltHub like a spreadsheet, so stay tuned! If you want to stay up to date or discuss any upcoming or desired DoltHub features, come talk to us in our Discord.