Validate database integrity, test migrations forward and backward, verify schema constraints, manage seed data, detect migration drift, and identify query performance issues. Covers PostgreSQL, MySQL, MongoDB with Prisma, TypeORM, Drizzle, and SQLAlchemy, plus Testcontainers test databases. Use when

<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

database-testing