testcontainers

Not a mock — real PostgreSQL spawned inside the test. 99% of production behaviour.

1. Why not SQLite mocks?

• SQLite ≠ PostgreSQL — JSONB, TIMESTAMPTZ, arrays, FK CASCADE differ
• Real migrations — CREATE TABLE actually runs
• Isolation — per-test containers or transaction rollback

Cost: ~30s first pull, 1–3s per test. Integration-tier only.

2. Node + vitest

pnpm add -D testcontainers pg

import { PostgreSqlContainer } from "@testcontainers/postgresql";
import { Pool } from "pg";

let container: StartedPostgreSqlContainer;
let pool: Pool;

beforeAll(async () => {
  container = await new PostgreSqlContainer("postgres:15-alpine")
    .withDatabase("test").withUsername("test").withPassword("test").start();
  pool = new Pool({
    host: container.getHost(), port: container.getPort(),
    database: "test", user: "test", password: "test",
  });
  await pool.query(await fs.readFile("sql/001_create.sql", "utf-8"));
}, 60_000);

afterAll(async () => { await pool.end(); await container.stop(); });

test("insert + select", async () => {
  await pool.query("INSERT INTO users (email) VALUES ($1)", ["a@b.c"]);
  const r = await pool.query("SELECT count(*)::int AS n FROM users");
  expect(r.rows[0].n).toBe(1);
});

3. Python (pytest)

uv add --dev testcontainers pytest-asyncio asyncpg

import pytest
from testcontainers.postgres import PostgresContainer

@pytest.fixture(scope="session")
def postgres_url():
    with PostgresContainer("postgres:15-alpine") as pg:
        yield pg.get_connection_url()

4. Idempotent migrations / seeds

Use your SQL-as-SSOT files directly — no separate test schema.

5. Isolation strategy

Option	Speed	Isolation	Setup
Container per test	slow (3s)	perfect	rare
One container + rollback tx	fast	high	recommended
One container + TRUNCATE	medium	medium	easy identity reset

beforeEach(async () => { await pool.query("BEGIN"); });
afterEach(async () => { await pool.query("ROLLBACK"); });

6. CI

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: pnpm install
      - run: pnpm test:integration

GitHub Actions ships Docker, no services: needed.

7. Gotchas

• 60s timeout too small on first pull → set 120s
• Hardcoded ports → let testcontainers pick
• BEGIN/ROLLBACK does not reset sequences → use TRUNCATE ... RESTART IDENTITY
• Parallel workers sharing a container → prefer rollback tx or container per worker

8. Kafka / Redis / Elasticsearch

import { KafkaContainer } from "@testcontainers/kafka";
import { RedisContainer } from "@testcontainers/redis";

const kafka = await new KafkaContainer("confluentinc/cp-kafka:7.5.0").start();
const redis = await new RedisContainer("redis:7-alpine").start();

Closing

"Mock DB bugs that only appear in production" is the main reason to adopt testcontainers. The overhead is worth it.

Next

• 05-playwright-e2e