AUTO_INCREMENT vs UUID Primary Keys
Dolt is the first and only version controlled SQL database. Dolt supports branches, merges, and clones. Astute Dolt users have long noticed that Dolt branches and clones do not play well with AUTO_INCREMENT primary keys. The Dolt recommended alternative to AUTO_INCREMENT primary keys is Universal Unique Identifier (UUID) keys. In this blog article, we will show how both AUTO_INCREMENT and UUID keys work with Dolt branches and clones.
AUTO_INCREMENT
AUTO_INCREMENT primary keys are a way to have your database generate unique keys for your rows by making the key the next number in an integer sequence. AUTO_INCREMENT is very popular among ORMs, including the very popular Python ORM, SQLAlchemy.
Dolt released support for AUTO_INCREMENT way back in November 2020. Originally, inserting rows with AUTO_INCREMENT keys on different branches and then trying to merge those branches would generate conflicts.
Rows with different values were assigned the same primary key, the next value in the sequence. Dolt performs cell-wise data merges with rows identified by primary key across versions. Consider a database with an AUTO_INCREMENT counter of 1 on the main branch. Two branches are created. One branch inserts a row with data A, B, C and gets the next AUTO_INCREMENT key 2. The other branch inserts a row with data X, Y, Z and also gets the AUTO_INCREMENT key 2. If you try and merge both these branches into main, the second merge will generate a conflict because two rows with key 2 contain different data.
To alleviate this problem, we added support for sharing the current AUTO_INCREMENT value across branches in August 2022. But, Dolt is a decentralized version control system so each individual instance of Dolt knows nothing of its clones. Merging AUTO_INCREMENT keys created on different clones can still cause conflicts.
UUID
As an alternative to AUTO_INCREMENT, we recommend using universal unique identifiers (UUIDs) as keys in Dolt. A UUID is a randomly generated 128-bit value. You usually see UUIDs represented in 8-4-4-4-12 format like 00000000-0000-0000-0000-000000000000. They're so big that the chance for collisions is very small. Because of this small collision chance, UUID keys make a lot more sense to use as automatically generated keys for Dolt. To automatically generate a UUID key, you use the SQL id varchar(36) default(uuid()) primary key to define your key column when creating your table. Note, the use of default with the uuid() function. UUID keys produce conflict-free merges on branches and clones.
Example
Let's start by creating a simple database with two tables to show off how AUTO_INCREMENT and UUID keys work in practice. The database is pushed to DoltHub if you want to play along.
To create the database I'm going to use the Dolt CLI. As an aside, Dolt is both a version controlled database and Git for data. The CLI shows off Dolt in its offline, Git for data form. You could also start a dolt sql-server and connect with any MySQL client to execute the below SQL.
First I initialize my Dolt database.
$ mkdir autoincrement_vs_uuid
$ cd autoincrement_vs_uuid
$ dolt init --fun
Successfully initialized dolt data repository.Then, I create a table keyed by an auto_increment primary key called t_autoincrement.
$ dolt sql -q "create table t_autoincrement(id int auto_increment primary key, words varchar(100))" And then I create another table keyed by a UUID. The best way to do this is by setting a default calling the uuid() function. The uuid() function returns a random UUID so whenever you insert a row the key will be a random UUID. The type of a UUID is varchar(36), 32 characters plus 4 dashes.
$ dolt sql -q "create table t_uuid(id varchar(36) default(uuid()) primary key, words varchar(100))" Now I'll insert a row into the AUTO_INCREMENT table without specifying a key.
$ dolt sql -q "insert into t_autoincrement(words) values ('I\'m just counting')"
Query OK, 1 row affected (0.00 sec)
$ dolt sql -q "select * from t_autoincrement"
+----+-------------------+
| id | words |
+----+-------------------+
| 1 | I'm just counting |
+----+-------------------+And I'll insert into UUID keyed table in the same way.
$ dolt sql -q "insert into t_uuid(words) values ('Woah. Random numbers')"
Query OK, 1 row affected (0.00 sec)
$ dolt sql -q "select * from t_uuid"
+--------------------------------------+----------------------+
| id | words |
+--------------------------------------+----------------------+
| 2cc171f8-54bb-4829-963d-0604d6685ca3 | Woah. Random numbers |
+--------------------------------------+----------------------+Both tables automatically generate a random key for the inserted row. My application doesn't need to worry about keying the data. But which is better? Please read on.
Finally, I'll create a Dolt commit and push to DoltHub so I can show off merges later.
$ dolt commit -Am "Seeded database"
commit et72ecm10rhn2epvp7d3ml39ursm6r84 (HEAD -> main)
Author: timsehn <tim@dolthub.com>
Date: Fri Oct 13 14:02:29 -0700 2023
Seeded database
$ dolt remote add origin timsehn/autoincrement_vs_uuid
$ dolt push
/ Uploading...Auto Increment Merges
Now, let's insert rows on two branches for the AUTO_INCREMENT keyed table, one using a single Dolt server and another using DoltHub and two clones. Then, we'll attempt to merge in both those scenarios.
Merge Branches
First, I checkout a branch, insert a row on the t_autoincrement table, and then make a Dolt commit.
$ dolt checkout -b insert-row
Switched to branch 'insert-row'
$ dolt sql -q "insert into t_autoincrement(words) values ('Look ma! No conflicts')"
Query OK, 1 row affected (0.00 sec)
$ dolt commit -am "Inserted row on a branch"
commit t12bk74nsmb832d0hdgr73urgb7cqka3 (HEAD -> insert-row)
Author: timsehn <tim@dolthub.com>
Date: Mon Oct 16 12:21:16 -0700 2023
Inserted row on a branch
Now I checkout the main branch, insert another row, and make a Dolt commit.
$ dolt checkout main
Switched to branch 'main'
$ dolt sql -q "insert into t_autoincrement(words) values ('I see you. Very impressive.')"
Query OK, 1 row affected (0.00 sec)
$ dolt commit -am "Inserted row on main"
commit je52i96293g6bd1e0e560lo97sjbk2t9 (HEAD -> main)
Author: timsehn <tim@dolthub.com>
Date: Mon Oct 16 12:21:59 -0700 2023
Inserted row on main
Now the payoff. Will it merge?
$ dolt merge insert-row
Updating jmtudpcskl6dgn02rsgie27jd5or9d6u..t12bk74nsmb832d0hdgr73urgb7cqka3
commit jmtudpcskl6dgn02rsgie27jd5or9d6u (HEAD -> main)
Merge: je52i96293g6bd1e0e560lo97sjbk2t9 t12bk74nsmb832d0hdgr73urgb7cqka3
Author: timsehn <tim@dolthub.com>
Date: Mon Oct 16 12:22:12 -0700 2023
Merge branch 'insert-row' into main
t_autoincrement | 1 +
1 tables changed, 1 rows added(+), 0 rows modified(*), 0 rows deleted(-)
$ dolt sql -q "select * from t_autoincrement"
+----+-----------------------------+
| id | words |
+----+-----------------------------+
| 1 | I'm just counting |
| 2 | Look ma! No conflicts |
| 3 | I see you. Very impressive. |
+----+-----------------------------+As you can see the AUTO_INCREMENT counter is shared between branches, preventing merge conflicts. The insert on the branch gets id 2 and the next insert on main gets id 3.
Merge Clones
Let's make a clone called autoincrement_vs_uuid_clone.
$ cd ..
$ dolt clone timsehn/autoincrement_vs_uuid autoincrement_vs_uuid_clone
cloning https://doltremoteapi.dolthub.com/timsehn/autoincrement_vs_uuid
$ cd autoincrement_vs_uuid_clone
$ dolt sql -q "select * from t_autoincrement"
+----+-------------------+
| id | words |
+----+-------------------+
| 1 | I'm just counting |
+----+-------------------+Now, we insert a row on that clone's main branch, commit, and push back to DoltHub.
$ dolt sql -q "insert into t_autoincrement(words) values ('Clones make conflicts')"
Query OK, 1 row affected (0.00 sec)
$ dolt commit -am "First to DoltHub wins"
commit qohu3bup02bgupjlipn5v7j4751sjnjl (HEAD -> main)
Author: timsehn <tim@dolthub.com>
Date: Mon Oct 16 12:31:20 -0700 2023
First to DoltHub wins
$ dolt push
| Uploading...
We go back to the original database, fetch, and try to merge the remote main branch into our main branch.
$ cd ../autoincrement_vs_uuid
$ dolt fetch
$ dolt branch -va
insert-row t12bk74nsmb832d0hdgr73urgb7cqka3
* main jmtudpcskl6dgn02rsgie27jd5or9d6u
remotes/origin/main qohu3bup02bgupjlipn5v7j4751sjnjl
$ dolt merge remotes/origin/main
merge finished, but failed to get hash of merge ref
error getting hash of ref 'remotes/origin/main': invalid ref spec
commit jmtudpcskl6dgn02rsgie27jd5or9d6u (HEAD -> main)
Merge: je52i96293g6bd1e0e560lo97sjbk2t9 t12bk74nsmb832d0hdgr73urgb7cqka3
Author: timsehn <tim@dolthub.com>
Date: Mon Oct 16 12:22:12 -0700 2023
Merge branch 'insert-row' into main
Auto-merging t_autoincrement
CONFLICT (content): Merge conflict in t_autoincrement
Automatic merge failed; 1 table(s) are unmerged.
Use 'dolt conflicts' to investigate and resolve conflicts.
$ dolt conflicts cat t_autoincrement
+-----+--------+----+-----------------------+
| | | id | words |
+-----+--------+----+-----------------------+
| + | ours | 2 | Look ma! No conflicts |
| + | theirs | 2 | Clones make conflicts |
+-----+--------+----+-----------------------+As you can see the AUTO_INCREMENT counter is not shared across clones. Two rows have been assigned the id 2. Let's resolve the conflicts by keeping what we have locally and commit so we can use the database again for UUID keys.
$ dolt conflicts resolve --ours t_autoincrement
$ dolt commit -am "Successful merge"
commit 9m70onpsb1qucdfrojdq9g8qujnhgsje (HEAD -> main)
Merge: 2qlp1e2v4fnelu5e1763bci15j31tpkl k0kf8qmi02g75kskb7p1ba3mvonqn07q
Author: timsehn <tim@dolthub.com>
Date: Mon Oct 16 13:10:00 -0700 2023
Successful merge
UUID Merges
Finally, let's see how UUID keys merge under the same scenarios.
Merge Branches
Following the same process as above, we switch back to our insert-row branch, insert a row in the t_uuid table, and commit.
$ dolt checkout insert-row
Switched to branch 'insert-row'
$ dolt sql -q "insert into t_uuid(words) values ('Random numbers are cool.')"
Query OK, 1 row affected (0.00 sec)
$ dolt commit -am "Inserted uuid row on a branch"
commit bqvnkprdu2mfoqh8n5c7r149ju5j1iq9 (HEAD -> insert-row)
Author: timsehn <tim@dolthub.com>
Date: Mon Oct 16 13:05:27 -0700 2023
Inserted uuid row on a branch
Then we switch back to main, insert another row, and commit.
$ dolt checkout main
Switched to branch 'main'
$ dolt sql -q "insert into t_uuid(words) values ('So random. No way to collide')"
Query OK, 1 row affected (0.00 sec)
$ dolt commit -am "Inserted uuid row on main"
commit agccbrnj79g69sgslh12dpg1kq7kj36v (HEAD -> main)
Author: timsehn <tim@dolthub.com>
Date: Mon Oct 16 13:05:57 -0700 2023
Inserted uuid row on main
Finally, we do a merge and expect no conflicts, just like in the AUTO_INCREMENT example.
$ dolt merge insert-row
Updating 2qlp1e2v4fnelu5e1763bci15j31tpkl..bqvnkprdu2mfoqh8n5c7r149ju5j1iq9
commit 2qlp1e2v4fnelu5e1763bci15j31tpkl (HEAD -> main)
Merge: agccbrnj79g69sgslh12dpg1kq7kj36v bqvnkprdu2mfoqh8n5c7r149ju5j1iq9
Author: timsehn <tim@dolthub.com>
Date: Mon Oct 16 13:06:07 -0700 2023
Merge branch 'insert-row' into main
t_uuid | 1 +
1 tables changed, 1 rows added(+), 0 rows modified(*), 0 rows deleted(-)
$ dolt sql -q "select * from t_uuid"
+--------------------------------------+------------------------------+
| id | words |
+--------------------------------------+------------------------------+
| 2cc171f8-54bb-4829-963d-0604d6685ca3 | Woah. Random numbers |
| 9ed40203-c484-4f07-8b54-163cd9f4b581 | So random. No way to collide |
| a97cf1a7-539b-4bb4-822a-803ab94b7d89 | Random numbers are cool. |
+--------------------------------------+------------------------------+Everything merged as expected.
Merge Clones
Now, where decentralized merge of AUTO_INCREMENT keys failed, we expect decentralized merge of UUID keys to succeed.
We go back to our clone, insert a row, commit, and push.
$ cd ../autoincrement_vs_uuid_clone
$ dolt sql -q "insert into t_uuid(words) values ('Even clones can insert with impunity')"
Query OK, 1 row affected (0.00 sec)
$ dolt commit -am "Inserted uuid row on a clone"
commit k0kf8qmi02g75kskb7p1ba3mvonqn07q (HEAD -> main)
Author: timsehn <tim@dolthub.com>
Date: Mon Oct 16 13:07:42 -0700 2023
Inserted uuid row on a clone
$ dolt push
\ Uploading...Now back to our original database. We fetch and attempt to merge.
$ cd ../autoincrement_vs_uuid
$ dolt fetch
$ dolt merge remotes/origin/main
commit 2qlp1e2v4fnelu5e1763bci15j31tpkl (HEAD -> main)
Merge: agccbrnj79g69sgslh12dpg1kq7kj36v bqvnkprdu2mfoqh8n5c7r149ju5j1iq9
Author: timsehn <tim@dolthub.com>
Date: Mon Oct 16 13:06:07 -0700 2023
Merge branch 'insert-row' into main
t_uuid | 1 +
1 tables changed, 1 rows added(+), 0 rows modified(*), 0 rows deleted(-)The t_uuid table merged without conflicts!
$ dolt sql -q "select * from t_uuid"
+--------------------------------------+--------------------------------------+
| id | words |
+--------------------------------------+--------------------------------------+
| 2cc171f8-54bb-4829-963d-0604d6685ca3 | Woah. Random numbers |
| 2fbae494-6d84-4d11-b14c-511d33d23110 | Even clones can insert with impunity |
| 9ed40203-c484-4f07-8b54-163cd9f4b581 | So random. No way to collide |
| a97cf1a7-539b-4bb4-822a-803ab94b7d89 | Random numbers are cool. |
+--------------------------------------+--------------------------------------+
As you can see, we have the resulting database on DoltHub. The commit graph shows our progress.
Conclusion
In Dolt, we've made the AUTO_INCREMENT counter shared across branches to minimize merge conflicts. This approach does not work for clones. We recommend using UUID keys instead. Curious about what other problems we're solving in the world's first version controlled SQL database? Come by our Discord to discuss.