# pglite-test

<p align="center" width="100%">
  <img height="250" src="https://raw.githubusercontent.com/constructive-io/constructive/refs/heads/main/assets/outline-logo.svg" />
</p>

<p align="center" width="100%">
  <a href="https://github.com/constructive-io/constructive/actions/workflows/run-tests.yaml">
    <img height="20" src="https://github.com/constructive-io/constructive/actions/workflows/run-tests.yaml/badge.svg" />
  </a>
  <a href="https://github.com/constructive-io/constructive/blob/main/LICENSE">
    <img height="20" src="https://img.shields.io/badge/license-MIT-blue.svg"/>
  </a>
  <a href="https://www.npmjs.com/package/pglite-test">
    <img height="20" src="https://img.shields.io/github/package-json/v/constructive-io/constructive?filename=postgres%2Fpglite-test%2Fpackage.json"/>
  </a>
</p>

`pglite-test` is a [**PGlite**](https://github.com/electric-sql/pglite)-optimized version of [`pgsql-test`](https://www.npmjs.com/package/pgsql-test) that runs entirely **in-process** — no Postgres server, no `createdb`, no `psql`, no TCP. It provides instant, isolated PostgreSQL databases for testing with automatic transaction rollbacks, context switching, and clean seeding, all backed by ElectricSQL's WASM build of Postgres. It's ideal for local-first development and especially great for GitHub Actions and CI/CD — **no service container required**.

Like [`supabase-test`](https://www.npmjs.com/package/supabase-test) and [`drizzle-orm-test`](https://www.npmjs.com/package/drizzle-orm-test), it's a thin `getConnections()` wrapper that composes the existing `pg-cache` / `pgsql-client` seams, so your tests read exactly like `pgsql-test`.

## Install

```sh
npm install pglite-test
```

You also install PGlite yourself (it's a peer dependency, so you pin the version):

```sh
npm install @electric-sql/pglite
```

## Features

* 🚀 **Zero infrastructure** — pure WASM, no Postgres server, no Docker, no service container in CI
* ⚡ **Instant test DBs** — spin up an isolated in-process instance per suite
* 🔄 **Per-test rollback** — every test runs in its own transaction/savepoint (ref-counted for the single session)
* 🛡️ **RLS-friendly** — role-based auth via `.setContext()` (full GUC support)
* 🌱 **pgpm-native seeding** — deploy your real modules with the unmodified pgpm engine via `seed.pgpm()`
* 🧠 **pgvector & friends** — register WASM extensions (`vector`, `pg_trgm`, …) at construction
* 🧪 **Compatible with any async runner** — works with `Jest`, `Mocha`, etc.
* 🧹 **Auto teardown** — no residue, no reboots, just clean exits

## How it works

- [`@pgpmjs/pglite-adapter`](https://www.npmjs.com/package/@pgpmjs/pglite-adapter)'s `registerPglite()` routes `pg-cache`'s `getPgPool()` at an in-process PGlite instance, so `seed.pgpm()` deploys your module into it with the **unmodified pgpm engine**.
- `pgsql-client`'s client-factory seam routes `PgTestClient`'s underlying `pg.Client` at the same PGlite session.

## Usage

```typescript
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();
  await db.beforeEach();
});

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

it('queries the pgpm-deployed schema', async () => {
  const { rows } = await db.query('SELECT count(*)::int AS n FROM app.users');
  expect(rows[0].n).toBe(0);
});
```

Jest must run with `NODE_OPTIONS=--experimental-vm-modules` (PGlite loads a WASM module); the package's `test` script sets this.

## Options

```typescript
import { vector } from '@electric-sql/pglite-pgvector';

await getConnections(
  {
    pglite: {
      dataDir: undefined,           // in-memory by default
      roles: true,                  // seed standard app roles (default) — see below
      extensions: { vector },       // WASM extensions (e.g. pglite-pgvector)
      extensionSql: [               // run once after ready
        'CREATE EXTENSION IF NOT EXISTS vector;'
      ]
    }
  },
  [seed.pgpm()]                     // default seed adapter
);
```

## Roles

On a server, `pgsql-test` bootstraps the standard app roles at `createdb`. PGlite
has no `createdb` — it boots as a lone superuser — so by default `getConnections()`
creates the same roles for you before seeding: `anonymous`, `authenticated`, and
`administrator` (with `BYPASSRLS`), using the same
attributes as the server bootstrap. That's why `db.setContext({ role: 'authenticated' })`
just works with no manual `CREATE ROLE`. Custom role *names* come from `db.roles`
(a `RoleMapping`), exactly like `pgsql-test`.

### Bring your own roles/users

Want a clean superuser-only instance and full control over your own roles? Opt
out with `pglite: { roles: false }` and create them in `extensionSql` (real
Postgres DDL — the same statements you'd run on a server):

```typescript
await getConnections({
  pglite: {
    roles: false, // skip the built-in bootstrap
    extensionSql: [
      "CREATE ROLE app_reader NOLOGIN;",
      "CREATE ROLE app_writer NOLOGIN;",
      // a login user, if a test needs one (no password needed in-process):
      "CREATE ROLE app_user LOGIN;",
      "GRANT app_writer TO app_user;"
    ]
  }
});
```

## Single-session model

PGlite is one in-process session, so `pg` and `db` share it. That differs from `pgsql-test` (two authenticated connections on a real server):

- Transaction control is **ref-counted** (`SharedTxn`) so the standard two-client `beforeEach`/`afterEach` harness emits exactly one `BEGIN`/`SAVEPOINT`/`ROLLBACK`/`COMMIT` per test. The single-client (`db` only) pattern also works.
- Role-based RLS uses `setContext({ role })` (i.e. `SET LOCAL role`) on the shared session rather than separate authenticated connections. The standard app roles are created for you by default (see [Roles](#roles)); any *extra* role you switch to must be created via `pglite.extensionSql`.
- `publish()` (commit-and-continue) is not supported under the shared-session coordinator.

## Related

- [`@pgpmjs/pglite-adapter`](https://www.npmjs.com/package/@pgpmjs/pglite-adapter) — the in-process PGlite driver that both this package and the pgpm engine ride on.
- [`pgsql-test`](https://www.npmjs.com/package/pgsql-test) — the server-backed original this mirrors.
- [`supabase-test`](https://www.npmjs.com/package/supabase-test) · [`drizzle-orm-test`](https://www.npmjs.com/package/drizzle-orm-test) — sibling `getConnections()` wrappers.

---

## Education and Tutorials

 1. 🚀 [Quickstart: Getting Up and Running](https://constructive.io/learn/quickstart)
Get started with modular databases in minutes. Install prerequisites and deploy your first module.

 2. 📦 [Modular PostgreSQL Development with Database Packages](https://constructive.io/learn/modular-postgres)
Learn to organize PostgreSQL projects with pgpm workspaces and reusable database modules.

 3. ✏️ [Authoring Database Changes](https://constructive.io/learn/authoring-database-changes)
Master the workflow for adding, organizing, and managing database changes with pgpm.

 4. 🧪 [End-to-End PostgreSQL Testing with TypeScript](https://constructive.io/learn/e2e-postgres-testing)
Master end-to-end PostgreSQL testing with ephemeral databases, RLS testing, and CI/CD automation.

 5. ⚡ [Supabase Testing](https://constructive.io/learn/supabase)
Use TypeScript-first tools to test Supabase projects with realistic RLS, policies, and auth contexts.

 6. 💧 [Drizzle ORM Testing](https://constructive.io/learn/drizzle-testing)
Run full-stack tests with Drizzle ORM, including database setup, teardown, and RLS enforcement.

 7. 🔧 [Troubleshooting](https://constructive.io/learn/troubleshooting)
Common issues and solutions for pgpm, PostgreSQL, and testing.

## Related Constructive Tooling

### 📦 Package Management

* [pgpm](https://github.com/constructive-io/constructive/tree/main/pgpm/pgpm): **🖥️ PostgreSQL Package Manager** for modular Postgres development. Works with database workspaces, scaffolding, migrations, seeding, and installing database packages.

### 🧪 Testing

* [pgsql-test](https://github.com/constructive-io/constructive/tree/main/postgres/pgsql-test): **📊 Isolated testing environments** with per-test transaction rollbacks—ideal for integration tests, complex migrations, and RLS simulation.
* [pglite-test](https://github.com/constructive-io/constructive/tree/main/postgres/pglite-test): **🪶 Drop-in pgsql-test replacement backed by PGlite** — in-process Postgres, no server required, instance-per-suite isolation.
* [pgsql-seed](https://github.com/constructive-io/constructive/tree/main/postgres/pgsql-seed): **🌱 PostgreSQL seeding utilities** for CSV, JSON, SQL data loading, and pgpm deployment.
* [supabase-test](https://github.com/constructive-io/constructive/tree/main/postgres/supabase-test): **🧪 Supabase-native test harness** preconfigured for the local Supabase stack—per-test rollbacks, JWT/role context helpers, and CI/GitHub Actions ready.
* [graphile-test](https://github.com/constructive-io/constructive/tree/main/graphile/graphile-test): **🔐 Authentication mocking** for Graphile-focused test helpers and emulating row-level security contexts.
* [pg-query-context](https://github.com/constructive-io/constructive/tree/main/postgres/pg-query-context): **🔒 Session context injection** to add session-local context (e.g., `SET LOCAL`) into queries—ideal for setting `role`, `jwt.claims`, and other session settings.

### 🧠 Parsing & AST

* [pgsql-parser](https://www.npmjs.com/package/pgsql-parser): **🔄 SQL conversion engine** that interprets and converts PostgreSQL syntax.
* [libpg-query-node](https://www.npmjs.com/package/libpg-query): **🌉 Node.js bindings** for `libpg_query`, converting SQL into parse trees.
* [pg-proto-parser](https://www.npmjs.com/package/pg-proto-parser): **📦 Protobuf parser** for parsing PostgreSQL Protocol Buffers definitions to generate TypeScript interfaces, utility functions, and JSON mappings for enums.
* [@pgsql/enums](https://www.npmjs.com/package/@pgsql/enums): **🏷️ TypeScript enums** for PostgreSQL AST for safe and ergonomic parsing logic.
* [@pgsql/types](https://www.npmjs.com/package/@pgsql/types): **📝 Type definitions** for PostgreSQL AST nodes in TypeScript.
* [@pgsql/utils](https://www.npmjs.com/package/@pgsql/utils): **🛠️ AST utilities** for constructing and transforming PostgreSQL syntax trees.

### 📚 Documentation & Skills

* [constructive-skills](https://github.com/constructive-io/constructive-skills): **📖 Platform documentation and AI agent skills** — feature catalog, blueprint reference, SDK guides (i18n, billing, limits, events, uploads, security, entities, search, AI), and deployment guides.

Install skills for AI coding agents:

```bash
# All platform skills (security, blueprints, codegen, billing, etc.)
npx skills add constructive-io/constructive-skills

# Individual repo skills (pgpm, testing, CLI, search, etc.)
npx skills add https://github.com/constructive-io/constructive --skill pgpm
npx skills add https://github.com/constructive-io/constructive --skill constructive-testing
```

## Credits

**🛠 Built by the [Constructive](https://constructive.io) team — creators of modular Postgres tooling for secure, composable backends. If you like our work, contribute on [GitHub](https://github.com/constructive-io).**

## Disclaimer

AS DESCRIBED IN THE LICENSES, THE SOFTWARE IS PROVIDED "AS IS", AT YOUR OWN RISK, AND WITHOUT WARRANTIES OF ANY KIND.

No developer or entity involved in creating this software will be liable for any claims or damages whatsoever associated with your use, inability to use, or your interaction with other users of the code, including any direct, indirect, incidental, special, exemplary, punitive or consequential damages, or loss of profits, cryptocurrencies, tokens, or anything else of value.