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