Did you know that you now have the ability to clone, branch, merge, push your database in a similar way that you can do that to your code? Are we not in for a treat? Indeed, we are.. So let us get into the party and get the music started.

From its GitHub space “Dolt is a SQL database that you can fork, clone, branch, merge, push and pull just like a git repository. Connect to Dolt just like any MySQL database to run queries or update the data using SQL commands. Use the command line interface to import CSV files, commit your changes, push them to remote, or merge your teammate’s changes. All the commands you know for Git work exactly the same for Dolt. Git versions files, Dolt versions tables. It’s like Git and MySQL had a baby!”

Features that work better in Dolt than in other databases

  • Snapshots: With Dolt snapshots are automatic: every commit is a snapshot you can refer to for backup, recovery, reproducible access or more
  • Time travel: You can query older history for free, out of the box with Dolt, and adds the ability to diff the values of rows in any two revisions.
  • Rollbacks: With Dolt, rollback is built in. Just dolt reset –hard HEAD and you’ve immediately undone the last 3 commits.

Installing Dolt – Git for SQL Database

And now onto the most favorite part where we get out hands rough with this shiny Git-like database. I know you are excited so let us commence.

Install on Linux From Latest Release

If you like living on the edge, you can install Dolt from its very latest release. On Linux run the command below in your fancy terminal to get the latest of Dolt fetched and installed. Make sure you have curl and wget installed.

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

The command above will download the latest dolt release and put it in /usr/local/bin/, which is in most Linux distributions already on $PATH.

You should see an output like below when the command is run

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   134  100   134    0     0     22      0  0:00:06  0:00:05  0:00:01    32
100   618  100   618    0     0     98      0  0:00:06  0:00:06 --:--:--    98
100  2971  100  2971    0     0    428      0  0:00:06  0:00:06 --:--:-- 10843
Downloading: https://github.com/dolthub/dolt/releases/download/v0.26.4/dolt-linux-amd64.tar.gz
Installing dolt, git-dolt and git-dolt-smudge to /usr/local/bin.

Install Latest Release on Mac from Homebrew

Good news for all Mac users is that Dolt is on Homebrew, and it is updated on every release. As you know, installing everything from Homebrew is pretty simple. And Dolt is no different. Open up your favorite terminal and run the command below.

brew install dolt

Install Latest Release on Windows using Chocolatey

You can install Dolt on Windows using Chocolatey in a simple fashion as it is done in Homebrew. In case you are not familiar with Chocolatey, check out the following post Manage Windows Applications from Command Prompt (CLI) with Chocolatey. Once you have Chocolatey setup, open up your PowerShell or Command Prompt and run the command below to get Dolt installed in your Windows.

choco install dolt

Install Latest Release on Windows using MSI Installer

In case you are the traditional Windows user and would like to install things the old school way, then you should smile because there in a MSI installer available for you to download and run.

Download the latest Microsoft Installer (.msi file) in Dolt releases page and run it. You should be good to go after that.

Installing From Source

If none of the above methods appeal to you and you are more of a source person, then that option is available for you. There is only one pre-requisite. That is you must have Go installed in your system. We have a few guides that will help you get Go installed. They include the following:

How To Install Go on CentOS 8 | CentOS 7
How To Install Go (Golang) On Fedora
How to Install Go (Golang) on Linux Mint
How To Install Go on CentOS 8 / RHEL 8

After you get your Golang working, clone Dolt’s git repository as shown below. Make sure you have git installed.

$ cd ~
$ git clone https://github.com/dolthub/dolt.git
Cloning into 'dolt'...
remote: Enumerating objects: 108334, done.
remote: Counting objects: 100% (3389/3389), done.
remote: Compressing objects: 100% (1452/1452), done.
remote: Total 108334 (delta 2130), reused 3042 (delta 1901), pack-reused 104945
Receiving objects: 100% (108334/108334), 122.18 MiB | 1.31 MiB/s, done.
Resolving deltas: 100% (72616/72616), done.

Navigate to the “go” directory

cd ~/dolt/go/

Then run the command below to get Dolt installed

go install ./cmd/dolt

You can confirm that Dolt is installed by running the command below

dolt version

dolt version 0.26.4

Putting Dolt to use

After the installations are done, the next important and productive thing to do is to put Dolt to work for us. And to do that, we are going to demonstrate a few examples here to get everyone up to speed and giving good foundation to build on more features that Dolt has to offer. Dolt has Dolt CLI and we are going to it much in this section. Before that, we are supposed to configure dolt.

Configure dolt with your username and email, which you will need to create commits just the same way you would do in git.

$ dolt config --global --add user.email [email protected]
Config successfully updated.
$ dolt config --global --add user.name GeeksAdmin
Config successfully updated.

Dolt CLI

The dolt CLI has the same commands as git, but with some extras. Running dolt without any arguments gives you the following output shared in the screenshot below:

dolt output

Let us get started

Thus far, we have Dolt installed and we have created username and email for our use. Next, let us create our first repository, storing ComputingForGeeks posts data.

$ mkdir computingforgeeks-posts && cd computingforgeeks-posts

Run “dolt init” to set up a new dolt repository, just like you do with git. Then run some SQL queries to insert data.

$ dolt init
Successfully initialized dolt data repository.

Create a table for the posts

dolt sql -q "create table computingforgeeks_posts ( id int, posts varchar(14), primary key (id) )"

Show the table if it is created

$ dolt sql -q "show tables"
+-------------------------+
| Table                   |
+-------------------------+
| computingforgeeks_posts |
+-------------------------+

Insert Values into the columns in the table

$ dolt sql -q "insert into computingforgeeks_posts (id, posts) values
(1, 'Automation'),
(2, 'DevOps'),
(3, 'Gadgets'),
(4, 'Linux'),
(5, 'Windows'),
(6, 'Databases'),
(7, 'Kubernetes'),
(8, 'Cloud'),
(9, 'Books'),
(10, 'Storage')"

Output:

Query OK, 10 rows affected

Use “dolt sql” to jump into a SQL shell, or run single queries with the -q option.

$ dolt sql -q "select * from computingforgeeks_posts"

+----+------------+
| id | posts      |
+----+------------+
| 1  | Automation |
| 2  | DevOps     |
| 3  | Gadgets    |
| 4  | Linux      |
| 5  | Windows    |
| 6  | Databases  |
| 7  | Kubernetes |
| 8  | Cloud      |
| 9  | Books      |
| 10 | Storage    |
+----+------------+

The queries works exactly like any other SQL System with filters and such

$ dolt sql -q "select * from computingforgeeks_posts where id=5"

+----+---------+
| id | posts   |
+----+---------+
| 5  | Windows |
+----+---------+

Add the new tables and commit them. Every command matches git exactly, but with tables instead of files. Simply replace “git” with “dolt“. You can see it in action below.

$ dolt add .
$ dolt commit -m "initial computingforgeeks posts data"
commit tsitks3g0qmlnn7f5ql9i18cgfc918dd
Author: GeeksAdmin <[email protected]>
Date:   Wed May 19 16:57:07 +0000 2021

$ dolt status
On branch master
nothing to commit, working tree clean

Next, let us update the tables with more SQL commands, and this time, we are going to explore the shell:

$ dolt sql
# Welcome to the DoltSQL shell.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
computingforgeeks_posts> update computingforgeeks_posts set posts = 'NFS' where id = 10;

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

Confirm that the changes were made:

$ dolt sql -q "select * from computingforgeeks_posts where id=10"
+----+-------+
| id | posts |
+----+-------+
| 10 | NFS   |
+----+-------+

See what you changed with dolt diff:

$ dolt diff
diff --dolt a/computingforgeeks_posts b/computingforgeeks_posts
--- a/computingforgeeks_posts @ 361qe208l8p5ppkhdmgea2u3gj90r499
+++ b/computingforgeeks_posts @ b0fhf1vbe343e5t7n49uk15nb6cfmt8a
+-----+----+---------+
|     | id | posts   |
+-----+----+---------+
|  <  | 10 | Storage |
|  >  | 10 | NFS     |
+-----+----+---------+

Then commit your changes once more with dolt add and dolt commit.

$ dolt add computingforgeeks_posts
$ dolt commit -m "Changed Storage to NFS"
commit pto33bsehie2tnpva47kpdt704n9i39r
Author: GeeksAdmin <[email protected]>
Date:   Wed May 19 17:05:47 +0000 2021

        Changed Storage to NFS

If you would wish to see all that you have been doing with Dolt for the day, you can check the entire logs as follows:

$ dolt log
commit pto33bsehie2tnpva47kpdt704n9i39r
Author: GeeksAdmin <[email protected]>
Date:   Wed May 19 17:05:47 +0000 2021

        Changed Storage to NFS

commit tsitks3g0qmlnn7f5ql9i18cgfc918dd
Author: GeeksAdmin <[email protected]>
Date:   Wed May 19 16:57:07 +0000 2021

        initial computingforgeeks posts data

commit u4vha31rke4vkcbeand2k6f4r00m24ms
Author: GeeksAdmin <[email protected]>
Date:   Wed May 19 16:41:31 +0000 2021

        Initialize data repository

Working with remote Repositories

Just like in Git where you can clone code from remote repositories, Dolt has the same concept built in. This feature enables you to set up automatically when you clone data from remote repositories. Let us give one remote repository a try.

$ dolt clone dolthub/corona-virus
cloning https://doltremoteapi.dolthub.com/dolthub/corona-virus
16,479 of 16,479 chunks complete. 0 chunks being downloaded currently.

Navigate into the new database “directory” and view the tables in it via “dolt sql

$ cd corona-virus
$ dolt sql
# Welcome to the DoltSQL shell.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
corona_virus>

Let us view the tables in the cloned corona_virus database

corona_virus> show tables;
+------------------------------------+
| Table                              |
+------------------------------------+
| case_details                       |
| cases                              |
| cases_by_age_range                 |
| cases_by_age_sex                   |
| cases_by_sex                       |
| characteristics_age                |
| characteristics_case_severity      |
| characteristics_comorbid_condition |
| characteristics_occupation         |
| characteristics_onset_date_range   |
| characteristics_province           |
| characteristics_sex                |
| characteristics_wuhan_exposed      |
| current                            |
| current_cases                      |
| current_deaths                     |
| current_recovered                  |
| deaths_by_age_range                |
| deaths_by_age_sex                  |
| deaths_by_sex                      |
| mortality_rate_by_age_range        |
| mortality_rate_by_age_sex          |
| mortality_rate_by_sex              |
| mortality_rates                    |
| places                             |
| time_series                        |
+------------------------------------+

As you can see, the database we cloned contains data and you can access them via normal SQL Queries so that you do not have to learn new query languages.

Importing data

Another beautiful thing about Dolt is its ability to import data in the forms you are used to. Forms such as CSV or JSON can be easily imported in your data using the dolt table import command. You can use “dolt table import -u” to add data to an existing table, or “dolt table import -c” to create a new one.

Let us create a simple csv content to add to our posts table

$ vim newdata.csv
id,posts
12,Ansible
13,Terraform
14,Vagrant

Once done, let us import the data to be part of our table.

$ dolt table import -c -pk=id computingforgeeks_posts newdata.csv
Rows Processed: 3, Additions: 3, Modifications: 0, Had No Effect: 0Import completed successfully.

Let us see if our update was successful. Run “dolt sql” to get into dolt’s sql shell then select everything in that table. As you can see from the illustration below, the new records have been added in our table. Once that has been done, we can go ahead and add, commit and push our changes just like in git.

$ dolt sql
# Welcome to the DoltSQL shell.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.

computingforgeeks_posts> select * from computingforgeeks_posts;
+----+------------+
| id | posts      |
+----+------------+
| 1  | Automation |
| 2  | DevOps     |
| 3  | Gadgets    |
| 4  | Linux      |
| 5  | Windows    |
| 6  | Databases  |
| 7  | Kubernetes |
| 8  | Cloud      |
| 9  | Books      |
| 10 | NFS        |
| 12 | Ansible    |
| 13 | Terraform  |
| 14 | Vagrant    |
+----+------------+

Branch and merge

Just like with git, it’s a good idea to make changes on your own branch, then merge them back to master. The dolt checkout command works exactly the same as git checkout.

$ dolt checkout -b <branch>

The merge command works the same too.

$ dolt merge <branch>

Last Words

Without saying much, it is evident that Dolt is amazing. It is now possible to version, commit, track and monitor every detail of your database as well as easily share it to the world via DoltHub. If you liked Dolt, keep preach its marvels to others, support them and enjoy the magic it proffers to the world.

Before signing off, let me take this brief opportunity to thank you for reading through. Other guides you might enjoy include the following:

SQL from scratch: how to get started learning databases?

Backup MySQL databases to Amazon S3

Install MariaDB Galera Cluster on Ubuntu 20.04 with ProxySQL

Enable SSL Encryption and SCRAM-SHA-256 Password Authentication in PostgreSQL

Your support is our everlasting motivation,
that cup of coffee is what keeps us going!


As we continue to grow, we would wish to reach and impact more people who visit and take advantage of the guides we have on our blog. This is a big task for us and we are so far extremely grateful for the kind people who have shown amazing support for our work over the time we have been online.

Thank You for your support as we work to give you the best of guides and articles. Click below to buy us a coffee.

LEAVE A REPLY

Please enter your comment!
Please enter your name here