Vic Demuzere

Generating UUIDs in SQLite

by on .

SQLite does not have built-in UUID functions, but by getting creative with the randomblob(N) function allows us to generate version 4 (random) UUIDs.

Before we continue, it's important to note that you should verify whether SQLite's random functions are adequate for your usecase. They may not return truly random data. However, as the RFC notes, "they MUST NOT be used as security capabilities". And since we all follow that to the letter, it doesn't really matter.. right?

The excellent SQLite manual gives us a starting point:

Hint: applications can generate globally unique identifiers using this function together with hex() and/or lower().

Combining the randomblob(N) function with hex(N) does indeed create something that vaguely resembles a UUID. For some purposes this may suffice, but the result isn't actually a valid UUID1: valid UUIDs must have specific bits set to indicate their version and variant.

We can do better!

As binary blob

The binary representation of a version 4 UUID consists of:

  • 48 bits (6 bytes) random data
  • 4 bits (0.5 bytes) version field
  • 6 bits (1.5 bytes) random data
  • 4 bits (0.5 bytes) variant field
  • 60 bits (7.5 bytes) random data

As far as I know, we can't modify individual bytes in a blob using the available functions in SQLite. However, we do have access to a lot of string functions. Taking a detour using HEX encoding provides a solution. We'll first concatenate a HEX string containing the data we want, then convert it back to a binary blob. Note that each byte is represented by two hexadecimal characters.

The first 48 random bits are easy:

sqlite> select HEX(RANDOMBLOB(6));
4004714237AF

The first 4 bits of the next byte need to be set to 0100 (or 4 in hex). As every byte is represented by two characters in hexadecimal, we can just use a literal 4 for the next character.

sqlite> select '4';
4

Next, we need 6 bits of random data. We can only generate random bytes using RANDOMBLOB(N), but we need 1.5 bytes. Luckily, because we are working with a hexadecimal string, we can generate 2 bytes (4 hex characters) and take the first three characters.

sqlite> select HEX(RANDOMBLOB(2));
7B4A

sqlite> select SUBSTR(HEX(RANDOMBLOB(2)),0,4);
5BD

The next one is a little more complex. The variant field consists of a variable number of bits. For our version 4 UUID, we need to set the first two bits to 10, the others can be random. That gives us a possible range between 1000 and 1011, or a hexadecimal character between 8 and B inclusive.

select FORMAT('%X', 8 + ABS(RANDOM() % 4));
A

After this, we need 60 more random bits. Since this again includes a half byte again (7.5 bytes), we're doing the same SUBSTR trick as before:

sqlite> select SUBSTR(HEX(RANDOMBLOB(8)),0,16);
E83EF2136842130

Stitching it together

Using CONCAT(X, ...) we create a single string:

sqlite> select CONCAT(
(x1...>   HEX(RANDOMBLOB(6)),
(x1...>   '4',
(x1...>   SUBSTR(HEX(RANDOMBLOB(2)),0,4),
(x1...>   FORMAT('%X', 8 + ABS(RANDOM() % 4)),
(x1...>   SUBSTR(HEX(RANDOMBLOB(8)),0,16)
(x1...> );

64DF281823BE47249BB2CA8D6450066B

Then use UNHEX(X) to convert it back into a binary blob:

select UNHEX(CONCAT(
  HEX(RANDOMBLOB(6)),
  '4',
  SUBSTR(HEX(RANDOMBLOB(2)),0,4),
  FORMAT('%X', 8 + ABS(RANDOM() % 4)),
  SUBSTR(HEX(RANDOMBLOB(8)),0,16)
));

Generating UUIDs automatically

The SQL above can be used as the default value for a blob column:

CREATE TABLE "animals" (
  "id" INTEGER PRIMARY KEY NOT NULL,
  "uuid" BLOB UNIQUE NOT NULL
    DEFAULT (UNHEX(CONCAT(
      HEX(RANDOMBLOB(6)), '4',
      SUBSTR(HEX(RANDOMBLOB(2)),0,4),
      FORMAT('%X', 8 + ABS(RANDOM() % 4)),
      SUBSTR(HEX(RANDOMBLOB(8)),0,16))))
    UNIQUE CHECK(LENGTH("uuid") = 16),
   "name" TEXT NOT NULL
) STRICT;

This will generate a UUID if we don't specify one on insert:

sqlite> INSERT INTO "animals" ("name") VALUES ('Fluffy');

sqlite> SELECT * FROM "animals";
1|[blob]|Fluffy

sqlite> SELECT "id", HEX("uuid"), "name" FROM "animals";
1|85671F7120F14AA6802B8B4E9213B8BE|Fluffy

In human readable (hexadecimal) form

Depending on your use case, you may want to store UUIDs in a TEXT column.

We were close to a "hex-and-dash" string format with the SQL above, until we call the UNHEX(X) function. We just have to add dashes in the correct positions, as RFC 9562 specifies that dashes are required.

sqlite> select CONCAT(
  HEX(RANDOMBLOB(4)),
  '-',
  HEX(RANDOMBLOB(2)),
  '-',
  '4',
  SUBSTR(HEX(RANDOMBLOB(2)),0,4),
  '-',
  FORMAT('%X', 8 + ABS(RANDOM() % 4)),
  SUBSTR(HEX(RANDOMBLOB(2)),0,4),
  '-',
  SUBSTR(HEX(RANDOMBLOB(8)),0,13)
);

199ED82A-3102-42DB-B59B-8C356B6F3FF7

Voila!

  1. As technically correct is the best kind of correct: if you get really lucky with the generated random data, they might actually be valid. ↩