Database Test Manager
Overview
Manage database testing including fixture loading, transaction-based test isolation, migration validation, query performance testing, and data integrity checks. Supports PostgreSQL, MySQL, MongoDB, SQLite (in-memory), and Redis with ORM-agnostic patterns for Prisma, TypeORM, SQLAlchemy, Knex, and Drizzle.
Prerequisites
- Database instance available for testing (Docker container, in-memory SQLite, or dedicated test server)
- Database client library and ORM installed (Prisma, TypeORM, Knex, SQLAlchemy, etc.)
- Migration files up to date and tested independently
- Test database connection string configured in environment (distinct from development/production)
- Database seed data scripts for baseline test state
Instructions
- Set up the test database infrastructure:
- Use Docker to spin up a dedicated test database:
docker run -d -p 5433:5432 --name test-db postgres:16-alpine.
- Or use SQLite in-memory mode for fast unit tests:
sqlite::memory:.
- Or use Testcontainers for ephemeral database per test suite.
- Verify the test database is isolated from development data.
- Run database migrations against the test database:
- Execute
npx prisma migrate deploy or npx knex migrate:latest --env test.
- Verify all migrations apply cleanly to an empty database.
- Test rollback: run
migrate:rollback and verify schema reverts correctly.
- Implement test isolation strategy (choose one):
- Transaction rollback: Wrap each test in a transaction; roll back after assertions. Fastest option.
- Truncation: Truncate all tables in
beforeEach. Simpler but slower.
- Database recreation: Drop and recreate the database before each test suite. Slowest, most thorough.
- Create database fixture utilities:
- Factory functions that insert records and return the created entity with its database-generated ID.
- Seed functions for standard test scenarios (empty state, populated state, edge cases).
- Cleanup utilities that handle foreign key ordering for truncation.
- Write database-specific test cases:
- CRUD operations: Insert, query, update, delete records and verify database state.
- Constraint validation: Attempt invalid inserts (null on NOT NULL, duplicate on UNIQUE) and verify rejection.
- Referential integrity: Verify cascading deletes, foreign key enforcement, and orphan prevention.
- Index performance: Verify queries use expected indexes with EXPLAIN ANALYZE.
- Transaction isolation: Test co
