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 select, describe, and show statements. We recently extended that functionality to include statements that will mutate data, such as update, insert, 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

Our us-president-precinct-results bounty ended a few weeks ago. Some participants filed issues 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.

Conclusion

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.