Authority lesson · 984 words
SQLite, Transactions, Migrations, and Repository Design
Transactions protect invariants, not individual statements
A transaction is useful when several changes must succeed or fail as one business decision. The important question is not “should this function use a transaction?” but “which invariant would be broken if execution stopped between these writes?” Creating an invoice and its line items, reserving stock while recording an order, or changing a tenant membership while writing an audit event are examples where partial success can leave the system lying about reality. State the invariant in plain language before writing SQL. Then keep the transaction boundary narrow enough to complete quickly while still enclosing every change needed to preserve that promise.
Avoid keeping a transaction open while waiting on HTTP, email, user input, or a slow filesystem operation. Long transactions retain locks and increase contention. When a workflow crosses the database and an external provider, use a durable local state transition, an outbox or job record, and an idempotent worker rather than pretending one database transaction can control the remote system. A commit proves only that the database accepted the local changes; it does not prove a network side effect occurred.
Constraints are executable business rules
Application validation improves messages, but database constraints provide the final protection against races and alternate code paths. Use unique constraints for idempotency keys and natural identifiers, foreign keys for ownership, check constraints for impossible values, and not-null rules for genuinely required state. A repository method should not duplicate those rules as informal assumptions. It should translate constraint failures into meaningful domain outcomes and preserve enough detail for diagnosis without exposing sensitive data.
SQLite is excellent for local tools and many production workloads, but its locking and type behaviour are not identical to PostgreSQL. If production uses PostgreSQL, run transaction, concurrency, migration, and constraint tests there. SQLite-only tests can miss isolation differences, unsupported SQL, case behaviour, index choices, and concurrent-write failures. Treat the production engine as part of the contract.
Migrations are releases with data consequences
A migration must be reviewed as both schema change and operational change. Ask how long it can lock a table, whether old and new application versions can coexist, how existing rows receive a valid value, and what happens during rollback. Prefer expand-and-contract changes: add a compatible field or table, deploy code that writes both shapes, backfill in bounded batches, switch reads, verify, then remove the old shape in a later release. This reduces the chance that one irreversible command becomes the entire deployment plan.
Backups do not automatically make migrations safe. A useful recovery plan states which backup, how long restoration takes, which writes would be lost, and how the application is held during recovery. For high-value data, rehearse migration and restore on a disposable copy. Record row counts, checksums, failed records, duration, and the exact application and database versions used.
Repositories should clarify persistence rather than erase it
A repository can centralise queries, transaction expectations, tenant scoping, and mapping between storage rows and domain objects. It becomes harmful when it promises that every database is interchangeable or hides whether a query loads one row or ten thousand. Name methods by intent, return explicit result types, and keep query cost visible. A method such as reserve_available_slot communicates more than save, and it can document the lock or uniqueness rule that makes the reservation safe.
Keep transaction ownership clear. Either the service starts the unit of work and calls several repositories, or a repository method performs one complete atomic operation. Avoid hidden nested commits. Tests should prove rollback, duplicate handling, tenant isolation, and interrupted backfills. Operational dashboards should expose migration status, stale jobs, lock waits, and repeated constraint failures because persistence errors are often signals of a design or workload problem, not merely exceptions to log.
Scenario
A migration adds a required owner field and blocks production
A deployment adds a non-null owner_id to a large table and immediately backfills every row in one statement. The table remains locked, web requests queue, and rollback cannot restore writes already attempted by the new application. An expand-and-contract plan would add the nullable column and index, deploy compatible reads and writes, backfill in batches with checkpoints, verify ownership, and only then enforce the requirement.
- Identify compatibility between old and new code
- Measure lock and backfill behaviour on realistic data
- Use checkpoints and resumable batches
- Separate schema enforcement from data population
Worked example
Design an idempotent invoice repository
- Create a unique constraint on organisation_id plus idempotency_key.
- Start one transaction, insert the invoice, insert lines, and write an outbox event.
- If the unique constraint fires, load and return the existing invoice rather than creating another.
- Commit before a worker contacts the payment or email provider.
- Test the same key concurrently against PostgreSQL and verify one invoice and one outbox event.
Checklist
Use this before acting
- State the invariant protected by each transaction
- Keep network and human waits outside database transactions
- Use database constraints for race-safe rules
- Test production-engine behaviour
- Plan migrations with compatibility, backfill, verification, and rollback
- Record migration and restore evidence
- Make repository query cost and tenant scope visible
Common mistakes
Failure patterns to avoid
- Wrapping every function in a transaction without naming an invariant
- Relying only on application validation
- Testing concurrency only with SQLite
- Adding a required column and backfilling in one blocking step
- Hiding commits inside generic save methods
- Assuming a backup is equivalent to a rehearsed restore
Practical exercise
Write an expand-and-contract migration plan
Choose a real schema change. Define the invariant, compatible intermediate state, batched backfill, verification query, release sequence, rollback decision, and PostgreSQL test. Add one database constraint and one concurrency test that proves the intended behaviour.
Deliverable: A migration runbook, executable verification query, and database-backed regression test.
Sources and further reading
Primary and official references
Topic-specific takeaway
Reliable persistence comes from explicit invariants, database-enforced constraints, compatible migrations, and evidence from the production database engine.
