SQLite

Run PRAGMA journal_mode=WAL on the connection to enable write-ahead logging, which allows reads during writes. Also set PRAGMA busy_timeout=5000 so concurrent access waits instead of failing immediately with SQLITE_BUSY.

Foreign key enforcement is off by default and must be enabled per connection with PRAGMA foreign_keys=ON. Without it, constraints and ON DELETE CASCADE are silently ignored, so verify with PRAGMA foreign_keys before relying on them.

SQLite allows only one writer at a time; concurrent writes queue or fail even with WAL mode. For web apps with many simultaneous writers, a client-server database like PostgreSQL is the appropriate choice.

Never copy the database file while it is open, since a write in progress corrupts the copy. Use the .backup command, the sqlite3_backup API, or VACUUM INTO 'backup.db' (SQLite 3.27+) for a consistent standalone copy.

SQLite uses type affinity rather than strict typing, so an INTEGER column can store text without error. Use STRICT tables (SQLite 3.37+) to enforce declared types, and store booleans as INTEGER 0/1 and dates as ISO8601 TEXT or Unix timestamps.

Wrap inserts in an explicit transaction with BEGIN and COMMIT instead of relying on autocommit, which commits every statement individually. Batching inserts this way is typically 10 to 100 times faster.