Dolt Rollback Options
Dolt is like Git and MySQL had a baby. Dolt applies all the Git functionality you know to a SQL database. As such, Dolt provides many different ways to rollback. This blog covers those ways.
For the purpose of this blog, I made a simple example database. Feel free to clone it and try these examples yourself. It has one table people. I added a few people from DoltHub to the table over a few commits.
NOTE: Throughout this blog I will be using the Dolt CLI. However, equivalent functionality is exposed in SQL via Dolt procedures and Dolt System Tables
$ dolt sql -q "select * from people"
+----+------------+------------+--------------------+
| id | first_name | last_name | state_of_residence |
+----+------------+------------+--------------------+
| 0 | Tim | Sehn | California |
| 1 | Aaron | Son | California |
| 2 | Brian | Hendriks | California |
| 3 | Zach | Musgrave | Washington |
| 4 | Jason | Fulghum | Washington |
| 5 | Brian | Fitzgerald | Pennsylvania |
| 6 | Alec | Stein | New York |
+----+------------+------------+--------------------+
$ dolt log
commit qbhdlidekbpkuji28t209mbgr8mk49r9 (HEAD -> main)
Author: Tim Sehn <tim@dolthub.com>
Date: Mon Sep 12 11:48:09 -0700 2022
Added spacelove
commit as5ntp7mmakknhsvjoibfi0psbjvmohq
Author: Tim Sehn <tim@dolthub.com>
Date: Mon Sep 12 11:47:36 -0700 2022
Added Fitz
commit oofh2ndkkb5ejvv2mojnefh692e1r9mh
Author: Tim Sehn <tim@dolthub.com>
Date: Mon Sep 12 11:47:02 -0700 2022
Added Zach and Jason
commit 3ahdn16ocju6mv11ktmgjf8h6id212q9
Author: Tim Sehn <tim@dolthub.com>
Date: Mon Sep 12 11:45:57 -0700 2022
Added Brian
commit cmq47idmo3ntksf6asg07bhr8kqnngit
Author: Tim Sehn <tim@dolthub.com>
Date: Mon Sep 12 11:45:38 -0700 2022
Added Aaron
commit riuhiiajce25q07gjsksdvphd35a06oh
Author: Tim Sehn <tim@dolthub.com>
Date: Mon Sep 12 11:45:19 -0700 2022
Added Tim
commit hs76tqkq4u016lp8785be4hlan176eke
Author: Tim Sehn <tim@dolthub.com>
Date: Mon Sep 12 11:44:05 -0700 2022
Initialize data repository
Reset to a specific commit
First, let's say you want to rewind your database back to a specific commit. This can be done using the reset command. Like all Git commands it's a bit overloaded. In this usage, we're going to rewind the database to the commit before I added Zach and Jason.
First, we need to find the commit hash of the change we want to reset to. After inspecting the log, the commit before I added Zach and Jason was commit 3ahdn16ocju6mv11ktmgjf8h6id212q9. The commit message is "Added Brian".
There are two options for reset: soft and hard. I'll cover both.
Soft
If you want the changes between now (ie. HEAD) and then (ie. 3ahdn16ocju6mv11ktmgjf8h6id212q9) preserved after you reset, you use the dolt reset --soft option. No data in your database is destroyed. The commit history is rewound to 3ahdn16ocju6mv11ktmgjf8h6id212q9 and the changes that occurred between HEAD and 3ahdn16ocju6mv11ktmgjf8h6id212q9 look like changes made to the working set. You can even inspect the dolt diff to see what changed before making the decision to fully rollback. This is best shown through example.
$ dolt reset --soft 3ahdn16ocju6mv11ktmgjf8h6id212q9
$ dolt log
commit 3ahdn16ocju6mv11ktmgjf8h6id212q9 (HEAD -> main)
Author: Tim Sehn <tim@dolthub.com>
Date: Mon Sep 12 11:45:57 -0700 2022
Added Brian
commit cmq47idmo3ntksf6asg07bhr8kqnngit
Author: Tim Sehn <tim@dolthub.com>
Date: Mon Sep 12 11:45:38 -0700 2022
Added Aaron
commit riuhiiajce25q07gjsksdvphd35a06oh
Author: Tim Sehn <tim@dolthub.com>
Date: Mon Sep 12 11:45:19 -0700 2022
Added Tim
commit hs76tqkq4u016lp8785be4hlan176eke
Author: Tim Sehn <tim@dolthub.com>
Date: Mon Sep 12 11:44:05 -0700 2022
Initialize data repository
As you can see, the commit history has changed. Let's look a little closer though. The data has not changed.
$ dolt sql -q "select * from people"
+----+------------+------------+--------------------+
| id | first_name | last_name | state_of_residence |
+----+------------+------------+--------------------+
| 0 | Tim | Sehn | California |
| 1 | Aaron | Son | California |
| 2 | Brian | Hendriks | California |
| 3 | Zach | Musgrave | Washington |
| 4 | Jason | Fulghum | Washington |
| 5 | Brian | Fitzgerald | Pennsylvania |
| 6 | Alec | Stein | New York |
+----+------------+------------+--------------------+
$ dolt status
On branch main
Your branch is behind 'origin/main' by 3 commits, and can be fast-forwarded.
(use "dolt pull" to update your local branch)
Changes not staged for commit:
(use "dolt add <table>" to update what will be committed)
(use "dolt checkout <table>" to discard changes in working directory)
modified: people
$ dolt diff
diff --dolt a/people b/people
--- a/people @ gqhjonp9i2ujc6apc17ss3v9a7sqkkio
+++ b/people @ enltm56i20288sinuk3j5a35ip3spohe
+---+----+------------+------------+--------------------+
| | id | first_name | last_name | state_of_residence |
+---+----+------------+------------+--------------------+
| + | 3 | Zach | Musgrave | Washington |
| + | 4 | Jason | Fulghum | Washington |
| + | 5 | Brian | Fitzgerald | Pennsylvania |
| + | 6 | Alec | Stein | New York |
+---+----+------------+------------+--------------------+Usually, people use soft reset to compress commit history or as a two stage hard reset. If I now dolt checkout the people table, I am back to the state I was in commit 3ahdn16ocju6mv11ktmgjf8h6id212q9.
$ dolt checkout people
$ dolt sql -q "select * from people"
+----+------------+-----------+--------------------+
| id | first_name | last_name | state_of_residence |
+----+------------+-----------+--------------------+
| 0 | Tim | Sehn | California |
| 1 | Aaron | Son | California |
| 2 | Brian | Hendriks | California |
+----+------------+-----------+--------------------+
$ dolt diff
$You can also use this technique to only reset specific tables. After you've run dolt checkout on the particular tables you want to reset, make a new commit with your changes to keep the tables you did not want to rollback unchanged.
Hard
Now, let's say you just want the state of your database to look like a specified commit, potentially destroying changes you made. This is where you use dolt reset --hard.
For instance, let's say I didn't like what I did above and I want all my data back. Since I haven't run dolt gc to garbage collect those orphaned commits, they still exist. I'm going to reset hard to my previous HEAD.
$ dolt reset --hard qbhdlidekbpkuji28t209mbgr8mk49r9
$ dolt sql -q "select * from people"
+----+------------+------------+--------------------+
| id | first_name | last_name | state_of_residence |
+----+------------+------------+--------------------+
| 0 | Tim | Sehn | California |
| 1 | Aaron | Son | California |
| 2 | Brian | Hendriks | California |
| 3 | Zach | Musgrave | Washington |
| 4 | Jason | Fulghum | Washington |
| 5 | Brian | Fitzgerald | Pennsylvania |
| 6 | Alec | Stein | New York |
+----+------------+------------+--------------------+I'm back! Now, let's put the database back to 3ahdn16ocju6mv11ktmgjf8h6id212q9.
$ dolt reset --hard 3ahdn16ocju6mv11ktmgjf8h6id212q9
$ dolt sql -q "select * from people"
+----+------------+-----------+--------------------+
| id | first_name | last_name | state_of_residence |
+----+------------+-----------+--------------------+
| 0 | Tim | Sehn | California |
| 1 | Aaron | Son | California |
| 2 | Brian | Hendriks | California |
+----+------------+-----------+--------------------+As you can see, dolt reset --hard is the quickest way to restore your database to a previous state, but it does change the state of the database. If you get yourself in a bad situation and just want to go back to a certain state, you use dolt reset --hard.
Partial Rollback using SQL
Let's say you want to make partial rollbacks of a row or a column. You made some changes that are mostly good but need to fix some mistakes upon review.
A row
Let's say you accidentally updated a row. All the other changes you made are good but you just want to rollback that row.
Here's an example:
$ dolt commit -am "Update first name where last name starts with S"
commit nhudgovfdbsvbhvjkmflj36uk0k54dgs (HEAD -> main)
Author: Tim Sehn <tim@dolthub.com>
Date: Mon Sep 12 12:19:13 -0700 2022
Update first name where last name starts with S
rollback $ dolt diff HEAD^
diff --dolt a/people b/people
--- a/people @ enltm56i20288sinuk3j5a35ip3spohe
+++ b/people @ f294f7f68fntb6jnfimvjfl3r1juci8b
+---+----+------------+-----------+--------------------+
| | id | first_name | last_name | state_of_residence |
+---+----+------------+-----------+--------------------+
| < | 0 | Tim | Sehn | California |
| > | 0 | Timothy | Sehn | California |
| < | 1 | Aaron | Son | California |
| > | 1 | Timothy | Son | California |
| < | 6 | Alec | Stein | New York |
| > | 6 | Timothy | Stein | New York |
+---+----+------------+-----------+--------------------+Oops. I didn't realize other last names started with "S". Here, what you want to do is rollback rows with the 1 (Aaron) and 6 (Alec) ids because I've changed first names I did not intend to change. We leverage the fact that in Dolt you can select AS OF a commit.
rollback $ dolt sql -q "select * from people as of 'qbhdlidekbpkuji28t209mbgr8mk49r9' where id in (1,6)"
+----+------------+-----------+--------------------+
| id | first_name | last_name | state_of_residence |
+----+------------+-----------+--------------------+
| 6 | Alec | Stein | New York |
| 1 | Aaron | Son | California |
+----+------------+-----------+--------------------+Now, we want to update those two rows to those values. To do that we construct a JOIN for UPDATE. I'm going to format it nicely because it's a bit of a long query.
UPDATE people
JOIN
(SELECT id, first_name, last_name, state_of_residence
FROM people
AS OF 'qbhdlidekbpkuji28t209mbgr8mk49r9'
WHERE id IN (1,6)) as rollback
ON people.id=rollback.id
SET
people.first_name=rollback.first_name,
people.last_name=rollback.last_name,
people.state_of_residence=rollback.state_of_residenceThis is what it looks like when I run it.
$ dolt sql -q "update people join (select id, first_name, last_name, state_of_residence from people as of 'qbhdlidekbpkuji28t209mbgr8mk49r9' where id in (1,6)) as rollback on people.id=rollback.id set people.first_name=rollback.first_name, people.last_name=rollback.last_name, people.state_of_residence=rollback.state_of_residence"
Query OK, 2 rows affected
Rows matched: 2 Changed: 2 Warnings: 0
$ dolt diff
diff --dolt a/people b/people
--- a/people @ f294f7f68fntb6jnfimvjfl3r1juci8b
+++ b/people @ fteb40k31dm4tt168ti0eipctlvbiv1t
+---+----+------------+-----------+--------------------+
| | id | first_name | last_name | state_of_residence |
+---+----+------------+-----------+--------------------+
| < | 1 | Timothy | Son | California |
| > | 1 | Aaron | Son | California |
| < | 6 | Timothy | Stein | New York |
| > | 6 | Alec | Stein | New York |
+---+----+------------+-----------+--------------------+All better. Combining AS OF and JOIN for UPDATE is a powerful way to partially roll back.
A column
You accidentally updated a column. All the other changes you made are good but you just want to rollback that column.
Here's an example:
$ dolt sql -q "update people set first_name=lower(first_name), last_name=lower(last_name), state_of_residence=lower(state_of_residence)"
Query OK, 7 rows affected
Rows matched: 7 Changed: 7 Warnings: 0
$ dolt sql -q "select * from people"
+----+------------+------------+--------------------+
| id | first_name | last_name | state_of_residence |
+----+------------+------------+--------------------+
| 0 | tim | sehn | california |
| 1 | aaron | son | california |
| 2 | brian | hendriks | california |
| 3 | zach | musgrave | washington |
| 4 | jason | fulghum | washington |
| 5 | brian | fitzgerald | pennsylvania |
| 6 | alec | stein | new york |
+----+------------+------------+--------------------+Let's say I want names lower cased but I want to keep state names upper case. We again leverage the fact that in Dolt you can select AS OF a commit.
$ dolt sql -q "select state_of_residence from people as of 'qbhdlidekbpkuji28t209mbgr8mk49r9'"
+--------------------+
| state_of_residence |
+--------------------+
| California |
| California |
| California |
| Washington |
| Washington |
| Pennsylvania |
| New York |
+--------------------+Again, we JOIN for UPDATE.
UPDATE people
JOIN
(SELECT id,state_of_residence
FROM people as of 'qbhdlidekbpkuji28t209mbgr8mk49r9') as rollback
ON people.id=rollback.id
SET people.state_of_residence=rollback.state_of_residenceAnd voila:
$ dolt sql -q "update people join (select id,state_of_residence from people as of 'qbhdlidekbpkuji28t209mbgr8mk49r9') as rollback on people.id=rollback.id set people.state_of_residence=rollback.state_of_residence"
Query OK, 7 rows affected
Rows matched: 7 Changed: 7 Warnings: 0
rollback $ dolt sql -q "select * from people"
+----+------------+------------+--------------------+
| id | first_name | last_name | state_of_residence |
+----+------------+------------+--------------------+
| 0 | tim | sehn | California |
| 1 | aaron | son | California |
| 2 | brian | hendriks | California |
| 3 | zach | musgrave | Washington |
| 4 | jason | fulghum | Washington |
| 5 | brian | fitzgerald | Pennsylvania |
| 6 | alec | stein | New York |
+----+------------+------------+--------------------+You can accomplish all manner of partial rollbacks using AS OF queries combined with JOIN for UPDATE. You also have the dolt_diff and dolt_history tables to join against if you are not sure exactly which commit to reference.
Revert a specific commit
There is a bad change you want to undo. You want to keep all the other changes, just undo this one.
In the above example, let's say I don't want the change where I added Zach and Jason, commit oofh2ndkkb5ejvv2mojnefh692e1r9mh. To undo that specific change, I use dolt revert.
$ dolt revert oofh2ndkkb5ejvv2mojnefh692e1r9mh
commit opbmmr7pomblt19s5ro8pkub77rdjcbj (HEAD -> main)
Author: Tim Sehn <tim@dolthub.com>
Date: Mon Sep 12 14:09:03 -0700 2022
Revert "Added Zach and Jason"
$ dolt sql -q "select * from people"
+----+------------+------------+--------------------+
| id | first_name | last_name | state_of_residence |
+----+------------+------------+--------------------+
| 0 | Tim | Sehn | California |
| 1 | Aaron | Son | California |
| 2 | Brian | Hendriks | California |
| 5 | Brian | Fitzgerald | Pennsylvania |
| 6 | Alec | Stein | New York |
+----+------------+------------+--------------------+Now the reverse change is applied to the HEAD of my database and Zach and Jason are no longer in the table. Dolt revert is a very easy and powerful way to undo bad changes to your live database.
Restore from a Remote
You want to grab a remote copy because you either deleted your local copy or messed it up beyond repair.
Let's go back to the example above where I reset to a previous commit but this time, I will run dolt gc to garbage collect the orphaned commits to make them unrecoverable with another reset.
$ dolt reset --hard 3ahdn16ocju6mv11ktmgjf8h6id212q9
$ dolt gc
$ dolt sql -q "select * from people"
+----+------------+-----------+--------------------+
| id | first_name | last_name | state_of_residence |
+----+------------+-----------+--------------------+
| 0 | Tim | Sehn | California |
| 1 | Aaron | Son | California |
| 2 | Brian | Hendriks | California |
+----+------------+-----------+--------------------+
$ dolt reset --hard qbhdlidekbpkuji28t209mbgr8mk49r9
error: Failed to reset changes.
cause: target commit not foundIn this case, since I've pushed my database to a remote, namely DoltHub, I can recover the latest changes by running dolt pull to merge the copy from the remote with my copy.
$ dolt pull
Updating 3ahdn16ocju6mv11ktmgjf8h6id212q9..qbhdlidekbpkuji28t209mbgr8mk49r9
Fast-forward
$ dolt sql -q "select * from people"
+----+------------+------------+--------------------+
| id | first_name | last_name | state_of_residence |
+----+------------+------------+--------------------+
| 0 | Tim | Sehn | California |
| 1 | Aaron | Son | California |
| 2 | Brian | Hendriks | California |
| 3 | Zach | Musgrave | Washington |
| 4 | Jason | Fulghum | Washington |
| 5 | Brian | Fitzgerald | Pennsylvania |
| 6 | Alec | Stein | New York |
+----+------------+------------+--------------------+Restoring from a remote can also be used if I run DROP DATABASE in SQL mode as that command deletes all history on disk. In that case, you must dolt clone a fresh copy from a remote.
Conclusion
Dolt has a myriad of ways to rollback. This set of features are some of Dolt's most compelling. Interested in using Dolt's rollback functionalty, but are unsure how to get started? Come chat with us on our Discord.