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

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

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

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

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

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