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.

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

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.

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
• 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.