database-testing

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.