Dolt CLI in SQL - Update
Dolt is a SQL database with Git-style versioning. In a previous post we discussed the need to introduce Dolt CLI functions in SQL. We believe that version control is something that can be native to your SQL workflow. This allows for possibilities such as:
- Merge and diff as application components. That means you can render diffs and merges in your application.
- Immediate versioning. Instantly create Commits and Branches via SQL to build a robust database lineage.
- Complete integration with Database GUI tools. You don't have leave apps like Tableplus to do your Dolt operations.
This blog post provides an update on the progress we made so far.
The Functions Added
- DOLT_COMMIT
- DOLT_ADD
- DOLT_MERGE
- DOLT_CHECKOUT
We even added a new system table dolt_status that allows you to see which tables are modified in the current session.
These functions should feel quite familiar and completely match the CLI behavior. It's important to note that these functions do require autocommit to be on. This is because each of the functions require updated root values to perform correctly. We plan to include support in the future for when autocommit is off.
Usage
Let's go through an example to see these functions in action.
Let's clone the following test repo and spin up the sql shell with dolt sql.
dolt clone vinai/iris-simple
cd iris
dolt sqlWe have the following table in iris and we can see its structure.
# Welcome to the DoltSQL shell.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
iris_simple> show tables;
+-------+
| Table |
+-------+
| iris |
+-------+
iris_simple> describe iris;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| sample | int unsigned | NO | PRI | | |
| sepal_length | float | NO | | | |
| sepal_width | float | NO | | | |
| petal_length | float | NO | | | |
| petal_width | float | NO | | | |
| species | longtext | NO | | | |
+--------------+--------------+------+-----+---------+-------+
iris_simple>Let's go through the process of creating a branch, making a change, and merging to master.
iris_simple> SELECT DOLT_CHECKOUT('-b', 'add-samples');
+-----------------------------------+
| DOLT_CHECKOUT("-b","add-samples") |
+-----------------------------------+
| 0 |
+-----------------------------------+
iris_simple> INSERT INTO iris VALUES (151,3.14,3.14,3.14,3.14,"virginica");
Query OK, 1 row affected
iris_simple> select * from dolt_status;
+------------+--------+----------+
| table_name | staged | status |
+------------+--------+----------+
| iris | false | modified |
+------------+--------+----------+In this part we just checked out to a new branch and inserted a new value. We can see what tables we modified so far
by querying the dolt_status table. Let's now make a commit, checkout back to master, and merge.
iris_simple> SELECT DOLT_COMMIT('-a', '-m', 'add new samples');
+----------------------------------+
| commit_hash |
+----------------------------------+
| ar4r0c99v4vsrjntiano5rsa0c52m7m0 |
+----------------------------------+
iris_simple> SELECT DOLT_CHECKOUT('master');
+-------------------------+
| DOLT_CHECKOUT("master") |
+-------------------------+
| 0 |
+-------------------------+
iris_simple> SELECT DOLT_MERGE('add-samples');
+----------------------------------+
| DOLT_MERGE("add-samples") |
+----------------------------------+
| ar4r0c99v4vsrjntiano5rsa0c52m7m0 |
+----------------------------------+
iris_simple> select * from iris where sample=151;
+--------+--------------+-------------+--------------+-------------+-----------+
| sample | sepal_length | sepal_width | petal_length | petal_width | species |
+--------+--------------+-------------+--------------+-------------+-----------+
| 151 | 3.14 | 3.14 | 3.14 | 3.14 | virginica |
+--------+--------------+-------------+--------------+-------------+-----------+Looks like our changes successfully merged into master! Let's query our status table again to double check that it is a clean merge.
iris_simple> select * from dolt_status;
+------------+--------+--------+
| table_name | staged | status |
+------------+--------+--------+
+------------+--------+--------+Here's the link to the full docs
Conclusion
In this blog post we discussed how to use the newest DOLT SQL functions. Our goal is to keep shipping SQL functions that match CLI functionality. The next functions we see coming out are:
- DOLT_PULL()
- DOLT_PUSH()
- DOLT_CONFLICTS()
- dolt_remotes (a system table to manage remotes)
Soon you'll never have to leave SQL land to get full Dolt functionality. In the future, as we continue to chart towards full MySQL compatibility, there's no reason why any application can't use Dolt as an enterprise scale DB!
If you have any opinions on what you would like to see next, come chat with us in our Discord Channel!