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.

Slow Query Detection

Seed realistic volume (10K+ rows), then measure execution time with performance.now() and assert critical queries (dashboard aggregations with JOINs, GROUP BY, ORDER BY) complete under a threshold (e.g. 100ms).

MongoDB: use collection.find(...).explain('executionStats') to verify index usage (stage must not be COLLSCAN), check totalDocsExamined is close to nReturned, and verify compound indexes exist via collection.indexes().

---

Docker-Based Test Database

Preferred (2026): Testcontainers. @testcontainers/postgresql 11.14+ (May 2026) is the lower-friction default — programmatic container lifecycle, auto-cleanup, parallel execution with distinct ports. It removes the docker-compose file and port-conflict bookkeeping. See references/performance-and-docker.md for the PostgreSqlContainer setup.

Hand-rolled compose (still valid): docker-compose.test.yml with postgres:18-alpine, tmpfs for RAM-backed storage, and a pg_isready healthcheck. Map to a non-default port (e.g. 5433) to avoid conflicts with local Postgres. Match the major version to production — Postgres 18 is current (18.4, May 2026); bump from 17 unless production is pinned.

Chain scripts in package.json: test:db:up (compose up), test:db:migrate (prisma migrate deploy), test:db:seed (prisma db seed), test:db (all + jest), test:db:down (compose down -v).

---

Anti-Patterns

1. Testing against production database copies

Production data contains PII, is non-deterministic, and changes unpredictably. Use factories and seed scripts with synthetic data.

2. Shared database state between tests

Test A inserts a user; Test B assumes it exists; CI reorders them; Test B fails. Use transaction rollback or per-test cleanup.

3. Ignoring rollback testing

"We never roll back migrations" holds until the first migration breaks production. Test the down path. If the tool has no revert, that is a risk to document, not to skip.

4. Faking rollback with migrate resolve --rolled-back

That command only repairs a failed migration and throws on a clean one. It does not revert schema. Revert with the real mechanism: down.sql for Prisma, undoLastMigration() for TypeORM.

5. Using ORM sync instead of migrations

prisma db push, typeorm synchronize: true, Django migrate --run-syncdb skip the real migration path. Tests must use the same mechanism as production.

6. Testing only happy-path queries

A query that returns rows when data exists is the easy case. Test empty result sets, nulls in optional columns, max result sizes, and queries against the wrong data.

7. Performance assertions that can never fail

An EXPLAIN test that passes whether or not the index exists gives false confidence. Prove it goes red on a dropped index (see Verification).

8. Seeding with random data

faker.random() without a fixed seed, uuid(), and now() produce different data every run, making tests non-deterministic. Use fixed seeds and fixed values: faker.seed(42), explicit IDs, new Date('2026-01-01T00:00:00Z').

---

Verification

Prove the suite actually catches regressions, smallest check first:

1. Suite is green from clean: npm run test:db exits 0 against a fresh Testcontainers database.
2. The EXPLAIN test has teeth: in a scratch DB, DROP INDEX users_email_idx, re-run the query-performance test, confirm it fails (planner falls back to Seq Scan), then restore the index and confirm it passes again. A perf test that stays green with the index gone is broken — fix it before trusting it. See references/performance-and-docker.md.
3. Drift check fires: prisma migrate diff --from-migrations prisma/migrations --to-schema-datamodel prisma/schema.prisma --exit-code returns 0 on a clean repo; hand-edit schema.prisma and confirm it returns non-zero.

---

Done When

• A forward+rollback test file exists for the latest migration: forward applies from an empty DB and asserts schema via information_schema; rollback applies down.sql, asserts the reverted object absent, then re-applies — and it passes in CI.
• A constraints test asserts a rejection (with the DB error message) for each of NOT NULL, UNIQUE, FOREIGN KEY, and CHECK, at the pool.query level.
• A data-preservation test inserts rows before the migration under test and asserts they survive it (no fake --to flag).
• A migration-drift check (prisma migrate diff … --exit-code) runs in CI and exits 0 on a clean repo.
• Seed data is idempotent (upsert + fixed IDs) and switches profiles on SEED_ENV; re-running it twice produces identical state.
• An EXPLAIN test asserts Node Type matches /Index/ and Execution Time is under threshold, and has been shown to fail when the index is dropped.
• The test:db CI job exits 0 (green) against a Testcontainers database.

Reference Files (in references/)

• migration-tests.md — Forward validation, rollback via down.sql, data preservation, drift detection (migrate diff), and schema snapshot comparison.
• integrity-and-seed.md — Constraint and referential-integrity tests, data-quality audits, factory pattern, SEED_ENV seed script, and transaction-rollback isolation helpers.
• performance-and-docker.md — EXPLAIN ANALYZE plan assertions, index validation, the dropped-index teeth test, and Testcontainers setup.

Related Skills

• test-data-management — Synthetic data generation and masking at scale for non-production environments. Come here for in-test factories and seed scripts; go there for large realistic datasets and PII masking.
• test-environments — Docker/IaC provisioning of test databases, environment parity. This skill uses Testcontainers inside a test suite; test-environments owns the standing infrastructure.
• security-testing — SQL injection, database-level access control, and encryption verification. This skill tests integrity and correctness, not adversarial input.
• ci-cd-integration — Running migration and DB test jobs in CI pipelines and provisioning test databases in GitHub Actions.
• performance-testing — Load testing DB performance, connection-pool sizing, and query optimization under concurrent load (out of scope here).