Database Testing

<objective>
A migration that passes `prisma migrate deploy` can still silently drop a column's data, and an `EXPLAIN` assertion that never fails will green-light a query that lost its index — both ship to production looking fine. This skill produces database tests that catch those: forward AND backward migration tests, constraint-rejection tests, deterministic seed data, drift detection, and query-plan assertions that actually fail when the index disappears.

Before starting: check .agents/qa-project-context.md for database type, ORM, migration tooling, and environment config — they shape every pattern below. </objective>

---

Discovery Questions

Check .agents/qa-project-context.md first — if it exists, use it and skip anything already answered there. Then:

1. Database type: PostgreSQL, MySQL, SQLite, MongoDB, or multi-database? Each has different constraint syntax, migration tools, and performance profiling.
2. ORM / query builder: Prisma, TypeORM, Drizzle, Sequelize, SQLAlchemy, Django ORM, or raw SQL? The ORM determines migration tooling and test patterns.
3. Migration tool: Prisma Migrate, TypeORM migrations, Flyway, Liquibase, Alembic, knex, or custom? This determines how to test forward and backward migrations.
4. Test database strategy: isolated DB per test, transaction rollback, Testcontainers, or shared DB with cleanup? Affects speed and reliability.
5. Existing seed data: factories, fixtures, or seed scripts? Check prisma/seed.ts, seeds/, fixtures/, or factory patterns.
6. Performance baselines: any existing query benchmarks or slow-query monitoring?

---

Core Principles

1. Test migrations forward AND backward. Every migration should be reversible. If a rollback fails, you cannot recover from a bad deploy. Test the down path, not just the up — and test it with the actual revert mechanism (a hand-written down.sql for Prisma, a native revert command elsewhere), not a metadata flag.

2. Constraints are the first line of defense. NOT NULL, UNIQUE, FOREIGN KEY, and CHECK constraints stop bad data at the database, regardless of application code. Test that each one exists and rejects invalid data with the right error.

3. Deterministic seed data. Tests must produce the same result every run. Use factories with fixed IDs and fixed timestamps, not random data. faker.random() without a seed, uuid(), and now() in seed data create non-deterministic tests.

4. Isolate database state per test. Tests that share state are order-dependent and flaky. Use transaction rollback, per-test databases, or guaranteed cleanup.

5. Test the migration, not the ORM's sync. prisma db push / typeorm synchronize: true skip the migration path your users will actually run. Always exercise the real migration files.

6. A performance assertion that can't fail is worthless. Prove the EXPLAIN test goes red when the index is dropped before trusting it green. See Verification.

---

Migration Testing

For runnable migration test code, see references/migration-tests.md.

Forward Migration Validation

Spin up a fresh, empty database, run all migrations with prisma migrate deploy, then assert against information_schema that the expected tables and columns exist with the right types, nullability, and defaults. Prefer a Testcontainers-provided DATABASE_URL; the admin-Pool CREATE DATABASE path is the fallback when you must target a standing Postgres — pick one strategy per suite, don't mix.

Rollback Testing

Prisma has no migrate down / migrate rollback command. prisma migrate resolve --rolled-back is not a rollback tool — it only fixes a migration whose migrate deploy failed, and it throws on a cleanly-applied one. The supported test for a reversible change: apply forward, capture state, run the hand-written down.sql directly (psql -f down.sql), assert the reverted object is gone, then re-apply. Maintain a down.sql per migration directory.

For TypeORM and Sequelize, both ship native revert commands (dataSource.undoLastMigration(), sequelize-cli db:migrate:undo); swap them in for the psql -f down.sql step — the capture → revert → assert → re-apply shape is identical.

For Drizzle Kit v1.0 (still beta as of mid-2026 — latest is drizzle-kit@1.0.0-beta.22, no stable GA yet; 0.44.x is the conservative pin if you need stable): drizzle-kit generate + drizzle-kit migrate. Pin the exact version in CI — the v1 beta line reworked the casing API and removed RQB v1 ._query for Postgres, and the API is still shifting between betas. Drizzle has no down-migration generator; check in your own inverse SQL, same as Prisma.

Data Preservation During Migration

To test that an added column preserves existing rows, apply migrations up to N-1, insert data, then apply the migration under test and assert the rows survived (new nullable column carries its default or null). prisma migrate deploy has no --to flag — it applies all pending migrations. To stop at N-1, deploy a migrations directory containing only migrations up to N-1 (stage it in CI), then deploy the full directory. Tools with real targeting (Flyway -target=, Alembic upgrade <rev>) use the native flag instead.

Migration Drift Detection

The most common real migration bug: someone edits the DB or the schema without a matching migration, so the committed migrations no longer reproduce schema.prisma. prisma migrate diff --from-migrations … --to-schema-datamodel … --exit-code returns non-zero on drift — wire it into CI as a fast pre-flight before the heavier tests. See references/migration-tests.md.

Schema Snapshot Comparison

Capture a pg_dump --schema-only snapshot before and after the migration and diff table-by-table so only the intended tables changed. See references/migration-tests.md.

Other ORMs

TypeORM: DataSource with migrationsRun: false, then dataSource.runMigrations() and dataSource.undoLastMigration() in tests. Same shape: apply all, verify schema, revert last, verify rollback.

Alembic (Python): test alembic upgrade head from empty DB, alembic downgrade base for full rollback, and an upgrade→downgrade→upgrade cycle to verify schema consistency. Use a fresh test database via fixture.

---

Data Integrity Testing

For runnable constraint and referential-integrity code, see references/integrity-and-seed.md.

Constraint Testing

Assert that each constraint rejects invalid data: NOT NULL rejects missing required columns, UNIQUE rejects duplicates, FOREIGN KEY rejects dangling references, CHECK rejects out-of-range values, ON DELETE CASCADE removes dependent rows. Assert on the database error message (/null value in column/, /unique constraint/i, etc.) at the pool.query level — not at the ORM or application-validation layer, which can mask a missing DB constraint.

Referential Integrity & Data-Quality Audit

Run anti-join queries (LEFT JOIN … WHERE parent.id IS NULL) to assert there are no orphan records pointing at deleted parents. Then audit for the gap between intended and enforced integrity: COUNT(*) vs COUNT(DISTINCT col) flags a column that should be unique but lacks a constraint; COUNT(*) FILTER (WHERE col IS NULL) flags one that should be non-null. See references/integrity-and-seed.md.

Data Type Validation

Test: monetary values stored with correct precision (no float loss), VARCHAR length enforcement (value too long on overflow), and timezone-aware timestamps stored as UTC — insert with an offset (+02:00), retrieve, and verify ISO UTC output.

---

Seed Data Management

For runnable factory, seed-script, and isolation code, see references/integrity-and-seed.md.

Factory Pattern (TypeScript)

Build records from a buildUser(overrides) factory that increments a counter for stable, deterministic IDs and emails and uses a fixed timestamp (new Date('2026-01-01T00:00:00Z'), never new Date() with no argument), with a createUser(pool, overrides) helper that inserts and returns the record. See references/integrity-and-seed.md.

Prisma Seed Script

Use upsert with fixed IDs so the seed is idempotent and re-runnable, and switch profiles on process.env.SEED_ENV: test (minimal, 2–3 users), staging (realistic volume, 50+ users), demo (curated). staging and demo extend test. See references/integrity-and-seed.md.

Test Isolation with Transaction Rollback

Wrap each test in BEGIN/ROLLBACK so inserts never persist between tests. The module-level shared client works for serial runs (jest --runInBand); parallel test files in one worker need a per-suite client or savepoints. See references/integrity-and-seed.md.

---

Query Performance Testing

For runnable EXPLAIN ANALYZE and index-validation code, see references/performance-and-docker.md.

EXPLAIN ANALYZE Patterns

Run EXPLAIN (ANALYZE, FORMAT JSON) on critical queries, read plan.Plan['Node Type'], and assert it matches /Index/ (not Seq Scan) and that plan['Execution Time'] is under threshold. See references/performance-and-docker.md.

Index Validation

Query pg_indexes and assert the columns you rely on for lookups and range scans (users.email, orders.user_id, orders.created_at) are actually indexed. See references/performance-and-docker.md.