The ObjectSchemaBuilder describing the main table.
The ObjectSchemaBuilder describing the main table.
A configured Knex instance.
The ObjectSchemaBuilder for the primary table.
Must have a table name set via .hasTableName().
OptionalbaseQuery: QueryBuilder<any, any>Optional pre-configured Knex.QueryBuilder to use as
the base query instead of the default knex(tableName). Useful when you
need custom joins, CTEs, or other Knex features not exposed by this API.
Alias for where — explicitly adds an AND WHERE clause.
Identical to calling .where() when no logical-OR grouping is needed.
this for chaining.
Alias for where — explicitly adds an AND WHERE clause.
Identical to calling .where() when no logical-OR grouping is needed.
this for chaining.
Alias for where — explicitly adds an AND WHERE clause.
Identical to calling .where() when no logical-OR grouping is needed.
this for chaining.
Alias for where — explicitly adds an AND WHERE clause.
Identical to calling .where() when no logical-OR grouping is needed.
this for chaining.
Alias for where — explicitly adds an AND WHERE clause.
Identical to calling .where() when no logical-OR grouping is needed.
this for chaining.
Escape hatch: apply any Knex method to the underlying base query.
Use this when you need a Knex feature not exposed by this API (e.g.
forUpdate(), CTEs, join(), union()).
A callback that receives the raw Knex.QueryBuilder and
may mutate it in place.
this for chaining.
Add an AVG(column) aggregate.
this for chaining.
Add a COUNT(*) or COUNT(column) aggregate to the select list.
Optionalcolumn: Raw<any> | ColumnRef<TLocalSchema>Optional column to count (defaults to *).
this for chaining.
Add a COUNT(DISTINCT column) aggregate to the select list.
Optionalcolumn: Raw<any> | ColumnRef<TLocalSchema>Optional column (defaults to *).
this for chaining.
Add DISTINCT to the select clause. Duplicate rows are eliminated.
One or more column references or raw expressions.
this for chaining.
Execute the query and return all matching rows, mapped back to schema property names.
A promise that resolves to an array of result objects typed as
TResult[].
Execute the query and return only the first row, or undefined if no
rows match.
Add a GROUP BY clause.
One or more column references or raw expressions.
this for chaining.
Add a raw GROUP BY expression.
this for chaining.
Add a HAVING column operator value clause (used with GROUP BY).
this for chaining.
Add a raw HAVING expression.
this for chaining.
Insert a single row into the table and return the inserted record.
Property keys are mapped to SQL column names via the schema's
hasColumnName() metadata before the INSERT is executed. The
returned row is mapped back to property names.
The object to insert. Keys must be valid schema property names.
The full inserted row (including database-generated fields).
Insert multiple rows in a single INSERT statement and return all
inserted records.
Array of objects to insert.
The full inserted rows in insertion order.
Eager-load a collection of related rows (one-to-many relationship).
Related rows are fetched via a single CTE + jsonb_agg query. The
collection is attached to each result row under the field name specified
by spec.as. Supports limit, offset, and orderBy per-parent
using a row_number() window function to avoid fetching the full
relation before slicing.
Join specification. Key fields:
foreignSchema — the ObjectSchemaBuilder of the related table.localColumn — the primary/unique key on the local table.foreignColumn — the column on the foreign table that references localColumn.as — the property name to attach the array under.limit / offset — optional pagination per parent row.orderBy — optional { column, direction } for the sub-collection.foreignQuery — optional pre-filtered Knex.QueryBuilder.this (with an updated TResult type that includes the new field)
for chaining.
const UserSchema = object({
id: number(),
name: string(),
}).hasTableName('users');
const PostSchema = object({
id: number(),
title: string(),
authorId: number(),
}).hasTableName('posts');
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 — typed as Array<{ id: number; title: string; authorId: number }>
Eager-load a single related row (one-to-one / many-to-one relationship).
The related rows are fetched using a single CTE + jsonb_agg — no N+1
queries. The related object is attached to each result row under the
field name specified by spec.as.
Join specification. Key fields:
foreignSchema — the ObjectSchemaBuilder of the related table.localColumn — the local column that holds the foreign-table reference.foreignColumn — the primary/unique key on the foreign table.as — the property name to attach the related object under.required — if true (default), rows without a matching related
record are excluded (inner join); if false, they are included with
null (left join).foreignQuery — optional pre-filtered Knex.QueryBuilder for the
foreign table (e.g. to apply scopes).this (with an updated TResult type that includes the new field)
for chaining.
const PostSchema = object({
id: number(),
title: string(),
authorId: number(),
}).hasTableName('posts');
const AuthorSchema = object({
id: number(),
name: string(),
}).hasTableName('authors');
const posts = await query(db, PostSchema)
.joinOne({
foreignSchema: AuthorSchema,
localColumn: t => t.authorId,
foreignColumn: t => t.id,
as: 'author',
});
// posts[0].author.name — typed as string ✓
Limit the number of rows returned.
Maximum number of rows.
this for chaining.
Add a MAX(column) aggregate.
this for chaining.
Add a MIN(column) aggregate.
this for chaining.
Skip the first n rows in the result set (for cursor/offset pagination).
Number of rows to skip.
this for chaining.
Order the results by a column.
Column reference or raw expression.
Optionaldirection: "asc" | "desc"'asc' (default) or 'desc'.
this for chaining.
Order the results by a raw SQL expression.
Raw SQL (e.g. 'LOWER(name) ASC').
this for chaining.
Add an OR WHERE clause. Use this to create alternative filter branches.
this for chaining.
Add an OR WHERE clause. Use this to create alternative filter branches.
this for chaining.
Add an OR WHERE clause. Use this to create alternative filter branches.
this for chaining.
Add an OR WHERE clause. Use this to create alternative filter branches.
this for chaining.
Add an OR WHERE clause. Use this to create alternative filter branches.
this for chaining.
Add an OR WHERE column IN (values) clause.
this for chaining.
Add an OR WHERE column NOT IN (values) clause.
this for chaining.
Add an OR WHERE column IS NOT NULL clause.
this for chaining.
Add an OR WHERE column IS NULL clause.
this for chaining.
Select specific columns instead of *. Each column reference is
resolved to its SQL column name through the schema.
One or more column references or raw expressions.
this for chaining.
Add a SUM(column) aggregate.
this for chaining.
Returns the underlying Knex query builder. Useful when passing this
query as a foreignQuery in .joinOne() / .joinMany(), or any context
that expects a raw Knex.QueryBuilder.
Return the raw SQL string that would be executed, for debugging. Does not execute the query against the database.
Alias for toQuery — returns the raw SQL string.
Bind this query builder to a Knex transaction.
Returns a new builder that runs all operations — SELECT, INSERT, UPDATE, DELETE, and eager-loaded sub-queries — within the given transaction. The original builder is left unchanged.
Use this when you already have a transaction obtained from
knex.transaction() and want all operations performed by the returned
builder to participate in that transaction.
The Knex transaction obtained from knex.transaction().
A new SchemaQueryBuilder bound to the transaction.
async function createUser(
data: InsertType<typeof UserSchema>,
trx: Knex.Transaction
) {
return query(db, UserSchema).transacting(trx).insert(data);
}
await db.transaction(async trx => {
const user = await createUser({ name: 'Alice' }, trx);
await query(db, PostSchema).transacting(trx).insert({ authorId: user.id, title: 'Hello' });
});
Update all rows that match the current WHERE clause and return the
updated records.
Only the keys present in data are updated (partial update). Property
keys are resolved to column names automatically.
Partial schema object with fields to update.
All rows that were updated.
Add a WHERE clause to the query.
Accepts a column reference, an optional operator, and a value:
where(t => t.age, '>', 18) — property accessor + operator + value.where('age', 18) — string key + value (defaults to =).where({ name: 'Alice' }) — record object; property keys are mapped
to column names automatically.where(builder => { ... }) — Knex sub-builder callback for grouped
conditions.where(knex.raw('...')) — raw SQL expression.Multiple .where() calls are combined with AND.
this for chaining.
Add a WHERE clause to the query.
Accepts a column reference, an optional operator, and a value:
where(t => t.age, '>', 18) — property accessor + operator + value.where('age', 18) — string key + value (defaults to =).where({ name: 'Alice' }) — record object; property keys are mapped
to column names automatically.where(builder => { ... }) — Knex sub-builder callback for grouped
conditions.where(knex.raw('...')) — raw SQL expression.Multiple .where() calls are combined with AND.
this for chaining.
Add a WHERE clause to the query.
Accepts a column reference, an optional operator, and a value:
where(t => t.age, '>', 18) — property accessor + operator + value.where('age', 18) — string key + value (defaults to =).where({ name: 'Alice' }) — record object; property keys are mapped
to column names automatically.where(builder => { ... }) — Knex sub-builder callback for grouped
conditions.where(knex.raw('...')) — raw SQL expression.Multiple .where() calls are combined with AND.
this for chaining.
Add a WHERE clause to the query.
Accepts a column reference, an optional operator, and a value:
where(t => t.age, '>', 18) — property accessor + operator + value.where('age', 18) — string key + value (defaults to =).where({ name: 'Alice' }) — record object; property keys are mapped
to column names automatically.where(builder => { ... }) — Knex sub-builder callback for grouped
conditions.where(knex.raw('...')) — raw SQL expression.Multiple .where() calls are combined with AND.
this for chaining.
Add a WHERE clause to the query.
Accepts a column reference, an optional operator, and a value:
where(t => t.age, '>', 18) — property accessor + operator + value.where('age', 18) — string key + value (defaults to =).where({ name: 'Alice' }) — record object; property keys are mapped
to column names automatically.where(builder => { ... }) — Knex sub-builder callback for grouped
conditions.where(knex.raw('...')) — raw SQL expression.Multiple .where() calls are combined with AND.
this for chaining.
Add a WHERE clause to the query.
Accepts a column reference, an optional operator, and a value:
where(t => t.age, '>', 18) — property accessor + operator + value.where('age', 18) — string key + value (defaults to =).where({ name: 'Alice' }) — record object; property keys are mapped
to column names automatically.where(builder => { ... }) — Knex sub-builder callback for grouped
conditions.where(knex.raw('...')) — raw SQL expression.Multiple .where() calls are combined with AND.
this for chaining.
Add a WHERE column BETWEEN low AND high clause.
A two-element tuple [low, high].
this for chaining.
Add a WHERE EXISTS (subquery) clause.
A Knex query callback or sub-query builder.
this for chaining.
Add a case-insensitive WHERE column ILIKE value clause (PostgreSQL).
A SQL LIKE pattern (e.g. 'alice%').
this for chaining.
Add a WHERE column IN (values) clause.
Column reference (property accessor or string key).
Array of values or a sub-query.
this for chaining.
Add a case-sensitive WHERE column LIKE value clause.
A SQL LIKE pattern (e.g. 'Alice%').
this for chaining.
Add a WHERE NOT clause — negates the condition.
this for chaining.
Add a WHERE NOT clause — negates the condition.
this for chaining.
Add a WHERE NOT clause — negates the condition.
this for chaining.
Add a WHERE NOT clause — negates the condition.
this for chaining.
Add a WHERE NOT clause — negates the condition.
this for chaining.
Add a WHERE column NOT BETWEEN low AND high clause.
A two-element tuple [low, high].
this for chaining.
Add a WHERE column NOT IN (values) clause.
Column reference.
Array of values or a sub-query.
this for chaining.
Add a WHERE column IS NOT NULL clause.
this for chaining.
Add a WHERE column IS NULL clause.
this for chaining.
Add a raw WHERE clause. Useful for database-specific expressions.
Raw SQL string with optional :binding: or ? placeholders.
Values for the placeholders.
this for chaining.
Type-safe, schema-driven query builder for Knex.
SchemaQueryBuilderwraps a Knex.QueryBuilder and adds:t => t.name) or a string property name; both are resolved to the correct SQL column through the schema'shasColumnName()metadata automatically.jsonb_aggto load related rows in a single query.await-able so you can writeawait query(db, Schema)without calling execute explicitly.Create instances via the query factory function rather than calling the constructor directly.
Example