Get started with modular databases in minutes. Install prerequisites and deploy your first module.

Quickstart: Getting Up and Running

Set up Node.js, PostgreSQL, and pgpm. Everything you need to get started with modular database development.

Prerequisites

Experience the power of modular databases by installing a pre-built module and deploying it to PostgreSQL—no SQL writing required.

Spinning Up a Modular Database in 2 Minutes

Learn to organize PostgreSQL projects with pgpm workspaces and reusable database modules.

Modular PostgreSQL Development with Database Packages

Understand how pgpm workspaces bring npm-style modularity to PostgreSQL development.

pgpm Workspaces: A New Way to Organize PostgreSQL Projects

Set up your first pgpm workspace with proper configuration and structure.

Initializing a Workspace for Modular PostgreSQL Development

Build your first database module with deploy, revert, and verify scripts.

Creating Your First PostgreSQL Database Module

Write and run tests for your database module with watch mode for rapid feedback.

Testing Your Database Module

Master module dependencies, cross-module references, and automatic dependency resolution.

Managing Database Package Dependencies in a Workspace

Master the workflow for adding, organizing, and managing database changes with pgpm.

Authoring Database Changes

Learn the fundamental workflow for adding database changes using the pgpm add command and the three-file pattern for safe, reversible migrations.

Adding Your First Database Change

Structure migrations using nested paths for maintainable schemas.

Change Naming Conventions

Understand the plan file format and how pgpm generates deployment plans from SQL dependencies.

Plan Files and Deployment Plans

Master end-to-end PostgreSQL testing with ephemeral databases, RLS testing, and CI/CD automation.

End-to-End Postgres Testing with TypeScript

Create ephemeral PostgreSQL databases for testing with pgsql-test. Fast, isolated, and automatically cleaned up.

Spinning Up Temporary Testing Databases for Postgres

Test RLS policies by simulating different users and roles. Verify that your security policies actually work as intended.

How to Test Row-Level Security (RLS) in PostgreSQL

Test complex multi-user scenarios by switching between users within tests. Master the savepoint pattern to handle exceptions and aborted transactions when testing operations that should fail.

Advanced RLS Testing Scenarios - Simulating Many Users and Roles

Learn how to seed test databases with realistic data using loadJson, loadSql, and loadCsv methods for comprehensive testing scenarios.

Seeding PostgreSQL Test Databases

TypeScript-native testing for Supabase with modern workflows.

End-to-End Postgres Testing with Supabase

Introducing Supabase Test Suite: TypeScript-native testing for Supabase with isolated databases, RLS testing, and instant feedback loops.

Supabase Testing for the Modern Developer

Get started with supabase-test: install dependencies, configure local Supabase, and write your first isolated database test with automatic rollbacks.

Setting Up Local Supabase Database Testing

Create your first Supabase test with supabase-test's getConnections(), understand test isolation, and verify your setup with simple database queries.

Writing Your First Supabase Test in TypeScript

Test Supabase RLS policies by simulating authenticated users and anonymous access. Verify your security policies work as intended with supabase-test.

Testing Row-Level Security Policies in Supabase

Learn how to seed Supabase test databases with auth.users, realistic data using loadJson, loadSql, and loadCsv methods for comprehensive testing.

Seeding Supabase Test Databases

Set up GitHub Actions CI/CD pipeline to automatically test your Supabase database with every commit. Learn how to configure workflows, start Supabase, and run tests.

Running Supabase Tests in GitHub Actions

Automate Supabase testing in GitHub Actions: provision local Supabase stack, run tests in CI, and optimize for fast feedback loops.

Troubleshooting Supabase Test

Learn to test PostgreSQL databases with Drizzle ORM, including RLS policies and context management.

End-to-End Postgres Testing with Drizzle ORM

Set up Drizzle ORM with drizzle-orm-test for PostgreSQL testing. Create type-safe database queries with automatic test isolation.

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

Test RLS policies with Drizzle ORM using automatic context management. Verify security policies with type-safe queries.

Testing Row-Level Security with Drizzle ORM

Common issues and solutions for pgpm, PostgreSQL, and testing.

Troubleshooting

Quick fixes for common pgpm, PostgreSQL, and testing issues.

Common Issues and Solutions


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

In this lesson, you'll set up [drizzle-orm-test](https://www.npmjs.com/package/drizzle-orm-test) to test your Drizzle ORM queries with ephemeral PostgreSQL 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 PostgreSQL is running and database users are initialized as shown in [prerequisites](/learn/quickstart/prerequisites).

## Install pgpm

```bash
npm i -g pgpm
```

## Create a Workspace for Your Drizzle Project

Create a pgpm workspace to organize your Drizzle project:

```bash
pgpm init --workspace
```

When prompted, enter your workspace name:

```sh
? Enter workspace name: my-database-project
```

Install dependencies:

```bash
cd my-database-project
pnpm install
```

## Create a Module for Your Database

Create a pgpm module to organize your database code:

```bash
pgpm init
```

Enter module details when prompted:

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

Navigate to the module:

```bash
cd packages/pets
```

## Install Drizzle Testing Dependencies

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

```bash
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`:

```typescript
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:

```bash
pgpm add pets_table
```

Edit `deploy/pets_table.sql`:

```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`:

```typescript
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:

```bash
pnpm test
```

> **Note:** If you experience connection issues, see [Tests fail to connect to database](/learn/troubleshooting/common-issues-and-solutions#tests-fail-to-connect-to-database).

You should see:

```bash
 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:

```typescript
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:

```typescript
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.
