数据库迁移与完整性测试：在改 schema…

当需要为数据库迁移、数据完整性、种子数据和查询性能编写可回归的自动化测试时，用本技能确保测试能真正捕获 schema 漂移、约束缺失和索引失效。

适用场景

解决思路

1. 开始前检查 `.agents/qa-project-context.md`；若不存在则确认：数据库类型（PostgreSQL、MySQL、SQLite、MongoDB 或多库）、ORM/查询构建器（Prisma、TypeORM、Drizzle、Sequelize、SQLAlchemy、Django ORM 或 raw SQL）、迁移工具（Prisma Migrate、TypeORM migrations、Flyway、Liquibase、Alembic、knex 或自定义）、测试数据库策略（每测试隔离 DB、事务回滚、Testcontainers 或共享 DB 清理）、现有种子数据（factories、fixtures 或 seed scripts，检查 `prisma/seed.ts`、`seeds/`、`fixtures/` 或 factory 模式）、性能基线（已有查询 benchmark 或慢查询监控）。
2. 测试迁移要同时覆盖 forward 和 backward：每个迁移应可逆；若回滚失败，就无法从坏部署恢复；测试 `down` 路径，而不只是 `up`，并用真实回滚机制（Prisma 用手写 `down.sql`，其他工具有原生 revert 命令）测试，不要用元数据标志。
3. 把约束当作第一道防线：`NOT NULL`、`UNIQUE`、`FOREIGN KEY`、`CHECK` 约束在数据库层阻止坏数据，不依赖应用代码；测试每个约束存在并以正确错误拒绝非法数据。
4. 使用确定性种子数据：测试每次必须产生相同结果；使用固定 ID 和固定时间戳的 factories，不要随机数据；未固定种子的 `faker.random()`、`uuid()`、`now()` 会造成非确定性测试。
5. 按测试隔离数据库状态：共享状态的测试会依赖执行顺序并 flaky；使用事务回滚、每测试数据库或保证清理。
6. 测试迁移本身，而不是 ORM 的 sync：`prisma db push` / `typeorm synchronize: true` 跳过用户实际运行的迁移路径；始终执行真实迁移文件。
7. 性能断言必须能失败：在信任 EXPLAIN 测试变绿前，先证明删除索引后测试会变红；见 Verification。
8. 正向迁移验证：启动全新空数据库，用 `prisma migrate deploy` 运行所有迁移，然后对 `information_schema` 断言期望的表和列存在，且类型、可空性、默认值正确；优先用 Testcontainers 提供的 `DATABASE_URL`，必须面向常驻 Postgres 时用 admin-Pool `CREATE DATABASE` 作为 fallback；每套测试只选一种策略，不要混用。
9. 回滚测试（Prisma）：Prisma 没有 `migrate down` / `migrate rollback`；`prisma migrate resolve --rolled-back` 不是回滚工具，它只修复 `migrate deploy` 失败的迁移，对干净应用的迁移会抛错；可逆变更的受支持测试是：正向应用、捕获状态、直接运行手写 `down.sql`（`psql -f down.sql`）、断言被回滚对象已消失，然后重新应用；每个迁移目录维护一个 `down.sql`。
10. 回滚测试（TypeORM 和 Sequelize）：两者都有原生 revert 命令（`dataSource.undoLastMigration()`、`sequelize-cli db:migrate:undo`）；用它们替换 `psql -f down.sql` 步骤，capture → revert → assert → re-apply 形状相同。
11. 回滚测试（Drizzle Kit v1.0 beta）：使用 `drizzle-kit generate` + `drizzle-kit migrate`；在 CI 中固定精确版本；Drizzle 没有 down-migration 生成器，像 Prisma 一样签入自己的反向 SQL；v1 beta 线改过 `casing` API 并移除了 Postgres 的 RQB v1 `._query`，API 仍在 beta 间变动。
12. 迁移期间数据保留测试：先应用到 N-1，插入数据，再应用被测迁移，断言行存活（新 nullable 列带默认值或 null）；`prisma migrate deploy` 没有 `--to` 标志，它会应用所有 pending 迁移；要停在 N-1，就在 CI 中暂存只含前 N-1 个迁移的 migrations 目录，然后部署完整目录；有真实目标能力的工具用原生标志（Flyway `-target=`、Alembic `upgrade <rev>`）。
13. 迁移漂移检测：常见真实 bug 是有人改 DB 或 schema 却没有对应迁移，导致已提交迁移无法复现 `schema.prisma`；用 `prisma migrate diff --from-migrations … --to-schema-datamodel … --exit-code`，漂移时返回非零；把它接入 CI 作为重型测试前的快速 pre-flight。
14. Schema 快照比较：迁移前后捕获 `pg_dump --schema-only` 快照，逐表 diff，确保只有预期表发生变化。
15. 其他 ORM（TypeORM）：用 `DataSource` 且 `migrationsRun: false`，然后在测试中 `dataSource.runMigrations()` 和 `dataSource.undoLastMigration()`；同样形状：应用全部、验证 schema、回滚最后一个、验证回滚。
16. 其他 ORM（Alembic）：从空 DB 测试 `alembic upgrade head`，用 `alembic downgrade base` 做完整回滚，并做 upgrade→downgrade→upgrade 循环验证 schema 一致性；用 fixture 提供全新测试数据库。
17. 约束测试：断言每个约束拒绝非法数据：`NOT NULL` 拒绝缺失必填列，`UNIQUE` 拒绝重复，`FOREIGN KEY` 拒绝悬空引用，`CHECK` 拒绝越界值，`ON DELETE CASCADE` 删除依赖行；在 `pool.query` 层断言数据库错误消息（如 `/null value in column/`、`/unique constraint/i`），不要在 ORM 或应用验证层断言，那会掩盖缺失的数据库约束。
18. 引用完整性和数据质量审计：运行 anti-join 查询（`LEFT JOIN … WHERE parent.id IS NULL`）断言没有指向已删除父记录的孤儿记录；然后审计‘意图’与‘强制’完整性之间的差距：`COUNT(*)` vs `COUNT(DISTINCT col)` 标记本应唯一但缺少约束的列；`COUNT(*) FILTER (WHERE col IS NULL)` 标记本应非空但缺少约束的列。
19. 数据类型验证：测试货币值以正确精度存储（无浮点损失）、VARCHAR 长度强制（溢出时报 `value too long`）、带时区时间戳以 UTC 存储；插入带偏移（`+02:00`）的值，取回后验证 ISO UTC 输出。
20. Factory 模式（TypeScript）：用 `buildUser(overrides)` factory 构建记录，递增计数器生成稳定、确定性 ID 和 email，并用固定时间戳（`new Date('2026-01-01T00:00:00Z')`，绝不用无参 `new Date()`）；用 `createUser(pool, overrides)` helper 插入并返回记录。
21. Prisma Seed Script：用带固定 ID 的 `upsert`，让 seed 幂等、可重复运行；用 `process.env.SEED_ENV` 切换 profile：`test`（最小，2–3 用户）、`staging`（真实量级，50+ 用户）、`demo`（精选）；`staging` 和 `demo` 扩展 `test`。
22. 事务回滚测试隔离：把每个测试包在 `BEGIN`/`ROLLBACK` 中，使插入不会在测试间持久；模块级共享 client 适用于串行运行（`jest --runInBand`）；同一 worker 中的并行测试文件需要每套 client 或 savepoints。
23. EXPLAIN ANALYZE 模式：对关键查询运行 `EXPLAIN (ANALYZE, FORMAT JSON)`，读取 `plan.Plan['Node Type']`，断言匹配 `/Index/`（不是 `Seq Scan`），且 `plan['Execution Time']` 低于阈值。
24. 索引验证：查询 `pg_indexes`，断言用于查找和范围扫描的列（`users.email`、`orders.user_id`、`orders.created_at`）确实已建索引。
25. 慢查询检测：先播种真实量级（10K+ 行），再用 `performance.now()` 测量执行时间，断言关键查询（带 JOIN、GROUP BY、ORDER BY 的 dashboard 聚合）在阈值内完成（例如 100ms）。
26. MongoDB：用 `collection.find(...).explain('executionStats')` 验证索引使用（`stage` 不能是 `COLLSCAN`），检查 `totalDocsExamined` 接近 `nReturned`，并通过 `collection.indexes()` 验证复合索引存在。
27. Docker 测试数据库首选 Testcontainers：`@testcontainers/postgresql` 11.14+ 是低摩擦默认选择，提供程序化容器生命周期、自动清理、用不同端口并行执行；它去掉 docker-compose 文件和端口冲突记账；`PostgreSqlContainer` 设置见 `references/performance-and-docker.md`。
28. 手写 compose 仍有效：`docker-compose.test.yml` 使用 `postgres:18-alpine`、`tmpfs` 做 RAM-backed 存储、`pg_isready` healthcheck；映射到非默认端口（如 5433）避免与本地 Postgres 冲突；主版本匹配生产（Postgres 18 当前为 18.4，2026 年 5 月；除非生产固定，否则从 17 升级）。
29. 在 `package.json` 中串联脚本：`test:db:up`（compose up）、`test:db:migrate`（prisma migrate deploy）、`test:db:seed`（prisma db seed）、`test:db`（全部 + jest）、`test:db:down`（compose down -v）。
30. 避免反模式 1：不要测生产数据库副本；生产数据含 PII、非确定性且不可预测变化；改用 factories 和 seed scripts 配合成数据。
31. 避免反模式 2：不要在测试间共享数据库状态；Test A 插入 user，Test B 假设其存在，CI 重排后 Test B 失败；用事务回滚或每测试清理。
32. 避免反模式 3：不要忽略回滚测试；‘我们从不回滚迁移’在第一次迁移搞坏生产前都成立；测试 `down` 路径；如果工具没有 revert，这是要记录的风险，不是跳过的理由。
33. 避免反模式 4：不要用 `migrate resolve --rolled-back` 伪造回滚；该命令只修复失败的迁移，对干净迁移会抛错，不还原 schema；用真实机制回滚：Prisma 用 `down.sql`，TypeORM 用 `undoLastMigration()`。
34. 避免反模式 5：不要用 ORM sync 代替迁移；`prisma db push`、`typeorm synchronize: true`、Django `migrate --run-syncdb` 跳过真实迁移路径；测试必须使用与生产相同的机制。
35. 避免反模式 6：不要只测 happy-path 查询；有数据时返回行是简单情况；要测空结果集、可选列中的 null、最大结果大小、针对错误数据的查询。
36. 避免反模式 7：不要写永远不会失败的性能断言；EXPLAIN 测试无论索引是否存在都通过会给出虚假信心；证明删除索引后变红（见 Verification）。
37. 避免反模式 8：不要用随机数据播种；未固定种子的 `faker.random()`、`uuid()`、`now()` 每次运行产生不同数据，导致测试非确定性；用固定种子和固定值：`faker.seed(42)`、显式 ID、`new Date('2026-01-01T00:00:00Z')`。
38. 验证 1：套件从干净环境变绿：`npm run test:db` 对全新 Testcontainers 数据库退出 0。
39. 验证 2：EXPLAIN 测试有牙齿：在 scratch DB 中 `DROP INDEX users_email_idx`，重跑 query-performance 测试，确认它失败（planner 回退到 `Seq Scan`），然后恢复索引并确认再次通过；索引没了还保持绿色的性能测试是坏的，信任前先修。
40. 验证 3：漂移检查会触发：`prisma migrate diff --from-migrations prisma/migrations --to-schema-datamodel prisma/schema.prisma --exit-code` 在干净 repo 返回 0；手动编辑 `schema.prisma` 并确认返回非零。
41. 完成标准：最新迁移有 forward+rollback 测试文件：forward 从空 DB 应用并通过 `information_schema` 断言 schema；rollback 应用 `down.sql`，断言被回滚对象不存在，然后重新应用——并在 CI 通过。
42. 完成标准：约束测试对 NOT NULL、UNIQUE、FOREIGN KEY、CHECK 各断言一次拒绝（带数据库错误消息），在 `pool.query` 层。
43. 完成标准：数据保留测试在被测迁移前插入行，并断言它们存活（不使用假的 `--to` 标志）。
44. 完成标准：迁移漂移检查（`prisma migrate diff … --exit-code`）在 CI 运行，干净 repo 退出 0。
45. 完成标准：种子数据幂等（`upsert` + 固定 ID）且用 `SEED_ENV` 切换 profile；重新运行两次产生相同状态。
46. 完成标准：EXPLAIN 测试断言 `Node Type` 匹配 `/Index/` 且 `Execution Time` 低于阈值，并已证明删除索引时会失败。
47. 完成标准：`test:db` CI job 对 Testcontainers 数据库退出 0（绿色）。
48. 需要可运行代码时参考：`references/migration-tests.md`（forward 验证、`down.sql` 回滚、数据保留、漂移检测 `migrate diff`、schema 快照比较）、`references/integrity-and-seed.md`（约束和引用完整性测试、数据质量审计、factory 模式、`SEED_ENV` seed script、事务回滚隔离 helper）、`references/performance-and-docker.md`（EXPLAIN ANALYZE 计划断言、索引验证、dropped-index teeth test、Testcontainers 设置）。

适用边界 · 注意事项

• 不要用于大规模合成数据生成/掩码——用 test-data-management。
• 不要用于 Docker/IaC 测试环境供给——用 test-environments。
• 不要用于 SQL 注入等对抗性输入——用 security-testing。