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

Chameera Dulanga, Editor of Enlear Academy

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

    .

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.

dolt1

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.

3. Sharing Data via DoltHub

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.

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.

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');

dolt2

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');

dolt3

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;

dolt4

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;

dolt5

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.

dolt6

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.

dolt7

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.

Looking to find new roles that match your ambitions? Honeypot is Europe's job platform for developers and data specialists. Sign up today and get offers with salary and tech stack up front. (P.S. Honeypot is always free for developers.)