Querying History

Dolt databases allow you to query the data at any point in the commit history. There are several ways to do so.
Please note: when querying history, the unit of snapshot is the dolt commit. SQL transaction commits do not create a dolt commit by default.
Querying past snapshots with AS OF
Dolt SQL supports a variant of SQL 2011 syntax to query non-HEAD revisions of a database via the AS OF clause:
SELECT * FROM myTable AS OF 'kfvpgcf8pkd6blnkvv8e0kle8j6lug7a';
SELECT * FROM myTable AS OF 'myBranch';
SELECT * FROM myTable AS OF 'HEAD^2';
SELECT * FROM myTable AS OF TIMESTAMP('2020-01-01');
SELECT * FROM myTable AS OF 'myBranch' JOIN myTable AS OF 'yourBranch' AS foo;
The AS OF expression must name a valid Dolt reference, such as a commit hash, branch name, or other reference. Timestamp / date values are also supported. Each table in a query can use a different AS OF clause.
In addition to this AS OF syntax for SELECT statements, Dolt also supports various extensions to the standard MySQL syntax to examine the schemas of snapshots:
SHOW TABLES AS OF 'kfvpgcf8pkd6blnkvv8e0kle8j6lug7a';
SHOW CREATE TABLE myTable AS OF 'myBranch';
DESCRIBE myTable AS OF 'HEAD~';
Specifying a commit hash for an entire connection
You can connect to a non-HEAD, read-only database where every table is locked to a particular commit. Use a connection string like this:
mysql://127.0.0.1:3306/mydb/ia1ibijq8hq1llr7u85uivsi5lh3310p
Or a USE statement. Note the backtick quoting, which is necessary since the database identifier contains a slash character.
USE `mydb/ia1ibijq8hq1llr7u85uivsi5lh3310p`
Or specify the commit hash directly in the query. This is equivalent to AS OF, but works in some queries where the AS OF syntax is not supported.
show create table `mydb/ia1ibijq8hq1llr7u85uivsi5lh3310p`.myTable;
There are other variations on this as well. See the docs on using branches for more details.
Querying history using dolt system tables
For every table in the database, dolt also provides a set of system tables that you can query to see past values of rows, diffs between revisions, and more.
the dolt_history tables provide a row for every revision of a row in a table.
SELECT * FROM dolt_history_mytable
WHERE state = "Virginia"
ORDER BY "commit_date"
​
+----------+------------+----------+-------------+-----------+---------------------------------+
| state    | population | capital  | commit_hash | committer | commit_date                     |
+----------+------------+----------+-------------+-----------+---------------------------------+
| Virginia | 691937     | NULL     | ...         | billybob  | 1790-01-09 00:00:00.0 +0000 UTC |
| Virginia | 807557     | Richmond | ...         | billybob  | 1800-01-01 00:00:00.0 +0000 UTC |
| Virginia | 877683     | NULL     | ...         | billybob  | 1810-01-01 00:00:00.0 +0000 UTC |
+----------+------------+----------+-------------+-----------+---------------------------------+
To query how rows changed between two commits, use the dolt_commit_diff and dolt_diff tables.
SELECT * FROM dolt_commit_diff_mytable
WHERE to_commit = HASHOF('HEAD')
AND from_commit = HASHOF('HEAD~')
ORDER BY state, to_commit_date;
For more information, see the system table docs.
Querying historical view data
Database views are an edge case for historical queries. When you have a database view whose definition has changed, querying it with AS OF will use the current definition of the view, but use rows from the tables as they existed at the revision provided.
To query the historical definition of a view, you must checkout the database at a particular commit. You can do this by changing your branch, e.g.:
select dolt_checkout('-b', 'old-view-def', '81223g1cpmib215gmov8686b6310p37d');
You can also do this without changing your session's branch by using a commit hash-qualified database identifier when referencing the view.
Consider this example:
-- Past data
view_test> select * from t1 as of '81223g1cpmib215gmov8686b6310p37d';
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
+---+---+
-- Past view definition
view_test> show create table `view_test/81223g1cpmib215gmov8686b6310p37d`.v1;
+------+--------------------------------------+
| View | Create View                          |
+------+--------------------------------------+
| v1   | CREATE VIEW `v1` AS select * from t1 |
+------+--------------------------------------+
-- Current data
view_test> select * from t1;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+---+
-- Current view definition
view_test> show create table v1;
+------+-----------------------------------------------+
| View | Create View                                   |
+------+-----------------------------------------------+
| v1   | CREATE VIEW `v1` AS select a+10, b+10 from t1 |
+------+-----------------------------------------------+
-- Select past data using current view definition
view_test> select * from v1 as of '81223g1cpmib215gmov8686b6310p37d';
+------+------+
| a+10 | b+10 |
+------+------+
| 11   | 11   |
| 12   | 12   |
+------+------+
-- Select past data using past view definition
view_test> select * from `view_test/81223g1cpmib215gmov8686b6310p37d`.v1;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
+---+---+
-- Select past data using past view definition by checking out a new branch
view_test> select dolt_checkout('-b', 'old-view-def', '81223g1cpmib215gmov8686b6310p37d');
view_test> select * from v1;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
+---+---+

Merges

Procedures

Last modified 4mo ago

Export as PDF

Copy link

Edit on GitHub

On this page

Querying past snapshots with AS OF

Specifying a commit hash for an entire connection

Querying history using dolt system tables

Querying historical view data