Validating PRAGMA Configuration in CI

A connection factory that sets the right pragmas is not the same as a database that runs them. Because most SQLite pragmas are connection-scoped and fail silently, a refactor that reorders two lines, a build compiled without an option your baseline assumes, or a read-only filesystem that quietly refuses journal_mode = WAL can leave production running synchronous = FULL, foreign_keys = OFF, or a rollback journal you thought you had abandoned — with nothing in the logs to show for it. The fix is a CI check that opens the database exactly as production does, reads every hardened pragma back, and fails the build when the live set drifts from the contract. This is the focused, testable core of the Python Deployment Validation Pipelines topic within the SQLite Architecture & Production Hardening guide, and it earns its own page because the read-back comparison has enough sharp edges — string versus integer results, per-connection versus per-database scope, platform-dependent defaults — to get subtly wrong.

CI pragma assertion loop: dump live values, diff against contract, gate the build The CI job opens the database as production does and dumps the live pragma values. Those are diffed against the expected contract map. If they match, the check passes and the build is green. If any value drifts, the check exits nonzero and the build fails, reporting the pragma, the applied value, and the expected value. Open as prod dump PRAGMAs == contract? live vs expected Build green exit 0 Build fails exit 1 · reports drift match drift

Diagnosis

Before writing the assertion, see the drift for yourself. Open the database the way production opens it — same path, same URI flags, same connect arguments — and dump the pragmas that make up your hardened set alongside the values you expect. A side-by-side dump turns an invisible discrepancy into a printed line:

import sqlite3

EXPECTED = {
    "journal_mode": "wal",     # read-back is the mode string, lowercased
    "synchronous": 1,          # NORMAL reads back as the integer 1
    "foreign_keys": 1,         # ON reads back as 1; default is 0 (OFF)
    "busy_timeout": 5000,      # milliseconds
    "cache_size": -8000,       # negative == KiB (8 MB), not page count
}

conn = sqlite3.connect("build/app.db")
for pragma, want in EXPECTED.items():
    got = conn.execute(f"PRAGMA {pragma};").fetchone()[0]
    got = got.lower() if isinstance(got, str) else got
    flag = "OK " if got == want else "DRIFT"
    print(f"{flag} {pragma:14} live={got!r:>10}  expected={want!r}")
conn.close()

The output tells you which of the two failure shapes you have. If journal_mode prints delete when you expected wal, the mode never switched — often because the filesystem is read-only or the process lacks write access to the directory, and SQLite fell back rather than erroring. If synchronous prints 2 when you expected 1, a FULL default leaked in because the pragma was set on a different connection than the one you are inspecting, or the set statement was swallowed inside an open transaction. Naming the exact drifting pragma and its live value is the entire diagnosis; the assertion in the next section just makes CI do this same comparison and fail on it.

Solution

Encode the expected set once as a dictionary and let a single test loop over it. A dict-driven check has one place to update when the baseline changes and cannot fall out of sync the way a hand-written assertion per pragma does. The connection must be opened by the same factory production uses — validating a throwaway connection configured inline proves nothing about the real code path:

import sqlite3
import pytest

# The one authoritative expected map. Values are the READ-BACK form, not what you set:
# you set NORMAL/ON, but PRAGMA returns 1; you set WAL, PRAGMA returns the string "wal".
EXPECTED = {
    "journal_mode": "wal",
    "synchronous": 1,
    "foreign_keys": 1,
    "busy_timeout": 5000,
    "cache_size": -8000,
    "mmap_size": 268435456,
}

def open_production_connection(path: str) -> sqlite3.Connection:
    """The real factory under test — imported from application code in practice."""
    conn = sqlite3.connect(path, isolation_level=None)   # autocommit: PRAGMAs apply immediately
    conn.execute("PRAGMA journal_mode = WAL;")           # persistent header flag
    conn.execute("PRAGMA synchronous = NORMAL;")         # no per-commit fsync; safe under WAL
    conn.execute("PRAGMA foreign_keys = ON;")            # enforce FKs; default OFF ignores them
    conn.execute("PRAGMA busy_timeout = 5000;")          # bounded lock wait in ms
    conn.execute("PRAGMA cache_size = -8000;")           # 8 MB page cache; negative == KiB
    conn.execute("PRAGMA mmap_size = 268435456;")        # 256 MB mmap reads; 0 on 32-bit
    return conn

@pytest.fixture
def conn(tmp_path):
    c = open_production_connection(str(tmp_path / "app.db"))
    yield c
    c.close()

@pytest.mark.parametrize("pragma,expected", EXPECTED.items())
def test_pragma_matches_contract(conn, pragma, expected):
    try:
        row = conn.execute(f"PRAGMA {pragma};").fetchone()   # read the LIVE value back
    except sqlite3.Error as exc:
        pytest.fail(f"PRAGMA {pragma} raised {exc!r} on this build")
    assert row is not None, f"PRAGMA {pragma} returned no row"
    got = row[0]
    got = got.lower() if isinstance(got, str) else got
    want = expected.lower() if isinstance(expected, str) else expected
    assert got == want, f"{pragma} drift: live={got!r} expected={want!r}"

Parametrizing over EXPECTED.items() gives one test case per pragma, so a failure names the exact setting rather than aborting the whole suite on the first mismatch. The comparison normalizes types deliberately: string results (journal_mode) are lowercased before comparing, integer results (synchronous, foreign_keys, busy_timeout, cache_size, mmap_size) compare directly, and the try/except sqlite3.Error turns a pragma that raises on a stripped-down build into a clear failure instead of a traceback.

Verification

Confirm the check actually fails when it should — a green assertion that can never go red is worthless. Break one pragma deliberately and watch the build turn red, then restore it:

# Run the suite; expect all EXPECTED entries to pass on a correct build.
pytest -q test_pragma_contract.py

# Now prove it catches drift: temporarily flip synchronous to FULL in the factory
# and rerun — the synchronous case must fail with "synchronous drift: live=2 expected=1".
pytest -q test_pragma_contract.py::test_pragma_matches_contract
echo "exit status: $?"    # must be nonzero so CI gates on it

A nonzero exit status is the contract with CI: the pipeline gates the release on pytest’s return code, so a drift that the assertion catches becomes a blocked build. Also verify the check runs against the production factory by importing it, not a copy — if open_production_connection is duplicated into the test, the two will drift and the test will pass while production regresses.

Failure Modes & Gotchas

Connection-scoped versus database-scoped pragmas are being asserted the same way. Most of the hardened set — synchronous, foreign_keys, busy_timeout, cache_size, mmap_size — is per connection and resets to the compile-time default on every new handle, so asserting them on a connection other than the one production uses proves nothing. journal_mode is different: it is a persistent property of the database file, so once set it survives across connections. Assert the connection-scoped ones on the exact factory output, and remember that a passing journal_mode on a fresh handle does not imply the others were applied — they must be set again on that handle.

Compile-time defaults differ per platform. The runner may link a libsqlite3 whose default cache_size or mmap_size differs from the target’s, so a value you never explicitly set can read back differently on the device than in CI. Assert only values your factory explicitly sets, and pair this check with a PRAGMA compile_options and sqlite_version() assertion so a platform whose defaults or omitted features differ is caught as a build failure rather than surfacing as silent drift in the field.

PRAGMA journal_mode returns the mode, and a read-only filesystem keeps delete. Setting journal_mode = WAL returns the resulting mode as a row, and if WAL cannot be established — the directory is read-only, the file is on a filesystem without the shared-memory support WAL needs, or the process lacks write permission — SQLite silently returns delete (or memory) instead of raising. Code that sets WAL and moves on never notices. The read-back assertion is exactly what catches it: compare the returned string to wal and fail the build when the environment forced a fallback, a trap explored further in switching from DELETE to WAL mode safely.