For Linux users we provide an installation script that will detect your architecture, download the appropriate binary, and place in /usr/local/bin:

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

The use of sudo is required to ensure the binary lands in your path. The script can be examined before executing should you have any concerns.

winget

winget install dolt

Chocolatey

choco install dolt

We provide both .msi files and .zip files.

Scoop

MSI Files

The easiest way to install Dolt on Windows is to use our MSI files that are provided with each release, they can be found in the Assets section of our release. Grab the latest .

.zip Archive

For those preferring to install Dolt manually a zipped archive is provided with the requisite executables. It can be found in assets along with our .

Dolt is a SQL database you can fork, clone, branch, merge, push and pull just like a Git repository. Connect to Dolt just like any MySQL database to run SQL queries. 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 from Git work exactly the same in Dolt. Git versions files, Dolt versions tables. It's like Git and MySQL had a baby.

Dolt is a . Dolt is . Dolt is a .

Version Controlled Database

Dolt is extremely simple to install. Dolt is a single ~100 megabyte program. To install it, you download or compile that program and put it on your PATH. For each operating system, we created simpler, more familiar methods of installation.

Dolt is constantly evolving. We release a new Dolt approximately once a week.

To check which version you have installed, run dolt version on the command line or select dolt_version() against a running SQL server. Make sure the version matches the latest as seen on the GitHub releases page.

To upgrade, download the latest Dolt binary for your platform and replace the Dolt binary on your PATH with the downloaded one. Running the install process on most platforms again will do this for you.

If you are running a dolt sql-server you must restart the server to start using the new binary.

Dolt is MySQL compatible. Any MySQL client should be able to connect to a running Dolt SQL Server. If you find a client that is not compatible with Dolt, please file an issue and let us know.

We have actively tested the following clients, separated by type.

• Programmatic Clients

• SQL Editors

For those interested in building from source, clone the Dolt repo from GitHub and use go install. Note, you must have Golang installed on the machine you are compiling on.

$ git clone [email protected]:dolthub/dolt.git
Cloning into 'dolt'...
remote: Enumerating objects: 25, done.
remote: Counting objects: 100% (25/25), done.
remote: Compressing objects: 100% (25/25), done.
remote: Total 87117 (delta 4), reused 6 (delta 0), pack-reused 87092
Receiving objects: 100% (87117/87117), 93.77 MiB | 13.94 MiB/s, done.
Resolving deltas: 100% (57066/57066), done.v
$ cd dolt/go && go install ./cmd/dolt

This will create a binary named dolt at ~/go/bin/dolt, unless you have $GO_HOME set to something other than ~/go.

What is a Branch?

A branch adds non-distributed, write isolation to Dolt. A branch can be thought of as a long running transaction. If you have a set of database changes that logically should be grouped together or reviewed together, you make those changes on a branch.

A branch is a named reference that starts with a parent commit. When creating a branch you define it's parent commit and then effectively you have created a new copy of the Dolt database. Changes to the branch only effect that branch. As you commit to the branch the head of the branch changes to the new commit.

You can merge two branches together using the merge command. This creates a commit in the graph with two parents.

Your Dolt database starts with one named branch, main. The name is configurable.

Branches differ from tags which is a named commit that does not change.

How to use Branches

Branches can be used on a running Dolt server for write isolation or parallelism. Writes to a branch do not effect another branch until a merge.

In traditional SQL databases, transactions are designed to be short lived. The rows you change in a transaction are essentially locked for the duration of the transaction. Because Dolt allows for merge and more complex conflict resolution than traditional SQL databases, Dolt can essentially support long running transactions on branches.

See for a deeper dive into how to use branches in the SQL server context.

Difference between Git branch and Dolt branch

Conceptually Git branches and Dolt branches are the same.

In Dolt, branches become a slightly more important concept in server mode. In Git, you often are working on a clone of your repository locally. Thus, your write isolation can be at the clone level. With Dolt in server mode, your application will be coordinating writes to a single server through branches.

Example

Install Script

The download script for Linux can be used, as OSX is a *nix system. It will download the appropriate binary, and place it in /usr/local/bin:

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

Homebrew

We publish a Homebrew formula with every release, so Mac users using Homebrew for package management can build Dolt from source with a single command:

This will install Dolt as follows:

MacPorts

On macOS, Dolt can also be installed via a via :

Dolt implements Git-style version control on tables instead of files.

Dolt adopts the Git-interface to version control. There are commits, branches, merges, and all the other Git concepts you are familiar with. If you know Git, Dolt will feel very familiar because conceptually, Dolt is modeled on Git.

On the command-line, these concepts are exposed as a replica of the Git command line. Where you would type git log, you now type dolt log. Where you would type git add, you type dolt add. The replication extends to the command arguments.

In SQL, Dolt becomes a bit more complicated because no Git-equivalent to SQL exists. Git read operations are modeled as system tables. Git write operations are modeled as stored procedures. But conceptually, all the Git concepts you are familiar with extend to SQL.

In this section we explore the following Git concepts and explain how they work in Dolt:

Dolt publishes benchmarks for its correctness and performance relative to other relational databases. Additional benchmarks will be added over time.

To learn more, click on a subsection heading.

• SQL correctness tests Dolt's query engine against the sqllogictest suite for correctness issues.

• Server latency uses sysbench to compare Dolt's read and write latencies against MySQL.

• uses a custom benchmark to compare Dolt's bulk import performance against MySQL's LOAD DATA command's performance.

Dolt has three primary functions all with different ways to get started.

1. Version Controlled Database

Run Dolt like you would MySQL or Postgres.

2.

Use the Dolt Command Line Interface like you would the Git Command Line Interface.

3.

Use Dolt as a replica to your primary MySQL server to get version control features without migrating.

What is a log?

The Dolt log is a way to visualize the Dolt commit graph in an intuitive way. When viewing the log, you are seeing a topologically sorted commit order that led to the commit you have checked out. The log is an audit trail of commits.

In Dolt, you can visualize the log of a database, a table, a row, or even a cell.

Log is usually filtered by branch. Any commits not reachable in the graph from the current commit will be omitted from the log.

What is a Trigger?

Triggers are SQL statements you can set to run every time a row is inserted, updated, or deleted from a particular table. Triggers receive the value of the row being inserted, updated, or deleted like a parameter, and can change it in some cases.

Database users create triggers. Triggers are schema. Triggers are stored along with other schema elements in the database.

What is a Transaction?

A transaction is the unit of change isolation is a database.

How to use Transactions

There are two ways to run SQL queries against your database:

• dolt sql-server starts a MySQL-compatible server

• dolt sql runs SQL queries from your shell without starting a server

Problem

• Are you using spreadsheets to curate production data?

• Is the process of merging and reviewing everyone’s changes getting out of hand?

Problem

• Is your configuration too big and complex for files?

• Is your configuration more like code than configuration?

Dolt is a full-featured SQL database, akin to or .

Dolt implements the MySQL SQL dialect. You connect to Dolt using a MySQL client. The goal is for Dolt to be a drop in replacement for MySQL.

Dolt has and as you'd expect. Dolt implements all MySQL . Dolt supports . Dolt supports . Dolt supports , , and . Dolt implements for permissions.

This section of the documentation will explain Dolt's flavor of these standard SQL concepts. Perhaps more importantly, this section will also explain how these concepts interact with Dolt's version control features.

Concepts will be tackled in the following order:

What are Users and Grants?

Users and grants are SQL's permissions system. A database administrator creates users and grants them permissions to do certain actions, like read or write to tables.

How to use Users and Grants

Why DoltHub?

DoltHub is a web-based interface to share, discover, and collaborate on databases. Similar to GitHub, DoltHub let's multiple users easily collaborate on the same database at the same time. It lowers the barrier to entry for users who may want version control for their data without knowing SQL or Git or being comfortable using the command line.

Emailing around CSVs is a way of the past. Have you ever tried to have more than one person collaborate on a CSV at the same time and ended up with multiple versions of the same file without knowing which one has the most up-to-date information? DoltHub solves this problem.

On DoltHub, there is one source of truth. You can clone a database with one command, make some changes, and submit a to incorporate the change into the main branch. Sharing is made easy and you no longer need to worry about which version is the right version.

What is an issue?

Issues on DoltHub are a useful management tool that let users track bugs, ask questions, or document any nuances of data.

How to use issues

It's important that you have full control over can access or change your data. There are a few ways to control users' roles for your database on DoltHub.

Read permissions

When you on DoltHub you can create the database as an individual user or as an organization.

All databases are public unless you have a DoltHub Pro account. Having a public database means that anyone, whether they have a DoltHub account or not, can read your database. DoltHub Pro users or organizations can create private databases for free up to 1GB/month, and $50/month after that. You can learn more about our pricing .

For user-owned databases, only individuals you add through the collaborators form in your database settings can view your private database.

Unlike other relational databases, Dolt has multiple branches and stores all data in a commit graph, like git. This makes it possible to efficiently diff any two commits, as well as merge one branch into another. All the git-like version control functionality available on the Dolt CLI is available in the SQL server as well, exposed as system tables, system variables, functions, and stored procedures.

Version control overview

• explains how to work with different branches in a running server.

What is a Stored Procedure?

A stored procedure is SQL code that can be accessed using SQL CALL syntax. Much like a function in other programming languages, you can pass values into a stored procedures. Results are returned as a table.

Database users create procedures. Procedures are schema and are stored along with other schema elements in the database.

Dolt can be used as a Relational Database Management System or RDBMS. Dolt ships with a MySQL compatible server built in, accessed via the dolt sql-server command.

Dolt supports backups. There are two options for backups: remotes or backups. Pushing to a remote only backs up committed changes. Using dolt backup backs up uncommitted changes as well. Backups are accessed via the dolt backup command or dolt_backup() procedure.

Dolt leverages Git-style remotes to facilitate replication. The master and replicas configure the same remote. On the master, you configure "push on write" and on the replicas you configure "pull on read".

Dolt is a database designed for sharing. Dolt ships with a feature called "Saved Queries". Saved queries are queries that are created and versioned along with the database itself. You can use saved queries to make your Dolt database easier to use by adding examples of how the data in the database could be queried.

To create a saved query, you can use the Dolt CLI to save a SQL query for future use by yourself or others. You can also execute saved queries by name in using the command line.

us-businesses $ dolt sql --save "Example saved query" -q "show tables"
+----------------+
| Table          |
+----------------+
| business_types |
| businesses     |
| naics          |
| sic            |
+----------------+
us-businesses $ dolt sql -x "Example saved query"
Executing saved query 'Example saved query':
show tables
+----------------+
| Table          |
+----------------+
| business_types |
| businesses     |
| naics          |
| sic            |
+----------------+

This query is written to a special Dolt system table dolt_query_catalog. You can add, modify, or delete saved queries by changing that system table.

us-businesses $ dolt sql -q "select * from dolt_query_catalog"
+---------------------+---------------+---------------------+-------------+-------------+
| id                  | display_order | name                | query       | description |
+---------------------+---------------+---------------------+-------------+-------------+
| Example saved query | 1             | Example saved query | show tables |             |
+---------------------+---------------+---------------------+-------------+-------------+
us-businesses $ dolt sql -q "delete from dolt_query_catalog where id ='Example saved query'"
Query OK, 1 row affected
us-businesses $ dolt sql -q "select * from dolt_query_catalog"
+----+---------------+------+-------+-------------+
| id | display_order | name | query | description |
+----+---------------+------+-------+-------------+
+----+---------------+------+-------+-------------+
us-businesses $ dolt sql --save "Example saved query" -q "show tables" -m "You can even add a long description"
+----------------+
| Table          |
+----------------+
| business_types |
| businesses     |
| naics          |
| sic            |
+----------------+
us-businesses $ dolt sql -q "select * from dolt_query_catalog"
+---------------------+---------------+---------------------+-------------+-------------------------------------+
| id                  | display_order | name                | query       | description                         |
+---------------------+---------------+---------------------+-------------+-------------------------------------+
| Example saved query | 1             | Example saved query | show tables | You can even add a long description |
+---------------------+---------------+---------------------+-------------+-------------------------------------+

DoltHub displays these queries in the Queries tab of your database for easy use.

These instructions are for bootstrapping dolt as an application database server. They assume you are starting from scratch on a Linux machine without dolt installed or running.

Package manager support (.deb and .rpm distributions) is coming soon, but for now this set of manual setup work is necessary.

We have the instructions below packaged in a script here.

Installation

Install dolt. Run the following command:

This script puts the dolt binary in /usr/local/bin, which is probably on your $PATH. If it isn't add it there or use use the absolute path of the dolt binary for next steps.

Configuration

Create a system account for the dolt user to run the server.

Before running the server, you need to give this user a name and email, which it will use to create its commits. Choose a dolt system account for your product or company.

You can override this user for future commits with the --author flag, but this will be default author of every commit in the server.

Database creation

Before running the dolt server for the first time, you need to create a database. Choose a directory within /var/lib/doltdb/databases where you want your dolt data to live. Name the directory the same as the name of your database.

You should see output indicating that the database has been initialized:

Start the server

Assuming you want your dolt server to always be running when the machine is alive, you should configure it to run through the Linux service management tool, systemctl. Some distributions of Linux do not support this tool; consult their documentation for configuration instructions.

Write the server's config file in your home directory, then move it to where systemctl needs it to live.

Finally, start the server as a system daemon.

The dolt sql server will now be running as a daemon. Test connecting to it with any SQL shell. Here we are using the mysql shell to connect.

Note that by default, Dolt runs on the same port as MySQL (3306). If you have MySQL installed on the same host, choose a different port for the server with the -P argument.

Users and passwords

By default, when starting dolt sql-server, Dolt will automatically initialize the default root@localhostsuperuser, which is accessible only from the localhost and without a password. To change this account or add any additional accounts, you can use the standardCREATE USER, ALTER USER, and GRANT` SQL statements.

Other Linux distributions

These instructions should work for Debian, Ubuntu, Amazon Linux, and many other common distributions. If you find they don't work for yours and you would like your distribution documented, come chat with us on or to update the docs.

What is a Commit?

A commit signals to Dolt that you would like to save the state of the current database permanently for future reference. In practice, this stores the root hash (or reference) of the database in a graph of all the commits with a link to its parent commit. If the commit is a merge commit, the commit will have multiple parents.

Commit hashes are SHA-256 encoded hashes of the entire database. Commit hashes look like t5d5inj4bpc1fltrdm9uoscjdsgebaih. These are abbreviations of the entire hash that Dolt understands. When referring to a specific commit, this is the identifier you use.

A Dolt commit is different from a standard SQL transaction commit. Dolt supports both which can be a bit confusing.

How to use Commits

Dolt uses commits as the basis of comparison between two versions of a database. You can ask Dolt to do things like:

• Show me the differences between these two commits

• Give me the common ancestor of these two commits

• Make my current database look like this commit

• Show me the difference between my current database and the last commit

You should make a commit when you want to be able to use the current version of the database to do one of the above things.

To create a commit, you tell Dolt you want to make one on the command line or in the SQL interface. A user and commit message are required. Your user is defined in configuration. You provide a commit message via the user interface.

Difference between Git Commits and Dolt commits

Git commits and Dolt commits are very similar in purpose and practice.

In Dolt, you can create a commit via the SQL interface. There is no analogue in Git.

Example

Adding a table and making a commit

CLI

SQL

Creating an empty commit

Dolt brings the features of Git-style distributed version control to the SQL database.

Git-style Distributed Version Control allowed the world to collaborate on open source software in a beautiful way. Dolt aspires to bring that distributed collaboration model to data.

SQL is the worldwide standard for data description and querying. SQL has been popular for 50 years. By combining schema and data, SQL gives data a powerful language for data practitioners to communicate with.

Before Dolt, to share a SQL database with a fellow data practitioner, you both needed to share the same view of the data. Only one write could happen at a time. Making a copy implied creating a point in time backup and restoring on a separate running server. Once that copy was made, the two databases could change independently. There was no tractable way to compare the two copies of the database to see what changed. Moreover, there was no easy way to merge the two copies back together. In source code parlance, the copy was a hard fork of the database.

The inability to copy and merge forced databases into a specific model of usage. Data was hard to move and share. As an industry, we built complicated pipelines to move and transform data between databases. We built APIs to allow programmatic, controlled access to data.

Here at DoltHub, we looked at all these systems and thought there must be a better way. What if you could copy a database, make changes, compare the database to any other copy, and merge the changes whenever you wanted? What if thousands of people could do this at the same time? What if you could use Git workflows on databases?

A database with these properties would allow thousands of users to read and write at the same time. If someone made a mistake, no big deal, just roll back the change. Need a copy of the data to run a metrics job on? No problem, just make a clone. Bug in production? Create a copy of the database on your laptop, start your services, change the production data to speed debugging. Want to open your data up to the world? Push it up to a remote that's accessible via the internet.

Concepts

In order to achieve the above mission, Dolt needed to implement Git concepts in a SQL database. As best we could, we tried to keep things as similar as possible.

We built Dolt using the following axioms:

1. Git versions files. Dolt versions table schema and table data.

2. Dolt will copy the Git command line exactly.

3. Dolt will be MySQL compatible.

4. Git features in SQL will extend MySQL SQL. Write operations will be procedures. Read operations will be system tables.

In order to achieve the above at scale, we needed to start at the bottom; the storage engine of the database. to offer you the Git experience in a SQL database.

In this section of the documentation, we will explain , , and concepts and how we applied them in Dolt using the above axioms.

Problem

• Do you need to know who changed what, when, why in your SQL database?

• Do you want an immutable record of changes going back to the inception of your database?

• Is an audit team asking for this information for compliance purposes?

• Do you want to be able to query this audit log like any other table in your database?

• Do you want the data to be efficiently stored so you can trace changes back to inception?

Dolt solves this by…

Dolt provides a built-in, queryable audit log of every cell in your database. Whenever a is created, the user, time, and optional commit message are recorded along with the data that changed. These commits form an to every cell in your database going back to inception.

Dolt stores these changes efficiently by . Effectively, only the differences are stored between versions of the data.

The audit log created between commits is queryable via standard SQL using custom Dolt and . The results can be filtered and joined using other data in your database.

If you're not ready to switch your primary database to Dolt to get its audit capabilities, you can run MySQL as your primary and set Dolt up as . You lose users and commit messages but you still get a queryable log of every cell in your database.

Dolt replaces...

Soft Deletes

A technique to add audit capability to an existing database is to add . Soft delete is the use various techniques to mark data as inactive instead of deleting it. This is strictly worse than a version controlled database for audit purposes. With soft deletes, an operator can still modify data or the application can make mistakes. In Dolt, every write is part of the audit log. It is far more difficult for an operator to change Dolt history.

Change Data Capture

is another way to add audit capability to an existing database. Some change data capture techniques are similar to strategies. Modern change data capture tools consume replication logs to audit database changes. Dolt can consume the same logs in the producing a simpler and thus, more audit-friendly, change data capture solution.

Moreover, if Dolt is your production database, there is no need for an additional change data capture system. The audit capability is a built-in feature of the production Dolt database.

Companies Doing This

Case Studies

Let us know if you would like us to feature your use of Dolt for audit here.

Other Related Articles

Problem

• Is your production MySQL vulnerable to data loss?

• If an operator runs a bad query, script, or deployment can your production MySQL can be down for hours or days as you recover data from backups or logs?

• Are you worried your backups aren't working?

• Does internal audit want an immutable log of what changes on your MySQL instance?

• Do you want the ability to copy and sync your production MySQL database for analytics, development, or debugging?

Dolt solves this by…

Because Dolt is , you can set Dolt up as of your MySQL primary. Every transaction commit on your primary becomes a on the Dolt replica.

On your Dolt replica, you get a full, immutable, queryable audit log of every cell in your database. If an auditor wants guarantees that a cell in your database has not been modified, you can use Dolt to prove it. can be produced for every transaction.

If an operator makes a bad query, runs a bad script, or makes a bad deployment, you have an additional tool beyond backups and logs to restore production data. Find the bad transactions using Dolt's audit capabilities. Rollback the bad individual transactions. and apply that back to your primary. If there are conflicting writes, Dolt will surface those for you and you can decide how to proceed. A Dolt replica becomes an essential part of your disaster recovery plan, shortening some outages by hours or days or recovering lost production data.

Moreover, Dolt can be added to your serving path as a read-only MySQL replica, so you know that it is always in sync with your primary. Your disaster recovery instance can serve production traffic so you always know it's working.

Additionally, a Dolt replica can be easily cloned (ie. copied) to a developer's machine for debugging purposes. See a data issue in production? Debug locally on your laptop safely.

Dolt replaces...

Backups and Transaction Logs

Dolt as a versioned replica becomes your first line of defense against a bad operator query, script, or deployment. Dolt is online and contains the full history of your database. In a disaster you can use diffs to find a bad query and roll it back. Then you can produce a database patch and apply it to production. You do not need to reinstall from a backup and play the transaction log back to the point of the failure, an extremely time consuming process.

Change Data Capture

is a way to add a history of data changes to an existing database. Modern change data capture tools consume replication logs to produce database changes in a consumable stream. Dolt can consume the same logs producing a simpler change data capture solution.

Companies Doing this

Case Studies

Let us know if you would like us to feature your use of Dolt as a versioned MySQL replica here.

Related Articles

What is a database server?

A database server allows multiple local or remote clients to connect to the same database. You start a database server process on a host. The database server process opens a port or socket and then clients connect with a compatible client. The database server handles authentication.

How to use database servers

Database servers are used to allow multiple users to access the same database over a network. Database servers are often used to back applications. In that case, the application instances are the user.

Difference between the MySQL database server and the Dolt database server

Dolt behaves the same way as the MySQL database server started using mysqld. By default, Dolt starts on the same port as MySQL, 3306.

Interaction with Dolt Version Control

Dolt allows users to connect to multiple branches using a connection string. All users connected to the same branch see the same branch state.

Example

Start a Server

Connect a client

What is a Schema?

Schema defines the shape of the data in your database.

Tables are the core unit of schema. Tables have columns and rows. Each column in a table has a type. A table can have one or more primary keys, the combination of which identify the row and must be unique. Columns can also be assigned additional constraints, including foreign key constraints which are references to other tables in the database.

Schema also includes views. Views look like tables but the data in them is generated using SQL stored in the view definition. The data is stored in the tables the views reference not the view itself.

are a part of schema. An index allows read query performance to be improved at the expense of write performance and increased storage.

Finally, schema includes and . Triggers and procedures are code stored in your database that executes on specific conditions or when a user asks, respectively.

How to use Schema

Schema is the core of database design. You use schema to explain to database users the shape of the data in the database. What values are allowed in this column? What data is allowed to be duplicated in multiple rows? Can a value exist in this table without existing in this other table as well?

Schema design also effects the performance of queries against the database. Defining primary keys and indexes correctly can make your database perform for large databases or complex queries.

Changing schema can be a costly operation. For instance, adding an index to a column on a running database requires scanning the entire table and writing a new index artifact, usually while also restricting writes to that table.

Difference between MySQL Schema and Dolt Schema

Dolt supports .

Interaction with Dolt Version Control

Dolt versions your schema and data. So, if you want to see the difference between the schema of two different versions, Dolt provides this using diff functionality. See individual SQL concepts for how Dolt handles each individual schema element with regards to versioning.

Example

What is a Backup?

A backup is a copy of your database. You can restore the state of the database as it existed at the time of the backup.

How to use Backups

Backups are used for disaster recovery. When you create a backup it is wise to copy the backup to a different host. If you lose access to the host that houses your database, you restore the database from a backup on another host.

Backups have additional uses. Taking a backup of a database is often the easiest way to get a copy of the database. You can use this copy for development, testing, or analytics.

Difference between MySQL Backups and Dolt Backups

Dolt does not support any of the except the mysqldump method. Dolt can create a dump using the or mysqldump connected to the running Dolt server.

Interaction with Dolt Version Control

Dolt supports different methods of backup that leverage Dolt's git-style features.

Dolt databases contain the entire history of the database, meaning Dolt has backups built in to the running server. To restore to a previous point on a running server, you checkout the database at the commit you would like to restore to.

To replicate your database off host so you can restore if you lose the host, Dolt supports two different concepts: remotes and backups.

Using a for backup allows you to back up all committed changes. You add a remote using the . Then you push a branch to a remote using the or .

Dolt has an additional backup functionality beyond remotes. Using dolt backup backs up uncommitted changes as well. Backups are accessed via the or .

Interestingly, you can trigger backups from your application using the dolt_backup() procedure. If you hit a weird edge case you can create a backup of the state of your database for debugging.

Example

Create a new backup

Updating a backup

Restoring from a backup

How garbage is created

Dolt creates on-disk garbage. Dolt transactions that do not have a corresponding Dolt commit create on-disk garbage. This garbage is most noticeable after large data imports.

Specifically, writes to Dolt can result in multiple chunks of the prolly tree being rewritten, which writes a large portion of the tree. When you perform write operations without committing or delete a branch containing novel chunks, garbage is created.

Automatic GC

As of Dolt 1.75, garbage collection will be performed automatically in both dolt sql-server and dolt sql contexts.

If you determine you need to disable Automatic GC in the sql-server context, you must set the following :

To disable Automatic GC in the dolt sql context, start the command with the --disable-auto-gc flag:

How to run garbage collection manually

Garbage collection can be run offline using or online using .

Offline

If you have access to the server where your Dolt database is located and a Dolt sql-server is not running, navigate to the directory your database is stored in and run dolt gc. This will cycle through all the needed chunks in your database and delete those that are unnecessary. This process is CPU and memory intensive.

Online, with Automatic GC disabled

If you have disabled Automatic GC, you can run garbage collection on your running SQL server using through any connected client. To prevent concurrent writes potentially referencing garbage collected chunks, running will break all open connections to the running server. In-flight queries on those connections may fail and must be retried. Re-establishing connections after they are broken is safe.

At the end of the run, the connection which ran call dolt_gc() will be left open in order to deliver the results of the operation itself. The connection will be left in a terminally broken state where any attempt to run a query on it will result in the following error:

ERROR 1105 (HY000): this connection was established when this server performed an online garbage collection. this connection can no longer be used. please reconnect.

The connection should be closed. In some connection pools it can be awkward to cause a single connection to actually close. If you need to run call dolt_gc() programmatically, one workaround is to use a separate connection pool with a size of 1 which can be closed after the run is successful.

NOTE: Performing GC on which is in standby mode is not yet supported, and running call dolt_gc() on the replica will fail. This only applies when Automatic GC is disabled on the secondary.

What is a merge?

A merge is an operation that takes two branches and assembles a reasonable combination of the two databases represented by those branches. The merge may or may not generate conflicts. Merges happen at the Dolt storage layer. No SQL is used to merge.

A merge can be triggered on the command line, through a Dolt SQL function, or implicitly when committing a SQL transaction.

Dolt implements one merge strategy. The Dolt merge strategy will generally produce reasonable results. For schema, if the two branches modify different tables or columns, no conflict is generated. For data, Dolt does a cell-wise merge of data. See conflicts for details on when conflicts are generated and when they are not.

A commit that is created on a branch where a merge operation took place has two parent commits.

There is a special type of merge called a "Fast-forward" merge. Fast-forward merges happen when no changes happened on the branch you are merging into since you branched. In the commit log, fast-forward merges do not create merge commits, they just append the current branch's commits to the end of the branch you are merging into.

How to use merges

Merges are a fundamental building block used to power distributed writes to Dolt. Dolt merges are used to combine two copies of a database into one copy.

Dolt merges are used implicitly in SQL transactions.

Dolt merges are used explicitly in write isolation use cases. Make changes on a branch, examine the differences, and make a commit when you are satisfied the database looks as you expect. Switch to another branch and merge your changes into that branch. Examine the differences if it is not a fast-forward merge and make sure the branch looks as you expect. Make a commit to preserve the merged copy of your database.

Difference between Git merges and Dolt merges

Conceptually merges in Git and Dolt are the same. Practically, Dolt merges can only have two parents. Merges in Git can have N parents.

Example

What are Types?

A column in a SQL database has a defined type, like an integer or a string. Some databases are more strict on types than others. MySQL supports a number of types.

Types act as built in constraints to help define the shape of your data. Are you expecting this column to be an integer or will it sometimes have decimal values? Defining the type explains to other database users the shape of the data you expected when you defined the table.

Moreover types act as a way to control the storage footprint of your database. A database must procure the maximum size of the type you define for each row when it stores your data on disk or memory.

How to use Types

Each column in your tables must have a type. You use types when defining tables. You can change the type of a column with ALTER statements.

When querying tables, the type of the column defines which functions can be used on the data retrieved. To manipulate the type of a column when querying you use the CAST() function.

Difference between MySQL Types and Dolt Types

Dolt supports .

Interaction with Dolt Version Control

A type change in Dolt is versioned and can cause conflicts when merged. Dolt can diff across some type changes and will make a best effort to do so.

Example

Problem

• Are you expecting your application to make writes locally while offline?

• Do these writes need to be synced to a central server or other nodes?

• How are you going to detect conflicting writes?

• What are you going to do if you detect them?

• Would the Git model of clone, push, and pull on your data help?

Dolt solves this by…

Dolt brings Git-style decentralization to the SQL database. Just like Git is ideal in no connectivity environments when dealing with files, Dolt is ideal in low connectivity environments when dealing with tables. Most large scale data is stored in tables.

With Dolt you write to the database disconnected. You can have a fully functioning offline application that uses the exact same software and models it would use if it were a standard centralized SQL database.

When it is safe to connect to the internet, Dolt computes the difference between what you have and what a peer database has and only sends these differences both ways. This synchronization process is very efficient, effectively allowing you to get the most information possible in and out in the shortest amount of time. Once the synchronization is complete, go back to disconnected. You and the peer now share a synchronized view with complete, auditable edit history.

Conflicting writes are surfaced quickly and an operator or software can take additional action to resolve.

Dolt replaces

Custom syncing processes

Dolt replaces custom code to synchronize your client and server. This code is complicated and hard to get right. The Git model of clone, fetch, push, and pull is a proven synchronization model. Dolt brings this model to the database allowing you to remove most of your synchronization code.

Companies Doing This

Be the first

Case Studies

Let us know if you would like us to feature your use of Dolt for data sharing here.

Other Related Articles

What is a View?

Views look and act like tables, but the data in views is materialized on execution using a view definition query that itself references concrete tables. The data is stored in the tables the views reference not the view itself.

How to use Views

Views allow you to derive tables using SQL instead of storing a copy of all the data you might want to derive. As a simple example, in a table of employee's salaries, you may store yearly salary but when using the table to calculate monthly salary, you use a view that divides the yearly salary by 12.

Note, accessing views is be slower than accessing a table itself because the database must compute the values returned.

Difference between MySQL Views and Dolt Views

There is no difference between MySQL and Dolt views. They are functionally equivalent.

Interaction with Dolt Version Control

Dolt view definitions are versioned in the dolt_schemas system table.

If you would like to use a current view with an different version of the data, as of syntax works with views. To use a past definition of the view, you must checkout a branch at the commit of the definition you would like to use.

Example

Using as of with Views

Dolt's SQL server can optionally expose metrics through a Prometheus HTTP endpoint. You can enable the Prometheus HTTP endpoint by defining a metrics section in your YAML configuration. The following YAML configuration file shows a complete configuration file that enables Prometheus metrics on port 11228:

Once you start a Dolt SQL server with the configuration above, you'll be able to view all the exposed metrics and their descriptions at http://localhost:11228/metrics. The metrics prefixed with dss_ are Dolt SQL server metrics, and metrics prefixed with go_ are Golang runtime metrics.

Important Dolt SQL Server Metrics

• dss_concurrent_connections – Number of clients concurrently connected to this Dolt SQL server.

• dss_concurrent_queries – Number of queries concurrently being run on this instance of Dolt SQL server.

• dss_query_duration_bucket – Histogram buckets of Dolt SQL server query latencies.

• dss_is_replica – Indicates if this Dolt SQL server is a replica. (Only exposed when replication is enabled)

• dss_replication_lag – The replication lag in ms for this replica. (Only exposed when replication is enabled)

Important Go Runtime Metrics

• go_gc_duration_seconds – Histogram buckets containing counts for different pause durations of garbage collection cycles.

• go_gc_duration_seconds_count – The total number of seconds Dolt has spent performing garbage collection.

• go_memstats_alloc_bytes – Number of bytes allocated and still in use.

Scraping with Prometheus

After you've inspected the metrics by manually looking the /metrics page, you can configure a Prometheus server to scrape that data so you can use the Prometheus web UI to explore your metrics. Here's an example Prometheus server configuration file showing how to configure metrics scraping for a Dolt SQL server with metrics exposed on port 11228:

Other monitoring products, such as , can also be configured to exposed by a Dolt SQL server.

Dolt's goal is to be compliant with the MySQL dialect, with every query and statement that works in MySQL behaving identically in Dolt.

For most syntax and technical questions, you should feel free to refer to the MySQL user manual.

Any deviation from the MySQL manual should be documented on this page, or else indicates a bug. Please file issues with any incompatibilities you discover.

This series of documents shows:

• ✅ Which SQL language features we support the same as MySQL

• 🟠 Where we support the feature but deviate from MySQL in some way

• ❌ Where we lack support for the SQL language feature.

This section is divided into five main categories:

1. : SQL features for describing and organizing data

2. : SQL expressions, functions and operators used in queries

3. : statements Dolt supports

What is a Database?

A database is a container for a set of schema: , , , , etc. In relational databases, queries within a database are optimized, while queries across multiple databases are not.

A relational database management system or allows you to access multiple databases from a single running . Confusingly, "database" is also shorthand for RDBMS. Phrases like "Connect to this database" or "We run our database on AWS" refer to RDBMS, not the SQL concept of a schema container.

What is a remote?

A remote is a Dolt database in another location, usually on a different, network accessible host. A Dolt remote is the coordination mechanism between many local copies of Dolt. A Dolt database can have multiple remotes.

DoltHub is a hosted Dolt remote with an additional discovery and management user interface. is a self-hosted version of DoltHub. .

You configure a storage location as a remote. Once configured, you can perform Dolt's distributed operations using that remote: clone, fetch, push, and pull.

Clone creates a copy of remote database in your current directory. In the case of clone, the remote you cloned from is automatically configured as the

Problem

• Do you share data with customers?

• Do they ask you what changed between versions you share?

What is a fork?

A fork is a copy of a database. The fork model exists so that you control who can modify your data, and determine what data gets merged. You can continue to pull changes from the database that you forked from, and you can submit back to it. You can use it as a tool to get your changes onto a database, or you can use it to take that database in a different direction.

We built Dolt as a better way to . Along the way, customers wanted an OLTP SQL database with Git features, so that is what Dolt became. Dolt is still a great way to share data but it's also .

Anything you can build with MySQL or Postgres you can build with Dolt.

Dolt really shines when your database can benefit from , , , or . We've written about customers who use Dolt to , , or . These are just the customers who allowed us to write about their use case.

Other customers use Dolt to , , , , and much more.

What is a Table?

Tables are the core unit of database . Tables are defined by a set of columns. Columns can be which act as a unique identifier for each row. Once a table schema is defined, rows containing data can be inserted into the table.

Table data is stored on disk. The way a database lays out it's table data on disk defines some of the performance characteristics of the database.

What is a Secondary Index?

A secondary index can be added to any column or set of columns to convert lookup queries involving those columns into indexed lookups. Indexed lookups can be accomplished in constant time (ie. O(1)).

Secondary indexes are stored as separate data structures on disk or in memory. Thus, the use of secondary indexes uses more storage and increases insert and update time.

Secondary indexes are called "secondary" to distinguish them from which also provide indexed lookups.

Dolt supports a subset of SQL modes that MySQL supports. SQL modes are added upon request, so please file an issue if a SQL mode you need is missing.

Currently supporting 4 of 18 MySQL SQL Modes.

Problem

• Do your customers want branches and merges in your application?

• Do your customers want to review changes in your application before they go live?

• Do you want to add a pull request workflow to your application?

• Do you want to expose audit log functionality in your application?

• Do you want to expose rollback functionality in your application?

Dolt solves this by…

If you have an application that would benefit from , , , , and human review of changes, you can use Dolt to power that application. Dolt gives you branch, diff, and merge at the database layer.

Programmatically access git functionality via , , and . Programmatic control of Git operations combined with the ability to use creates the ideal foundation to add version control to your application.

Dolt ships with standard tools like and . Run Dolt with a hot standby and failover just like MySQL or Postgres.

is a hosted version of Dolt that works like AWS RDS. Let us worry about operating Dolt in the cloud. Write your application against a cloud endpoint.

In the past applications that needed these features required or . These approaches are cumbersome and do not support merge. Dolt gives application the full development power of Git.

Dolt replaces

Soft Deletes

A common technique to version your database is to use . When your application would make an update or a delete, you application instead makes an insert and marks the old row invalid. Dolt obviates the need for this technique. You can keep your existing database schema and Dolt ensures every write is non-destructive. Queries against soft deleted rows become Dolt history queries against .

Slowly Changing Dimension

A more advanced technique for versioning databases is . Slowly Changing Dimension is similar to soft deletes. Additional database columns are added to tables to manage versioning. Dolt is slowly changing dimension on every table by default. Queries involving the slowly changing dimension become Dolt history queries against . Moreover, complicated processes can happen at the database layer. Merges must handled by custom code at the application layer with slowly changing dimension.

Companies Doing This

Case Studies

Other Related Articles

What is Replication?

Replication is the ability for an RDBMS to synchronize a primary server with one or more read replicas. In this configuration, the primary database serves reads and writes while the replicas only serve reads.

How to use Replication

Replication is used for disaster recovery and to increase read throughput by distributing read load.

For disaster recovery, if your primary server goes offline, your database can still serve read traffic from its replicas. Often a manual or automated process can elect and configure a replica to be the primary instance limiting downtime.

To increase read throughput, multiple replicas can be used to scale reads horizontally. If you have N replicas and your primary still takes reads, each read replica serves 1/N+1 percent of the read traffic. Note, in this set up your application must be aware of the replicas. The database does not route requests automatically.

Differences between MySQL Replication and Dolt Replication

, most based on the . Dolt supports a , where you configure a Dolt sql-server as a replica for an existing MySQL or MariaDB database. Dolt does not create binary logs and can NOT act as a primary for binlog replication.

Dolt supports two replication modes where Dolt can act as a primary and replicate to other Dolt sql-servers. The first is called . In this mode the primary and the read replicas are completely decoupled. The primary and the read replicas leverage a shared, Git-style to facilitate replication. On the primary, you configure "push on write" and on the replicas you configure "pull on read". This mode only replicates branch heads, which means that new dolt commits are required in order to replicate writes.

The second mode is called . In this mode, you configure a cluster of dolt sql-server instances to replicate all writes to each other. Each server is configured to replicate writes to all other servers in the cluster. One server is configured as the primary replica and it accepts writes. All other servers are configured as standbys and only accept read requests.

Interaction with Dolt Version Control

Dolt uses remotes to synchronize between primary and read replicas. Replication leverages Dolt's ability to produce differences between two versions of a database quickly.

Example

The following example shows write replication from a primary and read replicas using a Git-style remote to rendezvous and maintain loose coupling. For more details on clustering in sql-server, see .

Configuring a Primary

In this example I use a DoltHub remote to facilitate replication. I created an empty database on DoltHub and .

The changes are pushed to the remote.

Configuring a Replica

To start a replica, you first need a clone.

Now, I'm going to configure my read replica to "pull on read" the main branch from origin.

Now on the primary.

And back to the replica.

What is a Constraint?

Constraints restrict the values allowed in a column. There are multiple forms of constraints:

1. NOT NULL or UNIQUE in the column definition

What is a Primary Key?

A primary key is a column or set of columns that defines a unique row in a table. Often the primary key is a unique identification or id number.

In most databases, a map of primary keys to the values of the other columns in the table is how the data is laid out on disk or memory. This makes row lookups by primary key indexed lookups. Indexed lookups can be accomplished in constant time (ie. O(1)). Thus, use of primary keys in table definition and queries is a common database performance optimization. are called "secondary" to distinguish them from primary keys.

Tables without primary keys, or keyless tables, are implemented differently in databases. Usually, a keyless table is implemented as a map of every column in the table pointing to a counter of the number of rows with that value. When the counter goes to zero the index is deleted.

Dolt comes with a built-in MySQL compatible server, making it easy to connect to your Dolt databases with existing SQL tooling. Here are a list of notable MySQL Editors and our compatibility status.

Problem

• Are you in the business of creating data and models?

• Do you want to institute human or automated review on data changes for data quality assurance?

SQLLogicTests

To measure Dolt's SQL correctness, we test each release of Dolt against a SQL testing suite called . This suite consists of 5.9 million SQL queries and their results, using the results returned by MySQL as a reference. Many of these are randomly generated queries that exercise very complex logic a human would have a hard time reasoning about. They give us greater confidence that the query engine produces correct results for any query, as opposed to just the ones we and our customers have thought to try so far. These tests have exposed many bugs in query execution logic before customers discovered them.

Here's an example of a query run by this test suite:

Here are Dolt's sqllogictest results for version 1.78.6. Tests that did not run could not complete due to a timeout earlier in the run.

Dolt's unique implements a Git-style commit graph of . Dolt's commit graph facilitates common version control operations like log, diff, branch and merge on database tables instead of files.

Git vs Dolt

Git and Dolt share the same . In Git and Dolt, the commit graph concepts are the same. In Git and Dolt, the commands to modify the commit graph are the same. The only difference is what Git and Dolt version. Git versions files. Dolt versions tables. Thus, if you know how the Git commit graph works, you know how the Dolt commit graph works. If not, read on.

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:

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:

Note that AS OF always names a revision at a specific Dolt commit. Changes on a branch's that have not been committed to that head via call dolt_commit() or similar are not visible via this syntax.

Specifying a revision in the database name

You can connect to any commit in the database history by including its commit hash in the name of the database, like this:

mysql://127.0.0.1:3306/mydb/ia1ibijq8hq1llr7u85uivsi5lh3310p

The database will be read-only in this case. You can do the same thing on an existing connection with a USE statement.

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.

There are other variations on this as well. See the docs on for more details.

Note that this syntax applied to a branch will name that branch's and therefore includes any changes not yet committed to the HEAD of the branch.

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.

To query how rows changed between two commits, use the dolt_commit_diff and dolt_diff tables.

For more information, see the .

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 , e.g.:

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:

Debugging a running Dolt server can be challenging. This document covers the debugging basics and how to diagnose what is happening from common symptoms.

Basics

Make sure you are running the latest Dolt version

Dolt is constantly evolving. We release a new Dolt approximately once a week. Connect to the SQL server and run select dolt_version(). Make sure the version matches the latest as seen on the .

To upgrade the server, download the latest Dolt binary for your platform and replace the Dolt binary on your PATH with the downloaded one. Running the install process on most platforms again will do this for you. Restart the Dolt server using dolt sql-server to have your running server start using the latest binary.

Examine your CPU, Memory, and Disk usage

Dolt consumes CPU, Memory, and Disk. Consuming more of any of these resources than the host has available can lead to degraded performance. Use your system's built in resource monitoring systems to inspect Dolt's usage of these resources. You may need a larger host or additional to support your load.

Set your log level to DEBUG or TRACE

To see queries being run against the server, query results, and query latency set your Dolt log level to DEBUG or TRACE. This can be done by starting the server like so dolt sql-server --loglevel=debug or by setting log_level: debug in your config.yaml. Your logs should be visible in the shell you started dolt sql-server in.

EXPLAIN PLAN for complex queries

Dolt supports the SQL EXPLAIN PLAN operation in order for you to see the plan for complex queries. Rearranging your query to perform fewer JOINs or make better use of indexes can help speed up complex queries.

Note: EXPLAIN currently returns MySQL-consistent but otherwise no-op output. Use EXPLAIN PLAN for Dolt formatted plans.

Compare to MySQL

Dolt strives to be 100% MySQL compatible. If you run a query that works in MySQL but does not work in Dolt, it is a Dolt bug and you should . You can dump your Dolt database using and import the resulting file into MySQL using mysql < dump.sql. The test the query you think should work using any MySQL client.

Submitting Issues

If you run into any issues requiring engineering attention, please submit a to the Dolt project. Please be as detailed as possible in your report. Note the schema of the database and query or queries that can be used to trigger the issue. If possible, push the database to so we can use a clone to reproduce the issue.

Problems

Dolt operational issues usually manifest as slow SQL queries. In rare occasions, Dolt may consume more of your system's resources than you expect. In these cases, this document has some recommendations.

Server Consuming Disk

Dolt creates disk garbage on write. This can sometimes become a substantial portion of the disk Dolt is consuming. Dolt ships with a garbage collection function. Running the garbage collection function can free disk.

To run garbage collection online, run . There is you can enable to run this periodically in the background as the database grows.

To run garbage collection offline, stop your dolt sql-server, navigate to the Dolt directory where your database is stored and run dolt gc. Once the operation is complete, restart your server using dolt sql-server.

Disk garbage is especially pronounced after imports. We recommend concluding imports with a dolt gc call.

Another potential cause is a commit-heavy workflow that uses a database design that is antagonistic to Dolt's structural sharing. We've written thoroughly about this , but some examples include

• Using primary keys with random values. Inserts into indexes with random values guarantees that edits will occur all throughout the index instead of being clustered around the same key space. This results in a rewrite of the prolly tree thereby increasing storage disproportionately to the delta of the changes.

• Adding a column to a table. A new column forks the storage of the table resulting in a loss of structural sharing. Dolt is row major and builds chunks for each primary key, row values pair. The row values encodes the schema length so every row now requires a new chunk.

Server Consuming Memory

Serving Dolt databases requires a fair amount of memory. As a general rule, we recommend a minimum of 2GB available RAM for any production use case. Larger databases or heavier workloads should start at 4GB of RAM, and 8GB is common for our production customers. Your server's RAM requirements grow with the size of the database being served, the number of concurrent connections, the size / complexity of queries being executed, and other factors. These numbers can vary dramatically and are only intended as first-step guidance on resource requirements. Your use case may require more or less memory to run well, and you should load test to determine the correct ceiling.

A query may cause Dolt to grow memory use unbounded and then eventually crash the server. If you discover one such queries, please submit a . Such queries should be rare but not impossible, especially with complex queries containing multiple JOINs.

Dolt may not free memory efficiently. If your Dolt server grows memory use unbounded over time and then frees the memory upon restart, you have discoverd a memory leak. Again, please submit a . Memory leaks should be rare and we treat memory leak fixes as high priority.

Server Consuming CPU

Under too much concurrent load, Dolt may consume all the CPU on a host. This is likely caused by too much read concurrency. In this case, create more and load balance your reads among your replicas.

If you discover a query consuming all of your CPU, please submit a . On rare occasions, this could be a Dolt bug.

Too much write concurrency

Currently, Dolt is not a high-throughput database for writes. The current transaction model serializes all writes, which means that after a certain threshold of writer concurrency, you'll observe increasing latency for write operations which becomes worse as more writers pile up. As of this writing Dolt can handle approximately 300 writes per second, but this number can be lower depending on the size of the database, the size of the updates, replication settings, and other factors.

Improving maximum write concurrency is an ongoing project.

What is a pull request?

Pull requests are a way to propose changes to a database. A pull request is created from a branch with new changes that a user would like to make to another branch (commonly the main or master branch). Once the pull request is opened, the reviewer can easily review the diff of the proposed changes and discuss potential improvements or implications of the change. If approved, the pull request can be merged, which will update the base branch with the changes from the feature branch.

How to use pull requests

You can use pull requests to propose changes for another branch. To create a pull request, you should make changes to a branch on your database or a of another database. Then you can use the new pull request form to choose your base branch. You can also add a title (required) and description (optional).

Difference between GitHub pull requests and DoltHub pull requests

GitHub pull requests are similar to DoltHub pull requests in purpose. GitHub pull requests include some extra features that are on our longer-term DoltHub roadmap (like actions, tagging other users as reviewers, etc). Both include a list of commits, comments, and a diff of proposed changes.

Due to the difference in nature of reviewing data vs reviewing code, diffs on DoltHub look a little from diffs on GitHub. On GitHub you compare files, while DoltHub compares changes to data tables (outside of README and LICENSE changes). It's rare to have a pull request on GitHub with a file containing thousands of line changes, while this is much more common for data tables. DoltHub diffs attempt to make reviewing data easier by letting the user focus on one table at a time and filter by added, deleted, or modified rows.

Example

We will use our database as an example.

First, we the database and clone our fork.

We check out a new branch for our change, and then use the SQL shell to make some changes to the data. In this case, we'll fill in some missing school websites.

We can look at the of our changes. If they look good we add and the changed table and push our branch to DoltHub.

We can open a pull request in dolthub/us-schools by selecting our fork and branch. We also add a title.

The owner of dolthub/us-schools can look at the diff and request changes.

Once the changes have been approved, the owner can merge the pull request. The commits from the pull request will show up on the main (or master in this case) branch.

And we have successful used pull requests to incorporate a change to a database!

What is a Working Set?

Dolt has three kinds of changes: committed, staged and working. The working set is the set of changes that has not been staged or committed. To stage a change in a table you add it to the staging set. The staging set is committed by issuing a commit command and supplying the appropriate metadata.

If you start a Dolt SQL server and start making changes, you are making changes to the working set. If you never make a commit, your working set is a standard MySQL database. So, a way to think about your working set is Dolt without version control features, just a standard MySQL relational database.

How to use Working Sets

Working sets are used to isolate changes to your database from committed schema or data. Each branch gets it's own working set so you can make changes in isolation. If you don't like the changes made to a working set, you can reset or checkout to the previous commit.

Difference between Git Working Sets and Dolt Working Sets

Git working sets change files. Dolt working sets change tables.

On the command line, working set changes do follow to the newly checked out branch, just like Git. However, in SQL server mode, working set changes are not transferred to the newly checked out branch when you do a dolt checkout. The working set changes stay on the branch they were originally made. This change was made to account for multiple users using the same branch in SQL server mode.

Example

Make changes in a working set

See what's changed in your working set

Reset a change to your working set

Checkout on the Command Line

Checkout in SQL server

Branches and database revisions allow you to work with your data at any commit in your database's commit graph. This is useful for isolating development on different branches, analyzing historical data, tracking data lineage, and much more.

Unlike other relational databases, Dolt has multiple heads, one for each branch in the database. A head can be a branch, a tag, or a working set. Multiple clients can connect to each branch, and will see other writes to the same branch following the normal SQL transactional isolation semantics (REPEATABLE_READ). In effect, each branch functions as its own isolated database instance, with changes only visible to other clients connected to the same branch.

A database server has a default branch, which is the checked-out branch at the time the server was started, and can be changed for new connections with a . Using database revision specifiers, clients can choose a specific branch, tag, or commit to pin their queries to.

Our approach to SQL performance benchmarking is to use sysbench, an industry standard benchmarking tool. We also benchmark Dolt using , an industry standard transactional throughput metric.

Performance Roadmap

Dolt is slightly slower than MySQL on the sysbench test suite. The goal is to get Dolt to match MySQL latency for common operations. Dolt is currently 10% slower than MySQL, approximately 10% faster on writes and 33% slower on reads. The multiple column represents this relationship with regard to a particular benchmark.

Access management in Dolt is handled similarly to how it is handled in MySQL. When Dolt is running, it relies upon the grant tables (mysql.user, mysql.db, etc.) to control user access. Access is determined by the privileges that a user has. For more information on the basics of how users and privileges work and how to use them, . This document will assume some familiarity with users and privileges.

Configuring Privileges

Users and grants are on by default. Users and grants are stored in the .doltcfg/privileges.db file by default. You can reference a non-default privileges file if you want to share privileges between databases.