Run PRAGMA integrity_check as local maintenance evidence

By Batu · English technical notes

Also published in our Blogger archive.

Direct answer

PRAGMA integrity_check asks SQLite to inspect internal database consistency and returns ok when the checked database has no reported integrity error. Run it against a known local database file when you need a maintenance observation, then record the literal result beside the file identity and time of the check. It is useful because it examines SQLite structures rather than merely proving that one query happened to succeed.

The example creates a disposable file, commits a row, and reads the first value returned by the pragma. The second assertion deliberately checks ordinary application data separately: an integrity result does not say that a queue state machine, retry rule, or row contents are correct. That distinction keeps a maintenance signal from becoming a misleading health claim.

An edge case is a busy or inaccessible database. This small script does not retry locks, copy a live file, or decide whether a reported error is repairable. SQLite’s documentation also describes transactions separately; a successful COMMIT persists the example row, but it is not evidence that every future write will commit. Use an offline copy or a coordinated maintenance window when the database is actively used.

Complete example

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    database = Path(directory) / "queue.sqlite3"
    connection = sqlite3.connect(database)
    connection.execute("CREATE TABLE jobs (id INTEGER PRIMARY KEY, state TEXT)")
    connection.execute("INSERT INTO jobs VALUES (1, 'done')")
    connection.commit()

    result = connection.execute("PRAGMA integrity_check").fetchone()[0]
    assert result == "ok"
    assert connection.execute("SELECT state FROM jobs").fetchone()[0] == "done"
    print(f"integrity_check={result}")
    connection.close()

Expected stdout:

integrity_check=ok

Sources

- Official API documentation

Prepared with AI assistance. The example uses synthetic data; its stated limits apply.