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.

terminal methods

these return a promise and execute the underlying query.

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 verifies that the value of a column is or is not null.

1await DB.table('users').whereNull('deleted_at').get();

whereBetween / whereNotBetween 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();

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 method to compare the values of two different columns within the same row.

1await DB.table('users').whereColumn('updated_at', '>', 'created_at').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 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: mysql2 ResultSetHeader (single row)
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 an array of generated ids.

1const ids = await DB.table('users').insert([
2  { email: 'tarou@example.com', name: 'tarou' },
3  { email: 'satou@example.com', name: 'satou' }
4]);
5// ids: Array<number>. one generated id per inserted row, in order.
6// pass an empty array to get [] back without executing any query.

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: mysql2 ResultSetHeader
5// result.affectedRows tells you how many rows actually changed.

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

deletes

the delete method removes records from the table.

1const result = await DB.table('users')
2  .where('status', 'inactive')
3  .delete();
4// result: mysql2 ResultSetHeader
5// result.affectedRows tells you how many rows were removed.

auto hashing

the query builder is aware of sensitive columns and automatically hashes their values using bcrypt during inserts and updates. by default, any column named password or secret triggers this behavior.

you can override the default fields for a specific query using the setHashFields method.

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

if you need to force hashing on an arbitrary value without relying on column names, the framework provides hash and fastHash wrappers exported from the QueryBuilder module.

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

both wrappers return an opaque object (not a string). the query builder unpacks them at execution time, so never read their value directly.

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.