Stop Mocking Your Database: Integration Tests in the Testcontainers Era

Every time we argued about mocks vs Testcontainers, production picked the winner. “Unit tests must be fast, so we mock the database.” I accepted this rule unquestioningly for years. Until mocks started lying to me - tests passed, but production failed on edge cases that the mocked in-memory database never revealed.

Testcontainers changed my thinking. You spin up a real PostgreSQL in a Docker container, tests run against an actual database, and yet they’re fast enough for CI.

Tested on: Node.js 20+, Jest/Vitest, PostgreSQL 15-16, Testcontainers Node.js 10.x. Same principles apply to Java, Python, Go.

Why Mocks Lie

Problem 1: Behavior Differs

// Mock
jest.mock('../db', () => ({
  query: jest.fn().mockResolvedValue({ rows: [{ id: 1, name: 'Test' }] })
}));

test('finds user', async () => {
  const user = await findUser(1);
  expect(user.name).toBe('Test');  // Passes!
});

In production:

-- Real query
SELECT * FROM users WHERE id = $1 AND deleted_at IS NULL;
-- Mock didn't know about deleted_at column

Problem 2: Transactions and Isolation

// Mock doesn't support transactions
test('atomic transfer', async () => {
  await transfer(accountA, accountB, 100);
  // Mock doesn't verify both operations are in one transaction
  // In production, partial failure can occur
});

Problem 3: Constraint Violations

// Mock doesn't see FK constraints
test('creates order with invalid user', async () => {
  const order = await createOrder({ userId: 999 });  // User doesn't exist
  expect(order).toBeDefined();  // Mock: OK!
  // PostgreSQL: ERROR: violates foreign key constraint
});

Problem 4: Query Syntax and Typos

// Mock doesn't validate SQL
const query = "SELECT * FORM users";  // Typo: FORM instead of FROM
// Mock passes, PostgreSQL throws syntax error

Testcontainers: Real DB in Tests

Basic Setup

// src/tests/setup.ts
import { PostgreSqlContainer, StartedPostgreSqlContainer } from '@testcontainers/postgresql';
import { Pool } from 'pg';

let container: StartedPostgreSqlContainer;
let pool: Pool;

export async function setupDatabase(): Promise<Pool> {
  container = await new PostgreSqlContainer('postgres:16-alpine')
    .withDatabase('testdb')
    .withUsername('test')
    .withPassword('test')
    .start();

  pool = new Pool({
    connectionString: container.getConnectionUri()
  });

  // Run migrations
  await runMigrations(pool);

  return pool;
}

export async function teardownDatabase(): Promise<void> {
  await pool.end();
  await container.stop();
}

export function getPool(): Pool {
  return pool;
}

Jest Configuration

// jest.setup.ts
import { setupDatabase, teardownDatabase, getPool } from './setup';

beforeAll(async () => {
  await setupDatabase();
}, 60000);  // Container startup can take time

afterAll(async () => {
  await teardownDatabase();
});

// Reset data between tests
afterEach(async () => {
  const pool = getPool();
  await pool.query('TRUNCATE users, orders, products RESTART IDENTITY CASCADE');
});

Vitest Configuration

// vitest.config.ts
import { defineConfig } from 'vitest/config';

export default defineConfig({
  test: {
    globalSetup: './src/tests/global-setup.ts',
    setupFiles: ['./src/tests/setup.ts'],
    testTimeout: 30000,
    hookTimeout: 60000,
    pool: 'forks',  // Isolation between test files
  }
});

// src/tests/global-setup.ts
import { PostgreSqlContainer } from '@testcontainers/postgresql';

export default async function globalSetup() {
  const container = await new PostgreSqlContainer('postgres:16-alpine').start();

  process.env.DATABASE_URL = container.getConnectionUri();
  process.env.TESTCONTAINER_ID = container.getId();

  return async () => {
    await container.stop();
  };
}

Data Isolation Strategies

Strategy 1: Truncate After Each Test

afterEach(async () => {
  await pool.query(`
    TRUNCATE users, orders, products
    RESTART IDENTITY CASCADE
  `);
});

Pros: Clean state, simple Cons: Slower with many tables

Strategy 2: Transaction Rollback

let client: PoolClient;

beforeEach(async () => {
  client = await pool.connect();
  await client.query('BEGIN');
});

afterEach(async () => {
  await client.query('ROLLBACK');
  client.release();
});

// Tests use client instead of pool
test('creates user', async () => {
  await client.query('INSERT INTO users (name) VALUES ($1)', ['Test']);
  // After test, it rolls back
});

Pros: Fastest, no deletion Cons: Not suitable for tests with external calls

Strategy 3: Savepoints for Nested Transactions

class TestContext {
  private savepointCounter = 0;

  async withSavepoint<T>(fn: () => Promise<T>): Promise<T> {
    const savepoint = `sp_${++this.savepointCounter}`;
    await this.client.query(`SAVEPOINT ${savepoint}`);

    try {
      return await fn();
    } finally {
      await this.client.query(`ROLLBACK TO SAVEPOINT ${savepoint}`);
    }
  }
}

Strategy 4: Database per Test (slow but isolated)

import { v4 as uuid } from 'uuid';

async function createTestDatabase(): Promise<string> {
  const dbName = `test_${uuid().replace(/-/g, '')}`;
  await adminPool.query(`CREATE DATABASE ${dbName} TEMPLATE testdb_template`);
  return dbName;
}

afterEach(async () => {
  await adminPool.query(`DROP DATABASE ${testDbName}`);
});

CI/CD Pipeline Setup

GitHub Actions

# .github/workflows/test.yml
name: Tests

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest

    services:
      # Not needed - Testcontainers start their own container

    steps:
      - uses: actions/checkout@v4

      - name: Setup Node.js
        uses: actions/setup-node@v4
        with:
          node-version: 20
          cache: 'npm'

      - name: Install dependencies
        run: npm ci

      - name: Run tests
        run: npm test
        env:
          TESTCONTAINERS_RYUK_DISABLED: true  # GitHub Actions optimization

      - name: Upload coverage
        uses: codecov/codecov-action@v3

CI Optimizations

// Reuse containers between test files
const container = await new PostgreSqlContainer()
  .withReuse()  // Reuse if exists
  .start();

// Smaller image
const container = await new PostgreSqlContainer('postgres:16-alpine')
  .start();

// Parallel tests with isolated schemas
const schema = `test_${process.env.JEST_WORKER_ID}`;
await pool.query(`CREATE SCHEMA IF NOT EXISTS ${schema}`);
await pool.query(`SET search_path TO ${schema}`);

Test Examples

Test with Real Constraints

describe('Order creation', () => {
  test('fails with invalid user', async () => {
    await expect(
      createOrder({ userId: 9999, items: [] })
    ).rejects.toThrow(/foreign key constraint/);
  });

  test('fails with duplicate order number', async () => {
    await createOrder({ orderNumber: 'ORD-001', userId: 1 });

    await expect(
      createOrder({ orderNumber: 'ORD-001', userId: 1 })
    ).rejects.toThrow(/unique constraint/);
  });
});

Test with Transaction

describe('Money transfer', () => {
  test('is atomic', async () => {
    await createAccount({ id: 1, balance: 100 });
    await createAccount({ id: 2, balance: 50 });

    // Simulate failure during transfer
    jest.spyOn(db, 'query').mockImplementationOnce((query) => {
      if (query.includes('UPDATE accounts SET balance')) {
        throw new Error('Network error');
      }
      return originalQuery(query);
    });

    await expect(transfer(1, 2, 30)).rejects.toThrow();

    // Both accounts must have original values
    const acc1 = await getAccount(1);
    const acc2 = await getAccount(2);
    expect(acc1.balance).toBe(100);  // Unchanged
    expect(acc2.balance).toBe(50);   // Unchanged
  });
});

Test with JSON/JSONB

describe('JSONB operations', () => {
  test('queries nested JSON correctly', async () => {
    await pool.query(`
      INSERT INTO products (name, metadata)
      VALUES ('Laptop', '{"specs": {"ram": 16, "storage": "512GB"}}')
    `);

    const result = await pool.query(`
      SELECT * FROM products
      WHERE metadata->'specs'->>'ram' = '16'
    `);

    expect(result.rows).toHaveLength(1);
    expect(result.rows[0].metadata.specs.storage).toBe('512GB');
  });
});

Bug That Mock Wouldn’t Catch

Case Study: Locale-Sensitive Sorting

// Mock
test('sorts users by name', async () => {
  mockDb.query.mockResolvedValue({
    rows: [{ name: 'Ábel' }, { name: 'Adam' }, { name: 'Boris' }]
  });

  const users = await getUsers({ orderBy: 'name' });
  // Test passes, mock returns exactly what we told it
});

// Real DB
test('sorts users by name - Testcontainers', async () => {
  await pool.query(`
    INSERT INTO users (name) VALUES ('Boris'), ('Adam'), ('Ábel')
  `);

  const users = await getUsers({ orderBy: 'name' });

  // PostgreSQL with C locale: Ábel is at the end (ASCII sorting)
  // PostgreSQL with Slovak locale: Ábel is at the beginning
  // Mock would never reveal this!
});

Case Study: Deadlock Detection

test('handles deadlock gracefully', async () => {
  // Simulate concurrent transactions
  const tx1 = pool.connect();
  const tx2 = pool.connect();

  await Promise.all([
    (async () => {
      await tx1.query('BEGIN');
      await tx1.query('UPDATE accounts SET balance = 100 WHERE id = 1');
      await delay(100);
      await tx1.query('UPDATE accounts SET balance = 200 WHERE id = 2');
    })(),
    (async () => {
      await tx2.query('BEGIN');
      await tx2.query('UPDATE accounts SET balance = 300 WHERE id = 2');
      await delay(100);
      await tx2.query('UPDATE accounts SET balance = 400 WHERE id = 1');
    })()
  ]).catch(e => {
    expect(e.message).toContain('deadlock');
    // Mock would never simulate deadlock
  });
});

Performance Comparison

When to Use Mocks

Mocks still have their place:

1. Unit tests for business logic - no DB calls
2. External APIs - third parties, payment gateways
3. Non-deterministic operations - time, random
4. Failure testing - network errors, timeouts

// Mock for external API - correct usage
jest.mock('../payment-gateway', () => ({
  charge: jest.fn().mockResolvedValue({ transactionId: 'tx_123' })
}));

// Database - Testcontainers
test('creates order with payment', async () => {
  const order = await createOrder({ ... });

  expect(paymentGateway.charge).toHaveBeenCalled();
  // Verify order is correctly saved in real DB
  const saved = await pool.query('SELECT * FROM orders WHERE id = $1', [order.id]);
  expect(saved.rows[0].status).toBe('paid');
});

Conclusion

Testcontainers aren’t a replacement for unit tests - they’re a complement. Use:

• Unit tests + mocks for isolated business logic
• Integration tests + Testcontainers for database operations
• E2E tests for complete flows

The investment in Testcontainers setup pays off with the first bug that a mock wouldn’t catch.

FAQ

Are Testcontainers slow for CI?

With good caching and reuse strategy, they add ~30-60s to total CI time. For most projects, this is acceptable for increased confidence.

Can I run tests in parallel?

Yes. Use isolated schemas or transaction rollback. Testcontainers support multiple parallel connections.

What if I don’t have Docker in CI?

GitHub Actions, GitLab CI, CircleCI - all have Docker. For Azure DevOps, you need Linux agents or Windows containers.

Related Articles

• CI/CD for Monorepo - Optimizing test pipeline
• Zero-Downtime PostgreSQL Migrations - Testing migrations