How to Create UUID in SQLite

How to Create UUID in SQLite 3.38+

SQLite version 3.38.0 and later includes a built-in uuid() function that generates version 4 (random) UUIDs:

SQL - SQLite 3.38+

-- Check SQLite version
SELECT sqlite_version();
-- Must be 3.38.0 or higher

-- Generate a UUID v4
SELECT uuid();
-- Result: 550e8400-e29b-41d4-a716-446655440000

-- Create table with UUID primary key
CREATE TABLE users (
    id TEXT PRIMARY KEY DEFAULT (uuid()),
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insert without specifying ID (auto-generated)
INSERT INTO users (name, email) 
VALUES ('John Doe', '[email protected]');

-- Query by UUID
SELECT * FROM users 
WHERE id = '550e8400-e29b-41d4-a716-446655440000';

-- Generate multiple UUIDs
SELECT uuid() AS uuid1, uuid() AS uuid2, uuid() AS uuid3;

SQLite 3.38+: The uuid() function is built-in and requires no extensions. Upgrade to 3.38+ for native UUID support.

How to Create UUID in Older SQLite Versions

For SQLite versions before 3.38, generate UUIDs in your application code (Python, JavaScript, etc.) and insert them:

Python with sqlite3

Python

import sqlite3
import uuid

# Connect to database
conn = sqlite3.connect('myapp.db')
cursor = conn.cursor()

# Create table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL
    )
''')

# Generate UUID and insert
user_id = str(uuid.uuid4())
cursor.execute(
    'INSERT INTO users (id, name, email) VALUES (?, ?, ?)',
    (user_id, 'John Doe', '[email protected]')
)

conn.commit()

# Query by UUID
cursor.execute('SELECT * FROM users WHERE id = ?', (user_id,))
user = cursor.fetchone()
print(user)

Node.js with better-sqlite3

JavaScript

const Database = require('better-sqlite3');
const { v4: uuidv4 } = require('uuid');

const db = new Database('myapp.db');

// Create table
db.exec(`
    CREATE TABLE IF NOT EXISTS users (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL
    )
`);

// Insert with generated UUID
const insertUser = db.prepare(
    'INSERT INTO users (id, name, email) VALUES (?, ?, ?)'
);

const userId = uuidv4();
insertUser.run(userId, 'Jane Smith', '[email protected]');

// Query by UUID
const getUser = db.prepare('SELECT * FROM users WHERE id = ?');
const user = getUser.get(userId);
console.log(user);

PHP with PDO

PHP

$db = new PDO('sqlite:myapp.db');

// Create table
$db->exec("
    CREATE TABLE IF NOT EXISTS users (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL
    )
");

// Generate UUID using PHP
function generateUuid() {
    return sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
        mt_rand(0, 0xffff), mt_rand(0, 0xffff),
        mt_rand(0, 0xffff),
        mt_rand(0, 0x0fff) | 0x4000,
        mt_rand(0, 0x3fff) | 0x8000,
        mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff)
    );
}

// Insert with UUID
$userId = generateUuid();
$stmt = $db->prepare('INSERT INTO users (id, name, email) VALUES (?, ?, ?)');
$stmt->execute([$userId, 'Bob Wilson', '[email protected]']);

SQLite UUID Storage Best Practices

SQLite stores UUIDs as TEXT. Follow these best practices for optimal performance:

SQL - Best Practices

-- Always use TEXT type for UUIDs
CREATE TABLE products (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    sku TEXT UNIQUE
);

-- Create indexes on UUID foreign keys
CREATE TABLE orders (
    id TEXT PRIMARY KEY,
    product_id TEXT NOT NULL,
    quantity INTEGER,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE INDEX idx_orders_product_id ON orders(product_id);

-- Use COLLATE NOCASE for case-insensitive UUID matching
CREATE TABLE sessions (
    id TEXT PRIMARY KEY COLLATE NOCASE,
    user_id TEXT NOT NULL,
    expires_at DATETIME
);

-- Check table structure
PRAGMA table_info(users);

UUID vs INTEGER Primary Keys

Aspect	UUID (TEXT)	INTEGER
Storage Size	36 bytes (as TEXT)	1-8 bytes (variable)
Globally Unique	✓ Yes	✗ Per-database only
Client-Side Generation	✓ No round-trip needed	✗ Requires INSERT to get ID
Security	✓ Non-guessable	✗ Sequential, predictable
Performance	Good for most apps	✓ Faster
URL Friendliness	Long URLs	✓ Short URLs
Best For	Distributed apps, sync, APIs	Local-only applications

UUID with SQLite ORM Frameworks

SQLAlchemy (Python)

Python - SQLAlchemy

from sqlalchemy import create_engine, Column, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import uuid

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = Column(String(100), nullable=False)
    email = Column(String(255), unique=True, nullable=False)

# Create engine and tables
engine = create_engine('sqlite:///myapp.db')
Base.metadata.create_all(engine)

# Create session
Session = sessionmaker(bind=engine)
session = Session()

# Insert user (UUID auto-generated)
new_user = User(name='Alice Brown', email='[email protected]')
session.add(new_user)
session.commit()

print(f"User ID: {new_user.id}")

Sequelize (Node.js)

JavaScript - Sequelize

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize({
    dialect: 'sqlite',
    storage: 'myapp.db'
});

const User = sequelize.define('User', {
    id: {
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4,
        primaryKey: true
    },
    name: {
        type: DataTypes.STRING(100),
        allowNull: false
    },
    email: {
        type: DataTypes.STRING(255),
        allowNull: false,
        unique: true
    }
});

// Sync and create user
await sequelize.sync();
const user = await User.create({
    name: 'Charlie Davis',
    email: '[email protected]'
});

console.log(user.id); // Auto-generated UUID

Checking SQLite Version

Verify your SQLite version to determine if uuid() is available:

Command Line

# Check SQLite version from command line
sqlite3 --version

# Check version inside SQLite shell
sqlite3 mydb.db
SELECT sqlite_version();

# Check in Python
import sqlite3
print(sqlite3.sqlite_version)

# Check in Node.js
const Database = require('better-sqlite3');
const db = new Database(':memory:');
console.log(db.prepare('SELECT sqlite_version()').get());

Upgrade Recommendation: If your SQLite version is below 3.38, consider upgrading for built-in UUID support and other improvements.

Other Database UUID Guides

PostgreSQL Native UUID type MySQL UUID() & BINARY(16) SQL Server NEWID() function MongoDB UUID() Binary type Oracle SYS_GUID()

Generate UUID for SQLite