references/integrity-and-seed.md
# Data Integrity & Seed Data Code
Runnable code for constraint tests, referential integrity, seed factories, and transaction-rollback isolation. The decision prose lives in `SKILL.md`.
## Constraint Testing
```typescript
describe('Database constraints', () => {
// NOT NULL
it('should reject user without email', async () => {
await expect(
pool.query(`INSERT INTO users (id, name) VALUES ($1, $2)`,
['550e8400-e29b-41d4-a716-446655440001', 'Bob'])
).rejects.toThrow(/null value in column "email"/);
});
// UNIQUE
it('should reject duplicate email', async () => {
await pool.query(`INSERT INTO users (id, email) VALUES ($1, $2)`,
['550e8400-e29b-41d4-a716-446655440001', 'alice@example.com']);
await expect(
pool.query(`INSERT INTO users (id, email) VALUES ($1, $2)`,
['550e8400-e29b-41d4-a716-446655440002', 'alice@example.com'])
).rejects.toThrow(/unique constraint/i);
});
// FOREIGN KEY
it('should reject order with nonexistent user', async () => {
await expect(
pool.query(`INSERT INTO orders (id, user_id, total) VALUES ($1, $2, $3)`,
['ord-1', 'nonexistent-user-id', 100])
).rejects.toThrow(/foreign key constraint/i);
});
// CHECK constraint
it('should reject negative order total', async () => {
await expect(
pool.query(`INSERT INTO orders (id, user_id, total) VALUES ($1, $2, $3)`,
['ord-1', existingUserId, -50])
).rejects.toThrow(/check constraint/i);
});
// CASCADE behavior
it('should cascade delete orders when user is deleted', async () => {
await pool.query(`INSERT INTO users (id, email) VALUES ($1, $2)`,
['user-cascade', 'cascade@example.com']);
await pool.query(`INSERT INTO orders (id, user_id, total) VALUES ($1, $2, $3)`,
['ord-cascade', 'user-cascade', 100]);
await pool.query('DELETE FROM users WHERE id = $1', ['user-cascade']);
const orders = await pool.query('SELECT * FROM orders WHERE user_id = $1', ['user-cascade']);
expect(orders.rows).toHaveLength(0);
});
});
```
## Referential Integrity
```typescript
it('should not create orphan records', async () => {
// Check for orders referencing nonexistent users
const orphans = await pool.query(`
SELECT o.id FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL
`);
expect(orphans.rows).toHaveLength(0);
});
it('should not create orphan line items', async () => {
const orphans = await pool.query(`
SELECT li.id FROM line_items li
LEFT JOIN orders o ON li.order_id = o.id
WHERE o.id IS NULL
`);
expect(orphans.rows).toHaveLength(0);
});
```
## Data-Quality Audit (intended vs enforced integrity)
Constraint tests prove the constraints that *exist* work. These queries catch columns that *should* be unique or non-null but have no DB constraint — the gap between intended and enforced integrity. Run them against realistic data; a non-zero count means an application invariant is unenforced at the database level.
```typescript
it('email is effectively unique (no constraint? still must hold)', async () => {
const { rows } = await pool.query(
`SELECT COUNT(*) AS total, COUNT(DISTINCT email) AS distinct_emails FROM users`,
);
expect(Number(rows[0].total)).toBe(Number(rows[0].distinct_emails));
});
it('orders.user_id is never null in practice', async () => {
const { rows } = await pool.query(
`SELECT COUNT(*) FILTER (WHERE user_id IS NULL) AS nulls FROM orders`,
);
expect(Number(rows[0].nulls)).toBe(0);
});
```
## Factory Pattern (TypeScript)
```typescript
// test/factories/user.factory.ts
let userCounter = 0;
export function buildUser(overrides: Partial<User> = {}): User {
userCounter++;
return {
id: overrides.id ?? `user-${userCounter.toString().padStart(4, '0')}`,
email: overrides.email ?? `user${userCounter}@example.com`,
name: overrides.name ?? `Test User ${userCounter}`,
role: overrides.role ?? 'user',
createdAt: overrides.createdAt ?? new Date('2026-01-01T00:00:00Z'),
};
}
export async function createUser(pool: Pool, overrides: Partial<User> = {}) {
const user = buildUser(overrides);
await pool.query(
`INSERT INTO users (id, email, name, role, created_at) VALUES ($1, $2, $3, $4, $5)`,
[user.id, user.email, user.name, user.role, user.createdAt]
);
return user;
}
// Usage: const admin = await createUser(pool, { role: 'admin' });
```
## Prisma Seed Script
```typescript
// prisma/seed.ts -- use upsert for idempotency, fixed IDs for stability.
// SEED_ENV selects the profile: test (minimal) | staging (volume) | demo (curated).
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
async function seedBase() {
await prisma.user.upsert({
where: { email: 'admin@example.com' },
update: {},
create: { id: 'seed-admin-001', email: 'admin@example.com', name: 'Admin User', role: 'ADMIN' },
});
await prisma.user.upsert({
where: { email: 'testuser@example.com' },
update: {},
create: { id: 'seed-user-001', email: 'testuser@example.com', name: 'Test User', role: 'USER' },
});
for (const p of [
{ id: 'seed-prod-001', name: 'Widget', price: 29.99, stock: 100 },
{ id: 'seed-prod-002', name: 'Gadget', price: 49.99, stock: 50 },
{ id: 'seed-prod-003', name: 'Doohickey', price: 9.99, stock: 0 },
]) {
await prisma.product.upsert({ where: { id: p.id }, update: {}, create: p });
}
}
async function seedStagingVolume() {
// staging extends test with realistic volume; fixed IDs keep it idempotent
for (let i = 0; i < 50; i++) {
const id = `seed-user-${String(i).padStart(3, '0')}`;
await prisma.user.upsert({
where: { id }, update: {},
create: { id, email: `user${i}@example.com`, name: `User ${i}`, role: 'USER' },
});
}
}
async function seed() {
const env = process.env.SEED_ENV ?? 'test';
await seedBase();
if (env === 'staging' || env === 'demo') await seedStagingVolume();
}
seed().catch((e) => { console.error(e); process.exit(1); }).finally(() => prisma.$disconnect());
```
## Test Isolation with Transaction Rollback
> **Parallel caveat:** the module-level `client` below is shared across the file, which is fine for *serial* runs (`jest --runInBand` or a single suite). Under parallel test files in the same worker, the shared client collides — give each suite its own client, or use savepoints (`SAVEPOINT` / `ROLLBACK TO`) for nested isolation.
```typescript
// test/helpers/db.ts
import { Pool, PoolClient } from 'pg';
let pool: Pool;
let client: PoolClient;
export async function setupTestTransaction() {
pool = new Pool({ database: 'test_db' });
client = await pool.connect();
await client.query('BEGIN');
return client;
}
export async function rollbackTestTransaction() {
await client.query('ROLLBACK');
client.release();
}
// In tests:
describe('OrderService', () => {
let db: PoolClient;
beforeEach(async () => { db = await setupTestTransaction(); });
afterEach(async () => { await rollbackTestTransaction(); });
it('should create order and decrement stock', async () => {
// This runs inside a transaction that rolls back after the test
await db.query(`INSERT INTO products (id, name, stock) VALUES ($1, $2, $3)`,
['prod-1', 'Widget', 10]);
const service = new OrderService(db);
await service.createOrder({ productId: 'prod-1', quantity: 2 });
const result = await db.query('SELECT stock FROM products WHERE id = $1', ['prod-1']);
expect(result.rows[0].stock).toBe(8);
// Transaction rolls back -- no persistent state
});
});
```
references/migration-tests.md
# Migration Testing Code
Runnable migration test code for forward migrations, rollback, data preservation, drift detection, and schema snapshot comparison. The decision prose and ORM notes live in `SKILL.md`.
## Forward Migration Validation
```typescript
// test/migrations/forward.test.ts
import { execSync } from 'child_process';
import { Pool } from 'pg';
// Prefer a Testcontainers-provided DATABASE_URL (see performance-and-docker.md).
// The admin-Pool / CREATE DATABASE path below works against a standing Postgres
// when you cannot use Testcontainers; pick ONE strategy per suite, do not mix.
describe('Forward migrations', () => {
let pool: Pool;
beforeAll(async () => {
// Create a fresh database for migration testing
const adminPool = new Pool({ database: 'postgres' });
await adminPool.query('DROP DATABASE IF EXISTS test_migrations');
await adminPool.query('CREATE DATABASE test_migrations');
await adminPool.end();
pool = new Pool({ database: 'test_migrations' });
});
afterAll(async () => {
await pool.end();
});
it('should apply all migrations from empty database', () => {
// Run all migrations against empty database
execSync('npx prisma migrate deploy', {
env: { ...process.env, DATABASE_URL: 'postgresql://localhost/test_migrations' },
});
});
it('should have correct schema after all migrations', async () => {
// Verify expected tables exist
const tables = await pool.query(`
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' ORDER BY table_name
`);
const tableNames = tables.rows.map((r) => r.table_name);
expect(tableNames).toContain('users');
expect(tableNames).toContain('orders');
expect(tableNames).toContain('products');
});
it('should have correct columns on users table', async () => {
const columns = await pool.query(`
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users' ORDER BY ordinal_position
`);
const colMap = Object.fromEntries(
columns.rows.map((r) => [r.column_name, r])
);
expect(colMap.id.data_type).toBe('uuid');
expect(colMap.email.is_nullable).toBe('NO');
expect(colMap.created_at.column_default).toContain('now()');
});
});
```
## Rollback Testing
Prisma has **no** `migrate down` / `migrate rollback`. `prisma migrate resolve --rolled-back` only fixes a migration whose `migrate deploy` *failed* — it errors on a cleanly-applied migration, so it is the wrong tool for testing a reversible change. The supported test is: apply forward, capture state, run the hand-written `down.sql` directly with `psql`, assert the reverted object is gone, then re-apply.
```typescript
import { execSync } from 'node:child_process';
describe('Migration rollback', () => {
it('the down migration cleanly reverses the latest up', async () => {
// Apply all migrations to a fresh DB
execSync('npx prisma migrate deploy', { env: migrationEnv });
// Capture pre-rollback state: the column the latest migration added exists
const before = await pool.query(`
SELECT column_name FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'display_name'
`);
expect(before.rows).toHaveLength(1);
// Get the latest applied migration name (Prisma stores in _prisma_migrations)
const { rows } = await pool.query(
`SELECT migration_name FROM _prisma_migrations
WHERE finished_at IS NOT NULL
ORDER BY finished_at DESC LIMIT 1`,
);
const latest = rows[0].migration_name;
// Revert by applying the hand-written down.sql checked in alongside the
// migration. Do NOT use `migrate resolve --rolled-back` here — that command
// is only valid against a FAILED migration and throws on a clean one.
execSync(`psql $DATABASE_URL -f prisma/migrations/${latest}/down.sql`, { env: migrationEnv });
// Verify rollback succeeded: the column added by the up migration is gone
const after = await pool.query(`
SELECT column_name FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'display_name'
`);
expect(after.rows).toHaveLength(0);
});
it('can re-apply after rollback (idempotent up)', async () => {
// Mark the reverted migration as un-applied so deploy will re-run it, then re-apply
execSync('npx prisma migrate reset --force --skip-seed', { env: migrationEnv });
execSync('npx prisma migrate deploy', { env: migrationEnv });
const after = await pool.query(`
SELECT column_name FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'display_name'
`);
expect(after.rows).toHaveLength(1);
});
});
```
> **TypeORM / Sequelize** ship a native revert. Replace the `psql -f down.sql` line with `dataSource.undoLastMigration()` (TypeORM) or `npx sequelize-cli db:migrate:undo` (Sequelize); the capture → revert → assert → re-apply shape is identical.
## Data Preservation During Migration
`prisma migrate deploy` has **no `--to` target** — it applies *all* pending migrations. To stop at N-1, point Prisma at a migrations directory that contains only migrations up to N-1 (here, a `migrations-upto-n1` fixture dir), insert data, then deploy the full directory to apply the migration under test.
```typescript
it('should preserve existing data when adding a column', async () => {
// Setup: apply migrations up to N-1 by deploying a directory holding only those.
// (Stage the dir in CI, or copy real migrations and drop the latest one.)
execSync('npx prisma migrate deploy --schema=prisma/schema-upto-n1.prisma', { env: migrationEnv });
// Insert data before the migration under test
await pool.query(`INSERT INTO users (id, email) VALUES ($1, $2)`,
['550e8400-e29b-41d4-a716-446655440000', 'alice@example.com']);
// Apply the migration under test (adds nullable 'display_name' column) — full dir
execSync('npx prisma migrate deploy', { env: migrationEnv });
// Verify existing data survived
const result = await pool.query('SELECT email, display_name FROM users WHERE id = $1',
['550e8400-e29b-41d4-a716-446655440000']);
expect(result.rows[0].email).toBe('alice@example.com'); // data survived
// New nullable column carries its default (or null when no default)
expect(result.rows[0].display_name).toBeNull();
});
```
For tools with real targeting (Flyway `migrate -target=`, Alembic `upgrade <rev>`), use the native flag instead of staging a directory.
## Migration Drift Detection (shadow-DB check)
The most common real migration bug: someone edits the schema or the DB without a matching migration, so the committed migrations no longer reproduce `schema.prisma`. `prisma migrate diff` with `--exit-code` returns non-zero on any drift — wire it into CI as a fast pre-flight check before the heavier tests.
```typescript
it('committed migrations reproduce schema.prisma exactly', () => {
// Non-zero exit (and a thrown error) means the migrations and the schema disagree.
execSync(
'npx prisma migrate diff ' +
'--from-migrations prisma/migrations ' +
'--to-schema-datamodel prisma/schema.prisma ' +
'--shadow-database-url "$SHADOW_DATABASE_URL" ' +
'--exit-code',
{ env: migrationEnv },
);
});
```
## Schema Snapshot Comparison
```typescript
// Compare schema before and after migration to detect unintended changes
import { execSync } from 'child_process';
function getSchemaSnapshot(dbUrl: string): string {
return execSync(`pg_dump --schema-only --no-owner --no-privileges ${dbUrl}`, {
encoding: 'utf-8',
});
}
it('should only change the expected tables', () => {
const before = getSchemaSnapshot(testDbUrl);
execSync('npx prisma migrate deploy', { env: migrationEnv });
const after = getSchemaSnapshot(testDbUrl);
// Parse both schemas and compare table-by-table
// Only 'orders' table should have changed
const changedTables = diffSchemas(before, after);
expect(changedTables).toEqual(['orders']);
});
```
references/performance-and-docker.md
# Query Performance & Docker Test Database Code
Runnable code for EXPLAIN ANALYZE checks, index validation, and Testcontainers/Docker-based test databases. The decision prose and ORM/MongoDB notes live in `SKILL.md`.
## EXPLAIN ANALYZE Patterns
```typescript
describe('Query performance', () => {
it('should use index for user lookup by email', async () => {
const explain = await pool.query(
'EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM users WHERE email = $1',
['alice@example.com']
);
const plan = explain.rows[0]['QUERY PLAN'][0];
// Verify index scan, not sequential scan
expect(plan.Plan['Node Type']).toMatch(/Index/);
// Execution time under threshold
expect(plan['Execution Time']).toBeLessThan(10); // ms
});
it('should use index for order date range queries', async () => {
const explain = await pool.query(
`EXPLAIN (ANALYZE, FORMAT JSON)
SELECT * FROM orders WHERE created_at BETWEEN $1 AND $2`,
['2026-01-01', '2026-01-31']
);
const plan = explain.rows[0]['QUERY PLAN'][0];
expect(plan.Plan['Node Type']).not.toBe('Seq Scan');
});
});
```
## Index Validation
```typescript
it('should have indexes on frequently queried columns', async () => {
const indexes = await pool.query(`
SELECT indexname, tablename, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname
`);
const indexMap = new Map<string, string[]>();
for (const row of indexes.rows) {
const key = row.tablename;
if (!indexMap.has(key)) indexMap.set(key, []);
indexMap.get(key)!.push(row.indexdef);
}
// Verify critical indexes exist
const userIndexes = indexMap.get('users')?.join(' ') ?? '';
expect(userIndexes).toContain('email');
const orderIndexes = indexMap.get('orders')?.join(' ') ?? '';
expect(orderIndexes).toContain('user_id');
expect(orderIndexes).toContain('created_at');
});
```
## Testcontainers Test Database
```typescript
import { PostgreSqlContainer } from '@testcontainers/postgresql';
let pg: Awaited<ReturnType<PostgreSqlContainer['start']>>;
beforeAll(async () => {
pg = await new PostgreSqlContainer('postgres:18-alpine')
.withDatabase('test')
.withTmpFs({ '/var/lib/postgresql/data': 'rw' })
.start();
process.env.DATABASE_URL = pg.getConnectionUri();
});
afterAll(async () => {
await pg.stop();
});
```
## Proving the EXPLAIN test has teeth
A performance assertion that can never fail gives false confidence — it is the #1 false-positive trap in DB testing. Before trusting the EXPLAIN test, confirm it *fails* when the index is gone. Drop the index, re-run, expect red; restore it, expect green.
```typescript
it('the index assertion actually catches a missing index', async () => {
await pool.query('DROP INDEX IF EXISTS users_email_idx');
const explain = await pool.query(
'EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM users WHERE email = $1',
['alice@example.com'],
);
const plan = explain.rows[0]['QUERY PLAN'][0];
// With the index dropped the planner must fall back to Seq Scan
expect(plan.Plan['Node Type']).toBe('Seq Scan');
// restore for the rest of the suite
await pool.query('CREATE INDEX users_email_idx ON users (email)');
});
```
SKILL.md
---
name: database-testing
description: >-
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: "database test," "migration test," "migration rollback," "rollback test,"
"data integrity," "SQL test," "schema validation," "seed data," "query performance,"
"Testcontainers."
Not for: synthetic data generation/masking at scale — use test-data-management;
Docker/IaC test-environment provisioning — use test-environments; SQL injection — use security-testing.
Related: test-data-management, test-environments, security-testing, ci-cd-integration.
license: MIT
metadata:
author: kindlmann
version: "2.0"
category: specialized
---
<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).