SQLite: Pragmas, STRICT Tables, JSON1, FTS5 and WAL Reference Guide

# macOS — Homebrew (modern sqlite ≥ 3.45)
brew install sqlite

# Linux — Debian/Ubuntu
sudo apt install sqlite3

# Show version + compile-time options (FTS5, JSON1 should be present)
sqlite3 --version
sqlite3 :memory: "PRAGMA compile_options;" | head

# Open or create a DB file (no separate server, no users)
sqlite3 app.db

# Run a SQL file against a DB without entering the shell
sqlite3 app.db < schema.sql
sqlite3 app.db ".read schema.sql"

# One-shot query, machine-readable output
sqlite3 -json app.db "SELECT id, name FROM users LIMIT 5;"

# Python — stdlib only, no install
python -c "import sqlite3; print(sqlite3.sqlite_version)"

-- Recommended pragmas for a server-side app (run on every connection)
PRAGMA journal_mode = WAL;          -- concurrent readers + one writer
PRAGMA synchronous  = NORMAL;       -- safe with WAL, much faster than FULL
PRAGMA foreign_keys = ON;           -- off by default — must enable per-connection
PRAGMA busy_timeout = 5000;         -- wait 5s on SQLITE_BUSY instead of erroring
PRAGMA cache_size   = -64000;       -- negative = KiB → 64 MiB page cache
PRAGMA temp_store   = MEMORY;       -- keep temp tables in RAM
PRAGMA mmap_size    = 268435456;    -- memory-map up to 256 MiB of the db file

-- Maintenance — run periodically (e.g. nightly)
PRAGMA optimize;                    -- rebuild stats based on recent queries
PRAGMA wal_checkpoint(TRUNCATE);    -- force checkpoint and shrink the -wal file
PRAGMA integrity_check;             -- ok / corruption report

-- App-defined schema version (no separate table needed)
PRAGMA user_version = 7;
SELECT user_version FROM pragma_user_version;

-- Standalone FTS5 table (FTS owns the data)
CREATE VIRTUAL TABLE docs USING fts5(
    title,
    body,
    tokenize = 'porter unicode61 remove_diacritics 2'
);

INSERT INTO docs(title, body) VALUES
  ('Redis caching', 'Use redis to cache hot keys with TTL.'),
  ('Postgres indexes', 'B-tree and GIN are the workhorses.');

-- Phrase, boolean, prefix, NEAR
SELECT rowid, title FROM docs WHERE docs MATCH '"hot keys"';
SELECT rowid, title FROM docs WHERE docs MATCH 'redis AND ttl';
SELECT rowid, title FROM docs WHERE docs MATCH 'red*';
SELECT rowid, title FROM docs WHERE docs MATCH 'redis NEAR/3 cache';

-- Ranking + highlight + snippet
SELECT rowid,
       highlight(docs, 0, '', '') AS hl_title,
       snippet(docs, 1, '', '', '…', 24) AS hl_body,
       bm25(docs) AS score
FROM docs
WHERE docs MATCH ?
ORDER BY score
LIMIT 10;

-- Optimize internal segments after bulk loads
INSERT INTO docs(docs) VALUES ('optimize');

import sqlite3
from contextlib import closing

def connect(path: str) -> sqlite3.Connection:
    con = sqlite3.connect(path, isolation_level=None)  # autocommit; manage txns explicitly
    con.row_factory = sqlite3.Row                       # rows behave like dicts
    con.execute("PRAGMA journal_mode=WAL")
    con.execute("PRAGMA foreign_keys=ON")
    con.execute("PRAGMA busy_timeout=5000")
    return con

with closing(connect("app.db")) as con, con:           # outer `with con` = transaction
    con.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id    INTEGER PRIMARY KEY,
            email TEXT NOT NULL UNIQUE,
            name  TEXT
        )
    """)
    # Parameterized — always use ?, never f-strings
    con.executemany(
        "INSERT INTO users(email, name) VALUES (?, ?) "
        "ON CONFLICT(email) DO UPDATE SET name=excluded.name",
        [("a@x.io", "Ada"), ("b@x.io", "Bob")],
    )
    row = con.execute(
        "SELECT id, name FROM users WHERE email = ?", ("a@x.io",)
    ).fetchone()
    print(dict(row))                                    # {'id': 1, 'name': 'Ada'}

import sqlite3, json

con = sqlite3.connect("todo.db", isolation_level=None)
con.row_factory = sqlite3.Row
con.executescript("""
    PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;

    CREATE TABLE IF NOT EXISTS todos (
        id       INTEGER PRIMARY KEY,
        title    TEXT NOT NULL,
        body     TEXT,
        done     INTEGER NOT NULL DEFAULT 0,
        created  TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE VIRTUAL TABLE IF NOT EXISTS todos_fts USING fts5(
        title, body, content='todos', content_rowid='id'
    );

    CREATE TRIGGER IF NOT EXISTS todos_ai AFTER INSERT ON todos BEGIN
        INSERT INTO todos_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
    END;
""")

with con:
    con.execute("INSERT INTO todos(title, body) VALUES (?, ?)",
                ("Ship cheatsheet", "Write SQLite quick reference with FTS5 example."))

hits = con.execute("""
    SELECT t.id, t.title, snippet(todos_fts, 1, '[', ']', '…', 12) AS hit
    FROM todos_fts JOIN todos t ON t.id = todos_fts.rowid
    WHERE todos_fts MATCH ?
    ORDER BY bm25(todos_fts)
""", ("fts5 OR cheatsheet",)).fetchall()
print(json.dumps([dict(r) for r in hits], indent=2))