Run Drizzle repository tests on real Postgres: a container per run, migrations once, a schema per worker, rollback per test, and PGlite for speed.

Testing Drizzle ORM Queries Against a Real Database

Drizzle is a thin layer over SQL, and that is exactly why mocking it is a poor idea: the interesting behaviour of a Drizzle query is the SQL it produces and how the database executes it — joins, constraints, null handling, ordering, the exact semantics of ilike and array operators. A mock of the query builder verifies that your code called .where() with some arguments; it cannot tell you the query returns the right rows. This guide covers testing Drizzle repositories against a real Postgres: starting one container per run, applying migrations once, isolating workers with a schema each, rolling back each test’s writes, and using PGlite — Postgres compiled to WebAssembly — when you want the same dialect without a container. It sits under database and ORM mocking.

Root Cause Analysis

Query bugs are overwhelmingly semantic rather than syntactic. The query compiles, runs, and returns rows — just not the right ones. A left join that should have been an inner join includes orphans; a filter on a nullable column silently excludes nulls; a case-sensitive comparison misses half the matches; an ordering without a tiebreaker returns rows in a different sequence on a different day. None of these is visible to a test that replaces the database with a stub returning prepared rows.

Constraints are the second category. Unique indexes, foreign keys, check constraints and not-null columns are behaviour, and application code often relies on them — catching a unique-violation error to report “email already taken”, for instance. A mock has no constraints, so the error path is never exercised, and the day the index is dropped in a migration, nothing notices.

The reason teams mock anyway is cost: a real database has been slow to start and awkward to reset. Both problems have mature solutions now, and once they are in place a real-database repository test runs in a few milliseconds per test, which removes the justification for mocking at this tier.

What a mocked query builder cannot catch Wrong join type, nulls excluded by a filter, case-sensitive matching, unstable ordering and violated constraints all produce valid SQL that returns the wrong rows or the wrong error, which only a real database reveals. semantic bugs left join where inner was meant nulls silently filtered out case-sensitive comparison ordering with no tiebreaker constraint behaviour unique violation mapped to a message foreign key blocks a delete check constraint rejects a value default and generated columns
Every item here produces valid SQL; only executing it against a real engine reveals the defect.

Reproducible Setup

A Drizzle schema and a repository function — the unit under test is the function and the SQL it runs.

// src/db/schema.ts
import { pgTable, text, integer, timestamp, uniqueIndex } from 'drizzle-orm/pg-core';

export const customers = pgTable('customers', {
  id: text('id').primaryKey(),
  email: text('email').notNull(),
  name: text('name'),
}, (t) => ({ emailIdx: uniqueIndex('customers_email_idx').on(t.email) }));

export const orders = pgTable('orders', {
  id: text('id').primaryKey(),
  customerId: text('customer_id').notNull().references(() => customers.id),
  totalPence: integer('total_pence').notNull(),
  placedAt: timestamp('placed_at').notNull().defaultNow(),
});
// src/db/repos/orders.ts
import { and, eq, gte, desc, sql } from 'drizzle-orm';
import type { Db } from '../client';
import { orders, customers } from '../schema';

export const ordersRepo = (db: Db) => ({
  recentForCustomer: (customerId: string, since: Date) =>
    db.select().from(orders)
      .where(and(eq(orders.customerId, customerId), gte(orders.placedAt, since)))
      .orderBy(desc(orders.placedAt), desc(orders.id)),

  topCustomers: (limit: number) =>
    db.select({ email: customers.email, spent: sql<number>`sum(${orders.totalPence})`.mapWith(Number) })
      .from(customers).innerJoin(orders, eq(orders.customerId, customers.id))
      .groupBy(customers.email).orderBy(desc(sql`sum(${orders.totalPence})`)).limit(limit),
});

Implementation

Step 1 — Start one Postgres container for the whole run. Global setup starts it once and passes the connection string to every worker; starting one per file would dominate the run time.

// test/global-setup.ts
import { PostgreSqlContainer } from '@testcontainers/postgresql';

export default async function setup({ provide }: { provide: (k: string, v: string) => void }) {
  const pg = await new PostgreSqlContainer('postgres:16.4-alpine').start();
  provide('DATABASE_URL', pg.getConnectionUri());
  return async () => { await pg.stop(); };
}

Step 2 — Give each worker its own schema and migrate it once. A schema per worker lets parallel workers run without touching each other’s rows, and migrating once per worker keeps per-test cost near zero.

// test/db.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import { Pool } from 'pg';
import { inject } from 'vitest';

const schema = `test_w${process.env.VITEST_POOL_ID ?? '0'}`;
const pool = new Pool({ connectionString: inject('DATABASE_URL'), options: `-c search_path=${schema}` });
export const db = drizzle(pool);

export async function prepareWorkerSchema() {
  await pool.query(`DROP SCHEMA IF EXISTS ${schema} CASCADE; CREATE SCHEMA ${schema}`);
  await migrate(db, { migrationsFolder: './drizzle', migrationsSchema: schema });
}

Step 3 — Roll back each test’s writes with a transaction. Wrap the test in a transaction that always rolls back; the database is left exactly as the migrations made it, in microseconds.

// test/with-rollback.ts
import { db } from './db';

class Rollback extends Error {}

export async function withRollback<T>(fn: (tx: typeof db) => Promise<T>) {
  let result: T | undefined;
  await db.transaction(async (tx) => {
    result = await fn(tx as typeof db);
    throw new Rollback();
  }).catch((e) => { if (!(e instanceof Rollback)) throw e; });
  return result as T;
}

Step 4 — Test the query’s semantics, not its calls. Seed exactly the rows that distinguish right from wrong, and assert on the rows returned.

// src/db/repos/orders.test.ts
import { test, expect, beforeAll } from 'vitest';
import { prepareWorkerSchema } from '../../../test/db';
import { withRollback } from '../../../test/with-rollback';
import { customers, orders } from '../schema';
import { ordersRepo } from './orders';

beforeAll(prepareWorkerSchema);

test('returns only this customer’s orders since the date, newest first, ties broken by id', () =>
  withRollback(async (tx) => {
    await tx.insert(customers).values([{ id: 'c1', email: 'a@x.test' }, { id: 'c2', email: 'b@x.test' }]);
    const t = new Date('2026-09-01T10:00:00Z');
    await tx.insert(orders).values([
      { id: 'o1', customerId: 'c1', totalPence: 100, placedAt: new Date('2026-08-01') },  // too old
      { id: 'o2', customerId: 'c1', totalPence: 200, placedAt: t },
      { id: 'o3', customerId: 'c1', totalPence: 300, placedAt: t },                         // same time
      { id: 'o4', customerId: 'c2', totalPence: 999, placedAt: t },                         // other customer
    ]);
    const rows = await ordersRepo(tx).recentForCustomer('c1', new Date('2026-08-15'));
    expect(rows.map((r) => r.id)).toEqual(['o3', 'o2']);
  }));
Isolation at three levels One container serves the whole run, each worker gets its own schema migrated once, and each test runs inside a transaction that rolls back, so tests never see each other's data and per-test cost stays near zero. one Postgres container — per run schema test_w1 — per worker test tx, rolled back test tx, rolled back migrated once for the worker schema test_w2 — per worker test tx, rolled back test tx, rolled back migrated once for the worker
Each level pays its cost once and hands the next level a clean, isolated space.

Step 5 — Test constraint behaviour explicitly. Error mapping from database codes to domain errors is application logic that only a real engine can drive.

test('maps a duplicate email to an EmailTaken error', () =>
  withRollback(async (tx) => {
    await tx.insert(customers).values({ id: 'c1', email: 'dup@x.test' });
    await expect(createCustomer(tx, { id: 'c2', email: 'dup@x.test' })).rejects.toBeInstanceOf(EmailTaken);
  }));

Step 6 — Use PGlite where a container is unavailable or too slow. PGlite runs Postgres in-process via WebAssembly, so the same Drizzle schema, migrations and SQL semantics apply with no Docker at all.

// test/db-pglite.ts
import { PGlite } from '@electric-sql/pglite';
import { drizzle } from 'drizzle-orm/pglite';
import { migrate } from 'drizzle-orm/pglite/migrator';

export async function pgliteDb() {
  const db = drizzle(new PGlite());
  await migrate(db, { migrationsFolder: './drizzle' });
  return db;
}

Verification

Confirm tests are isolated by running the repository suite in shuffled order with several workers; every seed must pass because nothing persists between tests.

npx vitest run src/db --sequence.shuffle --pool=forks --poolOptions.forks.maxForks=4
# ✓ 22 passed (1.9s)

Then confirm the tests exercise real semantics by introducing a semantic bug — change innerJoin to leftJoin in topCustomers. A test that seeds a customer with no orders must fail, which a mocked builder could never show.

Container Postgres versus PGlite A containerised Postgres matches production exactly including extensions and version, while PGlite needs no Docker and starts in milliseconds but lacks some extensions and runs single-connection. Postgres container exact production version extensions, real concurrency needs Docker, seconds to start PGlite same SQL dialect, in-process no Docker, milliseconds some extensions missing
PGlite for the fast inner loop, the container for anything that depends on extensions or concurrency.

Troubleshooting

Symptom: tests see rows from other tests. Diagnosis: a test wrote outside the rollback transaction — often because the code under test uses the global db rather than the tx passed in. Fix: pass the database handle into repository functions explicitly, so tests can hand them the transaction.

Symptom: migrations run on every test file and the suite is slow. Diagnosis: migration is in a beforeAll of each file rather than once per worker. Fix: track whether the worker’s schema is already migrated, or move preparation into a worker-scoped setup.

Symptom: a query that uses SELECT … FOR UPDATE deadlocks in tests. Diagnosis: the code under test opens its own transaction inside the test’s rollback transaction and waits on a lock the outer transaction holds. Fix: use savepoints for nested transactions, which Drizzle supports via nested transaction calls, or test locking behaviour with separate connections outside the rollback helper.

Symptom: PGlite rejects a migration that works in Postgres. Diagnosis: the migration uses an extension PGlite does not bundle. Fix: run those tests against the container, and keep PGlite for the majority of repository tests that use plain SQL.

FAQ

Should repository tests ever mock Drizzle?

Rarely. Mocking is reasonable in a service-layer test that wants to assert what happens when the repository throws — but then mock the repository function, not the query builder. The query builder is where the SQL lives, and the SQL is what needs a database.

Is SQLite a good substitute for Postgres here?

Not for Postgres-specific code. Differences in type affinity, case sensitivity, date handling and available functions mean tests pass on SQLite and fail on Postgres. If production is Postgres, PGlite or a container gives the same dialect; SQLite is right when production is SQLite, as in using an in-memory SQLite for repository tests.

How many rows should a query test seed?

As few as distinguish correct from incorrect: one row that should match, one that should not for each condition in the query, and one tie for any ordering. That usually means four to eight rows, which keeps the test readable and makes the reason for each row obvious.

What about testing migrations themselves?

Run every migration from empty in CI — the per-worker setup already does — and add a test that applies the latest migration to a snapshot of the previous schema with representative data. That catches migrations that work on an empty database and fail on real rows.