Integration testing against a real database

Where the pyramid bends: what only a real database can catch

Ask yourself: what is your 94%-coverage mock suite actually testing? It’s testing that your code is consistent with your assumptions about the database — not that the database behaves that way. Here is the specific bug class that gap produces.

Unit tests (mocked dependencies, ~1ms each, many of them) verify your logic in isolation. Integration tests verify that your code plus a real collaborator — the database — actually work together. That distinction is the whole point: a mock returns what you programmed it to return, so it can only ever confirm the assumptions you already hold. The bugs that reach prod from the data layer are exactly the ones your assumptions missed:

• Constraint violations — UNIQUE, NOT NULL, CHECK, foreign keys. A mocked repo will “save” a duplicate email all day; Postgres raises 23505.
• Cascade rules — ON DELETE CASCADE / SET NULL reshape rows you never explicitly touched. No mock models referential action.
• Migration drift — the migration that didn’t run, the column that’s still varchar(50), the index that’s missing. Mocks have no schema to drift from.
• Real ORM mapping — JSONB columns, enum types, numeric precision, sequences, snake_case ↔ camelCase. The ORM’s actual SQL generation, not your mental model of it.
• Transaction & isolation behaviour — what a rollback actually rolls back, what a lock actually blocks.

The cost is real: integration tests need a real Postgres and run in ~10–100ms+ (real IO) instead of ~1ms, and container startup costs ~1–3s once per suite. So you write far fewer of them than unit tests — the pyramid stays a pyramid. You’re not replacing unit tests; you’re adding a thin, high-value layer that asserts the things mocks can’t.

Why not a mocked repo, and why not SQLite

Two tempting shortcuts both fail the same way — by giving you green tests that lie:

// The mock asserts YOUR ASSUMPTION, not reality.
const repo = { save: jest.fn().mockResolvedValue({ id: 1, email: 'a@b.com' }) };
// This "passes" even if email a@b.com already exists in prod and would 23505.
// The mock cannot enforce UNIQUE, cannot cascade, cannot run a migration.

A mocked repository can’t catch a unique violation, can’t fire an FK cascade, can’t surface an N+1, and can’t notice a migration that never ran — because it is your assumption, encoded. SQLite-as-a-fake is the subtler trap: it’s a real database, so it feels honest, but it’s a different engine from prod. Its type affinity, JSONB handling, sequence semantics, and isolation behaviour all diverge from Postgres. You get tests that are green on SQLite and red in production — the worst possible outcome, because you trusted them. The senior stance is unambiguous: integration tests run on the same engine and major version as prod. If prod is Postgres 16, the test container is Postgres 16.

Testcontainers: a throwaway Postgres per test run

@testcontainers/postgresql spins up a real, disposable Postgres in Docker for the duration of the run and hands you a dynamic connection URL. You feed that URL into TypeOrmModule.forRoot in your testing module, run your real migrations against it (never synchronize: true — that skips the very migrations you need to test), execute the suite, and tear the container down. One beforeAll builds it all:

import { Test } from '@nestjs/testing';
import { TypeOrmModule, getDataSourceToken } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';
import { PostgreSqlContainer, StartedPostgreSqlContainer } from '@testcontainers/postgresql';
import { UsersModule } from '../src/users/users.module';

let container: StartedPostgreSqlContainer;
let dataSource: DataSource;

beforeAll(async () => {
  // ~1–3s once: a real Postgres on the SAME major version as prod
  container = await new PostgreSqlContainer('postgres:16').start();

  const moduleRef = await Test.createTestingModule({
    imports: [
      TypeOrmModule.forRoot({
        type: 'postgres',
        url: container.getConnectionUri(), // dynamic host/port — never hardcode 5432
        entities: [/* … */],
        synchronize: false,                // test the REAL migrations, not auto-sync
        migrationsRun: false,              // we run them explicitly below
      }),
      UsersModule,
    ],
  }).compile();

  dataSource = moduleRef.get<DataSource>(getDataSourceToken());
  await dataSource.runMigrations();        // exercise the same SQL prod will run
}, 60_000);                                // generous timeout: pulling the image is slow on a cold cache

afterAll(async () => {
  await dataSource?.destroy();
  await container?.stop();                  // dispose the throwaway DB
});

Running runMigrations() rather than synchronize: true is the load-bearing detail: it means a migration that drops a column, adds a constraint, or backfills data is actually executed in the test, so migration drift surfaces here instead of in prod.

Per-test isolation: the technique that decides whether your suite is trustworthy

Tests must not see each other’s data. The flakiness in the Hook came from shared mutable state — one test inserted a row, another counted rows and got a number that depended on test order. Three strategies, in order of preference:

// (a) PREFERRED — wrap each test in a transaction and ROLLBACK after.
//     Fastest: nothing is ever committed, so there is nothing to clean up.
let qr;
beforeEach(async () => {
  qr = dataSource.createQueryRunner();
  await qr.connect();
  await qr.startTransaction();             // BEGIN
  // hand qr.manager to the code under test so its writes live in THIS tx
});
afterEach(async () => {
  await qr.rollbackTransaction();          // ROLLBACK — every write vanishes
  await qr.release();
});

// (b) ROBUST — TRUNCATE every table between tests. Survives code that commits
//     its own transactions, but slower (real DELETEs + index churn).
afterEach(async () => {
  const tables = dataSource.entityMetadatas.map((m) => `"${m.tableName}"`).join(', ');
  await dataSource.query(`TRUNCATE ${tables} RESTART IDENTITY CASCADE;`);
});

// (c) PARALLEL — a fresh schema (or database) per test FILE, so workers never
//     share state. Most isolation, most setup; reach for it when you parallelize.

Strategy (a) is fastest because a rolled-back transaction commits nothing — there’s no cleanup to do. But it has a sharp edge: it breaks the moment the code under test manages its own transaction and commits. If your service opens its own dataSource.transaction(...) and commits inside it, that commit escapes your outer test transaction (or the nested savepoint releases), and your ROLLBACK no longer undoes it. For that code, fall back to (b) TRUNCATE. The trap to internalize: shared mutable DB state + a parallel test runner = order-dependent flakiness. Isolate per test, or per worker — never let two tests touch the same committed rows.