How to Query Dolt Commit History for Tables Using dolt_log and dolt_history_*

Dolt exposes every commit as queryable SQL rows through the dolt_log system table and dolt_history_* tables, allowing you to inspect repository and table-level changes using standard SELECT statements.

Dolt treats your database like a Git repository, storing every state change as a commit object that captures the entire database at a specific moment. Unlike traditional databases that overwrite data, Dolt's version-controlled architecture in the dolthub/dolt repository persists all historical states, making Dolt commit history for tables fully accessible via system tables. This guide explains how Dolt stores commit metadata and how to query it using SQL.

How Dolt Stores Commits in the Storage Layer

At the storage layer, Dolt writes commit objects as Noms values into the value store. The core routine that persists these commits lives in go/store/types/value_store.go, specifically the Commit method at line 519, which writes a new root value and a commit address (hash.Hash) into the object store.

Commit addresses use a specific encoding defined in go/store/val/codec.go at line 98, where the CommitAddrEnc field type indicates how commit references serialize into tuples. When building or reading these tuples, the system uses go/store/val/tuple_builder.go at line 487 via TupleBuilder.PutCommitAddr and TupleDesc.GetCommitAddr to handle the low-level encoding and decoding of commit references.

Querying Repository History with dolt_log

The dolt_log System Table Schema

The dolt_log table is a virtual table generated at query time that surfaces commit metadata as rows. Its schema includes:

• commit_hash: The SHA-1-like identifier of the commit
• committer: User who created the commit
• email: Email address of the committer
• date: Timestamp of the commit
• message: Commit message
• parent_hashes: Parent commit hashes for merge tracking

Implementation in history_table.go

The table driver implementation resides in go/libraries/doltcore/sqle/history_table.go. The rowConverter function at line 585 maps raw commit Noms tuples into the SQL columns expected by dolt_log. When executing a query, the engine calls GetRows on this virtual table, which walks the commit DAG using the value store's Commit iterator built on top of the tuple descriptors that understand CommitAddrEnc.

Tracking Table-Level Changes with dolt_history_*

For per-table history, Dolt generates history tables dynamically named dolt_history_<tablename>. These tables include all original columns from the user table plus commit metadata columns (commit_hash, committer, etc.), enabling queries like SELECT * FROM dolt_history_orders WHERE commit_hash = 'abc123'.

The conversion logic for these table-specific histories forks from the dolt_log converter in go/libraries/doltcore/sqle/schema_override.go at line 164. This implementation allows you to view any row's value at any point in history using standard SQL predicates.

Query Planning and Optimization

Execution Plans for History Queries

The SQL optimizer recognizes dolt_log and dolt_history_* as system tables. When you add an ORDER BY clause, the planner generates a Sort operator. For example, ordering by commit hash produces a plan documented in go/libraries/doltcore/sqle/enginetest/dolt_query_plans.go at line 90:

Sort(dolt_log.commit_hash ASC)
   └─ name: dolt_log

Join Optimization with Commit Tables

Queries joining diff tables with commit logs can use lookup join hints. The engine rewrites these to hash-based joins on the commit_hash column, as tested in go/libraries/doltcore/sqle/kvexec/lookup_join_test.go at line 136. Use the hint format:

/*+ LOOKUP_JOIN(dolt_diff_xy,dolt_log) */

Access Control and Privileges

dolt_log enforces Dolt's privilege system. According to tests in go/libraries/doltcore/sqle/enginetest/dolt_queries.go at line 1608, users without database-wide access receive errors when querying dolt_log('main'), while users granted explicit SELECT privileges can execute SELECT COUNT(*) FROM dolt_log('main').

Practical SQL Examples for Dolt Commit History

List Recent Commits

SELECT commit_hash, committer, date, message
FROM dolt_log
ORDER BY date DESC
LIMIT 10;

Count Commits in a Specific Branch

SELECT COUNT(*) FROM dolt_log('feature-branch');

View a Table's State at a Specific Commit

SELECT *
FROM dolt_history_orders
WHERE commit_hash = 'c3f9a2e1...'
ORDER BY pk;

Find When a Specific Row Changed

SELECT commit_hash, date, message
FROM dolt_history_customers
WHERE customer_id = 42
ORDER BY date DESC;

Combine Diffs with Commit Metadata

SELECT d.from_commit, d.to_commit, l.message
FROM dolt_diff_mytable AS d
JOIN dolt_log AS l ON d.from_commit = l.commit_hash;

Summary

• Dolt stores commits as Noms values in the value store, with addresses encoded using CommitAddrEnc in go/store/val/codec.go.
• The dolt_log system table exposes repository-wide commit metadata through the virtual table driver in go/libraries/doltcore/sqle/history_table.go.
• Table-specific history is available via dolt_history_<tablename> tables, implemented in go/libraries/doltcore/sqle/schema_override.go.
• Query plans for history tables support sorting and lookup joins on commit_hash for efficient filtering.
• Access control requires explicit SELECT privileges on the database to query commit history.

Frequently Asked Questions

How do I view the commit history for a specific table in Dolt?

Query the dolt_history_<tablename> system table, which contains every row version along with commit metadata. For example, SELECT * FROM dolt_history_users WHERE commit_hash = 'abc123' returns the state of the users table at that specific commit.

What is the difference between dolt_log and dolt_history_* tables?

dolt_log shows repository-level commit metadata (hash, author, message) for the entire database, while dolt_history_* tables show row-level data changes for a specific table combined with the commit that produced each version.

Can I join commit history tables with regular tables or diff tables?

Yes, you can join dolt_log with diff tables using the commit_hash column. The optimizer supports lookup joins on these columns, and you can use hints like /*+ LOOKUP_JOIN(dolt_diff_mytable,dolt_log) */ to optimize query performance as implemented in the engine.

Do I need special permissions to query Dolt commit history?

Yes, querying dolt_log requires SELECT privileges on the database. Users without these privileges will receive an error when attempting to query commit history, as enforced by Dolt's privilege system in go/libraries/doltcore/sqle/enginetest/dolt_queries.go.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}