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.
Bulk-insert many rows in chunks. The default chunk size of 500 keeps
comfortably below Postgres' parameter-count limit of 65535. The chunk
size also auto-shrinks when the number of bindings per row would
exceed that ceiling.
When opts.onConflict is supplied each chunk is wrapped in an
INSERT ... ON CONFLICT clause:
'ignore' — ON CONFLICT (...) DO NOTHING'merge' — ON CONFLICT (...) DO UPDATE SET ... (uses conflictColumns as
the conflict target and updates every inserted column).beforeInsert / afterInsert hooks fire per row, identically to
insertMany.
Optionalopts: {The inserted rows (excluding rows skipped by 'ignore').
Bulk-update many rows in a single SQL statement using a CASE expression keyed on the entity's primary key.
Each entry's where clause must fully match the entity's primary key
columns (single or composite). Updates that touch different columns
are coalesced into one statement; rows whose PK appears in updates
but whose set does not contain a given column retain their existing
value.
The number of rows affected.
Bulk-upsert many rows in chunks. Equivalent to
.bulkInsert(rows, { onConflict: 'merge', conflictColumns }).
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.
Delete all rows that match the current WHERE clause.
If the schema has soft-delete enabled via .softDelete(), this performs
an UPDATE SET deleted_at = NOW() instead of a real DELETE.
Use hardDelete for permanent deletion.
The number of rows deleted (or soft-deleted).
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.
Permanently delete rows matching the current WHERE clause, bypassing the soft-delete mechanism.
The number of rows deleted.
Add a HAVING column operator value clause (used with GROUP BY).
this for chaining.
Add a raw HAVING expression.
this for chaining.
Eager-load a named relation defined via .hasMany(), .belongsTo(),
.hasOne(), or .belongsToMany() on the schema.
The relation name passed to the schema's relation method.
Optionalcustomize: (q: SchemaQueryBuilder<any, any>) => voidOptional callback to customise the foreign query (e.g. add ordering, limits).
this for chaining.
Eager-load a named relation declared inside a withVariants variant spec.
The relation is loaded via a LEFT JOIN on the variant's alias table and
only populated on rows whose discriminator matches variantKey.
The variant key (e.g. 'assigned').
The relation name declared in variants[key].relations.
Optionalcustomize: (q: SchemaQueryBuilder<any, any>) => voidOptional callback to restrict which columns are selected from the foreign table (scope / projection).
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.
Insert a row (or rows) with an ON CONFLICT clause.
Returns a chainable object with .merge() and .ignore() methods.
.merge(updateData?) — updates the conflicting row with the provided
fields (or all insert fields if omitted)..ignore() — skips the insert when a conflict occurs (INSERT IGNORE).Column references that define the conflict target.
A chainable conflict builder.
Return only soft-deleted rows (WHERE deleted_at IS NOT NULL).
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.
Execute an offset-based paginated query.
Runs a count query and a data query in parallel. Returns the page data along with pagination metadata.
{ page, pageSize } — 1-based page number and page size.
A PaginationResult with data, total count, and page info.
Execute a cursor-based (keyset) paginated query.
More efficient than offset pagination for large datasets. Fetches one extra row to determine whether more data exists.
{ cursor, limit, column?, direction? }.
A CursorPaginationResult with data, next cursor, and
hasMore flag.
Execute the query and return an array of values for a single column.
Column reference (property accessor or string key) for the column whose values should be returned.
A promise resolving to an array of values for that column.
Apply a named projection defined on the schema via
.projection(name, columns).
Calling .projected() on the query builder does two things:
SELECT clause to the columns registered under
name (SQL column names are resolved via .hasColumnName()).Pick<Row, Keys> so
accessing columns outside the projection is a compile-time error.The name parameter is constrained to the literal projection names
registered on the schema — TypeScript will report an error for any
unregistered name.
Calling .projected() after .select(), any aggregate method
(.count(), .min(), etc.), or a second .projected() call throws
at runtime with a clear error message.
The projection name.
A new builder whose result type is Pick<Row, ProjectionKeys>.
const PostSchema = object({ id: number(), title: string(), body: string() })
.hasTableName('posts')
.projection('summary', 'id', 'title');
const rows = await query(db, PostSchema)
.scoped('published')
.projected('summary');
// rows: Array<Pick<Post, 'id' | 'title'>>
// rows[0].body // ← TS error: not in projection
ddlExtension .projection() for schema-side definition.
Restore soft-deleted rows by setting deleted_at = NULL.
The restored rows.
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.
DTO projection: select multiple aliased columns at once via a descriptor record. Returns a query whose result rows match the shape of the selector's return value (each value typed as the inferred schema-property type).
Add a raw SQL expression to the SELECT clause.
Raw SQL (e.g. '*, ts_rank(vector, query) AS rank').
Optionalbindings: any[]Optional parameter bindings.
this for chaining.
Restrict the query to only return rows for the specified variant keys.
Adds WHERE <discriminator> IN (...) to the query and skips the LEFT
JOINs for excluded variants. This is more efficient than filtering after
loading all variants.
Only valid on a polymorphic schema (created via .withVariants()).
Discriminator values to include (e.g. ['image', 'document']).
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.
Insert or update a row based on a conflict target (upsert shorthand).
Equivalent to calling .onConflict(conflictColumns).merge(updateData).
The row data to insert.
{ conflictColumns, updateColumns? }.
The resulting row (inserted or 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 PostgreSQL JSON path filter using @? / @@ operators or
a path-based equality test via jsonb_path_query_first.
Only supported on pg clients — throws at runtime on others.
Column reference for the jsonb column.
Dot-separated property path (e.g. 'a.b.c') or a
JSONPath expression string (e.g. '$.a.b ? (@ == 1)').
Optionaloperator: stringComparison operator (=, !=, <, <=, >, >=,
@?, @@). Use @? / @@ for JSONPath existence / predicate tests.
Optionalvalue: anyThe right-hand side value. Ignored for @? and @@.
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 NOT EXISTS (subquery) clause.
A Knex query callback or sub-query builder.
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.
Add a WHERE condition that applies only to rows matching a specific variant of a polymorphic schema. Rows for other variants pass through unaffected (the condition is ORed away for non-matching discriminator values).
The column name is resolved against the variant's schema properties.
Only valid on a polymorphic schema (created via .withVariants()).
The discriminator value identifying the variant (e.g. 'image').
Property key on the variant's schema (e.g. 'width').
SQL comparison operator ('=', '>', '<', 'like', etc.).
The value to compare against.
this for chaining.
Include soft-deleted rows in the results.
By default, schemas with .softDelete() automatically filter out
rows where deleted_at IS NOT NULL. Call .withDeleted() to
include them.
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