database-testing
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.
petrkindlmann · 已有 AI 审核记录
安装与使用
运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。
npx skills add petrkindlmann/qa-skills --skill database-testing技能说明
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
Before starting: check .agents/qa-project-context.md for database type, ORM, migration tooling, and environment config — they shape every pattern below.
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
downpath, not just theup— and test it with the actual revert mechanism (a hand-writtendown.sqlfor Prisma, a native revert command elsewhere), not a metadata flag. -
Constraints are the first line of defense.
NOT NULL,UNIQUE,FOREIGN KEY, andCHECKconstraints 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(), andnow()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: trueskip 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 migration