Knex.js (pronounced /kəˈnɛks/) is a "batteries included" SQL query builder for PostgreSQL, CockroachDB, MSSQL, MySQL, MariaDB, SQLite3, Better-SQLite3, Oracle, and Amazon Redshift designed to be flexible, portable, and fun to use. It features both traditional node style callbacks as well as a promise interface for cleaner async flow control, a stream interface, full-featured query and schema builders, transaction support (with savepoints), connection pooling and standardized responses between different query clients and dialects.

The project is hosted on GitHub, and has a comprehensive test suite.

Knex is available for use under the MIT software license.

You can report bugs and discuss features on the GitHub issues page, add pages to the wiki or send tweets to @kibertoad.

Thanks to all of the great contributions to the project.

The client created by the configuration initializes a connection pool, using the tarn.js library. This connection pool has a default setting of a min: 2, max: 10 for the MySQL and PG libraries, and a single connection for sqlite3 (due to issues with utilizing multiple connections on a single file). To change the config settings for the pool, pass a pool option as one of the keys in the initialize block.

Checkout the tarn.js library for more information.

In many places in APIs identifiers like table name or column name can be passed to methods.

Most commonly one needs just plain tableName.columnName, tableName or columnName, but in many cases one also needs to pass an alias how that identifier is referred later on in the query.

There are two ways to declare an alias for identifier. One can directly give as aliasName suffix for the identifier (e.g. identifierName as aliasName) or one can pass an object { aliasName: 'identifierName' }.

If the object has multiple aliases { alias1: 'identifier1', alias2: 'identifier2' }, then all the aliased identifiers are expanded to comma separated list.

NOTE: identifier syntax has no place for selecting schema, so if you are doing schemaName.tableName, query might be rendered wrong. Use .withSchema('schemaName') instead.

The query builder starts off either by specifying a tableName you wish to query against, or by calling any method directly on the knex object. This kicks off a jQuery-like chain, with which you can call additional query builder methods as needed to construct the query, eventually calling any of the interface methods, to either convert toString, or execute the query with a promise, callback, or stream. Optional second argument for passing options:* only: if true, the ONLY keyword is used before the tableName to discard inheriting tables' data. NOTE: only supported in PostgreSQL for now.

Usage with TypeScript

If using TypeScript, you can pass the type of database row as a type parameter to get better autocompletion support down the chain.

interface User {
  id: number;
  name: string;
  age: number;
}

knex('users')
  .where('id')
  .first(); // Resolves to any

knex<User>('users') // User is the type of row in database
  .where('id', 1) // Your IDE will be able to help with the completion of id
  .first(); // Resolves to User | undefined

It is also possible to take advantage of auto-completion support (in TypeScript-aware IDEs) with generic type params when writing code in plain JavaScript through JSDoc comments.

/**
 * @typedef {Object} User
 * @property {number} id
 * @property {number} age
 * @property {string} name
 *
 * @returns {Knex.QueryBuilder<User, {}>}
 */
const Users = () => knex('Users')

Users().where('id', 1) // 'id' property can be autocompleted by editor

Caveat with type inference and mutable fluent APIs

Most of the knex APIs mutate current object and return it. This pattern does not work well with type-inference.

knex<User>('users')
  .select('id')
  .then((users) => { // Type of users is inferred as Pick<User, "id">[]
    // Do something with users
  });

knex<User>('users')
  .select('id')
  .select('age')
  .then((users) => { // Type of users is inferred as Pick<User, "id" | "age">[]
    // Do something with users
  });

// The type of usersQueryBuilder is determined here
const usersQueryBuilder = knex<User>('users').select('id');

if (someCondition) {
  // This select will not change the type of usersQueryBuilder
  // We can not change the type of a pre-declared variable in TypeScript
  usersQueryBuilder.select('age');
}
usersQueryBuilder.then((users) => {
  // Type of users here will be Pick<User, "id">[]
  // which may not be what you expect.
});

// You can specify the type of result explicitly through a second type parameter:
const queryBuilder = knex<User, Pick<User, "id" | "age">>('users');

// But there is no type constraint to ensure that these properties have actually been
// selected.

// So, this will compile:
queryBuilder.select('name').then((users) => {
  // Type of users is Pick<User, "id"> but it will only have name
})

If you don't want to manually specify the result type, it is recommended to always use the type of last value of the chain and assign result of any future chain continuation to a separate variable (which will have a different type).

Sets a timeout for the query and will throw a TimeoutError if the timeout is exceeded. The error contains information about the query, bindings, and the timeout that was set. Useful for complex queries that you want to make sure are not taking too long to execute. Optional second argument for passing options:* cancel: if true, cancel query if timeout is reached. NOTE: only supported in MySQL and PostgreSQL for now.

knex.select().from('books').timeout(1000)
Outputs:
select * from `books`

knex.select().from('books').timeout(1000, {cancel: true}) // MySQL and PostgreSQL only
Outputs:
select * from `books`

Creates a select query, taking an optional array of columns for the query, eventually defaulting to * if none are specified when the query is built. The response of a select call will resolve with an array of objects selected from the database.

knex.select('title', 'author', 'year').from('books')
Outputs:
select `title`, `author`, `year` from `books`

knex.select().table('books')
Outputs:
select * from `books`

Usage with TypeScript

We are generally able to infer the result type based on the columns being selected as long as the select arguments match exactly the key names in record type. However, aliasing and scoping can get in the way of inference.

knex.select('id').from<User>('users'); // Resolves to Pick<User, "id">[]

knex.select('users.id').from<User>('users'); // Resolves to any[]
// ^ TypeScript doesn't provide us a way to look into a string and infer the type
//   from a substring, so we fall back to any

// We can side-step this using knex.ref:
knex.select(knex.ref('id').withSchema('users')).from<User>('users'); // Resolves to Pick<User, "id">[]

knex.select('id as identifier').from<User>('users'); // Resolves to any[], for same reason as above

// Refs are handy here too:
knex.select(knex.ref('id').as('identifier')).from<User>('users'); // Resolves to { identifier: number; }[]

Allows for aliasing a subquery, taking the string you wish to name the current query. If the query is not a sub-query, it will be ignored.

knex.avg('sum_column1').from(function() {
  this.sum('column1 as sum_column1').from('t1').groupBy('column1').as('t1')
}).as('ignored_alias')
Outputs:
select avg(`sum_column1`) from (select sum(`column1`) as `sum_column1` from `t1` group by `column1`) as `t1`

Specifically set the columns to be selected on a select query, taking an array, an object or a list of column names. Passing an object will automatically alias the columns with the given keys.

knex.column('title', 'author', 'year').select().from('books')
Outputs:
select `title`, `author`, `year` from `books`

knex.column(['title', 'author', 'year']).select().from('books')
Outputs:
select `title`, `author`, `year` from `books`

knex.column('title', {by: 'author'}, 'year').select().from('books')
Outputs:
select `title`, `author` as `by`, `year` from `books`

Specifies the table used in the current query, replacing the current table name if one has already been specified. This is typically used in the sub-queries performed in the advanced where or union methods. Optional second argument for passing options:* only: if true, the ONLY keyword is used before the tableName to discard inheriting tables' data. NOTE: only supported in PostgreSQL for now.

knex.select('*').from('users')
Outputs:
select * from `users`

Usage with TypeScript

We can specify the type of database row through the TRecord type parameter

knex.select('id').from('users'); // Resolves to any[]

knex.select('id').from<User>('users'); // Results to Pick<User, "id">[]

knex.select('*').fromRaw('(select * from "users" where "age" > ?)', '18')
Outputs:
select * from (select * from "users" where "age" > '18')

Add a "with" clause to the query. "With" clauses are supported by PostgreSQL, Oracle, SQLite3 and MSSQL. An optional column list can be provided after the alias; if provided, it must include at least one column name.

knex.with('with_alias', knex.raw('select * from "books" where "author" = ?', 'Test')).select('*').from('with_alias')
Outputs:
with `with_alias` as (select * from "books" where "author" = 'Test') select * from `with_alias`

knex.with('with_alias', ["title"], knex.raw('select "title" from "books" where "author" = ?', 'Test')).select('*').from('with_alias')
Outputs:
with `with_alias`(`title`) as (select "title" from "books" where "author" = 'Test') select * from `with_alias`

knex.with('with_alias', (qb) => {
  qb.select('*').from('books').where('author', 'Test')
}).select('*').from('with_alias')
Outputs:
with `with_alias` as (select * from `books` where `author` = 'Test') select * from `with_alias`

Identical to the with method except "recursive" is appended to "with" (or not, as required by the target database) to make self-referential CTEs possible. Note that some databases, such as Oracle, require a column list be provided when using an rCTE.

knex.withRecursive('ancestors', (qb) => {
  qb.select('*').from('people').where('people.id', 1).union((qb) => {
    qb.select('*').from('people').join('ancestors', 'ancestors.parentId', 'people.id')
  })
}).select('*').from('ancestors')
Outputs:
with recursive `ancestors` as (select * from `people` where `people`.`id` = 1 union select * from `people` inner join `ancestors` on `ancestors`.`parentId` = `people`.`id`) select * from `ancestors`

knex.withRecursive('family', ['name', 'parentName'], (qb) => {
  qb.select('name', 'parentName')
    .from('folks')
    .where({ name: 'grandchild' })
    .unionAll((qb) =>
      qb
        .select('folks.name', 'folks.parentName')
        .from('folks')
        .join(
          'family',
          knex.ref('family.parentName'),
          knex.ref('folks.name')
        )
    )
})
.select('name')
.from('family')
Outputs:
with recursive `family`(`name`, `parentName`) as (select `name`, `parentName` from `folks` where `name` = 'grandchild' union all select `folks`.`name`, `folks`.`parentName` from `folks` inner join `family` on `family`.`parentName` = `folks`.`name`) select `name` from `family`

Add a "with" materialized clause to the query. "With" materialized clauses are supported by PostgreSQL and SQLite3. An optional column list can be provided after the alias; if provided, it must include at least one column name.

knex.withMaterialized('with_alias', knex.raw('select * from "books" where "author" = ?', 'Test')).select('*').from('with_alias')
Error:
With materialized is not supported by this dialect

knex.withMaterialized('with_alias', ["title"], knex.raw('select "title" from "books" where "author" = ?', 'Test')).select('*').from('with_alias')
Error:
With materialized is not supported by this dialect

knex.withMaterialized('with_alias', (qb) => {
  qb.select('*').from('books').where('author', 'Test')
}).select('*').from('with_alias')
Error:
With materialized is not supported by this dialect

Add a "with" not materialized clause to the query. "With" not materialized clauses are supported by PostgreSQL and SQLite3. An optional column list can be provided after the alias; if provided, it must include at least one column name.

knex.withNotMaterialized('with_alias', knex.raw('select * from "books" where "author" = ?', 'Test')).select('*').from('with_alias')
Error:
With materialized is not supported by this dialect

knex.withNotMaterialized('with_alias', ["title"], knex.raw('select "title" from "books" where "author" = ?', 'Test')).select('*').from('with_alias')
Error:
With materialized is not supported by this dialect

knex.withNotMaterialized('with_alias', (qb) => {
  qb.select('*').from('books').where('author', 'Test')
}).select('*').from('with_alias')
Error:
With materialized is not supported by this dialect

Specifies the schema to be used as prefix of table name.

knex.withSchema('public').select('*').from('users')
Outputs:
select * from `public`.`users`

Extract a value from a json column given a JsonPath. An alias can be specified. The singleValue booleancan be used to specify, with Oracle or MSSQL, if the value returned by the function is a single value or an array/object value.An array of arrays can be used to specify multiple extractions with one call to this function.

knex('accounts').jsonExtract('json_col', '$.name')
Outputs:
select json_unquote(json_extract(`json_col`, '$.name')) from `accounts`

knex('accounts').jsonExtract('json_col', '$.name', 'accountName')
Outputs:
select json_unquote(json_extract(`json_col`, '$.name')) as `accountName` from `accounts`

knex('accounts').jsonExtract('json_col', '$.name', 'accountName', true)
Outputs:
select json_unquote(json_extract(`json_col`, '$.name')) as `accountName` from `accounts`

knex('accounts').jsonExtract([ 
  ['json_col', '$.name', 'accountName'], 
  ['json_col', '$.lastName', 'accountLastName'] 
])
Outputs:
select json_unquote(json_extract(`json_col`, '$.name')) as `accountName`, json_unquote(json_extract(`json_col`, '$.lastName')) as `accountLastName` from `accounts`

knex('cities').jsonExtract([
      [knex.jsonRemove('population', '$.min'), '$', 'withoutMin'],
      [knex.jsonRemove('population', '$.max'), '$', 'withoutMax'],
      [
        knex.jsonSet('population', '$.current', '1234'),
        '$',
        'currentModified',
      ]
    ])
Outputs:
select json_unquote(json_extract(json_remove(`population`,'$.min'), '$')) as `withoutMin`, json_unquote(json_extract(json_remove(`population`,'$.max'), '$')) as `withoutMax`, json_unquote(json_extract(json_set(`population`, '$.current', '1234'), '$')) as `currentModified` from `cities`

Return a json value/object/array where a given value is set at the given JsonPath. Value can be single value or json object. If a value already exists at the given place, the value is replaced.Not supported by Redshift and versions before Oracle 21c.

knex('accounts').jsonSet('json_col', '$.name', 'newName', 'newNameCol')
Outputs:
select json_set(`json_col`, '$.name', 'newName') as `newNameCol` from `accounts`

knex('accounts').jsonSet('json_col', '$.name', { "name": "newName" }, 'newNameCol')
Outputs:
select json_set(`json_col`, '$.name', {"name":"newName"}) as `newNameCol` from `accounts`

Return a json value/object/array where a given value is inserted at the given JsonPath. Value can be single value or json object. If a value exists at the given path, the value is not replaced.Not supported by Redshift and versions before Oracle 21c.

knex('accounts').jsonInsert('json_col', '$.name', 'newName', 'newNameCol')
Outputs:
select json_insert(`json_col`, '$.name', 'newName') as `newNameCol` from `accounts`

knex('accounts').jsonInsert('json_col', '$.name', { "name": "newName" }, 'newNameCol')
Outputs:
select json_insert(`json_col`, '$.name', {"name":"newName"}) as `newNameCol` from `accounts`

knex('accounts').jsonInsert(knex.jsonExtract('json_col', '$.otherAccount'), '$.name', { "name": "newName" }, 'newNameCol')
Outputs:
select json_insert(json_unquote(json_extract(`json_col`, '$.otherAccount')), '$.name', {"name":"newName"}) as `newNameCol` from `accounts`

Return a json value/object/array where a given value is removed at the given JsonPath.Not supported by Redshift and versions before Oracle 21c.

knex('accounts').jsonRemove('json_col', '$.name', 'colWithRemove')
Outputs:
select json_remove(`json_col`,'$.name') as `colWithRemove` from `accounts`

knex('accounts').jsonInsert('json_col', '$.name', { "name": "newName" }, 'newNameCol')
Outputs:
select json_insert(`json_col`, '$.name', {"name":"newName"}) as `newNameCol` from `accounts`

Several methods exist to assist in dynamic where clauses. In many places functions may be used in place of values, constructing subqueries. In most places existing knex queries may be used to compose sub-queries, etc. Take a look at a few of the examples for each method for instruction on use:

Important: Supplying knex with an undefined value to any of the where functions will cause knex to throw an error during sql compilation. This is both for yours and our sake. Knex cannot know what to do with undefined values in a where clause, and generally it would be a programmatic error to supply one to begin with. The error will throw a message containing the type of query and the compiled query-string. Example:

Shorthand for .where('id', 'in', obj), the .whereIn and .orWhereIn methods add a "where in" clause to the query. Note that passing empty array as the value results in a query that never returns any rows (WHERE 1 = 0)

knex.select('name').from('users')
  .whereIn('id', [1, 2, 3])
  .orWhereIn('id', [4, 5, 6])
Outputs:
select `name` from `users` where `id` in (1, 2, 3) or `id` in (4, 5, 6)

knex.select('name').from('users')
  .whereIn('account_id', function() {
    this.select('id').from('accounts');
  })
Outputs:
select `name` from `users` where `account_id` in (select `id` from `accounts`)

const subquery = knex.select('id').from('accounts');

knex.select('name').from('users')
  .whereIn('account_id', subquery)
Outputs:
select `name` from `users` where `account_id` in (select `id` from `accounts`)

knex.select('name').from('users')
  .whereIn(['account_id', 'email'], [[3, 'test3@example.com'], [4, 'test4@example.com']])
Outputs:
select `name` from `users` where (`account_id`, `email`) in ((3, 'test3@example.com'), (4, 'test4@example.com'))

knex.select('name').from('users')
  .whereIn(['account_id', 'email'], knex.select('id', 'email').from('accounts'))
Outputs:
select `name` from `users` where (`account_id`, `email`) in (select `id`, `email` from `accounts`)

knex('users').whereNotIn('id', [1, 2, 3])
Outputs:
select * from `users` where `id` not in (1, 2, 3)

knex('users').where('name', 'like', '%Test%').orWhereNotIn('id', [1, 2, 3])
Outputs:
select * from `users` where `name` like '%Test%' or `id` not in (1, 2, 3)

knex('users').whereNull('updated_at')
Outputs:
select * from `users` where `updated_at` is null

knex('users').whereNotNull('created_at')
Outputs:
select * from `users` where `created_at` is not null

knex('users').whereExists(function() {
  this.select('*').from('accounts').whereRaw('users.account_id = accounts.id');
})
Outputs:
select * from `users` where exists (select * from `accounts` where users.account_id = accounts.id)

knex('users').whereExists(knex.select('*').from('accounts').whereRaw('users.account_id = accounts.id'))
Outputs:
select * from `users` where exists (select * from `accounts` where users.account_id = accounts.id)

knex('users').whereNotExists(function() {
  this.select('*').from('accounts').whereRaw('users.account_id = accounts.id');
})
Outputs:
select * from `users` where not exists (select * from `accounts` where users.account_id = accounts.id)

knex('users').whereNotExists(knex.select('*').from('accounts').whereRaw('users.account_id = accounts.id'))
Outputs:
select * from `users` where not exists (select * from `accounts` where users.account_id = accounts.id)

knex('users').whereBetween('votes', [1, 100])
Outputs:
select * from `users` where `votes` between 1 and 100

knex('users').whereNotBetween('votes', [1, 100])
Outputs:
select * from `users` where `votes` not between 1 and 100

Convenience helper for .where(knex.raw(query)).

knex('users').whereRaw('id = ?', [1])
Outputs:
select * from `users` where id = 1

Adds a where clause with case-sensitive substring comparison on a given column with a given value.

knex('users').whereLike('email', '%mail%')
Outputs:
select * from `users` where `email` like '%mail%' COLLATE utf8_bin

knex('users')
.whereLike('email', '%mail%')
.andWhereLike('email', '%.com')
.orWhereLike('email', '%name%')
Outputs:
select * from `users` where `email` like '%mail%' COLLATE utf8_bin and `email` like '%.com' COLLATE utf8_bin or `email` like '%name%' COLLATE utf8_bin

Adds a where clause with case-insensitive substring comparison on a given column with a given value.

knex('users').whereILike('email', '%mail%')
Outputs:
select * from `users` where `email` like '%mail%'

knex('users')
.whereILike('email', '%MAIL%')
.andWhereILike('email', '%.COM')
.orWhereILike('email', '%NAME%')
Outputs:
select * from `users` where `email` like '%MAIL%' and `email` like '%.COM' or `email` like '%NAME%'

Adds a where clause with json object comparison on given json column.

knex('users').whereJsonObject('json_col', { "name" : "user_name"})
Outputs:
select * from `users` where json_contains(`json_col`, '{\"name\":\"user_name\"}')

Adds a where clause with comparison of a value returned by a JsonPath given an operator and a value.

knex('users').whereJsonPath('json_col', '$.age', '>', 18)
Outputs:
select * from `users` where json_extract(`json_col`, '$.age') > 18

knex('users').whereJsonPath('json_col', '$.name', '=', 'username')
Outputs:
select * from `users` where json_extract(`json_col`, '$.name') = 'username'

Adds a where clause where the comparison is true if a json given by the column include a given value. Only on MySQL, PostgreSQL and CockroachDB.

knex('users').whereJsonSupersetOf('hobbies', { "sport" : "foot" })
Outputs:
select * from `users` where json_contains(`hobbies`,'{\"sport\":\"foot\"}')

Adds a where clause where the comparison is true if a json given by the column is included in a given value. Only on MySQL, PostgreSQL and CockroachDB.

// given a hobby column with { "sport" : "tennis" }, the where clause is true
knex('users').whereJsonSubsetOf('hobby', { "sport" : "tennis", "book" : "fantasy" })
Outputs:
select * from `users` where json_contains('{\"sport\":\"tennis\",\"book\":\"fantasy\"}',`hobby`)

The join builder can be used to specify joins between tables, with the first argument being the joining table, the next three arguments being the first join column, the join operator and the second join column, respectively.

knex('users')
  .join('contacts', 'users.id', '=', 'contacts.user_id')
  .select('users.id', 'contacts.phone')
Outputs:
select `users`.`id`, `contacts`.`phone` from `users` inner join `contacts` on `users`.`id` = `contacts`.`user_id`

knex('users')
  .join('contacts', 'users.id', 'contacts.user_id')
  .select('users.id', 'contacts.phone')
Outputs:
select `users`.`id`, `contacts`.`phone` from `users` inner join `contacts` on `users`.`id` = `contacts`.`user_id`

knex.from('users').innerJoin('accounts', 'users.id', 'accounts.user_id')
Outputs:
select * from `users` inner join `accounts` on `users`.`id` = `accounts`.`user_id`

knex.table('users').innerJoin('accounts', 'users.id', '=', 'accounts.user_id')
Outputs:
select * from `users` inner join `accounts` on `users`.`id` = `accounts`.`user_id`

knex('users').innerJoin('accounts', function() {
  this.on('accounts.id', '=', 'users.account_id').orOn('accounts.owner_id', '=', 'users.id')
})
Outputs:
select * from `users` inner join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

knex.select('*').from('users').leftJoin('accounts', 'users.id', 'accounts.user_id')
Outputs:
select * from `users` left join `accounts` on `users`.`id` = `accounts`.`user_id`

knex.select('*').from('users').leftJoin('accounts', function() {
  this.on('accounts.id', '=', 'users.account_id').orOn('accounts.owner_id', '=', 'users.id')
})
Outputs:
select * from `users` left join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

knex.select('*').from('users').leftOuterJoin('accounts', 'users.id', 'accounts.user_id')
Outputs:
select * from `users` left outer join `accounts` on `users`.`id` = `accounts`.`user_id`

knex.select('*').from('users').leftOuterJoin('accounts', function() {
  this.on('accounts.id', '=', 'users.account_id').orOn('accounts.owner_id', '=', 'users.id')
})
Outputs:
select * from `users` left outer join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

knex.select('*').from('users').rightJoin('accounts', 'users.id', 'accounts.user_id')
Outputs:
select * from `users` right join `accounts` on `users`.`id` = `accounts`.`user_id`

knex.select('*').from('users').rightJoin('accounts', function() {
  this.on('accounts.id', '=', 'users.account_id').orOn('accounts.owner_id', '=', 'users.id')
})
Outputs:
select * from `users` right join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

knex.select('*').from('users').rightOuterJoin('accounts', 'users.id', 'accounts.user_id')
Outputs:
select * from `users` right outer join `accounts` on `users`.`id` = `accounts`.`user_id`

knex.select('*').from('users').rightOuterJoin('accounts', function() {
  this.on('accounts.id', '=', 'users.account_id').orOn('accounts.owner_id', '=', 'users.id')
})
Outputs:
select * from `users` right outer join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

knex.select('*').from('users').fullOuterJoin('accounts', 'users.id', 'accounts.user_id')
Outputs:
select * from `users` full outer join `accounts` on `users`.`id` = `accounts`.`user_id`

knex.select('*').from('users').fullOuterJoin('accounts', function() {
  this.on('accounts.id', '=', 'users.account_id').orOn('accounts.owner_id', '=', 'users.id')
})
Outputs:
select * from `users` full outer join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

Cross join conditions are only supported in MySQL and SQLite3. For join conditions rather use innerJoin.

knex.select('*').from('users').crossJoin('accounts')
Outputs:
select * from `users` cross join `accounts`

knex.select('*').from('users').crossJoin('accounts', 'users.id', 'accounts.user_id')
Outputs:
select * from `users` cross join `accounts` on `users`.`id` = `accounts`.`user_id`

knex.select('*').from('users').crossJoin('accounts', function() {
  this.on('accounts.id', '=', 'users.account_id').orOn('accounts.owner_id', '=', 'users.id')
})
Outputs:
select * from `users` cross join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

knex.select('*').from('accounts').joinRaw('natural full join table1').where('id', 1)
Outputs:
select * from `accounts` natural full join table1 where `id` = 1

knex.select('*').from('accounts').join(knex.raw('natural full join table1')).where('id', 1)
Outputs:
select * from `accounts` inner join natural full join table1 where `id` = 1

Adds a onIn clause to the query.

knex.select('*').from('users').join('contacts', function() {
  this.on('users.id', '=', 'contacts.id').onIn('contacts.id', [7, 15, 23, 41])
})
Outputs:
select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`id` in (7, 15, 23, 41)

Adds a onNotIn clause to the query.

knex.select('*').from('users').join('contacts', function() {
  this.on('users.id', '=', 'contacts.id').onNotIn('contacts.id', [7, 15, 23, 41])
})
Outputs:
select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`id` not in (7, 15, 23, 41)

Adds a onNull clause to the query.

knex.select('*').from('users').join('contacts', function() {
  this.on('users.id', '=', 'contacts.id').onNull('contacts.email')
})
Outputs:
select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`email` is null

Adds a onNotNull clause to the query.

knex.select('*').from('users').join('contacts', function() {
  this.on('users.id', '=', 'contacts.id').onNotNull('contacts.email')
})
Outputs:
select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`email` is not null

Adds a onExists clause to the query.

knex.select('*').from('users').join('contacts', function() {
  this.on('users.id', '=', 'contacts.id').onExists(function() {
    this.select('*').from('accounts').whereRaw('users.account_id = accounts.id');
  })
})
Outputs:
select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and exists (select * from `accounts` where users.account_id = accounts.id)

Adds a onNotExists clause to the query.

knex.select('*').from('users').join('contacts', function() {
  this.on('users.id', '=', 'contacts.id').onNotExists(function() {
    this.select('*').from('accounts').whereRaw('users.account_id = accounts.id');
  })
})
Outputs:
select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and not exists (select * from `accounts` where users.account_id = accounts.id)

Adds a onBetween clause to the query.

knex.select('*').from('users').join('contacts', function() {
  this.on('users.id', '=', 'contacts.id').onBetween('contacts.id', [5, 30])
})
Outputs:
select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`id` between 5 and 30

Adds a onNotBetween clause to the query.

knex.select('*').from('users').join('contacts', function() {
  this.on('users.id', '=', 'contacts.id').onNotBetween('contacts.id', [5, 30])
})
Outputs:
select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`id` not between 5 and 30

Adds a onJsonPathEquals clause to the query. The clause performs a join on value returned by two json paths on two json columns.

knex('cities')
  .select('cities.name as cityName', 'country.name as countryName')
  .join('country', function () {
    this.onJsonPathEquals(
      'country_name', // json column in cities
      '$.country.name', // json path to country name in 'country_name' column
      'description', // json column in country
      '$.name' // json field in 'description' column
    );
  })
Outputs:
select `cities`.`name` as `cityName`, `country`.`name` as `countryName` from `cities` inner join `country` on json_extract(`country_name`, '$.country.name') = json_extract(`description`, '$.name')

Clears the specified operator from the query. Available operators: 'select' alias 'columns', 'with', 'select', 'columns', 'where', 'union', 'join', 'group', 'order', 'having', 'limit', 'offset', 'counter', 'counters'. Counter(s) alias for method .clearCounter()

knex.select('email', 'name').from('users').where('id', '<', 10).clear('select').clear('where')
Outputs:
select * from `users`

Deprecated, use clear('select'). Clears all select clauses from the query, excluding subqueries.

knex.select('email', 'name').from('users').clearSelect()
Outputs:
select * from `users`

Deprecated, use clear('where'). Clears all where clauses from the query, excluding subqueries.

knex.select('email', 'name').from('users').where('id', 1).clearWhere()
Outputs:
select `email`, `name` from `users`

Deprecated, use clear('group'). Clears all group clauses from the query, excluding subqueries.

knex.select().from('users').groupBy('id').clearGroup()
Outputs:
select * from `users`

Deprecated, use clear('order'). Clears all order clauses from the query, excluding subqueries.

knex.select().from('users').orderBy('name', 'desc').clearOrder()
Outputs:
select * from `users`

Deprecated, use clear('having'). Clears all having clauses from the query, excluding subqueries.

knex.select().from('users').having('id', '>', 5).clearHaving()
Outputs:
select * from `users`

Clears all increments/decrements clauses from the query.

knex('accounts')
  .where('id', '=', 1)
  .update({ email: 'foo@bar.com' })
  .decrement({
    balance: 50,
  })
  .clearCounters()
Outputs:
update `accounts` set `email` = 'foo@bar.com' where `id` = 1

Sets a distinct clause on the query. If the parameter is falsy or empty array, method falls back to '*'.

// select distinct 'first_name' from customers
knex('customers')
  .distinct('first_name', 'last_name')
Outputs:
select distinct `first_name`, `last_name` from `customers`

// select which eliminates duplicate rows
knex('customers')
 .distinct()
Outputs:
select distinct * from `customers`

PostgreSQL only. Adds a distinctOn clause to the query.

knex('users').distinctOn('age')
Error:
.distinctOn() is currently only supported on PostgreSQL

Adds a group by clause to the query.

knex('users').groupBy('count')
Outputs:
select * from `users` group by `count`

Adds a raw group by clause to the query.

knex.select('year', knex.raw('SUM(profit)')).from('sales').groupByRaw('year WITH ROLLUP')
Outputs:
select `year`, SUM(profit) from `sales` group by year WITH ROLLUP

Adds an order by clause to the query. column can be string, or list mixed with string and object. nulls specify where the nulls values are put (can be 'first' or 'last').

Single Column:

knex('users').orderBy('email')
Outputs:
select * from `users` order by `email` asc

knex('users').orderBy('name', 'desc')
Outputs:
select * from `users` order by `name` desc

knex('users').orderBy('name', 'desc', 'first')
Outputs:
select * from `users` order by (`name` is not null) desc

Multiple Columns:

knex('users').orderBy(['email', { column: 'age', order: 'desc' }])
Outputs:
select * from `users` order by `email` asc, `age` desc

knex('users').orderBy([{ column: 'email' }, { column: 'age', order: 'desc' }])
Outputs:
select * from `users` order by `email` asc, `age` desc

knex('users').orderBy([{ column: 'email' }, { column: 'age', order: 'desc', nulls: 'last' }])
Outputs:
select * from `users` order by `email` asc, (`age` is null) desc

Adds an order by raw clause to the query.

knex.select('*').from('table').orderByRaw('col DESC NULLS LAST')
Outputs:
select * from `table` order by col DESC NULLS LAST

Adds a having clause to the query.

knex('users')
  .groupBy('count')
  .orderBy('name', 'desc')
  .having('count', '>', 100)
Outputs:
select * from `users` group by `count` having `count` > 100 order by `name` desc

Adds a havingIn clause to the query.

knex.select('*').from('users').havingIn('id', [5, 3, 10, 17])
Outputs:
select * from `users` having `id` in (5, 3, 10, 17)

Adds a havingNotIn clause to the query.

knex.select('*').from('users').havingNotIn('id', [5, 3, 10, 17])
Outputs:
select * from `users` having `id` not in (5, 3, 10, 17)

Adds a havingNull clause to the query.

knex.select('*').from('users').havingNull('email')
Outputs:
select * from `users` having `email` is null

Adds a havingNotNull clause to the query.

knex.select('*').from('users').havingNotNull('email')
Outputs:
select * from `users` having `email` is not null

Adds a havingExists clause to the query.

knex.select('*').from('users').havingExists(function() {
  this.select('*').from('accounts').whereRaw('users.account_id = accounts.id');
})
Outputs:
select * from `users` having exists (select * from `accounts` where users.account_id = accounts.id)

Adds a havingNotExists clause to the query.

knex.select('*').from('users').havingNotExists(function() {
  this.select('*').from('accounts').whereRaw('users.account_id = accounts.id');
})
Outputs:
select * from `users` having not exists (select * from `accounts` where users.account_id = accounts.id)

Adds a havingBetween clause to the query.

knex.select('*').from('users').havingBetween('id', [5, 10])
Outputs:
select * from `users` having `id` between 5 and 10

Adds a havingNotBetween clause to the query.

knex.select('*').from('users').havingNotBetween('id', [5, 10])
Outputs:
select * from `users` having `id` not between 5 and 10

Adds a havingRaw clause to the query.

knex('users')
  .groupBy('count')
  .orderBy('name', 'desc')
  .havingRaw('count > ?', [100])
Outputs:
select * from `users` group by `count` having count > 100 order by `name` desc

Adds an offset clause to the query. An optional skipBinding parameter may be specified which would avoid setting offset as a prepared value (some databases don't allow prepared values for offset).

knex.select('*').from('users').offset(10)
Outputs:
select * from `users` limit 18446744073709551615 offset 10

knex.select('*').from('users').offset(10).toSQL().sql
Outputs:
select * from `users` limit 18446744073709551615 offset ?

// Offset value isn't a prepared value.
knex.select('*').from('users').offset(10, {skipBinding: true}).toSQL().sql
Outputs:
select * from `users` limit 18446744073709551615 offset 10

Adds a limit clause to the query. An optional skipBinding parameter may be specified to avoid adding limit as a prepared value (some databases don't allow prepared values for limit).

knex.select('*').from('users').limit(10).offset(30)
Outputs:
select * from `users` limit 10 offset 30

knex.select('*').from('users').limit(10).offset(30).toSQL().sql
Outputs:
select * from `users` limit ? offset ?

// Limit value isn't a prepared value.
knex.select('*').from('users').limit(10, {skipBinding: true}).offset(30).toSQL().sql
Outputs:
select * from `users` limit 10 offset ?

Creates a union query, taking an array or a list of callbacks, builders, or raw statements to build the union statement, with optional boolean wrap. If the wrap parameter is true, the queries will be individually wrapped in parentheses.

knex.select('*').from('users').whereNull('last_name').union(function() {
  this.select('*').from('users').whereNull('first_name')
})
Outputs:
select * from `users` where `last_name` is null union select * from `users` where `first_name` is null

knex.select('*').from('users').whereNull('last_name').union([
  knex.select('*').from('users').whereNull('first_name')
])
Outputs:
select * from `users` where `last_name` is null union select * from `users` where `first_name` is null

knex.select('*').from('users').whereNull('last_name').union(
  knex.raw('select * from users where first_name is null'),
  knex.raw('select * from users where email is null')
)
Outputs:
select * from `users` where `last_name` is null union select * from users where first_name is null union select * from users where email is null

Creates a union all query, with the same method signature as the union method. If the wrap parameter is true, the queries will be individually wrapped in parentheses.

knex.select('*').from('users').whereNull('last_name').unionAll(function() {
  this.select('*').from('users').whereNull('first_name');
})
Outputs:
select * from `users` where `last_name` is null union all select * from `users` where `first_name` is null

knex.select('*').from('users').whereNull('last_name').unionAll([
  knex.select('*').from('users').whereNull('first_name')
])
Outputs:
select * from `users` where `last_name` is null union all select * from `users` where `first_name` is null

knex.select('*').from('users').whereNull('last_name').unionAll(
  knex.raw('select * from users where first_name is null'),
  knex.raw('select * from users where email is null')
)
Outputs:
select * from `users` where `last_name` is null union all select * from users where first_name is null union all select * from users where email is null

Creates an intersect query, taking an array or a list of callbacks, builders, or raw statements to build the intersect statement, with optional boolean wrap. If the wrap parameter is true, the queries will be individually wrapped in parentheses. The intersect method is unsupported on MySQL.

knex.select('*').from('users').whereNull('last_name').intersect(function() {
  this.select('*').from('users').whereNull('first_name')
})
Outputs:
select * from `users` where `last_name` is null intersect select * from `users` where `first_name` is null

knex.select('*').from('users').whereNull('last_name').intersect([
  knex.select('*').from('users').whereNull('first_name')
])
Outputs:
select * from `users` where `last_name` is null intersect select * from `users` where `first_name` is null

knex.select('*').from('users').whereNull('last_name').intersect(
  knex.raw('select * from users where first_name is null'),
  knex.raw('select * from users where email is null')
)
Outputs:
select * from `users` where `last_name` is null intersect select * from users where first_name is null intersect select * from users where email is null

Creates an insert query, taking either a hash of properties to be inserted into the row, or an array of inserts, to be executed as a single insert command. If returning array is passed e.g. ['id', 'title'], it resolves the promise / fulfills the callback with an array of all the added rows with specified columns. It's a shortcut for returning method

// Returns [1] in "mysql", "sqlite", "oracle"; [] in "postgresql" unless the 'returning' parameter is set.
knex('books').insert({title: 'Slaughterhouse Five'})
Outputs:
insert into `books` (`title`) values ('Slaughterhouse Five')

// Normalizes for empty keys on multi-row insert:
knex('coords').insert([{x: 20}, {y: 30},  {x: 10, y: 20}])
Outputs:
insert into `coords` (`x`, `y`) values (20, DEFAULT), (DEFAULT, 30), (10, 20)

// Returns [2] in "mysql", "sqlite"; [2, 3] in "postgresql"
knex.insert([{title: 'Great Gatsby'}, {title: 'Fahrenheit 451'}], ['id']).into('books')
Outputs:
insert into `books` (`title`) values ('Great Gatsby'), ('Fahrenheit 451')

For MSSQL, triggers on tables can interrupt returning a valid value from the standard insert statements. You can add the includeTriggerModifications option to get around this issue. This modifies the SQL so the proper values can be returned. This only modifies the statement if you are using MSSQL, a returning value is specified, and the includeTriggerModifications option is set.

// Adding the option includeTriggerModifications allows you to
// run statements on tables that contain triggers. Only affects MSSQL.
knex('books')
  .insert({title: 'Alice in Wonderland'}, ['id'], { includeTriggerModifications: true })

Implemented for the PostgreSQL, MySQL, and SQLite databases. A modifier for insert queries that specifies alternative behaviour in the case of a conflict. A conflict occurs when a table has a PRIMARY KEY or a UNIQUE index on a column (or a composite index on a set of columns) and a row being inserted has the same value as a row which already exists in the table in those column(s). The default behaviour in case of conflict is to raise an error and abort the query. Using this method you can change this behaviour to either silently ignore the error by using .onConflict().ignore() or to update the existing row with new data (perform an "UPSERT") by using .onConflict().merge().

Note: For PostgreSQL and SQLite, the column(s) specified by this method must either be the table's PRIMARY KEY or have a UNIQUE index on them, or the query will fail to execute. When specifying multiple columns, they must be a composite PRIMARY KEY or have composite UNIQUE index. MySQL will ignore the specified columns and always use the table's PRIMARY KEY. For cross-platform support across PostgreSQL, MySQL, and SQLite you must both explicitly specify the columns in .onConflict() and those column(s) must be the table's PRIMARY KEY.

For PostgreSQL and SQLite, you can use knex.raw(...) function in onConflict. It can be useful to specify condition when you have partial index :

knex('tableName')
  .insert({
    email: "ignore@example.com",
    name: "John Doe",
    active: true
  })
   // ignore only on email conflict and active is true.
  .onConflict(knex.raw('(email) where active'))
  .ignore()

See documentation on .ignore() and .merge() methods for more details.

Implemented for the PostgreSQL, MySQL, and SQLite databases. Modifies an insert query, and causes it to be silently dropped without an error if a conflict occurs. Uses INSERT IGNORE in MySQL, and adds an ON CONFLICT (columns) DO NOTHING clause to the insert statement in PostgreSQL and SQLite.

knex('tableName')
  .insert({
    email: "ignore@example.com",
    name: "John Doe"
  })
  .onConflict('email')
  .ignore()
Outputs:
insert ignore into `tableName` (`email`, `name`) values ('ignore@example.com', 'John Doe')

Implemented for the PostgreSQL, MySQL, and SQLite databases. Modifies an insert query, to turn it into an 'upsert' operation. Uses ON DUPLICATE KEY UPDATE in MySQL, and adds an ON CONFLICT (columns) DO UPDATE clause to the insert statement in PostgreSQL and SQLite. By default, it merges all columns.

knex('tableName')
  .insert({
    email: "ignore@example.com",
    name: "John Doe"
  })
  .onConflict('email')
  .merge()
Outputs:
insert into `tableName` (`email`, `name`) values ('ignore@example.com', 'John Doe') on duplicate key update `email` = values(`email`), `name` = values(`name`)

This also works with batch inserts:

knex('tableName')
  .insert([
    { email: "john@example.com", name: "John Doe" },
    { email: "jane@example.com", name: "Jane Doe" },
    { email: "alex@example.com", name: "Alex Doe" },
  ])
  .onConflict('email')
  .merge()
Outputs:
insert into `tableName` (`email`, `name`) values ('john@example.com', 'John Doe'), ('jane@example.com', 'Jane Doe'), ('alex@example.com', 'Alex Doe') on duplicate key update `email` = values(`email`), `name` = values(`name`)

It is also possible to specify a subset of the columns to merge when a conflict occurs. For example, you may want to set a 'created_at' column when inserting but would prefer not to update it if the row already exists:

const timestamp = Date.now();
knex('tableName')
  .insert({
    email: "ignore@example.com",
    name: "John Doe",
    created_at: timestamp,
    updated_at: timestamp,
  })
  .onConflict('email')
  .merge(['email', 'name', 'updated_at'])

It is also possible to specify data to update separately from the data to insert. This is useful if you want to update with different data to the insert. For example, you may want to change a value if the row already exists:

const timestamp = Date.now();
knex('tableName')
  .insert({
    email: "ignore@example.com",
    name: "John Doe",
    created_at: timestamp,
    updated_at: timestamp,
  })
  .onConflict('email')
  .merge({
    name: "John Doe The Second",
  })

For PostgreSQL/SQLite databases only, it is also possible to add a WHERE clause to conditionally update only the matching rows:

const timestamp = Date.now();
knex('tableName')
  .insert({
    email: "ignore@example.com",
    name: "John Doe",
    created_at: timestamp,
    updated_at: timestamp,
  })
  .onConflict('email')
  .merge({
    name: "John Doe",
    updated_at: timestamp,
  })
  .where('updated_at', '<', timestamp)

Implemented for the CockroachDB. Creates an upsert query, taking either a hash of properties to be inserted into the row, or an array of upserts, to be executed as a single upsert command. If returning array is passed e.g. ['id', 'title'], it resolves the promise / fulfills the callback with an array of all the added rows with specified columns. It's a shortcut for returning method

// insert new row with unique index on title column
knex('books').upsert({title: 'Great Gatsby'})
Error:
Upsert is not yet supported for dialect mysql

// update row by unique title 'Great Gatsby' and insert row with title 'Fahrenheit 451'
knex('books').upsert([{title: 'Great Gatsby'}, {title: 'Fahrenheit 451'}], ['id'])
Error:
Upsert is not yet supported for dialect mysql

// Normalizes for empty keys on multi-row upsert, result sql: ("x", "y") values (20, default), (default, 30), (10, 20):
knex('coords').upsert([{x: 20}, {y: 30}, {x: 10, y: 20}])
Error:
Upsert is not yet supported for dialect mysql

Creates an update query, taking a hash of properties or a key/value pair to be updated based on the other query constraints. If returning array is passed e.g. ['id', 'title'], it resolves the promise / fulfills the callback with an array of all the updated rows with specified columns. It's a shortcut for returning method

knex('books')
  .where('published_date', '<', 2000)
  .update({
    status: 'archived',
    thisKeyIsSkipped: undefined
  })
Outputs:
update `books` set `status` = 'archived' where `published_date` < 2000

// Returns [1] in "mysql", "sqlite", "oracle"; [] in "postgresql" unless the 'returning' parameter is set.
knex('books').update('title', 'Slaughterhouse Five')
Outputs:
update `books` set `title` = 'Slaughterhouse Five'

// Returns [ { id: 42, title: "The Hitchhiker's Guide to the Galaxy" } ]
knex('books')
  .where({ id: 42 })
  .update({ title: "The Hitchhiker's Guide to the Galaxy" }, ['id', 'title'])
Outputs:
update `books` set `title` = 'The Hitchhiker\'s Guide to the Galaxy' where `id` = 42

For MSSQL, triggers on tables can interrupt returning a valid value from the standard update statements. You can add the includeTriggerModifications option to get around this issue. This modifies the SQL so the proper values can be returned. This only modifies the statement if you are using MSSQL, a returning value is specified, and the includeTriggerModifications option is set.

// Adding the option includeTriggerModifications allows you
// to run statements on tables that contain triggers. Only affects MSSQL.
knex('books')
  .update({title: 'Alice in Wonderland'}, ['id', 'title'], { includeTriggerModifications: true })

Aliased to del as delete is a reserved word in JavaScript, this method deletes one or more rows, based on other conditions specified in the query. Resolves the promise / fulfills the callback with the number of affected rows for the query.

knex('accounts')
  .where('activated', false)
  .del()
Outputs:
delete from `accounts` where `activated` = false

For MSSQL, triggers on tables can interrupt returning a valid value from the standard delete statements. You can add the includeTriggerModifications option to get around this issue. This modifies the SQL so the proper values can be returned. This only modifies the statement if you are using MSSQL, a returning value is specified, and the includeTriggerModifications option is set.

// Adding the option includeTriggerModifications allows you
// to run statements on tables that contain triggers. Only affects MSSQL.
knex('books')
  .where('title', 'Alice in Wonderland')
  .del(['id', 'title'], { includeTriggerModifications: true })

For PostgreSQL, Delete statement with joins is both supported with classic 'join' syntax and 'using' syntax.

knex('accounts')
 .where('activated', false)
 .join('accounts', 'accounts.id', 'users.account_id')
 .del()
Outputs:
delete `accounts` from `accounts` inner join `accounts` on `accounts`.`id` = `users`.`account_id` where `activated` = false

Can be used to define in PostgreSQL a delete statement with joins with explicit 'using' syntax. Classic join syntax can be used too.

knex('accounts')
 .where('activated', false)
 .using('accounts')
 .whereRaw('accounts.id = users.account_id')
 .del()
Error:
'using' function is only available in PostgreSQL dialect with Delete statements.

Utilized by PostgreSQL, MSSQL, and Oracle databases, the returning method specifies which column should be returned by the insert, update and delete methods. Passed column parameter may be a string or an array of strings. The SQL result be reported as an array of objects, each containing a single property for each of the specified columns. The returning method is not supported on Amazon Redshift.

// Returns [ { id: 1 } ]
knex('books')
  .returning('id')
  .insert({title: 'Slaughterhouse Five'})
Outputs:
insert into `books` (`title`) values ('Slaughterhouse Five')

// Returns [ { id: 2 } ] in "mysql", "sqlite"; [ { id: 2 }, { id: 3 } ] in "postgresql"
knex('books')
  .returning('id')
  .insert([{title: 'Great Gatsby'}, {title: 'Fahrenheit 451'}])
Outputs:
insert into `books` (`title`) values ('Great Gatsby'), ('Fahrenheit 451')

// Returns [ { id: 1, title: 'Slaughterhouse Five' } ]
knex('books')
  .returning(['id','title'])
  .insert({title: 'Slaughterhouse Five'})
Outputs:
insert into `books` (`title`) values ('Slaughterhouse Five')

For MSSQL, triggers on tables can interrupt returning a valid value from the standard DML statements. You can add the includeTriggerModifications option to get around this issue. This modifies the SQL so the proper values can be returned. This only modifies the statement if you are using MSSQL, a returning value is specified, and the includeTriggerModifications option is set.

// Adding the option includeTriggerModifications allows you
// to run statements on tables that contain triggers. Only affects MSSQL.
knex('books')
  .returning(['id','title'], { includeTriggerModifications: true })
  .insert({title: 'Slaughterhouse Five'})

Used by knex.transaction, the transacting method may be chained to any query and passed the object you wish to join the query as part of the transaction for.

const Promise = require('bluebird');
knex.transaction(function(trx) {
  knex('books').transacting(trx).insert({name: 'Old Books'})
    .then(function(resp) {
      const id = resp[0];
      return someExternalMethod(id, trx);
    })
    .then(trx.commit)
    .catch(trx.rollback);
})
.then(function(resp) {
  console.log('Transaction complete.');
})
.catch(function(err) {
  console.error(err);
});

Dynamically added after a transaction is specified, the forUpdate adds a FOR UPDATE in PostgreSQL and MySQL during a select statement. Not supported on Amazon Redshift due to lack of table locks.

knex('tableName')
  .transacting(trx)
  .forUpdate()
  .select('*')
Outputs:
select * from `tableName` for update

Dynamically added after a transaction is specified, the forShare adds a FOR SHARE in PostgreSQL and a LOCK IN SHARE MODE for MySQL during a select statement. Not supported on Amazon Redshift due to lack of table locks.

knex('tableName')
  .transacting(trx)
  .forShare()
  .select('*')
Outputs:
select * from `tableName` lock in share mode

Dynamically added after a transaction is specified, the forNoKeyUpdate adds a FOR NO KEY UPDATE in PostgreSQL.

knex('tableName')
  .transacting(trx)
  .forNoKeyUpdate()
  .select('*')
Error:
this[this.single.lock] is not a function

Dynamically added after a transaction is specified, the forKeyShare adds a FOR KEY SHARE in PostgreSQL.

knex('tableName')
  .transacting(trx)
  .forKeyShare()
  .select('*')
Error:
this[this.single.lock] is not a function

MySQL 8.0+, MariaDB-10.6+ and PostgreSQL 9.5+ only. This method can be used after a lock mode has been specified with either forUpdate or forShare, and will cause the query to skip any locked rows, returning an empty set if none are available.

knex('tableName')
  .select('*')
  .forUpdate()
  .skipLocked()
Outputs:
select * from `tableName` for update skip locked

MySQL 8.0+, MariaDB-10.3+ and PostgreSQL 9.5+ only. This method can be used after a lock mode has been specified with either forUpdate or forShare, and will cause the query to fail immediately if any selected rows are currently locked.

knex('tableName')
  .select('*')
  .forUpdate()
  .noWait()
Outputs:
select * from `tableName` for update nowait

Performs a count on the specified column or array of columns (note that some drivers do not support multiple columns). Also accepts raw expressions. The value returned from count (and other aggregation queries) is an array of objects like: [{'COUNT(*)': 1}]. The actual keys are dialect specific, so usually we would want to specify an alias (Refer examples below). Note that in Postgres, count returns a bigint type which will be a String and not a Number (more info).

knex('users').count('active')
Outputs:
select count(`active`) from `users`

knex('users').count('active', {as: 'a'})
Outputs:
select count(`active`) as `a` from `users`

knex('users').count('active as a')
Outputs:
select count(`active`) as `a` from `users`

knex('users').count({ a: 'active' })
Outputs:
select count(`active`) as `a` from `users`

knex('users').count({ a: 'active', v: 'valid' })
Outputs:
select count(`active`) as `a`, count(`valid`) as `v` from `users`

knex('users').count('id', 'active')
Outputs:
select count(`id`) from `users`

knex('users').count({ count: ['id', 'active'] })
Outputs:
select count(`id`, `active`) as `count` from `users`

knex('users').count(knex.raw('??', ['active']))
Outputs:
select count(`active`) from `users`

Usage with TypeScript

The value of count will, by default, have type of string | number. This may be counter-intuitive but some connectors (eg. postgres) will automatically cast BigInt result to string when javascript's Number type is not large enough for the value.

knex('users').count('age') // Resolves to: Record<string, number | string>

knex('users').count({count: '*'}) // Resolves to { count?: string | number | undefined; }

Working with string | number can be inconvenient if you are not working with large tables. Two alternatives are available:

// Be explicit about what you want as a result:
knex('users').count<Record<string, number>>('age');

// Setup a one time declaration to make knex use number as result type for all
// count and countDistinct invocations (for any table)
declare module "knex/types/result" {
    interface Registry {
        Count: number;
    }
}

Gets the minimum value for the specified column or array of columns (note that some drivers do not support multiple columns). Also accepts raw expressions.

knex('users').min('age')
Outputs:
select min(`age`) from `users`

knex('users').min('age', {as: 'a'})
Outputs:
select min(`age`) as `a` from `users`

knex('users').min('age as a')
Outputs:
select min(`age`) as `a` from `users`

knex('users').min({ a: 'age' })
Outputs:
select min(`age`) as `a` from `users`

knex('users').min({ a: 'age', b: 'experience' })
Outputs:
select min(`age`) as `a`, min(`experience`) as `b` from `users`

knex('users').min('age', 'logins')
Outputs:
select min(`age`) from `users`

knex('users').min({ min: ['age', 'logins'] })
Outputs:
select min(`age`, `logins`) as `min` from `users`

knex('users').min(knex.raw('??', ['age']))
Outputs:
select min(`age`) from `users`

Gets the maximum value for the specified column or array of columns (note that some drivers do not support multiple columns). Also accepts raw expressions.

knex('users').max('age')
Outputs:
select max(`age`) from `users`

knex('users').max('age', {as: 'a'})
Outputs:
select max(`age`) as `a` from `users`

knex('users').max('age as a')
Outputs:
select max(`age`) as `a` from `users`

knex('users').max({ a: 'age' })
Outputs:
select max(`age`) as `a` from `users`

knex('users').max('age', 'logins')
Outputs:
select max(`age`) from `users`

knex('users').max({ max: ['age', 'logins'] })
Outputs:
select max(`age`, `logins`) as `max` from `users`

knex('users').max({ max: 'age', exp: 'experience' })
Outputs:
select max(`age`) as `max`, max(`experience`) as `exp` from `users`

knex('users').max(knex.raw('??', ['age']))
Outputs:
select max(`age`) from `users`

Retrieve the sum of the values of a given column or array of columns (note that some drivers do not support multiple columns). Also accepts raw expressions.

knex('users').sum('products')
Outputs:
select sum(`products`) from `users`

knex('users').sum('products as p')
Outputs:
select sum(`products`) as `p` from `users`

knex('users').sum({ p: 'products' })
Outputs:
select sum(`products`) as `p` from `users`

knex('users').sum('products', 'orders')
Outputs:
select sum(`products`) from `users`

knex('users').sum({ sum: ['products', 'orders'] })
Outputs:
select sum(`products`, `orders`) as `sum` from `users`

knex('users').sum(knex.raw('??', ['products']))
Outputs:
select sum(`products`) from `users`

Retrieve the average of the values of a given column or array of columns (note that some drivers do not support multiple columns). Also accepts raw expressions.

knex('users').avg('age')
Outputs:
select avg(`age`) from `users`

knex('users').avg('age as a')
Outputs:
select avg(`age`) as `a` from `users`

knex('users').avg({ a: 'age' })
Outputs:
select avg(`age`) as `a` from `users`

knex('users').avg('age', 'logins')
Outputs:
select avg(`age`) from `users`

knex('users').avg({ avg: ['age', 'logins'] })
Outputs:
select avg(`age`, `logins`) as `avg` from `users`

knex('users').avg(knex.raw('??', ['age']))
Outputs:
select avg(`age`) from `users`

Increments a column value by the specified amount. Object syntax is supported for column.

knex('accounts')
  .where('userid', '=', 1)
  .increment('balance', 10)
Outputs:
update `accounts` set `balance` = `balance` + 10 where `userid` = 1

knex('accounts')
  .where('id', '=', 1)
  .increment({
    balance: 10,
    times: 1,
  })
Outputs:
update `accounts` set `balance` = `balance` + 10, `times` = `times` + 1 where `id` = 1

Decrements a column value by the specified amount. Object syntax is supported for column.

knex('accounts').where('userid', '=', 1).decrement('balance', 5)
Outputs:
update `accounts` set `balance` = `balance` - 5 where `userid` = 1

knex('accounts')
  .where('id', '=', 1)
  .decrement({
    balance: 50,
  })
Outputs:
update `accounts` set `balance` = `balance` - 50 where `id` = 1

Truncates the current table.

knex('accounts').truncate()
Outputs:
truncate `accounts`

This will pluck the specified column from each row in your results, yielding a promise which resolves to the array of values selected.

knex.table('users').pluck('id').then(function(ids) { console.log(ids); });

Similar to select, but only retrieves & resolves with the first record from the query.

knex.table('users').first('id', 'name').then(function(row) { console.log(row); });

Add hints to the query using comment-like syntax /*+ ... */. MySQL and Oracle use this syntax for optimizer hints. Also various DB proxies and routers use this syntax to pass hints to alter their behavior. In other dialects the hints are ignored as simple comments.

knex('accounts').where('userid', '=', 1).hintComment('NO_ICP(accounts)')
Outputs:
select /*+ NO_ICP(accounts) */ * from `accounts` where `userid` = 1

Clones the current query chain, useful for re-using partial query snippets in other queries without mutating the original.

Add a dense_rank() call to your query. For all the following queries, alias can be set to a falsy value if not needed.

String Syntax — .denseRank(alias, orderByClause, [partitionByClause]) :

knex('users').select('*').denseRank('alias_name', 'email', 'firstName')
Outputs:
select *, dense_rank() over (partition by `firstName` order by `email`) as alias_name from `users`

It also accepts arrays of strings as argument :

knex('users').select('*').denseRank('alias_name', ['email', 'address'], ['firstName', 'lastName'])
Outputs:
select *, dense_rank() over (partition by `firstName`, `lastName` order by `email`, `address`) as alias_name from `users`

Raw Syntax — .denseRank(alias, rawQuery) :

knex('users').select('*').denseRank('alias_name', knex.raw('order by ??', ['email']))
Outputs:
select *, dense_rank() over (order by `email`) as alias_name from `users`

Function Syntax — .denseRank(alias, function) :

Use orderBy() and partitionBy() (both chainable) to build your query :

knex('users').select('*').denseRank('alias_name', function() {
  this.orderBy('email').partitionBy('firstName')
})
Outputs:
select *, dense_rank() over (partition by `firstName` order by `email`) as alias_name from `users`

Add a rank() call to your query. For all the following queries, alias can be set to a falsy value if not needed.

String Syntax — .rank(alias, orderByClause, [partitionByClause]) :

knex('users').select('*').rank('alias_name', 'email', 'firstName')
Outputs:
select *, rank() over (partition by `firstName` order by `email`) as alias_name from `users`

It also accepts arrays of strings as argument :

knex('users').select('*').rank('alias_name', ['email', 'address'], ['firstName', 'lastName'])
Outputs:
select *, rank() over (partition by `firstName`, `lastName` order by `email`, `address`) as alias_name from `users`

Raw Syntax — .rank(alias, rawQuery) :

knex('users').select('*').rank('alias_name', knex.raw('order by ??', ['email']))
Outputs:
select *, rank() over (order by `email`) as alias_name from `users`

Function Syntax — .rank(alias, function) :

Use orderBy() and partitionBy() (both chainable) to build your query :

knex('users').select('*').rank('alias_name', function() {
  this.orderBy('email').partitionBy('firstName')
})
Outputs:
select *, rank() over (partition by `firstName` order by `email`) as alias_name from `users`

Add a row_number() call to your query. For all the following queries, alias can be set to a falsy value if not needed.

String Syntax — .rowNumber(alias, orderByClause, [partitionByClause]) :

knex('users').select('*').rowNumber('alias_name', 'email', 'firstName')
Outputs:
select *, row_number() over (partition by `firstName` order by `email`) as alias_name from `users`

It also accepts arrays of strings as argument :

knex('users').select('*').rowNumber('alias_name', ['email', 'address'], ['firstName', 'lastName'])
Outputs:
select *, row_number() over (partition by `firstName`, `lastName` order by `email`, `address`) as alias_name from `users`

Raw Syntax — .rowNumber(alias, rawQuery) :

knex('users').select('*').rowNumber('alias_name', knex.raw('order by ??', ['email']))
Outputs:
select *, row_number() over (order by `email`) as alias_name from `users`

Function Syntax — .rowNumber(alias, function) :

Use orderBy() and partitionBy() (both chainable) to build your query :

knex('users').select('*').rowNumber('alias_name', function() {
  this.orderBy('email').partitionBy('firstName')
})
Outputs:
select *, row_number() over (partition by `firstName` order by `email`) as alias_name from `users`

Partitions rowNumber, denseRank, rank after a specific column or columns. If direction is not supplied it will default to ascending order.

No direction sort :

knex('users').select('*').rowNumber('alias_name', function() {
  this.partitionBy('firstName');
});

With direction sort :

knex('users').select('*').rowNumber('alias_name', function() {
  this.partitionBy('firstName', 'desc');
});

With multiobject :

knex('users').select('*').rowNumber('alias_name', function() {
  this.partitionBy([{ column: 'firstName', order: 'asc' }, { column: 'lastName', order: 'desc' }]);
});

Allows encapsulating and re-using query snippets and common behaviors as functions. The callback function should receive the query builder as its first argument, followed by the rest of the (optional) parameters passed to modify.

const withUserName = function(queryBuilder, foreignKey) {
  queryBuilder.leftJoin('users', foreignKey, 'users.id').select('users.user_name');
};
knex.table('articles').select('title', 'body').modify(withUserName, 'articles_user.id').then(function(article) {
  console.log(article.user_name);
});

Returns an object with the column info about the current table, or an individual column if one is passed, returning an object with the following keys:

• defaultValue: the default value for the column
• type: the column type
• maxLength: the max length set for the column
• nullable: whether the column may be null

knex('users').columnInfo().then(function(info) { // ... });

Overrides the global debug setting for the current query chain. If enabled is omitted, query debugging will be turned on.

The method sets the db connection to use for the query without using the connection pool. You should pass to it the same object that acquireConnection() for the corresponding driver returns

const Pool = require('pg-pool')
const pool = new Pool({ ... })
const connection = await pool.connect();
  try {
    return await knex.connection(connection); // knex here is a query builder with query already built
  } catch (error) {
    // Process error
  } finally {
    connection.release();
  }

Allows for mixing in additional options as defined by database client specific libraries:

knex('accounts as a1')
  .leftJoin('accounts as a2', function() {
    this.on('a1.email', '<>', 'a2.email');
  })
  .select(['a1.email', 'a2.email'])
  .where(knex.raw('a1.id = 1'))
  .options({ nestTables: true, rowMode: 'array' })
  .limit(2)
  .then(...

Allows for configuring a context to be passed to the wrapIdentifier and postProcessResponse hooks:

knex('accounts as a1')
  .queryContext({ foo: 'bar' })
  .select(['a1.email', 'a2.email'])

The context can be any kind of value and will be passed to the hooks without modification. However, note that objects will be shallow-cloned when a query builder instance is cloned, which means that they will contain all the properties of the original object but will not be the same object reference. This allows modifying the context for the cloned query builder instance.

Calling queryContext with no arguments will return any context configured for the query builder instance.

Important: this feature is experimental and its API may change in the future.

It allows to add custom function to the Query Builder.

Example:

If using TypeScript, you can extend the QueryBuilder interface with your custom method.

1. Create a knex.d.ts file inside a @types folder (or any other folder).

2. Add the new @types folder to typeRoots in your tsconfig.json.

Transactions are an important feature of relational databases, as they allow correct recovery from failures and keep a database consistent even in cases of system failure. All queries within a transaction are executed on the same database connection, and run the entire set of queries as a single unit of work. Any failure will mean the database will rollback any queries executed on that connection to the pre-transaction state.

Transactions are handled by passing a handler function into knex.transaction. The handler function accepts a single argument, an object which may be used in two ways:

1. As the "promise aware" knex connection
2. As an object passed into a query with and eventually call commit or rollback.

Throwing an error directly from the transaction handler function automatically rolls back the transaction, same as returning a rejected promise.

Notice that if a promise is not returned within the handler, it is up to you to ensure trx.commit, or trx.rollback are called, otherwise the transaction connection will hang.

Calling trx.rollback will return a rejected Promise. If you don't pass any argument to trx.rollback, a generic Error object will be created and passed in to ensure the Promise always rejects with something.

Note that Amazon Redshift does not support savepoints in transactions.

Specifies the schema to be used when using the schema-building commands.

knex.schema.withSchema('public').createTable('users', function (table) {
  table.increments();
})
Outputs:
create table `public`.`users` (`id` int unsigned not null auto_increment primary key)

Creates a new table on the database, with a callback function to modify the table's structure, using the schema-building commands.

knex.schema.createTable('users', function (table) {
  table.increments();
  table.string('name');
  table.timestamps();
})
Outputs:
create table `users` (`id` int unsigned not null auto_increment primary key, `name` varchar(255), `created_at` datetime, `updated_at` datetime)

Creates a new table on the database based on another table. Copy only the structure : columns, keys and indexes (expected on SQL Server which only copy columns) and not the data. Callback function can be specified to add columns in the duplicated table.

knex.schema.createTableLike('new_users', 'users')
Outputs:
create table `new_users` like `users`

// "new_users" table contains columns of users and two new columns 'age' and 'last_name'.
knex.schema.createTableLike('new_users', 'users', (table) => {
  table.integer('age');
  table.string('last_name');
})
Outputs:
create table `new_users` like `users`;
alter table `new_users` add `age` int, add `last_name` varchar(255)

Drops a table, specified by tableName.

knex.schema.dropTable('users')
Outputs:
drop table `users`

Drops a table conditionally if the table exists, specified by tableName.

knex.schema.dropTableIfExists('users')
Outputs:
drop table if exists `users`

Renames a table from a current tableName to another.

knex.schema.renameTable('users', 'old_users')
Outputs:
rename table `users` to `old_users`

Checks for a table's existence by tableName, resolving with a boolean to signal if the table exists.

knex.schema.hasTable('users').then(function(exists) {
  if (!exists) {
    return knex.schema.createTable('users', function(t) {
      t.increments('id').primary();
      t.string('first_name', 100);
      t.string('last_name', 100);
      t.text('bio');
    });
  }
});

Checks if a column exists in the current table, resolves the promise with a boolean, true if the column exists, false otherwise.

Chooses a database table, and then modifies the table, using the Schema Building functions inside of the callback.

knex.schema.table('users', function (table) {
  table.dropColumn('name');
  table.string('first_name');
  table.string('last_name');
})
Outputs:
alter table `users` add `first_name` varchar(255), add `last_name` varchar(255);
alter table `users` drop `name`

Chooses a database table, and then modifies the table, using the Schema Building functions inside of the callback.

knex.schema.alterTable('users', function (table) {
  table.dropColumn('name');
  table.string('first_name');
  table.string('last_name');
})
Outputs:
alter table `users` add `first_name` varchar(255), add `last_name` varchar(255);
alter table `users` drop `name`