🗄️ Database Testing

Why Real, Not Mocked

A mocked database is a stunt double that always hits its mark. You tell it "when asked for user 1, return this object," and it obediently does — so your test passes. But it never runs your actual SQL, never enforces your UNIQUE constraint, never applies a migration, never coerces a type. All the bugs that only a database can produce are invisible to it.

The principle from the integration-testing lesson holds here in its sharpest form: for the data layer, use a real database. Run the same engine you use in production — Postgres against Postgres, Mongo against Mongo — just a disposable test instance. Only then do your tests catch broken queries, constraint violations, and migration errors before your users do.

Test Database Options

"Use a real database" leaves a choice of how real and how it's provisioned. Each option trades realism against speed and setup effort.

Standing One Up

Two clean ways to get a real Postgres for your tests.

Option A — Docker Compose

Declare a dedicated test database as a service, mapped to a non-default port so it never collides with a local dev database. A healthcheck lets your CI wait until it's genuinely ready.

# docker-compose.test.yml
services:
  postgres-test:
    image: postgres:16
    environment:
      POSTGRES_USER: testuser
      POSTGRES_PASSWORD: testpass
      POSTGRES_DB: app_test
    ports:
      - "5433:5432"          # 5433 on the host avoids clashing with local dev
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U testuser -d app_test"]
      interval: 5s
      timeout: 5s
      retries: 5

Start it before the suite (docker compose -f docker-compose.test.yml up -d), point your test config at port 5433, and stop it after.

Option B — Testcontainers (self-contained)

Testcontainers starts a real container from inside your test run and hands you its connection details, then disposes of it automatically. No manual docker commands, and each run is fully isolated.

const { PostgreSqlContainer } = require('@testcontainers/postgresql');
const { Pool } = require('pg');

let container, pool;

beforeAll(async () => {
    // Boot a throwaway Postgres — real engine, random free port
    container = await new PostgreSqlContainer('postgres:16').start();

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

    // Apply the schema so tables exist
    await pool.query(`
        CREATE TABLE users (
            id    SERIAL PRIMARY KEY,
            name  TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL
        );
    `);
});

afterAll(async () => {
    await pool.end();          // close the pool
    await container.stop();    // dispose of the container
});

The Setup & Teardown Lifecycle

The same four hooks you met in the integration lesson orchestrate the database. The expensive work — start the container, apply the schema — happens once; the cheap reset that guarantees isolation happens before every test.

Isolation: Rollback vs. Truncate

Every test must start from an identical, known state. There are two industry-standard ways to guarantee that, and it's worth knowing when each shines.

Transaction rollback (fast)

Open a transaction in beforeEach, let the test do its inserts and updates, then ROLLBACK in afterEach. Because nothing is ever committed, the database is pristine for the next test — and rollback is faster than physically deleting rows.

let client;

beforeEach(async () => {
    client = await pool.connect();
    await client.query('BEGIN');      // start an isolated transaction
});

afterEach(async () => {
    await client.query('ROLLBACK');   // undo everything this test did
    client.release();
});

test('inserting a user is visible within the same transaction', async () => {
    await client.query(
        'INSERT INTO users (name, email) VALUES ($1, $2)',
        ['Ada', 'ada@example.com']
    );
    const { rows } = await client.query('SELECT COUNT(*) FROM users');
    expect(Number(rows[0].count)).toBe(1);
});
// After ROLLBACK the row is gone — the next test sees zero users.

Truncation (simple & robust)

In beforeEach, empty every table. It's obviously correct, works no matter how many connections the code uses, and RESTART IDENTITY resets auto-increment counters so ids are predictable.

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

Rule of thumb: reach for rollback when the code under test shares your test's connection (fast, elegant); reach for truncation when it doesn't or when you want the simplest thing that always works.

Seeding Fixtures

A test that needs rows should create them in its own setup — never lean on data another test left behind. A factory produces valid records with unique values, and lets a test override just the field it cares about.

// A factory: sensible defaults + unique values + easy overrides
function makeUser(overrides = {}) {
    const n = Math.random().toString(36).slice(2, 8);
    return {
        name: 'Test User',
        email: `user-${n}@example.com`,   // unique — no UNIQUE-constraint clashes
        role: 'user',
        ...overrides,
    };
}

async function seedUser(pool, overrides = {}) {
    const u = makeUser(overrides);
    const { rows } = await pool.query(
        'INSERT INTO users (name, email, role) VALUES ($1, $2, $3) RETURNING *',
        [u.name, u.email, u.role]
    );
    return rows[0];
}

test('an admin fixture has the admin role', async () => {
    const admin = await seedUser(pool, { role: 'admin' });   // arrange its own data
    expect(admin.role).toBe('admin');
});

Transactions, Constraints & Migrations

These are exactly the behaviors a mock can never verify — and precisely why you went to the trouble of a real database.

Test that a rollback protects a multi-step operation

An order that reserves inventory and writes an order row must be atomic: if the second step fails, the first must be undone. Prove it.

test('a failed order leaves inventory untouched', async () => {
    await seedProduct(pool, { id: 1, name: 'Widget', inventory: 5 });

    // The service wraps both steps in a transaction; the second step throws
    await expect(
        orderService.placeOrder({ productId: 1, quantity: 999 })   // exceeds stock
    ).rejects.toThrow(/insufficient inventory/i);

    // Because the transaction rolled back, inventory is unchanged and no order exists
    const product = await pool.query('SELECT inventory FROM products WHERE id = 1');
    expect(product.rows[0].inventory).toBe(5);

    const orders = await pool.query('SELECT COUNT(*) FROM orders');
    expect(Number(orders.rows[0].count)).toBe(0);
});

Test that a constraint is actually enforced

test('a duplicate email is rejected by the UNIQUE constraint', async () => {
    await seedUser(pool, { email: 'dup@example.com' });

    await expect(
        seedUser(pool, { email: 'dup@example.com' })   // same email
    ).rejects.toThrow();   // the DB throws — a mock never would
});

Test a migration up and down

Migrations are code too, and a bad one can lose data. Run the migration against a real test database, assert the new shape exists, then run the down-migration and assert it's cleanly reversed.

test('the add-profiles migration applies and reverts', async () => {
    await migration.up(pool);
    const after = await pool.query(`SELECT to_regclass('public.profiles') AS t`);
    expect(after.rows[0].t).toBe('profiles');    // table now exists

    await migration.down(pool);
    const reverted = await pool.query(`SELECT to_regclass('public.profiles') AS t`);
    expect(reverted.rows[0].t).toBeNull();        // cleanly removed
});

Practice & Quiz

beforeAll(async () => {
    await pool.query(`
        CREATE TABLE IF NOT EXISTS users (
            id    SERIAL PRIMARY KEY,
            name  TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL
        );
    `);
});

beforeEach(async () => {
    await pool.query('TRUNCATE users RESTART IDENTITY CASCADE');
});

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

test('rejects a duplicate email', async () => {
    await pool.query(
        'INSERT INTO users (name, email) VALUES ($1, $2)',
        ['Ada', 'ada@example.com']
    );

    await expect(
        pool.query(
            'INSERT INTO users (name, email) VALUES ($1, $2)',
            ['Grace', 'ada@example.com']   // same email
        )
    ).rejects.toThrow();
});

Best Practices & Pitfalls

✅ Do

• Test the data layer against a real database of the same engine as production
• Provision it with Testcontainers or a Docker Compose service for drift-free CI
• Isolate every test with transaction rollback or truncation in beforeEach
• Seed fixtures with factories that generate unique values
• Test the things only a database can do: transactions, constraints, migrations, concurrency
• Assert the database name ends in _test before any destructive query

❌ Don't

• Mock the database when the data layer itself is what you're testing
• Let tests share rows or depend on execution order
• Hard-code duplicate unique values in fixtures
• Assume an in-memory engine behaves exactly like your production database
• Ever run a test suite against a production or shared dev database

Summary

📚 Additional Resources

• Testcontainers for Node.js — real databases in tests
• node-postgres (pg) — the Postgres client used here
• Jest — setup and teardown hooks
• PostgreSQL — TRUNCATE and RESTART IDENTITY

🚀 What's Next?

You've now covered the whole integration band — strategies, endpoints, and the database beneath them. Next we climb to the top of the pyramid: End-to-End Testing Concepts — driving the whole application through a real browser, exactly as a user would.