ORM API Guide

This is the complete reference for the @dbsp/core TypeScript API. It covers schema definition, ORM setup, query building, mutations, and observability — with progressive examples from a minimal first query to advanced patterns like set operations, CTEs, and streaming. Use it as your primary reference when building applications with @dbsp/core.

Related guides: NQL Reference | README

---

Quick Start

Go from zero to your first typed query in 3 steps.

Install

npm install @dbsp/core @dbsp/adapter-pgsql pg
# or
pnpm add @dbsp/core @dbsp/adapter-pgsql pg

Define Your Schema

import { schema, ref } from '@dbsp/core';

const db = schema({
  users: {
    id: { type: 'uuid', primaryKey: true },
    email: { type: 'text', unique: true },
    name: 'string',
    active: 'boolean',
  },
  posts: {
    id: { type: 'uuid', primaryKey: true },
    title: 'string',
    content: { type: 'text', nullable: true },
    published: 'boolean',
    authorId: ref('users'),
  },
});

Query with Full Type Safety

import { createOrm, schema, ref, eq } from '@dbsp/core';
import { createPgCompileOnlyAdapter } from '@dbsp/adapter-pgsql';

// Schema declares camelCase identifiers (idiomatic JS/TS).
const db = schema({
  users: {
    id: { type: 'uuid', primaryKey: true },
    email: 'string',
    active: 'boolean',
  },
  posts: {
    id: { type: 'uuid', primaryKey: true },
    title: 'string',
    authorId: ref('users'),      // camelCase FK in JS
    createdAt: 'timestamp',
  },
});

// dbCasing: 'snake_case' translates camelCase model identifiers to
// snake_case at the SQL boundary — no runtime column renaming needed.
const adapter = createPgCompileOnlyAdapter({ dbCasing: 'snake_case' });
const orm = createOrm({ schema: db, adapter });

// Type-safe query — code uses camelCase names; SQL uses snake_case columns.
const dump = orm
  .select('posts')
  .where(eq('authorId', '11111111-1111-1111-1111-111111111111'))
  .dump();

// Generated SQL uses snake_case for all translated identifiers:
//   SELECT "t0".* FROM "posts" AS "t0" WHERE "t0"."author_id" = $1
// (createdAt → "created_at", users.email → "email", etc.)
console.log(dump.sql);
console.log(dump.params); // ['11111111-1111-1111-1111-111111111111']

Type Inference Flow

Types flow automatically from your schema definition through the entire query pipeline:

schema({ users: { id: 'uuid', name: 'string' } })
  │
  ▼
Schema<T>  →  createOrm({ schema })  →  OrmInstance<DB>
                                            │
                                            ▼
                              orm.select('users')
                              orm.from(orm.tables.users)
                                      │
                                      ▼
                              QueryBuilder<{ id: string; name: string }>
                                                          │
                                                          ▼
                                                .all()  →  Promise<Array<{ id: string; name: string }>>

Column types map to TypeScript types:

Schema Type	TypeScript Type	Notes
'string', 'text', 'uuid'	string
'integer', 'decimal'	number
'bigint'	bigint
'boolean'	boolean
'date', 'timestamp'	Date
'time'	string
'json', 'jsonb'	unknown
{ type: T, nullable: true }	T | null

Without a Database (Compile-Only)

For testing, CLI tooling, or SQL preview — no pg.Pool needed:

import { createOrm, eq } from '@dbsp/core';
import { createPgCompileOnlyAdapter } from '@dbsp/adapter-pgsql';

const adapter = createPgCompileOnlyAdapter();
const orm = createOrm({ schema: db, adapter }); // db from schema() above

const dump = orm.select('users').where(eq('active', true)).dump();
console.log(dump.sql);    // SELECT "t0".* FROM "users" AS "t0" WHERE "t0"."active" = $1
console.log(dump.params); // [true]

---

1. Schema Definition

schema() — Define Your Database

import { schema, ref } from '@dbsp/core';

const db = schema({
  users: {
    id: { type: 'uuid', primaryKey: true },
    name: 'string',
    email: { type: 'string', unique: true },
    bio: { type: 'text', nullable: true },
    active: { type: 'boolean', default: 'true' },
    createdAt: { type: 'timestamp', default: 'now()' },
  },
  posts: {
    id: { type: 'integer', primaryKey: true, autoIncrement: true },
    title: 'string',
    content: { type: 'text', nullable: true },
    published: { type: 'boolean', default: 'false', index: true },
    authorId: ref('users', { onDelete: 'CASCADE', inverse: 'posts' }),
  },
});

Column Types

Type	PostgreSQL	TypeScript
'string'	VARCHAR	string
'text'	TEXT	string
'integer'	INTEGER	number
'bigint'	BIGINT	bigint
'decimal'	DECIMAL	number
'boolean'	BOOLEAN	boolean
'date'	DATE	Date
'time'	TIME	string
'timestamp'	TIMESTAMP WITH TIME ZONE	Date
'json'	JSON	unknown
'jsonb'	JSONB	unknown
'uuid'	UUID	string
'daterange'	DATERANGE	[Date, Date]
'tstzrange'	TSTZRANGE	[Date, Date]
'int4range'	INT4RANGE	[number, number]

Column Options

Shorthand (type only) or object with options:

// doctest: skip — illustrative data/type literal fragment (not executable code)
{
  name: 'string',                                        // shorthand
  email: { type: 'string', unique: true },               // with options
  bio: { type: 'text', nullable: true },                 // nullable
  id: { type: 'integer', primaryKey: true, autoIncrement: true },
  status: { type: 'string', default: 'active', index: true },
}

Option	Type	Description
type	ColumnType	Column data type (required in object form)
primaryKey	boolean	Mark as primary key
autoIncrement	boolean	Auto-increment (serial)
nullable	boolean	Allow NULL values
unique	boolean	Unique constraint
index	boolean	Create index
default	string	Default value expression

Relations with ref()

// doctest: skip — API signature reference (TypeScript function signature, not executable code)
ref(targetTable: string, options?: RefOptions)

Relations are auto-inferred from ref() calls. The planner detects:

• belongsTo (N:1) — the table with the FK
• hasMany (1:N) — the target table
• hasOne (1:1) — the inverse of a unique FK

Declare many-to-many relations explicitly with SchemaOptions.relations and manyToMany() (see below). The declaration retains both junction foreign keys and their referenced key vectors. Traversal remains provisional until the junction lowering lands.

const db = schema({
  users: { id: 'uuid', name: 'string', email: 'string', active: 'boolean', createdAt: 'timestamp' },
  categories: { id: 'uuid', name: 'string' },
  posts: {
    authorId: ref('users', { as: 'author', inverse: 'authoredPosts' }),  // basic FK
    editorId: ref('users', { as: 'editor', inverse: 'editedPosts', nullable: true }),  // optional relation
    categoryId: ref('categories', {
      onDelete: 'CASCADE',
      as: 'category',          // local relation name
      inverse: 'posts',        // reverse relation name on target
    }),
  },
});

ref() Options

Option	Type	Description
nullable	boolean	Optional relation (LEFT JOIN)
unique	boolean	Makes it 1:1 instead of 1:N
onDelete	'CASCADE' | 'SET NULL' | 'RESTRICT' | 'NO ACTION'	Delete action
onUpdate	'CASCADE' | 'SET NULL' | 'RESTRICT' | 'NO ACTION'	Update action
as	string	Local relation name override
inverse	string	Reverse relation name on target table
roles	SelfRefRoles	Role names for self-referential relations
columns	string[]	Source columns (composite FK)
references	string[]	Target columns (defaults to PK)

Self-Referential Relations

For trees/hierarchies, use roles to name the traversal paths:

const db = schema({
  employees: {
    id: { type: 'integer', primaryKey: true, autoIncrement: true },
    name: 'string',
    managerId: ref('employees', {
      nullable: true,
      roles: {
        parent: 'manager',            // direct parent
        children: 'directReports',    // direct children
        ancestors: 'managementChain', // recursive upward (CTE)
        descendants: 'allReports',    // recursive downward (CTE)
      },
    }),
  },
});

Many-to-Many (Junction Tables)

import { schema, ref, manyToMany } from '@dbsp/core';
const db = schema({
  posts: { id: 'integer', title: 'string' },
  tags: { id: 'integer', name: 'string' },
  postTags: { postId: ref('posts'), tagId: ref('tags') },
}, undefined, {
  relations: {
    posts: {
      tags: manyToMany('tags', {
        through: 'postTags',
        sourceForeignKey: ['postId'],
        targetForeignKey: ['tagId'],
        inverse: 'posts',
      }),
    },
  },
});

This declares posts.tags and tags.posts. Composite foreign keys use ordered column lists matching declared junction foreign keys. The junction must have a primary key, unique constraint or B-tree unique index covered by the two foreign key lists; the inferred junction primary key satisfies this requirement. Names must not collide with columns or existing relations.

Traversal of a declared many-to-many relation is refused when planning one of the query's includes (any strategy, including NQL relation columns and | flat) or a relation .join(); the message contains Relation 'posts.tags': many-to-many traversal is not supported yet (#787). Relation predicates and their includes (exists(..., { include })) are refused with a message ending in many-to-many traversal is not supported yet (#787).

Schema Options (dbCasing)

Control how column names map between JS and database:

const db = schema({
  users: {
    firstName: 'string',  // JS: camelCase
    lastName: 'string',
  },
}, undefined, { dbCasing: 'snake_case' });
// DB columns: first_name, last_name
// JS properties: firstName, lastName

Value	DB Columns	JS Properties	Transform
'snake_case'	first_name	firstName	Auto camelCase <-> snake_case
'camelCase'	firstName	firstName	No transform
'preserve'	as-is	as-is	No transform

Schema Options (SchemaOptions)

The third argument to schema() accepts a SchemaOptions object that controls schema-wide DSL interpretation:

// doctest: skip — signature reference
schema(definition, constraints?, options?, extras?)

Option	Type	Default	Description
defaultFilters	Record<string, WhereIntent>	—	A default filter is a helper-built condition over its table’s own range. Self-qualified columns are allowed; anything reaching another range, including a self-relation, is refused. Structurally marked aggregate expressions and aggregate modifiers (FILTER, ORDER BY, DISTINCT) are refused at schema() construction. A generic fn('count', ...) is not identifiable as an aggregate without catalog information and is left to PostgreSQL. Context-only expression leaves are refused. Filters apply to every table scan of a read. Opt out with a builder’s withoutDefaultFilters() or orm.withoutDefaultFilters(). Mutations and raw SQL are unfiltered. Many-to-many junction tables (#787), NQL pseudo-column projections, binding relation-column projections and relation reads from projected CTEs refuse applicable filters by table and query path.
defaultPkColumnName	string | null	'id'	Column name treated as the implicit primary key for short-form columns. Set to null to disable the convention; pass a custom string for project-specific naming schemes. Empty or whitespace-only strings are rejected eagerly at schema() time.

Example: implicit-PK convention

// Default: 'id' is treated as PK when no explicit primaryKey: true is set
const db = schema({
  users: { id: 'uuid' }, // 'id' becomes PK via convention
});

// Custom convention: 'pk_uuid' is the implicit PK name
const db2 = schema(
  { users: { pk_uuid: 'uuid' } },
  undefined,
  { defaultPkColumnName: 'pk_uuid' }
);

// Strict mode: no implicit PK convention — every PK must be explicit
const db3 = schema(
  { users: { id: { type: 'uuid', primaryKey: true } } },
  undefined,
  { defaultPkColumnName: null }
);

Resolution order

When inferring the primary key for a table, schema() checks in this order:

1. Explicit primaryKey: true on a column
2. Column matching defaultPkColumnName (the implicit convention) — skipped when set to null
3. FK columns (composite, for junction tables — applies regardless of defaultPkColumnName)
4. No primary key

Schema Constraints

Add composite indexes and foreign keys via the constraints parameter:

const db = schema(
  {
    orders: { id: 'uuid', total: 'integer' },
    products: { id: 'uuid', name: 'string' },
    orderItems: { orderId: ref('orders'), productId: ref('products'), quantity: 'integer' },
  },
  { orderItems: { indexes: [{ columns: ['orderId', 'productId'], unique: true }] } }
);

---

2. Creating the ORM

createOrm() — ORM Instance

import { createOrm } from '@dbsp/core';
import { createPgAdapter } from '@dbsp/adapter-pgsql';
import { Pool } from 'pg';

const orm = createOrm({
  schema: db,
  adapter: createPgAdapter(new Pool({ connectionString: process.env.DATABASE_URL })),
});

Options

Option	Type	Default	Description
schema	Schema	-	From schema() (preferred)
model	ModelIR	-	Direct ModelIR (alternative to schema)
adapter	Adapter	-	Database adapter; use a connectionless PgAdapter for compile-only work
strictMode	boolean	false	Throw on ambiguous relations
maxDepth	number	10	Max recursive depth
maxTableHops	number	5	Max relation hops

Compile-Only Mode (No Database)

For tooling, testing, or SQL preview without a database connection:

import { createPgCompileOnlyAdapter } from '@dbsp/adapter-pgsql';

const orm = createOrm({
  schema: db,
  adapter: createPgCompileOnlyAdapter(),
});

// dump() works — returns SQL + params
const { sql, params } = orm.select('users').where(eq('active', true)).dump();

// execute() is callable, but refuses at runtime until the adapter has a connection.

Schema Scoping (Multi-Tenant)

const tenantOrm = orm.withSchema('tenant_123');
const users = await tenantOrm.select('users').dump();
// SQL: SELECT * FROM "tenant_123"."users"

Transactions

// doctest: skip — exec-only operation; uses orders/orderItems tables not in default preamble schema
const result = await orm.transaction(async (tx) => {
  await tx.insert('orders').values({ customerId: 1, total: 99 }).dump();
  await tx.insert('orderItems').values({ orderId: 1, productId: 5 }).dump();
  return tx.select('orders').where(eq('customerId', 1)).dump();
});
// Auto-commit on success, auto-rollback on error

The callback receives a transaction-scoped ORM instance (tx) with the full ORM API. The transaction result is the return value of your callback:

// doctest: skip — exec-only operation; uses orders table not in default preamble schema
// Typed return value
const order = await orm.transaction(async (tx) => {
  const [created] = await tx.insert('orders')
    .values({ customerId: 1, total: 99 })
    .returning(['id', 'total'])
    .dump();
  return created; // { id: 42, total: 99 }
});
console.log(order.id); // 42

// Schema-scoped transactions (multi-tenant)
orm.withSchema('tenant_42').transaction(async (tx) => {
  // tx is scoped to 'tenant_42' schema
  await tx.insert('events').values({ type: 'signup' }).dump();
});

A nested transaction is a real savepoint. It does not re-BEGIN, but it does take a SAVEPOINT with an unguessable name, so a failure inside it can be caught and survived without losing the parent's work. It must be awaited — dbsp refuses an unobserved child before the parent commits, even one that already succeeded. See Transactions for the full contract.

// doctest: skip — exec-only operation; uses logs table not in default preamble schema
orm.transaction(async (outer) => {
  await outer.insert('users').values({ name: 'Alice' }).dump();

  // Awaited, because dbsp refuses an unobserved child — even a successful one.
  await outer.transaction(async (inner) => {
    // Same connection, but a real SAVEPOINT: this can fail without taking the
    // outer insert with it.
    await inner.insert('logs').values({ action: 'user_created' }).dump();
  });
});

Behavior	Detail
Success	COMMIT after callback returns
Error thrown	ROLLBACK, error re-thrown
Nested call	Real SAVEPOINT — an inner failure can be survived. Must be awaited.
Connection	Dedicated client, released on completion

---

3. Querying (QueryBuilder)

All query builder methods return a new immutable instance. Safe to branch and reuse.

select() — Start a Query

select(name) is the ordinary table-name entry point. It is concise and typed by table name when the ORM is created from a typed schema. Use from(orm.tables.<table>) when you want the stricter TableRef form with column-level types.

const users = await orm.select('users').dump();

The same query using both forms:

const activeUsersQuery = await orm
  .select('users')
  .where(eq('active', true))
  .dump();

const { users } = orm.tables;
const activeUsersFromRefQuery = await orm
  .from(users)
  .where(eq(users.active, true))
  .dump();

from() - Start a Query from a TableRef

from(tableRef) starts from a table reference in orm.tables. It returns the same query builder surface as select(name), while carrying column-level types into filters and result inference.

const { users } = orm.tables;

const aliceQuery = await orm
  .from(users)
  .where(eq(users.email, 'alice@example.com'))
  .columns(['id', 'name'])
  .dump();

columns() — Select Specific Columns

const names = await orm.select('users').columns(['id', 'name']).dump();
// SQL: SELECT "id", "name" FROM "users"

distinct() — Remove Duplicates

// doctest: real-db-only — requires a live PostgreSQL connection
const distinctNames = await orm.select('users').columns(['name']).distinct().all();
// SQL: SELECT DISTINCT "name" FROM "users"

distinctOn() — PostgreSQL DISTINCT ON

Use distinctOn() for PostgreSQL's DISTINCT ON (...). Plain columns and relation-qualified columns are both supported. Relation paths such as users.email are resolved through the planner's final relation alias, so alias bumps from joins/includes do not point at the wrong table.

const latestPostByAuthor = orm.select('posts')
  .include('users', { join: 'inner' })
  .distinctOn('users.email')
  .columns(['id', 'title'])
  .dump();

console.log(latestPostByAuthor.sql);

where() — Filter Rows

Uses filter helpers (see full list below):

import { eq, gt, and, or, not, like, inArray, isNull, isNotNull } from '@dbsp/core';

// Simple equality
orm.select('users').where(eq('active', true))

// Multiple conditions
orm.select('users').where(and(
  eq('active', true),
  gt('age', 18),
  like('email', '%@example.com')
))

// OR conditions
orm.select('users').where(or(
  eq('role', 'admin'),
  eq('role', 'superadmin')
))

// NOT
orm.select('users').where(not(eq('deleted', true)))

// IN array
orm.select('users').where(inArray('status', ['active', 'pending']))

// NULL checks
orm.select('users').where(isNull('deletedAt'))
orm.select('users').where(isNotNull('email'))

Filter Helpers — Complete List

Helper	SQL	Example
eq(field, value)	=	eq('status', 'active')
neq(field, value)	!=	neq('role', 'guest')
gt(field, value)	>	gt('age', 18)
gte(field, value)	>=	gte('price', 100)
lt(field, value)	<	lt('stock', 10)
lte(field, value)	<=	lte('rating', 5)
like(field, pattern)	LIKE	like('name', 'A%')
inArray(field, values)	IN	inArray('id', [1, 2, 3])
isNull(field)	IS NULL	isNull('deletedAt')
isNotNull(field)	IS NOT NULL	isNotNull('email')
isDistinctFrom(field, value)	IS DISTINCT FROM	isDistinctFrom('status', 'active')
and(...conditions)	AND	and(eq('a', 1), gt('b', 2))
or(...conditions)	OR	or(eq('x', 1), eq('x', 2))
not(condition)	NOT	not(eq('deleted', true))

isDistinctFrom() — Null-safe Inequality

Unlike neq(), returns true when one side is NULL and the other is not. Standard SQL (SQL:2003).

// doctest: skip — block uses an isDistinctFrom WHERE handler not registered in the doctest adapter (compile-time, not exec-time)
import { isDistinctFrom } from '@dbsp/core';

// Find rows where status changed (NULL-safe)
const changed = await orm.select('users')
  .where(isDistinctFrom('status', 'active'))
  .dump();
// SQL: WHERE status IS DISTINCT FROM $1

Relation Filters

Filter by related records without loading them:

import { Pool } from 'pg';
import { schema, ref, createOrm, eq, exists, notExists, some, every, none } from '@dbsp/core';
import { createPgAdapter } from '@dbsp/adapter-pgsql';

const __db = schema({
  users: { id: 'integer', name: 'string' },
  posts: { id: 'integer', userId: ref('users'), published: 'boolean' },
} as const);
const __pool = new Pool({ connectionString: process.env.DATABASE_URL });
const __orm = createOrm({ schema: __db, adapter: createPgAdapter(__pool) });

// Users who have at least one post
__orm.select('users').where(exists('posts'))

// Users who have no posts
__orm.select('users').where(notExists('posts'))

// Users who have at least one published post — some() requires a RelationRef
__orm.select('users').where(some(__db.tables.users.posts, (p) => eq(p.published, true)))

// Users where ALL posts are published
__orm.select('users').where(every(__db.tables.users.posts, (p) => eq(p.published, true)))

// Users where NO post is a draft (false = not draft)
__orm.select('users').where(none(__db.tables.users.posts, (p) => eq(p.published, false)))

Range Operators (PostgreSQL)

import { schema, createOrm, rangeOverlaps, rangeContains, rangeContainedBy } from '@dbsp/core';
import { createPgCompileOnlyAdapter } from '@dbsp/adapter-pgsql';

const __rangeDb = schema({
  bookings: { id: 'integer', period: 'daterange' },
  events: { id: 'integer', dateRange: 'daterange' },
} as const);
const __rangeOrm = createOrm({ schema: __rangeDb, adapter: createPgCompileOnlyAdapter() });

// Bookings that overlap a date range
__rangeOrm.select('bookings').where(rangeOverlaps('period', ['2024-01-01', '2024-01-31'])).dump();

// Events that contain a specific date
__rangeOrm.select('events').where(rangeContains('dateRange', ['2024-06-15', '2024-06-15'])).dump();

// Events within a year
__rangeOrm.select('events').where(rangeContainedBy('dateRange', ['2024-01-01', '2024-12-31'])).dump();

orderBy() — Sort Results

// Single field (ascending by default)
orm.select('users').orderBy('name')

// With direction
orm.select('users').orderBy('createdAt', 'desc')

// Multiple fields (object syntax)
orm.select('users').orderBy({ createdAt: 'desc', name: 'asc' })

// Advanced: nulls positioning
orm.select('users').orderBy([
  { column: 'createdAt', direction: 'desc', nulls: 'last' }
])

limit() + offset() — Pagination Primitives

orm.select('posts').orderBy('createdAt', 'desc').limit(10).offset(20)

Aggregates

// doctest: skip — illustrative fragment uses orders/products tables not in the default schema
import { distinct } from '@dbsp/core';

// COUNT
orm.select('users').count()
orm.select('orders').count('id', 'totalOrders')
orm.select('orders').count(distinct('customerId'), 'uniqueCustomers')

// SUM, AVG, MIN, MAX
orm.select('orders').sum('amount', 'totalRevenue')
orm.select('orders').avg('amount', 'averageOrder')
orm.select('products').min('price', 'cheapest')
orm.select('products').max('price', 'mostExpensive')

groupBy() + having()

orm.select('posts')
  .groupBy(['published'])
  .count('id', 'postCount')
  .having(gt('postCount', 10))
  .dump()
// SQL: SELECT posts.published, count(posts.id) AS "postCount" FROM posts
//      GROUP BY posts.published HAVING count(posts.id) > $1

Window Functions

// doctest: skip — illustrative fragment: queries products, employees, orders, prices and sales, which are not in the default schema
import { rowNumber, rank, denseRank, wSum, wAvg, wCount, wMin, wMax, lag, lead } from '@dbsp/core';

// Row numbering
orm.select('posts').columns([
  'title', 'authorId',
  rowNumber().orderBy('createdAt', 'desc').as('rn')
])

// Ranking within partitions
orm.select('products').columns([
  'name', 'category', 'price',
  rank().partitionBy('category').orderBy('price').as('priceRank')
])

// Dense rank (no gaps)
orm.select('employees').columns([
  'name', 'department', 'salary',
  denseRank().partitionBy('department').orderBy('salary', 'desc').as('salaryRank')
])

// Running total
orm.select('orders').columns([
  'date', 'amount',
  wSum('amount').orderBy('date').as('runningTotal')
])

// Previous/next row values
orm.select('prices').columns([
  'date', 'price',
  lag('price').orderBy('date').as('prevPrice'),
  lead('price').orderBy('date').as('nextPrice')
])

// Aggregate windows
orm.select('sales').columns([
  'customerId', 'amount',
  wAvg('amount').partitionBy('customerId').as('avgPerCustomer'),
  wCount('id').partitionBy('customerId').as('ordersPerCustomer'),
  wMin('amount').partitionBy('customerId').as('minOrder'),
  wMax('amount').partitionBy('customerId').as('maxOrder')
])

Expressions

// doctest: skip — illustrative fragment: uses columns nickname and firstName and an author relation that are not in the default schema
import { coalesce, raw, col, relationColumn } from '@dbsp/core';

// COALESCE — first non-null value
orm.select('users').columns([
  'id',
  coalesce(['nickname', 'name'], 'displayName')
])
// SQL: SELECT "id", COALESCE("nickname", "name") AS "displayName"

// raw() — SQL escape hatch
orm.select('users').columns([
  'id',
  raw('EXTRACT(YEAR FROM "created_at")', 'joinYear')
])

// col() — aliased column
orm.select('users').columns([col('firstName', 'first')])

// relationColumn() — names a relation whose JOIN this query already emitted.
// Without one, compilation reports:
// relation column "author"."name" has no emitted alias in this query
orm.select('products')
  .join('category')
  .columns(['name', relationColumn('category', 'name', 'categoryName')])

// An include that explicitly emits an outer join supplies the alias too.
// A bare include aggregates the relation instead, so the column lands inside
// `category_json` rather than under `categoryName` (#793).
orm.select('products')
  .include('category', { join: 'left' })
  .columns(['name', relationColumn('category', 'name', 'categoryName')])

// relationColumn() does not derive joins: their type, null behavior, and row
// multiplication remain a caller-owned query decision.

Subqueries

// doctest: skip — exec-only operation; requires a real PostgreSQL connection (products table not in default preamble schema)
import { subquery, outerRef } from '@dbsp/core';

// Correlated subquery: products with above-average price in their category
orm.select('products')
  .where(gt('price',
    subquery('products')
      .select('avgPrice')
      .where(eq('categoryId', outerRef('categoryId')))
      .avg('price')
  ))
  .all()

inSubquery() — WHERE IN (Subquery)

Filter rows where a column value exists in the result of a subquery.

import { inSubquery, subquery, eq } from '@dbsp/core';

// Users who have published posts
const authors = await orm.select('users')
  .where(inSubquery('id', subquery('posts').select('authorId').where(eq('published', true))))
  .dump();
// SQL: WHERE id = ANY(SELECT author_id FROM posts WHERE published = $1)

Scalar Subquery in SELECT — .asExpr()

Use a subquery as a computed column in SELECT.

// doctest: skip — exec-only (uses `await orm.select(...).all()`); see compile-only `.asExpr()` example near the bottom of this file for a runnable variant
import { subquery, eq } from '@dbsp/core';

// Count posts per user as a computed column
const users = await orm.select('users')
  .columns([
    'id',
    'name',
    subquery('posts').count().where(eq('authorId', outerRef('id'))).asExpr('post_count')
  ])
  .all();
// SQL: SELECT id, name, (SELECT count(*) FROM posts WHERE author_id = users.id) AS post_count FROM users

Set Operations

Combine query results with UNION, INTERSECT, or EXCEPT. All variants support the All suffix (e.g., .unionAll()) to preserve duplicates.

const q1 = orm.select('users').where(eq('active', true));
const q2 = orm.select('users').where(eq('email', 'alice@example.com'));
const q3 = orm.select('users').where(eq('active', false));

// UNION (deduplicated)
const activeOrAlice = q1.union(q2).dump();

// UNION ALL (with duplicates)
const allRows = q1.unionAll(q2).dump();

// INTERSECT
const activeAlice = q1.intersect(q2).dump();

// EXCEPT
const activeExceptAlice = q1.except(q2).dump();

// Chaining
const result = q1.union(q2).except(q3).dump();

// Dump for SQL preview
const dump = q1.union(q2).dump();
console.log(dump.sql); // (SELECT ...) UNION (SELECT ...)

Returns a SetOperationBuilder with .all(), .first(), and .dump() methods.

---

4. Includes (Eager Loading)

Simple Include

const usersWithPosts = await orm.select('users').include('posts').dump();
// [{ id: 1, name: 'Alice', posts: [{ id: 1, title: '...' }, ...] }]

Dot Notation (Deep Nesting)

orm.select('users').include('posts.comments')
orm.select('users').include('posts.comments.author')

Multiple Includes

orm.select('users')
  .include('posts')
  .include('profile')
  .include('posts.comments')
  .dump()

Include Options

orm.select('users').include('posts', {
  join: 'inner',
  where: eq('published', true),                          // root WHERE predicate on a join include
  select: { type: 'fields', fields: ['title', 'slug'] }, // select specific columns
  via: 'authoredPosts',                                  // disambiguate relation
})

Option	Type	Description
join	'inner' | 'left'	Join type
where	WhereIntent	Added to the root WHERE; join includes only, other strategies refuse it (#892)
select	SelectSpec	Select specific columns
via	string	Disambiguate multiple relations to same table
recursive	boolean	Enable recursive CTE traversal
direction	'ancestors' | 'descendants'	Traversal direction (required when recursive)
flat	boolean	Flat array output with depth field
maxDepth	number	Maximum traversal depth (default: 100)

Recursive Includes (Hierarchies)

// Ancestors (up the tree)
orm.select('categories')
  .where(eq('id', 5))
  .include('parent', { recursive: true, direction: 'ancestors' })
  .dump()

// Descendants (down the tree) — flat output
orm.select('categories')
  .where(eq('id', 1))
  .include('children', {
    recursive: true,
    direction: 'descendants',
    flat: true,
    maxDepth: 10
  })
  .dump()

---

5. Mutations

Insert

// Basic insert
orm.insert('users')
  .values({ name: 'Alice', email: 'alice@example.com' })
  .dump();

// Bulk insert
orm.insert('users')
  .values([
    { name: 'Alice', email: 'alice@example.com' },
    { name: 'Bob', email: 'bob@example.com' },
  ])
  .dump();

// With RETURNING
const { sql: newUserSql } = orm.insert('users')
  .values({ name: 'Alice', email: 'alice@example.com' })
  .returning(['id', 'name', 'createdAt'])
  .dump();

Update

// Update with WHERE (required)
orm.update('users')
  .set({ name: 'Alice Smith' })
  .where(eq('id', 1))
  .dump();

// Update all rows (explicit intent)
orm.updateAll('users')
  .set({ active: false })
  .dump();

// With RETURNING
const updated = await orm.update('users')
  .set({ active: true })
  .where(eq('email', 'alice@example.com'))
  .returning(['id', 'name', 'active'])
  .dump();

Delete

// Delete with WHERE (required)
orm.delete('users')
  .where(eq('id', 1))
  .dump();

// Delete all rows (explicit intent)
orm.deleteAll('users').dump();

// With cascade
orm.delete('users')
  .where(eq('id', 1))
  .cascade()           // cascade to all relations
  .dump();

// With RETURNING
const deleted = await orm.delete('posts')
  .where(eq('published', false))
  .returning(['id', 'title'])
  .dump();

Upsert (Insert or Update on Conflict)

For .onConflictConstraint(name), the constraint must be declared on the target table; it is then resolved to its physical database name. An undeclared name is rejected.

// On conflict by columns — auto-update non-conflict fields
orm.upsert('users')
  .values({ name: 'Alice', email: 'alice@example.com', active: true })
  .onConflict(['email'])
  .doUpdate()
  .dump();

// On conflict — update specific fields
orm.upsert('users')
  .values({ name: 'Alice', email: 'alice@example.com', active: true })
  .onConflict(['email'])
  .doUpdate({ name: 'Alice Updated', active: true })
  .dump();

// On conflict — update only when the existing row matches a predicate
orm.upsert('users')
  .values({ name: 'Alice', email: 'alice@example.com', active: true })
  .onConflict(['email'])
  .doUpdate({ name: 'Alice Updated' }, eq('active', true))
  .dump();

// On conflict — update only when a related row exists
const guarded = orm.upsert('users')
  .values({
    id: 'user-1',
    name: 'Alice',
    email: 'alice@example.com',
    active: true,
  })
  .onConflict(['email'])
  .doUpdate(
    { active: true },
    exists('posts', { where: eq('published', true) }),
  )
  .dump();

console.log(guarded.sql);
console.log(guarded.parameters);

// On conflict by columns
orm.upsert('users')
  .values({ name: 'Alice', email: 'alice@example.com' })
  .onConflict(['email'])
  .doNothing()
  .dump();

// With RETURNING
const result = await orm.upsert('users')
  .values({ name: 'Alice', email: 'alice@example.com' })
  .onConflict(['email'])
  .doUpdate()
  .returning(['id', 'name'])
  .dump();

Mutation Observability

All mutation builders support dump():

const { sql, params } = orm.insert('users')
  .values({ name: 'Alice', email: 'alice@example.com' })
  .dump();

console.log(sql);    // INSERT INTO "users" ("name", "email") VALUES ($1, $2)
console.log(params); // ['Alice', 'alice@example.com']

---

6. Execution

Result Methods

Method	Returns	Description
all()	Promise<T[]>	All matching rows
execute()	Promise<T[]>	Alias for all()
first()	Promise<T | undefined>	First row or undefined
firstOrThrow()	Promise<T>	First row or throws NotFoundError
byId(value)	Promise<T | undefined>	Find by primary key
byIdOrThrow(value)	Promise<T>	Find by PK or throws NotFoundError
byIds(values)	Promise<T[]>	Find multiple by PKs
exists()	Promise<boolean>	Check if any rows match (optimized)

// Standard execution
const dump1 = orm.select('users').dump();
const dump2 = orm.select('users').where(eq('id', 1)).dump();

// Primary key shortcuts — these require real DB connection
// const user = await orm.select('users').byId(1);
// const user = await orm.select('users').byIdOrThrow(1);
// const users = await orm.select('users').byIds([1, 2, 3]);

Existence Check

The exists() method provides an optimized way to check if any rows match a query. Unlike first() !== undefined, it generates efficient SELECT EXISTS(...) SQL:

// doctest: real-db-only — requires a live PostgreSQL connection
// Check if any active users exist
const hasActiveUsers = await orm.select('users').where(eq('active', true)).exists();

// More efficient than:
// const hasActiveUsers = (await orm.select('users').where(eq('active', true)).dump()) !== undefined;

Generated SQL:

SELECT EXISTS(SELECT 1 FROM users WHERE users.active = $1) AS "exists"

Key behaviors:

• Returns true if at least one row matches, false otherwise
• Strips orderBy (irrelevant for existence)
• Strips include (related data not needed)
• Preserves where, groupBy, having, offset
• Sets internal limit: 1 for optimization

Debugging: Use existsDump() to inspect the generated intent without executing:

const dump = orm.select('users').where(eq('active', true)).existsDump();
console.log(dump.sql);     // SELECT EXISTS(...) AS "exists"
console.log(dump.params);  // [true]
console.log(dump.plan);    // PlanReport with existsWrap: true

Streaming

// doctest: real-db-only — requires a live PostgreSQL connection
const stream = orm.select('users').stream();
let shouldStop = false; // set to true to stop early (e.g. on timeout or signal)

for await (const user of stream) {
  console.log(user.name);
  if (shouldStop) break;  // early break releases connection
}

Stream Options

// doctest: real-db-only — requires a live PostgreSQL connection
const stream = orm.select('users').stream({
  chunkSize: 100,  // rows fetched per cursor batch (default: framework-defined)

  onStart(dump) {
    // Called once on first next() — lazy initialization
    console.log('SQL:', dump.sql);
    console.log('Params:', dump.params);
    console.log('Plan:', dump.plan);
  },
});

for await (const user of stream) {
  process.stdout.write(`${user.name}\n`);
}

Option	Type	Description
chunkSize	number	Rows per cursor fetch batch
onStart	(dump: Dump) => void	Callback invoked once before first row; receives full query dump (SQL, params, plan)

Pagination

Offset-Based

// doctest: real-db-only — requires a live PostgreSQL connection
const page = await orm.select('users')
  .orderBy('name')
  .paginate({ page: 2, perPage: 25 });

// page.data          — User[]
// page.pagination.page        — 2
// page.pagination.perPage     — 25
// page.pagination.total       — 150
// page.pagination.totalPages  — 6
// page.pagination.hasNextPage — true
// page.pagination.hasPrevPage — true

Cursor-Based

// doctest: real-db-only — requires a live PostgreSQL connection
const page = await orm.select('users')
  .orderBy('createdAt', 'desc')
  .cursorPaginate({ limit: 25 });

// page.data       — User[]
// page.nextCursor — 'eyJ...' (opaque cursor string)
// page.hasNextPage — true

// Next page:
const next = await orm.select('users')
  .orderBy('createdAt', 'desc')
  .cursorPaginate({ cursor: page.nextCursor, limit: 25 });

Observability

Compilation follows Authored intent and execution authority.

dump().plan preserves the original intent, decisions, warnings and metadata. execution holds resolved includes; include decisions describe the choice without carrying execution authority.

// Execution plan (no database call)
const plan = orm.select('users').include('posts').plan();
console.log(plan.execution?.includes); // Resolved includes and query ranges
console.log(plan.decisions);  // Strategy observations; context: { intentPath, nodeId }
console.log(plan.warnings);   // [{ type: 'performance', message: '...' }]

// Full dump: plan + SQL + params
const dump = orm.select('users').where(eq('active', true)).dump();
console.log(dump.sql);      // SELECT * FROM "users" WHERE "active" = $1
console.log(dump.params);   // [true]
console.log(dump.plan);     // PlanReport
console.log(dump.meta);     // { schema?: string, queryName?: string }

---

7. Error Handling

All errors have a code property for programmatic handling and a name property for type checking.

Error	Code	When
ExecutionError	DBSP_E001	Executing without adapter configured
NotFoundError	DBSP_E002	firstOrThrow() / byIdOrThrow() finds nothing
AmbiguousRelationError	DBSP_E003	Strict mode + ambiguous relation
RelationNotFoundError	DBSP_E004	Requested relation doesn't exist
InvalidOperationError	DBSP_E005	Malformed operation
UnsafeOperationError	DBSP_E006	update()/delete() without WHERE
TableNotFoundError	DBSP_E007	Table not in schema
ColumnNotFoundError	DBSP_E008	Column not on table

// doctest: skip — illustrative fragment uses .firstOrThrow(), which requires a real PostgreSQL connection
import { Errors } from '@dbsp/core';

try {
  await orm.select('users').firstOrThrow();
} catch (error) {
  if (Errors.isNotFound(error)) {
    console.log(`Table: ${error.table}`);  // 'users'
  }
  if (Errors.isTableNotFound(error)) {
    console.log(`Available: ${error.available}`);
    console.log(`Did you mean: ${error.suggestion}`);
  }
}

Strict Mode

Strict mode throws AmbiguousRelationError when a relation is ambiguous (e.g., two FKs to the same table):

const orm = createOrm({ schema: db, adapter, strictMode: true });

// Throws AmbiguousRelationError — use withRelationHint() to disambiguate
orm.select('posts').include('users')

// Fix: specify which relation
orm.select('posts').include('users', { via: 'author' })
// Or per-query strict mode:
orm.select('posts').withStrictMode(false).include('users')

---

8. Advanced

NQL Template Literals

Use the pipe-based Natural Query Language directly from TypeScript:

const dump = orm.nql`users | where active = ${true}`.dump();
console.log(dump.params); // [true]

const mutationDump = orm.nql<unknown>`
  insert into users set name = ${'Alice'}, email = ${'alice@example.com'}
`.dump() as {
  sql: string;
  parameters: readonly unknown[];
};
console.log(mutationDump.parameters); // ['Alice', 'alice@example.com']

Interpolated values are bound as SQL parameters. NQL tag mutations support .dump(), .run(), and .all(); executed tag mutations run the normal mutation hook lifecycle. Use nqlRaw() only for trusted NQL structure. See Named Parameters and Template Binding and TypeScript Tag Mutations for the detailed binding and mutation contracts.

Hierarchy Shortcuts

// doctest: skip — exec-only operation; listAncestors/listDescendants require a real PostgreSQL connection and an employees table not in default preamble
// List all ancestors of node (flat array)
const ancestors = await orm.listAncestors('employees', 42, {
  parentId: 'managerId',
  nodeId: 'id',
  maxDepth: 10,
});

// List all descendants of node (flat array)
const descendants = await orm.listDescendants('employees', 1, {
  parentId: 'managerId',
  nodeId: 'id',
});

Query Configuration

// Override strict mode per query
orm.select('posts').withStrictMode(true)

// Disambiguate relation
orm.select('posts').withRelationHint('users', 'author')

// Override plan options
orm.select('users').withPlanOptions({ preferredStrategy: 'json_agg' })

Raw SQL (Escape Hatch)

// doctest: real-db-only — requires a live PostgreSQL connection
const results = await orm.raw<{ count: number }>(
  'SELECT COUNT(*) as count FROM "users" WHERE "active" = $1',
  [true]
);

Warning: raw() bypasses the planner and type safety. Use only when the ORM API is insufficient.

PostgreSQL Built-in Helpers

Available from @dbsp/adapter-pgsql:

import { generateSeries, nextval } from '@dbsp/adapter-pgsql';

// Generate a series of values
generateSeries(1, 100)       // generate_series(1, 100)
generateSeries(0, 50, 5)     // generate_series(0, 50, 5) — with step

// Get next sequence value
nextval('order_id_seq')      // nextval('order_id_seq')

Automatic Parameter Type Casting

When the adapter has access to ModelIR column types (via originalDbType from introspection), it automatically adds explicit type casts to query parameters:

-- Without casting (ambiguous for nullable columns)
WHERE enclosing_symbol_id = $1

-- With casting (explicit, no type inference errors)
WHERE enclosing_symbol_id = CAST($1 AS integer)

This is transparent — no code changes needed. The adapter resolves types from the schema and applies casts where needed.

9. Naming Conventions

The adapter automatically derives foreign key column names from table names using a singularize(tableName) + "_" + pkColumn convention. For example, the posts table with PK id produces FK column post_id.

Both the primary key convention and the FK derivation function are configurable.

Adapter Options

import { createPgAdapter } from '@dbsp/adapter-pgsql';
import { singularize } from '@dbsp/core';

const adapter = createPgAdapter(pool, {
  // Default PK column name (default: 'id')
  defaultPkColumnName: 'uuid',

  // Custom FK derivation (default: singularize(table) + '_' + pk)
  deriveFkColumnName: (tableName, pkColumnName) =>
    `fk_${singularize(tableName)}_${pkColumnName}`,
});

Option	Type	Default	Description
defaultPkColumnName	string	'id'	Primary-key convention when compiling without a model
deriveFkColumnName	(table: string, pk: string) => string	singularize(table)_pk	Derives FK column names when compiling without a model

Hand-built ModelIR relations use their declared key vectors, falling back only to the referenced table’s declared primary key, when included, joined or used in a relation predicate; traversing such a relation without a declared foreign key column is refused by relation name. Relation guards of UPDATE, DELETE and upsert do not follow this rule yet.

NamingPlugin — Physical Column Names

NamingPlugin converts declared model names to physical database names during compilation. Result keys come from the query projection's logical keys; the adapter does not infer them from returned PostgreSQL labels.

import { createPgAdapter } from '@dbsp/adapter-pgsql';

// Built-in: CamelCaseNamingPlugin (camelCase ↔ snake_case)
const adapter = createPgAdapter(pool, {
  dbCasing: 'snake_case',  // enables CamelCaseNamingPlugin
});

// Declared model names such as eventId compile to physical names such as event_id.
// Projecting eventId returns the logical key eventId.

Two built-in plugins:

Plugin	dbCasing	Effect
IdentityNamingPlugin	'preserve' (default)	No transformation — columns pass through unchanged
CamelCaseNamingPlugin	'snake_case'	createdAt ↔ created_at, userProfileImage ↔ user_profile_image

The CamelCaseNamingPlugin handles edge cases: acronyms (parseJSON → parse_json), numbers (field1Name → field1_name), leading underscores (_privateField → _private_field).

singularize() and pluralize() — Importable Helpers

These are optional utility functions, exported from @dbsp/core, useful for building custom FK derivation or other naming logic:

import { singularize, pluralize, IRREGULAR_PLURALS } from '@dbsp/core';

singularize('posts');       // → 'post'
singularize('categories');  // → 'category'
singularize('people');      // → 'person' (built-in irregular)

pluralize('post');          // → 'posts'
pluralize('category');      // → 'categories'
pluralize('person');        // → 'people' (built-in irregular)

Custom Overrides

Pass a Record<string, string> to singularize() for domain-specific plurals not covered by the built-in rules:

import { singularize } from '@dbsp/core';

const domainOverrides = {
  matrices: 'matrix',
  alumni: 'alumnus',
  indices: 'index',
};

singularize('matrices', domainOverrides); // → 'matrix'
singularize('users', domainOverrides);    // → 'user' (falls through to built-in rules)

Overrides take priority over built-in irregular plurals:

import { singularize } from '@dbsp/core';

singularize('people', { people: 'individual' }); // → 'individual' (overrides built-in 'person')

IRREGULAR_PLURALS — Built-in Map

The IRREGULAR_PLURALS constant is exported for inspection or extension:

import { IRREGULAR_PLURALS } from '@dbsp/core';

// Built-in: people→person, children→child, men→man, women→woman,
//           teeth→tooth, feet→foot, geese→goose, mice→mouse,
//           data→datum, media→medium, criteria→criterion, phenomena→phenomenon

Putting It Together

A fully custom FK naming strategy using a Map of overrides:

import { createPgAdapter } from '@dbsp/adapter-pgsql';
import { singularize } from '@dbsp/core';

const myPlurals: Record<string, string> = {
  matrices: 'matrix',
  alumni: 'alumnus',
};

const adapter = createPgAdapter(pool, {
  deriveFkColumnName: (tableName, pkColumnName) =>
    `${singularize(tableName, myPlurals)}_${pkColumnName}`,
});

---

10. Adapter Logging

The adapter accepts an optional AdapterLogger for query observability. All methods are optional — implement only what you need:

import { createPgAdapter } from '@dbsp/adapter-pgsql';

const adapter = createPgAdapter(pool, {
  logger: {
    debug(message, ...args) {
      console.debug(`[dbsp] ${message}`, ...args);
    },
    warn(message, ...args) {
      console.warn(`[dbsp] ${message}`, ...args);
    },
    error(message, ...args) {
      console.error(`[dbsp] ${message}`, ...args);
    },
  },
});

Method	When called
debug?(message, ...args)	Query compilation details, plan decisions
warn?(message, ...args)	Potential performance issues, deprecations
error?(message, ...args)	Query failures, connection errors

The AdapterLogger interface is exported from @dbsp/core — use any logger that matches the shape (Winston, Pino, console, custom).

Note: The PostgreSQL adapter currently logs sparingly (cleanup errors during streaming transactions). The interface is designed for future expansion — additional log points will be added as the adapter matures.

Include payload keys

Each include column uses its explicit alias, or its declared model name when no alias is supplied. Physical database names never become payload keys: dbCasing affects SQL references only. This includes aliases that happen to equal a physical name and bigint read conversions, which run under the public key.

A relation uses the requested include name at every depth. For example, include('posts', { include: [{ relation: 'comments' }] }) returns posts[].comments, even when the model resolves that child to a relation named post_comments.

NQL's unaliased relation.column label is a default flat label, not an explicit alias. Flat SQL keeps that label; nested JSON uses the column's declared name. An explicit as supplies the public column key.

Compilation resolves these keys before generating SQL. Exact duplicate source/key requests deduplicate; two different owners of one public key fail with the payload path and key. A wildcard include over a target whose columns cannot be enumerated also fails.