dolt Documentation

File: README.md Copy Chapter

Dolt is Git for Data!

Dolt is a SQL database that you can fork, clone, branch, merge, push
and pull just like a Git repository.

Connect to Dolt just like any MySQL database to read or modify schema
and data. Version control functionality is exposed in SQL via system
tables, functions, and procedures.

Or, use the Git-like command line interface to import CSV files, commit
your changes, push them to a remote, or merge your teammate's changes.
All the commands you know for Git work exactly the same for Dolt.

Git versions files. Dolt versions tables. It's like Git and MySQL had a
baby.

We also built DoltHub, a place to share
Dolt databases. We host public data for free. If you want to host
your own version of DoltHub, we have DoltLab.
If you want us to run a Dolt server for you, we have Hosted Dolt.

Prefer Postgres instead of MySQL? Try Doltgres, now
in its Beta release.

Join us on Discord to say hi and
ask questions, or check out our roadmap
to see what we're building next.

Video Introduction

What's it for?

Lots of things! Dolt is a generally useful tool with countless
applications. But if you want some ideas, here's how people are using
it so far.

Dolt powers Beads and
Gas Town. It's the best database
for agent memory, especially as you move up the ladder to multi-agent and
multi-machine workflows.

Dolt CLI

The dolt CLI has the same commands as git, with some extras.

/* Detailed source-code truncated for AI context efficiency. */

Installation

Dolt is a single ~103 megabyte program.

dolt $ du -h /Users/timsehn/go/bin/dolt
103M	/Users/timsehn/go/bin/dolt

It's really easy to install. Download it and put it on your PATH.
We have a bunch of ways to make this even easier for most platforms.

From Latest Release Copy Chapter

To install on Linux or Mac based systems run this command in your
terminal:

sudo bash -c 'curl -L https://github.com/dolthub/dolt/releases/latest/download/install.sh | bash'

This will download the latest dolt release and put it in
/usr/local/bin/, which is probably on your $PATH.

The install script needs sudo in order to put dolt in /usr/local/bin. If you don't have root
privileges or aren't comfortable running a script with them, you can download the dolt binary
for your platform from the latest release, unzip it,
and put the binary somewhere on your $PATH.

Linux Copy Section

Arch Linux

Dolt is packaged in the official repositories for Arch Linux.

pacman -S dolt

Mac Copy Section

Homebrew

Dolt is on Homebrew, updated every release.

brew install dolt

MacPorts

On macOS, Dolt can also be installed via a community-managed port via MacPorts:

sudo port install dolt

Windows Copy Section

Download the latest Microsoft Installer (.msi file) in
releases and run
it.

For information on running on Windows, see here.

Chocolatey

You can install dolt using Chocolatey:

choco install dolt

Docker

There are following official Docker images for Dolt:

• dolthub/dolt for running Dolt
  as CLI tool.
• dolthub/dolt-sql-server for running Dolt in server mode.

From Source Copy Chapter

Make sure you have Go installed, and that go is in your path. Dolt has a dependency on cgo, so you will need a working C compiler and toolchain as well.

Clone this repository and cd into the go directory. Then run:

go install ./cmd/dolt

The output will be in $GOPATH/bin, which defaults to ~/go/bin. To test your build, try:

~/go/bin/dolt version

Configuration

Verify that your installation has succeeded by running dolt in your
terminal.

$ dolt
Valid commands for dolt are
[...]

Configure dolt with your user name and email, which you'll need to
create commits. The commands work exactly the same as git.

$ dolt config --global --add user.email [email protected]
$ dolt config --global --add user.name "YOUR NAME"

Getting started

Navigate to the directory where you would like your data stored Copy Chapter

Dolt needs a place to store your databases. I'm going to put my databases in ~/dolt.

% cd ~
% mkdir dolt
% cd dolt

Any databases you create will be stored in this directory. So, for this example, a directory named getting_started will be created here once you run create database getting_started. Navigating to ~/dolt/getting_started will allow you to access this database using the Dolt command line.

NOTE: For this example, the getting_started directory will be created after you run create database getting_started; in a SQL shell in the Create a schema section. Don't do anything except make the directory and navigate to it just yet.

Start a MySQL-compatible database server Copy Chapter

Dolt ships with a MySQL compatible database server built in. To start it you use the command dolt sql-server. Running this command starts the server on port 3306.

dolt sql-server
Starting server with Config HP="localhost:3306"|T="28800000"|R="false"|L="info"

Your terminal will just hang there. This means the server is running. Any errors will be printed in this terminal. Just leave it there and open a new terminal.

Connect with a MySQL client (up to version 8.4) Copy Chapter

In the new terminal, we will now connect to the running database server using a client. Dolt also ships with a MySQL compatible client.

% dolt -u root -p "" sql
# Welcome to the Dolt MySQL client.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
mysql>

In the other terminal where you ran dolt sql-server, you'll see the following log line.

2022-06-06T13:14:32-07:00 INFO [conn 1] NewConnection {DisableClientMultiStatements=false}

You are connected!

While we're here let's grab a copy of MySQL so we can connect with that client. Head over to the MySQL Getting Started documentation and install MySQL on your machine. I used Homebrew to install MySQL on my Mac: brew install [email protected]. Alternatively, you can install only the client component by running brew install [email protected].

NOTE: Make sure you install a MySQL 8.4 release. MySQL 8.4 is the current Long Term Support (LTS) release, meaning this is the stable and supported version of MySQL. MySQL 9.0 is also available, but is an "innovation" release, meaning it has more recent changes and features, but may not be as stable as the LTS release. The 9.0 release changes authentication support and isn't able to connect to a Dolt SQL server by default. You can install MySQL 8.4 with Homebrew by running brew install [email protected]. If you do want to use MySQL-9.0, read our post on how to configure Dolt for caching_sha2_password authentication.

MySQL comes with a MySQL server called mysqld and a MySQL client called mysql. You're only interested in the client. After following the instructions from MySQL's documentation, make sure you have a copy of the mysql client on your path:

% mysql --version
mysql  Ver 8.0.29 for macos12.2 on x86_64 (Homebrew)

Now, to connect the mysql client to Dolt, you are going to force the MySQL client through the TCP interface by passing in a host and port. The default is the socket interface which Dolt supports, but is only available on localhost. So, it's better to show off the TCP interface. The MySQL client also requires you specify a user, in this case root.

% mysql --host 127.0.0.1 --port 3306 -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9-Vitess 

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Again, to ensure the client actually connected, you should see the following in the dolt sql-server terminal

2022-06-06T13:26:55-07:00 INFO [conn 2] NewConnection {DisableClientMultiStatements=false}

As you can see, Dolt supports any MySQL-compatible client. Dolt ships with a client but you can use any MySQL client, like the one that comes with MySQL.

Create a schema Copy Chapter

Now we're actually ready to do something interesting. I'll stay in the mysql client and execute the following SQL statements to create a database called getting_started. The getting_started database will have three tables: employees, teams, and employees_teams.

mysql> create database getting_started;
Query OK, 1 row affected (0.04 sec)

mysql> use getting_started;
Database changed
mysql> create table employees (
    id int, 
    last_name varchar(255), 
    first_name varchar(255), 
    primary key(id));
Query OK, 0 rows affected (0.01 sec)

mysql> create table teams (
    id int, 
    team_name varchar(255), 
    primary key(id)); 
Query OK, 0 rows affected (0.00 sec)

mysql> create table employees_teams(
    team_id int, 
    employee_id int, 
    primary key(team_id, employee_id), 
    foreign key (team_id) references teams(id), 
    foreign key (employee_id) references employees(id));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+---------------------------+
| Tables_in_getting_started |
+---------------------------+
| employees                 |
| employees_teams           |
| teams                     |
+---------------------------+
3 rows in set (0.00 sec)

Dolt supports foreign keys, secondary indexes, triggers, check constraints, and stored procedures. It's a modern, feature-rich SQL database.

Make a Dolt commit Copy Chapter

It's time to use your first Dolt feature. We're going to make a Dolt commit. A Dolt commit allows you to time travel and see lineage. Make a Dolt commit whenever you want to restore or compare to this point in time.

Dolt exposes version control functionality through a Git-style interface. On the command line, Dolt commands map exactly to their Git equivalent with the targets being tables instead of files. In SQL, Dolt exposes version control read operations as system tables and version control write operations as stored procedures.

The naming of the system tables and stored procedures follows the dolt_<command> pattern. So dolt add on the CLI becomes dolt_add as a stored procedure. Passing options also follows the command line model. For instance, to specify tables to add, send the table names in as options to the dolt_add procedure. For named arguments like sending a message into the dolt_commit command use two arguments in sequence like ('-m', 'This is a message'). If you know Git, the version control procedures and system tables should feel familiar.

So, we add and commit our new schema like so.

mysql> call dolt_add('teams', 'employees', 'employees_teams');
+--------+
| status |
+--------+
|      0 |
+--------+
1 row in set (0.03 sec)

mysql> call dolt_commit('-m', 'Created initial schema');
+----------------------------------+
| hash                             |
+----------------------------------+
| ne182jemgrlm8jnjmoubfqsstlfi1s98 |
+----------------------------------+
1 row in set (0.02 sec)

mysql> select * from dolt_log;
+----------------------------------+-----------+-----------------+-------------------------+----------------------------+
| commit_hash                      | committer | email           | date                    | message                    |
+----------------------------------+-----------+-----------------+-------------------------+----------------------------+
| ne182jemgrlm8jnjmoubfqsstlfi1s98 | Tim Sehn  | [email protected] | 2022-06-07 16:35:49.277 | Created initial schema     |
| vluuhvd0bn59598utedt77ed9q5okbcb | Tim Sehn  | [email protected] | 2022-06-07 16:33:59.531 | Initialize data repository |
+----------------------------------+-----------+-----------------+-------------------------+----------------------------+
2 rows in set (0.01 sec)

There you have it. Your schema is created and you have a Dolt commit tracking the creation, as seen in the dolt_log system table.

Note, a Dolt commit is different than a standard SQL transaction COMMIT. In this case, I am running the database with AUTOCOMMIT on, so each SQL statement is automatically generating a transaction COMMIT. If you want system to generate a Dolt commit for every transaction use the system variable, @@dolt_transaction_commit.

Insert some data Copy Chapter

Now, I'm going to populate the database with a few employees here at DoltHub. Then, I'll assign the employees to two teams: engineering and sales. The CEO wears many hats at a start up so he'll be assigned to multiple teams.

mysql> insert into employees values 
    (0, 'Sehn', 'Tim'), 
    (1, 'Hendriks', 'Brian'), 
    (2, 'Son','Aaron'), 
    (3, 'Fitzgerald', 'Brian');
Query OK, 4 rows affected (0.01 sec)

mysql> select * from employees where first_name='Brian';
+------+------------+------------+
| id   | last_name  | first_name |
+------+------------+------------+
|    1 | Hendriks   | Brian      |
|    3 | Fitzgerald | Brian      |
+------+------------+------------+
2 rows in set (0.00 sec)

mysql> insert into teams values 
    (0, 'Engineering'), 
    (1, 'Sales');
Query OK, 2 rows affected (0.00 sec)

mysql> insert into employees_teams values 
    (0,0), 
    (1,0), 
    (2,0), 
    (0,1), 
    (3,1);
ERROR 1452 (HY000): cannot add or update a child row - Foreign key violation on fk: `rv9ek7ft`, table: `employees_teams`, referenced table: `teams`, key: `[2]`

Oops, I violated a constraint. It looks like I created the table with teams before employees. You should always specify your columns when you insert, not rely on natural ordering. Serves me right! Dolt comes with the full power of a modern SQL relational database to ensure data integrity.

mysql> insert into employees_teams(employee_id, team_id) values 
    (0,0), 
    (1,0), 
    (2,0), 
    (0,1), 
    (3,1);
Query OK, 5 rows affected (0.01 sec)

mysql> select first_name, last_name, team_name from employees 
    join employees_teams on (employees.id=employees_teams.employee_id) 
    join teams on (teams.id=employees_teams.team_id) 
    where team_name='Engineering';
+------------+-----------+-------------+
| first_name | last_name | team_name   |
+------------+-----------+-------------+
| Tim        | Sehn      | Engineering |
| Brian      | Hendriks  | Engineering |
| Aaron      | Son       | Engineering |
+------------+-----------+-------------+
3 rows in set (0.00 sec)

Looks like everything is inserted and correct. I was able to list the members of the engineering team using that three table JOIN. Dolt supports up to twelve table JOINs. Again, Dolt is a modern SQL relational database paired with Git-style version control.

Examine the diff Copy Chapter

Now, what if you want to see what changed in your working set before you make a commit? You use the dolt_status and dolt_diff_ system tables.

/* Detailed source-code truncated for AI context efficiency. */

As you can see from the diff I've added the correct values to the employees table. The values were previously NULL and now they are populated.

Let's finish off with another Dolt commit this time adding all effected tables using -am.

mysql> call dolt_commit('-am', 'Populated tables with data');
+----------------------------------+
| hash                             |
+----------------------------------+
| 13qfqa5rojq18j84d1n2htjkm6fletg4 |
+----------------------------------+
1 row in set (0.02 sec)

You can inspect the log using dolt_log and see which tables changed in each commit using an unscoped dolt_diff. Unscoped dolt_diff tells you whether schema, data, or both changed in that particular commit for the table.

/* Detailed source-code truncated for AI context efficiency. */

Oh no! I made a mistake. Copy Chapter

Dolt supports undoing changes via call dolt_reset(). Let's imagine I accidentally drop a table.

mysql> drop table employees_teams;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+---------------------------+
| Tables_in_getting_started |
+---------------------------+
| employees                 |
| teams                     |
+---------------------------+
2 rows in set (0.00 sec)

In a traditional database, this could be disastrous. In Dolt, you're one command away from getting your table back.

mysql> call dolt_reset('--hard');
+--------+
| status |
+--------+
|      0 |
+--------+
1 row in set (0.01 sec)

mysql> show tables;
+---------------------------+
| Tables_in_getting_started |
+---------------------------+
| employees                 |
| employees_teams           |
| teams                     |
+---------------------------+
3 rows in set (0.01 sec)

Dolt makes operating databases less error prone. You can always back out changes you have in progress or rewind to a known good state. You also have the ability to undo specific commits using dolt_revert(). Even if you accidentally run drop database on the wrong database, Dolt lets you undo that by calling the dolt_undrop() stored procedure.

See the data in a SQL Workbench Copy Chapter

Hate the command line? Let's use Tableplus to make some modifications. Tableplus is a free SQL Workbench. Follow the installation instructions from their website.

Now, to connect you must select MySQL as the connection type. Then enter a name for your connection, getting_started as your database, and root as your user.

Click connect and you'll be presented with a familiar database workbench GUI.

Make changes on a branch Copy Chapter

To make changes on a branch, I use the dolt_checkout() stored procedure. Using the -b option creates a branch, just like in Git.

Tableplus gives me the ability to enter a multiple line SQL script on the SQL tab. I entered the following SQL to checkout a branch, update, insert, delete, and finally Dolt commit my changes.

call dolt_checkout('-b','modifications');
update employees SET first_name='Timothy' where first_name='Tim';
insert INTO employees (id, first_name, last_name) values (4,'Daylon', 'Wilkins');
insert into employees_teams(team_id, employee_id) values (0,4);
delete from employees_teams where employee_id=0 and team_id=1;
call dolt_commit('-am', 'Modifications on a branch');

Here's the result in Tableplus.

Back in my terminal, I cannot see the table modifications made in Tableplus because they happened on a different branch than the one I have checked out in my session.

mysql> select * from dolt_branches;
+---------------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
| name          | hash                             | latest_committer | latest_committer_email | latest_commit_date      | latest_commit_message      |
+---------------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
| main          | 13qfqa5rojq18j84d1n2htjkm6fletg4 | Tim Sehn         | [email protected]        | 2022-06-07 16:39:32.066 | Populated tables with data |
| modifications | uhkv57j4bp2v16vcnmev9lshgkqq8ppb | Tim Sehn         | [email protected]        | 2022-06-07 16:41:49.847 | Modifications on a branch  |
+---------------+----------------------------------+------------------+------------------------+-------------------------+----------------------------+
2 rows in set (0.00 sec)

mysql> select active_branch();
+-----------------+
| active_branch() |
+-----------------+
| main            |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from employees;
+------+------------+------------+
| id   | last_name  | first_name |
+------+------------+------------+
|    0 | Sehn       | Tim        |
|    1 | Hendriks   | Brian      |
|    2 | Son        | Aaron      |
|    3 | Fitzgerald | Brian      |
+------+------------+------------+
4 rows in set (0.00 sec)

I can query the branch no matter what I have checked out using SQL as of syntax.

mysql> select * from employees as of 'modifications';
+------+------------+------------+
| id   | last_name  | first_name |
+------+------------+------------+
|    0 | Sehn       | Timothy    |
|    1 | Hendriks   | Brian      |
|    2 | Son        | Aaron      |
|    3 | Fitzgerald | Brian      |
|    4 | Wilkins    | Daylon     |
+------+------------+------------+
5 rows in set (0.01 sec)

If I'd like to see the diff between the two branches, I can use the dolt_diff() table function. It takes two branches and the table name as arguments.

mysql> select * from dolt_diff('main', 'modifications', 'employees');
+--------------+---------------+-------+---------------+-------------------------+----------------+-----------------+---------+-------------+-------------------------+-----------+
| to_last_name | to_first_name | to_id | to_commit     | to_commit_date          | from_last_name | from_first_name | from_id | from_commit | from_commit_date        | diff_type |
+--------------+---------------+-------+---------------+-------------------------+----------------+-----------------+---------+-------------+-------------------------+-----------+
| Sehn         | Timothy       |     0 | modifications | 2022-06-07 16:41:49.847 | Sehn           | Tim             |       0 | main        | 2022-06-07 16:39:32.066 | modified  |
| Wilkins      | Daylon        |     4 | modifications | 2022-06-07 16:41:49.847 | NULL           | NULL            |    NULL | main        | 2022-06-07 16:39:32.066 | added     |
+--------------+---------------+-------+---------------+-------------------------+----------------+-----------------+---------+-------------+-------------------------+-----------+
2 rows in set (0.00 sec)

As you can see, you have the full power of Git-style branches and diffs in a SQL database with Dolt.

Make a schema change on another branch Copy Chapter

I can also make schema changes on branches for isolated testing of new schema. I'm going to add a start_date column on a new branch and populate it.

mysql> call dolt_checkout('-b', 'schema_changes');
+--------+
| status |
+--------+
|      0 |
+--------+
1 row in set (0.01 sec)

mysql> alter table employees add column start_date date;
Query OK, 0 rows affected (0.02 sec)

mysql> update employees set start_date='2018-09-08';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> update employees set start_date='2021-04-19' where last_name='Fitzgerald';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employees;
+------+------------+------------+------------+
| id   | last_name  | first_name | start_date |
+------+------------+------------+------------+
|    0 | Sehn       | Tim        | 2018-09-08 |
|    1 | Hendriks   | Brian      | 2018-09-08 |
|    2 | Son        | Aaron      | 2018-09-08 |
|    3 | Fitzgerald | Brian      | 2021-04-19 |
+------+------------+------------+------------+
4 rows in set (0.00 sec)

mysql> call dolt_commit('-am', 'Added start_date column to employees');
+----------------------------------+
| hash                             |
+----------------------------------+
| pg3nfi0j1dpc5pf1rfgckpmlteaufdrt |
+----------------------------------+
1 row in set (0.01 sec)

Changing schema on a branch gives you a new method for doing isolated integration testing of new schema changes.

Merge it all together Copy Chapter

Let's assume all the testing of the new schema on the schema_changes branch and data on the modifications branch completed flawlessly. It's time to merge all our edits together onto main. This is done using the dolt_merge stored procedure.

mysql> call dolt_checkout('main');
+--------+
| status |
+--------+
|      0 |
+--------+
1 row in set (0.01 sec)

mysql> select * from dolt_status;
Empty set (0.00 sec)

mysql> call dolt_merge('schema_changes');
+--------------+
| no_conflicts |
+--------------+
|            1 |
+--------------+
1 row in set (0.01 sec)

mysql> select * from employees;
+------+------------+------------+------------+
| id   | last_name  | first_name | start_date |
+------+------------+------------+------------+
|    0 | Sehn       | Tim        | 2018-09-08 |
|    1 | Hendriks   | Brian      | 2018-09-08 |
|    2 | Son        | Aaron      | 2018-09-08 |
|    3 | Fitzgerald | Brian      | 2021-04-19 |
+------+------------+------------+------------+
4 rows in set (0.00 sec)

Schema change successful. We now have start dates. Data changes are next.

mysql> call dolt_merge('modifications');
+--------------+
| no_conflicts |
+--------------+
|            1 |
+--------------+
1 row in set (0.02 sec)

mysql> select * from employees;
+------+------------+------------+------------+
| id   | last_name  | first_name | start_date |
+------+------------+------------+------------+
|    0 | Sehn       | Timothy    | 2018-09-08 |
|    1 | Hendriks   | Brian      | 2018-09-08 |
|    2 | Son        | Aaron      | 2018-09-08 |
|    3 | Fitzgerald | Brian      | 2021-04-19 |
|    4 | Wilkins    | Daylon     | NULL       |
+------+------------+------------+------------+
5 rows in set (0.00 sec)

Data changes successful as well. As you can see, I am now "Timothy" instead of "Tim", Daylon is added, and we all have start dates except for Daylon who was added on a different branch.

mysql> select first_name, last_name, team_name from employees 
    join employees_teams on (employees.id=employees_teams.employee_id) 
    join teams on (teams.id=employees_teams.team_id) 
    where team_name='Sales';
+------------+------------+-----------+
| first_name | last_name  | team_name |
+------------+------------+-----------+
| Brian      | Fitzgerald | Sales     |
+------------+------------+-----------+
1 row in set (0.01 sec)

I'm also gone from the Sales Team. Engineering is life.

I have to commit all my changes because the last merge was not a fast-forward merge.

mysql> call dolt_commit('-m', 'Merged all branches');
+----------------------------------+
| hash                             |
+----------------------------------+
| vn9b0qcematsj2f6ka0hfoflhr5s6p0b |
+----------------------------------+
1 row in set (0.01 sec)

mysql> select * from dolt_log;
+----------------------------------+-----------+-----------------+-------------------------+--------------------------------------+
| commit_hash                      | committer | email           | date                    | message                              |
+----------------------------------+-----------+-----------------+-------------------------+--------------------------------------+
| vn9b0qcematsj2f6ka0hfoflhr5s6p0b | Tim Sehn  | [email protected] | 2022-06-07 17:10:02.07  | Merged all branches                  |
| pg3nfi0j1dpc5pf1rfgckpmlteaufdrt | Tim Sehn  | [email protected] | 2022-06-07 16:44:37.513 | Added start_date column to employees |
| uhkv57j4bp2v16vcnmev9lshgkqq8ppb | Tim Sehn  | [email protected] | 2022-06-07 16:41:49.847 | Modifications on a branch            |
| 13qfqa5rojq18j84d1n2htjkm6fletg4 | Tim Sehn  | [email protected] | 2022-06-07 16:39:32.066 | Populated tables with data           |
| ne182jemgrlm8jnjmoubfqsstlfi1s98 | Tim Sehn  | [email protected] | 2022-06-07 16:35:49.277 | Created initial schema               |
| vluuhvd0bn59598utedt77ed9q5okbcb | Tim Sehn  | [email protected] | 2022-06-07 16:33:59.531 | Initialize data repository           |
+----------------------------------+-----------+-----------------+-------------------------+--------------------------------------+
6 rows in set (0.00 sec)

Now, we have a database with all the schema and data changes merged and ready for use.

Audit Cell Lineage Copy Chapter

Which commit changed my first name? With Dolt you have lineage for every cell in your database. Let's use the dolt_history_ and dolt_diff_ to explore the lineage features in Dolt.

dolt_history_ shows you the state of the row at every commit.

mysql> select * from dolt_history_employees where id=0 order by commit_date;
+------+-----------+------------+------------+----------------------------------+-----------+-------------------------+
| id   | last_name | first_name | start_date | commit_hash                      | committer | commit_date             |
+------+-----------+------------+------------+----------------------------------+-----------+-------------------------+
|    0 | Sehn      | Tim        | NULL       | 13qfqa5rojq18j84d1n2htjkm6fletg4 | Tim Sehn  | 2022-06-07 16:39:32.066 |
|    0 | Sehn      | Timothy    | NULL       | uhkv57j4bp2v16vcnmev9lshgkqq8ppb | Tim Sehn  | 2022-06-07 16:41:49.847 |
|    0 | Sehn      | Tim        | 2018-09-08 | pg3nfi0j1dpc5pf1rfgckpmlteaufdrt | Tim Sehn  | 2022-06-07 16:44:37.513 |
|    0 | Sehn      | Timothy    | 2018-09-08 | vn9b0qcematsj2f6ka0hfoflhr5s6p0b | Tim Sehn  | 2022-06-07 17:10:02.07  |
+------+-----------+------------+------------+----------------------------------+-----------+-------------------------+
4 rows in set (0.00 sec)

dolt_diff_ allows you to filter the history down to only commits when the cell in question changed. In this case, I'm interested in the commits that are changing my first name. Note, there are two commits that changed my name because one is the original change and the second is the merge commit.

mysql> select to_commit,from_first_name,to_first_name from dolt_diff_employees 
    where (from_id=0 or to_id=0) and (from_first_name <> to_first_name or from_first_name is NULL)
    order by to_commit_date;
+----------------------------------+-----------------+---------------+
| to_commit                        | from_first_name | to_first_name |
+----------------------------------+-----------------+---------------+
| 13qfqa5rojq18j84d1n2htjkm6fletg4 | NULL            | Tim           |
| uhkv57j4bp2v16vcnmev9lshgkqq8ppb | Tim             | Timothy       |
| vn9b0qcematsj2f6ka0hfoflhr5s6p0b | Tim             | Timothy       |
+----------------------------------+-----------------+---------------+
3 rows in set (0.01 sec)

Dolt provides powerful data audit capabilities down to individual cells. When, how, and why has each cell in your database changed over time?

Additional Reading

Head over to our documentation now that you have a feel for Dolt. You can also read about what we've been working on in our blog.

Security Policy

Dolt's current security policy is maintained in this repository. Please follow the disclosure instructions there. Please do not initially report security issues in this repository's public GitHub issues.

Credits and License

Dolt relies heavily on open source code and ideas from the
Noms project. We are very
thankful to the Noms team for making this code freely available,
without which we would not have been able to build Dolt so rapidly.

Dolt is licensed under the Apache License, Version 2.0. See
LICENSE for
details.

File: docker/README.md Copy Chapter

Dolt is Git for Data!

Dolt is a SQL database that you can fork, clone, branch, merge, push
and pull just like a Git repository. Connect to Dolt just like any
MySQL database to run queries or update the data using SQL
commands. Use the command line interface to import CSV files, commit
your changes, push them to a remote, or merge your teammate's changes.

All the commands you know for Git work exactly the same for Dolt. Git
versions files, Dolt versions tables. It's like Git and MySQL had a
baby.

We also built DoltHub, a place to share
Dolt databases. We host public data for free. If you want to host
your own version of DoltHub, we have DoltLab. If you want us to run a Dolt server for you, we have Hosted Dolt.

Join us on Discord to say hi and
ask questions, or check out our roadmap
to see what we're building next.

What's it for? Copy Chapter

Lots of things! Dolt is a generally useful tool with countless
applications. But if you want some ideas, here's how people are using
it so far.

Learn more about Dolt use cases, configuration and guides to use dolt on our documentation page.

How to use this image

This image is for Dolt CLI, which has the same commands as git, with some extras. Running this image without any
arguments is equivalent to running dolt command locally.

/* Detailed source-code truncated for AI context efficiency. */

This image is useful for creating custom Docker Image using this image as base image.

File: go/performance/utils/dolt_builder/README.md Copy Chapter

Dolt builder is a tool for more easily installing dolt binaries.

It takes Dolt commit shas or tags as arguments
and builds corresponding binaries to a path specified
by $DOLT_BIN

If $DOLT_BIN is not set ./doltBin will be used

(Optional) set $DEBUG=true to run in debug mode

Supply additional Go build tags with -tags (before the revision arguments):

dolt-builder -tags tag_one,tag_two v1.33.0
dolt-builder -tags tag_one -profile /absolute/path/cpu.pprof v1.33.0

Tags apply to every requested revision. A PGO profile can only be used when
building a single revision. Output paths remain $DOLT_BIN/<revision>/dolt, so
building the same revision with different tags replaces the previous binary.

Go consumers can use RunWithBuildTags:

err := builder.RunWithBuildTags(ctx, []string{version}, profilePath, []string{"tag_one", "tag_two"})

The existing Run(ctx, revisions, profilePath) API builds without additional tags.

Example usage:

$ dolt-builder dccba46 4bad226
$ dolt version 0.1
$ dolt version 0.2

$ dolt-builder v0.19.0 v0.22.6
$ dolt version 0.19.0
$ dolt version v0.22.6

File: go/performance/utils/benchmark_runner/README.md Copy Chapter

Sysbench runner is a tool for running sysbench tests against sql servers. Custom sysbench lua scripts used
for benchmarking Dolt are here.

Configuration:

{
  "Runs": 1,
  "DebugMode": false,
  "Servers": "[{...}]",
  "TestOptions": [""],
  "Tests": "[{...}]"
}

Runs number of times to run all tests per server, default 1 (Optional)

DebugMode logs more output from various commands. (Optional)

Servers list of servers to test against. See Server definitions below. (Required)

TestOptions list of sysbench test options to supply to all tests (Optional)

Tests the sysbench tests to run. See Test definitions below. (Optional)

If no tests are provided,
the following default tests will be run:

oltp_read_only
oltp_insert
oltp_point_select
select_random_points
select_random_ranges
oltp_write_only
oltp_read_write
oltp_update_index
oltp_update_non_index

Server is a server to test against.

{
  "Host": "",
  "Port": 0,
  "Server": "",
  "Version": "",
  "ResultsFormat": "",
  "ServerExec": "",
  "ServerArgs": [""],
  "ConnectionProtocol": "",
  "Socket": ""
}

Host is the server host. (Required)

Port is the server port. Defaults to 3306 for dolt and mysql Servers. (Optional)

Version is the server version. (Required)

ResultsFormat is the format the results should be written in. Only json and csv are supported. (Required)

ServerExec is the path to a server binary (Required)

ServerArgs are the args used to start the server. Will be appended to command dolt sql-server for dolt server or mysqld --user=mysql for mysql server. (Optional)

ConnectionProtocol is the protocol for connecting to mysql, either "unix" or "tcp" (Required for mysql)

Socket is the path to the mysql socket (Required for mysql with unix protocol)

Test is a sysbench test or lua script.

{
  "Name": "",
  "N": 1,
  "FromScript": false,
  "Options": [""]
}

Name is the test name or lua script. (Required)

N number of times to repeat this test, default is 1 (Optional)

FromScript indicates if this test is from a lua script, defaults to false (Optional)

Options are additional sysbench test options. These will be provided to sysbench in the form:

sysbench [options]... [testname] [command]

Note: Be sure that all mysql processes are off when running this locally.

TPCC

TPCC runner is a tool for running TPCC tests against sql servers. These tests run against the
Percona Labs repo here.

Note to this run this locally you need to have the TPCC repo cloned. The ScriptDir variable should then be linked
to the path of the cloned repo.

Configuration:

{
  "Servers": "[...]",
  "ScriptDir":"/Users/vinairachakonda/go/src/dolthub/sysbench-tpcc",
  "ScaleFactors": [1]
}

Servers: The server definitions to run the benchmark against. Accepts Dolt and MySQL configuratiosn.

ScriptDir: The directory of the TPCC testing scripts

ScaleFactors: The number of warehouse to be generated in the test case.

NomsBinFormat: The NomsBinFormat to use for this benchmark.

Note that this configuration is still incomplete for the amount of the variable TPCC varies. This intentional as we
want expose small amounts of independent variables until Dolt gets more robust. See config.go to get a breakdown of all the
variables TPCC varies.

File: go/performance/sysbench/README.md Copy Chapter

Sysbench

MySQL tests expect local server (ex: mysqld --port 3309 --local-infile=1 --socket=/tmp/mysqld2.sock).

Interrupting a test midway can create cause "table already exists" error.

File: go/performance/import_benchmarker/README.md Copy Chapter

Import Benchmarking Copy Chapter

Benchmark different import workflows expressed as yaml files.

Unit tests in import_test.go are not suitable for reporting performance
comparisons.

Usage Copy Chapter

Sample:

go build \
  github.com/dolthub/dolt/go/performance/import_benchmarker/cmd \
  -test testdata/shuffle.yaml

Requirements:

Tests that use dolt require a dolt binary in PATH for performance comparisons.

Tests with an external-server configuration are expected to be available
from the host machine on startup.

Example mysqld server on the host OS, assuming an initialized datadir
and pre-existing database:

mysqld --port 3308 --local-infile=1 --socket=/tmp/mysqld2.sock

Example mysql server docker-compose.yml config:

mysql:
  image: mysql/mysql-server:8.0
  container_name: mysql-import-perf
  ports:
      - "3308:3306"
  command: --local-infile=1 --socket=/tmp/mysqld2.sock
  volumes:
      - ./mysql:/var/lib/mysql
  restart: always # always restart unless stopped manually
  environment:
      MYSQL_USER: root
      MYSQL_ROOT_PASSWORD: password
      MYSQL_PASSWORD: password
      MYSQL_DATABASE: test

Note the --local-infile parameter, which permits LOAD DATA, and
the --socket parameter, which specifies a non-default socket that
will not conflict with any dolt sql-server instances. All other
parameters, including the database name, are configurable in the test
file yaml.

Inputs Copy Chapter

Specify imports for different servers and workloads along several
dimensions:

• repo

  • dolt server (server field)
  • dolt cli (omit server field)
  • mysql server (external server field)

• table spec

  • fmt (string): file format for importing
    • csv: comma separated lines
    • sql: dump file of insert statements
  • rows (int): number of rows to import
  • schema (string): CREATE_TABLE statement for table to import
  • shuffle (bool): by default generated rows are sorted; indicate true to shuffle
  • batch (bool): whether to batch insert statements (only applies to fmt=sql)

For an examples of the specific yaml input syntax, see the example
files below, or refer to the tests in testdata/.

Server Details:

• For dolt sql-server tests, a new sql-server will be constructed individually
  for each test run.
• External servers are provided outside of the lifecycle of the import_benchmarker
  command. The same database instance is used for every table import test.
• Import files are cached on the schema, row number, and format in between
  tests.

Outputs Copy Chapter

The output format is a .sql file with the following schema:

CREATE TABLE IF NOT EXISTS import_perf_results (
  test_name varchar(64),
  server varchar(64),
  detail varchar(64),
  row_cnt int,
  time double,
  file_format varchar(8),
  sorted bool,
  primary key (test_name, server, detail)
);

A sample import file:

/* Detailed source-code truncated for AI context efficiency. */

Ingest the result file and run queries like the ones below to compare
import runtimes:

/* Detailed source-code truncated for AI context efficiency. */

Example tests Copy Chapter

Example test spec 1:

tests:
- name: "sorting"
  repos:
  - name: repo1
    server:
      port: 3308
  tables:
  - name: "shuffle"
    shuffle: true
    rows: 100000
    schema: |
      create table xy (
        x int primary key,
        y varchar(30)
      );
  - name: "sorted"
    shuffle: false
    rows: 100000
    schema: |
      create table xy (
        x int primary key,
        y varchar(30)
      );

We will import two tables with a dolt sql-server on port 3308.
Both tables have 100,000 rows, and a schema with two columns.
The "sorted" test imports the default sorted rows, while the
"shuffle" imports unsorted rows.

Example import spec 2:

tests:
- name: "row count"
  repos:
   - name: mysql
     external-server:
       name: test
       host: 127.0.0.1
       user: root
       password: password
       port: 4306
  tables:
  - name: "400k"
    fmt: "csv"
    rows: 40000
    schema: |
      create table xy (
        x int primary key,
        y varchar(30)
      );

We will connect to a database server named test on port 4306
with the credentials above to run a 40,000 row import of a table
with two columns.

File: go/store/cmd/noms/README.md Copy Chapter

The noms command is badly supported, but can be useful for certain debugging tasks. Notably, noms manifest
and noms show are kept in good working order. The rest are in varying states of brokenness, but may still be useful.

For inspecting the raw data of a dolt database, the splunk.pl script in this directory is your best bet. It
uses noms manifest and noms show in a simple shell to explore a tree of values and refs.

File: go/store/nbs/README.md Copy Chapter

Noms Block Store

A horizontally-scalable storage backend for Noms.

Overview Copy Chapter

NBS is a storage layer optimized for the needs of the Noms database.

NBS can run in two configurations: either backed by local disk, or backed by Amazon AWS.

When backed by local disk, NBS is significantly faster than LevelDB for our workloads and supports full multiprocess concurrency.

When backed by AWS, NBS stores its data mainly in S3, along with a single DynamoDB item. This configuration makes Noms "effectively CA", in the sense that Noms is always consistent, and Noms+NBS is as available as DynamoDB and S3 are. This configuration also gives Noms the cost profile of S3 with power closer to that of a traditional database.

Details Copy Chapter

• NBS provides storage for a content-addressed DAG of nodes (with exactly one root), where each node is encoded as a sequence of bytes and addressed by a 20-byte hash of the byte-sequence.
• There is no update or delete -- only insert, update root and garbage collect.
• Insertion of any novel byte-sequence is durable only upon updating the root.
• File-level multiprocess concurrency is supported, with optimistic locking for multiple writers.
• Writers need not worry about re-writing duplicate chunks. NBS will efficiently detect and drop (most) duplicates.

Storage locking Copy Chapter

Every file-backed NBS store takes exactly one exclusive advisory file lock:

<database dir>/.dolt/noms/LOCK

The lock is taken through github.com/dolthub/file-locks, which uses flock(2) on unix and LockFileEx on Windows. It is advisory: it only excludes processes that ask for the same lock, and it carries no information a non-locking process can read.

Lifetime Copy Section

newJournalLock takes the lock when a store is opened, and journalManifest.Close (reached from NomsBlockStore.Close) releases it. There is no maximum hold time and it is not a critical section -- a dolt sql-server holds the lock for its whole lifetime, because that is the lifetime of the store it opened. The correct thing to wait for is therefore "the holding process has exited or closed its store", not a fixed timeout.

Acquire timeout and the read-only fallback Copy Section

Dolt waits only lockFileTimeout, 100ms (file_manifest.go), before giving up. What happens next depends on the caller:

• By default the store opens read-only. Writes then fail with a read-only error, which is why a restart that races the previous server's release yields a server that accepts connections but rejects writes, rather than an error naming the lock.
• Embedded-driver callers can pass fail_on_journal_lock_timeout to get ErrDatabaseLocked ("the database is locked by another dolt process") instead of a silent read-only open.
• skip_journal_lock_timeout collapses the wait to a single non-blocking attempt.

Crash and staleness Copy Section

flock locks belong to the open file rather than the process, and the kernel releases them when the process dies -- including on kill -9. A hard crash does not leave a stale lock behind, and there is no operator step needed to clear one.

The LOCK file itself, however, always remains: it is created on open and deliberately left in place on close. Its presence says nothing about lock state -- only holding the flock does.

Do not delete the LOCK file to "clear" a lock. A lock held on an unlinked file excludes nobody, so removing the file while a process holds the lock silently breaks mutual exclusion.

External waiters Copy Section

An external process can take the same flock, but this is not a supported way to coordinate. Dolt does not publish its lock state anywhere and does not watch the file, so an external flock cannot tell a supervisor when a database was released -- it only changes whether Dolt's own next open succeeds, and there is no supported query for who holds it.

To coordinate a restart, wait on the previous process itself: make sure it has exited or closed its store before starting the next one.

Caveats Copy Section

These guarantees come from the underlying OS primitive and are advisory: over NFS or SMB they are only as good as the mount, so keep database directories on local storage. Waiters are not queued, and a Lock is not recursive.

Perf Copy Chapter

For the file back-end, perf is substantially better than LevelDB mainly because LDB spends substantial IO with the goal of keeping KV pairs in key-order which doesn't benefit Noms at all. NBS locates related chunks together and thus reading data from an NBS store can be done quite a lot faster. As an example, storing & retrieving a 1.1GB MP4 video file on a MBP i5 2.9Ghz:

• LDB
  • Initial import: 44 MB/s, size on disk: 1.1 GB.
  • Import exact same bytes: 35 MB/s, size on disk: 1.4 GB.
  • Export: 60 MB/s
• NBS
  • Initial import: 72 MB/s, size on disk: 1.1 GB.
  • Import exact same bytes: 92 MB/s, size on disk: 1.1GB.
  • Export: 300 MB/s

Status Copy Chapter

NBS is more-or-less "beta". There's still work we want to do, but it now works better than LevelDB for our purposes and so we have made it the default local backend for Noms:

# This uses nbs locally:
./csv-import foo.csv /Users/bob/csv-store::data

The AWS backend is available via the aws: scheme:

./csv-import foo.csv aws://[table:bucket]::data

File: go/store/README.md Copy Chapter

dolt/go/store

The persistence layer for dolt. Based on noms,
but adapted for storing table data.