@classytic/pgkit

PostgreSQL repository kit built on @classytic/repo-core and Drizzle ORM — the same cross-kit repository surface as @classytic/mongokit (Mongoose), @classytic/sqlitekit (Drizzle/SQLite), and @classytic/prismakit (Prisma).

One repository class gives you offset and keyset pagination, Filter/Update IR compilation, atomic CAS (claim / claimVersion / findOneAndUpdate — single-statement UPDATE … RETURNING, no read-modify-write), batch ops, native regex filters, transactions, duplicate-key classification, and compliance-grade tenant purge. One adapter call plugs it into @classytic/arc's defineResource() or any custom host.

Contract-proven: pgkit runs repo-core's cross-kit runStandardRepoConformance suite against a real Postgres 16 (PGlite) in CI — swap pgkit for any sibling kit and controller code keeps working.

Install

npm i @classytic/pgkit @classytic/repo-core drizzle-orm

| Peer | Range | Notes | |------|-------|-------| | @classytic/repo-core | >=0.7.0 | contract types + pagination/purge runtime | | drizzle-orm | >=0.44.0 | pg-core tables + any pg driver flavor |

Driver-agnostic: you hand pgkit a Drizzle Postgres database — drizzle-orm/node-postgres, drizzle-orm/pglite, drizzle-orm/neon-http (edge), drizzle-orm/aws-data-api — pgkit never picks a driver, so it runs anywhere Drizzle does, including edge runtimes with serverless drivers.

Quick start

import { drizzle } from 'drizzle-orm/node-postgres';
import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
import { createRepository } from '@classytic/pgkit';

const users = pgTable('users', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  status: text('status'),
  score: integer('score').notNull().default(0),
  createdAt: text('createdAt').notNull(),
});

const db = drizzle(pool);
const repo = createRepository<User>({ db, table: users, defaultSort: '-createdAt' });

// Canonical pagination envelopes (same shapes as every sibling kit)
const page = await repo.getAll({ page: 1, limit: 20 });            // offset
const feed = await repo.getAll({ sort: '-createdAt', limit: 20 }); // keyset
const next = await repo.getAll({ sort: '-createdAt', after: feed.next });

// Atomic CAS — one UPDATE ... RETURNING statement
await repo.claim(id, { from: 'pending', to: 'processing' });
await repo.claimVersion(id, { from: 3 }, { name: 'updated' });

// Portable Filter IR — including native Postgres regex
import { and, eq, gt, regex } from '@classytic/repo-core/filter';
const admins = await repo.findAll(and(eq('role', 'admin'), gt('score', 10)));
const widgets = await repo.findAll(regex('name', '^Widget-\\d+$'));

// Transactions with rollback
await repo.withTransaction(async (tx) => {
  await tx.create({ ... });
  await tx.updateMany({ status: 'stale' }, { status: 'archived' });
});

Subpath exports (tree-shake friendly — import what you use)

| Subpath | Contents | |---|---| | @classytic/pgkit | PgRepository, createRepository, PGKIT_CAPABILITIES, types | | @classytic/pgkit/adapter | createPgAdapter → DataAdapter for arc / custom hosts | | @classytic/pgkit/filter | compileFilterToDrizzle (Filter IR / Mongo subset / drizzle SQL) | | @classytic/pgkit/update | compileUpdateToPg (UpdateSpec IR / $set-$inc subset) | | @classytic/pgkit/schema | buildCrudSchemasFromTable — pg table → CRUD JSON Schemas | | @classytic/pgkit/better-auth | createBetterAuthOverlay — DataAdapter over Better-Auth pg tables |

Use with @classytic/arc

import { defineResource } from '@classytic/arc';
import { createRepository } from '@classytic/pgkit';
import { createPgAdapter } from '@classytic/pgkit/adapter';
import { buildCrudSchemasFromTable } from '@classytic/pgkit/schema';

export const products = defineResource({
  name: 'products',
  adapter: createPgAdapter({
    table: productsTable,
    repository: createRepository({ db, table: productsTable }),
    schemaGenerator: buildCrudSchemasFromTable,
  }),
  // auth, permissions, events, caching, OpenAPI, MCP — arc takes it from here
});

The adapter implements the DataAdapter surface arc feature-detects — generateSchemas (OpenAPI from Drizzle columns, with portable fieldRules merged), getSchemaMetadata, hasFieldPath (tenant-field inference), validate, healthCheck.

Capabilities

repo.capabilities (exported as PGKIT_CAPABILITIES) is the runtime feature-detection contract and the conformance-suite feature declaration — one source of truth:

transactions ✓ · upsert ✓ · duplicateKeyError ✓ (23505) · distinct ✓ · regexFilter ✓ (native ~ / ~*) · getOrCreate ✓ · countAndExists ✓ · purgeByField ✓ (chunked, abortable, retryable) · lean ✓ · aggregate ✗ (use Drizzle groupBy natively) · arrayOperators ✗ · changeStreams ✗

CAS semantics

Unlike kits whose backend lacks RETURNING, pgkit's claim / claimVersion compile to ONE UPDATE … WHERE id = $1 AND <guards> RETURNING * — the transition and the post-write read are the same statement. findOneAndUpdate over an arbitrary filter picks a candidate (with sort for FIFO queues), then re-applies the full filter in the write's WHERE — race losers match zero rows and retry.

Testing your app

PGlite makes real-Postgres tests trivial — no server, no containers:

import { PGlite } from '@electric-sql/pglite';
import { drizzle } from 'drizzle-orm/pglite';
import { createRepository } from '@classytic/pgkit';

const client = new PGlite();               // fresh in-process PG16
await client.exec(DDL);
const repo = createRepository({ db: drizzle(client), table: users });

See tests/_shared/pg-harness.ts for the exact pattern pgkit's own conformance run uses.

Development

npm test               # unit + integration (real Postgres via PGlite)
npm run typecheck      # tsc --noEmit (TypeScript 6)
npm run check          # biome ci
npm run build          # tsdown 0.22 → dist/

License

MIT