# name: managing-sqlite description: Sets up and operates SQLite in applications — choosing when SQLite fits, WAL mode, required pragmas, fast bulk inserts, safe backups, and type-affinity pitfalls. Use when the user asks to add or configure SQLite, mentions a .db or .sqlite file, hits "database is locked" errors, needs to back up or speed up an SQLite database, or asks whether SQLite is the right choice. Do not use for server databases like PostgreSQL or MySQL, or for generic SQL query writing.

# Managing SQLite

# When to use / when NOT to use

• Use for: embedding, configuring, operating, or debugging SQLite in an application (.db/.sqlite/.sqlite3 files).
• Do NOT use for: PostgreSQL/MySQL administration (administering-postgresql) or general SQL authoring (writing-sql-queries).

# Core rules

1. Pick SQLite deliberately. Right when: embedded/desktop/mobile/CLI apps, local-first data, one writer at a time, read-heavy sites, datasets well under ~1TB. Wrong when: many concurrent writers, network access from multiple hosts, or you need roles/permissions — use a server database.
2. WAL mode is the default choice for any app. Readers stop blocking the writer.
   • ✅ PRAGMA journal_mode=WAL; (persistent, set once per database)
   • ❌ Shipping with the default rollback journal and retro-fitting after lock errors.
3. Foreign keys are OFF by default — enable per connection. PRAGMA foreign_keys=ON; on every connection open, or FK constraints silently do nothing.
4. Set busy_timeout on every connection (e.g. PRAGMA busy_timeout=5000;) so concurrent writes wait instead of instantly failing with SQLITE_BUSY.
5. Wrap bulk inserts in one transaction. One-insert-per-transaction is 100–1000× slower because each commit is an fsync.
   • ✅ BEGIN; INSERT ×10 000; COMMIT;
   • ❌ 10 000 autocommit inserts.
6. Never copy a live database file. Use VACUUM INTO 'backup.db' (SQL, ≥3.27) or the online backup API / .backup in the CLI; copying mid-write yields a corrupt file, and WAL data lives in -wal until checkpoint.
7. Types are affinities, not constraints. INSERT INTO t(age) VALUES ('abc') succeeds on an INTEGER column. Use STRICT tables (≥3.37) when types must be enforced.
8. One writer at a time — design for it. Serialize writes through a single connection/queue in the app rather than relying on retries.

# Workflow

1. On every connection open, apply the pragma set: journal_mode=WAL (once), foreign_keys=ON, busy_timeout=5000, and synchronous=NORMAL for WAL databases.
2. Create schema with explicit STRICT tables when type safety matters.
3. Route all writes through one connection; use transactions for any multi-statement or bulk operation.
4. Back up with VACUUM INTO on a schedule; test the backup by opening it and running PRAGMA integrity_check;.
5. Validate the setup: PRAGMA journal_mode; returns wal, PRAGMA foreign_keys; returns 1, and PRAGMA integrity_check; returns ok.

# Edge cases & failure modes

• database is locked → missing busy_timeout, a long-running write transaction, or two processes writing; fix in that order.
• FKs "not working" → the connection that inserted skipped PRAGMA foreign_keys=ON (it is per-connection, not per-database).
• Growing -wal file → no checkpoints because a reader holds a long transaction; close it or run PRAGMA wal_checkpoint(TRUNCATE);.
• Corrupt database → PRAGMA integrity_check;, then .recover in the sqlite3 CLI; restore from backup if recovery fails. Never keep using a file that fails integrity_check.
• Network filesystem (NFS/SMB) → file locking is unreliable there; keep SQLite files on local disks only.
• No dependency needed → SQLite ships in Python's stdlib (import sqlite3); the CLI is preinstalled on macOS, else brew install sqlite.

# References

Connection templates, backup commands, STRICT tables, and WAL details: see references/patterns.md.