Testing Against a Database

When a real engine in a container is worth the seconds it costs, when a fake is fine, and how to isolate tests either way.

Syntaxconst container = await new PostgreSqlContainer('postgres:17-alpine').start();

There are two ways to put a database behind a test. Run the real engine in a throwaway container, or replace it with a fake: an in-memory SQLite database, or a double standing in for the repository.

What a fake cannot tell you

A fake answers questions about your code. It cannot answer questions about the database. SQLite will not raise the unique-violation error code Postgres raises, will not enforce a deferred foreign key the same way, has different transaction isolation, no jsonb or array types, different string comparison and collation rules, and no notion of your migrations. Every one of those differences has shipped a bug.

So: use a fake for business logic that merely happens to touch a repository, and the real engine for anything whose correctness is the SQL — queries, constraints, transactions, migrations, locking.

Running the real thing

Testcontainers starts a container from a test and stops it afterwards. new PostgreSqlContainer('postgres:17-alpine').start() resolves once the server accepts connections, and getConnectionUri() gives the URL to feed your TypeOrmModule, Prisma client or pool. Start it once per run in a global setup, not once per file.

Isolation

Shared state between tests is the real difficulty. Pick one: truncate the affected tables before each test, or open a transaction before each test and roll it back after. Rollback is faster and leaves nothing behind, but code that commits its own transaction will not work inside it.

Example

// test/global-setup.ts
// Referenced from vitest.config.e2e.mts as test.globalSetup, so it runs once
// for the whole run, before any test file, and the teardown runs once at the end.
import {
  PostgreSqlContainer,
  StartedPostgreSqlContainer,
} from '@testcontainers/postgresql';

let container: StartedPostgreSqlContainer;

export async function setup() {
  // start() resolves only once Postgres is accepting connections.
  container = await new PostgreSqlContainer('postgres:17-alpine').start();
  // Every test file reads the URL from the environment.
  process.env.DATABASE_URL = container.getConnectionUri();
}

export async function teardown() {
  await container.stop();
}

// test/orders.repository.e2e-spec.ts
import { beforeAll, afterAll, beforeEach, describe, expect, it } from 'vitest';
import { Test, TestingModule } from '@nestjs/testing';
import { TypeOrmModule, getDataSourceToken } from '@nestjs/typeorm';
import { DataSource, QueryFailedError } from 'typeorm';
import { Order } from '../src/orders/order.entity.js';
import { OrdersModule } from '../src/orders/orders.module.js';
import { OrdersRepository } from '../src/orders/orders.repository.js';

describe('OrdersRepository (real Postgres)', () => {
  let moduleRef: TestingModule;
  let dataSource: DataSource;
  let repository: OrdersRepository;

  beforeAll(async () => {
    moduleRef = await Test.createTestingModule({
      imports: [
        TypeOrmModule.forRoot({
          type: 'postgres',
          url: process.env.DATABASE_URL,
          entities: [Order],
          synchronize: true, // fine for a scratch database; see the migration example
        }),
        OrdersModule,
      ],
    }).compile();

    // TypeOrmModule registers the DataSource as an async factory provider, and
    // that factory calls dataSource.initialize(), so the connection is already
    // open when compile() resolves. init() is still what runs onModuleInit and
    // the rest of the lifecycle hooks, so keep it if any provider needs them.
    await moduleRef.init();

    // getDataSourceToken() is the token the default DataSource is registered under.
    dataSource = moduleRef.get<DataSource>(getDataSourceToken());
    repository = moduleRef.get(OrdersRepository);
  }, 60_000); // first run pulls the image

  beforeEach(async () => {
    // RESTART IDENTITY so generated ids are stable; CASCADE for child tables.
    await dataSource.query('TRUNCATE TABLE "orders" RESTART IDENTITY CASCADE');
  });

  afterAll(async () => {
    await moduleRef.close(); // closes the pool; the container is stopped by teardown
  });

  it('stores and reloads an order', async () => {
    const saved = await repository.create({ sku: 'A-1', quantity: 2 });

    await expect(repository.findById(saved.id)).resolves.toMatchObject({ sku: 'A-1' });
  });

  it('refuses a duplicate reference', async () => {
    // This is the assertion a SQLite fake cannot make honestly: the unique index
    // and the error it raises are Postgres behaviour.
    await repository.create({ sku: 'A-1', quantity: 1, reference: 'REF-1' });

    await expect(
      repository.create({ sku: 'A-2', quantity: 1, reference: 'REF-1' }),
    ).rejects.toThrow(QueryFailedError);
  });
});

When to use it

Prove a unique index actually rejects the duplicate your API is supposed to reject.
Run the migration files against an empty container in CI, before they run against production.
Test a query that uses jsonb, window functions or ON CONFLICT, which no fake reproduces.
Keep a fast logic suite on in-memory SQLite and a slower correctness suite on real Postgres.

More examples

Rolling back instead of truncating

Rollback isolation is the fastest option and the right default for repository tests. It has one hard limit: the code under test must use this same query runner. A service that calls dataSource.transaction() opens its own transaction on a different connection, so it cannot see the uncommitted rows and its own commit survives your rollback. For those, truncate instead.

// test/invoices.repository.e2e-spec.ts
import { afterEach, beforeEach, expect, it } from 'vitest';
import { DataSource, QueryRunner } from 'typeorm';
import { Invoice } from '../src/invoices/invoice.entity.js';

// dataSource comes from the module set up in beforeAll, as in the main example.
declare const dataSource: DataSource;

let queryRunner: QueryRunner;

beforeEach(async () => {
  queryRunner = dataSource.createQueryRunner();
  await queryRunner.connect();        // take one connection out of the pool
  await queryRunner.startTransaction();
});

afterEach(async () => {
  // Nothing this test wrote is ever committed, so the next test starts clean
  // without a TRUNCATE round trip.
  await queryRunner.rollbackTransaction();
  await queryRunner.release();        // give the connection back
});

it('writes an invoice inside the test transaction', async () => {
  await queryRunner.manager.save(Invoice, { total: 1200, orderId: 'o1' });

  const count = await queryRunner.manager.count(Invoice);
  expect(count).toBe(1);
});

Migrations, not synchronize

synchronize: true builds the schema from the entity classes, which is convenient and is not what production does. This test runs the migration files against a clean container, so a migration that fails on an empty database, or drifts from the entities, fails in CI. Worth one test in any project that deploys migrations.

// test/migrations.e2e-spec.ts
import { beforeAll, afterAll, expect, it } from 'vitest';
import { Test, TestingModule } from '@nestjs/testing';
import { TypeOrmModule, getDataSourceToken } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';
import { Order } from '../src/orders/order.entity.js';

let moduleRef: TestingModule;
let dataSource: DataSource;

beforeAll(async () => {
  moduleRef = await Test.createTestingModule({
    imports: [
      TypeOrmModule.forRoot({
        type: 'postgres',
        url: process.env.DATABASE_URL,
        entities: [Order],
        migrations: ['dist/migrations/*.js'],
        synchronize: false,          // build the schema the way production does
        migrationsRun: false,        // run them explicitly so the test can assert
      }),
    ],
  }).compile();

  await moduleRef.init();
  dataSource = moduleRef.get<DataSource>(getDataSourceToken());
});

afterAll(async () => {
  await moduleRef.close();
});

it('applies every pending migration and leaves nothing pending', async () => {
  const applied = await dataSource.runMigrations();
  expect(applied.length).toBeGreaterThan(0);

  // A second call must find nothing: a migration that is not idempotent, or one
  // whose entity no longer matches, shows up here rather than during a deploy.
  await expect(dataSource.showMigrations()).resolves.toBe(false);
});

The fake, and what it costs you

Use the in-memory engine when the database is incidental: the service needs a repository to exist, but the behaviour under test is arithmetic or branching. It keeps the fast suite fast. The rule of thumb is that a passing SQLite test is evidence about your code and no evidence at all about your schema.

// test/pricing.service.spec.ts
import { beforeAll, afterAll, expect, it } from 'vitest';
import { Test, TestingModule } from '@nestjs/testing';
import { TypeOrmModule } from '@nestjs/typeorm';
import { Order } from '../src/orders/order.entity.js';
import { PricingModule } from '../src/pricing/pricing.module.js';
import { PricingService } from '../src/pricing/pricing.service.js';

// Requires the better-sqlite3 npm package. No container, no Docker, starts in
// milliseconds - good for logic that reads a row and then computes.
let moduleRef: TestingModule;
let pricing: PricingService;

beforeAll(async () => {
  moduleRef = await Test.createTestingModule({
    imports: [
      TypeOrmModule.forRoot({
        type: 'better-sqlite3',
        database: ':memory:', // dropped when the connection closes
        entities: [Order],
        synchronize: true,
      }),
      PricingModule,
    ],
  }).compile();

  await moduleRef.init();
  pricing = moduleRef.get(PricingService);
});

afterAll(async () => {
  await moduleRef.close();
});

it('applies the volume discount above ten units', async () => {
  // What this test cannot see: Postgres-specific SQL in a raw query, jsonb and
  // array columns, the real unique-violation error, actual isolation levels,
  // and anything your migrations do. Keep those in the container suite.
  expect(pricing.priceFor({ unitPrice: 100, quantity: 12 })).toBe(1080);
});

Discussion

Be the first to comment on this lesson.

What a fake cannot tell you

Running the real thing

Isolation

Example

When to use it

More examples

Rolling back instead of truncating

Migrations, not synchronize

The fake, and what it costs you

Related

Discussion