database-testing

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

<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>

<objective> 即使通过`prisma migrate deploy`的迁移仍可能悄无声息地丢弃列数据，而永远不会失败的`EXPLAIN`断言会放行丢失索引的查询——这两种情况在上线时看起来都正常。本技能提供的数据库测试可以捕获这些问题：正向和反向迁移测试、约束拒绝测试、确定性种子数据、漂移检测，以及当索引消失时会实际失败的查询计划断言。

开始之前： 查看

.agents/qa-project-context.md

获取数据库类型、ORM、迁移工具和环境配置信息——这些会影响以下所有测试模式。 </objective>

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?

首先查看

.agents/qa-project-context.md

——如果存在，使用其中的信息并跳过已回答的问题。然后：

数据库类型： PostgreSQL、MySQL、SQLite、MongoDB，还是多数据库？每种数据库的约束语法、迁移工具和性能分析方式都不同。
ORM/查询构建器： Prisma、TypeORM、Drizzle、Sequelize、SQLAlchemy、Django ORM，还是原生SQL？ORM决定了迁移工具和测试模式。
迁移工具： Prisma Migrate、TypeORM migrations、Flyway、Liquibase、Alembic、knex，还是自定义工具？这决定了如何测试正向和反向迁移。
测试数据库策略： 每个测试使用独立数据库、事务回滚、Testcontainers，还是带清理机制的共享数据库？这会影响测试速度和可靠性。
现有种子数据： 使用工厂模式、固定数据，还是种子脚本？检查
```
prisma/seed.ts
```
、
```
seeds/
```
、
```
fixtures/
```
或工厂模式实现。
性能基准： 是否有现有的查询基准或慢查询监控？

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
```
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.
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.
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.
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: true
```
skip 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.

测试迁移的正向和反向执行。 每个迁移都应该是可逆的。如果回滚失败，你将无法从错误的部署中恢复。测试
```
down
```
路径，而不仅仅是
```
up
```
路径——并且要使用实际的回滚机制（Prisma使用手写的
```
down.sql
```
，其他工具使用原生回滚命令），而不是元数据标记。
约束是第一道防线。
```
NOT NULL
```
、
```
UNIQUE
```
、
```
FOREIGN KEY
```
和
```
CHECK
```
约束可以在数据库层面阻止不良数据，无论应用代码如何。测试每个约束都存在，并且能以正确的错误信息拒绝无效数据。
确定性种子数据。 测试必须每次运行都产生相同的结果。使用带有固定ID和固定时间戳的工厂模式，而非随机数据。种子数据中使用无种子的
```
faker.random()
```
、
```
uuid()
```
和
```
now()
```
会导致测试结果不确定。
每个测试隔离数据库状态。 共享状态的测试会依赖执行顺序且不稳定。使用事务回滚、每个测试独立数据库，或可靠的清理机制。
测试迁移，而非ORM的同步功能。
```
prisma db push
```
/
```
typeorm synchronize: true
```
会跳过用户实际运行的迁移路径。始终使用真实的迁移文件进行测试。
无法失败的性能断言毫无价值。 在信任EXPLAIN测试的通过结果之前，要证明当索引被删除时它会失败。详情请见验证部分。

Migration Testing

迁移测试

For runnable migration test code, see

references/migration-tests.md

可运行的迁移测试代码，请查看

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.

启动一个全新的空数据库，使用

prisma migrate deploy

运行所有迁移，然后通过

information_schema

断言预期的表和列存在，且类型、可空性和默认值正确。优先使用Testcontainers提供的

DATABASE_URL

；当必须指向现有Postgres时，使用管理员连接池的

CREATE DATABASE

作为备选——每个测试套件选择一种策略，不要混合使用。

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.

Prisma没有

migrate down

migrate rollback

命令。

prisma migrate resolve --rolled-back

不是回滚工具——它仅用于修复执行

migrate deploy

失败的迁移，对已成功应用的迁移执行该命令会报错。验证可逆变更的支持测试方法：应用正向迁移，捕获状态，直接运行手写的

down.sql

（

psql -f down.sql

），断言被回滚的对象已消失，然后重新应用迁移。为每个迁移目录维护一个

down.sql

文件。

对于TypeORM和Sequelize，两者都提供原生回滚命令（

dataSource.undoLastMigration()

、

sequelize-cli db:migrate:undo

）；将这些命令替换

psql -f down.sql

步骤——捕获→回滚→断言→重新应用的流程保持一致。

对于Drizzle Kit v1.0（截至2026年中期仍处于测试版——最新版本为
drizzle-kit@1.0.0-beta.22
，尚未发布稳定版；若需要稳定版可选择
0.44.x
）：使用

drizzle-kit generate

drizzle-kit migrate

。在CI中固定精确版本——v1测试版重写了

casing

API，并移除了Postgres的RQB v1

._query

，且API在测试版之间仍有变动。Drizzle没有向下迁移生成器，需自行提交反向SQL，与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.

为测试新增列是否保留现有行，先应用到第N-1个迁移，插入数据，然后应用待测试的迁移，断言行数据仍存在（新的可空列携带默认值或null）。prisma migrate deploy
没有
--to
参数——它会应用所有待处理的迁移。要停在第N-1个迁移，需部署仅包含前N-1个迁移的目录（在CI中暂存），然后部署完整目录。支持精准定位的工具（Flyway

-target=

、Alembic

upgrade <rev>

）使用原生参数即可。

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

最常见的实际迁移bug：有人修改了数据库或schema但未生成对应的迁移，导致已提交的迁移无法重现

schema.prisma

。

prisma migrate diff --from-migrations … --to-schema-datamodel … --exit-code

在检测到漂移时返回非零值——将其接入CI作为快速预检查，在更耗时的测试之前运行。详情请见

references/migration-tests.md

。

Schema Snapshot Comparison

Schema快照对比

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

在迁移前后分别捕获

pg_dump --schema-only

快照，逐表对比，确保只有预期的表发生了变更。详情请见

references/migration-tests.md

。

Other ORMs

其他ORM

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.

TypeORM： 使用

migrationsRun: false

的

DataSource

，然后在测试中调用

dataSource.runMigrations()

和

dataSource.undoLastMigration()

。流程相同：应用所有迁移，验证schema，回滚最后一个迁移，验证回滚结果。

Alembic（Python）： 测试从空数据库执行

alembic upgrade head

，执行

alembic downgrade base

进行完整回滚，并通过升级→降级→升级的循环验证schema一致性。通过fixture使用全新的测试数据库。

Data Integrity Testing

数据完整性测试

For runnable constraint and referential-integrity code, see

references/integrity-and-seed.md

可运行的约束和参照完整性代码，请查看

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.

断言每个约束都能拒绝无效数据：

NOT NULL

拒绝缺失必填列，

UNIQUE

拒绝重复值，

FOREIGN KEY

拒绝悬空引用，

CHECK

拒绝超出范围的值，

ON DELETE CASCADE

删除依赖行。在

pool.query

层面断言数据库错误信息（如

/null value in column/

、

/unique constraint/i

等）——不要在ORM或应用验证层断言，因为这可能掩盖数据库约束缺失的问题。

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(*)

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

运行反连接查询（

LEFT JOIN … WHERE parent.id IS NULL

）断言不存在指向已删除父记录的孤儿记录。然后审计预期完整性与实际强制完整性之间的差距：

COUNT(*)

与

COUNT(DISTINCT col)

的对比可标记应唯一但缺少约束的列；

COUNT(*) FILTER (WHERE col IS NULL)

可标记应非空但允许null的列。详情请见

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.

测试：货币值以正确精度存储（无浮点损失），VARCHAR长度限制（超出时提示

value too long

），时区感知时间戳以UTC存储——插入带偏移量的时间（如

+02:00

），检索后验证输出为ISO UTC格式。

Seed Data Management

种子数据管理

For runnable factory, seed-script, and isolation code, see

references/integrity-and-seed.md

可运行的工厂模式、种子脚本和隔离代码，请查看

references/integrity-and-seed.md

。

Factory Pattern (TypeScript)

工厂模式（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

通过

buildUser(overrides)

工厂创建记录，该工厂使用计数器生成稳定、确定的ID和邮箱，并使用固定时间戳（

new Date('2026-01-01T00:00:00Z')

，切勿使用无参数的

new Date()

），同时提供

createUser(pool, overrides)

辅助函数用于插入并返回记录。详情请见

references/integrity-and-seed.md

。

Prisma Seed Script

Prisma种子脚本

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

使用带固定ID的

upsert

确保种子数据具有幂等性且可重复运行，并根据

process.env.SEED_ENV

切换配置：

test

（极简，2-3个用户）、

staging

（真实量级，50+用户）、

demo

（定制化）。

staging

和

demo

继承

test

的配置。详情请见

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

将每个测试包裹在

BEGIN

ROLLBACK

中，确保插入的数据不会在测试之间持久化。模块级共享客户端适用于串行运行（

jest --runInBand

）；同一工作进程中的并行测试文件需要每个套件独立的客户端或保存点。详情请见

references/integrity-and-seed.md

。

Query Performance Testing

查询性能测试

For runnable EXPLAIN ANALYZE and index-validation code, see

references/performance-and-docker.md

可运行的EXPLAIN ANALYZE和索引验证代码，请查看

references/performance-and-docker.md

。

EXPLAIN ANALYZE Patterns

EXPLAIN ANALYZE模式

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

对关键查询运行

EXPLAIN (ANALYZE, FORMAT JSON)

，读取

plan.Plan['Node Type']

，断言其匹配

/Index/

（而非

Seq Scan

），且

plan['Execution Time']

低于阈值。详情请见

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

查询

pg_indexes

，断言用于查找和范围扫描的列（如

users.email

、

orders.user_id

、

orders.created_at

）确实已建立索引。详情请见

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()

生成真实量级的种子数据（10000+行），然后使用

performance.now()

测量执行时间，断言关键查询（带JOIN、GROUP BY、ORDER BY的仪表板聚合查询）在阈值内完成（如100ms）。

MongoDB： 使用

collection.find(...).explain('executionStats')

验证索引使用情况（

stage

不能为

COLLSCAN

），检查

totalDocsExamined

接近

nReturned

，并通过

collection.indexes()

验证复合索引存在。

Docker-Based Test Database

基于Docker的测试数据库

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).

首选方案（2026年）：Testcontainers。

@testcontainers/postgresql

11.14+（2026年5月）是低摩擦的默认选择——程序化容器生命周期、自动清理、不同端口并行执行。它无需docker-compose文件和端口冲突管理。

PostgreSqlContainer

的设置请见

references/performance-and-docker.md

。

手动编写compose（仍有效）： 使用

docker-compose.test.yml

配置

postgres:18-alpine

，采用

tmpfs

作为内存存储，并配置

pg_isready

健康检查。映射到非默认端口（如5433）以避免与本地Postgres冲突。主版本需与生产环境匹配——Postgres 18为当前版本（18.4，2026年5月）；除非生产环境固定为17，否则升级到18。

在

package.json

中链式调用脚本：

test:db:up

（启动compose）、

test:db:migrate

（执行prisma migrate deploy）、

test:db:seed

（执行prisma db seed）、

test:db

（以上所有步骤+jest测试）、

test:db:down

（停止并删除compose容器）。

Anti-Patterns

反模式

1. Testing against production database copies

1. 针对生产数据库副本进行测试

Production data contains PII, is non-deterministic, and changes unpredictably. Use factories and seed scripts with synthetic data.

生产数据包含PII，结果不确定且会不可预测地变化。使用工厂模式和种子脚本生成合成数据。

2. Shared database state between tests

2. 测试之间共享数据库状态

Test A inserts a user; Test B assumes it exists; CI reorders them; Test B fails. Use transaction rollback or per-test cleanup.

测试A插入用户；测试B假设该用户存在；CI重新排序测试；测试B失败。使用事务回滚或每个测试独立清理。

3. Ignoring rollback testing

3. 忽略回滚测试

"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.

“我们从不回滚迁移”的想法会持续到第一次迁移破坏生产环境。测试

down

路径。如果工具没有回滚功能，这是一个需要记录的风险，而非可以跳过的测试。

4. Faking rollback with

migrate resolve --rolled-back

4. 使用

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.

该命令仅用于修复失败的迁移，对已成功应用的迁移执行会报错。它不会回滚schema。使用真实机制回滚：Prisma使用

down.sql

，TypeORM使用

undoLastMigration()

。

5. Using ORM sync instead of migrations

5. 使用ORM同步而非迁移

prisma db push

typeorm synchronize: true

, Django

migrate --run-syncdb

skip the real migration path. Tests must use the same mechanism as production.

prisma db push

、

typeorm synchronize: true

、Django

migrate --run-syncdb

会跳过真实的迁移路径。测试必须使用与生产环境相同的机制。

6. Testing only happy-path queries

6. 仅测试查询的正常路径

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.

数据存在时返回行的查询是简单情况。测试空结果集、可选列中的null值、最大结果大小，以及针对错误数据的查询。

7. Performance assertions that can never fail

7. 永远不会失败的性能断言

An EXPLAIN test that passes whether or not the index exists gives false confidence. Prove it goes red on a dropped index (see Verification).

无论索引是否存在都通过的EXPLAIN测试会给出虚假的信心。要证明当索引被删除时它会失败（请见验证部分）。

8. Seeding with random data

8. 使用随机数据生成种子

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')

无固定种子的

faker.random()

、

uuid()

和

now()

每次运行都会生成不同的数据，导致测试结果不确定。使用固定种子和固定值：

faker.seed(42)

、明确的ID、

new Date('2026-01-01T00:00:00Z')

。

Verification

验证

Prove the suite actually catches regressions, smallest check first:

Suite is green from clean:
```
npm run test:db
```
exits 0 against a fresh Testcontainers database.
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
```
.

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.

证明测试套件确实能捕获回归问题，按最小检查项顺序：

全新环境下测试套件通过：
```
npm run test:db
```
在全新的Testcontainers数据库上执行后返回0。
EXPLAIN测试有效： 在临时数据库中执行
```
DROP INDEX users_email_idx
```
，重新运行查询性能测试，确认测试失败（查询规划器回退到
```
Seq Scan
```
），然后恢复索引并确认测试再次通过。如果索引删除后性能测试仍通过，则测试存在问题——在信任它之前修复。详情请见
```
references/performance-and-docker.md
```
。

漂移检测触发：

prisma migrate diff --from-migrations prisma/migrations --to-schema-datamodel prisma/schema.prisma --exit-code

在干净仓库中返回0；手动编辑

schema.prisma

并确认返回非零值。

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.

为最新迁移编写正向+回滚测试文件：从空数据库应用正向迁移并通过
```
information_schema
```
断言schema；应用
```
down.sql
```
进行回滚，断言被回滚的对象已不存在，然后重新应用迁移——且该测试在CI中通过。
编写约束测试，在
```
pool.query
```
层面对NOT NULL、UNIQUE、FOREIGN KEY和CHECK约束分别断言拒绝行为（带数据库错误信息）。
编写数据保留测试，在待测试迁移前插入行并断言它们在迁移后仍存在（不使用虚假的
```
--to
```
参数）。
在CI中运行迁移漂移检查（
```
prisma migrate diff … --exit-code
```
），干净仓库中返回0。
种子数据具有幂等性（
```
upsert
```
+固定ID），并根据
```
SEED_ENV
```
切换配置；重复运行两次会生成完全相同的状态。
编写EXPLAIN测试，断言
```
Node Type
```
匹配
```
/Index/
```
且
```
Execution Time
```
低于阈值，并已验证索引删除时测试会失败。
```
test:db
```
CI任务在Testcontainers数据库上执行后返回0（通过）。

Reference Files (in

references/

)

参考文件（位于

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.

migration-tests.md — 正向验证、通过
```
down.sql
```
回滚、数据保留、漂移检测（
```
migrate diff
```
）、schema快照对比。
integrity-and-seed.md — 约束和参照完整性测试、数据质量审计、工厂模式、
```
SEED_ENV
```
种子脚本、事务回滚隔离辅助工具。
performance-and-docker.md — EXPLAIN ANALYZE计划断言、索引验证、索引删除失效测试、Testcontainers设置。