Class: QueryBuilder<T>
Defined in: src/query/QueryBuilder.ts:19
Low-level SQL AST and query compiler supporting multiple database dialects.
QueryBuilder compiles SELECT, INSERT, UPDATE, DELETE, COUNT, and AGGREGATE queries
into parameterized SQL strings formatted for SQLite, PostgreSQL, MySQL, SQL Server, etc.
Type Parameters
| Type Parameter | Default type |
|---|---|
T | any |
Constructors
Constructor
new QueryBuilder<
T>(adapter,tableName,alias?):QueryBuilder<T>
Defined in: src/query/QueryBuilder.ts:48
Initializes a new QueryBuilder instance for a given table.
Parameters
| Parameter | Type | Description |
|---|---|---|
adapter | IDbAdapter | Database adapter used for identifier escaping and placeholder formatting. |
tableName | string | Target table name. |
alias? | string | Optional table alias for self-joins and subqueries. |
Returns
QueryBuilder<T>
Methods
as()
as(
alias):this
Defined in: src/query/QueryBuilder.ts:60
Sets or updates the alias for the target table.
Parameters
| Parameter | Type |
|---|---|
alias | string |
Returns
this
withCte()
withCte(
name,query,recursive?):this
Defined in: src/query/QueryBuilder.ts:72
Defines a Common Table Expression (WITH clause).
Parameters
| Parameter | Type | Default value | Description |
|---|---|---|---|
name | string | undefined | CTE identifier name. |
query | string | QueryBuilder<any> | Subquery<any> | ((qb) => any) | undefined | QueryBuilder, Subquery, or SQL string defining the CTE dataset. |
recursive | boolean | false | Whether the CTE is RECURSIVE. |
Returns
this
asSubquery()
asSubquery(
alias):Subquery<T>
Defined in: src/query/QueryBuilder.ts:86
Converts this query into an aliased Subquery usable inside FROM, JOIN, or EXISTS clauses.
Parameters
| Parameter | Type | Description |
|---|---|---|
alias | string | Alias for referencing this subquery. |
Returns
Subquery<T>
nearest()
nearest(
column,vector,options?):this
Defined in: src/query/QueryBuilder.ts:105
Performs semantic / vector distance search on an embedding column using pgvector operators.
Parameters
| Parameter | Type | Description |
|---|---|---|
column | string | Vector column name. |
vector | number[] | Query embedding coordinates array. |
options? | NearestOptions | Distance metric ('cosine', 'l2', 'inner_product') and limit. |
Returns
this
clone()
clone<
R>():QueryBuilder<R>
Defined in: src/query/QueryBuilder.ts:119
Creates an independent deep clone of this QueryBuilder instance.
Type Parameters
| Type Parameter | Default type |
|---|---|
R | T |
Returns
QueryBuilder<R>
Cloned QueryBuilder instance.
Usecase
Immutable query chaining in DbSet where new query modifications do not alter earlier instances.
groupBy()
groupBy(...
columns):this
Defined in: src/query/QueryBuilder.ts:148
Adds columns to the SQL GROUP BY clause.
Parameters
| Parameter | Type | Description |
|---|---|---|
...columns | string[] | Names of columns to group by. |
Returns
this
this builder instance for chaining.
Usecase
Grouping rows by categories or status for aggregate calculations.
having()
having(
expression,operator,value?,value2?):this
Defined in: src/query/QueryBuilder.ts:163
Adds an SQL HAVING clause condition to filter aggregated groups.
Parameters
| Parameter | Type | Description |
|---|---|---|
expression | string | Aggregate SQL expression (e.g. 'COUNT(*)'). |
operator | string | Comparison operator (e.g. '>', '=', 'BETWEEN'). |
value? | unknown | Target value. |
value2? | unknown | Secondary value when using BETWEEN. |
Returns
this
this builder instance for chaining.
Usecase
Filter grouped rows after aggregation (e.g. HAVING COUNT(*) > 5).
usePrimary()
usePrimary(
use?):this
Defined in: src/query/QueryBuilder.ts:175
Configures whether to force execution on the primary database connection rather than read replicas.
Parameters
| Parameter | Type | Default value | Description |
|---|---|---|---|
use | boolean | true | true to force primary connection. |
Returns
this
this builder instance for chaining.
Usecase
Read-after-write consistency in multi-replica environments.
isUsePrimary()
isUsePrimary():
boolean
Defined in: src/query/QueryBuilder.ts:185
Checks whether the primary database connection is forced for this query.
Returns
boolean
true if primary connection routing is enabled.
getGroupByColumns()
getGroupByColumns():
string[]
Defined in: src/query/QueryBuilder.ts:192
Returns a copy of the configured GROUP BY column names.
Returns
string[]
select()
select(...
columns):this
Defined in: src/query/QueryBuilder.ts:203
Specifies which columns to project in the SELECT clause.
Parameters
| Parameter | Type | Description |
|---|---|---|
...columns | string[] | List of column names or SQL expressions. |
Returns
this
this builder instance for chaining.
Usecase
Restrict returned columns to improve query performance.
selectWindow()
selectWindow(
fn):this
Defined in: src/query/QueryBuilder.ts:211
Adds a window function projection expression to the query.
Parameters
| Parameter | Type |
|---|---|
fn | (w) => WindowFunctionExpression |
Returns
this
toSql()
toSql():
string
Defined in: src/query/QueryBuilder.ts:229
Compiles and returns the SELECT query string.
Returns
string
distinct()
distinct(
distinct?):this
Defined in: src/query/QueryBuilder.ts:240
Enables or disables SELECT DISTINCT to eliminate duplicate rows.
Parameters
| Parameter | Type | Default value | Description |
|---|---|---|---|
distinct | boolean | true | true to enable DISTINCT (defaults to true). |
Returns
this
this builder instance for chaining.
Usecase
Filter out duplicates from joined or multi-row queries.
where()
where(
where):this
Defined in: src/query/QueryBuilder.ts:252
Attaches a WhereClause builder containing filter conditions.
Parameters
| Parameter | Type | Description |
|---|---|---|
where | WhereClause<T> | ((w) => void) | The WhereClause instance or a callback that receives a fresh WhereClause. |
Returns
this
this builder instance for chaining.
Usecase
Set the WHERE clause filter tree on this query.
getWhereClause()
getWhereClause():
WhereClause<T>
Defined in: src/query/QueryBuilder.ts:268
Returns the attached mutable WhereClause builder.
Returns
WhereClause<T>
Usecase
Access and mutate conditions directly on the query builder.
orderBy()
orderBy(
column,direction?):this
Defined in: src/query/QueryBuilder.ts:280
Adds an ORDER BY sorting clause.
Parameters
| Parameter | Type | Default value | Description |
|---|---|---|---|
column | string | undefined | Column name to sort by. |
direction | "desc" | "asc" | 'asc' | Sort direction ('asc' or 'desc'). |
Returns
this
this builder instance for chaining.
Usecase
Sort query results ascending or descending by column.
join()
join(
type,tableName,leftColumn,rightColumn,alias?):this
Defined in: src/query/QueryBuilder.ts:299
Adds a relational SQL JOIN clause against another table.
Parameters
| Parameter | Type | Description |
|---|---|---|
type | "INNER" | "LEFT" | "RIGHT" | "FULL" | JOIN type ('INNER', 'LEFT', 'RIGHT', or 'FULL'). |
tableName | string | Foreign table name. |
leftColumn | string | Primary table column key. |
rightColumn | string | Foreign table column key. |
alias? | string | Optional table alias for the joined table. |
Returns
this
this builder instance for chaining.
Usecase
Combine rows from two or more tables based on a related column.
limit()
limit(
limit):this
Defined in: src/query/QueryBuilder.ts:323
Sets the maximum number of records to return (LIMIT / TOP).
Parameters
| Parameter | Type | Description |
|---|---|---|
limit | number | Maximum rows count. |
Returns
this
this builder instance for chaining.
Usecase
Restrict result set size for pagination or top-N lists.
getLimit()
getLimit():
number|undefined
Defined in: src/query/QueryBuilder.ts:328
Returns
number | undefined
getOffset()
getOffset():
number|undefined
Defined in: src/query/QueryBuilder.ts:332
Returns
number | undefined
offset()
offset(
offset):this
Defined in: src/query/QueryBuilder.ts:343
Sets the number of rows to skip before returning results (OFFSET).
Parameters
| Parameter | Type | Description |
|---|---|---|
offset | number | Number of rows to skip. |
Returns
this
this builder instance for chaining.
Usecase
Offset-based pagination.
forUpdate()
forUpdate(
options?):this
Defined in: src/query/QueryBuilder.ts:357
Applies pessimistic row locking (FOR UPDATE / WITH (UPDLOCK, ROWLOCK)). Prevents concurrent transactions from modifying the selected rows until this transaction commits.
Parameters
| Parameter | Type | Description |
|---|---|---|
options? | { noWait?: boolean; skipLocked?: boolean; } | Optional lock modifiers: - noWait: Fail immediately if rows are locked. - skipLocked: Skip locked rows (ideal for high-concurrency worker queues). |
options.noWait? | boolean | - |
options.skipLocked? | boolean | - |
Returns
this
this builder instance for chaining.
forUpdateNoWait()
forUpdateNoWait():
this
Defined in: src/query/QueryBuilder.ts:373
Applies exclusive row locking with NOWAIT (fails immediately if rows are locked).
Returns
this
this builder instance for chaining.
forUpdateSkipLocked()
forUpdateSkipLocked():
this
Defined in: src/query/QueryBuilder.ts:383
Applies exclusive row locking skipping already locked rows (ideal for queue workers).
Returns
this
this builder instance for chaining.
forShare()
forShare():
this
Defined in: src/query/QueryBuilder.ts:393
Applies shared read locking (FOR SHARE / LOCK IN SHARE MODE / WITH (HOLDLOCK)).
Returns
this
this builder instance for chaining.
withLock()
withLock(
mode):this
Defined in: src/query/QueryBuilder.ts:404
Fluent helper to set locking strategy explicitly.
Parameters
| Parameter | Type | Description |
|---|---|---|
mode | "exclusive" | "shared" | "no-wait" | "skip-locked" | Lock strategy: 'exclusive' |
Returns
this
this builder instance for chaining.
toSelectSql()
toSelectSql(
params?,nextParamIdx?):object
Defined in: src/query/QueryBuilder.ts:428
Compiles the current query AST into a dialect-specific SELECT SQL string with parameterized values.
Parameters
| Parameter | Type | Default value |
|---|---|---|
params | AdapterParam[] | [] |
nextParamIdx? | () => number | undefined |
Returns
object
Object containing compiled sql string and params array.
| Name | Type | Defined in |
|---|---|---|
sql | string | src/query/QueryBuilder.ts:431 |
params | AdapterParam[] | src/query/QueryBuilder.ts:431 |
Usecase
Generate executable parameterized SQL and parameter arrays for database adapters.
toCountSql()
toCountSql(
column?):object
Defined in: src/query/QueryBuilder.ts:652
Compiles the query into an SQL COUNT statement (SELECT COUNT(...) AS total FROM ...).
Parameters
| Parameter | Type | Default value | Description |
|---|---|---|---|
column | string | '*' | Column to count (defaults to '*'). |
Returns
object
Object containing compiled sql string and params array.
| Name | Type | Defined in |
|---|---|---|
sql | string | src/query/QueryBuilder.ts:652 |
params | AdapterParam[] | src/query/QueryBuilder.ts:652 |
Usecase
Total record counting for pagination and metrics.
toAggregateSql()
toAggregateSql(
fn,column):object
Defined in: src/query/QueryBuilder.ts:694
Compiles the query into an SQL aggregate function call (SUM, AVG, MIN, MAX).
Parameters
| Parameter | Type | Description |
|---|---|---|
fn | "SUM" | "AVG" | "MIN" | "MAX" | Aggregate function name. |
column | string | Target column name. |
Returns
object
Object containing compiled sql string and params array.
| Name | Type | Defined in |
|---|---|---|
sql | string | src/query/QueryBuilder.ts:697 |
params | AdapterParam[] | src/query/QueryBuilder.ts:697 |
Usecase
Aggregate calculations on database numeric columns.
toInsertSql()
toInsertSql(
data):object
Defined in: src/query/QueryBuilder.ts:737
Compiles an INSERT SQL statement for a row object with safe parameter placeholders.
Parameters
| Parameter | Type | Description |
|---|---|---|
data | Record<string, unknown> | Key-value mapping of column names to values. |
Returns
object
Object containing compiled sql string and params array.
| Name | Type | Defined in |
|---|---|---|
sql | string | src/query/QueryBuilder.ts:737 |
params | AdapterParam[] | src/query/QueryBuilder.ts:737 |
Usecase
Insert a single row into the target table.
toUpsertSql()
toUpsertSql(
conflictTarget,updatePayload):object
Defined in: src/query/QueryBuilder.ts:771
Compiles an atomic dialect-specific UPSERT (INSERT ... ON CONFLICT / ON DUPLICATE KEY UPDATE / MERGE) SQL statement.
Parameters
| Parameter | Type | Description |
|---|---|---|
conflictTarget | Record<string, unknown> | Column key-value pairs that define unique conflict constraints (e.g. { email: 'user@corp.com' }). |
updatePayload | Record<string, unknown> | Column key-value pairs to update when a conflict occurs. |
Returns
object
Object containing compiled sql string and params array.
| Name | Type | Defined in |
|---|---|---|
sql | string | src/query/QueryBuilder.ts:774 |
params | AdapterParam[] | src/query/QueryBuilder.ts:774 |
toUpdateSql()
toUpdateSql(
data):object
Defined in: src/query/QueryBuilder.ts:847
Compiles an UPDATE SQL statement using the current WHERE clause conditions.
Parameters
| Parameter | Type | Description |
|---|---|---|
data | Record<string, unknown> | Key-value mapping of columns to update. |
Returns
object
Object containing compiled sql string and params array.
| Name | Type | Defined in |
|---|---|---|
sql | string | src/query/QueryBuilder.ts:847 |
params | AdapterParam[] | src/query/QueryBuilder.ts:847 |
Usecase
Update matching rows with new column values.
toDeleteSql()
toDeleteSql():
object
Defined in: src/query/QueryBuilder.ts:883
Compiles a DELETE SQL statement using the current WHERE clause conditions.
Returns
object
Object containing compiled sql string and params array.
| Name | Type | Defined in |
|---|---|---|
sql | string | src/query/QueryBuilder.ts:883 |
params | AdapterParam[] | src/query/QueryBuilder.ts:883 |
Usecase
Delete matching rows from the target table.
formatJsonPathExpression()
formatJsonPathExpression(
column,path):string
Defined in: src/query/QueryBuilder.ts:1224
Formats a JSON path extraction expression tailored to the active database provider.
Parameters
| Parameter | Type | Description |
|---|---|---|
column | string | Column name holding the JSON document. |
path | string | Property path within the JSON document (e.g. 'address.city'). |
Returns
string
Dialect-specific SQL JSON extraction expression.