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:
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
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.
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 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')"
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
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
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
Date: Wed May 19 17:05:47 +0000 2021 Changed Storage to NFS commit tsitks3g0qmlnn7f5ql9i18cgfc918dd Author: GeeksAdmin Date: Wed May 19 16:57:07 +0000 2021 initial computingforgeeks posts data commit u4vha31rke4vkcbeand2k6f4r00m24ms Author: GeeksAdmin 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.
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
The merge command works the same too.
$ dolt merge
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.