Testing, tooling and benchmarks for SQLite apps

Integrity checks, dbstat, VACUUM and pragma tuning, in-memory test databases, deterministic testing and honest benchmarking.

Health and inspection

pragma integrity_check;              -- full structural check, slow on a large database
pragma quick_check;                  -- faster, skips some index validation
pragma foreign_key_check;            -- reports violations without failing
pragma integrity_check('book_fts');  -- one table

-- where the file is going
pragma page_count;
pragma page_size;
pragma freelist_count;               -- free pages: VACUUM reclaims them
select (select * from pragma_page_count()) * (select * from pragma_page_size()) as size;

-- per-object sizing (dbstat is a compile-time option)
select name, sum(pgsize) as bytes, count(*) as pages
from dbstat group by name order by bytes desc limit 20;

-- what indexes actually exist
select name, tbl_name, sql from sqlite_master where type in ('table','index') order by tbl_name;

Testing against a real database

import sqlite3, pytest

@pytest.fixture
def db():
    con = sqlite3.connect(":memory:")           # fast, isolated, and really SQLite
    con.executescript(open("schema.sql").read())
    con.execute("pragma foreign_keys = on")
    yield con
    con.close()

def test_upsert_is_idempotent(db):
    stmt = "insert into book (isbn, title) values (?, ?) on conflict (isbn) do update set title = excluded.title"
    db.execute(stmt, ("9780441013593", "First"))
    db.execute(stmt, ("9780441013593", "Second"))
    rows = db.execute("select title from book where isbn = ?", ("9780441013593",)).fetchall()
    assert rows == [("Second",)]

def test_schema_matches_the_migration_chain(db, tmp_path):
    file_db = tmp_path / "migrated.db"
    run_migrations(str(file_db))
    fresh = sqlite3.connect(":memory:")
    fresh.executescript(open("schema.sql").read())
    assert schema_of(str(file_db)) == schema_of(fresh)   # compare sqlite_master

• An in-memory database runs the same engine with the same semantics, so it catches real SQL errors - unlike a mocked repository.
• Set the same pragmas in tests as in production. foreign_keys is off by default, so a test that does not enable it passes while production rejects the row.
• Compare the schema produced by running the migrations with the schema file, so drift between the two is caught in CI.
• Test with the concurrency mode you ship: a test on one connection never exercises busy_timeout or the single-writer behaviour.
• Seed with fixed timestamps so assertions on dates are deterministic; now makes tests flaky across a second boundary.

# measure, do not guess
import sqlite3, time

def bench(con, label, stmt, params=(), n=10_000):
    start = time.perf_counter()
    for _ in range(n):
        con.execute(stmt, params).fetchall()
    elapsed = time.perf_counter() - start
    print(f"{label:34s} {elapsed / n * 1e6:9.1f} us/query")

con = sqlite3.connect("app.db")
con.execute("pragma cache_size = -32000")
bench(con, "point lookup by pk", "select * from book where id = ?", (1,))
bench(con, "lookup by indexed column", "select * from book where isbn = ?", ("9780441013593",))
bench(con, "full scan aggregate", "select count(*), avg(pages) from book")

Tooling and maintenance

• Use the .timer on dot command in the CLI for rough timings, and .eqp on to print the query plan automatically for every statement.
• .dump produces SQL that recreates the database; it is the most portable backup and the easiest format to inspect.
• .import with --csv is the fastest way to load a CSV, and it wraps the whole import in one transaction.
• Run pragma optimize periodically: it analyses tables whose statistics are stale, and does nothing when they are fine.
• VACUUM rebuilds the file, reclaiming free pages and defragmenting. It needs free disk space equal to the database size and takes an exclusive lock.
• VACUUM INTO 'copy.db' does the same work into a new file, which doubles as a consistent backup and leaves the original intact.

-- routine maintenance for a single-file application
pragma optimize;
pragma wal_checkpoint(truncate);
vacuum into 'backup/app-2026-09-18.db';
vacuum;

Schedule maintenance when the application is idle. A VACUUM holds an exclusive lock for the whole operation, and a checkpoint blocks writers for a moment - both are fine in a maintenance window and disruptive in the middle of a busy hour.

FAQ

Related

Transactions, locking and concurrency Migrations and schema versioning for embedded apps

Last refreshed 2026-09-18.