SQLite

Use SQLite correctly with proper concurrency, pragmas, and type handling.

Concurrency (Biggest Gotcha)

• Only one writer at a time—concurrent writes queue or fail; not for high-write workloads
• Enable WAL mode: PRAGMA journal_mode=WAL—allows reads during writes, huge improvement
• Set busy timeout: PRAGMA busy_timeout=5000—waits 5s before SQLITE_BUSY instead of failing immediately
• WAL needs -wal and -shm files—don't forget to copy them with main database
• BEGIN IMMEDIATE to grab write lock early—prevents deadlocks in read-then-write patterns

Foreign Keys (Off by Default!)

• PRAGMA foreign_keys=ON required per connection—not persisted in database
• Without it, foreign key constraints silently ignored—data integrity broken
• Check before relying: PRAGMA foreign_keys returns 0 or 1
• ON DELETE CASCADE only works if foreign_keys is ON

Type System

• Type affinity, not strict types—INTEGER column accepts "hello" without error
• STRICT tables enforce types—but only SQLite 3.37+ (2021)
• No native DATE/TIME—use TEXT as ISO8601 or INTEGER as Unix timestamp
• BOOLEAN doesn't exist—use INTEGER 0/1; TRUE/FALSE are just aliases
• REAL is 8-byte float—same precision issues as any float

Schema Changes

• ALTER TABLE very limited—can add column, rename table/column; that's mostly it
• Can't change column type, add constraints, or drop columns (until 3.35)
• Workaround: create new table, copy data, drop old, rename—wrap in transaction
• ALTER TABLE ADD COLUMN can't have PRIMARY KEY, UNIQUE, or NOT NULL without default

Performance Pragmas

• PRAGMA optimize before closing long-running connections—updates query planner stats
• PRAGMA cache_size=-64000 for 64MB cache—negative = KB; default very small
• PRAGMA synchronous=NORMAL with WAL—good balance of safety and speed
• PRAGMA temp_store=MEMORY for temp tables in RAM—faster sorts and temp results

Vacuum & Maintenance

• Deleted data doesn't shrink file—VACUUM rewrites entire database, reclaims space
• VACUUM needs 2x disk space temporarily—ensure enough room
• PRAGMA auto_vacuum=INCREMENTAL with PRAGMA incremental_vacuum—partial reclaim without full rewrite
• After bulk deletes, always vacuum or file stays bloated

Backup Safety

• Never copy database file while open—corrupts if write in progress
• Use .backup command in sqlite3—or sqlite3_backup_* API
• WAL mode: -wal and -shm must be copied atomically with main file
• VACUUM INTO 'backup.db' creates standalone copy (3.27+)

Indexing

• Covering indexes work—add extra columns to avoid table lookup
• Partial indexes supported (3.8+): CREATE INDEX ... WHERE condition
• Expression indexes (3.9+): CREATE INDEX ON t(lower(name))
• EXPLAIN QUERY PLAN shows index usage—simpler than PostgreSQL EXPLAIN

Transactions

• Autocommit by default—each statement is own transaction; slow for bulk inserts
• Batch inserts: BEGIN; INSERT...; INSERT...; COMMIT—10-100x faster
• BEGIN EXCLUSIVE for exclusive lock—blocks all other connections
• Nested transactions via SAVEPOINT name / RELEASE name / ROLLBACK TO name

Common Mistakes

• Using SQLite for web app with concurrent users—one writer blocks all; use PostgreSQL
• Assuming ROWID is stable—VACUUM can change ROWIDs; use explicit INTEGER PRIMARY KEY
• Not setting busy_timeout—random SQLITE_BUSY errors under any concurrency
• In-memory database ':memory:'—each connection gets different database; use file::memory:?cache=shared for shared