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

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

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

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