Appearance
Generate UUID in SQLite
SQLite doesn't have built-in UUID generation functions, but you can implement UUID support through custom functions, extensions, or application-level generation:
sql
-- Custom UUID v4 function (simplified)
-- Note: This requires a custom SQLite build or extension
SELECT uuid_generate_v4();
-- More commonly, generate UUIDs in application code
INSERT INTO users (id, name, email)
VALUES ('550e8400-e29b-41d4-a716-446655440000', 'John Doe', '[email protected]');
-- Query with UUID
SELECT * FROM users WHERE id = '550e8400-e29b-41d4-a716-446655440000';Since SQLite lacks native UUID functions, here are the most practical approaches:
sql
-- Method 1: Application-generated UUIDs (most common)
CREATE TABLE users (
id TEXT PRIMARY KEY, -- Store UUID as TEXT
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Method 2: Binary UUID storage (more efficient)
CREATE TABLE users_binary (
id BLOB PRIMARY KEY, -- Store UUID as 16-byte BLOB
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Method 3: Hybrid approach with ROWID
CREATE TABLE users_hybrid (
rowid INTEGER PRIMARY KEY, -- SQLite's natural key
uuid TEXT UNIQUE NOT NULL, -- UUID for external references
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL
);Custom UUID Functions
SQLite Extension for UUID:
c
// uuid_extension.c - Custom SQLite extension
#include <sqlite3ext.h>
#include <stdlib.h>
#include <time.h>
SQLITE_EXTENSION_INIT1
static void uuid_v4_func(
sqlite3_context *context,
int argc,
sqlite3_value **argv
) {
char uuid[37];
// Simplified UUID v4 generation
srand(time(NULL));
sprintf(uuid, "%08x-%04x-4%03x-%04x-%012x",
rand(), rand() & 0xFFFF, rand() & 0x0FFF,
(rand() & 0x3FFF) | 0x8000, rand());
sqlite3_result_text(context, uuid, -1, SQLITE_TRANSIENT);
}
int sqlite3_extension_init(
sqlite3 *db,
char **pzErrMsg,
const sqlite3_api_routines *pApi
) {
SQLITE_EXTENSION_INIT2(pApi);
sqlite3_create_function(db, "uuid_v4", 0, SQLITE_UTF8,
NULL, uuid_v4_func, NULL, NULL);
return SQLITE_OK;
}Load and use extension:
sql
-- Load UUID extension
.load ./uuid_extension
-- Use custom UUID function
SELECT uuid_v4() as new_uuid;
-- Create table with UUID default
CREATE TABLE events (
id TEXT PRIMARY KEY DEFAULT (uuid_v4()),
event_type TEXT,
data TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);Application-Level UUID Generation
Python with SQLite:
python
import sqlite3
import uuid
# Connect to database
conn = sqlite3.connect('example.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,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')
# Insert with UUID
user_id = str(uuid.uuid4())
cursor.execute('''
INSERT INTO users (id, name, email)
VALUES (?, ?, ?)
''', (user_id, 'John Doe', '[email protected]'))
# Query by UUID
cursor.execute('SELECT * FROM users WHERE id = ?', (user_id,))
user = cursor.fetchone()
conn.commit()
conn.close()Node.js with SQLite:
javascript
const sqlite3 = require('sqlite3').verbose();
const { v4: uuidv4 } = require('uuid');
const db = new sqlite3.Database('example.db');
// Create table
db.run(`
CREATE TABLE IF NOT EXISTS users (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
`);
// Insert with UUID
const userId = uuidv4();
db.run(`
INSERT INTO users (id, name, email)
VALUES (?, ?, ?)
`, [userId, 'Jane Doe', '[email protected]']);
// Query by UUID
db.get('SELECT * FROM users WHERE id = ?', [userId], (err, row) => {
if (err) {
console.error(err);
} else {
console.log(row);
}
});
db.close();Mobile App Integration
Android with Room (Kotlin):
kotlin
import androidx.room.*
import java.util.UUID
@Entity(tableName = "users")
data class User(
@PrimaryKey val id: String = UUID.randomUUID().toString(),
val name: String,
val email: String,
val createdAt: Long = System.currentTimeMillis()
)
@Dao
interface UserDao {
@Query("SELECT * FROM users WHERE id = :id")
suspend fun getUserById(id: String): User?
@Insert
suspend fun insertUser(user: User)
@Query("SELECT * FROM users ORDER BY createdAt DESC")
suspend fun getAllUsers(): List<User>
}
// Usage
val user = User(
name = "John Doe",
email = "[email protected]"
)
userDao.insertUser(user)iOS with Core Data (Swift):
swift
import CoreData
import Foundation
@objc(User)
public class User: NSManagedObject {
@NSManaged public var id: String
@NSManaged public var name: String
@NSManaged public var email: String
@NSManaged public var createdAt: Date
public override func awakeFromInsert() {
super.awakeFromInsert()
self.id = UUID().uuidString
self.createdAt = Date()
}
}
// Usage
let context = persistentContainer.viewContext
let user = User(context: context)
user.name = "Jane Doe"
user.email = "[email protected]"
try context.save()Performance Optimization
UUID storage comparison:
sql
-- TEXT storage (36 bytes, human-readable)
CREATE TABLE users_text (
id TEXT PRIMARY KEY,
data TEXT
);
-- BLOB storage (16 bytes, more efficient)
CREATE TABLE users_blob (
id BLOB PRIMARY KEY,
data TEXT
);
-- WITHOUT ROWID optimization for UUID primary keys
CREATE TABLE users_optimized (
id TEXT PRIMARY KEY,
data TEXT
) WITHOUT ROWID;
-- Hybrid approach (best of both worlds)
CREATE TABLE users_hybrid (
rowid INTEGER PRIMARY KEY,
uuid TEXT UNIQUE NOT NULL,
data TEXT
);
-- Index on UUID for fast lookups
CREATE UNIQUE INDEX idx_users_uuid ON users_hybrid(uuid);UUID conversion functions:
sql
-- Custom functions for UUID conversion (requires extension)
-- Convert UUID string to BLOB
CREATE FUNCTION uuid_to_blob(uuid_text TEXT)
RETURNS BLOB AS $$
-- Implementation would convert hex string to binary
$$;
-- Convert BLOB to UUID string
CREATE FUNCTION blob_to_uuid(uuid_blob BLOB)
RETURNS TEXT AS $$
-- Implementation would convert binary to hex string
$$;Testing and Validation
UUID validation in SQLite:
sql
-- Check constraint for UUID format
CREATE TABLE validated_users (
id TEXT PRIMARY KEY CHECK (
length(id) = 36 AND
substr(id, 9, 1) = '-' AND
substr(id, 14, 1) = '-' AND
substr(id, 19, 1) = '-' AND
substr(id, 24, 1) = '-'
),
name TEXT NOT NULL
);
-- Trigger for UUID validation
CREATE TRIGGER validate_uuid_format
BEFORE INSERT ON users
FOR EACH ROW
WHEN length(NEW.id) != 36 OR
substr(NEW.id, 9, 1) != '-' OR
substr(NEW.id, 14, 1) != '-' OR
substr(NEW.id, 19, 1) != '-' OR
substr(NEW.id, 24, 1) != '-'
BEGIN
SELECT RAISE(ABORT, 'Invalid UUID format');
END;Migration Strategies
Adding UUIDs to existing tables:
sql
-- Add UUID column to existing table
ALTER TABLE existing_table ADD COLUMN uuid TEXT;
-- Update existing rows with UUIDs (requires application logic)
-- This would typically be done in application code:
-- UPDATE existing_table SET uuid = ? WHERE rowid = ?
-- Make UUID column NOT NULL after population
-- Note: SQLite doesn't support ALTER COLUMN, so recreate table
CREATE TABLE existing_table_new (
rowid INTEGER PRIMARY KEY,
uuid TEXT UNIQUE NOT NULL,
-- ... other columns
);
INSERT INTO existing_table_new SELECT rowid, uuid, ... FROM existing_table;
DROP TABLE existing_table;
ALTER TABLE existing_table_new RENAME TO existing_table;UUID Version Comparison
Choose the right approach for your SQLite application:
- Version 1 - Time-based, good for mobile apps with offline sync
- Version 3 - MD5 namespace-based, deterministic
- Version 4 - Random, most popular choice for mobile apps
- Version 5 - SHA-1 namespace-based, more secure than v3
- Version 6 - Time-ordered, better for chronological data
- Version 7 - Modern time-based with improved sorting
For SQLite applications:
- Mobile apps: Use Version 4 for local data and sync
- Embedded systems: Consider Version 1 for time-based tracking
- Desktop applications: Use Version 5 for deterministic resource IDs
How do I generate UUID in other databases?
SQL databases:
- PostgreSQL - Comprehensive UUID support with extensions
- MySQL - Built-in UUID() function
- SQL Server - NEWID() and NEWSEQUENTIALID()
- Oracle - SYS_GUID() function
NoSQL databases: