Learn how to – Install and Use Dolt – Git for SQL Database.
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 doltInstall 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 doltInstall 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/doltYou can confirm that Dolt is installed by running the command below
dolt version
dolt version 0.26.4Putting 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 user@ownyourlife.com.ng
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:
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 learnhowto-posts && cd learnhowto-postsRun “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 learnhowto_posts ( id int, posts varchar(14), primary key (id) )"Show the table if it is created
$ dolt sql -q "show tables"
+-------------------------+
| Table |
+-------------------------+
| learnhowto_posts |
+-------------------------+Insert Values into the columns in the table
$ dolt sql -q "insert into learnhowto_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 affectedUse “dolt sql” to jump into a SQL shell, or run single queries with the -q option.
$ dolt sql -q "select * from learnhowto_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 learnhowto_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 learnhowto posts data"
commit tsitks3g0qmlnn7f5ql9i18cgfc918dd
Author: GeeksAdmin <user@ownyourlife.com.ng>
Date: Wed May 19 16:57:07 +0000 2021
$ dolt status
On branch master
nothing to commit, working tree cleanNext, 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.
learnhowto_posts> update learnhowto_posts set posts = 'NFS' where id = 10;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0Confirm that the changes were made:
$ dolt sql -q "select * from learnhowto_posts where id=10"
+----+-------+
| id | posts |
+----+-------+
| 10 | NFS |
+----+-------+See what you changed with dolt diff:
$ dolt diff
diff --dolt a/learnhowto_posts b/learnhowto_posts
--- a/learnhowto_posts @ 361qe208l8p5ppkhdmgea2u3gj90r499
+++ b/learnhowto_posts @ b0fhf1vbe343e5t7n49uk15nb6cfmt8a
+-----+----+---------+
| | id | posts |
+-----+----+---------+
| < | 10 | Storage |
| > | 10 | NFS |
+-----+----+---------+Then commit your changes once more with dolt add and dolt commit.
$ dolt add learnhowto_posts
$ dolt commit -m "Changed Storage to NFS"
commit pto33bsehie2tnpva47kpdt704n9i39r
Author: GeeksAdmin <user@ownyourlife.com.ng>
Date: Wed May 19 17:05:47 +0000 2021
Changed Storage to NFSIf 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 <user@ownyourlife.com.ng>
Date: Wed May 19 17:05:47 +0000 2021
Changed Storage to NFS
commit tsitks3g0qmlnn7f5ql9i18cgfc918dd
Author: GeeksAdmin <user@ownyourlife.com.ng>
Date: Wed May 19 16:57:07 +0000 2021
initial learnhowto posts data
commit u4vha31rke4vkcbeand2k6f4r00m24ms
Author: GeeksAdmin <user@ownyourlife.com.ng>
Date: Wed May 19 16:41:31 +0000 2021
Initialize data repositoryWorking 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,VagrantOnce done, let us import the data to be part of our table.
$ dolt table import -c -pk=id learnhowto_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.
learnhowto_posts> select * from learnhowto_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.
Git Learning Video Courses
- The Git & Github Bootcamp
- Git Going Fast: One Hour Git Crash Course
- Git Complete: The definitive, step-by-step guide to Git
- Complete Git Guide: Understand and master Git and GitHub
- Git & GitHub For Beginners – Master Git and GitHub
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
Leave a Reply
You must be logged in to post a comment.