Getting Started

pg-schemata is an ESM-first Node.js package that provides a lightweight, PostgreSQL-first ORM layer built on pg-promise.

Requirements

• Node.js 20 or newer
• PostgreSQL 13 or newer — UUID primary keys default to the core gen_random_uuid(), which was added in 13, so no extension is required
• zod 4 or newer as a peer dependency
• pg-promise — a direct dependency, installed for you

Install

npm install pg-schemata zod

zod 4 is required

pg-schemata exchanges Zod objects with your code in both directions — colProps.validator in, _schema.validators out, and err instanceof ZodError — so both sides must resolve a single copy. It is declared as a peer dependency (^4.0.0), which means npm either hoists one copy or fails loudly with ERESOLVE rather than silently nesting a second one.

If your app is still on zod 3, upgrade it to zod 4 first, then pg-schemata.

Define a schema

A schema is a plain JavaScript object that describes your table — columns, types, constraints, and behavior flags.

const usersSchema = {
  dbSchema: 'public',
  table: 'users',
  hasAuditFields: true,
  softDelete: true,
  columns: [
    {
      name: 'id',
      type: 'uuid',
      default: 'gen_random_uuid()',
      immutable: true,
      colProps: { cnd: true },
    },
    { name: 'email', type: 'varchar(255)', notNull: true },
    { name: 'first_name', type: 'varchar(100)' },
    { name: 'last_name', type: 'varchar(100)' },
    { name: 'is_active', type: 'boolean', default: 'true', notNull: true },
  ],
  constraints: {
    primaryKey: ['id'],
    unique: [['email']],
  },
};

Create a model

Extend TableModel and pass the schema to super().

import { TableModel } from 'pg-schemata';

export class Users extends TableModel {
  constructor(db, pgp, logger = null) {
    super(db, pgp, usersSchema, logger);
  }
}

Connect the database

Use createDb() at startup with a connection and a repository map. Each repository is attached to that instance's database object via pg-promise's extend event, and the instance owns its own pool.

import { createDb } from 'pg-schemata';
import { Users } from './models/Users.js';

const appDb = createDb({
  connectionString: process.env.DATABASE_URL,
  repositories: { users: Users },
});

await appDb.connect();

const alice = await appDb.db.users.insert({
  email: 'alice@example.com',
  first_name: 'Alice',
});

await appDb.close(); // at shutdown

Need more than one database in the process — an admin database plus a cell, for example? Call createDb() once per handle; see createDb / Database. Your application owns which handle to use and where credentials come from.

The DB singleton

For a single-database application the original singleton still works and is now a documented default instance built by the same factory. DB.init() runs once at startup with a connection string and a repository map.

import { DB, db } from 'pg-schemata';
import { Users } from './models/Users.js';

const repositories = {
  users: Users,
};

DB.init(process.env.DATABASE_URL, repositories);

After initialization, access repositories through db():

const database = db();

// Create the table
await database.users.createTable();

// Insert a record
const alice = await database.users.insert({
  email: 'alice@example.com',
  first_name: 'Alice',
});

// Query records
const activeUsers = await database.users.findWhere(
  [{ is_active: true }],
  'AND',
  { orderBy: 'email' }
);

// Update a record
const updated = await database.users.update(alice.id, {
  last_name: 'Liddell',
});

// Soft delete
await database.users.removeWhere({ id: alice.id });

What to read next

• Schema Definition — understand the schema object in depth
• Models — learn about QueryModel vs TableModel
• CRUD Operations — the full insert/update/delete API