Node.js basics

@pgmem/core starts the pgmem binary and hands you a PostgreSQL URL. The binary comes from a platform package that npm installs as an optional dependency; there is no postinstall script and nothing is downloaded at run time. pgmem does not replace your PostgreSQL client or ORM: Prisma, Drizzle, TypeORM, Kysely, pg and postgres connect to the URL as they would to any server, pools included.

Install

Install pgmem together with the official client you use.

npm install --save-dev @pgmem/core pg

pnpm add --save-dev @pgmem/core pg

yarn add --dev @pgmem/core pg

bun add --dev @pgmem/core pg

pg is node-postgres. postgres.js, Prisma, Drizzle and TypeORM work the same way; install whichever your application already uses. Node.js 20 or newer. Binary packages exist for Linux and macOS on x64 and arm64 and for Windows on x64 and arm64; elsewhere, point PGMEM_BINARY at a build of cmd/pgmem.

The basic flow

Start a server. PgmemServer.start() spawns the binary, runs prepare against the template database, and snapshots the result so forks start from it.
```
import { PgmemServer } from '@pgmem/core';

await using server = await PgmemServer.start({
  database: 'app',
  async prepare({ url }) {
    await migrate(url);
    await seed(url);
  },
});

console.log(server.url); // postgres://postgres@127.0.0.1:54321/app?sslmode=disable
```
await using closes the server at the end of the scope; call server.close() yourself where explicit resource management is not available. Other options are user, params (postgres settings as an object, such as { log_statement: 'all' }), maxForks, waitTimeoutMs, log and binary.
Register the schema in prepare. It receives the template server (url, host, port, user, database). See migration tools.
Load seed data, also in prepare. See seed data. Close or commit every connection prepare opens: the snapshot waits for open transactions.

Use it with the official clients.

import pg from 'pg';

const pool = new pg.Pool({ connectionString: server.url });
const { rows } = await pool.query('SELECT name FROM users WHERE id = $1', [1]);
await pool.end();

import postgres from 'postgres';

const sql = postgres(server.url);
const [user] = await sql`SELECT name FROM users WHERE id = ${1}`;
await sql.end();

import { drizzle } from 'drizzle-orm/node-postgres';

const db = drizzle(server.url);
const rows = await db.select().from(users);

import { PrismaPg } from '@prisma/adapter-pg';
import { PrismaClient } from '../generated/prisma/client';

const prisma = new PrismaClient({ adapter: new PrismaPg({ connectionString: server.url }) });
const user = await prisma.user.findUnique({ where: { id: 1 } });

Keep sslmode=disable in the URL. pg treats prefer and require as “TLS required”, and pgmem has no TLS.

Migration tools

Every tool below reads a URL. Run it inside prepare, so it runs once on the template.

import { readFile } from 'node:fs/promises';
import pg from 'pg';

async function migrate(url: string) {
  const client = new pg.Client({ connectionString: url });
  await client.connect();
  await client.query(await readFile('schema.sql', 'utf8'));
  await client.end();
}

node-postgres sends a query without parameters as one simple query, so several statements are fine.

import { execFileSync } from 'node:child_process';

function migrate(url: string) {
  execFileSync('npx', ['prisma', 'migrate', 'deploy'], {
    stdio: 'inherit',
    env: { ...process.env, DATABASE_URL: url },
    shell: process.platform === 'win32',
  });
}

prisma.config.ts reads the URL from the environment: datasource: { url: process.env.DATABASE_URL ?? '' }. prisma db push works the same way.

import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate as drizzleMigrate } from 'drizzle-orm/node-postgres/migrator';

async function migrate(url: string) {
  const db = drizzle(url);
  await drizzleMigrate(db, { migrationsFolder: 'drizzle' });
  await db.$client.end();
}

import { DataSource } from 'typeorm';

async function migrate(url: string) {
  const ds = await new DataSource({ ...options, url }).initialize();
  await ds.runMigrations();
  await ds.destroy();
}

import knex from 'knex';

async function migrate(url: string) {
  const db = knex({ client: 'pg', connection: url });
  await db.migrate.latest();
  await db.destroy();
}

Authoring migrations with `prisma migrate dev`

prisma migrate dev needs a shadow database. pgmem serves every database a server has, so Prisma creates the shadow database on the same in-memory server, and you can author migrations without a local PostgreSQL:

import { spawnSync } from 'node:child_process';
import { PgmemServer } from '@pgmem/core';

const server = await PgmemServer.start({ database: 'app', control: false });
try {
  const run = spawnSync('npx', ['prisma', 'migrate', 'dev', ...process.argv.slice(2)], {
    stdio: 'inherit',
    env: { ...process.env, DATABASE_URL: server.url },
    shell: process.platform === 'win32',
  });
  process.exitCode = run.status ?? 1;
} finally {
  await server.close();
}

node scripts/migrate-dev.mjs --name add_posts

Seed data

await client.query(await readFile('seed.sql', 'utf8'));

execFileSync('npx', ['prisma', 'db', 'seed'], {
  stdio: 'inherit',
  env: { ...process.env, DATABASE_URL: url },
  shell: process.platform === 'win32',
});

const db = drizzle(url);
await db.insert(users).values([
  { id: 1, name: 'Frank', email: 'frank@example.com' },
  { id: 2, name: 'Grace', email: 'grace@example.com' },
]);
await db.$client.end();

Forks by hand

await using fork = await server.fork();   // a private copy of the prepared database
await useDatabase(fork.url);

// or let pgmem close it
await server.withFork(async (fork) => {
  await useDatabase(fork.url);
});

// put a fork back to the snapshot without changing its URL
await fork.reset();

A fork waits for a free slot while maxForks forks are alive. Closing a fork leaves idle pooled connections to their pools, so a pool without an error listener does not crash the process. The next page shows how the test runners use this.