Test migrations and constraints

Application code and schema migrations ship together but often fail separately. A typo in a migration can make every handler unusable.

Apply all migrations to an empty database, inspect the resulting schema, then run representative reads and writes. Also test upgrading from a supported previous schema when production already has data.

A clean-install test and an upgrade test answer different questions. The clean test proves that a new environment can reach the current schema from nothing. The upgrade test proves that an existing environment can move from a supported old version without losing or corrupting data. Running only the clean path misses changes that fail when a table already contains rows.

For an upgrade test, create the previous schema from a frozen migration snapshot, insert representative old data, then apply only the newer migrations. Read the old row through the current repository and assert both preserved values and intentional defaults. Do not construct the “old” database using today’s schema helpers; that would reproduce the destination rather than the real starting point.

Constraints are executable guarantees. Test a successful write at the boundary and a failing write beyond it: a duplicate ISBN, a missing required column, or a broken foreign key. Assert the database rejects the write and leaves existing data unchanged. An application validation test alone cannot prove the constraint exists, while a constraint test alone cannot prove the API reports a useful error.

Migration tests should fail forward. Avoid quietly editing a migration already applied in production; add a new migration and test the supported upgrade path.

Add a uniqueness rule, test a clean migration, a duplicate write, and an upgrade containing existing rows. After the rejected duplicate, query the table and prove that exactly one original row remains.