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 version controlled database. Dolt is Git for Data. Dolt is a Versioned MySQL Replica.

Version Controlled Database

Dolt is a . Connect to Dolt just like any MySQL database to run SQL queries. Use Dolt , , or to access version control information and features.

Git for Data

Dolt is . . When you would have run git add, you run dolt add. When you would have run git commit, you run dolt commit.

Versioned MySQL Replica

Dolt can be deployed as a . Because Dolt is MySQL compatible, Dolt can be configured just like any other MySQL replica. A Dolt replica gives you features of a without migrating from MySQL.

is a cloud-deployed Dolt database. Choose the type of server and disk you need and we'll provision the resources and run Dolt for you. Connect with any MySQL client. Hosted Dolt is perfect for teams who want to build a Dolt-powered application.

We also built , a place to share Dolt databases. We host public data for free! DoltHub adds a modern, secure, always on database management web GUI to the Dolt ecosystem. Edit your database on the web, have another person review it via a pull request, and have the production database pull it to deploy.

Not ready to put your databases on the internet, no matter the permissions? We have a self-hosted version of DoltHub we call . DoltLab gives you all the features of DoltHub, wherever you want them, in your own network or on your development machine.

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.

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.

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.

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.

• 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.

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

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

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 .

Types act as built in 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.

Problem

• Is your configuration too big and complex for files?

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.

Problem

• Are you using spreadsheets to curate production data?

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

winget

Chocolatey

We provide both .msi

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.

Dolt can be used as a Relational Database Management System or RDBMS. Dolt ships with built in, accessed via the .

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

Dolt leverages Git-style 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 implements Git-style version control on tables instead of files.

Dolt adopts the Git-interface to version control. There are , , , 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.

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 and let us know.

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

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

What is a Database?

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

A relational database management system or RDBMS allows you to access multiple databases from a single running server. 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.

How to use Databases

Databases logically divide up your schema. Permissions can be applied to databases as a logical entity.

When you connect a client to a running server, you can see the databases being served using the show databases command. To use a specific database, you issue a use <database> statement. You can also specify the database in the connection string to connect to a particular database.

Difference between MySQL Databases and Dolt Databases

In Dolt, databases act like they do in MySQL.

Interaction with Dolt Version Control

In Dolt, each database has its own commit graph. So, Dolt version control is limited to a single database. You cannot commit changes across multiple databases in a single commit. You cannot share a log across multiple databases. Branches cannot be made across databases.

Dolt databases are the unit of sharing. Clone, push, pull, and fetch act on individual databases. Thus, to create a copy of multiple databases, you must clone from multiple remotes.

The only SQL statement not versioned in Dolt is DROP DATABASE. This statement deletes the Dolt database on disk, removing the database and all of its history. DROP DATABASE works this way for SQL tool compatibility as it is common for import tools to issue a drop database to clear all database state before an import. Dolt implements like in Git so you can maintain an offline copy for backup using clone, fetch, push, and pull. Maintaining a remote copy allows you to restore in the case of an errant DROP DATABASE query.

Example

Note

Previous to Dolt 1.27.0, Dolt replaced hyphens and space characters in database names (e.g. database directories containing hyphens and space characters) with underscores. Dolt now allows databases to be named with hyphens and space characters. If you need the old behavior for compatibility, you can set the DOLT_DBNAME_REPLACE environment variable to any value.

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 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

DoltHub issues are useful for:

• Tracking future work

• Having open and transparent communication with database owners

• Discussing and fixing potential bugs

Difference between GitHub issues and DoltHub issues

DoltHub issues are similar to GitHub issues in purpose, but are somewhat simpler in practice. GitHub has some extra features that let's you easily cross-reference issues and . You can link an issue to a pull request and automatically close the issue when a pull request is merged. In GitHub you are also able to assign issues to users and add labels to issues.

These features are on our DoltHub roadmap.

Example

We will use our database as an example.

One of the bounty participants noticed some inconsistencies with one of the columns in the vote_tallies table. They decided to create an issue to document work that could be done to fix this issue. You can create an issue using the new issue form on any database page.

You notice the issue can be easily fixed, so you run a few SQL queries and create a new with some changes.

I can let the user who created the issue know that I fixed the issue by commenting on the issue, which notifies the issue creator and any participants in the issue.

Now that this issue has been documented and fixed, anyone who also notices this problem with the data can see the discussion surrounding it, and also view the pull request with the solution. If the owner of the database decides to approve and merge the pull request, the issue can be closed and any participants will be notified that there has been a resolution.

We built Dolt as a better way to share data. 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 a great SQL database.

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

Dolt really shines when your database can benefit from branches, merges, diffs, or clones. We've written about customers who use Dolt to build better cancer cell simulations, power an application with branches, or add a versioning layer to important spreadsheets. These are just the customers who allowed us to write about their use case.

Other customers use Dolt to manage video game configuration, get an immutable audit log of changes to their database, build reproducibility into machine learning models, ensure data quality using a pull request workflow, and much more.

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

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 Table?

Tables are the core unit of database schema. Tables are defined by a set of columns. Columns can be primary keys 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.

How to use Tables

Structure the data in your database into tables. Define relationships between tables using foreign key . Use CREATE statements to create tables and ALTER statements to change their schema.

Difference between MySQL Table and Dolt Table

A MySQL and Dolt table function the same on the surface. CREATE and ALTER statements work the same on both.

Dolt and MySQL are , meaning row values are stored next to each other. However, MySQL stores data in a binary tree structure while Dolt stores table data on disk using a content-addressed binary tree called a . This setup makes Dolt while also providing history-independence and fast diff between versions. Fast diff powers Dolt's version control capabilities.

Interaction with Dolt Version Control

Dolt versions table schema and data. A table in Dolt is akin to a file in Git, it is the unit of change. Tables are the target of dolt add.

Example

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.

How to use Triggers

Triggers are a general tool, but they are most commonly used to enforce complex constraints that can't be expressed by foreign keys, nullness, types, or the check syntax.

Difference between MySQL Triggers and Dolt Triggers

Dolt triggers match MySQL triggers exactly.

Interaction with Dolt Version Control

Triggers are versioned in the dolt_schemas table just like . You add and commit that table just like any other changed table after you create or modify a trigger.

Example

dolt_schemas table

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.

How to use logs

Logs are useful in reverting the database to a previous state. You determine the state of the database you want via log and then use other Dolt commands to change the database to a different state.

Logs are useful when trying to track down why the database is in a particular state. You use log to find the commits in question and usually follow up with diffs (i.e. differences) between two commits you found in the log.

Logs are useful in audit. If you would like to ensure a particular value in the database has not changed since the last time you read it, log is useful in verifying this.

Difference between Git log and Dolt log

Conceptually and practically log on the command line is very similar between Git and Dolt. A table is akin to a file in Git.

Dolt has additional log functionality beyond Git. You can produce a log of any cell (i.e. row, column pair) in the database using a SQL query against the dolt_history_<tablename> system table.

Example

Commit Log

Cell History

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

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

dolt sql-server

The dolt sql-server command runs a MySQL compatible server which clients can connect to and execute queries against. Any library or tool that can connect to MySQL can connect to Dolt.

The host, user, password, timeout, logging info and other options can be set on the command line or via a config file.

View the dolt sql-server command documentation .

Stopping the server

The dolt sql-server process can be stopped using your operating system's process control mechanism. Dolt will stop when sent a signal like SIGHUP, SIGQUIT, SIGABRT, or SIGKILL.

A common way to send a SIGKILL is to navigate to the shell running the dolt sql-server process and Ctrl-C.

Another common way to stop the server is to identify the process running dolt sql-server and send a signal to it using the kill command.

dolt sql

Using dolt sql you can issue SQL statements against a local database without starting a server.

With no arguments, dolt sql begins an interactive shell.

With the -q flag, it executes queries specified as arguments.

You can also use STDIN to the dolt sql command to execute many SQL statements at once. This is useful for .

View the dolt sql command documentation .

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 :

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 .

Problem

• Is your production MySQL vulnerable to data loss?

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.

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

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.

What is a Transaction?

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

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.

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

Problem

• Do you share data with customers?

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

• Do they want to actively switch versions instead of having data change out from under them?

• Or, are customers or vendors sharing data with you?

• Are you having trouble maintaining quality of scraped data?

• When new data is shared or scraped, do downstream systems break?

• Would you like to see exactly what changed between data versions?

• Do you want to add automated testing to data shared with you?

• Would you like to instantly rollback to the previous version if tests fail?

Dolt solves this by…

Dolt was built for sharing. The Git model of code sharing has scaled to thousands of contributors for open source software. We believe the same model can work for data.

Dolt is the world's first version controlled SQL database. Git-style version control allows for decentralized, asynchronous collaboration. Every person gets their own copy of the database to read and write. DoltHub allows you to coordinate collaboration over the internet with , , and all the other distributed collaboration tools you are used to from GitHub.

Dolt and DoltHub is the best way to share data with customers. Use versions to satisfy both slow and fast upgrading consumers. Let your customers help make your data better. Versions offer better debugging information. Version X works but version Y doesn't. Your customers can even make changes and submit data patches for your review, much like open source.

Dolt and DoltHub are also great if vendors share data with you. When you receive data from a vendor, import the data into Dolt. Examine the diff, either with the human eye or programmatically, before putting the data into production. You can now build integration tests for vendor data. If there's a problem, never the import into main or roll the change back if a bug was discovered in production. Use the problematic to debug with your vendor. The same tools you have for software dependencies, you now have for data dependencies.

Dolt replaces...

Exchanging Files

Dolt replaces exchanging flat data files like CSVs via email, FTP servers, or other file transfer techniques. Dolt allows data to maintain schema on exchange including constraints, triggers, and views. This more rich format of exchange reduces transfer errors. Dolt also allows you to change the data to fit your needs and still get updates from your source. Dolt will notify you if your changes with the source.

External APIs

Dolt is ideal for sharing data that does not have an API. But even for data with an API, Dolt is often more convenient. With Dolt, you get all the data and its history. With APIs you often have to assemble the data with multiple API calls. With APIs, the data can change out from under you, whereas with Dolt you can read a version of the data until you are ready to upgrade. DoltHub ships with a so you can choose the data sharing solution that is right for your use case.

Companies Doing This

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 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. DoltLab is a self-hosted version of DoltHub. Dolt also supports filesystem, HTTPS, AWS, and GCS remotes.

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 origin remote.

Fetch gathers all the changes made to the remote since you last fetched.

Push performs a merge of your current branch and the remote branch you are pushing to. It sends all the associated changed data and schema to the remote and updates the commit log to reflect the push.

Pull performs a fetch then a merge of the remote branch to your local branch. Essentially pull merges the changes on the remote branch into your local branch.

How to use remotes

A remote is the basis for all the distributed collaboration features of Dolt.

If you would like to make sure your local database can survive a destructive local operation, you create a remote on another machine and push your local Dolt database to it.

If you would like a Dolt database to be used by more than one person, you create and configure a remote and then push your local Dolt database to that remote. That person then can clone or pull the remote.

Difference between Git remotes and Dolt remotes

Dolt and Git remotes are conceptually the same: they coordinate distributed clones and let you clone, fetch, pull, and push.

Historically, Dolt remotes were backed by Dolt’s native remote storage (DoltHub / sql-server remotesapi, file:// directories, or cloud object stores), and you could not point at an existing Git repository and treat it as a remote.

Now, Dolt also supports using Git repositories as Dolt remotes. This lets you use a Git repository on disk or a hosted Git server (over HTTPS or SSH) as the backing store for a Dolt remote. Dolt stores its remote data under a Git ref (by default refs/dolt/data, configurable via --ref when adding / cloning).

This is separate from filesystem remotes: you still can’t use an existing Dolt working directory as a file:// remote. Use either a dedicated filesystem-remote directory, or a Git repo ending in .git for Git remotes.

For example, you can use GitHub via SSH ([email protected]:ORG/REPO.git) or HTTPS (https://github.com/ORG/REPO.git). For detailed examples, see .

Example

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

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 is a full-featured SQL database, akin to Postgres or MySQL.

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 databases and tables as you'd expect. Dolt implements all MySQL data types. Dolt supports secondary indexes. Dolt supports foreign key and check constraints. Dolt supports views, triggers, and procedures. Dolt implements users and grants 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 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 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

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 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

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

As an administrator user, you create users and roles using CREATE USER and CREATE ROLE statements. You grant permissions to users using GRANT statements. You can grant privileges to specific users but this is generally not advised. It is better to grant privileges to roles and then assign users roles using GRANT statements.

Difference between MySQL Users & Grants and Dolt Users & Grants

The goal is for Dolt users and grants to match MySQL users and grants exactly. As of now, Dolt users and grants are missing some functionality, like column-level privileges and restricted access to stored procedures. if you need more functionality.

Interaction with Dolt Version Control

The users and grants tables exist outside of Dolt in a separate database named mysql. We decided to implement users and grants this way to maintain MySQL compatibility. Thus, user and grant functionality is not versioned in Dolt.

Grants can only apply to traditional SQL access to Dolt tables as of now. Our roadmap includes adding grant functionality to version control operations. For instance, we think it is powerful to grant write permissions to a user on a specific branch.

Example

Create a user and grant permissions

Access data as a user

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 primary keys which also provide indexed lookups.

How to use Secondary Indexes

Add secondary indexes to columns that are often accessed with a where clause that you would like to return fast. For these columns you will need to tolerate slightly reduced insert and update performance. Additionally, you should have the disk space available to store the indexes.

You create indexes using the CREATE INDEX SQL statement.

Difference between MySQL Secondary Indexes and Dolt Secondary Indexes

Functionally, Dolt and MySQL indexes are equivalent.

Interaction with Dolt Version Control

Dolt indexes are versioned along with the core table they reference. Practically, this means querying a historical version is as fast as querying the current version because the index is intact for the historical version.

Dolt will merge indexes as part of a Dolt merge. This can be used to offload index creation to a branch or offline clone.

Example

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.

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

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.

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.

What is a pull request?

Pull requests are a way to propose changes to a database. A pull request is created from a 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 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.

What is a Constraint?

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

1. NOT NULL or

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:

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.

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?

• Are you worried about model reproducibility?

• Do different people or teams want to work on slightly different versions of the data?

• Are long running projects hard to pull off because of parallel data changes?

• Would data branches help?

• Do you want the ability to query or roll back to a previous version of the data instantly?

Dolt solves this by…

Traditional databases were built for a world of transactions and reports. Modern data science tools use data to create models that behave more like software than reports. Models produce user visible outputs and define application behavior. Tuning data to get the right model can be a lot like writing code.

The version control tools we use to build software apply to modern data science. Version control for data did not exist until Dolt, the first and only database you can , , and just like a Git repository.

Modern data science applications require model reproducibility, data quality, and multiple versions of data to perform at their best. Dolt allows for these capabilities directly in your database, in a most developers understand.

Dolt is used for model reproducibility. If you build a model from a version of the data, make a tag at that commit and refer to that tag in the model metadata. Some of our data and model quality control customers only use Dolt for this simple feature. Dolt shares storage between versions so you can store many more copies of the data using Dolt than say storing copies of the data in S3.

Dolt allows for human or automated review on data changes increasing data quality. If a bad change makes it through review simply . DoltHub, DoltLab, and the Hosted Dolt Workbench all implement workflow, the standard for human reviewing code changes. Extend that model to your data changes.

Dolt is the only database with and functionality. Branches allow for long running data projects. Want to add an additional feature to a model but don't want the new feature effecting the production model build? Make branch and run the project on that branch. Occasionally merge production data into that branch so you can stay in touch with changes there. Companies use Dolt branches to increase the number of parallel data projects by an order of magnitude.

Lastly, , , and can be used for model insights. Did Thursday's model perform better than Tuesday's but had the same model weights? Inspect the data diff to see what changed. Inspect the commit log to see where that new data came from.

Dolt replaces...

Unstructured files in cloud storage

It is common practice to store copies of training data or database backups in cloud storage for model reproducibility. A full copy of the data is stored for every training run. This can become quite expensive and limit the amount of models you can reproduce. Dolt stores only the differences between stored versions decreasing the cost of data storage. Additionally, Dolt can produce diffs between versions of training data producing novel model insights.

MySQL, Postgres, or other databases

Dolt can replace any database used to store and query data. Many of our customers switch from other OLTP databases like MySQL or Postgres to improve data and model quality through versioning. Customers have also switched to Dolt from document databases like MongoDB. Dolt's additional unique features like branches, diffs, and merges allow for human review of data changes and multiple parallel data projects.

Companies Doing This

Case Studies

Other Related Articles

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.

If you experience any issues using the supported SQL editors above, please so we can investigate and help.

Setting up Dolt with Tableplus

is the recommended SQL editor to use with Dolt. Let's clone a Dolt database and connect it to Tableplus. We have a that walks through this experience as well.

Step 1: Cloning a database.

Open your terminal and clone the following .

Now run: dolt sql -q 'show tables' and you should see the following output

Step 2: Setting up your server

Dolt's sql server can be started with one command.

The default configuration for our server uses a username of root and an empty password. You can configure access settings, logging, and timeouts by following additional documentation .

Step 3: Connecting our server with Tableplus

If you haven't already go ahead and install Tableplus from :

Click on create a new connection:

Hit MySQL in the selection box and fill in the following information. All the parameters are the same as for any MySQL database.

When you hit Test you should see all the boxes turn green like in the above image. Finally, hit connect to access the Dolt server.

Step 4: Writing queries with Tableplus

Let's start by selecting a database in the Dolt server and writing queries against it.

Select the ip_to_country database. You should see tables populate to the left like below.

Now click on the table IPv4ToCountry and see the screen populate. We just read from our Dolt database!