跳至正文
OpenAgentSkill
浏览已开放 · 目录记录日期 2026-11-21 · 自动更新和账户服务暂时关闭。
数据与分析

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:

  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 migration

阅读完整来源 ↗