UUIDv7 for SQLite using view and triggers

UUIDv7_SQLITE_VIEW.sql

DROP VIEW IF EXISTS uuid7;
CREATE VIEW uuid7 AS
WITH unixtime AS (
	SELECT CAST((STRFTIME('%s') * 1000) + ((STRFTIME('%f') * 1000) % 1000) AS INTEGER) AS time
	-- SELECT CAST((UNIXEPOCH('subsec') * 1000) AS INTEGER) AS time -- for SQLite v3.38.0 (2022)
)
SELECT PRINTF('%08x-%04x-%04x-%04x-%012x', 
       (select time from unixtime) >> 16,
       (select time from unixtime) & 0xffff,
       ABS(RANDOM()) % 0x0fff + 0x7000,
       ABS(RANDOM()) % 0x3fff + 0x8000,
       ABS(RANDOM()) >> 16) AS next;

-- EXAMPLE:
--
--     sqlite> SELECT next FROM uuid7;
--     01901973-f202-71ca-9a22-14e7146dab85
--

UUIDv7_SQLITE_VIEW_AND_TRIGGER_EXAMPLE.sql

-- DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table (
    uuid TEXT PRIMARY KEY,
    created DATE NOT NULL,
    content TEXT NOT NULL,
    CONSTRAINT check_my_table_uuid CHECK (uuid REGEXP '[a-f0-9]{8}-([a-f0-9]{4}-){3}[a-f0-9]{12}')
);

DROP TRIGGER IF EXISTS trigger_after_insert_on_my_table;
CREATE TRIGGER trigger_after_insert_on_my_table
    AFTER INSERT ON my_table FOR EACH ROW WHEN NEW.uuid IS NULL
BEGIN
	UPDATE my_table SET uuid = (SELECT next FROM uuid7) WHERE ROWID = NEW.ROWID;
END;

-- NOTES:
-- 1.  The trigger generates a UUIDv7 when none is provided in the insert statement.
-- 2.  The check constraint verifies if the provided UUID is valid on updates and inserts.
-- 3.  Unfortunately, we can't use a sub-query as DEFAULT value for the PK. That's why we use the trigger.

UUIDv7_SQLITE_VIEW_AND_TRIGGER_EXAMPLE_TESTS.sql

-- Insert some lines using the trigger
INSERT INTO my_table VALUES(NULL, DATETIME(), 'Hello, World!');
INSERT INTO my_table VALUES(NULL, DATETIME(), 'Hello, World!');
INSERT INTO my_table VALUES(NULL, DATETIME(), 'Hello, World!');

-- Insert more lines using the view (ignoring the trigger)
INSERT INTO my_table VALUES((SELECT next FROM uuid7), DATETIME(), 'Hello, World!');
INSERT INTO my_table VALUES((SELECT next FROM uuid7), DATETIME(), 'Hello, World!');
INSERT INTO my_table VALUES((SELECT next FROM uuid7), DATETIME(), 'Hello, World!');

-- Insert more lines providing UUIDs (also ignoring the trigger)
INSERT INTO my_table VALUES('99f30c47-d5be-4803-9f2b-95c232f89877', DATETIME(), 'Hello, World!');
INSERT INTO my_table VALUES('d38ae560-321f-408c-a70b-a5ad8f27d346', DATETIME(), 'Hello, World!');
INSERT INTO my_table VALUES('c78a27ec-b065-4af1-9619-7a676d34275a', DATETIME(), 'Hello, World!');

-- Try to insert lines with invalid UUIDs; they must throw check error
INSERT INTO my_table VALUES('', DATETIME(), 'Hello, World!');
INSERT INTO my_table VALUES('asdfasdfasdf', DATETIME(), 'Hello, World!');
INSERT INTO my_table VALUES('0d56252d-51a6-4d1b-8ddc----------', DATETIME(), 'Hello, World!');

SELECT * FROM my_table;
-- 01901a7a-3f0b-714e-b696-6d48d23a262e|2024-06-15 05:58:57|Hello, World!
-- 01901a7a-3f0b-76fb-82cd-17b01c4e7cfe|2024-06-15 05:58:57|Hello, World!
-- 01901a7a-3f0b-7a42-a03b-3055c708f422|2024-06-15 05:58:57|Hello, World!
-- 01901a7a-3f0b-7ad1-b2b9-0d4ead036759|2024-06-15 05:58:57|Hello, World!
-- 01901a7a-3f0b-7076-ae04-693bd8f276aa|2024-06-15 05:58:57|Hello, World!
-- 01901a7a-3f0c-7e05-bae2-4792b2e3192b|2024-06-15 05:58:57|Hello, World!
-- 99f30c47-d5be-4803-9f2b-95c232f89877|2024-06-15 05:58:57|Hello, World!
-- d38ae560-321f-408c-a70b-a5ad8f27d346|2024-06-15 05:58:57|Hello, World!
-- c78a27ec-b065-4af1-9619-7a676d34275a|2024-06-15 05:58:57|Hello, World!

INSERT INTO my_table VALUES(NULL, DATETIME(), 'Hello, World!');

This doesn't work for me. I get this error:

Execution finished with errors.
Result: CHECK constraint failed: check_my_table_uuid
At line 1:
INSERT INTO my_table VALUES(NULL, DATETIME(), 'Hello, World!');

Hi @brettinternet !

Unfortunately we can't use a BEFORE INSERT trigger to populate the UUID field before the insert. That's why we use the AFTER INSERT.

In the example, the primary key has no NOT NULL constraint in the UUID field. The trigger catches inserts with null UUIDs and replaces it with the next value generated in the view.

The insert trigger acts like a DEFAULT keyword as in this fake snippet:

CREATE TABLE my_table (
    uuid TEXT PRIMARY KEY DEFAULT (SELECT next FROM uuid7), ...

If you're using the NOT NULL constraint in the UUID field, you can't use the insert trigger. You can only do insert specifying a non-null UUID:

INSERT INTO my_table VALUES((SELECT next FROM uuid7), DATETIME(), 'Hello, World!');

Let me know if you find a way to have both the trigger and the NOT NULL constraint. I've searched a lot but couldn't find a way of doing it.

Thanks for your feedback!

---

Two updates:

• Replace FORMAT with PRINTF for older versions of SQLite, e.g. SQLite version 3.37.2 2022-01-06 13:25:41.
• Add FOR EACH ROW in trigger definition, which is optional, but it's also a good practice.

Hi @fabiolimace, I see now. Thanks for sharing this and explaining your solution!