@cleverbrush/orm

Quick Start

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

// 1. Define schema + entity
const UserSchema = object({
    id:    number().primaryKey(),
    email: string().hasColumnName('email_address'),
    name:  string(),
}).hasTableName('users');

const UserEntity = defineEntity(UserSchema);

// 2. Create the context
const db = createDb(
    knex({ client: 'pg', connection: process.env.DATABASE_URL }),
    { users: UserEntity }
);

// 3. Query
const alice = await db.users.find(1);

// 4. Insert / update (PK absent → INSERT, PK present → UPDATE)
const created = await db.users.save({ email: 'bob@example.com', name: 'Bob' });
const updated = await db.users.save({ id: 1, email: 'alice@example.com', name: 'Alice' });

Relations

Declare relations fluently on the entity. They are fully typed — .include() only accepts keys you have declared.

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, l => l.userId, r => r.id);

const UserEntity = defineEntity(UserSchema)
    .hasMany(t => t.todos, l => l.id, r => r.userId);

const db = createDb(knex, { users: UserEntity, todos: TodoEntity });

// Eager-load the author
const todo = await db.todos
    .where(t => t.id, 42)
    .include(t => t.author)
    .first();
console.log(todo?.author?.name); // fully typed

// Save a whole graph in one transaction
const user = await db.users.save({
    name: 'Alice',
    todos: [{ title: 'Buy milk', completed: false, userId: 0 }],
});

Change Tracking

Enable tracking with { tracking: true }. The context maintains an identity map and flushes all dirty entries inside a single transaction on saveChanges().

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

// Load — entity enters the identity map as Unchanged.
const user = await db.users.find(1);

// Mutate normally — no special setter required.
user.name = 'Updated';

// All dirty entries are flushed in one transaction.
const { inserted, updated, deleted } = await db.saveChanges();

// Works with await using — throws PendingChangesError if not flushed.
async function handler() {
    await using db = createDb(knex, { users: UserEntity }, { tracking: true });
    const user = await db.users.find(1);
    user.name = 'Updated';
    await db.saveChanges();
}   // Symbol.asyncDispose fires here

Optimistic Concurrency (Row Versioning)

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

const db = createDb(knex, { orders: OrderEntity }, { tracking: true });

const order = await db.orders.find(orderId);
order.status = 'shipped';

try {
    await db.saveChanges();
} catch (err) {
    if (err instanceof ConcurrencyError) {
        // Another process updated the row — reload and retry.
        await db.reload(order);
    }
}

Detached reads with projection schemas

Every DbSet query is immutable, with metadata matching its decoded projection and relations. Full entities retain identity tracking when enabled. Projected, grouped, distinct and raw results remain detached, so incomplete selections cannot replace tracked entities.

const read = db.projects
    .select(p => ({ id: p.id, name: p.name }))
    .include(r => r.tasks, tasks => tasks
        .select(t => ({ title: t.title })));
const Source = read.rowSchema; // nested task schema included
const projects = await read; // one SQL statement

Return child queries from customizers. STI/CTI reads expose variantRowSchemas for explicit mapper dispatch. Entity objects remain mutable. Reads, reloads and returning writes share exact numeric, date and null representations.

Polymorphic Entities (STI / CTI)

Model discriminated unions with Single-Table Inheritance (one table, discriminator column) or Class-Table Inheritance (base table + per-variant extension tables).

// ── STI: all variants in one table ──────────────────────────────
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(),
    }));

const db = createDb(knex, { activities: ActivityEntity });

// Get a typed view scoped to one variant (analogous to EF Core Set<T>())
const assigned = db.activities.ofVariant('assigned');

// Insert — discriminator is set automatically
const activity = await assigned.insert({ todoId: 42, assigneeId: 9 });
// activity.type === 'assigned', activity.assigneeId === 9

// Find by PK, typed to the variant
const found = await assigned.find(activity.id);

// Update rows matching a WHERE clause
await assigned.where(t => t.id, 3).update({ assigneeId: 99 });

// Delete rows matching a WHERE clause
await assigned.where(t => t.id, 3).delete();

Schema Migrations

Use @cleverbrush/orm-cli to diff your entity definitions against the live database and emit TypeScript migration files.

// db.config.ts — one config file, picked up automatically by cb-orm
import knex from 'knex';
import { defineConfig } from '@cleverbrush/orm-cli';
import { UserEntity, TodoEntity } from './src/db/schemas.js';

export default defineConfig({
    knex: knex({ client: 'pg', connection: process.env.DATABASE_URL }),
    entities: { users: UserEntity, todos: TodoEntity },
    migrations: { directory: './migrations' }
});

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

# Apply pending migrations
npx cb-orm migrate run

# Check migration status
npx cb-orm migrate status

# Read-only schema drift check
npx cb-orm validate

# Sync in-place (dev only — no migration file)
npx cb-orm db push

API Reference

Reliable query composition

For module-level queries, import query and parameter from @cleverbrush/orm, define query(UserSchema), and supply db.knex when calling it. These reads are detached; use DbSets when identity tracking and ORM lookup helpers are needed. See connection-independent query definitions.

Named parameters make DbSet reads callable: use db.users.where(t => t.id, parameter('id')), then call the result with an ID. SQL compiles once; complete rows retain identity tracking and projections stay detached. Use .query(...args) for a bound ORM reader or .toSQL(...args) to inspect SQL. See compiled query examples.

Eager loading retains parent ordering and page size. Relation customization callbacks infer the foreign schema. Scalar aggregate helpers accept optional output schemas; typed projections support grouped aggregates. DTOs are not tracked as entities.

ORM re-exports flat-join aliases and aggregate expressions. Composite cursors support non-null scalar sort fields with a declared unique tie-breaker. Read the query guide for examples and compatibility details.

See Also

• @cleverbrush/knex-schema — the schema DSL and query builder that powers this ORM
• @cleverbrush/orm-cli — migration CLI tool
• API Reference — full TypeDoc-generated API documentation