pg-mem in-memory Postgres with Kysely for fast DB tests: working versions (tested 2026-10-15)

{
  "name": "pg-mem-kysely-postgres-tests",
  "private": true,
  "type": "module",
  "scripts": {
    "test": "node --import tsx --test test/*.test.ts"
  },
  "dependencies": {
    "kysely": "0.29.6"
  },
  "devDependencies": {
    "@types/node": "26.6.5",
    "tsx": "4.23.15",
    "pg-mem": "3.0.14"
  }
}

import { newDb, DataType } from "pg-mem";
import { Kysely, PostgresDialect, sql, type Generated } from "kysely";

export interface DB {
  courses: { id: Generated<number>; slug: string; title: string; hours: number; created_at: Generated<Date> };
  enrollments: { id: Generated<number>; course_id: number; email: string };
}

/** In-memory Postgres (pg-mem) wired to Kysely through pg-mem's node-postgres adapter. */
export function makeDb() {
  const mem = newDb();
  // Freeze now(): built-ins live in pg_catalog, so override there (public.registerFunction is shadowed).
  mem.getSchema("pg_catalog").registerFunction({ name: "now", returns: DataType.timestamptz, implementation: () => new Date("2026-10-15T20:00:00Z"), impure: true }, true);
  mem.public.none(`
    create table courses (id serial primary key, slug text not null unique, title text not null, hours int not null check (hours < 120), created_at timestamptz not null default now());
    create table enrollments (id serial primary key, course_id int not null references courses(id), email text not null, unique (course_id, email));
  `);
  const { Pool } = mem.adapters.createPg();
  const db = new Kysely<DB>({ dialect: new PostgresDialect({ pool: new Pool() }) });
  return { db, mem };
}

export async function addCourse(db: Kysely<DB>, slug: string, title: string, hours: number) {
  return db.insertInto("courses").values({ slug, title, hours }).returning(["id", "slug", "created_at"]).executeTakeFirstOrThrow();
}

/**
 * One multi-row INSERT is atomic on real Postgres and on pg-mem. Note: pg-mem does NOT undo
 * earlier statements when a multi-query transaction (BEGIN ... ROLLBACK over separate queries)
 * fails, so keep must-be-atomic writes in a single statement or use mem.backup() in tests.
 */
export async function enroll(db: Kysely<DB>, slug: string, emails: string[]) {
  const c = await db.selectFrom("courses").select("id").where("slug", "=", slug).executeTakeFirstOrThrow();
  const rows = await db.insertInto("enrollments").values(emails.map((email) => ({ course_id: c.id, email }))).returning("id").execute();
  return rows.length;
}

export async function enrollOneByOneInTransaction(db: Kysely<DB>, slug: string, emails: string[]) {
  return db.transaction().execute(async (trx) => {
    const c = await trx.selectFrom("courses").select("id").where("slug", "=", slug).executeTakeFirstOrThrow();
    for (const email of emails) await trx.insertInto("enrollments").values({ course_id: c.id, email }).execute();
    return emails.length;
  });
}

export async function roster(db: Kysely<DB>) {
  return db
    .selectFrom("courses as c")
    .leftJoin("enrollments as e", "e.course_id", "c.id")
    .select(["c.slug", sql<number>`count(e.id)::int`.as("students")])
    .groupBy("c.slug")
    .orderBy("c.slug")
    .execute();
}

import { test } from "node:test";
import assert from "node:assert/strict";
import { makeDb, addCourse, enroll, enrollOneByOneInTransaction, roster } from "../src/db.ts";

test("insert with returning, serial ids and default now()", async () => {
  const { db } = makeDb();
  const a = await addCourse(db, "cits", "CITS Orientation", 8);
  const b = await addCourse(db, "rigging", "Rigging", 16);
  assert.equal(a.id, 1);
  assert.equal(b.id, 2);
  assert.equal(new Date(a.created_at).toISOString(), "2026-10-15T20:00:00.000Z");
  await db.destroy();
});

test("constraints: unique and check", async () => {
  const { db } = makeDb();
  await addCourse(db, "cits", "CITS", 8);
  await assert.rejects(addCourse(db, "cits", "dup", 8), /duplicate key|unique/i);
  await assert.rejects(addCourse(db, "long", "Too long", 200), /check/i);
  await db.destroy();
});

test("multi-row insert is atomic: a duplicate rejects the whole batch", async () => {
  const { db } = makeDb();
  await addCourse(db, "cits", "CITS", 8);
  await assert.rejects(enroll(db, "cits", ["[email protected]", "[email protected]", "[email protected]"]), /duplicate key|unique/i);
  assert.deepEqual(await roster(db), [{ slug: "cits", students: 0 }]);
  assert.equal(await enroll(db, "cits", ["[email protected]", "[email protected]"]), 2);
  assert.deepEqual(await roster(db), [{ slug: "cits", students: 2 }]);
  await db.destroy();
});

test("pg-mem gotcha: Kysely transaction errors propagate but earlier statements are not rolled back", async () => {
  const { db, mem } = makeDb();
  await addCourse(db, "cits", "CITS", 8);
  const snap = mem.backup();
  await assert.rejects(enrollOneByOneInTransaction(db, "cits", ["[email protected]", "[email protected]"]), /duplicate key|unique/i);
  // On real Postgres this would be 0. pg-mem keeps the first row, so restore a snapshot instead.
  assert.deepEqual(await roster(db), [{ slug: "cits", students: 1 }]);
  snap.restore();
  assert.deepEqual(await roster(db), [{ slug: "cits", students: 0 }]);
  await db.destroy();
});

test("foreign keys enforced and left join aggregates", async () => {
  const { db } = makeDb();
  await addCourse(db, "cits", "CITS", 8);
  await addCourse(db, "forklift", "Forklift", 4);
  await enroll(db, "forklift", ["[email protected]"]);
  await assert.rejects(db.insertInto("enrollments").values({ course_id: 99, email: "[email protected]" }).execute(), /foreign key/i);
  assert.deepEqual(await roster(db), [{ slug: "cits", students: 0 }, { slug: "forklift", students: 1 }]);
  await db.destroy();
});

test("backup and restore snapshots for fast test isolation", async () => {
  const { db, mem } = makeDb();
  await addCourse(db, "cits", "CITS", 8);
  const snap = mem.backup();
  await addCourse(db, "extra", "Extra", 2);
  snap.restore();
  assert.deepEqual((await roster(db)).map((r) => r.slug), ["cits"]);
  await db.destroy();
});