@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

Eager Loading (No N+1)

.joinOne() and .joinMany() load related rows in a single PostgreSQL query using CTEs 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',
        limit:         5,
        orderBy:       { column: t => t.id, direction: 'desc' },
    });
// 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) to call any Knex method not exposed by this API — the raw Knex.QueryBuilder is passed to your callback:

const rows = await query(db, UserSchema)
    .where(t => t.id, id)
    .apply(qb => qb.forUpdate().noWait());

// Pre-scoped base query (e.g. soft-delete filter)
const base = db('users').where('deleted_at', null);
const activeUsers = await query(db, UserSchema, base)
    .where(t => t.age, '>', 18);

API Reference