How to Setup Drizzle ORM for End-to-End Testing

Testing database logic with Drizzle ORM requires real Postgres—not mocks. But you shouldn't sacrifice type safety or deal with manual cleanup.

In this lesson, you'll set up drizzle-orm-test to test your Drizzle ORM queries with ephemeral Postgres databases. Get automatic context management, type safety, and transaction-based test isolation.

Why drizzle-orm-test?

drizzle-orm-test is a drop-in replacement for pgsql-test that patches Drizzle's client to automatically apply context before each query. This makes RLS testing seamless while maintaining Drizzle's type-safe query builder.

Prerequisites

Ensure Postgres is running and database users are initialized as shown in prerequisites.

Install pgpm

npm i -g pgpm

Create a Workspace for Your Drizzle Project

Create a pgpm workspace to organize your Drizzle project:

pgpm init workspace

When prompted, enter your workspace name:

? Enter workspace name: my-database-project

Install dependencies:

cd my-database-project
pnpm install

Create a Module for Your Database

Create a pgpm module to organize your database code:

pgpm init

Enter module details when prompted:

? Enter module name: pets
? Select extensions (use arrow keys and space to select):
  ◉ plpgsql
  ◉ uuid-ossp

Navigate to the module:

cd packages/pets

Install Drizzle Testing Dependencies

Install Drizzle ORM and drizzle-orm-test in your module:

pnpm add drizzle-orm drizzle-orm-test
pnpm add -D drizzle-kit

The drizzle-orm-test package is built on top of pgsql-test with Drizzle support baked in. It automatically patches Drizzle's client to apply context before each query, making RLS testing seamless.

Defining Your Schema

Create src/schema.ts:

import { pgTable, serial, text, integer, boolean } from 'drizzle-orm/pg-core';

export const pets = pgTable('pets', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  species: text('species').notNull(),
  age: integer('age'),
  adopted: boolean('adopted').default(false)
});

Adding the Database Change

Add the pets table as a database change:

pgpm add pets_table

Edit deploy/pets_table.sql:

-- Deploy pets_table

CREATE TABLE pets (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  species TEXT NOT NULL,
  age INTEGER,
  adopted BOOLEAN DEFAULT false
);

Your First Drizzle Test

Inside of __tests__/drizzle-basic.test.ts:

import { drizzle } from 'drizzle-orm/node-postgres';
import { getConnections, PgTestClient } from 'drizzle-orm-test';
import { pets } from '../src/schema';

let db: PgTestClient;
let pg: PgTestClient;
let teardown: () => Promise<void>;

beforeAll(async () => {
  ({ db, pg, teardown } = await getConnections());
});

afterAll(async () => { await teardown(); });

beforeEach(async () => { await pg.beforeEach(); });
afterEach(async () => { await pg.afterEach(); });

it('can insert and query pets with Drizzle', async () => {
  const drizzleDb = drizzle(pg.client);

  // Insert with type-safe values
  await drizzleDb.insert(pets).values({
    name: 'Buddy',
    species: 'dog',
    age: 3
  });

  // Query with type-safe results
  const result = await drizzleDb.select().from(pets);
  expect(result).toHaveLength(1);
  expect(result[0].name).toBe('Buddy');
  expect(result[0].species).toBe('dog');
  expect(result[0].age).toBe(3);
  expect(result[0].adopted).toBe(false);
});

it('starts with clean state', async () => {
  const drizzleDb = drizzle(pg.client);

  const result = await drizzleDb.select().from(pets);
  expect(result).toHaveLength(0);
});

Run the test:

pnpm test

Note: If you experience connection issues, see Tests fail to connect to database.

You should see:

 PASS  __tests__/drizzle-basic.test.ts
  ✓ can insert and query pets with Drizzle (18ms)
  ✓ starts with clean state (5ms)

Test Suites: 1 passed, 1 total
Tests:       2 passed, 2 total

What just happened? The drizzle(pg.client) pattern creates a Drizzle instance using the patched client from drizzle-orm-test. All queries automatically include context (which we'll use for RLS in the next lesson).

Note that we're using pg from getConnections(), which represents the superuser connection. The db connection (also available from getConnections()) represents the app user and will be featured in the next lesson for RLS testing. Both are PgTestClient instances with the same methods.

Understanding the Pattern

The key pattern is:

const drizzleDb = drizzle(pg.client);

This gives you:

• Type-safe queries with Drizzle's query builder
• Automatic context management for RLS (applied before each query)
• Transaction isolation via beforeEach()/afterEach()
• Standard Drizzle API - no wrapper needed

Using Drizzle's Query Builder

Test more advanced queries:

import { eq, and, gte } from 'drizzle-orm';

it('can filter pets with conditions', async () => {
  const drizzleDb = drizzle(pg.client);

  // Insert test data
  await drizzleDb.insert(pets).values([
    { name: 'Max', species: 'dog', age: 5 },
    { name: 'Luna', species: 'cat', age: 2 },
    { name: 'Charlie', species: 'dog', age: 7 }
  ]);

  // Query with filters
  const dogs = await drizzleDb
    .select()
    .from(pets)
    .where(eq(pets.species, 'dog'));

  expect(dogs).toHaveLength(2);

  // Query with multiple conditions
  const oldDogs = await drizzleDb
    .select()
    .from(pets)
    .where(and(
      eq(pets.species, 'dog'),
      gte(pets.age, 6)
    ));

  expect(oldDogs).toHaveLength(1);
  expect(oldDogs[0].name).toBe('Charlie');
});

What You've Accomplished

You now have a complete Drizzle testing environment:

• pgpm workspace organized for your database project
• Drizzle ORM configured with type-safe schema definitions
• drizzle-orm-test configured for instant, isolated test databases
• Database changes tracked with pgpm migrations
• Working tests that demonstrate type-safe queries and transaction isolation

Key Takeaways

• drizzle-orm-test is a drop-in replacement for pgsql-test with Drizzle support
• Use drizzle(pg.client) to create a Drizzle instance with context management
• pg vs db connections: Both are PgTestClient instances with the same methods. pg is the superuser connection (used in this lesson), while db is the app user connection that can toggle roles with setContext() for RLS testing (featured in the next lesson)
• Type-safe queries ensure compile-time correctness
• Transaction isolation keeps tests clean and fast
• Standard Drizzle API works without modification
• No wrapper needed - just use Drizzle normally

What's Next

In the next lesson, we'll add Row-Level Security (RLS) policies and test them with Drizzle by switching between user contexts.

RLS testing is where drizzle-orm-test really shines—the automatic context management means you can test security policies with the same Drizzle queries you use in production.