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

# SQLite — Patterns

## Contents
- Connection setup (Python)
- Fast bulk inserts
- STRICT tables and type safety
- Backups
- WAL maintenance
- Single-writer pattern
- Gotchas

## Connection setup (Python)

```python
import sqlite3

def connect(path):
    conn = sqlite3.connect(path, timeout=5.0)     # timeout ≈ busy_timeout
    conn.execute("PRAGMA journal_mode=WAL")       # persistent; readers don't block writer
    conn.execute("PRAGMA foreign_keys=ON")        # per-connection, off by default
    conn.execute("PRAGMA busy_timeout=5000")      # wait 5s on lock instead of failing
    conn.execute("PRAGMA synchronous=NORMAL")     # safe with WAL, much faster than FULL
    conn.row_factory = sqlite3.Row
    return conn
```

## Fast bulk inserts

```python
rows = [(i, f"name-{i}") for i in range(100_000)]
with connect("app.db") as conn:                   # context manager = one transaction
    conn.executemany("INSERT INTO users (id, name) VALUES (?, ?)", rows)
# One COMMIT (one fsync) instead of 100k. Order-of-magnitude speedup.
```

## STRICT tables and type safety

```sql
-- Without STRICT, 'abc' inserts fine into an INTEGER column (affinity only).
CREATE TABLE users (
  id   INTEGER PRIMARY KEY,          -- alias for rowid: fast, auto-increment
  name TEXT NOT NULL,
  age  INTEGER CHECK (age >= 0)
) STRICT;                            -- SQLite ≥ 3.37: types are enforced
```

`INTEGER PRIMARY KEY` is the rowid — use it instead of `AUTOINCREMENT`
(AUTOINCREMENT adds overhead and is almost never needed).

## Backups

```sql
-- Online, consistent, from SQL (SQLite ≥ 3.27):
VACUUM INTO '/backups/app-2026-08-05.db';
```

```bash
# CLI equivalent:
sqlite3 app.db ".backup '/backups/app.db'"
# Verify every backup:
sqlite3 /backups/app.db "PRAGMA integrity_check;"   # must print: ok
```

```python
# Python online backup API (works while the app runs):
src = sqlite3.connect("app.db")
dst = sqlite3.connect("backup.db")
with dst:
    src.backup(dst)
```

Never `cp app.db backup.db` while the app can write — and remember WAL means
recent commits live in `app.db-wal`, not the main file.

## WAL maintenance

```sql
PRAGMA wal_checkpoint(TRUNCATE);   -- merge -wal into the db and truncate it
PRAGMA journal_mode;               -- confirm: wal
PRAGMA wal_autocheckpoint;         -- default 1000 pages (~4MB)
```

A `-wal` file that grows without bound means a long-lived read transaction is
pinning it — find and close that reader.

## Single-writer pattern

```python
# One writer thread owns the write connection; others enqueue.
import queue, threading

write_q = queue.Queue()

def writer(path):
    conn = connect(path)
    while True:
        sql, params = write_q.get()
        with conn:
            conn.execute(sql, params)
```

Readers can each have their own connection — WAL lets them run concurrently
with the single writer.

## Gotchas

- `PRAGMA foreign_keys=ON` is **per connection**. Pools/ORMs must set it in a
  connect hook (SQLAlchemy: `event.listens_for(engine, "connect")`).
- `journal_mode=WAL` is per **database** (persistent), but `synchronous`,
  `busy_timeout`, `foreign_keys` are per connection.
- Python's `sqlite3` opens implicit transactions around DML and holds them —
  pass `isolation_level=None` (autocommit) and manage `BEGIN`/`COMMIT`
  explicitly if lock durations surprise you.
- `REAL` stores IEEE-754 doubles — money should be integer cents, not REAL.
- Date/time types don't exist; store ISO-8601 TEXT or unix-epoch INTEGER and
  pick one convention per database.
- WAL databases can't live on read-only media; use `PRAGMA query_only=ON` or
  rollback mode for read-only deployments.
- Dropping columns needs SQLite ≥ 3.35; before that it's create-new-table,
  copy, rename.