SQLite

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