spb/ultra-sharp-agent-skills Public

1<!--2Author: Simon-Pierre Boucher3Contact: contact@spboucher.ai4-->56# SQLite — Patterns78## Contents9- Connection setup (Python)10- Fast bulk inserts11- STRICT tables and type safety12- Backups13- WAL maintenance14- Single-writer pattern15- Gotchas1617## Connection setup (Python)1819```python20import sqlite32122def connect(path):23    conn = sqlite3.connect(path, timeout=5.0)     # timeout ≈ busy_timeout24    conn.execute("PRAGMA journal_mode=WAL")       # persistent; readers don't block writer25    conn.execute("PRAGMA foreign_keys=ON")        # per-connection, off by default26    conn.execute("PRAGMA busy_timeout=5000")      # wait 5s on lock instead of failing27    conn.execute("PRAGMA synchronous=NORMAL")     # safe with WAL, much faster than FULL28    conn.row_factory = sqlite3.Row29    return conn30```3132## Fast bulk inserts3334```python35rows = [(i, f"name-{i}") for i in range(100_000)]36with connect("app.db") as conn:                   # context manager = one transaction37    conn.executemany("INSERT INTO users (id, name) VALUES (?, ?)", rows)38# One COMMIT (one fsync) instead of 100k. Order-of-magnitude speedup.39```4041## STRICT tables and type safety4243```sql44-- Without STRICT, 'abc' inserts fine into an INTEGER column (affinity only).45CREATE TABLE users (46  id   INTEGER PRIMARY KEY,          -- alias for rowid: fast, auto-increment47  name TEXT NOT NULL,48  age  INTEGER CHECK (age >= 0)49) STRICT;                            -- SQLite ≥ 3.37: types are enforced50```5152`INTEGER PRIMARY KEY` is the rowid — use it instead of `AUTOINCREMENT`53(AUTOINCREMENT adds overhead and is almost never needed).5455## Backups5657```sql58-- Online, consistent, from SQL (SQLite ≥ 3.27):59VACUUM INTO '/backups/app-2026-08-05.db';60```6162```bash63# CLI equivalent:64sqlite3 app.db ".backup '/backups/app.db'"65# Verify every backup:66sqlite3 /backups/app.db "PRAGMA integrity_check;"   # must print: ok67```6869```python70# Python online backup API (works while the app runs):71src = sqlite3.connect("app.db")72dst = sqlite3.connect("backup.db")73with dst:74    src.backup(dst)75```7677Never `cp app.db backup.db` while the app can write — and remember WAL means78recent commits live in `app.db-wal`, not the main file.7980## WAL maintenance8182```sql83PRAGMA wal_checkpoint(TRUNCATE);   -- merge -wal into the db and truncate it84PRAGMA journal_mode;               -- confirm: wal85PRAGMA wal_autocheckpoint;         -- default 1000 pages (~4MB)86```8788A `-wal` file that grows without bound means a long-lived read transaction is89pinning it — find and close that reader.9091## Single-writer pattern9293```python94# One writer thread owns the write connection; others enqueue.95import queue, threading9697write_q = queue.Queue()9899def writer(path):100    conn = connect(path)101    while True:102        sql, params = write_q.get()103        with conn:104            conn.execute(sql, params)105```106107Readers can each have their own connection — WAL lets them run concurrently108with the single writer.109110## Gotchas111112- `PRAGMA foreign_keys=ON` is **per connection**. Pools/ORMs must set it in a113  connect hook (SQLAlchemy: `event.listens_for(engine, "connect")`).114- `journal_mode=WAL` is per **database** (persistent), but `synchronous`,115  `busy_timeout`, `foreign_keys` are per connection.116- Python's `sqlite3` opens implicit transactions around DML and holds them —117  pass `isolation_level=None` (autocommit) and manage `BEGIN`/`COMMIT`118  explicitly if lock durations surprise you.119- `REAL` stores IEEE-754 doubles — money should be integer cents, not REAL.120- Date/time types don't exist; store ISO-8601 TEXT or unix-epoch INTEGER and121  pick one convention per database.122- WAL databases can't live on read-only media; use `PRAGMA query_only=ON` or123  rollback mode for read-only deployments.124- Dropping columns needs SQLite ≥ 3.35; before that it's create-new-table,125  copy, rename.126