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.

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();

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}

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();

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');

counting results

to determine the total number of records matching your criteria, use the count method. it executes a raw count aggregation and returns an integer.

1const total = await DB.table('orders').where('status', 'pending').count();

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();

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();

to force the query to return only distinct results, use the distinct method.

1const activeRoles = await DB.table('users').distinct('role').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();

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.

1await DB.table('users').whereIn('id', [1, 2, 3]).get();

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.

1await DB.table('users').whereBetween('votes', [1, 100]).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();

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. it returns the array of created ids or a result object containing the insertId property.

1const result = await DB.table('users').insert({
2  email: 'tarou@example.com',
3  name: 'tarou'
4});

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.

1await DB.table('users').insert([
2  { email: 'tarou@example.com', name: 'tarou' },
3  { email: 'satou@example.com', name: 'satou' }
4]);

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.

1await DB.table('users')
2  .where('id', 1)
3  .update({ votes: 1 });

the save method acts as an alias for update.

deletes

the delete method removes records from the table. for safety, the framework requires at least one where clause to be present before executing a delete. calling delete without conditions will throw an exception to prevent accidental table truncation.

1await DB.table('users')
2  .where('status', 'inactive')
3  .delete();

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' });

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});

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.

1await DB.table('tokens').whereHashed('secret', 'plain-text-key').first();