Imagine a critical bug appears in your application, or perhaps a data entry error corrupts a crucial record. In a traditional database, fixing this often means scrambling for backups, losing recent changes, or painstakingly reconstructing data. But what if you could simply “rewind” your database to any point in time, inspect its state, or even revert specific changes with the ease of Git?

That’s precisely what Dolt’s “time travel” capabilities and data rollback features offer. This chapter dives deep into how Dolt transforms your database into a version-controlled timeline, allowing you to query historical data, understand exactly what changed, and confidently undo mistakes without complex recovery procedures.

By the end of this chapter, you’ll be able to:

  • Query your database “as of” any past commit, branch, or timestamp.
  • Precisely identify data and schema changes using dolt diff.
  • Safely roll back data to previous states using dolt checkout and dolt revert.
  • Understand the critical differences between read-only historical queries and destructive rollback operations.

Before we begin, ensure you’re comfortable with basic Dolt commands like dolt clone, dolt commit, and dolt log from previous chapters. We’ll build on those foundational Git-for-Data concepts.

The Magic of Time Travel: Querying Historical Data

One of Dolt’s most powerful features is its ability to let you query your data as it existed at any previous point in time. This isn’t just about looking at old backups; it’s about executing standard SQL queries against historical versions of your tables, seamlessly integrated into your workflow.

Why Time Travel Matters

Why would you need to query historical data? Think about:

  • Auditing: Regulatory compliance often demands a clear, immutable record of every data change.
  • Debugging: Pinpointing when a bug or data corruption was introduced by inspecting the database’s state at various points in time.
  • Analytics: Analyzing trends or comparing data across specific historical snapshots without complex data warehousing.
  • Recovery: Easily viewing a correct state before deciding to perform a rollback.

Introducing the AS OF Clause

Dolt extends standard SQL with an AS OF clause, allowing you to specify the exact version of your database you want to query. Think of it as peeking into a specific snapshot from your database’s Git history.

You can specify the version using:

  • Commit Hash: A unique identifier for a database state.
  • Branch Name: The latest state on a particular branch.
  • Timestamp: A specific date and time.

Let’s set up a simple table and make some changes to demonstrate.

Step-by-Step: Querying Data with AS OF

First, let’s create a database and a table. We’ll start by ensuring we are on the main branch.

dolt sql -q "CREATE DATABASE IF NOT EXISTS inventory_db;"
dolt checkout main # Ensure we are on the main branch
dolt sql inventory_db -q "CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(255), stock INT);"
dolt commit -m "Add products table"

Next, we’ll add some initial data to our products table and commit it. This creates our first significant historical point.

dolt sql inventory_db -q "INSERT INTO products (id, name, stock) VALUES (1, 'Laptop', 50), (2, 'Mouse', 200);"
dolt commit -m "Add initial products"

Now, let’s make another change to create more history, simulating a stock adjustment.

dolt sql inventory_db -q "UPDATE products SET stock = 45 WHERE id = 1;"
dolt commit -m "Adjust stock for Laptop"

To view your commit history and get the hashes, use dolt log:

dolt log

You’ll see output similar to this (actual hashes and dates will differ):

commit 8a1b2c3d4e5f678901234567890abcdef01234567
Author: Your Name <[email protected]>
Date:   2026-06-06 10:00:00 +0000

    Adjust stock for Laptop

commit f1a2b3c4d5e6f78901234567890abcdef01234567
Author: Your Name <[email protected]>
Date:   2026-06-06 09:55:00 +0000

    Add initial products

commit c0d1e2f3a4b5c6d7e8f90123456789abcdef012345
Author: Your Name <[email protected]>
Date:   2026-06-06 09:50:00 +0000

    Add products table

Let’s query the current state of our products table:

dolt sql inventory_db -q "SELECT * FROM products;"

You should see the updated stock for ‘Laptop’:

+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
| 1  | Laptop | 45    |
| 2  | Mouse  | 200   |
+----+--------+-------+

Querying AS OF a Commit Hash

To query the state of the products table before the last commit, you’ll need the commit hash of the “Add initial products” commit. Find it using dolt log. For this example, let’s assume it was f1a2b3c4 (you’ll use your actual hash).

SELECT * FROM products AS OF 'f1a2b3c4';

Explanation: The AS OF 'f1a2b3c4' clause tells Dolt to execute the SELECT query against the database state captured by commit f1a2b3c4. This is a read-only operation; your main branch remains unchanged.

+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
| 1  | Laptop | 50    |
| 2  | Mouse  | 200   |
+----+--------+-------+

Notice how the stock for ‘Laptop’ is 50, not 45. You’ve successfully time-traveled to a previous state!

Querying AS OF a Timestamp

Dolt also allows you to query AS OF a specific timestamp. This is incredibly useful for point-in-time analysis or recovering data from a particular moment.

SELECT * FROM products AS OF '2026-06-06 09:56:00'; -- IMPORTANT: Adjust this to a timestamp *after* "Add initial products" but *before* "Adjust stock for Laptop" from your `dolt log` output.

Explanation: Dolt will find the latest commit that occurred at or before the specified timestamp and execute the query against that version of the database. This allows for very granular historical data access.

Querying AS OF a Branch

If you have multiple branches, you can easily query the state of a table as it exists on a different branch without actually checking out that branch.

Let’s create a new branch and add a product there:

dolt branch feature/new-product
dolt checkout feature/new-product
dolt sql inventory_db -q "INSERT INTO products (id, name, stock) VALUES (3, 'Keyboard', 150);"
dolt commit -m "Add Keyboard on feature branch"

Now, switch back to main to see the magic:

dolt checkout main

From your main branch, query the products table as it exists on the feature/new-product branch:

SELECT * FROM products AS OF 'feature/new-product';

You’ll see the ‘Keyboard’ product, even though it’s not present on your main branch.

+----+----------+-------+
| id | name     | stock |
+----+----------+-------+
| 1  | Laptop   | 45    |
| 2  | Mouse    | 200   |
| 3  | Keyboard | 150   |
+----+----------+-------+

How AS OF Queries Work

Under the hood, when you use AS OF, Dolt intelligently rewrites your SQL query to target the specific historical state. It leverages its immutable storage model, where every commit is a complete snapshot of the database, to efficiently reconstruct the data for your query.

flowchart TD A[SQL Query with AS OF] --> B{Parse AS OF clause} B --> C{Identify Target Commit} C --> D[Dolt's Storage Layer] D --> E[Reconstruct Database State] E --> F[Execute SQL Query] F --> G[Return Historical Data]

📌 Key Idea: AS OF queries are read-only and non-destructive. They allow you to inspect the past without altering your current database state. This is crucial for auditing, reporting, and debugging without risking your live data.

⚡ Real-world insight: Many financial institutions and regulatory bodies require detailed audit trails. AS OF queries in Dolt allow you to reconstruct the exact state of your data at any past moment, providing irrefutable evidence for compliance and forensics.

Inspecting Changes: dolt diff for Data and Schema

While AS OF lets you see past states, dolt diff is your magnifying glass for understanding how the database changed between any two states. Just like Git’s diff, Dolt’s diff command shows you line-by-line (or rather, row-by-row and column-by-column) what was added, deleted, or modified.

Why dolt diff is Essential

  • Change Review: Before merging a branch, review all data and schema changes.
  • Debugging: Quickly identify which specific data point or schema definition changed between two versions.
  • Auditing: Understand the full scope of a change for compliance or incident response.

Step-by-Step: Using dolt diff

Let’s make another change to our products table:

dolt sql inventory_db -q "UPDATE products SET stock = 190 WHERE id = 2;"
dolt commit -m "Adjust stock for Mouse"

Now, let’s see the difference between the current state (HEAD) and the previous commit (HEAD~1). HEAD~1 refers to the commit immediately before the current HEAD.

dolt diff --data products HEAD~1 HEAD
--- a/products
+++ b/products
@@ -1,3 +1,3 @@
  | id | name   | stock |
 |----+--------+-------|
-| 2  | Mouse  | 200   |
+| 2  | Mouse  | 190   |

Explanation:

  • --- a/products and +++ b/products indicate the “before” and “after” states of the products table.
  • The @@ line shows the line number and count for the changed block.
  • Lines prefixed with - are removed (representing the old value).
  • Lines prefixed with + are added (representing the new value).

Here, we clearly see that for id = 2 (‘Mouse’), the stock changed from 200 to 190.

You can also compare between two arbitrary commit hashes:

dolt diff --data products <commit_hash_1> <commit_hash_2>

Or between two branches (this would show the ‘Keyboard’ product as an addition):

dolt diff --data products main feature/new-product

Diffing Schema Changes

Dolt also tracks schema changes. If you modify a table structure, dolt diff can show you those changes.

Let’s add a new column to our products table:

dolt sql inventory_db -q "ALTER TABLE products ADD COLUMN description TEXT;"
dolt commit -m "Add description column to products"

Now, let’s diff the schema between the current state and the previous commit:

dolt diff --schema products HEAD~1 HEAD
--- a/products
+++ b/products
@@ -1,4 +1,5 @@
 CREATE TABLE `products` (
   `id` int NOT NULL,
   `name` varchar(255) COLLATE utf8mb4_0900_bin NOT NULL,
-  `stock` int NOT NULL,
+  `stock` int NOT NULL,
+  `description` text COLLATE utf8mb4_0900_bin,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;

Explanation: The output clearly shows the description column being added to the CREATE TABLE statement. This is invaluable for understanding how your database schema has evolved over time, especially in collaborative environments or when debugging application migrations.

Rewriting History (Carefully): Data Rollbacks

While AS OF and dolt diff are excellent for inspection, sometimes you need to actually undo changes or revert your database to a previous state. Dolt provides powerful commands for this, similar to Git, but with important distinctions for data.

dolt checkout: Reverting Uncommitted Changes or Specific Tables

You’ve already used dolt checkout to switch branches. You can also use it to discard uncommitted changes or to revert a specific table to its state from the last commit. This is useful when you’ve made local changes you no longer want.

Let’s make an uncommitted change:

dolt sql inventory_db -q "UPDATE products SET stock = 10 WHERE id = 1;"

Now, check the status of your working directory:

dolt status

You’ll see products listed under “Changes not staged for commit,” indicating modifications that haven’t been saved to version history yet.

To discard this uncommitted change, just like in Git:

dolt checkout products

Explanation: dolt checkout <table_name> discards any uncommitted changes in that table, reverting it to the state of the HEAD commit (the last committed version).

Now, if you query products, the stock for ‘Laptop’ will be 45 again, not 10.

dolt revert: The Safer Way to Undo Committed Changes

dolt revert is the recommended way to undo a committed change in Dolt. It creates a new commit that systematically undoes the changes introduced by a previous commit. This preserves the history, showing that a change was made and then explicitly undone, which is crucial for auditability and team collaboration.

Let’s recall our commit history using dolt log --oneline:

dolt log --oneline

Let’s say the commit Adjust stock for Mouse (which changed stock from 200 to 190) has a hash like a1b2c3d. We want to undo this specific change.

dolt revert a1b2c3d

Dolt will perform the revert. It might open your default text editor (like Vim or Nano) to allow you to modify the new commit message. The default message will typically look like this:

Revert "Adjust stock for Mouse"

This reverts commit a1b2c3d.

Accept the message (e.g., by saving and closing the editor).

Now, if you query products for id = 2:

dolt sql inventory_db -q "SELECT * FROM products WHERE id = 2;"

The stock for ‘Mouse’ will be 200 again. If you check dolt log, you’ll see a new commit, explicitly undoing the previous one, maintaining a clear and auditable history.

🧠 Important: dolt revert is generally preferred over dolt reset for shared history because it creates a new commit that explicitly undoes changes, maintaining a clear, linear history. This is vital when working with teams, as it avoids rewriting history that others might have already pulled.

dolt reset: A Powerful, Destructive Tool

dolt reset is similar to Git’s reset and is a powerful command that can rewrite history. It moves the current branch’s HEAD to a specified commit, effectively “erasing” subsequent commits from that branch’s history.

⚠️ What can go wrong: Using dolt reset on a branch that has been pushed to a remote (like DoltHub) can cause significant problems for collaborators, as it rewrites shared history. This can lead to lost work and complex merge scenarios. Use it with extreme caution, typically only on local, unpushed branches, or when you fully understand the implications for all collaborators.

For example, to move HEAD back to the commit before the Adjust stock for Laptop commit (e.g., commit f1a2b3c4 in our earlier example, which was “Add initial products”):

dolt reset --hard f1a2b3c4

Explanation:

  • --hard means that not only will HEAD move, but your working directory and staging area will also be updated to match the state of f1a2b3c4. Any commits after f1a2b3c4 on this branch will be permanently lost.

After a hard reset, if you query products, the stock for ‘Laptop’ will be 50 and ‘Mouse’ will be 200 (assuming f1a2b3c4 was the “Add initial products” commit). The commits that happened after f1a2b3c4 are no longer part of this branch’s history.

Hands-On Challenge: Auditing and Recovering Inventory

Let’s put your new knowledge to the test. You’re managing an inventory system, and a mistake was made in a recent update.

Challenge:

  1. Add a new product and commit:
    • Add a product: (4, 'Monitor', 100)
    • Commit with message: “Add Monitor product”
  2. Introduce an error:
    • Update ‘Monitor’ stock to 10 (instead of 100). This is your simulated mistake.
    • Commit with message: “Update Monitor stock (mistake)”
  3. Discover the mistake:
    • Use dolt log --oneline to see your recent commits and their hashes.
    • Use dolt diff --data products <commit_hash_before_mistake> <commit_hash_with_mistake> to pinpoint the exact change where the stock became 10.
  4. View the correct state:
    • Use an AS OF query to view the products table as it existed before the “Update Monitor stock (mistake)” commit. Observe the correct stock for ‘Monitor’ (it should be 100).
  5. Revert the error:
    • Use dolt revert to undo the “Update Monitor stock (mistake)” commit.
    • Verify that ‘Monitor’ stock is back to 100 by querying the products table.

Hint: Pay close attention to the commit hashes from dolt log. They are your key to navigating history. You’ll need the hash of the “Add Monitor product” commit for your AS OF query and the hash of the “Update Monitor stock (mistake)” commit for dolt revert.

Click for Solution HintTo find the commit hash for the "Add Monitor product" commit (the good state before the mistake), use `dolt log --oneline`. Then, use that hash in your `AS OF` query to inspect the correct state. For `dolt revert`, you'll target the hash of the "Update Monitor stock (mistake)" commit.

Once you’ve completed the challenge, take a moment to reflect on how much easier and safer this process is compared to traditional database recovery methods.

Common Pitfalls and Troubleshooting

  • Forgetting to dolt commit: If you make changes and immediately try AS OF or dolt diff, Dolt will only show you the committed history. Uncommitted changes are not part of the version history. Always dolt commit your changes to make them versioned.
  • Misunderstanding dolt reset vs. dolt revert:
    • dolt revert creates a new commit that undoes a previous commit, preserving history. Use this for shared branches.
    • dolt reset rewrites history by moving the branch pointer, effectively erasing commits. Use with extreme caution, primarily on local, unpushed branches.
  • Performance with AS OF on large historical datasets: While Dolt is optimized for versioned data, querying very old or very large historical versions might be slower than querying the current HEAD. Consider indexing frequently queried historical columns and strategically pruning old branches if history beyond a certain point is not needed for active queries.
  • Accidentally discarding uncommitted changes: Using dolt checkout <table_name> will discard any changes you’ve made to that table that haven’t been dolt added and dolt commited. Always check dolt status first to understand the state of your working directory.
  • Merge conflicts during dolt revert: If the commit you’re reverting has changes that were later modified by another commit, you might encounter a merge conflict. Dolt will guide you through resolving these, similar to Git merge conflicts, requiring you to manually decide which changes to keep.

Summary: Your Data’s Timeline at Your Fingertips

In this chapter, you’ve gained powerful capabilities for navigating and manipulating your database’s history:

  • Time Travel with AS OF: You can query your data as it existed at any specific commit, branch, or timestamp, providing invaluable insights for auditing, debugging, and historical analysis.
  • Precision with dolt diff: You learned how to use dolt diff to meticulously examine both data and schema changes between any two versions of your database.
  • Confident Rollbacks: You explored how dolt checkout can discard uncommitted changes and how dolt revert provides a safe, history-preserving method to undo committed changes. You also understood the power and danger of dolt reset for rewriting history.

These features fundamentally change how you interact with a database, moving from a single mutable state to a rich, auditable timeline. You now have the tools to understand data evolution, recover from errors, and ensure data integrity with a level of control previously unimaginable in traditional SQL databases.

Next, we’ll explore how Dolt extends the Git collaboration model to data, enabling seamless teamwork and robust workflows with Dolt remotes and DoltHub.

References

This page is AI-assisted and reviewed. It references official documentation and recognized resources where relevant.