Chapter 17 · Schema Evolution, Migrations, Testing, and Release Compatibility
Database Tests: Schema, Constraints, Queries, Transactions, and Concurrency
Build repeatable tests that verify schema shape, rejection behavior, migration results, transaction atomicity, query semantics, and real two-connection locking behavior.
Learning outcomes
“The query returned rows” is not a database test strategy. A SQLite release can fail because the schema shape is wrong, a forbidden row is accepted, a migration loses an index, a transaction leaks a partial state, or a concurrency path works only in a one-connection demo. Database tests should target those contracts directly.
Test schema shape with SQLite introspection rather than brittle CREATE TABLE string matching.
Test expected constraint rejection using stable error classes/codes where available.
Test query results with deterministic fixtures and explicit ordering.
Test transaction atomicity by injecting failure inside a multi-step business operation.
Use temporary database files for WAL/locking/multi-connection tests instead of assuming :memory: is equivalent.
Build a repeatable Python suite with per-test isolation and cleanup.
Five layers of database tests
| Layer | Question | Example |
|---|---|---|
| Schema | Is the deployed structure the one the code expects? | Columns, STRICT flag, indexes, FKs, triggers, views. |
| Constraints | Does invalid state get rejected? | Duplicate code, orphan child, invalid status, blank note. |
| Queries | Does valid data produce the expected result? | Active-device report, ordering, aggregate counts. |
| Transactions | Can partial business state escape after failure? | Inventory/maintenance workflow rolls back as a unit. |
| Concurrency | Does the workflow behave with independent handles? | Second writer receives/waits on SQLITE_BUSY as designed. |
Why temporary files matter
Each plain :memory: connection is a separate database. It also has no OS database file, WAL companion file, or ordinary cross-process file locking. Use :memory: for fast unit tests that genuinely need only one connection; use a temporary file for anything whose semantics depend on multiple connections, journal modes, file paths, backups, or locking.
from pathlib import Pathfrom tempfile import TemporaryDirectoryimport sqlite3with TemporaryDirectory(prefix="fieldnotes-test-") as td: db = Path(td) / "test.sqlite" con = sqlite3.connect(db) try: con.execute("PRAGMA foreign_keys=ON") # create deterministic fixture finally: con.close() # directory and DB disappear after the test blockSchema tests inspect meaning
Prefer PRAGMAs that expose parsed schema facts. This keeps tests focused on semantics instead of whitespace or SQL formatting.
cols = con.execute("PRAGMA table_xinfo('device')").fetchall()by_name = {{row[1]: row for row in cols}}assert by_name['device_id'][5] == 1 # pk positionassert by_name['device_code'][3] == 1 # NOT NULLindexes = {{row[1] for row in con.execute("PRAGMA index_list('device')")}}assert 'idx_device_site_status' in indexesfks = con.execute("PRAGMA foreign_key_list('device')").fetchall()assert any(row[2] == 'site' and row[3] == 'site_id' for row in fks)# Current table_list exposes the STRICT flag.row = con.execute("PRAGMA table_list('device')").fetchone()print('strict flag:', row[5])Constraint rejection is a successful test
A database contract includes invalid inputs that must fail. In Python, catch sqlite3.IntegrityError; when the runtime exposes sqlite_errorcode/sqlite_errorname, assert the stable class/code instead of coupling the test to the full English error message.
import sqlite3try: con.execute(""" INSERT INTO device(site_id, device_code, device_name, status) VALUES (1, 'DUP-001', 'duplicate', 'impossible') """)except sqlite3.IntegrityError as exc: print(type(exc).__name__) print(getattr(exc, 'sqlite_errorcode', None)) print(getattr(exc, 'sqlite_errorname', None))else: raise AssertionError('invalid device status was accepted')Messages can gain detail across SQLite/driver versions. A test that asserts the entire message string can fail after an upgrade even when the database is correctly rejecting the same invariant.
Query tests need deterministic fixtures
con.executemany( "INSERT INTO site(site_id,site_code,site_name,active) VALUES (?,?,?,?)", [(1,'NORTH','North Plant',1),(2,'LAB','Harbor Lab',1)])con.executemany( """INSERT INTO device(device_id,site_id,device_code,device_name,status) VALUES (?,?,?,?,?)""", [(1,1,'PUMP-007','Pump 7','active'), (2,1,'FAN-014','Fan 14','inspection_due'), (3,2,'SENS-003','Sensor 3','active')])rows = con.execute(""" SELECT device_code FROM device WHERE status='active' ORDER BY device_code""").fetchall()assert rows == [('PUMP-007',), ('SENS-003',)]The explicit ORDER BY is part of the test contract. SQLite does not guarantee the order of rows from an unordered SELECT.
Transaction atomicity test through failure injection
The test should fail between two business steps and prove that neither step remains committed.
con.isolation_level = Nonecon.execute("BEGIN IMMEDIATE")try: con.execute("UPDATE inventory SET qty=qty-1 WHERE item_id=1") # Deliberate failure after first write. raise RuntimeError('injected after decrement') con.execute("INSERT INTO dispatch(item_id) VALUES (1)") con.commit()except Exception: con.rollback()qty = con.execute("SELECT qty FROM inventory WHERE item_id=1").fetchone()[0]dispatches = con.execute("SELECT count(*) FROM dispatch").fetchone()[0]assert qty == 10assert dispatches == 0Migration test: old fixture → runner → new assertions
Do not test a migration only on an empty latest-schema database. Create the predecessor schema and representative legacy data, run the real migration runner, then verify both new behavior and transformed old rows.
create_v1_fixture(db)assert read_user_version(db) == 1migrate(db, target=2)with sqlite3.connect(db) as con: assert con.execute("PRAGMA user_version").fetchone()[0] == 2 cols = {{r[1] for r in con.execute("PRAGMA table_xinfo('migration_device')")}} assert 'location' in cols assert con.execute("SELECT count(*) FROM migration_device").fetchone()[0] == 3# Duplicate execution policy: running the runner again is a no-op at version 2,# not a second execution of migration 2.migrate(db, target=2)Two-connection concurrency test
A critical write workflow should be exercised through two independent connections to the same temporary file. The following intentionally uses zero busy timeout so the test observes contention immediately and deterministically.
a = sqlite3.connect(db, timeout=0.0, isolation_level=None)b = sqlite3.connect(db, timeout=0.0, isolation_level=None)try: a.execute("BEGIN IMMEDIATE") a.execute("UPDATE counter SET value=value+1 WHERE id=1") try: b.execute("BEGIN IMMEDIATE") except sqlite3.OperationalError as exc: assert getattr(exc, 'sqlite_errorcode', None) == sqlite3.SQLITE_BUSY else: raise AssertionError('second writer unexpectedly acquired transaction') a.commit() b.execute("BEGIN IMMEDIATE") b.execute("UPDATE counter SET value=value+1 WHERE id=1") b.commit()finally: a.close(); b.close()This is not a throughput benchmark. It proves the application’s expected conflict behavior. A separate test can set a bounded busy timeout and coordinate threads/processes to prove waiting/retry policy.
One compact repeatable suite
tests/ test_schema.py test_constraints.py test_queries.py test_transactions.py test_migrations.py test_concurrency.pyEvery test: create isolated temp DB (unless true single-connection :memory: unit test) initialize connection PRAGMAs apply deterministic schema/fixture execute one behavior assert stable outcomes/codes close connections let temp fixture be deletedCheckpoint
What kind of test is missing?
Identify the blind spot.
- A test verifies only SELECT results after a migration.
- A locking test uses two separate :memory: connections.
- A constraint test asserts one exact English error message.
- A transaction test never injects failure between writes.
- A migration test starts directly from the newest schema.
- A query-result test omits ORDER BY but asserts row order.
Review the answers
The first misses schema/constraints/dependencies. Two plain :memory: connections do not share one file, so the locking test is invalid. Exact messages are brittle; prefer exception class/code plus minimal semantic checks. Without failure injection atomic rollback is untested. Starting from newest schema never executes the migration. Unordered SELECT output has no guaranteed row order.
Bridge to compatibility testing
A suite can be perfect on one developer machine and still fail on an older bundled SQLite or a build without FTS5. Lesson 5 adds the runtime itself to the test matrix: version, source ID, compile options, and direct capability probes.