Query Builder Methods

This page describes all the query builder methods in detail.

Introduction

The main query builder methods all work in a similar way with respect to the arguments they accept. The short form is to pass a string which may have some additional processing applied to it (typically splitting a whitespace delimited string into multiple tokens). Where there are two, three or four possible arguments, an array can be passed. Or an object can be passed with explicit properties for the different parameters.

All the methods can generate raw SQL. This can be specified by passing an object with a single sql property:

db.select({ sql: 'COUNT(id) AS n' })

Or using the sql function to create a tagged template literal:

import { sql } from '@abw/badger-database'

db.select(sql`COUNT(id) AS n`)

All of the methods can be called multiple times, or passed multiple arguments. They both have the same effect. Generally speaking, you can call the methods in any order and the query builder will generate the SQL query with them in the correct order.

For the sake of brevity these examples assume that you've already connected to a database and stored the connection in the db variable.

import connect from '@abw/badger-database'

const db = connect({ database: 'sqlite:memory' });

// ...examples would go here

db.disconnect()

The examples show the SQL generated by the fragment. In many cases they're not complete queries and we've omitted the final .sql() method call which is called internally to generate the SQL.

In these example we're assuming that the database is Sqlite which quotes table and columns names using double quotes, e.g. "users"."id". For Mysql the names will be quoted using backticks. Examples that show placeholders also assume Sqlite (and Mysql) which uses question marks, e.g. ?. For Postgress the placeholders are of the form $1, $2, $3, etc.

Select Queries

select(columns)

This method is used to specify one or more columns that you want to select.

db.select('id');
// -> SELECT "id"

You can specify multiple columns using the shorthand syntax as a string of whitespace delimited column names.

db.select('id email');
// -> SELECT "id", "email"

Commas (with optional whitespace following) can also be used to delimit column names.

db.select('id, email');
// -> SELECT "id", "email"

Columns can have the table name included in them. Both the table and columns names will be automatically quoted.

db.select('users.id users.email');
// -> SELECT "users"."id", "users"."email"

You can specify * to select all columns. This will not be quoted.

db.select('*');
// -> SELECT *

You can also use * to select all columns from a table. The table name will be automatically quoted but the asterisk will not.

db.select('users.*');
// -> SELECT "users".*

You can pass an object to the method containing the columns property. The format for the value is either a single column name or multiple columns delimited with whitespace or commas/whitespace, as shown in the previous examples.

db.select({ columns: 'id email company.*' });
// -> SELECT "id", "email", "company".*

You can also define the table as a property to have the table name automatically added to the column names.

db.select({ table: 'users', columns: 'id email' });
// -> SELECT "users"."id", "users"."email"

The prefix property can be used to automatically create aliases for the columns. The prefix will be attached to the front of each column name.

db.select({ columns: 'id email', prefix: 'user_' });
// -> SELECT "id" AS "user_id", "email" AS "user_email"

This also works in conjunction with the table property.

db.select({ table: 'users', columns: 'id email', prefix: 'user_' });
// -> SELECT "users"."id" AS "user_id", "users"."email" AS "user_email"

An object can also contain a column item. In this case it is assumed to be a single column name which is not split into separate columns. The optional as property can be provided to create an alias for the column.

db.select({ column: 'email', as: 'email_address' });
// -> SELECT "email" AS "email_address"

The table property can also be provided in this case.

db.select({ table: 'users', column: 'email', as: 'email_address' });
// -> SELECT "users"."email" AS "email_address"

Or you can include the table name in the column.

db.select({ column: 'users.email', as: 'email_address' });
// -> SELECT "users"."email" AS "email_address"

The shorthand format for creating a column alias is to pass an array of either two elements (the column name and alias) or three (the table name, column name and alias).

db.select(['email', 'email_address' });
// -> SELECT "email" AS "email_address"

db.select(['users', 'email', 'email_address' });
// -> SELECT "users"."email" AS "email_address"

You can use raw SQL to define the table columns. The explicit way is to pass an object with a sql property.

db.select({ sql: '"email" AS "email_address"' });
// -> SELECT "email" AS "email_address"

Or you can use the sql function to create a tagged template literal.

import { sql } from '@abw/badger-database'
db.select(sql`"email" AS "email_address"`);
// -> SELECT "email" AS "email_address"

You can call the method multiple times. The column names will all be collected after the SELECT keyword.

db.select('id email').select({ table: 'companies', column: 'name', as: 'company_name' });
// -> SELECT "id", "email", "companies"."name" AS "company_name"

Or you can pass multiple arguments to a single method call. Each argument can be any of the values described above.

db.select('id email', { table: 'companies', column: 'name', as: 'company_name' });
// -> SELECT "id", "email", "companies"."name" AS "company_name"

from(table)

This method is used to specify one or more tables that you want to select from.

db.from('users');
// -> FROM "users"

You can specify multiple tables using the shorthand syntax as a string of whitespace delimited table names.

db.from('users companies');
// -> FROM "users", "companies"

Commas (with optional whitespace following) can also be used to delimit table names.

db.from('users, companies');
// -> FROM "users", "companies"

You can pass an object to the method containing the tables property. The format for the value is either a single table name or multiple tables delimited with either whitespace or commas, as shown in the previous examples.

db.from({ tables: 'users, companies' });
// -> FROM "users", "companies"

An object can also contain a table item. In this case it is assumed to be a single table name is not split into separate table names. The optional as property can be provided to create an alias for the table.

db.from({ table: 'users', as: 'people' });
// -> FROM "users" AS "people"

The shorthand format for creating a table alias is to pass an array of two elements: the table name and alias.

db.from(['users', 'people']);
// -> FROM "users" AS "people"

You can use raw SQL to define the table name. The explicit way is to pass an object with a sql property.

db.from({ sql: '"users" AS "people"' });
// -> FROM "users" AS "people"

Or you can use the sql function to create a tagged template literal.

import { sql } from '@abw/badger-database'
db.from(sql`"users" AS "people"`);
// -> FROM "users" AS "people"

You can call the method multiple times. The tables names will all be collected after the FROM keyword.

db.from(['users', 'people']).from('companies').from({ table: 'employees' })
// -> FROM "users" AS "people", "companies", "employees"

Or you can pass multiple arguments to a single method call. Each argument can be any of the values described above.

db.from(['users', 'people'], 'companies', { table: 'employees' })
// -> FROM "users" AS "people", "companies", "employees"

where(criteria)

This method can be used to specify the criteria for matching rows. You can specify one or more columns that you want to match against.

db.select('name email')
  .from('users')
  .where('id')
// -> SELECT "name", "email"
//    FROM "users"
//    WHERE "id" = ?

The query will be constructed with placeholders matching the specified column or columns.

Values for placeholders can be passed as an array to the one(), any() or all() methods.

const row = await db
  .select('name email')
  .from('users')
  .where('id')
  .one([12345])

You can specify multiple columns using the shorthand syntax as a string of whitespace delimited table names.

db.select('name email')
  .from('users')
  .where('id name')
// -> SELECT "name", "email"
//    FROM "users"
//    WHERE "id" = ? AND "name" = ?

Commas (with optional whitespace following) can also be used to delimit column names.

db.select('name email')
  .from('users')
  .where('id, name')
// -> SELECT "name", "email"
//    FROM "users"
//    WHERE "id" = ? AND "name" = ?

Columns can have the table name included in them. Both the table and columns names will be automatically quoted.

db.select('name email')
  .from('users')
  .where('users.id')
// -> SELECT "name", "email"
//    FROM "users"
//    WHERE "users"."id" = ?

You can pass an object to the method mapping column names to their respective values.

const row = await db
  .select('id name email')
  .from('users')
  .where({ id: 12345 })
  .one()      // automatically uses placeholder values: [12345]
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE "id" = ?

The query will still be constructed with placeholder values but all the values will be collected and automatically provided to the one(), any() or all() methods. In this case the values would be [12345].

You can pass additional values to those method to provide any additional values. Be warned that they will always be added after values specified in the query.

To illustrate, this will work as intended:

const row = await db
  .select('id name email')
  .from('users')
  .where({ id: 12345 })     // placeholder for id
  .where('name')            // placeholder for name
  .one(['Bobby Badger'])    // placeholder values are [12345, 'Bobby Badger']
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE "id" = ?
//    AND "name" = ?

But this won't:

// DON'T DO THIS!
const row = await db
  .select('id name email')
  .from('users')
  .where('name')            // placeholder for name
  .where({ id: 12345 })     // placeholder for id
  .one(['Bobby Badger'])    // WRONG! placeholder values are [12345, 'Bobby Badger']
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE "name" = ?
//    AND "id" = ?

For this reason it is usually best if you either specify all of the values in the where() clauses, or pass them all into the one(), any() or all() methods. This is also particularly relevant if you have a query that includes having() clauses as well.

You can also provide values as an array of [column, value].

const row = await db
  .select('id name email')
  .from('users')
  .where(['id', '12345'])
  .one()            // automatically uses placeholder values: [12345]
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE "id" = ?

If you want to see what placeholder values have been collected in a query then you can call the allValues() method.

const query = db
  .select('id name')
  .from('users')
  .where({ id: 12345 })

console.log(query.allValues())
// -> [12345]

The column can be raw SQL if necessary. Either use the sql function to create a tagged template literal or pass it as an object with a single sql property.

const row = await db
  .select('id name email')
  .from('users')
  .where([sql`id + 100`, 102])
  .one()            // automatically uses placeholder values: [102]
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE id + 100 = ?

By default the comparison operator is =. You can provide an array of three values to set a different comparison operator: [column, operator, value].

db.select('id name email')
  .from('users')
  .where(['id', '>', '12345'])
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE "id" > ?

If you want to provide a comparison operator but define the value later then set the third item to undefined.

db.select('id name email')
  .from('users')
  .where(['id', '>', undefined])
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE "id" > ?

Or you can define the operator in an array, either with or without a value.

db.select('id name email')
  .from('users')
  .where(['id', ['>', 12345]])
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE "id" > ?

db.select('id name email')
  .from('users')
  .where(['id', ['>']])
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE "id" > ?

There's a special case for the in and not in operators. Here the query builder has to know how many items are in the list of candidate values so that it can generate the appropriate number of placeholders. So you must pass the values in as part of the where clause. You can do this using either a three-element array, where the array of values are passed as the third element:

db.select('id name email')
  .from('users')
  .where(['id', 'in', [123, 456]])
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE "id" IN (?,?)

Or using a two-element array with the array of values nested inside the second array.

db.select('id name email')
  .from('users')
  .where(['id', ['in' [123, 456]]])
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE "id" IN (?,?)

Here are similar queries using not in:

db.select('id name email')
  .from('users')
  .where(['id', 'not in', [123, 456]])
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE "id" NOT IN (?,?)

db.select('id name email')
  .from('users')
  .where(['id', ['not in' [123, 456]]])
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE "id" NOT IN (?,?)

Note that the in operator is itself case insensitive, so you can write it as in, IN, In or even iN if you so wish. However, you can't write it as Ni (or Peng or Neee-Wom for that matter), even if you bring a shrubbery to offer as appeasement.

The same rule applies for not in which can be written as NOT IN or in any mixture of upper and lower case.

You can also set a comparison operator using an object by setting the value to a two element array: [operator, value].

db.select('id name email')
  .from('users')
  .where({ id: ['>', '12345']})
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE "id" > ?

Or if you want to provide the value later then use a single element array: [operator].

db.select('id name email')
  .from('users')
  .where({ id: ['>']})
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE "id" > ?

The special case for the in and not in operators also applies here. The value must be an array with the array of values provided as the second element.

db.select('id name email')
  .from('users')
  .where({ id: ['in', [123, 456]]})
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE "id" in (?,?)

You can use raw SQL to define the criteria. The explicit way is to pass an object with a sql property.

db.select('id name email')
  .from('users')
  .where({ sql: 'id > ?' })
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE id > ?

Or you can use the sql function to create a tagged template literal.

db.select('id name email')
  .from('users')
  .where(sql`id > ?`)
// -> SELECT "id", "name", "email"
//    FROM "users"
//    WHERE id > ?

You can call the method multiple times. The criteria will all be collected after the WHERE keyword and combined with AND.

db.select('name email')
  .from('users')
  .where(['id', '>', 12345])
  .where('name')
// -> SELECT "name", "email"
//    FROM "users"
//    WHERE "id" > ? AND "name" = ?

Or you can pass multiple arguments to a single method call. Each argument can be any of the values described above.

db.select('name email')
  .from('users')
  .where(['id', '>', 12345], 'name')
// -> SELECT "name", "email"
//    FROM "users"
//    WHERE "id" > ? AND "name" = ?

join(table)

This method can be used to join tables.

A string can be passed as a shorthand syntax of the form from = table.to. Here from is the column you're joining from. This can include the table name if necessary to disambiguate (e.g. users.id) or can just be the column name if it's unique (e.g. id). The table is the table you're joining onto and to is the column in that table that should match the value in the from column. Spaces are optional around the equals sign, e.g. from=table.to or from = table.to are both treated the same.

db.select('users.name users.email')
  .select(['companies.name', 'company_name'])
  .from('users')
  .join('users.company_id = companies.id')
// -> SELECT "users"."name", "users"."email", "companies"."name" AS "company_name"
//    FROM "users"
//    JOIN "companies" ON "users"."company_id" = "companies"."id"

For a left join, use a left pointing arrow, e.g. from <= table.to. For a right join, use a right pointing arrow, e.g. from => table.to. For a full join, use a double headed arrow, e.g. from <=> table.to. Spaces around the arrow are optional.

If you want to create an alias for the target table then you can add as <alias> to the end of the string.

db.select('users.name users.email')
  .select(['employer.name', 'company'])
  .from('users')
  .join('users.company_id = companies.id as employer')
// -> SELECT "users"."name", "users"."email", "employer"."name" AS "company"
//    FROM "users"
//    JOIN "companies" AS "employer" ON "users"."company_id" = "employer"."id"

You can pass an array to the method containing 2, 3, or 4 elements. When using two elements, the first should be the table column you're joining from and the second should be the table column you're joining to.

db.select('users.name users.email')
  .from('users')
  .select(['companies.name', 'company_name'])
  .join(['users.company_id', 'companies.id'])
// -> SELECT "users"."name", "users"."email", "companies"."name" AS "company_name"
//    FROM "users"
//    JOIN "companies" ON "users"."company_id" = "companies"."id"

The three element version has the destination table and column separated.

db.select('users.name users.email')
  .from('users')
  .select(['companies.name', 'company_name'])
  .join(['users.company_id', 'companies', 'id'])
// -> SELECT "users"."name", "users"."email", "companies"."name" AS "company_name"
//    FROM "users"
//    JOIN "companies" ON "users"."company_id" = "companies"."id"

The four element version allows you to specify the join type at the beginning. Valid types are left, right, inner and full.

db.select('users.name users.email')
  .from('users')
  .select(['companies.name', 'company_name'])
  .join(['left', 'users.company_id', 'companies', 'id'])
// -> SELECT "users"."name", "users"."email", "companies"."name" AS "company_name"
//    FROM "users"
//    LEFT JOIN "companies" ON "users"."company_id" = "companies"."id"

You can pass an object to the method containing the from, table and to properties, and optionally the type.

db.select('users.name users.email')
  .from('users')
  .select(['companies.name', 'company_name'])
  .join({
    type:  'left',
    from:  'users.company_id',
    table: 'companies',
    to:    'id'
  })
// -> SELECT "users"."name", "users"."email", "companies"."name" AS "company_name"
//    FROM "users"
//    LEFT JOIN "companies" ON "users"."company_id" = "companies"."id"

Or you can combine the table name and column in the to property as you might for the from property.

db.select('users.name users.email')
  .from('users')
  .select(['companies.name', 'company_name'])
  .join({
    type: 'left',
    from: 'users.company_id',
    to:   'companies.id'
  })
// -> SELECT "users"."name", "users"."email", "companies"."name" AS "company_name"
//    FROM "users"
//    LEFT JOIN "companies" ON "users"."company_id" = "companies"."id"

You can also use the as property to create an alias for the table.

db.select('users.name users.email')
  .from('users')
  .select(['employer.name', 'company'])
  .join({
    from:  'users.company_id',
    table: 'companies',
    as:    'employer'
    to:    'id'
  })
// -> SELECT "users"."name", "users"."email", "employer"."name" AS "company"
//    FROM "users"
//    LEFT JOIN "companies" AS "employer"
//    ON "users"."company_id" = "employer"."id"

You know the drill, right? If the method doesn't do what you need then you can use raw SQL to define the joins, either with an object containing a sql property:

db.select('users.name users.email employee.job_title')
  .select(['companies.name', 'company_name'])
  .from('users')
  .join({ sql: 'JOIN employees ON users.id=employees.user_id' })
// -> SELECT "users"."name", "users"."email", "employee"."job_title", "companies"."name" AS "company_name"
//    FROM "users"
//    JOIN employees ON users.id=employees.user_id

Or using the sql function to create a tagged template literal.

db.select('users.name users.email employee.job_title')
  .select(['companies.name', 'company_name'])
  .from('users')
  .join(sql`JOIN employees ON users.id=employees.user_id`)
// -> SELECT "users"."name", "users"."email", "employee"."job_title", "companies"."name" AS "company_name"
//    FROM "users"
//    JOIN employees ON users.id=employees.user_id

And just like the other methods, you can call the method multiple times.

db.select('users.name users.email employee.job_title')
  .select(['companies.name', 'company_name'])
  .from('users')
  .join('users.id = employees.user_id')
  .join('employees.company_id = companies.id')
// -> SELECT "users"."name", "users"."email", "employee"."job_title", "companies"."name" AS "company_name"
//    FROM "users"
//    JOIN "employees" ON "users"."id" = "employees"."user_id"
//    JOIN "companies" ON "employees"."company_id" = "companies"."id"

Or you can pass multiple arguments to a single method call. Each argument can be any of the values described above.

db.select('users.name users.email employee.job_title')
  .select(['companies.name', 'company_name'])
  .from('users')
  .join('users.id = employees.user_id', 'employees.company_id = companies.id')
// -> SELECT "users"."name", "users"."email", "employee"."job_title", "companies"."name" AS "company_name"
//    FROM "users"
//    JOIN "employees" ON "users"."id" = "employees"."user_id"
//    JOIN "companies" ON "employees"."company_id" = "companies"."id"

order(columns)

This method can be used to create an ORDER BY clause. There's also an orderBy() alias for the method if you prefer something a little closer in name to the SQL it generates.

A string can be passed containing one or more columns.

db.select('*')
  .from('users')
  .order('name')
// -> SELECT *
//    FROM "users"
//    ORDER BY "name"

Columns can be delimited by whitespace or commas, as usual.

db.select('*')
  .from('users')
  .order('name, email')
// -> SELECT *
//    FROM "users"
//    ORDER BY "name", "email"

Columns can include the table name for disambiguation.

db.select('*')
  .from('users')
  .order('users.name users.email')
// -> SELECT *
//    FROM "users"
//    ORDER BY "users"."name", "users"."email"

The default order is ASC for "ascending". To set a different sort order (e.g. DESC for "descending"), pass a two element array with the columns as the first element and DESC as the second.

db.select('*')
  .from('users')
  .order(['name email', 'DESC'])
// -> SELECT *
//    FROM "users"
//    ORDER BY "name", "email" DESC

You can also pass an objecting containing the column or columns property. The column is assumed to be a single column whereas columns can contain multiple columns separated by whitespace or commas in the usual way. In addition you can specify either asc or desc as a boolean flag to set the sort direction to be ascending or descending, respectively.

db.select('*')
  .from('users')
  .order({ columns: 'name email', desc: true })
// -> SELECT *
//    FROM "users"
//    ORDER BY "name", "email" DESC

Or use direction (or dir for short) set to either ASC or DESC if you prefer.

db.select('*')
  .from('users')
  .order({ columns: 'name email', dir: 'DESC' })
// -> SELECT *
//    FROM "users"
//    ORDER BY "name", "email" DESC

Of course it also supports raw SQL, either using a sql property in an object.

db.select('*')
  .from('users')
  .order({ sql: 'name DESC, email' })
// -> SELECT *
//    FROM "users"
//    ORDER BY name DESC, email

Or using a tagged template literal.

db.select('*')
  .from('users')
  .order(sql`name DESC, email`)
// -> SELECT *
//    FROM "users"
//    ORDER BY name DESC, email

You can call the method multiple times or pass multiple arguments to it.

db.select('*')
  .from('users')
  .order(['name', 'DESC'], 'email')
// -> SELECT *
//    FROM "users"
//    ORDER BY "name" DESC, "email"

group(columns)

This method can be used to create a GROUP BY clause. There's also a groupBy() alias for it.

A string can be passed containing one or more columns.

db.select(sql`company_id, COUNT(id) AS employees`)
  .from('users')
  .group('company_id')
// -> SELECT company_id, COUNT(id) AS employees
//    FROM "users"
//    GROUP BY "company_id"

Multiple columns can be delimited by whitespace or commas and can contain a table name.

db.select('*')
  .from('users')
  .group('users.company_id, users.start_year')
// -> SELECT *
//    FROM "users"
//    GROUP BY "users"."company_id", "users"."start_year"

db.select('*')
  .from('users')
  .group({ columns: 'company_id, year' })
// -> SELECT *
//    FROM "users"
//    GROUP BY "company_id", "year"

As you might expect it also supports raw SQL, either using a sql property in an object.

db.select('*')
  .from('users')
  .group({ sql: 'company_id' })
// -> SELECT *
//    FROM "users"
//    GROUP BY company_id

Or using a tagged template literal.

db.select('*')
  .from('users')
  .group(sql`company_id`)
// -> SELECT *
//    FROM "users"
//    GROUP BY company_id

You can call the method multiple times or pass multiple arguments to it.

db.select('*')
  .from('users')
  .order('company_id', 'start_year')
// -> SELECT *
//    FROM "users"
//    GROUP BY "company_id", "start_year"

having(criteria)

This method works exactly like where() but is used to specify the criteria for matching rows with the HAVING keyword.

One important thing to note is that the HAVING clause always appears near the end of the generated query, coming after the WHERE clause. When you are providing values for placeholders you should always put the WHERE values first followed by the HAVING values.

The query builder allows you to call methods in any order and will automatically arrange them correctly when building the SQL query. For example, it is perfectly valid to call having() before where(), but you MUST provide the values for the where() clauses before those for the having() clauses.

db.select(...)
  .from(...)
  .having('x')
  .where('y')
  .all([yValue, xValue])
// -> SELECT ...
//    FROM ...
//    WHERE "y" = ?
//    HAVING "x" = ?

For this reason it is recommended that you put all where() clauses before any having() clauses so that you don't confuse yourself.

If you provide values in the where() or having() clauses then you don't need to worry. The query builder automatically collects all where() values separately from having() values and passed them to the database engine in the correct order.

db.select(...)
  .from(...)
  .having({ x: xValue })
  .where({ y: yValue })
  .all()            // placeholder values will be [yValue, xValue]

You can see what placeholder values have been collected in a query, and the order that they will appear, in by calling the allValues() method. Note that regardless of the order of method calls, all where() placeholder values comes before having() values.

const query = db
  .select('...')
  .from('...')
  .where({ a: 123 })
  .having({ b: 789 })
  .where({ a: 456 })

console.log(query.allValues())
// -> [123, 456, 789]

In order to get the placeholder values in the right order, the query builder stores where() values separately from having() values. The whereValues() and havingValues() methods all you to inspect them.

console.log(query.whereValues())
// -> [123, 456]
console.log(query.havingValues())
// -> [789]

The insert() and update() methods also have their own array for storing values which can be inspected by calling setValues().

limit(n)

This method can be used to set a LIMIT for the number of rows returned.

An integer should be passed to it.

db.select('id name')
  .from('users')
  .limit(10)
// -> SELECT "id", "name"
//    FROM "users"
//    LIMIT 10

If you call the method multiple times the previously set value will be overwritten.

db.select('id name')
  .from('users')
  .limit(10)
  .limit(20)
// -> SELECT "id", "name"
//    FROM "users"
//    LIMIT 20

offset(n)

This method can be used to set an OFFSET for the number of rows returned.

An integer should be passed to it.

db.select('id name')
  .from('users')
  .offset(10)
// -> SELECT "id", "name"
//    FROM "users"
//    OFFSET 10

If you call the method multiple times the previously set value will be overwritten.

db.select('id name')
  .from('users')
  .offset(10)
  .offset(20)
// -> SELECT "id", "name"
//    FROM "users"
//    OFFSET 20

range(from, to)

This method allows you to set both the LIMIT and OFFSET at once.

It expects two integers representing the first row you want returned and the last row. Note that the numbers start at 0 and the range is inclusive.

db.select('id name')
  .from('users')
  .range(50, 59)
// -> SELECT "id", "name"
//    FROM "users"
//    LIMIT 10
//    OFFSET 50

If you pass one integer then it is assumed to be the first row you want returned and there will be no LIMIT to the number of rows returned.

db.select('id name')
  .from('users')
  .range(50)
// -> SELECT "id", "name"
//    FROM "users"
//    OFFSET 50

You can also provide an object containing from and/or to.

db.select('id name')
  .from('users')
  .range({ from: 50, to: 59 })
// -> SELECT "id", "name"
//    FROM "users"
//    LIMIT 10
//    OFFSET 50

db.select('id name')
  .from('users')
  .range({ from: 50 })
// -> SELECT "id", "name"
//    FROM "users"
//    OFFSET 50

db.select('id name')
  .from('users')
  .range({ to: 49 })
// -> SELECT "id", "name"
//    FROM "users"
//    LIMIT 50

You can also use it to explicitly set the limit and/or offset.

db.select('id name')
  .from('users')
  .range({ limit: 10, offset: 50 })
// -> SELECT "id", "name"
//    FROM "users"
//    LIMIT 10
//    OFFSET: 50

db.select('id name')
  .from('users')
  .range({ offset: 50 })
// -> SELECT "id", "name"
//    FROM "users"
//    OFFSET 50

columns(columns)

This is just like select() with one important distinction. If you don't explicitly specify a table name then it will automatically attach the column names to the most recently specified table.

db.from('users')
  .columns('id email');
// -> SELECT "users"."id", "users"."email"
//    FROM "users"

You can interleave it with multiple calls to from() to access columns from different tables.

db.from('users')
  .columns('id email');
  .from('companies')
  .columns('name');
// -> SELECT "users"."id", "users"."email", "companies"."name"
//    FROM "users"

If you specify multiple table names in from() then the last one will be used.

db.from('companies users')
  .columns('id name');
// -> SELECT "users"."id", "users"."name"
//    FROM "companies", "users"

If you specify a table with an alias then the alias will be used.

db.from({ table: "users", as: "people" })
  .columns('id name');
// -> SELECT "people"."id", "people"."name"
//    FROM "users" AS "people"

table(table)

This can be used in conjuction with columns() to select a table to attach columns to. The table should previously have been specified using from().

db.from('users companies')
  .table('users').columns('id name')
  .table('companies').columns(['name', 'company_name'])
// -> SELECT "users"."id", "users"."name", "companies"."name" AS "company_name"
//    FROM "users", "companies"

prefix(prefix)

This can be used in conjuction with columns() to define a prefix for subsequent columns.

db.from('users companies')
  .table('users').prefix('user_').columns('id name')
  .table('companies').prefix('company_').columns('name')
// -> SELECT "users"."id" AS "user_id", "users"."name" AS "user_name", "companies"."name" AS "company_name"
//    FROM "users", "companies"

You can clear the current prefix by calling prefix() without any arguments.

Insert Queries

insert(columns)

This method is used to generate an INSERT query. The argument(s) specify the columns that you want to insert.

db.insert('name email');

It should be used in conjunction with into().

into(table)

This method is used to specify the name of the table that you want to insert into.

db.insert('name email')
  .into('users');
// -> INSERT INTO "users" ("name", "email") VALUES (?, ?)

values(values)

This method can be used to provide values for an insert query. They can be provided as individual arguments or passed as an array.

await db
  .insert('name email')
  .into('users')
  .values('Brian Badger', 'brian@badgerpowercom')
  .run();

If you don't specify the values here then you should provide them as an array to the run() method.

await db
  .insert('name email')
  .into('users')
  .run(['Brian Badger', 'brian@badgerpowercom'])

returning(columns)

This methods generate a RETURNING clause for Postgres. The argument should be one or more columns that the query should return.

db.insert('name email')
  .into('users')
  .returning('id');
// -> INSERT INTO "users" ("name", "email")
//    VALUES (?, ?)
//    RETURING "id"

The method works much like the select() method. For example, you can define an alias by passing a two element array.

db.insert('name email')
  .into('users')
  .returning(['id', 'user_id');
// -> INSERT INTO "users" ("name", "email")
//    VALUES (?, ?)
//    RETURING "id" AS "user_id"

Update Queries

update(table)

This method is used to start building an UPDATE query. The argument is the name of the table you want to update. It should be used in conjunction with set().

db.update("users")
// -> UPDATE "users"

set(values)

This method is used to specify the changes that you want to make in an UPDATE query. You can specify the values as column names and then provide the values when you call the run() method.

await db
  .update("users")
  .set('name')
  .where('id')
  .run(['Brian the Badger', 12345])
// -> UPDATE "users"
//    SET "name" = ?, "email" = ?
//    WHERE "id" = ?

Or you can provide the values to the set() method, in the same way that you can for where().

await db
  .update("users")
  .set({ name: 'Brian the Badger' })
  .where({ id: 12345 })
  .run()

Delete Queries

delete()

The delete() method is used to start a DELETE query. In the usual case it doesn't take any arguments, but should be used in conjunction with from() to specify the table, and optionally where(), to select the rows that you want to delete.

await db
  .delete()
  .from('users')
  .where({ id: 12345 })
  .run()
// -> DELETE FROM "users"
//    WHERE "id" = ?

As usual, values can be specified in the where() method, as shown above, or passed to the run() method.

await db
  .delete()
  .from('users')
  .where('id')
  .run([12345])

Execution Methods

run(values, options)

This method can be used to run query where you're not expecting to get any rows returned. This is typically used for insert(), update() and delete() queries.

If you have any placeholders in the query that you haven't already defined values for then you should provide them as an array.

const result = await db
  .insert('name email')
  .into('users')
  .run(['Brian Badger', 'brian@badgerpower.com'])
// -> INSERT INTO "users" ("name", "email")
//    VALUES (?, ?)

Although this method doesn't return any rows from the database is does return a result.

You can pass a second argument to the run() method as an object containing options. The sanitizeResult option standardised the response for different database types so that, for example, changes always contains the number of rows changed.

const result = await db
  .insert('name email')
  .into('users')
  .run(
    ['Brian Badger', 'brian@badgerpower.com'],
    { sanitizeResult: true }
  )
console.log("changes: ", result.changes)

If you specify any placeholder values in the query then these will automatically be provided to the run() method. For example, the values() method can be used to provide values to an insert() query.

const result = await db
  .insert('name email')
  .into('users')
  .values('Brian Badger', 'brian@badgerpower.com')
  .run()

one(values, options)

This method will execute the query and return exactly one row. If the query returns more than one row or no rows then an error will be thrown.

If you have any placeholders in the query that you haven't already defined values for then you should provide them as an array.

In this query the value for id is specified in the where() method so you don't need to pass anything to the one() method.

const row = await db
  .select('name email')
  .from('users')
  .where({ id: 12345 })
  .one()            // automatically receives placeholder values: [12345]
// -> SELECT "name", "email"
//    FROM "users"
//    WHERE "id" = ?

In this query it isn't so you need to provide it to the one() method.

const row = await db
  .select('name email')
  .from('users')
  .where('id')
  .one([12345])     // manually provider placeholder values
// -> SELECT "name", "email"
//    FROM "users"
//    WHERE "id" = ?

Although it generally isn't recommended you can mix and match the two approaches. However you should note that all placeholder values that have been specified in where() clauses will be provided first, followed by any in having() clauses. Any additional values that you provide to the one() method will come last. It is your responsibility to ensure that these are in the correct order for your query!

If you have a mixture of where() and having() calls, then you might find yourself in a tight spot if you've mixed and matched.

Consider this somewhat contrived example:

db.select(...)
  .from(...)
  .where({ a: 10 })
  .where('b')
  .having({ c: 30 })
  .having('d');
// -> SELECT ...
//    FROM ...
//    WHERE "a" = ?
//    AND "b" = ?
//    HAVING "c" = ?
//    AND "d" = ?

Now you've got a problem. When you call the one() (or any()/all()) method you need to provide values for b and d. But the query has already got a list of values for where() clauses set to [10] (for a) and a list for having() clauses set to [30] (for c). If you pass the values for b and c as [20, 40] then you'll end up with a complete list of values set to [10, 30, 20, 40] which isn't in the correct order for the query.

At this point we could refer you back to the bit where we said that mixing up different approaches isn't recommended. But you already know that.

If you need to jiggle around with the order of values then you can pass a function to the one() method. This will received three lists of placeholder values:

setValues contains any placeholder values provided via the values() method as part of an insert() query, or via the set() method as part of an update() query.
whereValues contains any placeholder values provided via the where() method.
havingValues contains any placeholder values provided via the having() method.

The function should return a new array containing the values in the right order.

db.select(...)
  .from(...)
  .where({ a: 10 })
  .where('b')
  .having({ c: 30 })
  .having('d')
  .one((where, having) => [...where, 20, ...having, 40])

Now the order of placeholder values will be correctly set to [10, 20, 30, 40].

If you want to double-check you can call the allValues() method on a query to check that it returns them in the right order.

db.select(...)
  .from(...)
  .where({ a: 10 })
  .where('b')
  .having({ c: 30 })
  .having('d')
  .values((where, having) => [...where, 20, ...having, 40])
  .allValues()
// -> [10, 20, 30, 40]

You can also call the whereValues() and havingValues() methods to see what the query has got stored for them.

any(values, options)

This method will execute the query and return one row if it exists or undefined if it doesn't. In all other respects it works exactly like one().

all(values, options)

This method will execute the query and return an array of all matching rows. The array may be empty if no rows are matched. In all other respects it works exactly like one().

sql()

This methods generates and returns the SQL for the query.

Placeholder Value Methods

setValues()

This returns any array of any placeholder values provided to the values() or set() methods.

whereValues()

This returns any array of any placeholder values provided to the where() method.

havingValues()

This returns any array of any placeholder values provided to the having() method.

allValues()

This returns an array of all placeholder values. It is the concatenated list of setValues(), whereValues() and havingValues()

Where Next?

In the next section we'll look at tables which provide methods to automatically generate queries to insert, update, fetch and delete rows.

Query Builder Methods ​

Introduction ​

Select Queries ​

select(columns) ​

from(table) ​

where(criteria) ​

join(table) ​

order(columns) ​

group(columns) ​

having(criteria) ​

limit(n) ​

offset(n) ​

range(from, to) ​

columns(columns) ​

table(table) ​

prefix(prefix) ​

Insert Queries ​

insert(columns) ​

into(table) ​

values(values) ​

returning(columns) ​

Update Queries ​

update(table) ​

set(values) ​

Delete Queries ​

delete() ​

Execution Methods ​

run(values, options) ​

one(values, options) ​

any(values, options) ​

all(values, options) ​

sql() ​

Placeholder Value Methods ​

setValues() ​

whereValues() ​

havingValues() ​

allValues() ​

Where Next? ​

Query Builder Methods

Introduction

Select Queries

select(columns)

from(table)

where(criteria)

join(table)

order(columns)

group(columns)

having(criteria)

limit(n)

offset(n)

range(from, to)

columns(columns)

table(table)

prefix(prefix)

Insert Queries

insert(columns)

into(table)

values(values)

returning(columns)

Update Queries

update(table)

set(values)

Delete Queries

delete()

Execution Methods

run(values, options)

one(values, options)

any(values, options)

all(values, options)

sql()

Placeholder Value Methods

setValues()

whereValues()

havingValues()

allValues()

Where Next?