@cleverbrush/orm

EF-Core-like typed ORM layer on top of @cleverbrush/knex-schema.

• Define entities with defineEntity(schema) and declare relations via .hasOne() / .hasMany() / .belongsTo() / .belongsToMany().
• Group entities into a typed context with createDb(knex, { todos, users }).
• Access db.todos, db.users as fully-typed DbSet<TEntity> instances.
• Eager-load related entities with .include(t => t.author).
• Use { tracking: true } for an identity map + change-tracking context.
• Model inheritance with STI (single-table) and CTI (class-table) variants.
• Manage schema migrations with @cleverbrush/orm-cli.

Upgrading? See Migrating from v4.x to v5.

Installation

npm install @cleverbrush/orm
Copy

knex is a peer dependency (install it alongside @cleverbrush/orm). @cleverbrush/knex-schema is a direct dependency and is installed automatically.

Quick start

1. Define a schema and entity

import { number, object, string, defineEntity } from '@cleverbrush/orm';

const UserSchema = object({
    id:    number().primaryKey(),
    email: string().hasColumnName('email_address'),
    name:  string(),
}).hasTableName('users');

export const UserEntity = defineEntity(UserSchema);
Copy

2. Create a DbContext

import knex from 'knex';
import { createDb } from '@cleverbrush/orm';
import { UserEntity } from './schemas.js';

const knexClient = knex({ client: 'pg', connection: process.env.DATABASE_URL });

const db = createDb(knexClient, { users: UserEntity });
Copy

3. Query and mutate

// Find by PK
const alice = await db.users.find(1);

// Find with WHERE clause
const user = await db.users.where(t => t.email, 'alice@example.com').first();

// Insert
const created = await db.users.save({ email: 'bob@example.com', name: 'Bob' });

// Update (PK present → UPDATE)
const updated = await db.users.save({ id: 1, email: 'alice@example.com', name: 'Alice' });
Copy

DbSet<TEntity> API

Any method from the underlying SchemaQueryBuilder (e.g. .execute(), .where(), .orderBy(), .pluck(), .count()) is also available and fully typed.

Relations

Declare relations on the entity using fluent builders. Relations are optional by default (omit from the save-graph to skip them).

const TodoSchema = object({
    id:     number().primaryKey(),
    title:  string(),
    userId: number().hasColumnName('user_id'),
    author: object({ id: number().primaryKey(), name: string() })
                .hasTableName('users').optional(),
}).hasTableName('todos');

const TodoEntity = defineEntity(TodoSchema)
    .belongsTo(t => t.author, 'userId');   // FK is on todos.user_id → users.id

const UserEntity = defineEntity(UserSchema)
    .hasMany(t => t.todos, TodoEntity, 'userId');   // FK on todos.user_id
Copy

Eager-loading

const todo = await db.todos
    .where(t => t.id, 42)
    .include(t => t.author)
    .first();

console.log(todo?.author?.name);   // fully typed
Copy

Saving with relations

db.todos.save(graph) traverses the whole object graph and persists every level in the correct FK order inside a single transaction.

// Create a new user and a new todo in one call.
const result = await db.users.save({
    name: 'Alice',
    todos: [
        { title: 'Buy milk', completed: false, userId: 0 },
    ],
});
Copy

Topology rules:

• belongsTo parents are inserted first; their PK feeds the child FK.
• The root entity is then written.
• hasOne / hasMany children inherit the root PK into their FK.
• belongsToMany children may be new objects (inserted + pivot) or bare { pk: value } references (pivot only).

Change-tracking context

Pass { tracking: true } to createDb to get a TrackedDbContext. The context maintains an identity map (same PK → same object reference) and tracks every mutation automatically.

const db = createDb(knex, { users: UserEntity }, { tracking: true });

// Load entity — it's now in the identity map.
const user = await db.users.find(1);

// Mutate normally.
user.name = 'Updated';

// Flush all dirty entries in a single transaction.
const { inserted, updated, deleted } = await db.saveChanges();
Copy

TrackedDbContext API

Row versioning

Mark any numeric or timestamp column as a row version:

const OrderSchema = object({
    id:      number().primaryKey(),
    status:  string(),
    version: number().rowVersion(),   // auto-incremented by the ORM on every UPDATE
}).hasTableName('orders');
Copy

saveChanges() appends AND version = <snapshot> to every UPDATE and throws ConcurrencyError if rowCount === 0.

await using integration

async function updateUser(userId: number) {
    await using db = createDb(knex, { users: UserEntity }, { tracking: true });
    const user = await db.users.find(userId);
    if (!user) return;
    user.name = 'Updated';
    await db.saveChanges();
}   // Symbol.asyncDispose fires here; throws if changes are still pending
Copy

Immutable queries and projection schemas

Every DbSet/query chain is immutable and automatically exposes rowSchema. Full entity reads still participate in the identity map when { tracking: true }. Selected, grouped, distinct and raw results are detached; partial rows never replace tracked entities. Entity objects themselves remain mutable: edit a full entity and call saveChanges() as before. Reads, reloads and write-returning rows now consistently use exact bigint/decimal strings, Date objects and SQL null.

const read = db.users
    .select(u => ({ id: u.id, name: u.name }));
const Source = read.rowSchema;
const toDto = mapper().configure(Source, UserDto, m => m)
    .getSyncMapper(Source, UserDto);
const users = (await read).map(toDto);
Copy

Typed relation customizers return their configured query; nested graphs are decoded in one SQL statement. STI/CTI readers expose variantRowSchemas for explicit application mapping. See the read-schema consumer guide for numeric/null/date rules, examples and compatibility boundaries.

Ordinary queries also support grouped where/andWhere/orWhere, IN/EXISTS subqueries, bound whereRaw/orderByRaw, and ref(selector) for quoted column references. These operations preserve the reader's rowSchema identity and work in nested relation customizers; polymorphic branches use forVariant(). Group callbacks are synchronous, immutable and predicate-only; always return the configured group. Conditional filters must reassign the returned query. See filtering and ordering for scoped search, subquery snapshots, pagination, and raw-SQL boundaries.

Parameterized compiled reads

For module-level reads that receive an injected connection only at execution, query and parameter are also re-exported from this package:

// data/user-queries.ts
import { parameter, query } from '@cleverbrush/orm';
import { UserSchema } from './user-schema.js';

export const findUser = query(UserSchema)
    .where(user => user.id, parameter('id'));

// api/handlers/get-user.ts — db is the injected DbContext
const user = await findUser.query(db.knex, userId).first();
const rows = await findUser(db.knex, userId);
Copy

These connection-independent reads return detached schema-backed rows, not entities registered in a DbContext's identity map. Use DbSet queries below when tracking, find() helpers or context-managed changes are required. See the multi-file query-definition example for transactions, connection-specific SQL caching and binding before writes.

parameter is re-exported by @cleverbrush/orm. Adding a named placeholder to a DbSet query creates a callable SELECT with schema-inferred positional arguments:

import { parameter } from '@cleverbrush/orm';

const findUsers = db.users
    .where(t => t.name, parameter('name'))
    .where(t => t.id, '>=', parameter('minimumId'));
const users = await findUsers('John', 10);
const sql = findUsers.toSQL('Jane', 20); // inspect without execution
const one = await findUsers.query('John', 10).find(10);
Copy

SQL compiles once on the first direct call or SQL inspection. Complete entity rows participate in the context's identity map exactly as ordinary reads do; projections remain detached. Bound readers retain ORM lookup helpers and permitted mutation methods. Unbound templates cannot execute parameterless terminals or writes. ofVariant(...) views support the same callable behavior.

See parameterized compiled queries for argument ordering, null semantics, relation/variant parameters, transactions, and the supported PostgreSQL SELECT shapes.

Polymorphic entities (STI / CTI)

Single-Table Inheritance (STI)

All variants share one table; a discriminator column identifies the type.

const ActivityBase = object({
    id:     number().primaryKey(),
    type:   string(),
    todoId: number().hasColumnName('todo_id'),
}).hasTableName('activities');

const ActivityEntity = defineEntity(ActivityBase)
    .discriminator('type')
    .stiVariant('assigned', object({
        type:       string('assigned'),
        assigneeId: number().hasColumnName('assignee_id').optional(),
    }))
    .stiVariant('commented', object({
        type: string('commented'),
        body: string().optional(),
    }));
Copy

Class-Table Inheritance (CTI)

The base row is in one table; each variant has its own extension table.

const AssignedExtras = defineEntity(
    object({
        activityId: number().hasColumnName('activity_id'),
        assigneeId: number().hasColumnName('assignee_id'),
    }).hasTableName('assigned_activities')
);

const ActivityEntity = defineEntity(ActivityBase)
    .discriminator('type')
    .ctiVariant('assigned', AssignedExtras, t => t.activityId);
Copy

Querying variants

Call db.set.ofVariant('key') to obtain a VariantDbSet — a typed view scoped to that variant, analogous to EF Core's Set<DerivedType>(). All reads are pre-filtered by the discriminator; writes use the correct STI / CTI logic automatically.

// Insert a new variant row (discriminator is set automatically)
const activity = await db.activities.ofVariant('assigned').insert({
    todoId:     42,
    assigneeId: 9,
});
// activity.type === 'assigned'
// activity.assigneeId === 9

// Find a single variant by PK
const found = await db.activities.ofVariant('assigned').find(activityId);

// Update matching rows  (chain .where() before .update())
await db.activities.ofVariant('assigned').where(t => t.id, 3).update({ assigneeId: 99 });

// Delete matching rows
await db.activities.ofVariant('assigned').where(t => t.id, 3).delete();
Copy

VariantDbSet<TEntity, K> API

Polymorphic root queries are read-only. Use ofVariant() for explicit mutations; tracked polymorphic inserts, updates and removals use the same lifecycle pipeline when saveChanges() runs. Writes require a single-column base primary key and an unprojected variant view without loaded relations. Primary keys, discriminators and CTI join keys cannot be changed by an update, including from a hook.

Variant deletion and lifecycle

The base schema controls entity soft deletion. With .softDelete(), delete() sets its deletion marker and keeps CTI child rows intact. Without that metadata, delete() removes the physical rows. hardDelete() always removes CTI children before their base rows. The child schema's own deletion marker is not changed by soft deletion or restoration of the entity.

All mutations retain query predicates, default scopes, ordering/pagination, and deleted-row visibility. Select hidden rows explicitly when restoring or purging:

const assigned = db.activities.ofVariant('assigned');
await assigned.onlyDeleted().where(t => t.id, activityId).restore();
await assigned.withDeleted().where(t => t.id, activityId).hardDelete();
Copy

Base hooks run before variant hooks, in registration order, once per operation. beforeInsert and beforeUpdate receive the combined property-name payload before it is split into storage tables. afterInsert receives the complete decoded row. beforeDelete receives a transaction-bound read query restricted to the captured mutation targets, for both soft and permanent deletion. Query configuration is immutable; hooks cannot add delete predicates by changing that query. No hooks run when an update or delete matches no rows.

Inserts set configured creation/update timestamps on both storage schemas; updates advance configured update timestamps, including the base timestamp for child-only changes. Soft deletion and restoration change only the base deletion marker: restoration runs no lifecycle hook and neither operation advances update timestamps, matching ordinary query writes. Restoring a schema without base soft deletion fails.

Target selection and all storage writes execute in one transaction. Existing transactions use a savepoint, so catching a failed mutation cannot retain half a CTI write. Hooks run before commit: database changes roll back on failure, but external side effects performed by hooks cannot be rolled back. Tracked saves preserve optimistic row-version checks and apply returned values and new snapshots only after their save transaction succeeds.

Transactions

// One-off transaction (non-tracking context)
await db.transaction(async trx => {
    const user = await trx.users.save({ name: 'Alice' });
    await trx.todos.save({ title: 'Buy milk', userId: user.id });
});

// Wrap existing transaction
const user = await db.users.withTransaction(existingTrx).save({ name: 'Alice' });
Copy

Error classes

Schema migrations

Use @cleverbrush/orm-cli to generate and apply migration files from your entity definitions:

# Diff schema vs DB → emit a TypeScript migration file
npx cb-orm migrate generate add_users_table

# Apply pending migrations
npx cb-orm migrate run

# Read-only CI check for schema drift
npx cb-orm validate
Copy

Composable queries

ORM re-exports alias, eq, and, or, and aggregate. Use query(db.knex, alias(TaskSchema, 'task')) for flat DTO joins and include for nested relations. Transaction-bound contexts expose their transaction as db.knex.

countValue, countDistinctValue, sumValue, avgValue, minValue, and maxValue are available on entity queries. An optional { output: schema } replaces default decoding and controls the inferred output, including nulls. Aggregate/DTO projections are not attached as entities in tracked contexts.

const tasks = await db.tasks
    .orderBy(t => t.createdAt, 'desc')
    .orderBy(t => t.id, 'desc')
    .include(t => t.owner, owners => {
        return owners.where(t => t.name, 'Alice'); // foreign schema, not any
    })
    .limit(20);
Copy

Eager loading retains parent order and page size. Filtering an included relation does not necessarily filter parents. Callback types infer the declared foreign schema, including variant queries when the relation schema is known.

paginateAfter({ limit, cursor, orderBy: [...] }) supports non-null scalar sorts with a declared unique tie-breaker. Use column for a single-column cursor. See the complete query guide for defaults, precision policy, grouped aggregates, cursor restrictions, and examples.

Related packages

• @cleverbrush/knex-schema — the underlying schema DSL and query builder
• @cleverbrush/orm-cli — migration CLI tool
• API reference

JSON document columns

Declare document columns with object({...}).jsonb() using the ORM schema factories. Add .acceptUnknownProps() to preserve undeclared JSON fields. See JSONB document contracts for declarations, null behavior and permissive object schemas.

Tracked JSON columns use independent document snapshots and structural comparison. Editing a nested object or array marks the column modified; replacing it with an equivalent document does not. entry(entity).reset() and discardChanges() restore independent copies, so subsequent edits cannot mutate the saved snapshot. JSON validation and encoding also apply to ORM saves and tracked updates.