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:

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

Core Principles

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.
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.
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.
Isolate database state per test. Tests that share state are order-dependent and flaky. Use transaction rollback, per-test databases, or guaranteed cleanup.
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.
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(*)

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:

Suite is green from clean:
```
npm run test:db
```
exits 0 against a fresh Testcontainers database.
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
```
.

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).

database-testing

NPX Install

Tags

SKILL.md Content

Discovery Questions

Core Principles

Migration Testing

Forward Migration Validation

Rollback Testing

Data Preservation During Migration

Migration Drift Detection

Schema Snapshot Comparison

Other ORMs

Data Integrity Testing

Constraint Testing

Referential Integrity & Data-Quality Audit

Data Type Validation

Seed Data Management

Factory Pattern (TypeScript)

Prisma Seed Script

Test Isolation with Transaction Rollback

Query Performance Testing

EXPLAIN ANALYZE Patterns

Index Validation

Slow Query Detection

Docker-Based Test Database

Anti-Patterns

1. Testing against production database copies

2. Shared database state between tests

3. Ignoring rollback testing

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

5. Using ORM sync instead of migrations

6. Testing only happy-path queries

7. Performance assertions that can never fail

8. Seeding with random data

Verification

Done When

Reference Files (in
`references/`
)

Related Skills

database-testing

NPX Install

Tags

SKILL.md Content

Discovery Questions

Core Principles

Migration Testing

Forward Migration Validation

Rollback Testing

Data Preservation During Migration

Migration Drift Detection

Schema Snapshot Comparison

Other ORMs

Data Integrity Testing

Constraint Testing

Referential Integrity & Data-Quality Audit

Data Type Validation

Seed Data Management

Factory Pattern (TypeScript)

Prisma Seed Script

Test Isolation with Transaction Rollback

Query Performance Testing

EXPLAIN ANALYZE Patterns

Index Validation

Slow Query Detection

Docker-Based Test Database

Anti-Patterns

1. Testing against production database copies

2. Shared database state between tests

3. Ignoring rollback testing

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

5. Using ORM sync instead of migrations

6. Testing only happy-path queries

7. Performance assertions that can never fail

8. Seeding with random data

Verification

Done When

Reference Files (in references/)

Related Skills

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

Reference Files (in
`references/`
)