Python Deployment Validation Pipelines #

A hardened SQLite configuration that lives only in a wiki page, a shell script someone ran once, or a developer’s memory is not hardened at all — it is a hope. The moment a fleet grows past a handful of devices, the gap between the PRAGMA baseline you designed and the one actually running on a field gateway becomes invisible until something corrupts, stalls, or silently drops referential integrity. This part of the SQLite Architecture & Production Hardening guide closes that gap by treating the database’s configuration and schema as code: every value that matters — journal_mode, synchronous, busy_timeout, cache_size, mmap_size, foreign_keys — is declared once, applied deterministically, read back, and asserted inside a continuous-integration check that fails the build before the image is ever promoted to hardware you cannot easily reach. The two companion pages take the narrow cases: validating PRAGMA configuration in CI drills into the read-back assertion loop, and smoke-testing migrations on edge fleets covers proving a schema change against a real device snapshot.

Figure — configuration and schema flow from a versioned source of truth through a CI validator that either blocks the release or promotes the artifact to the fleet.

Core Mechanism & Crash-Safety Defaults #

The reason a validation pipeline is worth building at all is that almost every SQLite setting that matters for durability is connection-scoped and silent on failure. PRAGMA journal_mode is one of the few that persists in the database header; the rest — synchronous, busy_timeout, cache_size, mmap_size, foreign_keys — reset to the compile-time default on every new handle and produce no error when you set them wrong. Set foreign_keys on a build compiled with SQLITE_OMIT_FOREIGN_KEY, misspell a pragma name so it parses as a no-op query, or apply synchronous after an implicit transaction has already opened, and SQLite returns success. The value you believe is in force and the value actually governing the write path have quietly diverged, and nothing on the device will tell you.

That silence is precisely why the baseline must be read back. Applying a pragma proves you sent a statement; querying it back proves the engine accepted it on this connection and this build. The crash-safety chain depends on the whole set agreeing: journal_mode = WAL is the prerequisite that makes synchronous = NORMAL non-corrupting across power loss, foreign_keys = ON is what keeps a partially-applied migration from leaving orphaned rows, and busy_timeout is what turns a lock collision into a bounded wait instead of an immediate SQLITE_BUSY. A pipeline that asserts only one of these has verified a link, not the chain.

The second mechanism is the fixture. A configuration can read back perfectly and still ship a broken schema, so the validator seeds a small representative database, applies the pending migrations against it, and runs PRAGMA integrity_check and PRAGMA foreign_key_check on the result. integrity_check walks the B-tree structure and reports the first structural faults; foreign_key_check reports rows that violate declared foreign keys — a distinct check that integrity_check does not perform. Running both against a seeded fixture in CI catches a migration that corrupts an index or strands child rows before the image reaches a device, where the same fault would surface as SQLITE_CORRUPT in the field.

Step-by-Step Implementation #

1. Pin and verify the sqlite3 build #

The most corrosive failure in a fleet pipeline is a version skew between the CI runner and the device. CI runs on a modern amd64 image with a recent libsqlite3; the target ships an eMMC image whose libsqlite3.so is two years older and compiled with different options. A migration that uses a RETURNING clause (SQLite 3.35+) or a strict table (3.37+) passes CI and fails on the device with a syntax error. Pin the expectation and assert it, both the library version and the compile-time options your baseline depends on:

import sqlite3

MIN_SQLITE = (3, 37, 0)   # STRICT tables (3.37), RETURNING (3.35), UPSERT (3.24)

def verify_build() -> None:
    ver = sqlite3.sqlite_version_info
    if ver < MIN_SQLITE:
        raise RuntimeError(f"libsqlite3 {ver} < required {MIN_SQLITE}")

    conn = sqlite3.connect(":memory:")
    try:
        opts = {row[0] for row in conn.execute("PRAGMA compile_options;")}
        # These omissions turn a hardening pragma into a silent no-op on the target build.
        for forbidden in ("OMIT_FOREIGN_KEY", "OMIT_WAL", "DEFAULT_FOREIGN_KEYS=0"):
            assert not any(forbidden in o for o in opts), f"unsafe build option: {forbidden}"
        # Confirm the runtime library matches what sqlite3 reports it linked against.
        linked = conn.execute("SELECT sqlite_version();").fetchone()[0]
        assert linked == sqlite3.sqlite_version, "sqlite3 module/library version mismatch"
    finally:
        conn.close()

Run this identical function in CI and as the first step of the device’s boot self-test. If the two environments disagree, the build is rejected before anything downstream runs — the version contract is the foundation every later assertion stands on.

2. Declare the expected-PRAGMA contract #

The baseline must have exactly one authoritative form. Scatter the values across a connection factory, a migration script, and a docstring and they will drift; declare them once as data and every consumer reads the same source. Each entry pairs the value with the read-back you expect, so the table doubles as the assertion spec:

The read-back column is not decoration. journal_mode echoes the mode string, so you assert wal, not WAL; synchronous and foreign_keys are booleans/enums that read back as integers, so NORMAL becomes 1 and ON becomes 1; cache_size reads back exactly as you set it, negative sign and all. Encoding these in a dictionary keyed by pragma name is what lets the validator in step 3 loop instead of hand-writing a fragile assertion per line.

3. Run the validator and exit nonzero on drift #

The validator opens the database, applies the baseline, reads every pragma back against the contract, dry-runs the pending migrations inside a transaction it rolls back, and returns a nonzero exit code the instant anything diverges. Nonzero is the whole point: CI gates on the exit status, so a silent drift becomes a red build:

import sqlite3
import sys

BASELINE = {
    "journal_mode": ("WAL", "wal"),
    "synchronous": ("NORMAL", 1),
    "busy_timeout": ("5000", 5000),
    "cache_size": ("-8000", -8000),
    "mmap_size": ("268435456", 268435456),
    "foreign_keys": ("ON", 1),
}

def apply_and_verify(conn: sqlite3.Connection) -> list[str]:
    drift = []
    for pragma, (set_to, expect) in BASELINE.items():
        try:
            conn.execute(f"PRAGMA {pragma} = {set_to};")           # apply the hardened value
            got = conn.execute(f"PRAGMA {pragma};").fetchone()[0]   # READ IT BACK, never trust the write
        except sqlite3.Error as exc:
            drift.append(f"{pragma}: pragma raised {exc!r}")
            continue
        got = got.lower() if isinstance(got, str) else got
        want = expect.lower() if isinstance(expect, str) else expect
        if got != want:
            drift.append(f"{pragma}: applied {got!r}, contract wants {want!r}")
    return drift

def dry_run_migrations(conn: sqlite3.Connection, migrations: list[str]) -> list[str]:
    problems = []
    try:
        conn.execute("BEGIN;")                     # everything here is provisional
        for stmt in migrations:
            conn.executescript(stmt)
        # Both checks: integrity_check walks the B-tree; foreign_key_check finds orphans.
        integ = conn.execute("PRAGMA integrity_check;").fetchone()[0]
        if integ != "ok":
            problems.append(f"integrity_check: {integ}")
        if conn.execute("PRAGMA foreign_key_check;").fetchall():
            problems.append("foreign_key_check: orphaned rows after migration")
    except sqlite3.Error as exc:
        problems.append(f"migration dry-run failed: {exc!r}")
    finally:
        conn.execute("ROLLBACK;")                  # never persist the dry run
    return problems

def main(db_path: str, migrations: list[str]) -> int:
    try:
        conn = sqlite3.connect(db_path, isolation_level=None)  # autocommit: PRAGMAs apply now
    except sqlite3.Error as exc:
        print(f"cannot open {db_path}: {exc!r}", file=sys.stderr)
        return 1
    try:
        findings = apply_and_verify(conn) + dry_run_migrations(conn, migrations)
    finally:
        conn.close()
    if findings:
        for f in findings:
            print(f"DRIFT: {f}", file=sys.stderr)
        return 1                                   # nonzero -> CI fails the build
    print("baseline + migrations verified")
    return 0

if __name__ == "__main__":
    raise SystemExit(main("build/fixture.db", []))

Two details make this trustworthy. isolation_level=None puts the connection in autocommit so each pragma takes effect immediately rather than being swallowed by an implicit BEGIN. And the migration dry-run always rolls back — CI proves the migration can apply cleanly without mutating the fixture, so the same check reruns identically on every push. The read-back loop mirrors the PRAGMA Optimization Guide discipline of never trusting a silent write.

Workload Profiles & Threshold Reference #

What you validate — and how hard you fail on a given finding — depends on the deployment shape. A CI scratch database that lives for one test run does not need the same recovery guarantees as an unattended field gateway, but every profile still asserts its own baseline rather than assuming the default.

The two constants across every row: assert sqlite_version() parity between CI and the target before anything else, and never let a migration reach the promotion gate without a clean integrity_check and foreign_key_check on a fixture that resembles production data. A migration validated only against an empty schema has proven syntax, not safety.

Failure Documentation & Edge Cases #

sqlite_version mismatch between CI and device #

Trigger: CI validates against libsqlite3 3.45 on the runner; the eMMC image carries 3.31. A migration using ALTER TABLE ... DROP COLUMN (3.35+) or a STRICT table passes CI and throws a syntax error on first boot after the device updates.

Diagnosis: compare SELECT sqlite_version(); on the runner and on the device image; a difference in the minor version is enough to change accepted syntax.

Fallback: pin MIN_SQLITE (step 1) to the lowest version anywhere in the fleet and run verify_build() in both CI and the device self-test, so the older runtime is the contract, not an afterthought.

A PRAGMA is silently ignored #

Trigger: a pragma is misspelled, applied on a build compiled with SQLITE_OMIT_*, or set after an implicit transaction opened — SQLite returns success and the value never takes effect.

Diagnosis: the read-back in apply_and_verify reports applied X, contract wants Y; a pragma that raises instead surfaces as a pragma raised finding.

Fallback: keep every connection in autocommit while applying the baseline, assert each read-back against the contract table, and reject the build on any mismatch — covered end to end in validating PRAGMA configuration in CI.

A migration applies only partially #

Trigger: a multi-statement migration succeeds on the first CREATE/ALTER and fails on the third; without a wrapping transaction the schema is left half-migrated and the next run sees an inconsistent starting state.

Diagnosis: PRAGMA user_version disagrees with the migration ledger, or integrity_check on the fixture reports a dangling index after the dry-run.

Fallback: wrap the whole migration in one BEGIN/ROLLBACK for the dry-run and one BEGIN/COMMIT for the real apply, and gate on integrity_check — the integrity checking and corruption recovery guide covers the recovery path when a device did tear mid-migration.

foreign_keys off by default lets orphans through #

Trigger: the pipeline assumes referential integrity but never asserts foreign_keys=ON; because the default is OFF, a migration that deletes parent rows leaves orphaned children the application later reads as valid.

Diagnosis: PRAGMA foreign_keys; reads back 0, and PRAGMA foreign_key_check; returns rows after the migration dry-run.

Fallback: include foreign_keys in the contract table, enforce it on every connection, and run foreign_key_check as a distinct gate from integrity_check, since the latter does not validate foreign keys.

Production Hardening Checklist #

• sqlite_version() parity asserted between CI and the lowest runtime in the fleet
• Required compile_options verified; OMIT_FOREIGN_KEY / OMIT_WAL treated as build failures
• The PRAGMA baseline exists as one authoritative contract table, not scattered literals
• Every pragma is applied in autocommit and read back, with the read-back asserted against the contract
• journal_mode compared as the lowercased mode string (wal), booleans as integers (1)
• Pending migrations dry-run inside a transaction that always rolls back in CI
• PRAGMA integrity_check runs on a fixture resembling production data and must return ok
• PRAGMA foreign_key_check runs as a separate gate and must return zero rows
• The validator exits nonzero on any drift and CI gates the release on that exit status
• The identical validator runs as a device boot self-test, not only in CI

Related Pages #

• Validating PRAGMA Configuration in CI — the read-back assertion loop that fails a build on configuration drift.
• Smoke-Testing Migrations on Edge Fleets — proving a schema change against a real device snapshot before rollout.
• PRAGMA Optimization Guide — the connection-scoped baseline and initialization order this pipeline enforces.
• Integrity Checking & Corruption Recovery — the recovery path when a device tears mid-migration in the field.
• SQLite Architecture & Production Hardening — the parent guide for the full hardening discipline.