Documentation / zerotal / orm / QueryBuilder

Class: QueryBuilder

Defined in: packages/orm/src/db/QueryBuilder.ts:315

Fluent, low-level SQL query builder over a SQLInstance (Bun.sql).

Chain methods to describe a query, then call a terminal (get, first, count, insert, update, delete, …) to compile and execute it. Every builder method returns this, so calls chain. This is the engine beneath DB.table() and the model query builder; most application code reaches it through those, but it can be used directly.

Remarks

Safety model. User-supplied values always flow through Bun.sql tagged-template bindings (? placeholders) — they are never string-concatenated into SQL. User-supplied identifiers (column, table, alias, group/order columns) are interpolated into the SQL text and are therefore forced through an identifier-assertion allowlist (/^[a-zA-Z_][a-zA-Z0-9_.]*$/, plus a simple aggregate form for having); anything else throws.

Not every method asserts, and the differences are load-bearing:

where, whereIn, orderBy, groupBy, having, join, the aggregates and the write methods DO assert their identifiers.
select asserts each entry as a safe SELECT expression; selectRaw does NOT — it interpolates verbatim, so never pass user input to it.
The raw escape hatches selectRaw, whereRaw, orderByRaw inject their SQL verbatim and are trusted-input only; pass dynamic values through their bindings argument, never by concatenation.

A few features are dialect-aware (row locks are no-ops on SQLite; random ordering is RAND() on MySQL and RANDOM() elsewhere; date-part extraction differs per engine).

Example

// Read: where → order → limit → fetch
const users = await DB.table('users')
  .where('active', true)
  .where('age', '>=', 18)
  .orderBy('created_at', 'desc')
  .limit(10)
  .get();

// Write
await DB.table('users').insert({ email: 'a@example.com', active: true });
await DB.table('users').where('id', 1).update({ active: false });

Extended by

ModelQueryBuilder

Constructors

Constructor

new QueryBuilder(table, sql): QueryBuilder

Defined in: packages/orm/src/db/QueryBuilder.ts:358

Parameters

table

string

sql

SQLInstance

Returns

QueryBuilder

Aggregates

count()

count(): Promise<number>

Defined in: packages/orm/src/db/QueryBuilder.ts:1374

COUNT(*) over the current query, ignoring any select list. Returns 0 when there are no rows.

Counts what the query returns. A grouped query returns one row per group, so groupBy("country").count() is the number of countries, not the number of rows — the previous implementation dropped the grouping and answered 1, which also made paginate() report total: 1 beside two rows of data. DISTINCT is likewise honoured rather than forced off. Those cases count through a subquery, which is the only form that gets both right.

ORDER BY is always dropped: it cannot change a count, and retaining it makes the count query illegal under PostgreSQL and MySQL's ONLY_FULL_GROUP_BY — so orderBy(...).paginate(), the most common call in the framework, could not run there at all.

Returns

Promise<number>

sum()

sum(column): Promise<number>

Defined in: packages/orm/src/db/QueryBuilder.ts:1386

SUM(column), coerced to a number (0 when the sum is NULL/no rows).

Parameters

column

string

Returns

Promise<number>

Throws

When column is not a safe SQL identifier.

avg()

avg(column): Promise<number>

Defined in: packages/orm/src/db/QueryBuilder.ts:1400

AVG(column), coerced to a number (0 when NULL/no rows).

Parameters

column

string

Returns

Promise<number>

Throws

When column is not a safe SQL identifier.

min()

min(column): Promise<number>

Defined in: packages/orm/src/db/QueryBuilder.ts:1414

MIN(column), coerced to a number (0 when NULL/no rows).

Parameters

column

string

Returns

Promise<number>

Throws

When column is not a safe SQL identifier.

max()

max(column): Promise<number>

Defined in: packages/orm/src/db/QueryBuilder.ts:1428

MAX(column), coerced to a number (0 when NULL/no rows).

Parameters

column

string

Returns

Promise<number>

Throws

When column is not a safe SQL identifier.

exists()

exists(): Promise<boolean>

Defined in: packages/orm/src/db/QueryBuilder.ts:1442

Whether at least one row matches. Runs SELECT 1 … LIMIT 1 and restores the previous limit/select state afterward.

Returns

Promise<boolean>

doesntExist()

doesntExist(): Promise<boolean>

Defined in: packages/orm/src/db/QueryBuilder.ts:1457

Inverse of exists — true when no rows match.

Returns

Promise<boolean>

Execution

clone()

clone(): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1259

Deep-copy this builder so repeated paginated reads (chunk, cursor, …) do not mutate the original. Subclasses override _newInstance() to preserve their own state.

Returns

this

get()

get<T>(): Promise<T[]>

Defined in: packages/orm/src/db/QueryBuilder.ts:1286

Compile and execute the SELECT, returning all matching rows.

Type Parameters

T

T = Record<string, unknown>

Returns

Promise<T[]>

The result rows (plain records, or model instances under the model query builder).

first()

first<T>(): Promise<T | null>

Defined in: packages/orm/src/db/QueryBuilder.ts:1296

Execute the SELECT with LIMIT 1 and return the first row, or null when none match. Restores any previously-set limit afterward.

Type Parameters

T

T = Record<string, unknown>

Returns

Promise<T | null>

pluck()

pluck<V>(column, key?): Promise<Record<string, V> | V[]>

Defined in: packages/orm/src/db/QueryBuilder.ts:1314

Return an array of a single column's values. Pass key to return an object keyed by that column instead.

Type Parameters

V

V = unknown

Parameters

column

string

key?

string

Returns

Promise<Record<string, V> | V[]>

Example

await DB.table('users').pluck('email');            // ['a@x', 'b@y']
await DB.table('users').pluck('name', 'id');       // { 1: 'Al', 2: 'Bo' }

value()

value<V>(column): Promise<V | null>

Defined in: packages/orm/src/db/QueryBuilder.ts:1344

Return a single column's value from the first matching row, or null.

Type Parameters

V

V = unknown

Parameters

column

string

Returns

Promise<V | null>

sole()

sole<T>(): Promise<T>

Defined in: packages/orm/src/db/QueryBuilder.ts:1466

Return the single matching row, asserting uniqueness.

Type Parameters

T

T = Record<string, unknown>

Returns

Promise<T>

Throws

When zero rows match, or when more than one row matches.

chunk()

chunk<T>(size, callback): Promise<void>

Defined in: packages/orm/src/db/QueryBuilder.ts:1602

Process results in fixed-size pages (offset-based). Return false from the callback to stop early. Memory-safe for large tables.

Type Parameters

T

T = Record<string, unknown>

Parameters

size

number

callback

(rows, page) => unknown

Returns

Promise<void>

chunkById()

chunkById<T>(size, callback, column?): Promise<void>

Defined in: packages/orm/src/db/QueryBuilder.ts:1626

Like chunk() but pages by an incrementing key (keyset). Stable when rows are inserted/deleted during iteration. column defaults to id.

Type Parameters

T

T = Record<string, unknown>

Parameters

size

number

callback

(rows) => unknown

column?

string = "id"

Returns

Promise<void>

lazy()

lazy<T>(size?): AsyncGenerator<T>

Defined in: packages/orm/src/db/QueryBuilder.ts:1652

Async generator yielding one row at a time (offset-paged internally).

Type Parameters

T

T = Record<string, unknown>

Parameters

size?

number = 1000

Returns

AsyncGenerator<T>

lazyById()

lazyById<T>(size?, column?): AsyncGenerator<T>

Defined in: packages/orm/src/db/QueryBuilder.ts:1672

Async generator yielding one row at a time (keyset-paged on column, default id).

Type Parameters

T

T = Record<string, unknown>

Parameters

size?

number = 1000

column?

string = "id"

Returns

AsyncGenerator<T>

cursor()

cursor<T>(size?): AsyncGenerator<T>

Defined in: packages/orm/src/db/QueryBuilder.ts:1693

Alias of lazy — stream rows one at a time.

Type Parameters

T

T = Record<string, unknown>

Parameters

size?

number = 1000

Returns

AsyncGenerator<T>

each()

each<T>(callback, size?): Promise<void>

Defined in: packages/orm/src/db/QueryBuilder.ts:1702

Invoke callback for each row, streaming in pages. Return false from the callback to stop early.

Type Parameters

T

T = Record<string, unknown>

Parameters

callback

(row, index) => unknown

size?

number = 1000

Returns

Promise<void>

toSql()

toSql(): string

Defined in: packages/orm/src/db/QueryBuilder.ts:1719

Compiled SELECT SQL with ? placeholders. Does not execute.

Returns

string

toSqlWithBindings()

toSqlWithBindings(): object

Defined in: packages/orm/src/db/QueryBuilder.ts:1727

{ sql, bindings } for the current SELECT. Does not execute.

Returns

object

sql

sql: string

bindings

bindings: unknown[]

toRawSql()

toRawSql(): string

Defined in: packages/orm/src/db/QueryBuilder.ts:1736

SQL with bindings inlined as literals — for logging only. The result is not safe to execute (values are not re-escaped for a driver).

Returns

string

dump()

dump(): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1747

Log the compiled SQL and bindings to the console, then return the builder for continued chaining.

Returns

this

dd()

dd(): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1759

Alias of dump. Note: despite the conventional dd() name, this does NOT dump-and-die — it logs and returns for chaining.

Returns

this

explain()

explain<T>(): Promise<T[]>

Defined in: packages/orm/src/db/QueryBuilder.ts:1768

Run the query plan for the current SELECT and return the plan rows — EXPLAIN QUERY PLAN on SQLite, EXPLAIN elsewhere.

Type Parameters

T

T = Record<string, unknown>

Returns

Promise<T[]>

Insert / update / delete

insert()

insert(data): Promise<void>

Defined in: packages/orm/src/db/QueryBuilder.ts:1487

Insert a single row. Object keys become columns (each asserted); values are bound. An empty object is a no-op. Ignores any WHERE clauses on the builder.

Parameters

data

Record<string, unknown>

Returns

Promise<void>

Throws

When any column key is not a safe SQL identifier.

Example

await DB.table('users').insert({ email: 'a@example.com', active: true });

update()

update(data): Promise<void>

Defined in: packages/orm/src/db/QueryBuilder.ts:1513

UPDATE … SET … for rows matching the current WHERE clauses. Object keys become assigned columns (each asserted); values are bound. An empty object is a no-op.

Parameters

data

Record<string, unknown>

Returns

Promise<void>

Throws

When any column key is not a safe SQL identifier.

Example

await DB.table('users').where('id', 1).update({ active: false });

updateOrInsert()

updateOrInsert(attributes, values?): Promise<boolean>

Defined in: packages/orm/src/db/QueryBuilder.ts:1537

Update rows matching attributes; insert a merged { ...attributes, ...values } row when none exist.

Parameters

attributes

Record<string, unknown>

values?

Record<string, unknown> = {}

Returns

Promise<boolean>

true when a row was inserted, false when an existing row was updated.

delete()

delete(): Promise<void>

Defined in: packages/orm/src/db/QueryBuilder.ts:1558

DELETE FROM … for rows matching the current WHERE clauses.

Returns

Promise<void>

Remarks

With no WHERE clauses this deletes every row in the table.

increment()

increment(column, amount?): Promise<void>

Defined in: packages/orm/src/db/QueryBuilder.ts:1571

Atomically add amount (default 1) to column for matching rows (SET column = column + ?).

Parameters

column

string

amount?

number = 1

Returns

Promise<void>

Throws

When column is not a safe SQL identifier.

decrement()

decrement(column, amount?): Promise<void>

Defined in: packages/orm/src/db/QueryBuilder.ts:1586

Atomically subtract amount (default 1) from column for matching rows (SET column = column - ?).

Parameters

column

string

amount?

number = 1

Returns

Promise<void>

Throws

When column is not a safe SQL identifier.

Joins

join()

join(table, first, operator, second): this

Defined in: packages/orm/src/db/QueryBuilder.ts:991

INNER JOIN table ON first <operator> second. The table and both columns are asserted; the operator is checked against the column-comparison set.

Parameters

table

string

first

string

operator

string

second

string

Returns

this

Throws

When an identifier is unsafe or the operator is unsupported.

Example

DB.table('users').join('orders', 'users.id', '=', 'orders.user_id');

leftJoin()

leftJoin(table, first, operator, second): this

Defined in: packages/orm/src/db/QueryBuilder.ts:999

LEFT JOIN. See join.

Parameters

table

string

first

string

operator

string

second

string

Returns

this

Throws

When an identifier is unsafe or the operator is unsupported.

rightJoin()

rightJoin(table, first, operator, second): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1007

RIGHT JOIN. See join.

Parameters

table

string

first

string

operator

string

second

string

Returns

this

Throws

When an identifier is unsafe or the operator is unsupported.

crossJoin()

crossJoin(table): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1015

CROSS JOIN table. Only the table name is asserted.

Parameters

table

string

Returns

this

Throws

When table is not a safe SQL identifier.

joinSub()

joinSub(sub, alias, first, operator, second, type?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1040

Join a subquery, aliased. Pass a builder (or a callback that populates one) plus an alias and the join condition. The subquery's bindings are merged into the parent query.

Parameters

sub

QueryBuilder | ((q) => void)

alias

string

first

string

operator

string

second

string

type?

JoinType = "inner"

Returns

this

Remarks

The alias, first and second are asserted as safe identifiers and operator against the allowed column-comparison set — the same guards join applies. Only the subquery's own SQL comes from the passed builder.

Example

qb.joinSub(
  DB.table('orders').selectRaw('user_id, COUNT(*) c').groupBy('user_id'),
  'o', 'users.id', '=', 'o.user_id',
);

Ordering & grouping

orderBy()

orderBy(column, direction?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:870

Order by a column. The column must be a safe identifier — for raw SQL expressions (e.g. RANDOM()) use orderByRaw.

Parameters

column

string

direction?

OrderDirection = "asc"

'asc' (default) or 'desc', any casing.

Returns

this

Throws

When column is not a safe SQL identifier, or direction is neither asc nor desc — the OrderDirection type is a compile-time hint, and a direction read off a query string arrives as an unchecked string.

orderByDesc()

orderByDesc(column): this

Defined in: packages/orm/src/db/QueryBuilder.ts:882

Order descending by a column. Shorthand for orderBy(column, 'desc').

Parameters

column

string

Returns

this

Throws

When column is not a safe SQL identifier.

desc()

desc(column?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:891

Order descending by a column (default the primary key id).

Parameters

column?

string = "id"

Returns

this

Throws

When column is not a safe SQL identifier.

asc()

asc(column?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:900

Order ascending by a column (default the primary key id).

Parameters

column?

string = "id"

Returns

this

Throws

When column is not a safe SQL identifier.

latest()

latest(column?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:909

Order newest-first by a timestamp column (default created_at).

Parameters

column?

string = "created_at"

Returns

this

Throws

When column is not a safe SQL identifier.

oldest()

oldest(column?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:918

Order oldest-first by a timestamp column (default created_at).

Parameters

column?

string = "created_at"

Returns

this

Throws

When column is not a safe SQL identifier.

inRandomOrder()

inRandomOrder(): this

Defined in: packages/orm/src/db/QueryBuilder.ts:926

Randomise row order — RAND() on MySQL, RANDOM() on SQLite/Postgres.

Returns

this

reorder()

reorder(column?, direction?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:935

Clear all ORDER BY clauses; optionally apply a new one.

Parameters

column?

string

direction?

OrderDirection = "asc"

Returns

this

Throws

When a replacement column is given and is unsafe.

groupBy()

groupBy(...columns): this

Defined in: packages/orm/src/db/QueryBuilder.ts:946

Add columns to the GROUP BY clause. Each column is asserted.

Parameters

columns

...string[]

Returns

this

Throws

When any column is not a safe SQL identifier.

having()

having(column, operatorOrValue, value?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:966

Add a HAVING clause. The column may be a plain identifier or a simple aggregate such as SUM(score); the value is always bound. Two-arg form defaults the operator to =. Multiple HAVING clauses are joined with AND.

Parameters

column

string

operatorOrValue

unknown

value?

unknown

Returns

this

Throws

When column is neither a safe identifier nor a simple aggregate, or when the operator is unsupported.

Example

DB.table('orders').groupBy('user_id').having('SUM(total)', '>', 100);

union()

union(other, all?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1097

Append UNION (or UNION ALL when all is true) with another builder's compiled SELECT. The other query's bindings are merged.

Parameters

other

QueryBuilder

all?

boolean = false

Returns

this

unionAll()

unionAll(other): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1107

Append UNION ALL with another builder's SELECT. Shorthand for union(other, true).

Parameters

other

QueryBuilder

Returns

this

Other

_state

protected _state: QueryState

Defined in: packages/orm/src/db/QueryBuilder.ts:316

_sql

protected _sql: SQLInstance

Defined in: packages/orm/src/db/QueryBuilder.ts:317

_userWhereStart

protected _userWhereStart: number = 0

Defined in: packages/orm/src/db/QueryBuilder.ts:327

Index into _state.wheres at which caller-supplied predicates begin.

Anything before it was injected by the framework (currently the soft-delete predicate seeded in BaseModel.query()) and must stay outside the group built by _groupUserWheres. Defaults to 0 for a plain DB.table() builder, which has no framework predicates.

_newInstance()

protected _newInstance(): QueryBuilder

Defined in: packages/orm/src/db/QueryBuilder.ts:1274

Returns

QueryBuilder

_groupUserWheres()

protected _groupUserWheres(): void

Defined in: packages/orm/src/db/QueryBuilder.ts:2288

Returns

void

Pagination

limit()

limit(n): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1225

Cap the number of rows returned (LIMIT). The value is bound.

Parameters

n

number

Returns

this

offset()

offset(n): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1234

Skip a number of leading rows (OFFSET). The value is bound.

Parameters

n

number

Returns

this

paginate()

paginate<T>(perPage?, page?, pageName?): Promise<PaginateResult<T>>

Defined in: packages/orm/src/db/QueryBuilder.ts:1793

Offset-based pagination.

Runs COUNT then SELECT — count ignores LIMIT/OFFSET by temporarily clearing them; SELECT uses this.get() so subclass overrides (ModelQueryBuilder) get model instances and eager-load relations automatically.

Type Parameters

T

T = Record<string, unknown>

Parameters

perPage?

number = 15

Rows per page (clamped to ≥ 1).

page?

number

1-based page number. Omit it to use the request's current page (the ?page= query string, or a resolver a server-driven view registered).

pageName?

string = "page"

Which paginator to read when page is omitted, so one request can drive several independently. Defaults to "page".

Returns

Promise<PaginateResult<T>>

A PaginateResult with data, total, lastPage and URL helpers.

cursorPaginate()

cursorPaginate<T>(options?): Promise<CursorPaginateResult<T>>

Defined in: packages/orm/src/db/QueryBuilder.ts:1858

Cursor-based pagination using WHERE <column> > cursor ORDER BY <column> ASC (column defaults to id).

Avoids a COUNT(*) entirely — ideal for very large tables and infinite-scroll UIs. Fetches limit + 1 rows to detect whether a next page exists, trims the extra row, and sets nextCursor to the last returned id.

Results flow through this.get(), so a ModelQueryBuilder returns model instances (with eager-loaded relations), while a raw QueryBuilder returns plain rows.

Returns { data, nextCursor, prevCursor, hasMore }:

nextCursor — pass to the next call's cursor; null on the last page.
prevCursor — the cursor that produced the page before this one (the incoming cursor), or null on the first page.
hasMore — true when another page follows.

Defaults: { cursor: 0, limit: 15, column: 'id' }.

Type Parameters

T

T = Record<string, unknown>

Parameters

options?

cursor?

number

limit?

number

column?

string

Returns

Promise<CursorPaginateResult<T>>

Remarks

Like paginate, the builder's clause state is snapshotted and restored, so the query can be safely reused. column defaults to id; for non-numeric sort keys and opaque cursors, prefer keysetPaginate.

Throws

When column is not a safe SQL identifier.

simplePaginate()

simplePaginate<T>(perPage?, page?, pageName?): Promise<SimplePaginateResult<T>>

Defined in: packages/orm/src/db/QueryBuilder.ts:1923

"Simple" offset pagination — next/prev only, no COUNT(*).

Fetches perPage + 1 rows to detect whether another page follows, then trims the probe row. Use this instead of paginate() when you don't need a total row count or numbered page links (cheaper on large tables).

Results flow through this.get(), so a ModelQueryBuilder returns model instances. Returns a SimplePaginateResult with hasMorePages, page, and URL helpers — but no total or lastPage. Omit page to use the request's current page, exactly like paginate().

Type Parameters

T

T = Record<string, unknown>

Parameters

perPage?

number = 15

page?

number

pageName?

string = "page"

Returns

Promise<SimplePaginateResult<T>>

Example

const page = await Post.query().orderBy('id').simplePaginate(15);
page.hasMorePages;   // boolean
page.nextPageUrl();  // '?page=2' | null

keysetPaginate()

keysetPaginate<T>(options?): Promise<KeysetPaginateResult<T>>

Defined in: packages/orm/src/db/QueryBuilder.ts:1982

Keyset (cursor) pagination — scales to any table size with no offset cost.

Unlike cursorPaginate() this method:

Accepts any sort column (not just id).
Supports 'asc' and 'desc' ordering.
Returns an opaque base64 cursor that encodes the sort value of the last row, so clients cannot interpret or tamper with it.
Adds a secondary tiebreaker on the primary key when the sort column is not unique, ordered in the same direction as the primary sort, so page boundaries are stable.

The tiebreaker's direction is not cosmetic. The cursor predicate compares the tiebreaker with the primary sort's operator (> ascending, < descending), so an id ASC tiebreaker under a desc sort asked for rows before the ones just returned: within a tie group, page 1 emitted the lowest ids and page 2 then re-emitted them while the rest of the group became unreachable.

Type Parameters

T

T = Record<string, unknown>

Parameters

options?

KeysetOptions

Returns

Promise<KeysetPaginateResult<T>>

Example

// First page
const p1 = await db('posts').keysetPaginate({ column: 'created_at', direction: 'desc' });

// Next page — pass the opaque cursor directly
const p2 = await db('posts').keysetPaginate({
  column: 'created_at', direction: 'desc', cursor: p1.nextCursor,
});

Throws

When options.column is not a safe SQL identifier.

Raw

selectRaw()

selectRaw(expression): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1145

Add a raw SQL expression to the SELECT list.

Security: The expression is injected verbatim into the query — never interpolate user-controlled values directly. Build the expression from trusted constants only, and pass dynamic values via whereRaw() bindings.

Parameters

expression

string

Returns

this

Example

qb.selectRaw('COUNT(*) as total, MAX(score) as top')
qb.selectRaw('price * quantity as revenue')

whereRaw()

whereRaw(sql, bindings?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1159

Add a raw SQL WHERE clause. Bindings are passed as the second argument to avoid SQL injection.

Parameters

sql

string

bindings?

unknown[] = []

Returns

this

Example

qb.whereRaw('LOWER(email) = ?', ['alice@example.com'])
qb.whereRaw('score BETWEEN ? AND ?', [10, 50])

orWhereRaw()

orWhereRaw(sql, bindings?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1168

OR form of whereRaw. The SQL fragment is trusted-input only; pass dynamic values via bindings.

Parameters

sql

string

bindings?

unknown[] = []

Returns

this

orderByRaw()

orderByRaw(expression): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1216

Add a raw SQL ORDER BY expression.

Parameters

expression

string

Returns

this

Remarks

Injected verbatim — trusted-input only. Prefer orderBy for plain column ordering.

Example

qb.orderByRaw('RAND()')
qb.orderByRaw('created_at DESC, id ASC')

Select

from()

from(table): this

Defined in: packages/orm/src/db/QueryBuilder.ts:388

Change the table this query targets. Useful for subquery builders that start with an empty table.

Parameters

table

string

Table name; asserted as a safe identifier.

Returns

this

Throws

When table is not a safe SQL identifier.

select()

select(...columns): this

Defined in: packages/orm/src/db/QueryBuilder.ts:406

Add columns to the SELECT list. Called with no columns the query selects *. Repeated calls accumulate.

Parameters

columns

...string[]

Returns

this

Remarks

Each column is asserted as a safe SELECT entry (a column, table.*, or a simple aggregate, with an optional [AS] alias) — the same identifier guard the where/order builders use. For raw projection SQL, use selectRaw.

Throws

When a column is not a safe select expression.

distinct()

distinct(): this

Defined in: packages/orm/src/db/QueryBuilder.ts:417

Emit SELECT DISTINCT.

Returns

this

lockForUpdate()

lockForUpdate(): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1118

SELECT … FOR UPDATE — take an exclusive row lock. No-op on SQLite, which lacks row locks (the suffix is omitted from the compiled SQL).

Returns

this

sharedLock()

sharedLock(): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1128

Take a shared row lock — LOCK IN SHARE MODE on MySQL, FOR SHARE elsewhere. No-op on SQLite.

Returns

this

when()

when(condition, callback): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1248

Conditionally apply builder mutations: when condition is truthy, invoke callback(this, condition) and continue chaining. No otherwise branch.

Parameters

condition

unknown

callback

(q, value) => void

Returns

this

Example

DB.table('users').when(search, (q, s) => q.whereLike('name', `%${s}%`));

Where clauses

where()

Call Signature

where(column, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:435

Add an AND WHERE clause. Two-arg form defaults the operator to =; the three-arg form takes an explicit operator (=, !=, >, >=, <, <=, like, not like, in, not in). The column is asserted; the value is always bound.

Parameters

column

string

value

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

Example

DB.table('users').where('active', true).where('age', '>=', 18);

Call Signature

where(column, operator, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:436

Parameters

column

string

operator

WhereOperator

value

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

Example

DB.table('users').where('active', true).where('age', '>=', 18);

Call Signature

where(group): this

Defined in: packages/orm/src/db/QueryBuilder.ts:437

Parameters

group

(query) => void

Returns

this

Throws

When column is not a safe SQL identifier.

Example

DB.table('users').where('active', true).where('age', '>=', 18);

orWhere()

Call Signature

orWhere(column, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:459

Add an OR WHERE clause. Same operator/value semantics as where.

Parameters

column

string

value

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

Call Signature

orWhere(column, operator, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:460

Add an OR WHERE clause. Same operator/value semantics as where.

Parameters

column

string

operator

WhereOperator

value

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

Call Signature

orWhere(group): this

Defined in: packages/orm/src/db/QueryBuilder.ts:461

Add an OR WHERE clause. Same operator/value semantics as where.

Parameters

group

(query) => void

Returns

this

Throws

When column is not a safe SQL identifier.

whereIn()

whereIn(column, values): this

Defined in: packages/orm/src/db/QueryBuilder.ts:485

AND column IN (…). Each value is bound. An empty list compiles to a constant-false predicate (1 = 0) so no rows match.

Parameters

column

string

values

unknown[]

Returns

this

Throws

When column is not a safe SQL identifier.

orWhereIn()

orWhereIn(column, values): this

Defined in: packages/orm/src/db/QueryBuilder.ts:498

OR column IN (…). See whereIn.

Parameters

column

string

values

unknown[]

Returns

this

Throws

When column is not a safe SQL identifier.

whereNotIn()

whereNotIn(column, values): this

Defined in: packages/orm/src/db/QueryBuilder.ts:512

AND column NOT IN (…). An empty list compiles to a constant-true predicate (1 = 1) so all rows match.

Parameters

column

string

values

unknown[]

Returns

this

Throws

When column is not a safe SQL identifier.

orWhereNotIn()

orWhereNotIn(column, values): this

Defined in: packages/orm/src/db/QueryBuilder.ts:525

OR column NOT IN (…). See whereNotIn.

Parameters

column

string

values

unknown[]

Returns

this

Throws

When column is not a safe SQL identifier.

whereNull()

whereNull(column): this

Defined in: packages/orm/src/db/QueryBuilder.ts:538

AND column IS NULL.

Parameters

column

string

Returns

this

Throws

When column is not a safe SQL identifier.

orWhereNull()

orWhereNull(column): this

Defined in: packages/orm/src/db/QueryBuilder.ts:550

OR column IS NULL.

Parameters

column

string

Returns

this

Throws

When column is not a safe SQL identifier.

whereNotNull()

whereNotNull(column): this

Defined in: packages/orm/src/db/QueryBuilder.ts:562

AND column IS NOT NULL.

Parameters

column

string

Returns

this

Throws

When column is not a safe SQL identifier.

orWhereNotNull()

orWhereNotNull(column): this

Defined in: packages/orm/src/db/QueryBuilder.ts:574

OR column IS NOT NULL.

Parameters

column

string

Returns

this

Throws

When column is not a safe SQL identifier.

whereBetween()

whereBetween(column, range): this

Defined in: packages/orm/src/db/QueryBuilder.ts:588

AND column BETWEEN ? AND ? — both bounds bound as values.

Parameters

column

string

range

[unknown, unknown]

Returns

this

Throws

When column is not a safe SQL identifier.

orWhereBetween()

orWhereBetween(column, range): this

Defined in: packages/orm/src/db/QueryBuilder.ts:597

OR column BETWEEN ? AND ?.

Parameters

column

string

range

[unknown, unknown]

Returns

this

Throws

When column is not a safe SQL identifier.

whereNotBetween()

whereNotBetween(column, range): this

Defined in: packages/orm/src/db/QueryBuilder.ts:606

AND column NOT BETWEEN ? AND ?.

Parameters

column

string

range

[unknown, unknown]

Returns

this

Throws

When column is not a safe SQL identifier.

orWhereNotBetween()

orWhereNotBetween(column, range): this

Defined in: packages/orm/src/db/QueryBuilder.ts:615

OR column NOT BETWEEN ? AND ?.

Parameters

column

string

range

[unknown, unknown]

Returns

this

Throws

When column is not a safe SQL identifier.

whereColumn()

whereColumn(first, operatorOrSecond, second?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:639

Compare two columns instead of a column to a value: whereColumn('updated_at', '>', 'created_at'). With two arguments the operator defaults to =. Both column names are asserted.

Parameters

first

string

operatorOrSecond

string

second?

string

Returns

this

Throws

When either column is unsafe or the operator is unsupported.

orWhereColumn()

orWhereColumn(first, operatorOrSecond, second?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:648

OR form of whereColumn.

Parameters

first

string

operatorOrSecond

string

second?

string

Returns

this

Throws

When either column is unsafe or the operator is unsupported.

whereDate()

whereDate(column, operatorOrValue, value?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:674

Filter on the date portion of a timestamp column. Two-arg form defaults the operator to =. The date-extraction SQL is dialect-specific.

Parameters

column

string

operatorOrValue

unknown

value?

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

whereTime()

whereTime(column, operatorOrValue, value?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:682

Filter on the time portion of a timestamp column. See whereDate.

Parameters

column

string

operatorOrValue

unknown

value?

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

whereDay()

whereDay(column, operatorOrValue, value?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:690

Filter on the day-of-month component of a timestamp column.

Parameters

column

string

operatorOrValue

unknown

value?

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

whereMonth()

whereMonth(column, operatorOrValue, value?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:698

Filter on the month component of a timestamp column.

Parameters

column

string

operatorOrValue

unknown

value?

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

whereYear()

whereYear(column, operatorOrValue, value?): this

Defined in: packages/orm/src/db/QueryBuilder.ts:706

Filter on the year component of a timestamp column.

Parameters

column

string

operatorOrValue

unknown

value?

unknown

Returns

this

Throws

When column is not a safe SQL identifier.

whereLike()

whereLike(column, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:736

AND column LIKE ? — the pattern is bound as a value.

Parameters

column

string

value

string

Returns

this

Throws

When column is not a safe SQL identifier.

orWhereLike()

orWhereLike(column, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:746

OR column LIKE ?.

Parameters

column

string

value

string

Returns

this

Throws

When column is not a safe SQL identifier.

whereNotLike()

whereNotLike(column, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:756

AND column NOT LIKE ?.

Parameters

column

string

value

string

Returns

this

Throws

When column is not a safe SQL identifier.

orWhereNotLike()

orWhereNotLike(column, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:766

OR column NOT LIKE ?.

Parameters

column

string

value

string

Returns

this

Throws

When column is not a safe SQL identifier.

whereAny()

whereAny(columns, operator, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:779

Match when ANY of the columns satisfies the operator/value, as a parenthesised OR group. Each column is asserted and the value is bound once per column.

Parameters

columns

string[]

operator

string

value

unknown

Returns

this

Throws

When a column is unsafe or the operator is unsupported.

whereAll()

whereAll(columns, operator, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:788

Match when ALL of the columns satisfy the operator/value, as a parenthesised AND group.

Parameters

columns

string[]

operator

string

value

unknown

Returns

this

Throws

When a column is unsafe or the operator is unsupported.

whereExists()

whereExists(callback): this

Defined in: packages/orm/src/db/QueryBuilder.ts:824

AND EXISTS (subquery). The callback receives a fresh QueryBuilder to build the correlated subquery; its bindings are merged into the parent.

Parameters

callback

(q) => void

Returns

this

Example

DB.table('users').whereExists((q) =>
  q.from('orders').whereColumn('orders.user_id', 'users.id'));

orWhereExists()

orWhereExists(callback): this

Defined in: packages/orm/src/db/QueryBuilder.ts:831

OR EXISTS (subquery). See whereExists.

Parameters

callback

(q) => void

Returns

this

whereNotExists()

whereNotExists(callback): this

Defined in: packages/orm/src/db/QueryBuilder.ts:838

AND NOT EXISTS (subquery). See whereExists.

Parameters

callback

(q) => void

Returns

this

orWhereNotExists()

orWhereNotExists(callback): this

Defined in: packages/orm/src/db/QueryBuilder.ts:845

OR NOT EXISTS (subquery). See whereExists.

Parameters

callback

(q) => void

Returns

this

whereJson()

whereJson(column, value): this

Defined in: packages/orm/src/db/QueryBuilder.ts:1192

Filter by a JSONB/JSON column path value using the ->> text-extraction operator.

Accepts 'column->key' notation. Works natively on PostgreSQL (JSONB columns) and MySQL (JSON columns). For SQLite use whereRaw('json_extract(col, ?) = ?', …).

The column name and key must be safe SQL identifiers (letters, digits, _, .). Throws if either part fails validation — never pass user-controlled strings here.

Parameters

column

string

value

unknown

Returns

this

Throws

When the column or path fails identifier validation.

Example

DB.table('users').whereJson('preferences->theme', 'dark')
// WHERE preferences->>'theme' = ?