Your unit tests pass, but your data still disappears

If you mock your database in tests, you are testing that your repository layer calls the right methods. You are not testing that data survives a crash, that a unique constraint actually blocks duplicates, or that a transaction rolls back when something fails.

That distinction matters. A mocked save() returns whatever you tell it to return. A real save() can deadlock, silently truncate a string, or write to a replica that lags three seconds behind the primary. Your test suite will be green while your users lose data.

Why mocked database tests give false confidence

Most persistence tests look like this:

// A test that verifies syntax, not physics
jest.mock('./db', () => ({
  query: jest.fn().mockResolvedValue({ rows: [{ id: 1 }] }),
}));

it('saves a user', async () => {
  const repo = new UserRepository();
  const user = await repo.create({ name: 'Ada' });
  expect(user.id).toBe(1);
});

This test proves your SQL is syntactically valid and your mapper does not throw. It proves nothing about persistence.

The real hazards live in the gap between “the query ran” and “the data is durable.” Does your ORM flush the write buffer before returning? Does your connection pool reuse a transaction that already rolled back? Does ON CONFLICT DO NOTHING swallow an insert you expected to succeed?

You will not find those bugs with a mock that returns { id: 1 } on command.

The round-trip test: the simplest honest test

The most basic persistence test is also the most honest. Write data, read it back, assert it matches.

import { createConnection } from './db';

it('persists and retrieves a user', async () => {
  const db = await createConnection();
  const repo = new UserRepository(db);

  await repo.create({ id: 'user-1', name: 'Ada', email: 'ada@example.com' });

  const found = await repo.findById('user-1');
  expect(found).toEqual({
    id: 'user-1',
    name: 'Ada',
    email: 'ada@example.com',
  });
});

This catches the obvious failures: missing commits, wrong column mappings, serializer bugs. It also catches the subtle ones. If your create method hashes the email before writing but findById does not hash before querying, this test fails. A mocked test would not.

Testing constraints where they live

Database constraints are logic. They deserve tests just like any other branch in your code.

it('enforces unique emails at the database level', async () => {
  const db = await createConnection();
  const repo = new UserRepository(db);

  await repo.create({ id: 'user-1', email: 'ada@example.com' });

  await expect(
    repo.create({ id: 'user-2', email: 'ada@example.com' })
  ).rejects.toThrow(/unique constraint/i);
});

This test documents a business rule in the only place that actually enforces it. If someone later removes the unique index to “fix” a flaky migration, this test screams. A mock would happily let two users share an email because the mock has no concept of an index.

Transaction behavior is not theoretical

Your application probably wraps multiple writes in a transaction. The test for that transaction should prove that partial failure leaves the database consistent.

it('rolls back the entire transfer on failure', async () => {
  const db = await createConnection();
  const accounts = new AccountRepository(db);

  await accounts.create({ id: 'A', balance: 100 });
  await accounts.create({ id: 'B', balance: 50 });

  // Force a failure mid-transaction by violating a check constraint
  const transfer = db.transaction(async (trx) => {
    const acc = new AccountRepository(trx);
    await acc.debit('A', 30); // succeeds
    await acc.debit('B', 9999); // exceeds balance, throws
  });

  await expect(transfer).rejects.toThrow();

  const a = await accounts.findById('A');
  const b = await accounts.findById('B');
  expect(a.balance).toBe(100); // rollback preserved original state
  expect(b.balance).toBe(50);
});

This is the test that saves you from the 3 a.m. page where accounts are missing money because a downstream API timed out and the debit committed but the credit did not. Mocks cannot model transaction isolation. Only a real database can.

Isolation: the hard part

The objection everyone raises is speed and flakiness. If every test hits PostgreSQL, your suite will crawl, and tests will interfere with each other.

There are three practical answers, and you will probably use all three.

Template databases. PostgreSQL and most serious databases support CREATE DATABASE ... TEMPLATE. You initialize one database with your schema, then clone it per test file in milliseconds.

// test-setup.ts
import { execSync } from 'child_process';

let templateCreated = false;

export async function createIsolatedDatabase() {
  if (!templateCreated) {
    execSync('createdb test_template');
    execSync('psql test_template -f schema.sql');
    templateCreated = true;
  }
  const dbName = `test_${process.pid}_${Date.now()}`;
  execSync(`createdb -T test_template ${dbName}`);
  return connect(dbName);
}

Each test file gets a fresh database. No cleanup logic needed. At the end of the run, drop the clones.

Transactions as isolation boundaries. If template databases are too heavy, run each test inside a transaction and roll it back at the end. Most database clients expose this explicitly.

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

it('inserts a row', async () => {
  await db.query('BEGIN');
  await repo.create({ id: 'x' });
  expect(await repo.findById('x')).toBeDefined();
  // rolled back by afterEach
});

The catch is that code that itself uses transactions may conflict with the outer rollback transaction. Nested transactions via SAVEPOINT usually solve this, but not every driver handles them cleanly.

Testcontainers for CI parity. If your tests run against SQLite locally and PostgreSQL in production, you are testing a different database. Use Testcontainers to spin up the real database in Docker for every CI run.

import { PostgreSqlContainer } from '@testcontainers/postgresql';

const container = await new PostgreSqlContainer().start();
const db = await createConnection(container.getConnectionUri());

It adds 2-3 seconds to the test run. It eliminates the entire class of bugs that only reproduce on Postgres 15 with standard_conforming_strings = on.

What to mock, and what not to

I am not arguing for zero mocks. Mocks are correct at boundaries you do not control: Stripe API calls, S3 uploads, SMTP servers. Those are slow, expensive, and rate-limited.

Your database is not an external boundary in the same sense. It is infrastructure you operate. Its behavior is deterministic. Its schema is code you wrote. Mocking it means testing against a fantasy version of a system you actually run.

The trade-off is speed. A real database test takes 20-50ms. A mocked one takes 2-5ms. If you have ten thousand persistence tests, that difference matters. Most teams do not have ten thousand persistence tests. They have a hundred, and the honesty is worth the wait.

Schema drift tests catch the bugs code reviews miss

There is one more test that is embarrassingly effective and almost nobody writes it. After your migrations run, assert that your TypeScript types still match the schema.

import { sql } from './db';

it('types match the database schema', async () => {
  const columns = await sql`
    SELECT column_name, data_type, is_nullable
    FROM information_schema.columns
    WHERE table_name = 'users'
  `;

  const userColumns = new Map(columns.map((c) => [c.column_name, c]));

  // If someone adds a NOT NULL column without updating the type, this fails
  expect(userColumns.get('email').is_nullable).toBe('NO');
  expect(userColumns.get('created_at').data_type).toBe('timestamp with time zone');
});

This is the test that catches the migration adding a NOT NULL default to a column your TypeScript still thinks is optional. It is tedious to maintain by hand, which is why tools like kysely-codegen or drizzle-kit generate exist. But even a small manual version will save you a production incident.

FAQ

Should I delete all my mocked database tests?

No. Keep mocks for pure logic tests where the database is irrelevant, like testing that a service formats a query string correctly. Replace them with real database tests anywhere persistence behavior is the point.

How do I handle migrations in tests?

Run migrations against your template database once, then clone. Or run them per test suite if they are fast. Never skip migrations in CI and hope the schema matches.

What about read replicas?

If your app writes to primary and reads from replica, your tests should too. Spin up two connections in Testcontainers and configure routing the same way production does. Lag-induced stale reads are real bugs.

Does this apply to NoSQL databases?

Yes. The principle is the same. A mocked MongoDB client will not tell you if your compound index supports the query you think it does. A real one will.

Run one round-trip test today

Pick the repository method you touch most often. Delete its mock. Write a test that creates a row, reads it back, and asserts the fields match. Time it. If it takes under 50ms, write another one.

Most teams find that real database tests are not the reason their suite is slow. The reason is usually spinning up a headless browser for a unit test, or calling a real payment API in CI, or not parallelizing test files. Database tests are honest, fast enough, and they catch the bugs that cost you sleep.