How to Set Up PGlite for End-to-End Testing

Testing database logic normally means running a real Postgres server—starting Docker, provisioning a database, tearing it down. PGlite changes that: it's a full Postgres compiled to WASM that runs in-process, right inside your Node test runner. No server, no containers, no services in CI.

In this lesson, you'll set up pglite-test to deploy real pgpm migrations into an in-memory PGlite database and test them with transaction-based isolation. It's the same testing model as pgsql-test, minus the infrastructure.

Why PGlite?

pglite-test is a drop-in sibling of pgsql-test: same getConnections() API, same pg/db clients, same beforeEach/afterEach isolation. The difference is what runs underneath—an in-process PGlite instance instead of a Postgres server. That means:

• No services. CI is just pnpm install && pnpm test. No Postgres container, no createdb, no Docker.
• In-memory by default. Each run spins up a fresh database in milliseconds.
• Real Postgres. It's actual Postgres SQL—schemas, RLS, triggers, extensions—not a mock.

Prerequisites

None beyond Node and pnpm. There's no Postgres server to install or bootstrap—that's the whole point.

Install pgpm

npm i -g pgpm

Create a PGlite Workspace

Bootstrap a workspace pre-wired for PGlite with the --pglite flag:

pgpm init workspace --pglite

When prompted, enter your workspace name:

? Enter workspace name: my-pglite-project

The --pglite flag scaffolds from the pglite-boilerplates templates, so everything PGlite needs is already configured. It's a workspace flag: it records the template repo in pgpm.json, and every module you create inside inherits it—you don't repeat --pglite per module. (Creating a PGlite module inside a workspace that wasn't initialized with --pglite is the one case where you pass pgpm init --pglite.)

Scripting this? Every prompt has a flag, so init runs unattended in CI or from an agent. With --no-tty there are no prompts, so pass every value:

pgpm init workspace --pglite --no-tty \
  --name my-pglite-project --repoName my-pglite-project \
  --fullName "Ada Lovelace" --email ada@example.com \
  --username ada --license MIT

Install dependencies:

cd my-pglite-project
pnpm install

Create a Module for Your Database

Create a pgpm module to organize your database code:

pgpm init

Because the workspace was created with --pglite, the setting is inherited—pgpm init scaffolds a PGlite-ready module with no extra flags, including a Jest setup with the WASM flag and a generous testTimeout (PGlite's cold WASM boot can exceed Jest's 5s default). Enter the module name when prompted:

? Enter module name: pets

The module scaffolds with no extensions. Add any you need later with pgpm extension --add <ext>, or up front with pgpm init --extensions <a,b>. Navigate to the module:

cd packages/pets

The module ships with a working __tests__/basic.test.ts, so pnpm test passes before you write anything. We'll write our own test file below—delete or overwrite the scaffolded one if you don't want both.

Scripting this? The module's author, repo, and license are inherited from the workspace, so only two values are left:

pgpm init --no-tty --moduleName pets --moduleDesc "Pets module"

Adding the Database Change

Add a pets table as a database change:

pgpm add pets_table

Edit deploy/pets_table.sql:

-- Deploy pets_table

CREATE SCHEMA app;

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

Our table uses the built-in gen_random_uuid() (core Postgres since 13), so it needs no extensions at all—it runs identically on a server and in PGlite.

Deploying Without a Server

Tests aren't the only thing that runs in-process. A --pglite workspace sets "engine": "pglite" in its pgpm.json, so pgpm's migration commands target PGlite too—no server, no createdb:

pgpm deploy --package pets --yes

That deploys into a throwaway in-memory instance, which is a fast "does my plan apply cleanly?" check. To keep the database around—and verify against it later—point it at a data directory:

pgpm deploy --package pets --yes --pglite=./.pglite
pgpm verify --package pets --pglite=./.pglite

The engine is per-command overridable: --engine pg deploys the same module to a real Postgres server, and --engine pglite opts a normal workspace into PGlite. Server-only commands (pgpm docker, dump, tune, admin-users) tell you they don't apply to the pglite engine rather than failing to connect.

Your First PGlite Test

Inside of __tests__/pets.test.ts:

import { getConnections, PgTestClient } from 'pglite-test';

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

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

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

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

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

it('deployed the pgpm module into PGlite', async () => {
  const { rows } = await pg.query<{ n: number }>(
    "SELECT count(*)::int AS n FROM information_schema.tables WHERE table_schema = 'app' AND table_name = 'pets'"
  );
  expect(rows[0].n).toBe(1);
});

it('can insert and query pets', async () => {
  await pg.query(
    `INSERT INTO app.pets (name, species, age) VALUES ($1, $2, $3)`,
    ['Buddy', 'dog', 3]
  );

  const pet = await pg.one<{ name: string; species: string; age: number }>(
    'SELECT name, species, age FROM app.pets'
  );

  expect(pet.name).toBe('Buddy');
  expect(pet.species).toBe('dog');
  expect(pet.age).toBe(3);
});

it('starts with clean state', async () => {
  const { rows } = await pg.query<{ n: number }>(
    'SELECT count(*)::int AS n FROM app.pets'
  );
  expect(rows[0].n).toBe(0);
});

Run the test:

pnpm test

You should see:

 PASS  __tests__/pets.test.ts
  ✓ deployed the pgpm module into PGlite (900ms)
  ✓ can insert and query pets (14ms)
  ✓ starts with clean state (5ms)

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

What just happened? getConnections() booted an in-memory PGlite instance and deployed your module's migrations into it—the same deploy/*.sql you'd ship to a real server. No database existed a second before, and none survives after teardown().

Understanding the Pattern

The key call is:

const { pg, db, teardown } = await getConnections();

This gives you:

• pg – the superuser connection, used for setup, seeding, and transaction isolation
• db – the app-user connection, used for RLS testing with setContext() (next lesson)
• In-memory Postgres – a fresh database per run, no dataDir needed
• Transaction isolation – pg.beforeEach()/afterEach() roll back each test's writes

With no arguments, getConnections() runs in-memory and deploys the pgpm module in the current package. Pass a config object only when you need more—extensions, roles, or persistence.

In-memory vs. persistent: By default PGlite is in-memory. To persist between runs, pass a dataDir: getConnections({ pglite: { dataDir: './.pglite' } }).

What You've Accomplished

You now have a complete PGlite testing environment:

• A pgpm workspace pre-wired for PGlite via pgpm init workspace --pglite
• A module with schema managed by pgpm migrations
• pglite-test deploying real migrations into an in-process database
• Working tests with transaction isolation—no server, no Docker, no services

Key Takeaways

• pglite-test is a drop-in for pgsql-test: same API, but Postgres runs in-process—no server, no Docker
• pgpm init workspace --pglite scaffolds everything pre-configured; pgpm init inside it inherits the setting
• pgpm deploy runs in-process too—the workspace's engine: pglite means no server; add --pglite=./.pglite to persist, --engine pg to target a server
• getConnections() with no arguments boots an in-memory PGlite and deploys your module's migrations
• pg vs db: pg is the superuser (setup/seeding/isolation), db is the app user (RLS, next lesson)
• In-memory by default—pass dataDir only if you want persistence

What's Next

In the next lesson, we'll add Row-Level Security (RLS) policies and test them in PGlite by switching between user contexts—plus wire up a WASM extension (pgvector) for similarity search.