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 concurrent updates and verify conflict handling.

• Test database query performance:

• Run EXPLAIN ANALYZE on critical queries and assert expected index usage.

• Benchmark query execution time with representative data volumes.

• Flag queries doing sequential scans on large tables.

• Validate migration safety:

• Test each migration can run on a populated database without data loss.

• Verify backward compatibility (old code works with new schema during rollout).

• Check migration execution time is acceptable for production deployment.

Output

• Database test files organized by entity in tests/database/ or tests/models/

• Fixture and factory utility files in tests/helpers/ or tests/factories/

• Migration test scripts validating up/down migrations

• Query performance benchmarks with EXPLAIN ANALYZE output

• Test database Docker Compose configuration

Error Handling

Error Cause Solution

Foreign key constraint violation during cleanup Truncation order does not respect foreign key dependencies Truncate tables in reverse dependency order; or disable FK checks during cleanup (SET CONSTRAINTS ALL DEFERRED )

Connection pool exhausted Too many test workers opening separate connections Use a single shared connection for tests; limit pool size; close connections in afterAll

Migration fails on test database Schema drift between development and test databases Drop and recreate test database; run all migrations from scratch; verify migration checksums

Transaction rollback does not clean up ORM auto-commits or test creates a new connection outside the transaction Inject the transaction connection into all ORM operations; disable auto-commit in test config

Slow test suite due to database I/O Too many INSERT/DELETE operations per test Use in-memory SQLite for unit tests; batch seed data; use transaction rollback instead of truncation

Examples

Jest with Prisma transaction rollback:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

describe('UserRepository', () => { afterAll(async () => { await prisma.$disconnect(); });

it('creates and retrieves a user', async () => { await prisma.$transaction(async (tx) => { const created = await tx.user.create({ data: { name: 'Alice', email: 'alice@test.com' }, }); const found = await tx.user.findUnique({ where: { id: created.id } }); expect(found).toMatchObject({ name: 'Alice', email: 'alice@test.com' }); // Transaction rolls back automatically when we throw throw new Error('ROLLBACK'); }).catch((e) => { if (e.message !== 'ROLLBACK') throw e; }); }); });

pytest with database fixture and rollback:

import pytest from sqlalchemy import create_engine from sqlalchemy.orm import Session

@pytest.fixture def db_session(): engine = create_engine("postgresql://test:test@localhost:5433/testdb") # 5433 = configured value connection = engine.connect() transaction = connection.begin() session = Session(bind=connection) yield session session.close() transaction.rollback() connection.close()

def test_insert_and_query_user(db_session): db_session.execute( text("INSERT INTO users (name, email) VALUES (:n, :e)"), {"n": "Alice", "e": "alice@test.com"} ) result = db_session.execute(text("SELECT name FROM users WHERE email = :e"), {"e": "alice@test.com"}).fetchone() assert result[0] == "Alice"

Migration validation test:

describe('Database Migrations', () => { it('applies all migrations to empty database', async () => { const result = await exec('npx prisma migrate deploy'); expect(result.exitCode).toBe(0); });

it('migration is idempotent', async () => { await exec('npx prisma migrate deploy'); const result = await exec('npx prisma migrate deploy'); expect(result.exitCode).toBe(0); // Second run should succeed (no-op) }); });

Resources

• Prisma testing guide: https://www.prisma.io/docs/guides/testing

• SQLAlchemy testing patterns: https://docs.sqlalchemy.org/en/20/orm/session_transaction.html

• Testcontainers databases: https://testcontainers.com/modules/databases/

• Knex migrations: https://knexjs.org/guide/migrations.html

• PostgreSQL EXPLAIN ANALYZE: https://www.postgresql.org/docs/current/using-explain.html