7 min. read

October 11, 2022

Dolt - A SQL Database that Works like Git

Y'all know Git, but what about Dolt? A SQL-based database that works just like Git repositories.

Chameera Dulanga, Associate Technical Lead

What if I told you there is a way to manage your SQL databases like Git repositories? I'm sure you'll be excited since it will give you significant flexibility in database management.

So, in this article, I will introduce you to Dolt, an SQL-based database that allows you to fork, pull, push, and clone, similar to a Git repository.

What is Dolt?

As mentioned, Dolt is an SQL-based database that allows you to maintain your database like a Git repository. It combines all the great features of Git and SQL in order to give you an amazing experience. Dolt comes with a powerful CLI similar to Git, and you can apply your knowledge in Git to easily commit, merge, and push database changes with your teammates.

You don't need to make any changes to your infrastructure to use Dolt. You can just use it as a normal database while benefiting from its amazing features:

Features of Dolt

Versioning system similar to Git.
Works with standard SQL connectors.
Comes with a powerful CLI.
Allows exploring the complete history of your data.
Instant rollback to any commit.
DoltHub
provides a web-based UI to collaborate on Dolt databases.
DoltLab
allows the creation of self-hosted DoltHubs.
Built-in backup and monitoring.
Make money through DoltHub data bounties.

Git Commands - Best Cheat Sheet for all Developers

7 Ways ChatGPT Can Help Developers

How does Dolt Work?

Dolt works similarly to any other SQL database. You can use all the usual SQL commands and features like tables, stored procedures, and functions in Dolt. In addition, it brings the power of Git to your hands by supporting all the Git commands.

In Git, you perform actions on files. When it comes to Dolt, all the changes you make will be applied to Dolt tables. For example, a write operation on your data is considered a commit or a merge. Dolt handles write operations through stored procedures and will present the difference between data versions cell-wise.

How to Install Dolt?

Installing Dolt is pretty straightforward. There are multiple methods to install Dolt based on your operating system.

Windows

Download .msi file from Dolt releases
and install it.
Using Chocolatey - choco install dolt
Using Winget - winget install dolt

Mac

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

Linux

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

Note - Make sure to include the installation path into the PATH environment variable before using Dolt.

After installation, you can type dolt in your command line and verify the installation.

Want to know more about the origins of React.js? Check out our React Documentary!

Getting Started with Dolt

Dolt provides four main functionalities for you. Each has different ways to get started, and you need to choose one based on your requirements. This section will discuss what you need to do before starting each approach.

1. Running Dolt as a MySQL-Compatible Server

The first approach you can take with Dolt is to run it as a MySQL-compatible server on your host, similar to running a MySQL or Postgres database locally but with Git features.

Step 1 - Create a File Directory for the Database

First, you need to create a dedicated folder to keep Dolt databases. Once the folder is created, you can use it as the root folder to run all the Dolt CLI commands.

Step 2 - Starting and Connecting to a MySQL Database Server

Secondly, you need to start a MySQL server and connect to it. You can use dolt sql-server command to start the MySQL server on port 3306.

After that, open a new command line in the newly created folder and run dolt sql-client --user <username> to connect to the server.

Step 3 - Create a New Database Schema

Now, you can create a new table in the Dolt database using create database <database-name> command. Then, you can switch to the new database using use <database-name> command and create new tables using regular MySQL syntax as shown below:

// Create Database create database dolt_example; // Select Database use dolt_example; // Create Tables create table writers ( id int, first_name varchar(255), last_name varchar(255), age int, primary key(id) ); create table articles ( id int, title varchar(255), writer_id int, primary key(id), foreign key (writer_id) references writers(id) ); // View Table Listshow tables; +------------------------+| Tables_in_dolt_example | +------------------------+| articles || writers | +------------------------+

After that, you can treat it as a normal MySQL database and perform the usual SQL operations alongside Git commands.

2. Using Dolt CLI to Version Control Data

As mentioned, Dolt CLI supports all the Git commands. So, you can use Dolt CLI to create a repository, import data, and manage it like a Git repository.

But, before that, you need to set a username and email using the below commands:

dolt config --global --add user.name <your name> dolt config --global --add user.email <your email>

After that, you can initialize a new Dolt repository using the dolt init command.

>> dolt init >> Successfully initialized dolt data repository.

You can find more information on using Dolt for data version control in their documentation.

DoltHub is a web-based tool that allows you to find public databases created by the Dolt community. You can clone these databases, create branches, make changes, create commits, and pull requests using Dolt CLI commands.

For example, you can clone a database from DoltHub using the dolt clone <database-name> command. I'll explain more about these commands in the next section. Meanwhile, you can find more information on DoltHub from their documentation.

4. Earn Money from DoltHub Data Bounties

DoltHub data bounties are a great way to earn some extra cash while doing what you love. Data bounties are the rewards paid by organisations for sourcing all, or part of a specific dataset. You'll get paid once you find the data specified in the bounty.

To get started with data bounties, you must have a DoltHub account. Once you register and log into your account, you'll see a list of available bounties on your profile page. Please check out the DoltHub documentation for more details on data bounties.

Discover how much data scientists in Europe make.

Discover how much data engineers in Europe make.

Using Git Features in Dolt

Now, let's discuss how we can use Git features with Dolt. The list below contains some of the most common Git commands with examples of using them with Dolt.

Watch GitHub CEO Thomas Dohmke playing around with... LEGOS.

1. Making a Dolt Commit

Dolt commits are pretty similar to Git commits. You can use Dolt commits to go back in time to compare or restore a specified commit.

For example, consider a scenario where you need to commit two newly created tables named writers and articles. In Git, we use git add command to stage the changes before committing. Similarly, we need to use the dolt_add command in Dolt. The below command will stage the two newly created tables:

call dolt_add('writers', 'articles');

Then, you can commit the changes with a commit message using the dolt_commit command. Similar to Git, you need to enter a commit message with the commit.

call dolt_commit('-m', 'Adding 2 new tables');

As you can see, the Dolt commands are identical to Git commands, and you can easily get hold of them.

2. Comparing the Difference Between Two Commits

Now, suppose that you have populated the writers' table with data and need to see the difference between the current version and the previous before committing. For that, you can use dolt_status and dolt_diff_<table-name> commands.

dolt_status command will give you the status of all the tables as seen below:

select * from dolt_status;

dolt_diff_<tablename> command can be used to view table level differences. It will give you the previous and updated values of each row in the table alongside the commit details.

select * from dolt_diff_writers;

3. Reverting a Change

Reverting a change in a normal MySQL database can be a hectic task. However, Dolt makes things easier for you by providing two commands to reset and revert database changes.

You can use dolt_reset command to recover it.

// "Hard" resets the working tables and staged tables call dolt_reset('--hard'); call dolt_reset('--hard', '<branch-nameorcomment>'); // "Soft" resets the table by removing all staged changes. call dolt_reset('<table-name>');

For example, if you accidentally delete a database table, you can recover it using call dolt_reset(--hard) command.

Similarly, you can use dolt_revert command to revert the changes from a specified commit or set of commits.

call dolt_revert('<commit-hash>');

Note - The only change you can't revert in Dolt is the database deletion.

4. Creating/Switching Branches

Creating and switching branches in Dolt is exactly similar to Git. You can use dolt_checkout() command to create new branches and switch to existing branches like the below:

// Use -b optiontocreate a new branch call dolt_checkout('-b', '<branch-name>'); // Switch to an existing branch call dolt_checkout('<branch-name>');

You can find more on Dolt branching here.

5. Merging Branches

Suppose you have made some changes in a separate branch and need to merge it with the main branch. For that, Dolt provides a command named dolt_merge(<branch-name>).

First, you need to checkout to the main branch using call dolt_checkout('main'); command. Then, you can use call dolt_merge(<branch-name>) to merge the branch.

You can find more on the Dolt branch merging here.

In addition to the above-discussed features, Dolt has a set of stored procedures and functions for version control purposes. You can find more about them in their documentation.

Conclusion

Dolt is an exciting introduction to database management. It has completely revolutionized traditional SQL-based databases by integrating Git features. Hence, it can be a perfect database solution when working with data sharing, machine learning, data ingestion, or when you need to version your application data.

On the other hand, using Dolt in production-level applications can have some significant drawbacks. Compared to other database solutions, it lacks community support and recognition since it is still new to the competition. Also, many advanced features of Dolt are yet to be released. So, you must carefully review their documentation and decide if it fulfills the project requirements before using it.

Thank you for reading.