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