Dolt SQL Features and Capabilities: A Complete Technical Guide

Dolt is a MySQL-compatible SQL database that combines standard relational operations with Git-style version control, enabling you to branch, merge, and time-travel through data history using pure SQL commands.

Dolt SQL features and capabilities extend far beyond traditional relational databases by embedding version control directly into the query layer. As an open-source project maintained by Dolthub, the dolthub/dolt repository provides a MySQL-compatible server that treats tables like Git repositories, allowing data engineers to execute JOINs and transactions while simultaneously managing commits and branches through system tables and stored procedures.

Core Dolt SQL Features and MySQL Compatibility

Standard SQL Support

Dolt implements a fully MySQL-compatible query engine supporting all standard constructs from MySQL 5.7 and 8.x. According to the project README.md, this includes:

• Foreign keys enforcing referential integrity across tables using FOREIGN KEY (team_id) REFERENCES teams(id)
• Secondary indexes created via CREATE INDEX idx_last_name ON employees(last_name) for query optimization
• Check constraints validating data on write with CHECK (price > 0)
• Transactions supporting BEGIN, COMMIT, and ROLLBACK semantics

Advanced Database Objects

Beyond basic CRUD operations, Dolt supports complex database programming constructs implemented in go/libraries/doltcore/sqle/:

• Triggers executing procedural code on data modifications: CREATE TRIGGER upd_ts BEFORE UPDATE ON employees FOR EACH ROW SET NEW.updated_at = NOW()
• Stored procedures defining reusable SQL logic: CREATE PROCEDURE inc_salary(IN inc INT) BEGIN UPDATE employees SET salary = salary + inc; END

Dolt-Specific SQL Capabilities

Version Control System Tables

Dolt exposes repository metadata through read-only system tables defined in go/libraries/doltcore/doltdb/AGENT.md (lines 291-307). These tables allow SQL-based inspection of version control state:

• dolt_log – Linear commit history showing hash, author, date, and message
• dolt_status – Staged versus unstaged changes in the working set
• dolt_branches – List of branches with head commit hashes
• dolt_diff_<table> – Row-level differences for a specific table between revisions
• dolt_schema_diff – Schema-level changes between revisions
• dolt_conflicts_<table> – Merge conflict details requiring resolution
• dolt_commits – Full commit metadata including parent relationships

Version Control Stored Procedures

Write-side version control operations mirror Git CLI commands through stored procedures documented in AGENT.md (lines 321-334):

CALL dolt_add('employees');                          -- Stage tables
CALL dolt_commit('-m', 'Add employees table');       -- Create commit
CALL dolt_checkout('feature-branch');                -- Switch branch
CALL dolt_merge('feature-branch');                   -- Merge branches
CALL dolt_branch('new-branch');                      -- Create branch
CALL dolt_reset('--hard');                           -- Discard changes
CALL dolt_revert('abc123');                          -- Revert specific commit
CALL dolt_undrop('employees');                       -- Restore dropped table

Time-Travel Queries

Dolt supports AS OF syntax for querying historical data states without restoring backups. According to README.md (line 614), you can query any commit, branch, or timestamp:

-- Query specific branch
SELECT * FROM employees AS OF 'main';

-- Query specific timestamp
SELECT * FROM employees AS OF '2022-01-15 12:00:00';

-- Query specific commit hash
SELECT * FROM employees AS OF 'feature-branch';

Branch-Scoped Sessions

Each SQL client connection maintains its own active branch context. As documented in AGENT.md (lines 48-52), when running dolt sql-server, a client's branch remains isolated unless explicitly changed via CALL dolt_checkout(). This enables multiple concurrent sessions operating on different branches simultaneously without interference.

Practical Implementation Examples

Initialize Repository and Create Schema

# Initialize repository

dolt init

# Start SQL server

dolt sql-server &

-- Connect via MySQL client
CREATE DATABASE getting_started;
USE getting_started;

CREATE TABLE employees (
    id INT PRIMARY KEY,
    last_name VARCHAR(255),
    first_name VARCHAR(255)
);

Version Control Workflow

-- Stage and commit
CALL dolt_add('employees');
CALL dolt_commit('-m', 'Add employees table');

-- View history
SELECT * FROM dolt_log;

Branching and Merging

-- Create feature branch
CALL dolt_checkout('-b', 'feature/rename');

-- Modify schema
ALTER TABLE employees CHANGE last_name surname VARCHAR(255);

-- Commit changes
CALL dolt_add('employees');
CALL dolt_commit('-m', 'Rename last_name to surname');

-- Merge to main
CALL dolt_checkout('main');
CALL dolt_merge('feature/rename');

Data Validation Testing

Dolt includes a testing framework accessible via SQL:

-- Define test cases
INSERT INTO dolt_tests VALUES (
  'test_user_count', 
  'validation',
  'SELECT COUNT(*) FROM users;', 
  'row_count', 
  '>', 
  '0'
);

-- Execute tests
SELECT * FROM dolt_test_run();

Key Source Files and Architecture

Understanding Dolt's implementation requires familiarity with these critical paths:

File	Role
README.md	High-level documentation, feature overview, and getting-started guide
go/libraries/doltcore/doltdb/AGENT.md	Detailed SQL workflow documentation, system tables, and stored procedures
go/libraries/doltcore/sqle/	Core SQL engine implementation handling MySQL compatibility
go/libraries/doltcore/doltdb/	Underlying version-control data structures (commits, branches, diffs)
docker/serverREADME.md	Docker deployment configuration for dolt sql-server
integration-tests/	Comprehensive test suite for SQL features and version control operations

Summary

Dolt SQL features and capabilities merge traditional relational database functionality with distributed version control:

• Full MySQL compatibility supporting foreign keys, indexes, triggers, stored procedures, and transactions as implemented in go/libraries/doltcore/sqle/
• Git-style version control exposed through system tables (dolt_log, dolt_diff_*, dolt_branches) and stored procedures (dolt_commit, dolt_merge, dolt_checkout)
• Time-travel queries using AS OF syntax to access historical data states without restoration
• Branch-scoped sessions enabling concurrent connections to operate on isolated branches within the same database instance
• Integrated testing framework allowing data validation tests to be defined and executed via SQL

Frequently Asked Questions

What SQL standards does Dolt support?

Dolt implements MySQL 5.7 and 8.x syntax, supporting standard SQL features including JOIN operations, window functions, foreign key constraints, secondary indexes, triggers, check constraints, and stored procedures. The SQL engine is implemented in go/libraries/doltcore/sqle/ and maintains wire-protocol compatibility with MySQL clients.

How do I perform Git operations like commit and branch in Dolt SQL?

Dolt exposes version control operations as stored procedures callable from any SQL client. Use CALL dolt_add('table_name') to stage changes, CALL dolt_commit('-m', 'message') to create commits, and CALL dolt_checkout('branch') to switch branches. These procedures are documented in go/libraries/doltcore/doltdb/AGENT.md and provide atomic operations that persist version control metadata alongside your data.

Can I query historical data in Dolt without restoring backups?

Yes, Dolt supports time-travel queries using the AS OF clause, allowing you to query any commit, branch, or timestamp directly. For example, SELECT * FROM employees AS OF 'HEAD~1' retrieves the previous commit state, while SELECT * FROM employees AS OF '2022-01-15' queries a specific point in time. This functionality eliminates the need for manual backup restoration when analyzing historical data states.

What are Dolt system tables and how do I use them?

Dolt system tables are read-only views into the version control metadata, prefixed with dolt_ and queryable via standard SQL. Key tables include dolt_log for commit history, dolt_diff_<table> for row-level changes, dolt_branches for branch metadata, and dolt_conflicts_<table> for merge conflict resolution. These tables are defined in go/libraries/doltcore/doltdb/AGENT.md and enable programmatic inspection of repository state without leaving the SQL environment.

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