v0.21

query builder

introduction

dframework provides a fluent, chainable query builder that allows you to construct database queries. it protects against sql injection attacks by relying exclusively on prepared statements. you do not need to clean or sanitize bindings manually.

you begin a query builder chain by calling the table method on the globally available DB facade.

1const builder = DB.table('users');
2// builder: TableQuery. chainable, thenable, and async iterable.

methods overview

the query builder is built up by chaining constraint calls (which return the builder) and terminated by a terminal call (which returns a promise). the returns column below reflects the actual resolved value.

chainable constraint methods

each of these returns the same TableQuery instance for chaining.

method arguments
from(table) target table name or alias; updates source table for builder
select(...columns) column names; replaces the current select list
selectRaw(expression, bindings?) raw sql fragment plus optional bindings
selectSub(query, as) scalar subquery expression with alias
distinct(...columns?) mark distinct, optionally replacing the select list
where(column, operator?, value?) column name/value, { column: value } object, or closure (q) => ...
whereNot(...) negated form of where (supports closure)
orWhere(...) or form of where (supports closure)
orWhereNot(...) or form of whereNot (supports closure)
whereIn(column, values) column name plus Array of values or subquery closure (q) => ...
whereNotIn(column, values) negated whereIn (supports subquery closure)
orWhereIn(...) / orWhereNotIn(...) or forms of the IN clauses
whereExists(callback) / whereNotExists(callback) exists / not exists subquery check
orWhereExists(callback) / orWhereNotExists(callback) or forms of exists checks
whereNull(column) / whereNotNull(column) null check
orWhereNull(column) / orWhereNotNull(column) or forms of null checks
whereBetween(column, [min, max]) / whereNotBetween(...) range check, requires exactly two values
orWhereBetween(...) / orWhereNotBetween(...) or forms of range checks
whereColumn(column, operator?, otherColumn) compare two columns in the same row
orWhereColumn(column, operator?, otherColumn) or form of column comparison
whereRaw(sql, bindings?) raw sql where condition with optional bindings
orWhereRaw(sql, bindings?) raw sql or where condition with optional bindings
whereHashed(column, operator?, value) / orWhereHashed(...) compare against the fast hash of a value
when(value, callback, defaultCallback?) apply callback if value is truthy
unless(value, callback, defaultCallback?) apply callback if value is falsy
join(table, first, operator?, second) inner join on another table (supports join closure)
leftJoin(table, first, operator?, second) left outer join on another table (supports join closure)
rightJoin(table, first, operator?, second) right outer join on another table (supports join closure)
crossJoin(table) cross join on another table
joinRaw(expression, bindings?) raw join clause with parameter bindings
groupBy(...columns) grouping columns
having(column, operator?, value?) having condition on grouped results
orHaving(column, operator?, value?) or form of having condition on grouped results
havingRaw(sql, bindings?) raw sql having condition with optional bindings
orHavingRaw(sql, bindings?) raw sql or having condition with optional bindings
orderBy(column, direction='ASC') sort, direction is 'ASC' or 'DESC'
orderByRaw(sql) raw sql order by expression
limit(n) / offset(n) numeric row cap and skip count
clone() produces an isolated clone of the query builder instance
setHashFields(fields) override which columns are autohashed on this builder

terminal methods

these return a promise and execute the underlying query (or return the compiled query string without execution).

method arguments returns
toSql() none string (compiled select sql string without executing)
get() none Promise<Array<object>> of matching rows; [] when none match
first(where?) optional { column: value } object Promise<object|null> (first matching row, or null)
firstWhere(col, op?, val?) column name, operator/value, optional value Promise<object|null> (first matching row, or null)
latest(count?, column?) null or number; optional column name null count: Promise<object|null>; numeric count: Promise<Array<object>>
exists() none Promise<boolean> whether any matching rows exist
doesntExist() none Promise<boolean> whether no matching rows exist
count(column?) optional column name (defaults to *) Promise<number> (matching row count; group by returns the number of groups)
sum(column) column name Promise<number> (sum of values; 0 when no rows)
avg(column) column name Promise<number|null> (average value; null when no rows)
min(column) column name Promise<any> (minimum value; null when no rows)
max(column) column name Promise<any> (maximum value; null when no rows)
pluck(column) column name Promise<Array> of that column's values across the matching rows (empty [] when none)
getWithCount() none Promise<{ rows: Array<object>, total: number }>; rows have the internal _total_count field stripped
paginate(perPage?, pageName?) perPage number (default 10); optional pageName query param string (default 'page') Promise<Paginator> instance containing items, total count, and navigation links
insert(data) row object, or array of row objects single row: { insertId, affectedRows, changedRows }; array of rows: bulk insert header with affectedRows and insertId
update(data, where?) column/value object; optional where object Promise<object> { affectedRows, changedRows, insertId }
increment(column, amount?, extra?, where?) column name; optional numeric amount (default 1); optional extra column/values object; optional where object Promise<object> { affectedRows, changedRows, insertId }
decrement(column, amount?, extra?, where?) column name; optional numeric amount (default 1); optional extra column/values object; optional where object Promise<object> { affectedRows, changedRows, insertId }
save(data) alias for update(data) Promise<object> { affectedRows, changedRows, insertId }
delete(where?) optional where object (or chained where) Promise<object> { affectedRows, changedRows, insertId }; throws when no where is set

the builder itself is thenable and async iterable, so you can await DB.table('users') or for await (const row of DB.table('users')) directly. both forms execute the select and consume the same Array<object> shape.

retrieving results

getting all rows

the get method executes the query and returns an array of result objects.

1const users = await DB.table('users').get();
2// users: Array<object>. empty [] when no rows match.

the query builder is also an async iterable, allowing you to iterate directly over the builder instance without calling get.

1for await (const user of DB.table('users')) {
2  console.log(user.name);
3}
4// yields plain row objects (not model instances).

getting a single row

if you only need to retrieve a single row, use the first method. it returns the object directly instead of wrapping it in an array.

1const user = await DB.table('users').where('email', 'tarou@example.com').first();
2// user: object | null. null when no row matches.

latest rows

the latest method orders rows by a column descending and returns either a single row or an array. it is shorthand for orderBy(column, 'DESC').limit(n).get().

1const newest = await DB.table('posts').latest();
2// newest: object | null
3const recent = await DB.table('posts').latest(10);
4// recent: Array<object>
5const recentByPublished = await DB.table('posts').latest(10, 'published_at');

calling latest() with no arguments orders by created_at desc and returns the single most recent row (or null when none match). passing a numeric count returns that many rows as an array. the column defaults to created_at but may be overridden with the second argument.

plucking values

if you want to retrieve a flat array containing the values of a single column, use the pluck method.

1const titles = await DB.table('posts').pluck('title');
2// titles: Array. one entry per matching row, in the order returned by the database.

aggregates

the query builder provides helper methods for aggregating data: count, sum, avg, min, and max.

1const totalOrders = await DB.table('orders').where('status', 'pending').count();
2// totalOrders: number. 0 when no rows match.
3 
4const totalRevenue = await DB.table('orders').where('status', 'completed').sum('amount');
5// totalRevenue: number. 0 when no rows match.
6 
7const avgPrice = await DB.table('products').where('category_id', 4).avg('price');
8// avgPrice: number | null. null when no rows match.
9 
10const cheapest = await DB.table('products').min('price');
11const priciest = await DB.table('products').max('price');

all aggregates automatically respect joined tables, table aliases, and where constraints:

1const totalDuration = await DB.table('track_interactions')
2  .join('tracks', 'track_interactions.track_id', '=', 'tracks.id')
3  .where('track_interactions.user_id', 1)
4  .where('track_interactions.type', 'like')
5  .sum('tracks.duration');

selects

by default, the query builder selects all columns. to specify exact columns, use the select method.

1const users = await DB.table('users').select('id', 'name', 'email').get();
2// users: Array<object>. each row only carries the requested columns.

if you need to insert a raw sql expression into the select clause, use the selectRaw method alongside any bindings.

1const users = await DB.table('users')
2  .selectRaw('COUNT(id) as total, status')
3  .groupBy('status')
4  .get();
5// users: Array<object>. each row has { total, status }.

to force the query to return only distinct results, use the distinct method. call it with column names to also set the select list.

1const activeRoles = await DB.table('users').distinct('role').get();
2// activeRoles: Array<object>. each row has the distinct role values.

subquery selects

you can add subqueries into your select clause using the selectSub method. it accepts either a closure or a query builder instance, and an alias name.

1const users = await DB.table('users')
2  .select('id', 'name')
3  .selectSub(q => {
4    q.from('orders').selectRaw('COUNT(*)').whereColumn('orders.user_id', 'users.id');
5  }, 'orders_count')
6  .get();

where clauses

basic where clauses

the where method accepts three arguments: the column name, the operator, and the value. if you omit the operator, the builder assumes equality.

1await DB.table('users').where('votes', '=', 100).get();
2await DB.table('users').where('votes', 100).get();
3await DB.table('users').where('votes', '>=', 100).get();
4await DB.table('users').where('name', 'LIKE', '%test%').get();

the whereNot method negates the condition.

1await DB.table('users').whereNot('status', 'inactive').get();

you can also pass an object to apply multiple equality conditions simultaneously.

1await DB.table('users').where({
2  status: 'active',
3  role: 'admin'
4}).get();

logical grouping (where closures)

to create complex grouped boolean expressions (nested parenthesis), pass a closure to where, whereNot, orWhere, or orWhereNot. the closure receives a query builder instance to nest your constraints.

1const users = await DB.table('users')
2  .where('active', 1)
3  .where(q => {
4    q.where('role', 'admin')
5     .orWhere('role', 'superadmin');
6  })
7  .get();
8// compiles to: WHERE `active` = ? AND (`role` = ? OR `role` = ?)

you can combine nested closures with whereNot or orWhereNot to wrap expressions in NOT (...):

1const users = await DB.table('users')
2  .where('tenant_id', 1)
3  .whereNot(q => {
4    q.where('status', 'banned')
5     .orWhere('suspended', 1);
6  })
7  .get();

or statements

use the orWhere method to chain clauses with a logical or operator.

1await DB.table('users')
2  .where('votes', '>', 100)
3  .orWhere('name', 'tarou')
4  .get();

the orWhereNot method is also available for negated or conditions.

additional where clauses

the query builder provides specialized methods for common condition types.

whereIn / whereNotIn verifies that a given column's value is contained within an array, or matches a subquery closure.

1await DB.table('users').whereIn('id', [1, 2, 3]).get();
2// when the array is empty, whereIn compiles to `0 = 1` (no rows)
3// and whereNotIn compiles to `1 = 1` (all rows).

subqueries in where in

you can pass a closure callback to whereIn and whereNotIn to construct subquery filters:

1const customersWithOrders = await DB.table('users')
2  .whereIn('id', q => {
3    q.from('orders').select('user_id').where('status', 'paid');
4  })
5  .get();
6// compiles to: WHERE `id` IN (SELECT `user_id` FROM `orders` WHERE `status` = ?)

whereNull / whereNotNull / orWhereNull / orWhereNotNull verifies that the value of a column is or is not null.

1await DB.table('users').whereNull('deleted_at').get();
2await DB.table('users').where('status', 'active').orWhereNull('archived_at').get();
3await DB.table('users').whereNotNull('verified_at').orWhereNotNull('confirmed_at').get();

whereBetween / whereNotBetween / orWhereBetween / orWhereNotBetween verifies that a column's value lies within two bounds. you must provide an array with exactly two values. both values must be defined (use null rather than undefined to keep a bound as sql null).

1await DB.table('users').whereBetween('votes', [1, 100]).get();
2await DB.table('orders')
3  .whereBetween('total', [50, 200])
4  .orWhereBetween('total', [500, 1000])
5  .get();

whereRaw / orWhereRaw injects a raw sql where clause into the query using parameterized bindings:

1await DB.table('orders')
2  .whereRaw('price > IF(state = "tx", ?, 100)', [200])
3  .orWhereRaw('status = ?', ['pending'])
4  .get();

where exists and where not exists

use whereExists and whereNotExists (or their orWhereExists / orWhereNotExists counterparts) to write EXISTS (SELECT ...) subqueries:

1const users = await DB.table('users')
2  .whereExists(q => {
3    q.from('orders')
4      .whereColumn('orders.user_id', 'users.id')
5      .where('orders.total', '>', 100);
6  })
7  .get();

column comparisons

use the whereColumn and orWhereColumn methods to compare the values of two different columns within the same row.

1await DB.table('users').whereColumn('updated_at', '>', 'created_at').get();
2 
3await DB.table('users')
4  .where('status', 'active')
5  .orWhereColumn('last_login_at', '>', 'created_at')
6  .get();

conditional clauses (when and unless)

sometimes you want query clauses to apply only when a given condition is true. use when and unless to conditionally modify a query without breaking the method chain.

the when method executes the given closure if the first argument evaluates to truthy. an optional third closure executes if the condition is falsy.

1const role = req.query?.role;
2const sortBy = req.query?.sort;
3 
4const users = await DB.table('users')
5  .when(role, (q, val) => q.where('role', val))
6  .when(sortBy, (q, val) => q.orderBy(val, 'ASC'), q => q.orderBy('id', 'DESC'))
7  .get();

the unless method operates as the inverse: it executes the closure when the first argument is falsy.

1const users = await DB.table('users')
2  .unless(includeInactive, q => q.where('active', 1))
3  .get();

joins

the query builder supports joining multiple tables using inner joins, left/right outer joins, cross joins, and raw join expressions.

inner join

to perform a basic inner join, call the join method. you can specify the target table, the first column, an optional operator (defaults to =), and the second column.

1const users = await DB.table('users')
2  .join('contacts', 'users.id', '=', 'contacts.user_id')
3  .select('users.*', 'contacts.phone')
4  .get();

when only three arguments are passed, the operator defaults to =:

1await DB.table('users')
2  .join('contacts', 'users.id', 'contacts.user_id')
3  .get();

left join / right join

to perform a LEFT JOIN or RIGHT JOIN, use leftJoin or rightJoin:

1const tracks = await DB.table('tracks')
2  .leftJoin('genres', 'tracks.genre_id', '=', 'genres.id')
3  .select('tracks.*', 'genres.name as genre_name')
4  .get();

you can chain multiple joins together to traverse relationships:

1const tracks = await DB.table('tracks')
2  .leftJoin('track_artists', 'tracks.id', '=', 'track_artists.track_id')
3  .leftJoin('artists', 'track_artists.artist_id', '=', 'artists.id')
4  .select('tracks.*')
5  .orderBy('artists.name', 'ASC')
6  .get();

table aliases are supported in the table argument:

1await DB.table('tracks')
2  .leftJoin('artists as a', 'tracks.artist_id', '=', 'a.id')
3  .get();

advanced join clauses

if you need to specify multiple join conditions or combine ON clauses with WHERE clauses on the joined table, pass a closure callback as the second argument to join, leftJoin, or rightJoin:

1const users = await DB.table('users')
2  .join('contacts', j => {
3    j.on('users.id', '=', 'contacts.user_id')
4     .where('contacts.primary', '=', 1)
5     .whereNull('contacts.deleted_at');
6  })
7  .get();
8// compiles to: INNER JOIN `contacts` ON `users`.`id` = `contacts`.`user_id` AND `contacts`.`primary` = ? AND `contacts`.`deleted_at` IS NULL

join clauses support on, orOn, where, orWhere, whereNull, and whereNotNull.

cross join

to perform a cartesian product, use the crossJoin method:

1const combos = await DB.table('sizes')
2  .crossJoin('colors')
3  .get();

raw joins

for complex join conditions or subqueries in joins, use joinRaw:

1await DB.table('users')
2  .joinRaw('LEFT JOIN contacts ON contacts.user_id = users.id AND contacts.status = ?', ['active'])
3  .get();

ordering, grouping, and limits

the orderBy method sorts the result set. the second argument specifies the direction, accepting either ASC or DESC.

1await DB.table('users')
2  .orderBy('name', 'DESC')
3  .get();

the groupBy method accepts one or more column names to group the results.

1await DB.table('users')
2  .groupBy('account_id', 'status')
3  .get();

the having and havingRaw methods filter grouped results. use orHaving and orHavingRaw for logical or conditions:

1await DB.table('orders')
2  .groupBy('account_id')
3  .having('total', '>', 500)
4  .orHaving('item_count', '>=', 10)
5  .get();
6 
7await DB.table('orders')
8  .groupBy('account_id')
9  .havingRaw('SUM(price) > ?', [2500])
10  .orHavingRaw('COUNT(id) > ?', [50])
11  .get();

the orderByRaw method allows arbitrary sorting expressions:

1await DB.table('users')
2  .orderByRaw('FIELD(status, "active", "pending", "banned")')
3  .get();

the limit and offset methods restrict the number of records returned and specify the starting point.

1await DB.table('users')
2  .offset(10)
3  .limit(5)
4  .get();

inserts

the insert method accepts an object of column and value pairs to insert into the database.

1const result = await DB.table('users').insert({
2  email: 'tarou@example.com',
3  name: 'tarou'
4});
5// result: { insertId, affectedRows, changedRows }
6// use result.insertId to read the assigned auto increment id.

bulk inserts

if you pass an array of objects to the insert method, the query builder will execute a single, highly optimized bulk insert statement and return the normalized mutation header.

1const result = await DB.table('users').insert([
2  { email: 'tarou@example.com', name: 'tarou' },
3  { email: 'satou@example.com', name: 'satou' }
4]);
5// result: { affectedRows, changedRows, insertId }
6// result.affectedRows indicates total inserted rows.
7// result.insertId indicates the first inserted auto increment id.

updates

the update method updates existing records. it accepts an object containing the columns to modify and their new values. it affects any records matching the previously chained where clauses.

1const result = await DB.table('users')
2  .where('id', 1)
3  .update({ votes: 1 });
4// result: { affectedRows, changedRows, insertId: 0 }
5// result.affectedRows tells you how many rows actually changed.

update() also honors chained .limit(n) clauses to constrain updates to a specific batch of rows:

1await DB.table('users')
2  .where('status', 'pending')
3  .limit(100)
4  .update({ status: 'processing' });

the save method acts as an alias for update and returns the same mutation header.

increment and decrement

the query builder also provides convenient methods for incrementing or decrementing the value of a numeric column. both methods accept at least one argument: the column to modify. a second argument may be passed to control the amount by which the column should be incremented or decremented (defaults to 1). an optional third argument allows passing an object of extra columns to update in the same statement.

1// increment votes by 1
2await DB.table('users').where('id', 1).increment('votes');
3 
4// increment credits by 5
5await DB.table('users').where('id', 1).increment('credits', 5);
6 
7// increment credits by 10 and update a timestamp in the same statement
8await DB.table('users').where('id', 1).increment('credits', 10, { updated_at: '2026-09-14' });
9 
10// decrement credits by 2
11await DB.table('users').where('id', 1).decrement('credits', 2);

deletes

the delete method removes records from the table. delete() supports chained .limit(n) clauses to constrain deletions to a specific batch size.

IMPORTANT

at least one where condition is required before executing `delete()`. calling `delete()` without constraints throws an exception to prevent accidental table truncation.

1const result = await DB.table('users')
2  .where('status', 'inactive')
3  .limit(500)
4  .delete();
5// result: { affectedRows, changedRows, insertId: 0 }
6// result.affectedRows tells you how many rows were removed.
NOTE

`update()` and `delete()` also support `.join()` clauses. note that mysql prohibits combining `LIMIT` with multi table `JOIN` operations in `UPDATE` or `DELETE` statements (doing so throws a diagnostic error). on postgresql and sqlite, the grammar compiler automatically compiles mutations that combine limits or joins into subquery `IN` expressions against the table's primary key (`WHERE id IN (SELECT id FROM ...)`), ensuring consistent behavior across all database engines.

cloning queries

the clone method creates an independent deep copy of the query builder instance. the cloned query inherits all select columns, where clauses, join definitions, group by fields, having clauses, order criteria, limit/offset parameters, bindings, and hash configurations. modifying the cloned instance does not affect the original query.

1const activeUsers = DB.table('users').where('status', 'active');
2 
3// branch query to compute total count without altering base query
4const totalActive = await activeUsers.clone().count();
5 
6// branch query to fetch a paginated slice
7const pageResults = await activeUsers.clone().limit(20).offset(0).get();

auto hashing

the query builder is aware of sensitive columns and automatically hashes their values using bcrypt during inserts and updates. by default, only columns named password trigger this behavior.

you can declare application wide auto hashed fields in config/app.js using app.hashFields (or the HASH_FIELDS environment variable as a comma separated list), or override them for a specific query using setHashFields.

1await DB.table('tokens')
2  .setHashFields(['api_key'])
3  .insert({ api_key: 'plain-text-key' });
4// returns the single row insert result ({ insertId, affectedRows, changedRows }).

if you need to hash an arbitrary value without relying on automatic column configuration, use the Hash facade exported from dframework.

1import { Hash } from 'dframework';
2 
3// uses slow, secure bcrypt (for passwords)
4await DB.table('users').insert({
5  custom_secret: await Hash.make('plain-text-password')
6});
7 
8// uses fast, peppered sha256 (for indexable tokens)
9await DB.table('tokens').insert({
10  token_hash: Hash.fast('plain-text-token')
11});

you can also perform direct where comparisons against plaintext values if the column is configured as a hash field, using the whereHashed and orWhereHashed methods.

1const token = await DB.table('tokens').whereHashed('secret', 'plain-text-key').first();
2// token: object | null. whereHashed hashes the value with sha256
3// and compares it against the stored hash.