Unlike PostgreSQL or SQL Server, SQLite has no built-in UUID generation function — there's no UUID() or gen_random_uuid() waiting for you in the core engine. That's not really a problem: you either generate the UUID in your application code before inserting it as TEXT, or build one directly with a well-known SQL expression using randomblob() and hex(). Below you'll find copy-paste ready examples for creating a SQLite UUID, storing it efficiently, and the schema-level alternatives SQLite offers instead.
How to Generate a UUID in SQLite
SQLite core ships with no UUID() or gen_random_uuid() equivalent, so you have two honest options: generate the UUID in application code before inserting it, or build one directly with a pure-SQL expression. Oracle has no dedicated type, so the oracle uuid guide uses a 16-byte raw column instead.
# Recommended: generate the UUID in application code, then insert it as TEXT.
# Python's sqlite3 and uuid modules are both in the standard library --
# no extra packages required.
import sqlite3
import uuid
conn = sqlite3.connect('app.db')
conn.execute(
'INSERT INTO users (id, email) VALUES (?, ?)',
(str(uuid.uuid4()), 'jane@example.com')
)
conn.commit()-- Pure-SQL alternative: a well-known community pattern that builds a
-- v4-shaped UUID directly with randomblob() and hex(), no app code needed
SELECT lower(
hex(randomblob(4)) || '-' ||
hex(randomblob(2)) || '-4' ||
substr(hex(randomblob(2)), 2) || '-' ||
substr('89ab', abs(random()) % 4 + 1, 1) ||
substr(hex(randomblob(2)), 2) || '-' ||
hex(randomblob(6))
);
-- Output: 7e57ac1d-9b3f-4a2e-8f1c-6d4b8a9e0f12Explanation
- SQLite core has no built-in UUID-generating function at all — the most common real-world approach is generating the value in application code and inserting it as plain
TEXT. uuid.uuid4()from Python's standard library produces a random UUID; wrapping it instr()gives you the 36-character string SQLite stores asTEXT.- The pure-SQL expression uses
randomblob()to grab random bytes andhex()to turn them into hex text, hard-coding the version nibble to4and picking the variant character from89abto produce an RFC 4122-compliant v4 shape without leaving SQL. - Application-side generation is usually preferred in practice — it avoids repeating the expression in every query and lets you validate the format before the row is ever inserted.
Convert a String to a UUID in SQLite
SQLite is dynamically typed with no dedicated UUID column type, so a UUID received as a string — from an API request or another system — is simply stored as TEXT, or converted to BLOB when storage efficiency matters more than readability. When two values must be compared by content rather than by reference, a java uuid snippet parses a hyphenated string into an object first.
-- Simplest: store the UUID as TEXT, no conversion needed
CREATE TABLE users (
id TEXT PRIMARY KEY,
email TEXT NOT NULL
);
INSERT INTO users (id, email)
VALUES ('550e8400-e29b-41d4-a716-446655440000', 'jane@example.com');
SELECT * FROM users WHERE id = '550e8400-e29b-41d4-a716-446655440000';-- Storage-efficient alternative: convert to 16 raw bytes and store as BLOB
CREATE TABLE users_compact (
id BLOB PRIMARY KEY,
email TEXT NOT NULL
);
-- unhex() is available in SQLite 3.41+; strip the hyphens first
INSERT INTO users_compact (id, email)
VALUES (
unhex(replace('550e8400-e29b-41d4-a716-446655440000', '-', '')),
'jane@example.com'
);
-- Convert back to the readable string form on read
SELECT lower(hex(id)) FROM users_compact;Explanation
- SQLite has no dedicated UUID type — declaring the column
TEXTstores the 36-character hyphenated string exactly as received, and standard string comparison works fine for lookups. - Because SQLite is dynamically typed, a
TEXTorBLOBcolumn doesn't enforce length or format on its own, so validate the UUID string in application code before it reaches the database. - Storing as
BLOB(16 raw bytes) instead ofTEXT(36 bytes) roughly halves the per-row storage and can speed up primary-key comparisons on large tables, at the cost of manual hex conversion on every read and write. unhex()turns a hex string into raw bytes andhex()reverses it — strip the hyphens first, sinceunhex()expects a pure hex string.
Other Options for Generating UUIDs in SQLite
Beyond application-side generation and the pure-SQL v4 expression, SQLite supports a column default that produces a random value automatically on every INSERT, without touching application code at all.
-- SQLite 3.38+: a simple random-value default, generated on every INSERT
CREATE TABLE sessions (
id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
user_id TEXT NOT NULL,
expires_at DATETIME
);
-- Insert without specifying id -- SQLite fills it in automatically
INSERT INTO sessions (user_id, expires_at)
VALUES ('u_042', datetime('now', '+1 day'));
SELECT id FROM sessions LIMIT 1;
-- Output: 3f9a7c21e8b4419fa02d6c7e1b5f8a90Explanation
DEFAULT (lower(hex(randomblob(16))))runs on everyINSERTthat omitsid, turning 16 random bytes into a 32-character hex string.- This value is not RFC 4122 compliant — it has no version nibble or variant bits set — but it is still 128 bits of randomness, which is plenty of entropy to avoid collisions in practice.
- Function calls inside parenthesized
DEFAULTexpressions, likerandomblob()here, require SQLite 3.38 or later. - Reach for this only when you don't need standards-compliant UUIDs; for anything interoperating with other systems, prefer the RFC 4122 v4 expression from the first section instead.