Auth.js Drizzle adapter on PGlite Postgres (database sessions): working versions (tested 2026-10-15)

{
  "name": "authjs-drizzle-adapter-pglite",
  "private": true,
  "type": "module",
  "scripts": {
    "test": "node --import tsx --test test/*.test.ts"
  },
  "dependencies": {
    "@auth/drizzle-adapter": "1.11.3",
    "@auth/core": "0.41.3",
    "drizzle-orm": "0.45.4",
    "@electric-sql/pglite": "0.5.10"
  },
  "devDependencies": {
    "tsx": "4.23.15",
    "@types/node": "24.19.2"
  }
}

import { pgTable, text, timestamp, integer, primaryKey } from "drizzle-orm/pg-core";
import type { AdapterAccountType } from "@auth/core/adapters";

// Table shapes from the Auth.js Drizzle adapter docs (Postgres)
export const users = pgTable("user", {
  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
  name: text("name"),
  email: text("email").unique(),
  emailVerified: timestamp("emailVerified", { mode: "date" }),
  image: text("image"),
});
export const accounts = pgTable("account", {
  userId: text("userId").notNull().references(() => users.id, { onDelete: "cascade" }),
  type: text("type").$type<AdapterAccountType>().notNull(),
  provider: text("provider").notNull(),
  providerAccountId: text("providerAccountId").notNull(),
  refresh_token: text("refresh_token"),
  access_token: text("access_token"),
  expires_at: integer("expires_at"),
  token_type: text("token_type"),
  scope: text("scope"),
  id_token: text("id_token"),
  session_state: text("session_state"),
}, (t) => [primaryKey({ columns: [t.provider, t.providerAccountId] })]);
export const sessions = pgTable("session", {
  sessionToken: text("sessionToken").primaryKey(),
  userId: text("userId").notNull().references(() => users.id, { onDelete: "cascade" }),
  expires: timestamp("expires", { mode: "date" }).notNull(),
});
export const verificationTokens = pgTable("verificationToken", {
  identifier: text("identifier").notNull(),
  token: text("token").notNull(),
  expires: timestamp("expires", { mode: "date" }).notNull(),
}, (t) => [primaryKey({ columns: [t.identifier, t.token] })]);

import { PGlite } from "@electric-sql/pglite";
import { drizzle } from "drizzle-orm/pglite";
import { DrizzleAdapter } from "@auth/drizzle-adapter";
import { accounts, sessions, users, verificationTokens } from "./schema.ts";

const DDL = `
create table "user" (id text primary key, name text, email text unique, "emailVerified" timestamp, image text);
create table account ("userId" text not null references "user"(id) on delete cascade, type text not null, provider text not null,
  "providerAccountId" text not null, refresh_token text, access_token text, expires_at integer, token_type text, scope text,
  id_token text, session_state text, primary key (provider, "providerAccountId"));
create table session ("sessionToken" text primary key, "userId" text not null references "user"(id) on delete cascade, expires timestamp not null);
create table "verificationToken" (identifier text not null, token text not null, expires timestamp not null, primary key (identifier, token));`;

export async function createAdapter() {
  const client = new PGlite();
  await client.exec(DDL); // use drizzle-kit migrations in a real app
  const db = drizzle(client);
  // pass this to NextAuth({ adapter, session: { strategy: "database" }, providers: [...] })
  return DrizzleAdapter(db, { usersTable: users, accountsTable: accounts, sessionsTable: sessions, verificationTokensTable: verificationTokens });
}

import { test } from "node:test";
import assert from "node:assert/strict";
import { createAdapter } from "../src/auth.ts";

test("user + linked OAuth account", async () => {
  const a = await createAdapter();
  const u = await a.createUser!({ id: crypto.randomUUID(), email: "ada@example.com", emailVerified: null, name: "Ada" });
  await a.linkAccount!({ userId: u.id, type: "oauth", provider: "github", providerAccountId: "gh_1", access_token: "tok" });
  assert.equal((await a.getUserByAccount!({ provider: "github", providerAccountId: "gh_1" }))?.email, "ada@example.com");
  assert.equal((await a.getUserByEmail!("ada@example.com"))?.id, u.id);
});

test("database session lifecycle", async () => {
  const a = await createAdapter();
  const u = await a.createUser!({ id: crypto.randomUUID(), email: "bo@example.com", emailVerified: null });
  await a.createSession!({ sessionToken: "s1", userId: u.id, expires: new Date(Date.now() + 3600_000) });
  assert.equal((await a.getSessionAndUser!("s1"))?.user.email, "bo@example.com");
  await a.deleteSession!("s1");
  assert.equal(await a.getSessionAndUser!("s1"), null);
});

test("email verification tokens are single-use", async () => {
  const a = await createAdapter();
  const vt = { identifier: "c@example.com", token: "hash", expires: new Date(Date.now() + 60_000) };
  await a.createVerificationToken!(vt);
  assert.equal((await a.useVerificationToken!({ identifier: vt.identifier, token: vt.token }))?.token, "hash");
  assert.equal(await a.useVerificationToken!({ identifier: vt.identifier, token: vt.token }), null);
});