How to Create UUID in SQLite

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. The postgres uuid generator reference covers UUID primary keys, BRIN indexes, and partitioning strategies in PostgreSQL.

sqlite3

# 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()

sqlite3

-- 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-6d4b8a9e0f12

Explanation

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 in str() gives you the 36-character string SQLite stores as TEXT.
The pure-SQL expression uses randomblob() to grab random bytes and hex() to turn them into hex text, hard-coding the version nibble to 4 and picking the variant character from 89ab to 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. Use the sql server uuid guide to implement SQL Server GUIDs with Entity Framework Core and Dapper ORM.

sqlite3

-- 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';

sqlite3

-- 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 TEXT stores the 36-character hyphenated string exactly as received, and standard string comparison works fine for lookups.
Because SQLite is dynamically typed, a TEXT or BLOB column 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 of TEXT (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 and hex() reverses it — strip the hyphens first, since unhex() 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.

sqlite3

-- 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: 3f9a7c21e8b4419fa02d6c7e1b5f8a90

Explanation

DEFAULT (lower(hex(randomblob(16)))) runs on every INSERT that omits id, 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 DEFAULT expressions, like randomblob() 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.

Generate UUID for SQLite

How to Generate a UUID in SQLite

Explanation

Convert a String to a UUID in SQLite

Explanation

Other Options for Generating UUIDs in SQLite

Explanation

Comments & Feedback