ProvenPin Install

Drizzle ORM migrations on PGlite (in-memory Postgres): working versions (tested 2026-10-15)

Apply versioned SQL migrations with Drizzle's PGlite migrator, use Postgres features (jsonb, enums, arrays, onConflictDoUpdate) and test against a fresh in-memory database.

passed3 passed · 0 failed2026-10-15 13:28 UTC
Status
passed
Last passed
2026-10-15 13:28 UTC
Tests
3 / 3
Language
TypeScript
Runtime
node v22.23.3
pinned versions4
drizzle-orm@0.45.4@electric-sql/pglite@0.5.10tsx@4.23.15@types/node@24.19.2
run it
npm install --no-audit --no-fund --ignore-scripts=false
npm test
test environment
node v22.23.3
linux 6.17.0-1022-azure
x64
runner: github-actions 38742087302

The code

Exactly the files that passed. Your agent can pull them with get_recipe("drizzle-pglite-migrations") (install), or see them on GitHub.

package.json
{
  "name": "drizzle-pglite-migrations",
  "private": true,
  "type": "module",
  "scripts": {
    "test": "node --import tsx --test test/*.test.ts"
  },
  "dependencies": {
    "drizzle-orm": "0.45.4",
    "@electric-sql/pglite": "0.5.10"
  },
  "devDependencies": {
    "tsx": "4.23.15",
    "@types/node": "24.19.2"
  }
}
drizzle/0000_init.sql
CREATE TYPE "plan" AS ENUM('free', 'pro');
--> statement-breakpoint
CREATE TABLE "accounts" (
  "id" serial PRIMARY KEY,
  "email" text NOT NULL UNIQUE,
  "plan" "plan" DEFAULT 'free' NOT NULL,
  "tags" text[] DEFAULT '{}' NOT NULL,
  "settings" jsonb DEFAULT '{}'::jsonb NOT NULL
);
drizzle/meta/_journal.json
{
  "version": "7",
  "dialect": "postgresql",
  "entries": [
    { "idx": 0, "version": "7", "when": 1760000000000, "tag": "0000_init", "breakpoints": true }
  ]
}
src/schema.ts
import { pgTable, pgEnum, serial, text, jsonb } from "drizzle-orm/pg-core";

export const plan = pgEnum("plan", ["free", "pro"]);
export const accounts = pgTable("accounts", {
  id: serial("id").primaryKey(),
  email: text("email").notNull().unique(),
  plan: plan("plan").notNull().default("free"),
  tags: text("tags").array().notNull().default([]),
  settings: jsonb("settings").$type<{ theme?: "dark" | "light"; beta?: boolean }>().notNull().default({}),
});
src/db.ts
import { PGlite } from "@electric-sql/pglite";
import { drizzle } from "drizzle-orm/pglite";
import { migrate } from "drizzle-orm/pglite/migrator";
import { arrayContains, sql } from "drizzle-orm";
import { accounts } from "./schema.ts";

export async function freshDb() {
  const db = drizzle(new PGlite());
  await migrate(db, { migrationsFolder: "./drizzle" }); // same folder drizzle-kit generate writes
  return db;
}
type Db = Awaited<ReturnType<typeof freshDb>>;

// Upsert: insert or upgrade plan and merge settings
export const upsertAccount = (db: Db, email: string, plan: "free" | "pro", settings: Record<string, unknown>) =>
  db.insert(accounts).values({ email, plan, settings }).onConflictDoUpdate({
    target: accounts.email,
    set: { plan, settings: sql`${accounts.settings} || ${JSON.stringify(settings)}::jsonb` },
  }).returning();

export const taggedWith = (db: Db, tag: string) => db.select().from(accounts).where(arrayContains(accounts.tags, [tag]));
test/db.test.ts
import { test } from "node:test";
import assert from "node:assert/strict";
import { eq, sql } from "drizzle-orm";
import { freshDb, taggedWith, upsertAccount } from "../src/db.ts";
import { accounts } from "../src/schema.ts";

test("migrations create enum, array and jsonb columns", async () => {
  const db = await freshDb();
  const [a] = await db.insert(accounts).values({ email: "[email protected]", tags: ["beta", "eu"] }).returning();
  assert.equal(a.plan, "free");
  assert.deepEqual(a.settings, {});
  assert.equal((await taggedWith(db, "beta")).length, 1);
  assert.equal((await taggedWith(db, "us")).length, 0);
});

test("upsert merges jsonb and updates enum", async () => {
  const db = await freshDb();
  await upsertAccount(db, "[email protected]", "free", { theme: "dark" });
  const [row] = await upsertAccount(db, "[email protected]", "pro", { beta: true });
  assert.equal(row.plan, "pro");
  assert.deepEqual(row.settings, { theme: "dark", beta: true });
});

test("enum rejects unknown values", async () => {
  const db = await freshDb();
  await assert.rejects(db.execute(sql`insert into accounts (email, plan) values ('[email protected]', 'gold')`));
  assert.equal((await db.select().from(accounts).where(eq(accounts.email, "[email protected]"))).length, 0);
});

Connect to ProvenPin

Add the free MCP server so your agent can pull this recipe and others like it. Same setup as the docs.

terminal
claude mcp add --transport http provenpin https://mcp.provenpin.com/mcp

Any other MCP client: Streamable HTTP at https://mcp.provenpin.com/mcp. No API key, no signup.

Related stacks

Drizzle ORM + PGlite in-memory Postgres

Real Postgres semantics in tests with no server: Drizzle ORM on PGlite (WASM Postgres) with relations, returning() and transactions.

Drizzle ORM · @electric-sql/pglite · postgres

Auth.js Drizzle adapter on PGlite Postgres (database sessions)

Set up @auth/drizzle-adapter with the standard Postgres tables on Drizzle, and test user, account linking and database session lifecycle against in-memory PGlite.

@auth/drizzle-adapter · @auth/core · Drizzle ORM · @electric-sql/pglite

Drizzle ORM + better-sqlite3 CRUD

Typed schema, migrations-free table creation and CRUD with Drizzle on an in-memory SQLite database.

Drizzle ORM · better-sqlite3 · SQLite

Drizzle ORM + drizzle-zod validated inserts on SQLite

Generate Zod insert and select schemas from Drizzle tables with drizzle-zod, refine them, and validate input before inserting into SQLite.

Drizzle ORM · drizzle-zod · Zod · better-sqlite3

All Drizzle ORM recipesAll @electric-sql/pglite recipesAll postgres recipes