DoltDB is the world's first fully-versioned relational database. You can branch, merge, diff, push, and pull your relational data in the same ways Git allows you to work with source code files. If you're curious what sorts of use cases that enables, check out some of the many ways customers are already using Dolt.

This blog post announces the new last_insert_uuid() function, that makes using generated UUIDs in your primary keys even easier than before. We'll explain why we encourage customers to use UUIDs over AUTO_INCREMENT columns, and show a few examples of using the new last_insert_uuid() function.

The case against AUTO_INCREMENT

AUTO_INCREMENT has traditionally been a very useful and widely used feature in MySQL databases. Being able to automatically generate a monotonically increasing ID as rows are inserted into a table is pretty helpful and works well with traditional databases. However, once you get into the world of distributed databases, with forks and clones, the AUTO_INCREMENT concept can quickly cause problems. The issue is that when you clone or fork a Dolt database, that database needs to work independently of all other clones and forks, and the behavior of AUTO_INCREMENT doesn't allow for this –when you insert a row with an AUTO_INCREMENT column, you aren't going to get a globally unique ID. When it comes time to merge your changes back into the upstream database, if multiple remotes have inserted rows into the same AUTO_INCREMENT table, then you now have a totally unnecessary conflict to deal with since two different logical rows have been given the same ID. The image below shows an example of this.

Dolt is MySQL compatible, so we fully support AUTO_INCREMENT columns, but we strongly encourage customers to use UUIDs instead, so that you can take full advantage of Dolt's ability to merge in changes from forks and remote databases.

However, until recently, there was one advantage of AUTO_INCREMENT columns that customers couldn't get when using UUIDs...

Introducing last_insert_uuid()

When inserting data for a new entity, you often need to insert several rows into various tables in your database to create associated data. For example, you may insert one row into a users table for a new user and then use the primary key of that new user to insert a row into a unverified_users table to indicate that the new user account still needs to be verified. How can you reliably get the generated UUID primary key of the new user row that was just inserted?

To solve this problem with an AUTO_INCREMENT column, you would use the last_insert_id() function. This function returns the inserted AUTO_INCREMENT column value from the last statement in your current session. Or, if the last statement inserted multiple records, it returns the AUTO_INCREMENT value from the first row inserted. We wanted to follow this pattern for UUIDs, so we added a new SQL function, called last_insert_uuid(), that returns the last UUID value that was inserted as a key. Let's take a quick look at a concrete example of using this function, before we dive into the details of how it works.

-- Create a table with an automatic UUID key (we'll talk more about the rules for this later)
create table users (pk char(36) primary key default (UUID()), name varchar(100));

-- Insert a row into our new table
insert into users (name) values ("Harvey");
Query OK, 1 row affected (0.00 sec)

select * from users;
+--------------------------------------+--------+
| pk                                   | name   |
+--------------------------------------+--------+
| 99ee3f08-a65e-4cfb-8567-1b4373367f2d | Harvey |
+--------------------------------------+--------+

-- Use the last_insert_uuid() function to find the last generated UUID() in this session
select last_insert_uuid();
+--------------------------------------+
| last_insert_uuid()                   |
+--------------------------------------+
| 99ee3f08-a65e-4cfb-8567-1b4373367f2d |
+--------------------------------------+

-- Use the last_insert_uuid() function to add a row in another table that references the generated UUID
insert into unverified_users values (last_insert_uuid());
Query OK, 1 row affected (0.00 sec)

select * from unverified_users;
+--------------------------------------+
| pk                                   |
+--------------------------------------+
| 99ee3f08-a65e-4cfb-8567-1b4373367f2d |
+--------------------------------------+

Just as with last_insert_id(), the value here is session specific. So, if there are 100 clients connected to your database and they're all inserting a row to the same table, each one will still be able to call last_insert_uuid() to see the last UUID value they inserted in their own session.

How to use last_insert_uuid()

The biggest difference between using last_insert_id() and last_insert_uuid() is how you tell Dolt that those functions should apply to a column. With last_insert_id() you just mark your column with the AUTO_INCREMENT modifier. Since there isn't a modifier keyword for UUIDs as part of the MySQL dialect, to tell Dolt that the last_insert_uuid() function should apply to a column, you need to define your column as part of the table's primary key, and with one of the following type definitions:

• VARCHAR(36) or CHAR(36) with a default value expression of UUID()
• VARBINARY(16) or BINARY(16) with a default value expression of UUID_TO_BIN(UUID())

As we'll see in the next session, when the column is defined as VARBINARY or BINARY and uses the UUID_TO_BIN() function in the default value expression, the swap_flag for UUID_TO_BIN() may optionally be used, too.

A more complex example

Let's take a look at a slightly more complex example. Here, we're using a table that has a composite key and the generated UUID is one component of the key. We're also using a binary UUID, and storing it in a BINARY(16) column in order to save space with the more efficient, but harder for humans to directly read, binary format. We're also setting the swap_flag parameter of uuid_to_bin() in order to have a more efficient sort of our UUIDs in storage.

This allows indexes to more efficiently branch on the time-based component of the UUID, leading to more locality in updates as new UUIDs are inserted.

-- create a table with a composite key that includes a binary UUID, with the swap_flag set
create table users (
  pk BINARY(16) default (UUID_TO_BIN(UUID(), 1)),
  internal_account bool default(0), 
  name varchar(100),
  primary key (pk, internal_account));

-- insert a few rows into our new table
insert into users (name) values ("Jane");
Query OK, 1 row affected (0.00 sec)

insert into users (name) values ("Joe");
Query OK, 1 row affected (0.00 sec)

-- use the last_insert_uuid() function to find the last generated UUID() in this session
select last_insert_uuid();
+--------------------------------------+
| last_insert_uuid()                   |
+--------------------------------------+
| eb725e19-39ff-411b-8ec2-3e3862cf2fb4 |
+--------------------------------------+

select name from users where pk = UUID_TO_BIN(last_insert_uuid(), 1);
+------+
| name |
+------+
| Joe  |
+------+ 

Wrap Up

UUIDs make a ton of sense in distributed systems, and they offer many benefits over AUTO_INCREMENT columns with Dolt, since they allow you cleanly merge in inserts to the same table from different forks and remote databases. The new last_insert_uuid() function makes it even easier to use UUIDs when building your applications on Dolt.

The idea for last_insert_uuid() came from a customer request on GitHub. We love hearing from customers about what they need to build their applications on Dolt, so if you have a feature request or idea for us, please don't hesitate to open an issue on GitHub or swing by our Discord server and chat with us! We'd love to hear from you!