---
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.
---

<!--
Author: Simon-Pierre Boucher
Contact: contact@spboucher.ai
-->

# 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](references/patterns.md).