Testing with Databases

Database Testing Strategies

Testing database interactions requires balancing test isolation, speed, and realism. You need each test to start with a known state and leave no side effects for subsequent tests. The right approach depends on your stack, test type, and performance requirements.

Test Database Setup

// jest.setup.ts - Configure test database
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  datasources: {
    db: { url: process.env.TEST_DATABASE_URL },
  },
});

beforeAll(async () => {
  // Run migrations on test database
  // execSync('npx prisma migrate deploy');
  await prisma.$connect();
});

afterAll(async () => {
  await prisma.$disconnect();
});

// Clean tables between tests
afterEach(async () => {
  // Delete in reverse order of foreign key dependencies
  await prisma.orderItem.deleteMany();
  await prisma.order.deleteMany();
  await prisma.product.deleteMany();
  await prisma.user.deleteMany();
});

export { prisma };

// .env.test
// TEST_DATABASE_URL="postgresql://localhost:5432/myapp_test"

// package.json scripts
// "test": "dotenv -e .env.test -- jest",
// "test:db:setup": "dotenv -e .env.test -- prisma migrate deploy"

Factories and Fixtures

// factories/userFactory.ts - Reusable test data builders
import { prisma } from '../jest.setup';

let counter = 0;

interface UserOverrides {
  name?: string;
  email?: string;
  role?: 'USER' | 'ADMIN';
}

export async function createUser(overrides: UserOverrides = {}) {
  counter++;
  return prisma.user.create({
    data: {
      name: overrides.name ?? `Test User \${counter}`,
      email: overrides.email ?? `user\${counter}@test.com`,
      role: overrides.role ?? 'USER',
    },
  });
}

export async function createProduct(overrides: Partial<Product> = {}) {
  counter++;
  return prisma.product.create({
    data: {
      name: overrides.name ?? `Product \${counter}`,
      price: overrides.price ?? 19.99,
      stock: overrides.stock ?? 100,
    },
  });
}

export async function createOrderWithItems(userId: string) {
  const product1 = await createProduct({ price: 10 });
  const product2 = await createProduct({ price: 20 });

  return prisma.order.create({
    data: {
      userId,
      items: {
        create: [
          { productId: product1.id, quantity: 2, price: 10 },
          { productId: product2.id, quantity: 1, price: 20 },
        ],
      },
    },
    include: { items: true },
  });
}

// Usage in tests
test('calculates order total', async () => {
  const user = await createUser();
  const order = await createOrderWithItems(user.id);

  const total = await orderService.getTotal(order.id);
  expect(total).toBe(40); // (2 * 10) + (1 * 20)
});

Transaction Rollback Pattern

Wrap each test in a transaction that rolls back, giving instant cleanup with zero leftover data.

// helpers/transactional-test.ts
import { prisma } from '../jest.setup';

export function transactionalTest(
  name: string,
  fn: (tx: PrismaClient) => Promise<void>
) {
  test(name, async () => {
    await prisma.$transaction(async (tx) => {
      await fn(tx as any);
      // Force rollback by throwing after assertions pass
      throw new RollbackError();
    }).catch((e) => {
      if (!(e instanceof RollbackError)) throw e;
    });
  });
}

class RollbackError extends Error {
  constructor() { super('Intentional rollback'); }
}

// Usage
transactionalTest('creates user in DB', async (tx) => {
  const user = await tx.user.create({
    data: { name: 'Alice', email: 'alice@test.com' },
  });

  expect(user.id).toBeDefined();
  const found = await tx.user.findUnique({ where: { id: user.id } });
  expect(found?.name).toBe('Alice');
  // Automatically rolled back - no cleanup needed!
});

Testcontainers for Realistic Databases

// Use real databases in Docker containers for tests
import { PostgreSqlContainer } from '@testcontainers/postgresql';
import { PrismaClient } from '@prisma/client';
import { execSync } from 'child_process';

let container;
let prisma: PrismaClient;

beforeAll(async () => {
  // Start a real PostgreSQL container
  container = await new PostgreSqlContainer('postgres:16')
    .withDatabase('testdb')
    .withUsername('test')
    .withPassword('test')
    .start();

  const dbUrl = container.getConnectionUri();

  // Run migrations
  execSync(`DATABASE_URL=\${dbUrl} npx prisma migrate deploy`);

  prisma = new PrismaClient({
    datasources: { db: { url: dbUrl } },
  });
  await prisma.$connect();
}, 60000); // Container startup can take time

afterAll(async () => {
  await prisma.$disconnect();
  await container.stop();
});

test('full database integration test', async () => {
  // This runs against a real PostgreSQL instance
  const user = await prisma.user.create({
    data: { name: 'Alice', email: 'alice@test.com' },
  });

  const found = await prisma.user.findUnique({
    where: { email: 'alice@test.com' },
  });

  expect(found?.name).toBe('Alice');
});