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

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