@cleverbrush/knex-schema

Quick Start

Import schema builders from @cleverbrush/knex-schema (not from @cleverbrush/schema) to get the .hasColumnName() / .hasTableName() extension methods:

import knex from 'knex';
import { query, object, string, number, date } from '@cleverbrush/knex-schema';

// Describe your table — one definition for types and SQL
const UserSchema = object({
    id:        number(),
    firstName: string().hasColumnName('first_name'),
    lastName:  string().hasColumnName('last_name'),
    age:       number().optional(),
    createdAt: date().hasColumnName('created_at'),
}).hasTableName('users');

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

// Query — property accessors resolve to SQL column names automatically
const adults = await query(db, UserSchema)
    .where(t => t.age, '>', 18)
    .orderBy(t => t.lastName);
//→ Array<{ id: number; firstName: string; lastName: string; age?: number; createdAt: Date }>

CRUD Operations

// INSERT — returns the full inserted row (database-generated fields included)
const user = await query(db, UserSchema).insert({
    firstName: 'Alice',
    lastName:  'Smith',
    age:       30,
    createdAt: new Date(),
});

// UPDATE — returns updated rows
const updated = await query(db, UserSchema)
    .where(t => t.id, userId)
    .update({ firstName: 'Alicia' });

// DELETE — returns row count
const count = await query(db, UserSchema)
    .where(t => t.id, userId)
    .delete();

// SELECT first match
const user = await query(db, UserSchema)
    .where(t => t.id, userId)
    .first();  // → UserType | undefined

Guarded Upserts

Use onConflict().merge() when an import or sync job should insert missing rows, but update existing rows only when the incoming data is newer.

await query(db, ProductSchema)
    .onConflict(t => t.sku)
    .merge(
        {
            sku: 'SKU-123',
            price: 1999,
            sourceUpdatedAt: new Date('2026-01-15T10:00:00Z'),
        },
        {
            price: ({ excluded }) => excluded(t => t.price),
            sourceUpdatedAt: ({ excluded }) =>
                excluded(t => t.sourceUpdatedAt),
            syncedAt: ({ knex }) => knex.fn.now(),
        },
        {
            where: (qb, { column }) => {
                qb.whereRaw('?? < excluded.??', [
                    column(t => t.sourceUpdatedAt),
                    column(t => t.sourceUpdatedAt),
                ]);
            },
        }
    );

import type { InferDatabaseRow } from '@cleverbrush/knex-schema';
type ProductRow = InferDatabaseRow<typeof ProductSchema>;

excluded() references the incoming row from the failed insert, column() resolves schema property names to SQL column names, and the where callback adds a PostgreSQL guarded update.

insert into "products" ("sku", "price", "source_updated_at")
values (?, ?, ?)
on conflict ("sku") do update set
    "price" = excluded."price",
    "source_updated_at" = excluded."source_updated_at",
    "synced_at" = CURRENT_TIMESTAMP
where "source_updated_at" < excluded."source_updated_at"
returning *

Eager Loading (No N+1)

.joinOne() and .joinMany() load related rows in a single PostgreSQL query using correlated subqueries and jsonb_agg. The inferred TypeScript type is updated automatically for each join you add.

const PostSchema = object({
    id:       number(),
    title:    string(),
    authorId: number().hasColumnName('author_id'),
}).hasTableName('posts');

// One-to-many — load each user's posts (latest 5, newest first)
const users = await query(db, UserSchema)
    .joinMany({
        foreignSchema: PostSchema,
        localColumn:   t => t.id,
        foreignColumn: t => t.authorId,
        as:            'posts',
    }, posts => posts.orderBy(t => t.id, 'desc').limit(5));
// users[0].posts → Array<{ id: number; title: string; authorId: number }>

// Many-to-one — attach the author to each post
const posts = await query(db, PostSchema)
    .joinOne({
        foreignSchema: UserSchema,
        localColumn:   t => t.authorId,
        foreignColumn: t => t.id,
        as:            'author',
        required:      false,  // left join — author may be null
    });
// posts[0].author → { id: number; firstName: string; ... } | null

Filtering

Use property accessors or string keys — both are resolved to SQL column names. Record objects ({ name: "Alice" }) have their keys mapped automatically too.

query(db, UserSchema)
    .where(t => t.firstName, 'like', 'A%')          // property accessor
    .andWhere('age', '>', 18)                        // string key
    .orWhere({ lastName: 'Smith' })                  // record — keys mapped to columns
    .whereIn(t => t.id, [1, 2, 3])
    .whereNotNull(t => t.createdAt)
    .whereBetween(t => t.age, [20, 40])
    .whereILike(t => t.lastName, 'sm%')             // case-insensitive (PostgreSQL)
    .whereRaw('extract(year from created_at) = ?', [2025]);

Escape Hatch

Use .apply(fn, { output }) with a complete output schema for raw SQL. The independently mutable Knex.QueryBuilder is passed to your callback:

const UserName = object({ name: string() });
const rows = await query(db, UserSchema)
    .where(t => t.id, id)
    .apply(qb => qb.clearSelect().select({ name: 'first_name' }), {
        output: UserName,
    });

// Share an immutable Framework base query; retain each configured result.
const base = query(db, UserSchema).whereNull(t => t.deletedAt);
const activeUsers = await base.where(t => t.age, '>', 18);

Composable read queries

Use alias(schema, name) with typed flat joins and object projections for DTOs. Left-joined fields include null; source scopes and soft deletion are retained.

const task = alias(TaskSchema, 'task');
const owner = alias(UserSchema, 'owner');
const rows = await query(knex, task)
    .leftJoin(owner, t => eq(t.task.ownerId, t.owner.id))
    .select(t => ({ id: t.task.id, ownerName: t.owner.name }));

Aggregates and optional output schemas

Count helpers return checked numbers; sum and average preserve database numeric text or null. Min/max follow the column representation. Optional output schemas replace decoding and receive raw driver values, including null. Typed aggregate expressions support grouped results.

const count = await query(knex, TaskSchema).countValue();
const average = await query(knex, TaskSchema).avgValue(t => t.estimate, {
    output: number().isFloat().coerce().nullable()
});

Ordering and composite cursors

Eager loading preserves final parent order and page size. Composite cursors retain timestamp precision and require non-null scalar columns with a declared unique tie-breaker. Reapply access filters; cursors are not authorization.

const page = await query(knex, TaskSchema).paginateAfter({
    limit: 50, cursor,
    orderBy: [
        { column: t => t.createdAt, direction: 'desc' },
        { column: t => t.id, direction: 'desc' }
    ]
});

Queries are immutable. Read the complete query guide for examples, precision policy, and restrictions.

Automatic projection-aware schemas

Every query exposes rowSchema automatically. Immutable PostgreSQL queries describe the actual decoded result schema: SQL null stays null, dates are Date objects at every depth, and decimal/bigint values are exact strings before JSON parsing.

const read = query(knex, TaskSchema)
    .select(t => ({ title: t.title, amount: t.amount }));
const Source = read.rowSchema;
const rows = await read.where(t => t.id, taskId);

Typed aliases, aggregates, named projections and nested graphs retain their selected shape. Polymorphic readers expose a union rowSchema and per-variant object schemas. Raw shapes require an explicit Framework output schema. Retain every returned builder when configuring a query.

Define now, supply the connection at execution

Keep reusable queries in their own modules. Their row schemas and argument types are available without Knex; handlers supply an injected connection or transaction.

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

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

// api/handlers/get-user.ts
import type { Knex } from 'knex';
import { findUser } from '../../data/user-queries.js';

export async function getUser(knex: Knex, id: number) {
    return findUser.query(knex, id).first();
}

// Direct SELECT or SQL inspection:
await findUser(knex, 10);
await names(knex); // parameterless definition
findUser.toSQL(knex, 10);

Definitions are immutable and non-thenable. Selectors, scopes and relation/variant customizers run once during configuration. Compiled PostgreSQL SELECTs are cached per definition and actual Knex instance, never across unrelated clients; each call executes with fresh bindings.

Bind with .query(knex, ...values) before pagination, native SQL escape hatches or supported writes. The caller owns transaction lifetime. Existing connection-first queries and ORM DbSets remain supported.

Parameterized compiled queries

Add a named parameter to make a PostgreSQL SELECT callable. Argument types come from the selected schema properties, in the order their names first appear. Repeated names share one argument.

import { parameter, query } from '@cleverbrush/knex-schema';

const findUser = query(knex, UserSchema)
    .where(t => t.id, parameter('id'));
const users = await findUser(10);

const search = query(knex, UserSchema)
    .where(t => t.firstName, parameter('name'))
    .where(t => t.age, '>=', parameter('minimumAge'));
await search('John', 18);
await search('Jane', 30);

const sql = search.toSQL('John', 18); // inspect without executing
const bound = search.query('John', 18);
await bound.orderBy(t => t.age).limit(10);

The first call or SQL inspection caches SQL, binding slots and decoding. Later calls bind fresh values and execute again. Bound readers compose independently; template derivatives own their compiled statements. Relations, aliases, variants and caller-owned transactions are supported.

Filtering and ordering schema-aware reads

Group AND/OR search predicates without changing the selected row schema. IN/EXISTS subqueries, null checks, and bound raw predicates and ordering remain explicit. Every outer operation returns an independent reader.

const base = query(knex, TaskSchema)
    .select(t => ({ id: t.id, title: t.title }));
const read = base.where(t => t.projectId, projectId)
    .andWhere(group => group
        .where(t => t.title, 'ilike', pattern)
        .orWhereExists(labelMatches))
    .orderByRaw('case when ?? = ? then 0 else 1 end', [
        base.ref(t => t.id), priorityTaskId
    ]);
// read.rowSchema === base.rowSchema

Group callbacks run synchronously once and expose only predicates. Subquery SQL and bindings are captured on attachment, with no database execution. Use ref() for quoted columns and generated aliases; keep values in bindings and raw SQL fragments application-authored. Raw apply() requires an explicit output schema. Return the configured group from every predicate callback.

API Reference

JSONB documents

Use object({ ... }).jsonb() for a document column. Add .acceptUnknownProps() on each object that must retain undeclared JSON fields. Reads, projections and write-returning results preserve those fields alongside typed, declared properties.

Invalid JSON extension values are rejected before persistence. Tracked ORM document columns detect nested edits and compare documents structurally; object key order is not a storage guarantee. Optional and nullable object columns accept SQL null.