Knex Query Builder

The heart of the library, the knex query builder is the interface used for building and executing standard SQL queries, such as select, insert, update, delete.

Identifier Syntax

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.

INFO

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

knex({ a: 'table', b: 'table' })
  .select({
    aTitle: 'a.title',
    bTitle: 'b.title',
  })
  .whereRaw('?? = ??', ['a.column_1', 'b.column_2']);

select "a"."title" as "aTitle", "b"."title" as "bTitle" from "table" as "a", "table" as "b" where "a"."column_1" = "b"."column_2"

select [a].[title] as [aTitle], [b].[title] as [bTitle] from [table] as [a], [table] as [b] where [a].[column_1] = [b].[column_2]

select `a`.`title` as `aTitle`, `b`.`title` as `bTitle` from `table` as `a`, `table` as `b` where `a`.`column_1` = `b`.`column_2`

select "a"."title" "aTitle", "b"."title" "bTitle" from "table" "a", "table" "b" where "a"."column_1" = "b"."column_2"

select "a"."title" as "aTitle", "b"."title" as "bTitle" from "table" as "a", "table" as "b" where "a"."column_1" = "b"."column_2"

select "a"."title" as "aTitle", "b"."title" as "bTitle" from "table" as "a", "table" as "b" where "a"."column_1" = "b"."column_2"

select `a`.`title` as `aTitle`, `b`.`title` as `bTitle` from `table` as `a`, `table` as `b` where `a`.`column_1` = `b`.`column_2`

Common

knex

knex(tableName, options)knex.[methodName]

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.

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

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

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).

only (knex/from option) PG only

The only option is a second argument you can pass to knex(tableName, options) or .from(tableName, options). When only: true is set, Knex prefixes the table name with the ONLY keyword, which tells PostgreSQL to read rows from the named table only, excluding rows inherited from child tables. This is useful when table inheritance is in use and you want to avoid pulling in data from descendants.

WARNING

Only supported in PostgreSQL for now.

knex('users', { only: true }).select('*');
knex.select('*').from('users', { only: true });

timeout PG+MY only

.timeout(ms, options={cancel: boolean})

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.

WARNING

Only supported in MySQL and PostgreSQL for now.

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

select

.select([*columns])

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');
knex.select().table('books');

select "title", "author", "year" from "books"

-- ----

select * from "books"

select [title], [author], [year] from [books]

-- ----

select * from [books]

select `title`, `author`, `year` from `books`

-- ----

select * from `books`

select "title", "author", "year" from "books"

-- ----

select * from "books"

select "title", "author", "year" from "books"

-- ----

select * from "books"

select "title", "author", "year" from "books"

-- ----

select * from "books"

select `title`, `author`, `year` from `books`

-- ----

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; }[]

select "id" from "users"

-- ----

select "users"."id" from "users"

-- ----

select "users"."id" from "users"

-- ----

select "id" as "identifier" from "users"

-- ----

select "id" as "identifier" from "users"

select [id] from [users]

-- ----

select [users].[id] from [users]

-- ----

select [users].[id] from [users]

-- ----

select [id] as [identifier] from [users]

-- ----

select [id] as [identifier] from [users]

select `id` from `users`

-- ----

select `users`.`id` from `users`

-- ----

select `users`.`id` from `users`

-- ----

select `id` as `identifier` from `users`

-- ----

select `id` as `identifier` from `users`

select "id" from "users"

-- ----

select "users"."id" from "users"

-- ----

select "users"."id" from "users"

-- ----

select "id" "identifier" from "users"

-- ----

select "id" as "identifier" from "users"

select "id" from "users"

-- ----

select "users"."id" from "users"

-- ----

select "users"."id" from "users"

-- ----

select "id" as "identifier" from "users"

-- ----

select "id" as "identifier" from "users"

select "id" from "users"

-- ----

select "users"."id" from "users"

-- ----

select "users"."id" from "users"

-- ----

select "id" as "identifier" from "users"

-- ----

select "id" as "identifier" from "users"

select `id` from `users`

-- ----

select `users`.`id` from `users`

-- ----

select `users`.`id` from `users`

-- ----

select `id` as `identifier` from `users`

-- ----

select `id` as `identifier` from `users`

as

.as(name)

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

select avg("sum_column1") from (select sum("column1") as "sum_column1" from "t1" group by "column1") as "t1"

select avg([sum_column1]) from (select sum([column1]) as [sum_column1] from [t1] group by [column1]) as [t1]

select avg(`sum_column1`) from (select sum(`column1`) as `sum_column1` from `t1` group by `column1`) as `t1`

select avg("sum_column1") from (select sum("column1") "sum_column1" from "t1" group by "column1") "t1"

select avg("sum_column1") from (select sum("column1") as "sum_column1" from "t1" group by "column1") as "t1"

select avg("sum_column1") from (select sum("column1") as "sum_column1" from "t1" group by "column1") as "t1"

select avg(`sum_column1`) from (select sum(`column1`) as `sum_column1` from `t1` group by `column1`) as `t1`

column

.column(columns)

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');
knex.column(['title', 'author', 'year']).select().from('books');
knex.column('title', { by: 'author' }, 'year').select().from('books');

select "title", "author", "year" from "books"

-- ----

select "title", "author", "year" from "books"

-- ----

select "title", "author" as "by", "year" from "books"

select [title], [author], [year] from [books]

-- ----

select [title], [author], [year] from [books]

-- ----

select [title], [author] as [by], [year] from [books]

select `title`, `author`, `year` from `books`

-- ----

select `title`, `author`, `year` from `books`

-- ----

select `title`, `author` as `by`, `year` from `books`

select "title", "author", "year" from "books"

-- ----

select "title", "author", "year" from "books"

-- ----

select "title", "author" "by", "year" from "books"

select "title", "author", "year" from "books"

-- ----

select "title", "author", "year" from "books"

-- ----

select "title", "author" as "by", "year" from "books"

select "title", "author", "year" from "books"

-- ----

select "title", "author", "year" from "books"

-- ----

select "title", "author" as "by", "year" from "books"

select `title`, `author`, `year` from `books`

-- ----

select `title`, `author`, `year` from `books`

-- ----

select `title`, `author` as `by`, `year` from `books`

from

.from([tableName], options={only: boolean})

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.

WARNING

Only supported in PostgreSQL for now.

knex.select('*').from('users');

select * from "users"

select * from [users]

select * from `users`

select * from "users"

select * from "users"

select * from "users"

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">[]

select "id" from "users"

-- ----

select "id" from "users"

select [id] from [users]

-- ----

select [id] from [users]

select `id` from `users`

-- ----

select `id` from `users`

select "id" from "users"

-- ----

select "id" from "users"

select "id" from "users"

-- ----

select "id" from "users"

select "id" from "users"

-- ----

select "id" from "users"

select `id` from `users`

-- ----

select `id` from `users`

fromRaw

.fromRaw(sql, [bindings])

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

select * from (select * from "users" where "age" > ?)

select * from (select * from "users" where "age" > ?)

select * from (select * from "users" where "age" > ?)

select * from (select * from "users" where "age" > ?)

select * from (select * from "users" where "age" > ?)

select * from (select * from "users" where "age" > ?)

select * from (select * from "users" where "age" > ?)

with MYCRRS

.with(alias, [columns], callback|builder|raw)

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');
knex
  .with(
    'with_alias',
    ['title'],
    knex.raw('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');

with "with_alias" as (select * from "books" where "author" = ?) select * from "with_alias"

-- ----

with "with_alias"("title") as (select "title" from "books" where "author" = ?) select * from "with_alias"

-- ----

with "with_alias" as (select * from "books" where "author" = ?) select * from "with_alias"

with [with_alias] as (select * from "books" where "author" = ?) select * from [with_alias]

-- ----

with [with_alias]([title]) as (select "title" from "books" where "author" = ?) select * from [with_alias]

-- ----

with [with_alias] as (select * from [books] where [author] = ?) select * from [with_alias]

with `with_alias` as (select * from "books" where "author" = ?) select * from `with_alias`

-- ----

with `with_alias`(`title`) as (select "title" from "books" where "author" = ?) select * from `with_alias`

-- ----

with `with_alias` as (select * from `books` where `author` = ?) select * from `with_alias`

with "with_alias" as (select * from "books" where "author" = ?) select * from "with_alias"

-- ----

with "with_alias"("title") as (select "title" from "books" where "author" = ?) select * from "with_alias"

-- ----

with "with_alias" as (select * from "books" where "author" = ?) select * from "with_alias"

with "with_alias" as (select * from "books" where "author" = ?) select * from "with_alias"

-- ----

with "with_alias"("title") as (select "title" from "books" where "author" = ?) select * from "with_alias"

-- ----

with "with_alias" as (select * from "books" where "author" = ?) select * from "with_alias"

with "with_alias" as (select * from "books" where "author" = ?) select * from "with_alias"

-- ----

with "with_alias"("title") as (select "title" from "books" where "author" = ?) select * from "with_alias"

-- ----

with "with_alias" as (select * from "books" where "author" = ?) select * from "with_alias"

with `with_alias` as (select * from "books" where "author" = ?) select * from `with_alias`

-- ----

with `with_alias`(`title`) as (select "title" from "books" where "author" = ?) select * from `with_alias`

-- ----

with `with_alias` as (select * from `books` where `author` = ?) select * from `with_alias`

withRecursive MYCRRS

.withRecursive(alias, [columns], callback|builder|raw)

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. When using union/unionAll, both terms must return the same columns and types; avoid select('*') in the recursive term if it introduces a join.

knex
  .withRecursive('ancestors', (qb) => {
    qb.select('people.*')
      .from('people')
      .where('people.id', 1)
      .unionAll((qb) => {
        qb.select('people.*')
          .from('people')
          .join('ancestors', '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');

with recursive "ancestors" as (select "people".* from "people" where "people"."id" = ? union all select "people".* from "people" inner join "ancestors" on "ancestors"."parentId" = "people"."id") select * from "ancestors"

-- ----

with recursive "family"("name", "parentName") as (select "name", "parentName" from "folks" where "name" = ? union all select "folks"."name", "folks"."parentName" from "folks" inner join "family" on "family"."parentName" = "folks"."name") select "name" from "family"

with [ancestors] as (select [people].* from [people] where [people].[id] = ? union all select [people].* from [people] inner join [ancestors] on [ancestors].[parentId] = [people].[id]) select * from [ancestors]

-- ----

with [family]([name], [parentName]) as (select [name], [parentName] from [folks] where [name] = ? union all select [folks].[name], [folks].[parentName] from [folks] inner join [family] on [family].[parentName] = [folks].[name]) select [name] from [family]

with recursive `ancestors` as (select `people`.* from `people` where `people`.`id` = ? union all select `people`.* from `people` inner join `ancestors` on `ancestors`.`parentId` = `people`.`id`) select * from `ancestors`

-- ----

with recursive `family`(`name`, `parentName`) as (select `name`, `parentName` from `folks` where `name` = ? union all select `folks`.`name`, `folks`.`parentName` from `folks` inner join `family` on `family`.`parentName` = `folks`.`name`) select `name` from `family`

with "ancestors" as (select "people".* from "people" where "people"."id" = ? union all select "people".* from "people" inner join "ancestors" on "ancestors"."parentId" = "people"."id") select * from "ancestors"

-- ----

with "family"("name", "parentName") as (select "name", "parentName" from "folks" where "name" = ? union all select "folks"."name", "folks"."parentName" from "folks" inner join "family" on "family"."parentName" = "folks"."name") select "name" from "family"

with recursive "ancestors" as (select "people".* from "people" where "people"."id" = ? union all select "people".* from "people" inner join "ancestors" on "ancestors"."parentId" = "people"."id") select * from "ancestors"

-- ----

with recursive "family"("name", "parentName") as (select "name", "parentName" from "folks" where "name" = ? union all select "folks"."name", "folks"."parentName" from "folks" inner join "family" on "family"."parentName" = "folks"."name") select "name" from "family"

with recursive "ancestors" as (select "people".* from "people" where "people"."id" = ? union all select "people".* from "people" inner join "ancestors" on "ancestors"."parentId" = "people"."id") select * from "ancestors"

-- ----

with recursive "family"("name", "parentName") as (select "name", "parentName" from "folks" where "name" = ? union all select "folks"."name", "folks"."parentName" from "folks" inner join "family" on "family"."parentName" = "folks"."name") select "name" from "family"

with recursive `ancestors` as (select `people`.* from `people` where `people`.`id` = ? union all select `people`.* from `people` inner join `ancestors` on `ancestors`.`parentId` = `people`.`id`) select * from `ancestors`

-- ----

with recursive `family`(`name`, `parentName`) as (select `name`, `parentName` from `folks` where `name` = ? union all select `folks`.`name`, `folks`.`parentName` from `folks` inner join `family` on `family`.`parentName` = `folks`.`name`) select `name` from `family`

withMaterialized PG+SQ only

.withMaterialized(alias, [columns], callback|builder|raw)

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');
knex
  .withMaterialized(
    'with_alias',
    ['title'],
    knex.raw('select "title" from "books" where "author" = ?', 'Test')
  )
  .select('*')
  .from('with_alias');
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

-- ----

Error: With materialized is not supported by this dialect

-- ----

Error: With materialized is not supported by this dialect

Error: With materialized is not supported by this dialect

-- ----

Error: With materialized is not supported by this dialect

-- ----

Error: With materialized is not supported by this dialect

Error: With materialized is not supported by this dialect

-- ----

Error: With materialized is not supported by this dialect

-- ----

Error: With materialized is not supported by this dialect

Error: With materialized is not supported by this dialect

-- ----

Error: With materialized is not supported by this dialect

-- ----

Error: With materialized is not supported by this dialect

with "with_alias" as materialized (select * from "books" where "author" = ?) select * from "with_alias"

-- ----

with "with_alias"("title") as materialized (select "title" from "books" where "author" = ?) select * from "with_alias"

-- ----

with "with_alias" as materialized (select * from "books" where "author" = ?) select * from "with_alias"

with "with_alias" as materialized (select * from "books" where "author" = ?) select * from "with_alias"

-- ----

with "with_alias"("title") as materialized (select "title" from "books" where "author" = ?) select * from "with_alias"

-- ----

with "with_alias" as materialized (select * from "books" where "author" = ?) select * from "with_alias"

with `with_alias` as materialized (select * from "books" where "author" = ?) select * from `with_alias`

-- ----

with `with_alias`(`title`) as materialized (select "title" from "books" where "author" = ?) select * from `with_alias`

-- ----

with `with_alias` as materialized (select * from `books` where `author` = ?) select * from `with_alias`

withNotMaterialized PG+SQ only

.withNotMaterialized(alias, [columns], callback|builder|raw)

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');
knex
  .withNotMaterialized(
    'with_alias',
    ['title'],
    knex.raw('select "title" from "books" where "author" = ?', 'Test')
  )
  .select('*')
  .from('with_alias');
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

-- ----

Error: With materialized is not supported by this dialect

-- ----

Error: With materialized is not supported by this dialect

Error: With materialized is not supported by this dialect

-- ----

Error: With materialized is not supported by this dialect

-- ----

Error: With materialized is not supported by this dialect

Error: With materialized is not supported by this dialect

-- ----

Error: With materialized is not supported by this dialect

-- ----

Error: With materialized is not supported by this dialect

Error: With materialized is not supported by this dialect

-- ----

Error: With materialized is not supported by this dialect

-- ----

Error: With materialized is not supported by this dialect

with "with_alias" as not materialized (select * from "books" where "author" = ?) select * from "with_alias"

-- ----

with "with_alias"("title") as not materialized (select "title" from "books" where "author" = ?) select * from "with_alias"

-- ----

with "with_alias" as not materialized (select * from "books" where "author" = ?) select * from "with_alias"

with "with_alias" as not materialized (select * from "books" where "author" = ?) select * from "with_alias"

-- ----

with "with_alias"("title") as not materialized (select "title" from "books" where "author" = ?) select * from "with_alias"

-- ----

with "with_alias" as not materialized (select * from "books" where "author" = ?) select * from "with_alias"

with `with_alias` as not materialized (select * from "books" where "author" = ?) select * from `with_alias`

-- ----

with `with_alias`(`title`) as not materialized (select "title" from "books" where "author" = ?) select * from `with_alias`

-- ----

with `with_alias` as not materialized (select * from `books` where `author` = ?) select * from `with_alias`

withSchema

.withSchema([schemaName])

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

knex.withSchema('public').select('*').from('users');

select * from "public"."users"

select * from [public].[users]

select * from `public`.`users`

select * from "public"."users"

select * from "public"."users"

select * from "public"."users"

select * from `public`.`users`

jsonExtract

.jsonExtract(column|builder|raw|array[], path, [alias], [singleValue])

Extract a value from a json column given a JsonPath. An alias can be specified. The singleValue boolean can 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');
knex('accounts').jsonExtract('json_col', '$.name', 'accountName');
knex('accounts').jsonExtract('json_col', '$.name', 'accountName', true);
knex('accounts').jsonExtract([
  ['json_col', '$.name', 'accountName'],
  ['json_col', '$.lastName', 'accountLastName'],
]);

select json_extract_path("json_col", ?) from "accounts"

-- ----

select json_extract_path("json_col", ?) as "accountName" from "accounts"

-- ----

select json_extract_path("json_col", ?) as "accountName" from "accounts"

-- ----

select json_extract_path("json_col", ?) as "accountName", json_extract_path("json_col", ?) as "accountLastName" from "accounts"

select JSON_VALUE([json_col], ?) from [accounts]

-- ----

select JSON_VALUE([json_col], ?) as [accountName] from [accounts]

-- ----

select JSON_VALUE([json_col], ?) as [accountName] from [accounts]

-- ----

select JSON_VALUE([json_col], ?) as [accountName], JSON_VALUE([json_col], ?) as [accountLastName] from [accounts]

select json_unquote(json_extract(`json_col`, ?)) from `accounts`

-- ----

select json_unquote(json_extract(`json_col`, ?)) as `accountName` from `accounts`

-- ----

select json_unquote(json_extract(`json_col`, ?)) as `accountName` from `accounts`

-- ----

select json_unquote(json_extract(`json_col`, ?)) as `accountName`, json_unquote(json_extract(`json_col`, ?)) as `accountLastName` from `accounts`

select json_value("json_col", '$.name') from "accounts"

-- ----

select json_value("json_col", '$.name') "accountName" from "accounts"

-- ----

select json_value("json_col", '$.name') "accountName" from "accounts"

-- ----

select json_value("json_col", '$.name') "accountName", json_value("json_col", '$.lastName') "accountLastName" from "accounts"

select jsonb_path_query("json_col", ?) from "accounts"

-- ----

select jsonb_path_query("json_col", ?) as "accountName" from "accounts"

-- ----

select jsonb_path_query("json_col", ?) as "accountName" from "accounts"

-- ----

select jsonb_path_query("json_col", ?) as "accountName", jsonb_path_query("json_col", ?) as "accountLastName" from "accounts"

select json_extract_path_text("json_col", ?) from "accounts"

-- ----

select json_extract_path_text("json_col", ?) as "accountName" from "accounts"

-- ----

select json_extract_path_text("json_col", ?) as "accountName" from "accounts"

-- ----

select json_extract_path_text("json_col", ?) as "accountName", json_extract_path_text("json_col", ?) as "accountLastName" from "accounts"

select json_extract(`json_col`, ?) from `accounts`

-- ----

select json_extract(`json_col`, ?) as `accountName` from `accounts`

-- ----

select json_extract(`json_col`, ?) as `accountName` from `accounts`

-- ----

select json_extract(`json_col`, ?) as `accountName`, json_extract(`json_col`, ?) as `accountLastName` from `accounts`

All json*() functions can be used directly from knex object and can be nested.

knex('cities').jsonExtract([
  [knex.jsonRemove('population', '$.min'), '$', 'withoutMin'],
  [knex.jsonRemove('population', '$.max'), '$', 'withoutMax'],
  [knex.jsonSet('population', '$.current', '1234'), '$', 'currentModified'],
]);

select json_extract_path("population" #- ?, ?) as "withoutMin", json_extract_path("population" #- ?, ?) as "withoutMax", json_extract_path(jsonb_set("population", ?, ?), ?) as "currentModified" from "cities"

select JSON_VALUE(JSON_MODIFY([population],?, NULL), ?) as [withoutMin], JSON_VALUE(JSON_MODIFY([population],?, NULL), ?) as [withoutMax], JSON_VALUE(JSON_MODIFY([population], ?, ?), ?) as [currentModified] from [cities]

select json_unquote(json_extract(json_remove(`population`,?), ?)) as `withoutMin`, json_unquote(json_extract(json_remove(`population`,?), ?)) as `withoutMax`, json_unquote(json_extract(json_set(`population`, ?, ?), ?)) as `currentModified` from `cities`

select json_value(json_transform("population", remove ?), '$') "withoutMin", json_value(json_transform("population", remove ?), '$') "withoutMax", json_value(json_transform("population", set ? = ?), '$') "currentModified" from "cities"

select jsonb_path_query("population" #- ?, ?) as "withoutMin", jsonb_path_query("population" #- ?, ?) as "withoutMax", jsonb_path_query(jsonb_set("population", ?, ?), ?) as "currentModified" from "cities"

Error: Json remove is not supported by Redshift

select json_extract(json_remove(`population`,?), ?) as `withoutMin`, json_extract(json_remove(`population`,?), ?) as `withoutMax`, json_extract(json_set(`population`, ?, ?), ?) as `currentModified` from `cities`

jsonSet RS

.jsonSet(column|builder|raw, path, value, [alias])

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');
knex('accounts').jsonSet(
  'json_col',
  '$.name',
  { name: 'newName' },
  'newNameCol'
);

select jsonb_set("json_col", ?, ?) as "newNameCol" from "accounts"

-- ----

select jsonb_set("json_col", ?, ?) as "newNameCol" from "accounts"

select JSON_MODIFY([json_col], ?, ?) as [newNameCol] from [accounts]

-- ----

select JSON_MODIFY([json_col], ?, ?) as [newNameCol] from [accounts]

select json_set(`json_col`, ?, ?) as `newNameCol` from `accounts`

-- ----

select json_set(`json_col`, ?, ?) as `newNameCol` from `accounts`

select json_transform("json_col", set ? = ?) from "accounts"

-- ----

select json_transform("json_col", set ? = ?) from "accounts"

select jsonb_set("json_col", ?, ?) as "newNameCol" from "accounts"

-- ----

select jsonb_set("json_col", ?, ?) as "newNameCol" from "accounts"

Error: Json set is not supported by Redshift

-- ----

Error: Json set is not supported by Redshift

select json_set(`json_col`, ?, ?) as `newNameCol` from `accounts`

-- ----

select json_set(`json_col`, ?, ?) as `newNameCol` from `accounts`

jsonInsert RS

.jsonInsert(column|builder|raw, path, value, [alias])

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');
knex('accounts').jsonInsert(
  'json_col',
  '$.name',
  { name: 'newName' },
  'newNameCol'
);
knex('accounts').jsonInsert(
  knex.jsonExtract('json_col', '$.otherAccount'),
  '$.name',
  { name: 'newName' },
  'newNameCol'
);

select jsonb_insert("json_col", ?, ?) as "newNameCol" from "accounts"

-- ----

select jsonb_insert("json_col", ?, ?) as "newNameCol" from "accounts"

-- ----

select jsonb_insert(json_extract_path("json_col", ?), ?, ?) as "newNameCol" from "accounts"

select JSON_MODIFY([json_col], ?, ?) as [newNameCol] from [accounts]

-- ----

select JSON_MODIFY([json_col], ?, ?) as [newNameCol] from [accounts]

-- ----

select JSON_MODIFY(JSON_VALUE([json_col], ?), ?, ?) as [newNameCol] from [accounts]

select json_insert(`json_col`, ?, ?) as `newNameCol` from `accounts`

-- ----

select json_insert(`json_col`, ?, ?) as `newNameCol` from `accounts`

-- ----

select json_insert(json_unquote(json_extract(`json_col`, ?)), ?, ?) as `newNameCol` from `accounts`

select json_transform("json_col", insert ? = ?) from "accounts"

-- ----

select json_transform("json_col", insert ? = ?) from "accounts"

-- ----

select json_transform(json_value("json_col", '$.otherAccount'), insert ? = ?) from "accounts"

select jsonb_insert("json_col", ?, ?) as "newNameCol" from "accounts"

-- ----

select jsonb_insert("json_col", ?, ?) as "newNameCol" from "accounts"

-- ----

select jsonb_insert(jsonb_path_query("json_col", ?), ?, ?) as "newNameCol" from "accounts"

Error: Json insert is not supported by Redshift

-- ----

Error: Json insert is not supported by Redshift

-- ----

Error: Json insert is not supported by Redshift

select json_insert(`json_col`, ?, ?) as `newNameCol` from `accounts`

-- ----

select json_insert(`json_col`, ?, ?) as `newNameCol` from `accounts`

-- ----

select json_insert(json_extract(`json_col`, ?), ?, ?) as `newNameCol` from `accounts`

jsonRemove RS

.jsonRemove(column|builder|raw, path, [alias])

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');
knex('accounts').jsonInsert(
  'json_col',
  '$.name',
  { name: 'newName' },
  'newNameCol'
);

select "json_col" #- ? as "colWithRemove" from "accounts"

-- ----

select jsonb_insert("json_col", ?, ?) as "newNameCol" from "accounts"

select JSON_MODIFY([json_col],?, NULL) as [colWithRemove] from [accounts]

-- ----

select JSON_MODIFY([json_col], ?, ?) as [newNameCol] from [accounts]

select json_remove(`json_col`,?) as `colWithRemove` from `accounts`

-- ----

select json_insert(`json_col`, ?, ?) as `newNameCol` from `accounts`

select json_transform("json_col", remove ?) "colWithRemove" from "accounts"

-- ----

select json_transform("json_col", insert ? = ?) from "accounts"

select "json_col" #- ? as "colWithRemove" from "accounts"

-- ----

select jsonb_insert("json_col", ?, ?) as "newNameCol" from "accounts"

Error: Json remove is not supported by Redshift

-- ----

Error: Json insert is not supported by Redshift

select json_remove(`json_col`,?) as `colWithRemove` from `accounts`

-- ----

select json_insert(`json_col`, ?, ?) as `newNameCol` from `accounts`

offset

.offset(value, options={skipBinding: boolean})

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);
knex.select('*').from('users').offset(10).toSQL().sql;

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

select * from "users" offset ?

select * from [users] offset ? rows

select * from `users` limit 18446744073709551615 offset ?

select * from (select row_.*, ROWNUM rownum_ from (select * from "users") row_ where rownum <= ?) where rownum_ > ?

select * from "users" offset ?

select * from "users" offset ?

select * from `users` limit ? offset ?

limit

Important: Knex may throw errors during SQL compilation when the query is unsound. This prevents unexpected data loss or unexpected behavior. "Limit" clauses may throw when:

• A "limit" clause is present on a "delete" or "truncate" statement

Examples of queries that would throw:

knex('accounts').limit(10).del().toSQL();
knex('logs').limit(10).truncate().toSQL();

.limit(value, options={skipBinding: boolean})

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);
knex.select('*').from('users').limit(10).offset(30).toSQL().sql;

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

select * from "users" limit ? offset ?

select * from [users] offset ? rows fetch next ? rows only

select * from `users` limit ? offset ?

select * from (select row_.*, ROWNUM rownum_ from (select * from "users") row_ where rownum <= ?) where rownum_ > ?

select * from "users" limit ? offset ?

select * from "users" limit ? offset ?

select * from `users` limit ? offset ?

union

.union([*queries], [wrap])

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');
  });
knex
  .select('*')
  .from('users')
  .whereNull('last_name')
  .union([knex.select('*').from('users').whereNull('first_name')]);
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')
  );

select * from "users" where "last_name" is null union select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null union select * from "users" where "first_name" is null

-- ----

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

select * from [users] where [last_name] is null union select * from [users] where [first_name] is null

-- ----

select * from [users] where [last_name] is null union select * from [users] where [first_name] is null

-- ----

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

select * from `users` where `last_name` is null union select * from `users` where `first_name` is null

-- ----

select * from `users` where `last_name` is null union select * from `users` where `first_name` is null

-- ----

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

select * from "users" where "last_name" is null union select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null union select * from "users" where "first_name" is null

-- ----

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

select * from "users" where "last_name" is null union select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null union select * from "users" where "first_name" is null

-- ----

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

select * from "users" where "last_name" is null union select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null union select * from "users" where "first_name" is null

-- ----

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

select * from `users` where `last_name` is null union select * from `users` where `first_name` is null

-- ----

select * from `users` where `last_name` is null union select * from `users` where `first_name` is null

-- ----

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

If you want to apply orderBy, groupBy, limit, offset or having to inputs of the union you need to use knex.union as a base statement. If you don't do this, those clauses will get appended to the end of the union.

// example showing how clauses get appended to the end of the query
knex('users')
  .select('id', 'name')
  .groupBy('id')
  .union(knex('invitations').select('id', 'name').orderBy('expires_at'));
knex.union([
  knex('users').select('id', 'name').groupBy('id'),
  knex('invitations').select('id', 'name').orderBy('expires_at'),
]);

select "id", "name" from "users" union select "id", "name" from "invitations" order by "expires_at" asc group by "id"

-- ----

select "id", "name" from "users" group by "id" union select "id", "name" from "invitations" order by "expires_at" asc

select [id], [name] from [users] union select [id], [name] from [invitations] order by [expires_at] asc group by [id]

-- ----

select [id], [name] from [users] group by [id] union select [id], [name] from [invitations] order by [expires_at] asc

select `id`, `name` from `users` union select `id`, `name` from `invitations` order by `expires_at` asc group by `id`

-- ----

select `id`, `name` from `users` group by `id` union select `id`, `name` from `invitations` order by `expires_at` asc

select "id", "name" from "users" union select "id", "name" from "invitations" order by "expires_at" asc group by "id"

-- ----

select "id", "name" from "users" group by "id" union select "id", "name" from "invitations" order by "expires_at" asc

select "id", "name" from "users" union select "id", "name" from "invitations" order by "expires_at" asc group by "id"

-- ----

select "id", "name" from "users" group by "id" union select "id", "name" from "invitations" order by "expires_at" asc

select "id", "name" from "users" union select "id", "name" from "invitations" order by "expires_at" asc group by "id"

-- ----

select "id", "name" from "users" group by "id" union select "id", "name" from "invitations" order by "expires_at" asc

select `id`, `name` from `users` union select `id`, `name` from `invitations` order by `expires_at` asc group by `id`

-- ----

select `id`, `name` from `users` group by `id` union select `id`, `name` from `invitations` order by `expires_at` asc

before and after

unionAll

.unionAll([*queries], [wrap])

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');
  });
knex
  .select('*')
  .from('users')
  .whereNull('last_name')
  .unionAll([knex.select('*').from('users').whereNull('first_name')]);
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')
  );

select * from "users" where "last_name" is null union all select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null union all select * from "users" where "first_name" is null

-- ----

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

select * from [users] where [last_name] is null union all select * from [users] where [first_name] is null

-- ----

select * from [users] where [last_name] is null union all select * from [users] where [first_name] is null

-- ----

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

select * from `users` where `last_name` is null union all select * from `users` where `first_name` is null

-- ----

select * from `users` where `last_name` is null union all select * from `users` where `first_name` is null

-- ----

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

select * from "users" where "last_name" is null union all select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null union all select * from "users" where "first_name" is null

-- ----

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

select * from "users" where "last_name" is null union all select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null union all select * from "users" where "first_name" is null

-- ----

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

select * from "users" where "last_name" is null union all select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null union all select * from "users" where "first_name" is null

-- ----

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

select * from `users` where `last_name` is null union all select * from `users` where `first_name` is null

-- ----

select * from `users` where `last_name` is null union all select * from `users` where `first_name` is null

-- ----

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

intersect

.intersect([*queries], [wrap])

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');
  });
knex
  .select('*')
  .from('users')
  .whereNull('last_name')
  .intersect([knex.select('*').from('users').whereNull('first_name')]);
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')
  );

select * from "users" where "last_name" is null intersect select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null intersect select * from "users" where "first_name" is null

-- ----

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

select * from [users] where [last_name] is null intersect select * from [users] where [first_name] is null

-- ----

select * from [users] where [last_name] is null intersect select * from [users] where [first_name] is null

-- ----

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

select * from `users` where `last_name` is null intersect select * from `users` where `first_name` is null

-- ----

select * from `users` where `last_name` is null intersect select * from `users` where `first_name` is null

-- ----

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

select * from "users" where "last_name" is null intersect select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null intersect select * from "users" where "first_name" is null

-- ----

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

select * from "users" where "last_name" is null intersect select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null intersect select * from "users" where "first_name" is null

-- ----

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

select * from "users" where "last_name" is null intersect select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null intersect select * from "users" where "first_name" is null

-- ----

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

select * from `users` where `last_name` is null intersect select * from `users` where `first_name` is null

-- ----

select * from `users` where `last_name` is null intersect select * from `users` where `first_name` is null

-- ----

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

except

.except([*queries], [wrap])

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

knex
  .select('*')
  .from('users')
  .whereNull('last_name')
  .except(function () {
    this.select('*').from('users').whereNull('first_name');
  });
knex
  .select('*')
  .from('users')
  .whereNull('last_name')
  .except([knex.select('*').from('users').whereNull('first_name')]);
knex
  .select('*')
  .from('users')
  .whereNull('last_name')
  .except(
    knex.raw('select * from users where first_name is null'),
    knex.raw('select * from users where email is null')
  );

select * from "users" where "last_name" is null except select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null except select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null except select * from users where first_name is null except select * from users where email is null

select * from [users] where [last_name] is null except select * from [users] where [first_name] is null

-- ----

select * from [users] where [last_name] is null except select * from [users] where [first_name] is null

-- ----

select * from [users] where [last_name] is null except select * from users where first_name is null except select * from users where email is null

select * from `users` where `last_name` is null except select * from `users` where `first_name` is null

-- ----

select * from `users` where `last_name` is null except select * from `users` where `first_name` is null

-- ----

select * from `users` where `last_name` is null except select * from users where first_name is null except select * from users where email is null

select * from "users" where "last_name" is null except select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null except select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null except select * from users where first_name is null except select * from users where email is null

select * from "users" where "last_name" is null except select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null except select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null except select * from users where first_name is null except select * from users where email is null

select * from "users" where "last_name" is null except select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null except select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null except select * from users where first_name is null except select * from users where email is null

select * from `users` where `last_name` is null except select * from `users` where `first_name` is null

-- ----

select * from `users` where `last_name` is null except select * from `users` where `first_name` is null

-- ----

select * from `users` where `last_name` is null except select * from users where first_name is null except select * from users where email is null

insert

.insert(data, [returning], [options])

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

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

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

insert into "books" ("title") values (?)

-- ----

insert into "coords" ("x", "y") values (?, DEFAULT), (DEFAULT, ?), (?, ?)

-- ----

insert into "books" ("title") values (?), (?) returning "id"

insert into [books] ([title]) values (?)

-- ----

insert into [coords] ([x], [y]) values (?, DEFAULT), (DEFAULT, ?), (?, ?)

-- ----

insert into [books] ([title]) output inserted.[id] values (?), (?)

insert into `books` (`title`) values (?)

-- ----

insert into `coords` (`x`, `y`) values (?, DEFAULT), (DEFAULT, ?), (?, ?)

-- ----

insert into `books` (`title`) values (?), (?)

insert into "books" ("title") values (?)

-- ----

begin execute immediate 'insert into "coords" ("x", "y") values (:1, DEFAULT)' using ?; execute immediate 'insert into "coords" ("x", "y") values (DEFAULT, :1)' using ?; execute immediate 'insert into "coords" ("x", "y") values (:1, :2)' using ?, ?;end;

-- ----

begin execute immediate 'insert into "books" ("title") values (:1) returning "id" into :2' using ?, out ?; execute immediate 'insert into "books" ("title") values (:1) returning "id" into :2' using ?, out ?;end;

insert into "books" ("title") values (?)

-- ----

insert into "coords" ("x", "y") values (?, DEFAULT), (DEFAULT, ?), (?, ?)

-- ----

insert into "books" ("title") values (?), (?) returning "id"

insert into "books" ("title") values (?)

-- ----

insert into "coords" ("x", "y") values (?, DEFAULT), (DEFAULT, ?), (?, ?)

-- ----

insert into "books" ("title") values (?), (?)

insert into `books` (`title`) values (?)

-- ----

insert into `coords` (`x`, `y`) select ? as `x`, ? as `y` union all select ? as `x`, ? as `y` union all select ? as `x`, ? as `y`

-- ----

insert into `books` (`title`) select ? as `title` union all select ? as `title` returning `id`

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

insert into "books" ("title") values (?) returning "id"

select top(0) [t].[id] into #out from [books] as t left join [books] on 0=1;insert into [books] ([title]) output inserted.[id] into #out values (?); select [id] from #out; drop table #out;

insert into `books` (`title`) values (?)

insert into "books" ("title") values (?) returning "id" into ?

insert into "books" ("title") values (?) returning "id"

insert into "books" ("title") values (?)

insert into `books` (`title`) values (?) returning `id`

If one prefers that undefined keys are replaced with NULL instead of DEFAULT one may give useNullAsDefault configuration parameter in knex config.

const knex = require('knex')({
  client: 'mysql',
  connection: {
    host: '127.0.0.1',
    port: 3306,
    user: 'your_database_user',
    password: 'your_database_password',
    database: 'myapp_test',
  },
  useNullAsDefault: true,
});

knex('coords').insert([{ x: 20 }, { y: 30 }, { x: 10, y: 20 }]);

insert into `coords` (`x`, `y`) values (20, NULL), (NULL, 30), (10, 20)"

onConflict MSORCRRS

insert(..).onConflict(column)insert(..).onConflict([column1, column2, ...])insert(..).onConflict(knex.raw(...))

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

INFO

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: '[email protected]',
    name: 'John Doe',
    active: true,
  })
  // ignore only on email conflict and active is true.
  .onConflict(knex.raw('(email) where active'))
  .ignore();

insert into "tableName" ("active", "email", "name") values (?, ?, ?) on conflict (email) where active do nothing

Error: .onConflict() is not supported for mssql.

insert ignore into `tableName` (`active`, `email`, `name`) values (?, ?, ?)

Error: .onConflict() is not supported for oracledb.

insert into "tableName" ("active", "email", "name") values (?, ?, ?) on conflict (email) where active do nothing

insert into "tableName" ("active", "email", "name") values (?, ?, ?)

insert into `tableName` (`active`, `email`, `name`) values (?, ?, ?) on conflict (email) where active do nothing

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

ignore

insert(..).onConflict(..).ignore()

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: '[email protected]',
    name: 'John Doe',
  })
  .onConflict('email')
  .ignore();

insert into "tableName" ("email", "name") values (?, ?) on conflict ("email") do nothing

Error: .onConflict() is not supported for mssql.

insert ignore into `tableName` (`email`, `name`) values (?, ?)

Error: .onConflict() is not supported for oracledb.

insert into "tableName" ("email", "name") values (?, ?) on conflict ("email") do nothing

insert into "tableName" ("email", "name") values (?, ?)

insert into `tableName` (`email`, `name`) values (?, ?) on conflict (`email`) do nothing

merge

insert(..).onConflict(..).merge()insert(..).onConflict(..).merge(updates)

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: '[email protected]',
    name: 'John Doe',
  })
  .onConflict('email')
  .merge();

insert into "tableName" ("email", "name") values (?, ?) on conflict ("email") do update set "email" = excluded."email", "name" = excluded."name"

Error: .onConflict() is not supported for mssql.

insert into `tableName` (`email`, `name`) values (?, ?) on duplicate key update `email` = values(`email`), `name` = values(`name`)

Error: .onConflict() is not supported for oracledb.

insert into "tableName" ("email", "name") values (?, ?) on conflict ("email") do update set "email" = excluded."email", "name" = excluded."name"

insert into "tableName" ("email", "name") values (?, ?)

insert into `tableName` (`email`, `name`) values (?, ?) on conflict (`email`) do update set `email` = excluded.`email`, `name` = excluded.`name`

This also works with batch inserts:

knex('tableName')
  .insert([
    { email: '[email protected]', name: 'John Doe' },
    { email: '[email protected]', name: 'Jane Doe' },
    { email: '[email protected]', name: 'Alex Doe' },
  ])
  .onConflict('email')
  .merge();

insert into "tableName" ("email", "name") values (?, ?), (?, ?), (?, ?) on conflict ("email") do update set "email" = excluded."email", "name" = excluded."name"

Error: .onConflict() is not supported for mssql.

insert into `tableName` (`email`, `name`) values (?, ?), (?, ?), (?, ?) on duplicate key update `email` = values(`email`), `name` = values(`name`)

Error: .onConflict() is not supported for oracledb.

insert into "tableName" ("email", "name") values (?, ?), (?, ?), (?, ?) on conflict ("email") do update set "email" = excluded."email", "name" = excluded."name"

insert into "tableName" ("email", "name") values (?, ?), (?, ?), (?, ?)

insert into `tableName` (`email`, `name`) select ? as `email`, ? as `name` union all select ? as `email`, ? as `name` union all select ? as `email`, ? as `name` where true on conflict (`email`) do update set `email` = excluded.`email`, `name` = excluded.`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: '[email protected]',
    name: 'John Doe',
    created_at: timestamp,
    updated_at: timestamp,
  })
  .onConflict('email')
  .merge(['email', 'name', 'updated_at']);

insert into "tableName" ("created_at", "email", "name", "updated_at") values (?, ?, ?, ?) on conflict ("email") do update set "email" = excluded."email", "name" = excluded."name", "updated_at" = excluded."updated_at"

Error: .onConflict() is not supported for mssql.

insert into `tableName` (`created_at`, `email`, `name`, `updated_at`) values (?, ?, ?, ?) on duplicate key update `email` = values(`email`), `name` = values(`name`), `updated_at` = values(`updated_at`)

Error: .onConflict() is not supported for oracledb.

insert into "tableName" ("created_at", "email", "name", "updated_at") values (?, ?, ?, ?) on conflict ("email") do update set "email" = excluded."email", "name" = excluded."name", "updated_at" = excluded."updated_at"

insert into "tableName" ("created_at", "email", "name", "updated_at") values (?, ?, ?, ?)

insert into `tableName` (`created_at`, `email`, `name`, `updated_at`) values (?, ?, ?, ?) on conflict (`email`) do update set `email` = excluded.`email`, `name` = excluded.`name`, `updated_at` = excluded.`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: '[email protected]',
    name: 'John Doe',
    created_at: timestamp,
    updated_at: timestamp,
  })
  .onConflict('email')
  .merge({
    name: 'John Doe The Second',
  });

insert into "tableName" ("created_at", "email", "name", "updated_at") values (?, ?, ?, ?) on conflict ("email") do update set "name" = ?

Error: .onConflict() is not supported for mssql.

insert into `tableName` (`created_at`, `email`, `name`, `updated_at`) values (?, ?, ?, ?) on duplicate key update `name` = ?

Error: .onConflict() is not supported for oracledb.

insert into "tableName" ("created_at", "email", "name", "updated_at") values (?, ?, ?, ?) on conflict ("email") do update set "name" = ?

insert into "tableName" ("created_at", "email", "name", "updated_at") values (?, ?, ?, ?)

insert into `tableName` (`created_at`, `email`, `name`, `updated_at`) values (?, ?, ?, ?) on conflict (`email`) do update set `name` = ?

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: '[email protected]',
    name: 'John Doe',
    created_at: timestamp,
    updated_at: timestamp,
  })
  .onConflict('email')
  .merge({
    name: 'John Doe',
    updated_at: timestamp,
  })
  .where('tableName.updated_at', '<', timestamp);

insert into "tableName" ("created_at", "email", "name", "updated_at") values (?, ?, ?, ?) on conflict ("email") do update set "name" = ?,"updated_at" = ? where "tableName"."updated_at" < ?

Error: .onConflict() is not supported for mssql.

Error: .onConflict().merge().where() is not supported for mysql

Error: .onConflict() is not supported for oracledb.

insert into "tableName" ("created_at", "email", "name", "updated_at") values (?, ?, ?, ?) on conflict ("email") do update set "name" = ?,"updated_at" = ? where "tableName"."updated_at" < ?

insert into "tableName" ("created_at", "email", "name", "updated_at") values (?, ?, ?, ?)

insert into `tableName` (`created_at`, `email`, `name`, `updated_at`) values (?, ?, ?, ?) on conflict (`email`) do update set `name` = ?,`updated_at` = ? where `tableName`.`updated_at` < ?

upsert MY+CR only

.upsert(data, [returning], [options])

Implemented for the CockroachDB and MySQL. 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. Please be cautious because the returning option is not supported by MySQL.

// insert new row with unique index on title column
knex('books').upsert({ title: 'Great Gatsby' });

// update row by unique title 'Great Gatsby'
// and insert row with title 'Fahrenheit 451'
knex('books').upsert(
  [{ title: 'Great Gatsby' }, { title: 'Fahrenheit 451' }],
  ['id']
);

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

upsert into "books" ("title") values (?)

-- ----

upsert into "books" ("title") values (?), (?) returning "id"

-- ----

upsert into "coords" ("x", "y") values (?, DEFAULT), (DEFAULT, ?), (?, ?)

Error: Upsert is not yet supported for dialect mssql

-- ----

Error: Upsert is not yet supported for dialect mssql

-- ----

Error: Upsert is not yet supported for dialect mssql

replace into `books` (`title`) values (?)

-- ----

replace into `books` (`title`) values (?), (?)

-- ----

replace into `coords` (`x`, `y`) values (?, DEFAULT), (DEFAULT, ?), (?, ?)

Error: Upsert is not yet supported for dialect oracle

-- ----

Error: Upsert is not yet supported for dialect oracle

-- ----

Error: Upsert is not yet supported for dialect oracle

Error: Upsert is not yet supported for dialect postgresql

-- ----

Error: Upsert is not yet supported for dialect postgresql

-- ----

Error: Upsert is not yet supported for dialect postgresql

Error: Upsert is not yet supported for dialect redshift

-- ----

Error: Upsert is not yet supported for dialect redshift

-- ----

Error: Upsert is not yet supported for dialect redshift

Error: Upsert is not yet supported for dialect sqlite3

-- ----

Error: Upsert is not yet supported for dialect sqlite3

-- ----

Error: Upsert is not yet supported for dialect sqlite3

update

.update(data, [returning], [options]).update(key, value, [returning], [options])

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

// Returns [1] in "mysql", "sqlite", "oracle";
// [] in "postgresql"
// unless the 'returning' parameter is set.
knex('books').update('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']
);

update "books" set "status" = ? where "published_date" < ?

-- ----

update "books" set "title" = ?

-- ----

update "books" set "title" = ? where "id" = ? returning "id", "title"

update [books] set [status] = ? where [published_date] < ?;select @@rowcount

-- ----

update [books] set [title] = ?;select @@rowcount

-- ----

update [books] set [title] = ? output inserted.[id], inserted.[title] where [id] = ?

update `books` set `status` = ? where `published_date` < ?

-- ----

update `books` set `title` = ?

-- ----

update `books` set `title` = ? where `id` = ?

update "books" set "status" = ? where "published_date" < ?

-- ----

update "books" set "title" = ?

-- ----

update "books" set "title" = ? where "id" = ? returning "id","title" into ?, ?

update "books" set "status" = ? where "published_date" < ?

-- ----

update "books" set "title" = ?

-- ----

update "books" set "title" = ? where "id" = ? returning "id", "title"

update "books" set "status" = ? where "published_date" < ?

-- ----

update "books" set "title" = ?

-- ----

update "books" set "title" = ? where "id" = ?

update `books` set `status` = ? where `published_date` < ?

-- ----

update `books` set `title` = ?

-- ----

update `books` set `title` = ? where `id` = ? returning `id`, `title`

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

update "books" set "title" = ? returning "id", "title"

select top(0) [t].[id],[t].[title] into #out from [books] as t left join [books] on 0=1;update [books] set [title] = ? output inserted.[id], inserted.[title] into #out; select [id],[title] from #out; drop table #out;

update `books` set `title` = ?

update "books" set "title" = ? returning "id","title" into ?, ?

update "books" set "title" = ? returning "id", "title"

update "books" set "title" = ?

update `books` set `title` = ? returning `id`, `title`

updateFrom PG only

.updateFrom(tableName)

Can be used to define in PostgreSQL an update statement with explicit 'from' syntax which can be referenced in 'where' conditions.

knex('accounts')
  .update({ enabled: false })
  .updateFrom('clients')
  .where('accounts.id', '=', 'clients.id')
  .where('clients.active', '=', false);

Error: TypeError: knex(...).update(...).updateFrom is not a function

Error: TypeError: knex(...).update(...).updateFrom is not a function

Error: TypeError: knex(...).update(...).updateFrom is not a function

Error: TypeError: knex(...).update(...).updateFrom is not a function

update "accounts" set "enabled" = ? from "clients" where "accounts"."id" = ? and "clients"."active" = ?

update "accounts" set "enabled" = ? where "accounts"."id" = ? and "clients"."active" = ?

Error: TypeError: knex(...).update(...).updateFrom is not a function

del / delete

.del([returning], [options])

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

delete from "accounts" where "activated" = ?

delete from [accounts] where [activated] = ?;select @@rowcount

delete from `accounts` where `activated` = ?

delete from "accounts" where "activated" = ?

delete from "accounts" where "activated" = ?

delete from "accounts" where "activated" = ?

delete from `accounts` where `activated` = ?

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

delete from "books" where "title" = ? returning "id", "title"

select top(0) [t].[id],[t].[title] into #out from [books] as t left join [books] on 0=1;delete from [books] output deleted.[id], deleted.[title] into #out where [title] = ?; select [id],[title] from #out; drop table #out;

delete from `books` where `title` = ?

delete from "books" where "title" = ?

delete from "books" where "title" = ? returning "id", "title"

delete from "books" where "title" = ?

delete from `books` where `title` = ?

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

delete from "accounts" using "accounts" where "activated" = ? and "accounts"."id" = "users"."account_id"

delete [accounts] from [accounts] inner join [accounts] on [accounts].[id] = [users].[account_id] where [activated] = ?;select @@rowcount

delete `accounts` from `accounts` inner join `accounts` on `accounts`.`id` = `users`.`account_id` where `activated` = ?

delete "accounts" from "accounts" inner join "accounts" on "accounts"."id" = "users"."account_id" where "activated" = ?

delete from "accounts" using "accounts" where "activated" = ? and "accounts"."id" = "users"."account_id"

delete "accounts" from "accounts" inner join "accounts" on "accounts"."id" = "users"."account_id" where "activated" = ?

delete `accounts` from `accounts` inner join `accounts` on `accounts`.`id` = `users`.`account_id` where `activated` = ?

using PG only

.using(tableName|tableNames)

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.

Error: 'using' function is only available in PostgreSQL dialect with Delete statements.

Error: 'using' function is only available in PostgreSQL dialect with Delete statements.

Error: 'using' function is only available in PostgreSQL dialect with Delete statements.

delete from "accounts" using "accounts" where "activated" = ? and accounts.id = users.account_id

delete from "accounts" where "activated" = ? and accounts.id = users.account_id

Error: 'using' function is only available in PostgreSQL dialect with Delete statements.

returning MYCRRS

.returning(column, [options]).returning([column1, column2, ...], [options])

Utilized by PostgreSQL, MSSQL, SQLite, 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' });

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

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

insert into "books" ("title") values (?) returning "id"

-- ----

insert into "books" ("title") values (?), (?) returning "id"

-- ----

insert into "books" ("title") values (?) returning "id", "title"

insert into [books] ([title]) output inserted.[id] values (?)

-- ----

insert into [books] ([title]) output inserted.[id] values (?), (?)

-- ----

insert into [books] ([title]) output inserted.[id], inserted.[title] values (?)

insert into `books` (`title`) values (?)

-- ----

insert into `books` (`title`) values (?), (?)

-- ----

insert into `books` (`title`) values (?)

insert into "books" ("title") values (?) returning "id" into ?

-- ----

begin execute immediate 'insert into "books" ("title") values (:1) returning "id" into :2' using ?, out ?; execute immediate 'insert into "books" ("title") values (:1) returning "id" into :2' using ?, out ?;end;

-- ----

insert into "books" ("title") values (?) returning "id","title" into ?,?

insert into "books" ("title") values (?) returning "id"

-- ----

insert into "books" ("title") values (?), (?) returning "id"

-- ----

insert into "books" ("title") values (?) returning "id", "title"

insert into "books" ("title") values (?)

-- ----

insert into "books" ("title") values (?), (?)

-- ----

insert into "books" ("title") values (?)

insert into `books` (`title`) values (?) returning `id`

-- ----

insert into `books` (`title`) select ? as `title` union all select ? as `title` returning `id`

-- ----

insert into `books` (`title`) values (?) returning `id`, `title`

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

insert into "books" ("title") values (?) returning "id", "title"

select top(0) [t].[id],[t].[title] into #out from [books] as t left join [books] on 0=1;insert into [books] ([title]) output inserted.[id], inserted.[title] into #out values (?); select [id],[title] from #out; drop table #out;

insert into `books` (`title`) values (?)

insert into "books" ("title") values (?) returning "id","title" into ?,?

insert into "books" ("title") values (?) returning "id", "title"

insert into "books" ("title") values (?)

insert into `books` (`title`) values (?) returning `id`, `title`

transacting

.transacting(transactionObj)

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

forUpdate PG+MY only

.transacting(t).forUpdate()

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

select * from "tableName" for update

select * from [tableName] with (UPDLOCK)

select * from `tableName` for update

select * from "tableName" for update

select * from "tableName" for update

select * from "tableName"

select * from `tableName`

forShare PG+MY only

.transacting(t).forShare()

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

select * from "tableName" for share

select * from [tableName] with (HOLDLOCK)

select * from `tableName` lock in share mode

select * from "tableName"

select * from "tableName" for share

select * from "tableName"

select * from `tableName`

forNoKeyUpdate PG only

.transacting(t).forNoKeyUpdate()

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

knex('tableName').transacting(trx).forNoKeyUpdate().select('*');

select * from "tableName" for no key update

Error: TypeError: this[this.single.lock] is not a function

Error: TypeError: this[this.single.lock] is not a function

Error: TypeError: this[this.single.lock] is not a function

select * from "tableName" for no key update

select * from "tableName"

select * from `tableName`

forKeyShare PG only

.transacting(t).forKeyShare()

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

knex('tableName').transacting(trx).forKeyShare().select('*');

select * from "tableName" for key share

Error: TypeError: this[this.single.lock] is not a function

Error: TypeError: this[this.single.lock] is not a function

Error: TypeError: this[this.single.lock] is not a function

select * from "tableName" for key share

select * from "tableName"

select * from `tableName`

skipLocked PG+MY only

.skipLocked()

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

select * from "tableName" for update skip locked

select * from [tableName] with (UPDLOCK)

select * from `tableName` for update skip locked

select * from "tableName" for update

select * from "tableName" for update skip locked

select * from "tableName" skip locked

Error: .skipLocked() is currently only supported on MySQL 8.0+ and PostgreSQL 9.5+

noWait PG+MY only

.noWait()

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

select * from "tableName" for update nowait

select * from [tableName] with (UPDLOCK)

select * from `tableName` for update nowait

select * from "tableName" for update

select * from "tableName" for update nowait

select * from "tableName" nowait

Error: .noWait() is currently only supported on MySQL 8.0+, MariaDB 10.3.0+ and PostgreSQL 9.5+

count

.count(column|columns|raw, [options])

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');
knex('users').count('active', { as: 'a' });
knex('users').count('active as a');
knex('users').count({ a: 'active' });
knex('users').count({ a: 'active', v: 'valid' });
knex('users').count('id', 'active');
knex('users').count({ count: ['id', 'active'] });
knex('users').count(knex.raw('??', ['active']));

select count("active") from "users"

-- ----

select count("active") as "a" from "users"

-- ----

select count("active") as "a" from "users"

-- ----

select count("active") as "a" from "users"

-- ----

select count("active") as "a", count("valid") as "v" from "users"

-- ----

select count("id") from "users"

-- ----

select count("id", "active") as "count" from "users"

-- ----

select count("active") from "users"

select count([active]) from [users]

-- ----

select count([active]) as [a] from [users]

-- ----

select count([active]) as [a] from [users]

-- ----

select count([active]) as [a] from [users]

-- ----

select count([active]) as [a], count([valid]) as [v] from [users]

-- ----

select count([id]) from [users]

-- ----

select count([id], [active]) as [count] from [users]

-- ----

select count([active]) from [users]

select count(`active`) from `users`

-- ----

select count(`active`) as `a` from `users`

-- ----

select count(`active`) as `a` from `users`

-- ----

select count(`active`) as `a` from `users`

-- ----

select count(`active`) as `a`, count(`valid`) as `v` from `users`

-- ----

select count(`id`) from `users`

-- ----

select count(`id`, `active`) as `count` from `users`

-- ----

select count(`active`) from `users`

select count("active") from "users"

-- ----

select count("active") "a" from "users"

-- ----

select count("active") "a" from "users"

-- ----

select count("active") "a" from "users"

-- ----

select count("active") "a", count("valid") "v" from "users"

-- ----

select count("id") from "users"

-- ----

select count("id", "active") "count" from "users"

-- ----

select count("active") from "users"

select count("active") from "users"

-- ----

select count("active") as "a" from "users"

-- ----

select count("active") as "a" from "users"

-- ----

select count("active") as "a" from "users"

-- ----

select count("active") as "a", count("valid") as "v" from "users"

-- ----

select count("id") from "users"

-- ----

select count("id", "active") as "count" from "users"

-- ----

select count("active") from "users"

select count("active") from "users"

-- ----

select count("active") as "a" from "users"

-- ----

select count("active") as "a" from "users"

-- ----

select count("active") as "a" from "users"

-- ----

select count("active") as "a", count("valid") as "v" from "users"

-- ----

select count("id") from "users"

-- ----

select count("id", "active") as "count" from "users"

-- ----

select count("active") from "users"

select count(`active`) from `users`

-- ----

select count(`active`) as `a` from `users`

-- ----

select count(`active`) as `a` from `users`

-- ----

select count(`active`) as `a` from `users`

-- ----

select count(`active`) as `a`, count(`valid`) as `v` from `users`

-- ----

select count(`id`) from `users`

-- ----

select count(`id`, `active`) as `count` from `users`

-- ----

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

select count("age") from "users"

-- ----

select count(*) as "count" from "users"

select count([age]) from [users]

-- ----

select count(*) as [count] from [users]

select count(`age`) from `users`

-- ----

select count(*) as `count` from `users`

select count("age") from "users"

-- ----

select count(*) "count" from "users"

select count("age") from "users"

-- ----

select count(*) as "count" from "users"

select count("age") from "users"

-- ----

select count(*) as "count" from "users"

select count(`age`) from `users`

-- ----

select count(*) as `count` from `users`

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

Use countDistinct to add a distinct expression inside the aggregate function.

knex('users').countDistinct('active');

select count(distinct "active") from "users"

select count(distinct [active]) from [users]

select count(distinct `active`) from `users`

select count(distinct "active") from "users"

select count(distinct "active") from "users"

select count(distinct "active") from "users"

select count(distinct `active`) from `users`

min

.min(column|columns|raw, [options])

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');
knex('users').min('age', { as: 'a' });
knex('users').min('age as a');
knex('users').min({ a: 'age' });
knex('users').min({ a: 'age', b: 'experience' });
knex('users').min('age', 'logins');
knex('users').min({ min: ['age', 'logins'] });
knex('users').min(knex.raw('??', ['age']));

select min("age") from "users"

-- ----

select min("age") as "a" from "users"

-- ----

select min("age") as "a" from "users"

-- ----

select min("age") as "a" from "users"

-- ----

select min("age") as "a", min("experience") as "b" from "users"

-- ----

select min("age") from "users"

-- ----

select min("age", "logins") as "min" from "users"

-- ----

select min("age") from "users"

select min([age]) from [users]

-- ----

select min([age]) as [a] from [users]

-- ----

select min([age]) as [a] from [users]

-- ----

select min([age]) as [a] from [users]

-- ----

select min([age]) as [a], min([experience]) as [b] from [users]

-- ----

select min([age]) from [users]

-- ----

select min([age], [logins]) as [min] from [users]

-- ----

select min([age]) from [users]

select min(`age`) from `users`

-- ----

select min(`age`) as `a` from `users`

-- ----

select min(`age`) as `a` from `users`

-- ----

select min(`age`) as `a` from `users`

-- ----

select min(`age`) as `a`, min(`experience`) as `b` from `users`

-- ----

select min(`age`) from `users`

-- ----

select min(`age`, `logins`) as `min` from `users`

-- ----

select min(`age`) from `users`

select min("age") from "users"

-- ----

select min("age") "a" from "users"

-- ----

select min("age") "a" from "users"

-- ----

select min("age") "a" from "users"

-- ----

select min("age") "a", min("experience") "b" from "users"

-- ----

select min("age") from "users"

-- ----

select min("age", "logins") "min" from "users"

-- ----

select min("age") from "users"

select min("age") from "users"

-- ----

select min("age") as "a" from "users"

-- ----

select min("age") as "a" from "users"

-- ----

select min("age") as "a" from "users"

-- ----

select min("age") as "a", min("experience") as "b" from "users"

-- ----

select min("age") from "users"

-- ----

select min("age", "logins") as "min" from "users"

-- ----

select min("age") from "users"

select min("age") from "users"

-- ----

select min("age") as "a" from "users"

-- ----

select min("age") as "a" from "users"

-- ----

select min("age") as "a" from "users"

-- ----

select min("age") as "a", min("experience") as "b" from "users"

-- ----

select min("age") from "users"

-- ----

select min("age", "logins") as "min" from "users"

-- ----

select min("age") from "users"

select min(`age`) from `users`

-- ----

select min(`age`) as `a` from `users`

-- ----

select min(`age`) as `a` from `users`

-- ----

select min(`age`) as `a` from `users`

-- ----

select min(`age`) as `a`, min(`experience`) as `b` from `users`

-- ----

select min(`age`) from `users`

-- ----

select min(`age`, `logins`) as `min` from `users`

-- ----

select min(`age`) from `users`

max

.max(column|columns|raw, [options])

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');
knex('users').max('age', { as: 'a' });
knex('users').max('age as a');
knex('users').max({ a: 'age' });
knex('users').max('age', 'logins');
knex('users').max({ max: ['age', 'logins'] });
knex('users').max({ max: 'age', exp: 'experience' });
knex('users').max(knex.raw('??', ['age']));

select max("age") from "users"

-- ----

select max("age") as "a" from "users"

-- ----

select max("age") as "a" from "users"

-- ----

select max("age") as "a" from "users"

-- ----

select max("age") from "users"

-- ----

select max("age", "logins") as "max" from "users"

-- ----

select max("age") as "max", max("experience") as "exp" from "users"

-- ----

select max("age") from "users"

select max([age]) from [users]

-- ----

select max([age]) as [a] from [users]

-- ----

select max([age]) as [a] from [users]

-- ----

select max([age]) as [a] from [users]

-- ----

select max([age]) from [users]

-- ----

select max([age], [logins]) as [max] from [users]

-- ----

select max([age]) as [max], max([experience]) as [exp] from [users]

-- ----

select max([age]) from [users]

select max(`age`) from `users`

-- ----

select max(`age`) as `a` from `users`

-- ----

select max(`age`) as `a` from `users`

-- ----

select max(`age`) as `a` from `users`

-- ----

select max(`age`) from `users`

-- ----

select max(`age`, `logins`) as `max` from `users`

-- ----

select max(`age`) as `max`, max(`experience`) as `exp` from `users`

-- ----

select max(`age`) from `users`

select max("age") from "users"

-- ----

select max("age") "a" from "users"

-- ----

select max("age") "a" from "users"

-- ----

select max("age") "a" from "users"

-- ----

select max("age") from "users"

-- ----

select max("age", "logins") "max" from "users"

-- ----

select max("age") "max", max("experience") "exp" from "users"

-- ----

select max("age") from "users"

select max("age") from "users"

-- ----

select max("age") as "a" from "users"

-- ----

select max("age") as "a" from "users"

-- ----

select max("age") as "a" from "users"

-- ----

select max("age") from "users"

-- ----

select max("age", "logins") as "max" from "users"

-- ----

select max("age") as "max", max("experience") as "exp" from "users"

-- ----

select max("age") from "users"

select max("age") from "users"

-- ----

select max("age") as "a" from "users"

-- ----

select max("age") as "a" from "users"

-- ----

select max("age") as "a" from "users"

-- ----

select max("age") from "users"

-- ----

select max("age", "logins") as "max" from "users"

-- ----

select max("age") as "max", max("experience") as "exp" from "users"

-- ----

select max("age") from "users"

select max(`age`) from `users`

-- ----

select max(`age`) as `a` from `users`

-- ----

select max(`age`) as `a` from `users`

-- ----

select max(`age`) as `a` from `users`

-- ----

select max(`age`) from `users`

-- ----

select max(`age`, `logins`) as `max` from `users`

-- ----

select max(`age`) as `max`, max(`experience`) as `exp` from `users`

-- ----

select max(`age`) from `users`

sum

.sum(column|columns|raw)

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');
knex('users').sum('products as p');
knex('users').sum({ p: 'products' });
knex('users').sum('products', 'orders');
knex('users').sum({ sum: ['products', 'orders'] });
knex('users').sum(knex.raw('??', ['products']));

select sum("products") from "users"

-- ----

select sum("products") as "p" from "users"

-- ----

select sum("products") as "p" from "users"

-- ----

select sum("products") from "users"

-- ----

select sum("products", "orders") as "sum" from "users"

-- ----

select sum("products") from "users"

select sum([products]) from [users]

-- ----

select sum([products]) as [p] from [users]

-- ----

select sum([products]) as [p] from [users]

-- ----

select sum([products]) from [users]

-- ----

select sum([products], [orders]) as [sum] from [users]

-- ----

select sum([products]) from [users]

select sum(`products`) from `users`

-- ----

select sum(`products`) as `p` from `users`

-- ----

select sum(`products`) as `p` from `users`

-- ----

select sum(`products`) from `users`

-- ----

select sum(`products`, `orders`) as `sum` from `users`

-- ----

select sum(`products`) from `users`

select sum("products") from "users"

-- ----

select sum("products") "p" from "users"

-- ----

select sum("products") "p" from "users"

-- ----

select sum("products") from "users"

-- ----

select sum("products", "orders") "sum" from "users"

-- ----

select sum("products") from "users"

select sum("products") from "users"

-- ----

select sum("products") as "p" from "users"

-- ----

select sum("products") as "p" from "users"

-- ----

select sum("products") from "users"

-- ----

select sum("products", "orders") as "sum" from "users"

-- ----

select sum("products") from "users"

select sum("products") from "users"

-- ----

select sum("products") as "p" from "users"

-- ----

select sum("products") as "p" from "users"

-- ----

select sum("products") from "users"

-- ----

select sum("products", "orders") as "sum" from "users"

-- ----

select sum("products") from "users"

select sum(`products`) from `users`

-- ----

select sum(`products`) as `p` from `users`

-- ----

select sum(`products`) as `p` from `users`

-- ----

select sum(`products`) from `users`

-- ----

select sum(`products`, `orders`) as `sum` from `users`

-- ----

select sum(`products`) from `users`

Use sumDistinct to add a distinct expression inside the aggregate function.

knex('users').sumDistinct('products');

select sum(distinct "products") from "users"

select sum(distinct [products]) from [users]

select sum(distinct `products`) from `users`

select sum(distinct "products") from "users"

select sum(distinct "products") from "users"

select sum(distinct "products") from "users"

select sum(distinct `products`) from `users`

avg

.avg(column|columns|raw)

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');
knex('users').avg('age as a');
knex('users').avg({ a: 'age' });
knex('users').avg('age', 'logins');
knex('users').avg({ avg: ['age', 'logins'] });
knex('users').avg(knex.raw('??', ['age']));

select avg("age") from "users"

-- ----

select avg("age") as "a" from "users"

-- ----

select avg("age") as "a" from "users"

-- ----

select avg("age") from "users"

-- ----

select avg("age", "logins") as "avg" from "users"

-- ----

select avg("age") from "users"

select avg([age]) from [users]

-- ----

select avg([age]) as [a] from [users]

-- ----

select avg([age]) as [a] from [users]

-- ----

select avg([age]) from [users]

-- ----

select avg([age], [logins]) as [avg] from [users]

-- ----

select avg([age]) from [users]

select avg(`age`) from `users`

-- ----

select avg(`age`) as `a` from `users`

-- ----

select avg(`age`) as `a` from `users`

-- ----

select avg(`age`) from `users`

-- ----

select avg(`age`, `logins`) as `avg` from `users`

-- ----

select avg(`age`) from `users`

select avg("age") from "users"

-- ----

select avg("age") "a" from "users"

-- ----

select avg("age") "a" from "users"

-- ----

select avg("age") from "users"

-- ----

select avg("age", "logins") "avg" from "users"

-- ----

select avg("age") from "users"

select avg("age") from "users"

-- ----

select avg("age") as "a" from "users"

-- ----

select avg("age") as "a" from "users"

-- ----

select avg("age") from "users"

-- ----

select avg("age", "logins") as "avg" from "users"

-- ----

select avg("age") from "users"

select avg("age") from "users"

-- ----

select avg("age") as "a" from "users"

-- ----

select avg("age") as "a" from "users"

-- ----

select avg("age") from "users"

-- ----

select avg("age", "logins") as "avg" from "users"

-- ----

select avg("age") from "users"

select avg(`age`) from `users`

-- ----

select avg(`age`) as `a` from `users`

-- ----

select avg(`age`) as `a` from `users`

-- ----

select avg(`age`) from `users`

-- ----

select avg(`age`, `logins`) as `avg` from `users`

-- ----

select avg(`age`) from `users`

Use avgDistinct to add a distinct expression inside the aggregate function.

knex('users').avgDistinct('age');

select avg(distinct "age") from "users"

select avg(distinct [age]) from [users]

select avg(distinct `age`) from `users`

select avg(distinct "age") from "users"

select avg(distinct "age") from "users"

select avg(distinct "age") from "users"

select avg(distinct `age`) from `users`

increment

.increment(column, amount)

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

knex('accounts').where('userid', '=', 1).increment('balance', 10);
knex('accounts').where('id', '=', 1).increment({
  balance: 10,
  times: 1,
});

update "accounts" set "balance" = "balance" + ? where "userid" = ?

-- ----

update "accounts" set "balance" = "balance" + ?, "times" = "times" + ? where "id" = ?

update [accounts] set [balance] = [balance] + ? where [userid] = ?;select @@rowcount

-- ----

update [accounts] set [balance] = [balance] + ?, [times] = [times] + ? where [id] = ?;select @@rowcount

update `accounts` set `balance` = `balance` + ? where `userid` = ?

-- ----

update `accounts` set `balance` = `balance` + ?, `times` = `times` + ? where `id` = ?

update "accounts" set "balance" = "balance" + ? where "userid" = ?

-- ----

update "accounts" set "balance" = "balance" + ?, "times" = "times" + ? where "id" = ?

update "accounts" set "balance" = "balance" + ? where "userid" = ?

-- ----

update "accounts" set "balance" = "balance" + ?, "times" = "times" + ? where "id" = ?

update "accounts" set "balance" = "balance" + ? where "userid" = ?

-- ----

update "accounts" set "balance" = "balance" + ?, "times" = "times" + ? where "id" = ?

update `accounts` set `balance` = `balance` + ? where `userid` = ?

-- ----

update `accounts` set `balance` = `balance` + ?, `times` = `times` + ? where `id` = ?

decrement

.decrement(column, amount)

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

knex('accounts').where('userid', '=', 1).decrement('balance', 5);
knex('accounts').where('id', '=', 1).decrement({
  balance: 50,
});

update "accounts" set "balance" = "balance" - ? where "userid" = ?

-- ----

update "accounts" set "balance" = "balance" - ? where "id" = ?

update [accounts] set [balance] = [balance] - ? where [userid] = ?;select @@rowcount

-- ----

update [accounts] set [balance] = [balance] - ? where [id] = ?;select @@rowcount

update `accounts` set `balance` = `balance` - ? where `userid` = ?

-- ----

update `accounts` set `balance` = `balance` - ? where `id` = ?

update "accounts" set "balance" = "balance" - ? where "userid" = ?

-- ----

update "accounts" set "balance" = "balance" - ? where "id" = ?

update "accounts" set "balance" = "balance" - ? where "userid" = ?

-- ----

update "accounts" set "balance" = "balance" - ? where "id" = ?

update "accounts" set "balance" = "balance" - ? where "userid" = ?

-- ----

update "accounts" set "balance" = "balance" - ? where "id" = ?

update `accounts` set `balance` = `balance` - ? where `userid` = ?

-- ----

update `accounts` set `balance` = `balance` - ? where `id` = ?

truncate

.truncate()

Truncates the current table.

knex('accounts').truncate();

truncate "accounts"

truncate table [accounts]

truncate `accounts`

truncate table "accounts"

truncate "accounts" restart identity

truncate "accounts"

delete from `accounts`

pluck

.pluck(id)

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

first

.first([columns])

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

Returns undefined when no rows match.

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

hintComment MY+OR only

.hintComment(hint|hints)

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

select /*+ NO_ICP(accounts) */ * from "accounts" where "userid" = ?

select /*+ NO_ICP(accounts) */ * from [accounts] where [userid] = ?

select /*+ NO_ICP(accounts) */ * from `accounts` where `userid` = ?

select /*+ NO_ICP(accounts) */ * from "accounts" where "userid" = ?

select /*+ NO_ICP(accounts) */ * from "accounts" where "userid" = ?

select /*+ NO_ICP(accounts) */ * from "accounts" where "userid" = ?

select /*+ NO_ICP(accounts) */ * from `accounts` where `userid` = ?

comment

.comment(comment)

Prepend comment to the sql query using the syntax /* ... */. Some characters are forbidden such as /*, */ and ?.

knex('users').where('id', '=', 1).comment('Get user by id');

/* Get user by id */ select * from "users" where "id" = ?

/* Get user by id */ select * from [users] where [id] = ?

/* Get user by id */ select * from `users` where `id` = ?

/* Get user by id */ select * from "users" where "id" = ?

/* Get user by id */ select * from "users" where "id" = ?

/* Get user by id */ select * from "users" where "id" = ?

/* Get user by id */ select * from `users` where `id` = ?

clone

.clone()

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

denseRank

.denseRank(alias, ~mixed~)

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

select *, dense_rank() over (partition by "firstName" order by "email") as alias_name from "users"

select *, dense_rank() over (partition by [firstName] order by [email]) as alias_name from [users]

select *, dense_rank() over (partition by `firstName` order by `email`) as alias_name from `users`

select *, dense_rank() over (partition by "firstName" order by "email") as alias_name from "users"

select *, dense_rank() over (partition by "firstName" order by "email") as alias_name from "users"

select *, dense_rank() over (partition by "firstName" order by "email") as alias_name from "users"

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

select *, dense_rank() over (partition by "firstName", "lastName" order by "email", "address") as alias_name from "users"

select *, dense_rank() over (partition by [firstName], [lastName] order by [email], [address]) as alias_name from [users]

select *, dense_rank() over (partition by `firstName`, `lastName` order by `email`, `address`) as alias_name from `users`

select *, dense_rank() over (partition by "firstName", "lastName" order by "email", "address") as alias_name from "users"

select *, dense_rank() over (partition by "firstName", "lastName" order by "email", "address") as alias_name from "users"

select *, dense_rank() over (partition by "firstName", "lastName" order by "email", "address") as alias_name from "users"

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

select *, dense_rank() over (order by "email") as alias_name from "users"

select *, dense_rank() over (order by [email]) as alias_name from [users]

select *, dense_rank() over (order by `email`) as alias_name from `users`

select *, dense_rank() over (order by "email") as alias_name from "users"

select *, dense_rank() over (order by "email") as alias_name from "users"

select *, dense_rank() over (order by "email") as alias_name from "users"

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

select *, dense_rank() over (partition by "firstName" order by "email") as alias_name from "users"

select *, dense_rank() over (partition by [firstName] order by [email]) as alias_name from [users]

select *, dense_rank() over (partition by `firstName` order by `email`) as alias_name from `users`

select *, dense_rank() over (partition by "firstName" order by "email") as alias_name from "users"

select *, dense_rank() over (partition by "firstName" order by "email") as alias_name from "users"

select *, dense_rank() over (partition by "firstName" order by "email") as alias_name from "users"

select *, dense_rank() over (partition by `firstName` order by `email`) as alias_name from `users`

rank

.rank(alias, ~mixed~)

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

select *, rank() over (partition by "firstName" order by "email") as alias_name from "users"

select *, rank() over (partition by [firstName] order by [email]) as alias_name from [users]

select *, rank() over (partition by `firstName` order by `email`) as alias_name from `users`

select *, rank() over (partition by "firstName" order by "email") as alias_name from "users"

select *, rank() over (partition by "firstName" order by "email") as alias_name from "users"

select *, rank() over (partition by "firstName" order by "email") as alias_name from "users"

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

select *, rank() over (partition by "firstName", "lastName" order by "email", "address") as alias_name from "users"

select *, rank() over (partition by [firstName], [lastName] order by [email], [address]) as alias_name from [users]

select *, rank() over (partition by `firstName`, `lastName` order by `email`, `address`) as alias_name from `users`

select *, rank() over (partition by "firstName", "lastName" order by "email", "address") as alias_name from "users"

select *, rank() over (partition by "firstName", "lastName" order by "email", "address") as alias_name from "users"

select *, rank() over (partition by "firstName", "lastName" order by "email", "address") as alias_name from "users"

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

select *, rank() over (order by "email") as alias_name from "users"

select *, rank() over (order by [email]) as alias_name from [users]

select *, rank() over (order by `email`) as alias_name from `users`

select *, rank() over (order by "email") as alias_name from "users"

select *, rank() over (order by "email") as alias_name from "users"

select *, rank() over (order by "email") as alias_name from "users"

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

select *, rank() over (partition by "firstName" order by "email") as alias_name from "users"

select *, rank() over (partition by [firstName] order by [email]) as alias_name from [users]

select *, rank() over (partition by `firstName` order by `email`) as alias_name from `users`

select *, rank() over (partition by "firstName" order by "email") as alias_name from "users"

select *, rank() over (partition by "firstName" order by "email") as alias_name from "users"

select *, rank() over (partition by "firstName" order by "email") as alias_name from "users"

select *, rank() over (partition by `firstName` order by `email`) as alias_name from `users`

rowNumber

.rowNumber(alias, ~mixed~)

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

select *, row_number() over (partition by "firstName" order by "email") as alias_name from "users"

select *, row_number() over (partition by [firstName] order by [email]) as alias_name from [users]

select *, row_number() over (partition by `firstName` order by `email`) as alias_name from `users`

select *, row_number() over (partition by "firstName" order by "email") as alias_name from "users"

select *, row_number() over (partition by "firstName" order by "email") as alias_name from "users"

select *, row_number() over (partition by "firstName" order by "email") as alias_name from "users"

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

select *, row_number() over (partition by "firstName", "lastName" order by "email", "address") as alias_name from "users"

select *, row_number() over (partition by [firstName], [lastName] order by [email], [address]) as alias_name from [users]

select *, row_number() over (partition by `firstName`, `lastName` order by `email`, `address`) as alias_name from `users`

select *, row_number() over (partition by "firstName", "lastName" order by "email", "address") as alias_name from "users"

select *, row_number() over (partition by "firstName", "lastName" order by "email", "address") as alias_name from "users"

select *, row_number() over (partition by "firstName", "lastName" order by "email", "address") as alias_name from "users"

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

select *, row_number() over (order by "email") as alias_name from "users"

select *, row_number() over (order by [email]) as alias_name from [users]

select *, row_number() over (order by `email`) as alias_name from `users`

select *, row_number() over (order by "email") as alias_name from "users"

select *, row_number() over (order by "email") as alias_name from "users"

select *, row_number() over (order by "email") as alias_name from "users"

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

select *, row_number() over (partition by "firstName" order by "email") as alias_name from "users"

select *, row_number() over (partition by [firstName] order by [email]) as alias_name from [users]

select *, row_number() over (partition by `firstName` order by `email`) as alias_name from `users`

select *, row_number() over (partition by "firstName" order by "email") as alias_name from "users"

select *, row_number() over (partition by "firstName" order by "email") as alias_name from "users"

select *, row_number() over (partition by "firstName" order by "email") as alias_name from "users"

select *, row_number() over (partition by `firstName` order by `email`) as alias_name from `users`

partitionBy

.partitionBy(column, direction)

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

select *, row_number() over (partition by "firstName" order by ) as alias_name from "users"

select *, row_number() over (partition by [firstName] order by ) as alias_name from [users]

select *, row_number() over (partition by `firstName` order by ) as alias_name from `users`

select *, row_number() over (partition by "firstName" order by ) as alias_name from "users"

select *, row_number() over (partition by "firstName" order by ) as alias_name from "users"

select *, row_number() over (partition by "firstName" order by ) as alias_name from "users"

select *, row_number() over (partition by `firstName` order by ) as alias_name from `users`

With direction sort :

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

select *, row_number() over (partition by "firstName" desc order by ) as alias_name from "users"

select *, row_number() over (partition by [firstName] desc order by ) as alias_name from [users]

select *, row_number() over (partition by `firstName` desc order by ) as alias_name from `users`

select *, row_number() over (partition by "firstName" desc order by ) as alias_name from "users"

select *, row_number() over (partition by "firstName" desc order by ) as alias_name from "users"

select *, row_number() over (partition by "firstName" desc order by ) as alias_name from "users"

select *, row_number() over (partition by `firstName` desc order by ) as alias_name from `users`

With multiobject :

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

select *, row_number() over (partition by "firstName" asc, "lastName" desc order by ) as alias_name from "users"

select *, row_number() over (partition by [firstName] asc, [lastName] desc order by ) as alias_name from [users]

select *, row_number() over (partition by `firstName` asc, `lastName` desc order by ) as alias_name from `users`

select *, row_number() over (partition by "firstName" asc, "lastName" desc order by ) as alias_name from "users"

select *, row_number() over (partition by "firstName" asc, "lastName" desc order by ) as alias_name from "users"

select *, row_number() over (partition by "firstName" asc, "lastName" desc order by ) as alias_name from "users"

select *, row_number() over (partition by `firstName` asc, `lastName` desc order by ) as alias_name from `users`

modify

.modify(fn, *arguments)

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

columnInfo

.columnInfo([columnName])

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) {
    /*...*/
  });

debug

.debug([enabled])

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

connection

.connection(dbConnection)

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

options

.options()

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({
    /*...*/
  });

Better-SQLite3

better-sqlite3 can return integers as either plain JS number (the default) or as bigint. You can configure this per-query with .options({safeIntegers: boolean}).

Example:

// {balance: bigint}[]
const rows = await knex('accounts')
  .select('balance')
  .options({ safeIntegers: true });

// {balance: number}[]
const rows = await knex('accounts')
  .select('balance')
  .options({ safeIntegers: false });

The default for all queries can be set via global configuration options

queryContext

.queryContext(context)

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

select "a1"."email", "a2"."email" from "accounts" as "a1"

select [a1].[email], [a2].[email] from [accounts] as [a1]

select `a1`.`email`, `a2`.`email` from `accounts` as `a1`

select "a1"."email", "a2"."email" from "accounts" "a1"

select "a1"."email", "a2"."email" from "accounts" as "a1"

select "a1"."email", "a2"."email" from "accounts" as "a1"

select `a1`.`email`, `a2`.`email` from `accounts` as `a1`

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.

Extending Query Builder

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

It allows to add custom function to the Query Builder.

Example:

const { knex } = require('knex');
knex.QueryBuilder.extend('customSelect', function (value) {
  return this.select(this.client.raw(`${value} as value`));
});

const meaningOfLife = await knex('accounts').customSelect(42);

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).

// knex.d.ts

import { Knex as KnexOriginal } from 'knex';

declare module 'knex' {
  namespace Knex {
    interface QueryInterface {
      customSelect<TRecord, TResult>(
        value: number
      ): KnexOriginal.QueryBuilder<TRecord, TResult>;
    }
  }
}

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

// tsconfig.json
{
  "compilerOptions": {
    "typeRoots": ["node_modules/@types", "@types"]
  }
}

Where Clauses

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: Knex may throw errors during SQL compilation when the query is unsound. This prevents unexpected data loss or unexpected behavior. "Where" clauses may throw when:

• Any "undefined" value is passed to a where clause
• A "where" clause is present on a "truncate" statement

Examples of queries that would throw:

knex('accounts').where('login', undefined).select().toSQL();
knex('logs').where('server', 'dev').truncate().toSQL();

where

.where(~mixed~).orWhere

Object Syntax:

knex('users')
  .where({
    first_name: 'Test',
    last_name: 'User',
  })
  .select('id');

select "id" from "users" where "first_name" = ? and "last_name" = ?

select [id] from [users] where [first_name] = ? and [last_name] = ?

select `id` from `users` where `first_name` = ? and `last_name` = ?

select "id" from "users" where "first_name" = ? and "last_name" = ?

select "id" from "users" where "first_name" = ? and "last_name" = ?

select "id" from "users" where "first_name" = ? and "last_name" = ?

select `id` from `users` where `first_name` = ? and `last_name` = ?

Key, Value:

knex('users').where('id', 1);

select * from "users" where "id" = ?

select * from [users] where [id] = ?

select * from `users` where `id` = ?

select * from "users" where "id" = ?

select * from "users" where "id" = ?

select * from "users" where "id" = ?

select * from `users` where `id` = ?

Functions:

knex('users')
  .where((builder) =>
    builder.whereIn('id', [1, 11, 15]).whereNotIn('id', [17, 19])
  )
  .andWhere(function () {
    this.where('id', '>', 10);
  });

select * from "users" where ("id" in (?, ?, ?) and "id" not in (?, ?)) and ("id" > ?)

select * from [users] where ([id] in (?, ?, ?) and [id] not in (?, ?)) and ([id] > ?)

select * from `users` where (`id` in (?, ?, ?) and `id` not in (?, ?)) and (`id` > ?)

select * from "users" where ("id" in (?, ?, ?) and "id" not in (?, ?)) and ("id" > ?)

select * from "users" where ("id" in (?, ?, ?) and "id" not in (?, ?)) and ("id" > ?)

select * from "users" where ("id" in (?, ?, ?) and "id" not in (?, ?)) and ("id" > ?)

select * from `users` where (`id` in (?, ?, ?) and `id` not in (?, ?)) and (`id` > ?)

Grouped Chain:

knex('users')
  .where(function () {
    this.where('id', 1).orWhere('id', '>', 10);
  })
  .orWhere({ name: 'Tester' });

select * from "users" where ("id" = ? or "id" > ?) or ("name" = ?)

select * from [users] where ([id] = ? or [id] > ?) or ([name] = ?)

select * from `users` where (`id` = ? or `id` > ?) or (`name` = ?)

select * from "users" where ("id" = ? or "id" > ?) or ("name" = ?)

select * from "users" where ("id" = ? or "id" > ?) or ("name" = ?)

select * from "users" where ("id" = ? or "id" > ?) or ("name" = ?)

select * from `users` where (`id` = ? or `id` > ?) or (`name` = ?)

Operator:

knex('users').where('columnName', 'like', '%rowlikeme%');

select * from "users" where "columnName" like ?

select * from [users] where [columnName] like ?

select * from `users` where `columnName` like ?

select * from "users" where "columnName" like ?

select * from "users" where "columnName" like ?

select * from "users" where "columnName" like ?

select * from `users` where `columnName` like ?

The above query demonstrates the common use case of returning all users for which a specific pattern appears within a designated column.

knex('users').where('votes', '>', 100);

const subquery = knex('users')
  .where('votes', '>', 100)
  .andWhere('status', 'active')
  .orWhere('name', 'John')
  .select('id');
knex('accounts').where('id', 'in', subquery);

select * from "users" where "votes" > ?

-- ----

select * from "accounts" where "id" in (select "id" from "users" where "votes" > ? and "status" = ? or "name" = ?)

select * from [users] where [votes] > ?

-- ----

select * from [accounts] where [id] in (select [id] from [users] where [votes] > ? and [status] = ? or [name] = ?)

select * from `users` where `votes` > ?

-- ----

select * from `accounts` where `id` in (select `id` from `users` where `votes` > ? and `status` = ? or `name` = ?)

select * from "users" where "votes" > ?

-- ----

select * from "accounts" where "id" in (select "id" from "users" where "votes" > ? and "status" = ? or "name" = ?)

select * from "users" where "votes" > ?

-- ----

select * from "accounts" where "id" in (select "id" from "users" where "votes" > ? and "status" = ? or "name" = ?)

select * from "users" where "votes" > ?

-- ----

select * from "accounts" where "id" in (select "id" from "users" where "votes" > ? and "status" = ? or "name" = ?)

select * from `users` where `votes` > ?

-- ----

select * from `accounts` where `id` in (select `id` from `users` where `votes` > ? and `status` = ? or `name` = ?)

.orWhere with an object automatically wraps the statement and creates an or (and - and - and) clause

knex('users').where('id', 1).orWhere({ votes: 100, user: 'knex' });

select * from "users" where "id" = ? or ("votes" = ? and "user" = ?)

select * from [users] where [id] = ? or ([votes] = ? and [user] = ?)

select * from `users` where `id` = ? or (`votes` = ? and `user` = ?)

select * from "users" where "id" = ? or ("votes" = ? and "user" = ?)

select * from "users" where "id" = ? or ("votes" = ? and "user" = ?)

select * from "users" where "id" = ? or ("votes" = ? and "user" = ?)

select * from `users` where `id` = ? or (`votes` = ? and `user` = ?)

whereNot

.whereNot(~mixed~).orWhereNot

Object Syntax:

knex('users')
  .whereNot({
    first_name: 'Test',
    last_name: 'User',
  })
  .select('id');

select "id" from "users" where not "first_name" = ? and not "last_name" = ?

select [id] from [users] where not [first_name] = ? and not [last_name] = ?

select `id` from `users` where not `first_name` = ? and not `last_name` = ?

select "id" from "users" where not "first_name" = ? and not "last_name" = ?

select "id" from "users" where not "first_name" = ? and not "last_name" = ?

select "id" from "users" where not "first_name" = ? and not "last_name" = ?

select `id` from `users` where not `first_name` = ? and not `last_name` = ?

Key, Value:

knex('users').whereNot('id', 1);

select * from "users" where not "id" = ?

select * from [users] where not [id] = ?

select * from `users` where not `id` = ?

select * from "users" where not "id" = ?

select * from "users" where not "id" = ?

select * from "users" where not "id" = ?

select * from `users` where not `id` = ?

Grouped Chain:

knex('users')
  .whereNot(function () {
    this.where('id', 1).orWhereNot('id', '>', 10);
  })
  .orWhereNot({ name: 'Tester' });

select * from "users" where not ("id" = ? or not "id" > ?) or not "name" = ?

select * from [users] where not ([id] = ? or not [id] > ?) or not [name] = ?

select * from `users` where not (`id` = ? or not `id` > ?) or not `name` = ?

select * from "users" where not ("id" = ? or not "id" > ?) or not "name" = ?

select * from "users" where not ("id" = ? or not "id" > ?) or not "name" = ?

select * from "users" where not ("id" = ? or not "id" > ?) or not "name" = ?

select * from `users` where not (`id` = ? or not `id` > ?) or not `name` = ?

Operator:

knex('users').whereNot('votes', '>', 100);

select * from "users" where not "votes" > ?

select * from [users] where not [votes] > ?

select * from `users` where not `votes` > ?

select * from "users" where not "votes" > ?

select * from "users" where not "votes" > ?

select * from "users" where not "votes" > ?

select * from `users` where not `votes` > ?

WARNING

WhereNot is not suitable for "in" and "between" type subqueries. You should use "not in" and "not between" instead.

const subquery = knex('users')
  .whereNot('votes', '>', 100)
  .andWhere('status', 'active')
  .orWhere('name', 'John')
  .select('id');
knex('accounts').where('id', 'not in', subquery);

select * from "accounts" where "id" not in (select "id" from "users" where not "votes" > ? and "status" = ? or "name" = ?)

select * from [accounts] where [id] not in (select [id] from [users] where not [votes] > ? and [status] = ? or [name] = ?)

select * from `accounts` where `id` not in (select `id` from `users` where not `votes` > ? and `status` = ? or `name` = ?)

select * from "accounts" where "id" not in (select "id" from "users" where not "votes" > ? and "status" = ? or "name" = ?)

select * from "accounts" where "id" not in (select "id" from "users" where not "votes" > ? and "status" = ? or "name" = ?)

select * from "accounts" where "id" not in (select "id" from "users" where not "votes" > ? and "status" = ? or "name" = ?)

select * from `accounts` where `id` not in (select `id` from `users` where not `votes` > ? and `status` = ? or `name` = ?)

whereIn

.whereIn(column|columns, array|callback|builder).orWhereIn

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]);
knex
  .select('name')
  .from('users')
  .whereIn('account_id', function () {
    this.select('id').from('accounts');
  });

const subquery = knex.select('id').from('accounts');
knex.select('name').from('users').whereIn('account_id', subquery);
knex
  .select('name')
  .from('users')
  .whereIn(
    ['account_id', 'email'],
    [
      [3, '[email protected]'],
      [4, '[email protected]'],
    ]
  );
knex
  .select('name')
  .from('users')
  .whereIn(
    ['account_id', 'email'],
    knex.select('id', 'email').from('accounts')
  );

select "name" from "users" where "id" in (?, ?, ?) or "id" in (?, ?, ?)

-- ----

select "name" from "users" where "account_id" in (select "id" from "accounts")

-- ----

select "name" from "users" where "account_id" in (select "id" from "accounts")

-- ----

select "name" from "users" where ("account_id", "email") in ((?, ?), (?, ?))

-- ----

select "name" from "users" where ("account_id", "email") in (select "id", "email" from "accounts")

select [name] from [users] where [id] in (?, ?, ?) or [id] in (?, ?, ?)

-- ----

select [name] from [users] where [account_id] in (select [id] from [accounts])

-- ----

select [name] from [users] where [account_id] in (select [id] from [accounts])

-- ----

select [name] from [users] where ([account_id], [email]) in ((?, ?), (?, ?))

-- ----

select [name] from [users] where ([account_id], [email]) in (select [id], [email] from [accounts])

select `name` from `users` where `id` in (?, ?, ?) or `id` in (?, ?, ?)

-- ----

select `name` from `users` where `account_id` in (select `id` from `accounts`)

-- ----

select `name` from `users` where `account_id` in (select `id` from `accounts`)

-- ----

select `name` from `users` where (`account_id`, `email`) in ((?, ?), (?, ?))

-- ----

select `name` from `users` where (`account_id`, `email`) in (select `id`, `email` from `accounts`)

select "name" from "users" where "id" in (?, ?, ?) or "id" in (?, ?, ?)

-- ----

select "name" from "users" where "account_id" in (select "id" from "accounts")

-- ----

select "name" from "users" where "account_id" in (select "id" from "accounts")

-- ----

select "name" from "users" where ("account_id", "email") in ((?, ?), (?, ?))

-- ----

select "name" from "users" where ("account_id", "email") in (select "id", "email" from "accounts")

select "name" from "users" where "id" in (?, ?, ?) or "id" in (?, ?, ?)

-- ----

select "name" from "users" where "account_id" in (select "id" from "accounts")

-- ----

select "name" from "users" where "account_id" in (select "id" from "accounts")

-- ----

select "name" from "users" where ("account_id", "email") in ((?, ?), (?, ?))

-- ----

select "name" from "users" where ("account_id", "email") in (select "id", "email" from "accounts")

select "name" from "users" where "id" in (?, ?, ?) or "id" in (?, ?, ?)

-- ----

select "name" from "users" where "account_id" in (select "id" from "accounts")

-- ----

select "name" from "users" where "account_id" in (select "id" from "accounts")

-- ----

select "name" from "users" where ("account_id", "email") in ((?, ?), (?, ?))

-- ----

select "name" from "users" where ("account_id", "email") in (select "id", "email" from "accounts")

select `name` from `users` where `id` in (?, ?, ?) or `id` in (?, ?, ?)

-- ----

select `name` from `users` where `account_id` in (select `id` from `accounts`)

-- ----

select `name` from `users` where `account_id` in (select `id` from `accounts`)

-- ----

select `name` from `users` where (`account_id`, `email`) in ( values (?, ?), (?, ?))

-- ----

select `name` from `users` where (`account_id`, `email`) in (select `id`, `email` from `accounts`)

whereNotIn

.whereNotIn(column, array|callback|builder).orWhereNotIn

knex('users').whereNotIn('id', [1, 2, 3]);
knex('users').where('name', 'like', '%Test%').orWhereNotIn('id', [1, 2, 3]);

select * from "users" where "id" not in (?, ?, ?)

-- ----

select * from "users" where "name" like ? or "id" not in (?, ?, ?)

select * from [users] where [id] not in (?, ?, ?)

-- ----

select * from [users] where [name] like ? or [id] not in (?, ?, ?)

select * from `users` where `id` not in (?, ?, ?)

-- ----

select * from `users` where `name` like ? or `id` not in (?, ?, ?)

select * from "users" where "id" not in (?, ?, ?)

-- ----

select * from "users" where "name" like ? or "id" not in (?, ?, ?)

select * from "users" where "id" not in (?, ?, ?)

-- ----

select * from "users" where "name" like ? or "id" not in (?, ?, ?)

select * from "users" where "id" not in (?, ?, ?)

-- ----

select * from "users" where "name" like ? or "id" not in (?, ?, ?)

select * from `users` where `id` not in (?, ?, ?)

-- ----

select * from `users` where `name` like ? or `id` not in (?, ?, ?)

whereNull

.whereNull(column).orWhereNull

knex('users').whereNull('updated_at');

select * from "users" where "updated_at" is null

select * from [users] where [updated_at] is null

select * from `users` where `updated_at` is null

select * from "users" where "updated_at" is null

select * from "users" where "updated_at" is null

select * from "users" where "updated_at" is null

select * from `users` where `updated_at` is null

whereNotNull

.whereNotNull(column).orWhereNotNull

knex('users').whereNotNull('created_at');

select * from "users" where "created_at" is not null

select * from [users] where [created_at] is not null

select * from `users` where `created_at` is not null

select * from "users" where "created_at" is not null

select * from "users" where "created_at" is not null

select * from "users" where "created_at" is not null

select * from `users` where `created_at` is not null

whereExists

.whereExists(builder | callback).orWhereExists

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

select * from "users" where exists (select * from "accounts" where users.account_id = accounts.id)

-- ----

select * from "users" where exists (select * from "accounts" where users.account_id = accounts.id)

select * from [users] where exists (select * from [accounts] where users.account_id = accounts.id)

-- ----

select * from [users] where exists (select * from [accounts] where users.account_id = accounts.id)

select * from `users` where exists (select * from `accounts` where users.account_id = accounts.id)

-- ----

select * from `users` where exists (select * from `accounts` where users.account_id = accounts.id)

select * from "users" where exists (select * from "accounts" where users.account_id = accounts.id)

-- ----

select * from "users" where exists (select * from "accounts" where users.account_id = accounts.id)

select * from "users" where exists (select * from "accounts" where users.account_id = accounts.id)

-- ----

select * from "users" where exists (select * from "accounts" where users.account_id = accounts.id)

select * from "users" where exists (select * from "accounts" where users.account_id = accounts.id)

-- ----

select * from "users" where exists (select * from "accounts" where users.account_id = accounts.id)

select * from `users` where exists (select * from `accounts` where users.account_id = accounts.id)

-- ----

select * from `users` where exists (select * from `accounts` where users.account_id = accounts.id)

whereNotExists

.whereNotExists(builder | callback).orWhereNotExists

knex('users').whereNotExists(function () {
  this.select('*').from('accounts').whereRaw('users.account_id = accounts.id');
});
knex('users').whereNotExists(
  knex.select('*').from('accounts').whereRaw('users.account_id = accounts.id')
);

select * from "users" where not exists (select * from "accounts" where users.account_id = accounts.id)

-- ----

select * from "users" where not exists (select * from "accounts" where users.account_id = accounts.id)

select * from [users] where not exists (select * from [accounts] where users.account_id = accounts.id)

-- ----

select * from [users] where not exists (select * from [accounts] where users.account_id = accounts.id)

select * from `users` where not exists (select * from `accounts` where users.account_id = accounts.id)

-- ----

select * from `users` where not exists (select * from `accounts` where users.account_id = accounts.id)

select * from "users" where not exists (select * from "accounts" where users.account_id = accounts.id)

-- ----

select * from "users" where not exists (select * from "accounts" where users.account_id = accounts.id)

select * from "users" where not exists (select * from "accounts" where users.account_id = accounts.id)

-- ----

select * from "users" where not exists (select * from "accounts" where users.account_id = accounts.id)

select * from "users" where not exists (select * from "accounts" where users.account_id = accounts.id)

-- ----

select * from "users" where not exists (select * from "accounts" where users.account_id = accounts.id)

select * from `users` where not exists (select * from `accounts` where users.account_id = accounts.id)

-- ----

select * from `users` where not exists (select * from `accounts` where users.account_id = accounts.id)

whereBetween

.whereBetween(column, range).orWhereBetween

knex('users').whereBetween('votes', [1, 100]);

select * from "users" where "votes" between ? and ?

select * from [users] where [votes] between ? and ?

select * from `users` where `votes` between ? and ?

select * from "users" where "votes" between ? and ?

select * from "users" where "votes" between ? and ?

select * from "users" where "votes" between ? and ?

select * from `users` where `votes` between ? and ?

whereNotBetween

.whereNotBetween(column, range).orWhereNotBetween

knex('users').whereNotBetween('votes', [1, 100]);

select * from "users" where "votes" not between ? and ?

select * from [users] where [votes] not between ? and ?

select * from `users` where `votes` not between ? and ?

select * from "users" where "votes" not between ? and ?

select * from "users" where "votes" not between ? and ?

select * from "users" where "votes" not between ? and ?

select * from `users` where `votes` not between ? and ?

whereRaw

.whereRaw(query, [bindings])

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

knex('users').whereRaw('id = ?', [1]);

select * from "users" where id = ?

select * from [users] where id = ?

select * from `users` where id = ?

select * from "users" where id = ?

select * from "users" where id = ?

select * from "users" where id = ?

select * from `users` where id = ?

whereLike

.whereLike(column, string|builder|raw).orWhereLike

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

knex('users').whereLike('email', '%mail%');
knex('users')
  .whereLike('email', '%mail%')
  .andWhereLike('email', '%.com')
  .orWhereLike('email', '%name%');

select * from "users" where "email" like ?

-- ----

select * from "users" where "email" like ? and "email" like ? or "email" like ?

select * from [users] where [email] collate SQL_Latin1_General_CP1_CS_AS like ?

-- ----

select * from [users] where [email] collate SQL_Latin1_General_CP1_CS_AS like ? and [email] collate SQL_Latin1_General_CP1_CS_AS like ? or [email] collate SQL_Latin1_General_CP1_CS_AS like ?

select * from `users` where `email` like ? COLLATE utf8_bin

-- ----

select * from `users` where `email` like ? COLLATE utf8_bin and `email` like ? COLLATE utf8_bin or `email` like ? COLLATE utf8_bin

select * from "users" where "email" like ?

-- ----

select * from "users" where "email" like ? and "email" like ? or "email" like ?

select * from "users" where "email" like ?

-- ----

select * from "users" where "email" like ? and "email" like ? or "email" like ?

select * from "users" where "email" like ?

-- ----

select * from "users" where "email" like ? and "email" like ? or "email" like ?

select * from `users` where `email` like ?

-- ----

select * from `users` where `email` like ? and `email` like ? or `email` like ?

whereILike

.whereILike(column, string|builder|raw).orWhereILike

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

knex('users').whereILike('email', '%mail%');
knex('users')
  .whereILike('email', '%MAIL%')
  .andWhereILike('email', '%.COM')
  .orWhereILike('email', '%NAME%');

select * from "users" where "email" ilike ?

-- ----

select * from "users" where "email" ilike ? and "email" ilike ? or "email" ilike ?

select * from [users] where [email] collate SQL_Latin1_General_CP1_CI_AS like ?

-- ----

select * from [users] where [email] collate SQL_Latin1_General_CP1_CI_AS like ? and [email] collate SQL_Latin1_General_CP1_CI_AS like ? or [email] collate SQL_Latin1_General_CP1_CI_AS like ?

select * from `users` where `email` like ?

-- ----

select * from `users` where `email` like ? and `email` like ? or `email` like ?

select * from "users" where "email" ilike ?

-- ----

select * from "users" where "email" ilike ? and "email" ilike ? or "email" ilike ?

select * from "users" where "email" ilike ?

-- ----

select * from "users" where "email" ilike ? and "email" ilike ? or "email" ilike ?

select * from "users" where "email" ilike ?

-- ----

select * from "users" where "email" ilike ? and "email" ilike ? or "email" ilike ?

select * from `users` where `email` ilike ?

-- ----

select * from `users` where `email` ilike ? and `email` ilike ? or `email` ilike ?

whereJsonObject

.whereJsonObject(column, string|json|builder|raw)

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

knex('users').whereJsonObject('json_col', { name: 'user_name' });

select * from "users" where "json_col" = ?

select * from [users] where [json_col] = ?

select * from `users` where json_contains(`json_col`, ?)

select * from "users" where "json_col" = ?

select * from "users" where "json_col" = ?

select * from "users" where "json_col" = ?

select * from `users` where `json_col` = ?

whereJsonPath

.whereJsonPath(column, jsonPath, operator, value)

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);
knex('users').whereJsonPath('json_col', '$.name', '=', 'username');

select * from "users" where json_extract_path("json_col", ?)::int > ?

-- ----

select * from "users" where json_extract_path("json_col", ?) #>> '{}' = ?

select * from [users] where JSON_VALUE([json_col], ?) > ?

-- ----

select * from [users] where JSON_VALUE([json_col], ?) = ?

select * from `users` where json_extract(`json_col`, ?) > ?

-- ----

select * from `users` where json_extract(`json_col`, ?) = ?

select * from "users" where json_value("json_col", '$.age') > ?

-- ----

select * from "users" where json_value("json_col", '$.name') = ?

select * from "users" where jsonb_path_query_first("json_col", ?)::int > ?

-- ----

select * from "users" where jsonb_path_query_first("json_col", ?) #>> '{}' = ?

Error: TypeError: Cannot read properties of undefined (reading 'replace')

-- ----

Error: TypeError: Cannot read properties of undefined (reading 'replace')

select * from `users` where json_extract(`json_col`, ?) > ?

-- ----

select * from `users` where json_extract(`json_col`, ?) = ?

whereJsonSupersetOf SQMSORRS

.whereJsonSupersetOf(column, string|json|builder|raw)

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

select * from "users" where "hobbies" @> ?

Error: Json superset where clause not actually supported by MSSQL

select * from `users` where json_contains(`hobbies`,?)

Error: Json superset where clause not actually supported by Oracle

select * from "users" where "hobbies" @> ?

Error: Json superset is not supported by Redshift

Error: Json superset where clause not actually supported by SQLite

whereJsonSubsetOf SQMSORRS

.whereJsonSubsetOf(column, string|json|builder|raw)

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

select * from "users" where "hobby" <@ ?

Error: Json subset where clause not actually supported by MSSQL

select * from `users` where json_contains(?,`hobby`)

Error: Json subset where clause not actually supported by Oracle

select * from "users" where "hobby" <@ ?

Error: Json subset is not supported by Redshift

Error: Json subset where clause not actually supported by SQLite

Join Methods

Several methods are provided which assist in building joins.

join

.join(table, first, [operator], second)

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');
knex('users')
  .join('contacts', 'users.id', 'contacts.user_id')
  .select('users.id', 'contacts.phone');

select "users"."id", "contacts"."phone" from "users" inner join "contacts" on "users"."id" = "contacts"."user_id"

-- ----

select "users"."id", "contacts"."phone" from "users" inner join "contacts" on "users"."id" = "contacts"."user_id"

select [users].[id], [contacts].[phone] from [users] inner join [contacts] on [users].[id] = [contacts].[user_id]

-- ----

select [users].[id], [contacts].[phone] from [users] inner join [contacts] on [users].[id] = [contacts].[user_id]

select `users`.`id`, `contacts`.`phone` from `users` inner join `contacts` on `users`.`id` = `contacts`.`user_id`

-- ----

select `users`.`id`, `contacts`.`phone` from `users` inner join `contacts` on `users`.`id` = `contacts`.`user_id`

select "users"."id", "contacts"."phone" from "users" inner join "contacts" on "users"."id" = "contacts"."user_id"

-- ----

select "users"."id", "contacts"."phone" from "users" inner join "contacts" on "users"."id" = "contacts"."user_id"

select "users"."id", "contacts"."phone" from "users" inner join "contacts" on "users"."id" = "contacts"."user_id"

-- ----

select "users"."id", "contacts"."phone" from "users" inner join "contacts" on "users"."id" = "contacts"."user_id"

select "users"."id", "contacts"."phone" from "users" inner join "contacts" on "users"."id" = "contacts"."user_id"

-- ----

select "users"."id", "contacts"."phone" from "users" inner join "contacts" on "users"."id" = "contacts"."user_id"

select `users`.`id`, `contacts`.`phone` from `users` inner join `contacts` on `users`.`id` = `contacts`.`user_id`

-- ----

select `users`.`id`, `contacts`.`phone` from `users` inner join `contacts` on `users`.`id` = `contacts`.`user_id`

For grouped joins, specify a function as the second argument for the join query, and use on with orOn or andOn to create joins that are grouped with parentheses.

knex
  .select('*')
  .from('users')
  .join('accounts', function () {
    this.on('accounts.id', '=', 'users.account_id').orOn(
      'accounts.owner_id',
      '=',
      'users.id'
    );
  });

select * from "users" inner join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from [users] inner join [accounts] on [accounts].[id] = [users].[account_id] or [accounts].[owner_id] = [users].[id]

select * from `users` inner join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

select * from "users" inner join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from "users" inner join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from "users" inner join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from `users` inner join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

For nested join statements, specify a function as first argument of on, orOn or andOn

knex
  .select('*')
  .from('users')
  .join('accounts', function () {
    this.on(function () {
      this.on('accounts.id', '=', 'users.account_id');
      this.orOn('accounts.owner_id', '=', 'users.id');
    });
  });

select * from "users" inner join "accounts" on ("accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id")

select * from [users] inner join [accounts] on ([accounts].[id] = [users].[account_id] or [accounts].[owner_id] = [users].[id])

select * from `users` inner join `accounts` on (`accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`)

select * from "users" inner join "accounts" on ("accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id")

select * from "users" inner join "accounts" on ("accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id")

select * from "users" inner join "accounts" on ("accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id")

select * from `users` inner join `accounts` on (`accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`)

It is also possible to use an object to represent the join syntax.

knex
  .select('*')
  .from('users')
  .join('accounts', { 'accounts.id': 'users.account_id' });

select * from "users" inner join "accounts" on "accounts"."id" = "users"."account_id"

select * from [users] inner join [accounts] on [accounts].[id] = [users].[account_id]

select * from `users` inner join `accounts` on `accounts`.`id` = `users`.`account_id`

select * from "users" inner join "accounts" on "accounts"."id" = "users"."account_id"

select * from "users" inner join "accounts" on "accounts"."id" = "users"."account_id"

select * from "users" inner join "accounts" on "accounts"."id" = "users"."account_id"

select * from `users` inner join `accounts` on `accounts`.`id` = `users`.`account_id`

If you need to use a literal value (string, number, or boolean) in a join instead of a column, use knex.raw.

knex
  .select('*')
  .from('users')
  .join('accounts', 'accounts.type', knex.raw('?', ['admin']));

select * from "users" inner join "accounts" on "accounts"."type" = ?

select * from [users] inner join [accounts] on [accounts].[type] = ?

select * from `users` inner join `accounts` on `accounts`.`type` = ?

select * from "users" inner join "accounts" on "accounts"."type" = ?

select * from "users" inner join "accounts" on "accounts"."type" = ?

select * from "users" inner join "accounts" on "accounts"."type" = ?

select * from `users` inner join `accounts` on `accounts`.`type` = ?

innerJoin

.innerJoin(table, ~mixed~)

knex.from('users').innerJoin('accounts', 'users.id', 'accounts.user_id');
knex.table('users').innerJoin('accounts', 'users.id', '=', 'accounts.user_id');
knex('users').innerJoin('accounts', function () {
  this.on('accounts.id', '=', 'users.account_id').orOn(
    'accounts.owner_id',
    '=',
    'users.id'
  );
});

select * from "users" inner join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" inner join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" inner join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from [users] inner join [accounts] on [users].[id] = [accounts].[user_id]

-- ----

select * from [users] inner join [accounts] on [users].[id] = [accounts].[user_id]

-- ----

select * from [users] inner join [accounts] on [accounts].[id] = [users].[account_id] or [accounts].[owner_id] = [users].[id]

select * from `users` inner join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` inner join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` inner join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

select * from "users" inner join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" inner join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" inner join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from "users" inner join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" inner join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" inner join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from "users" inner join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" inner join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" inner join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from `users` inner join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` inner join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` inner join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

leftJoin

.leftJoin(table, ~mixed~)

knex
  .select('*')
  .from('users')
  .leftJoin('accounts', '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'
    );
  });

select * from "users" left join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" left join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from [users] left join [accounts] on [users].[id] = [accounts].[user_id]

-- ----

select * from [users] left join [accounts] on [accounts].[id] = [users].[account_id] or [accounts].[owner_id] = [users].[id]

select * from `users` left join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` left join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

select * from "users" left join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" left join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from "users" left join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" left join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from "users" left join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" left join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from `users` left join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` left join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

leftOuterJoin

.leftOuterJoin(table, ~mixed~)

knex
  .select('*')
  .from('users')
  .leftOuterJoin('accounts', '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'
    );
  });

select * from "users" left outer join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" left outer join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from [users] left outer join [accounts] on [users].[id] = [accounts].[user_id]

-- ----

select * from [users] left outer join [accounts] on [accounts].[id] = [users].[account_id] or [accounts].[owner_id] = [users].[id]

select * from `users` left outer join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` left outer join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

select * from "users" left outer join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" left outer join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from "users" left outer join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" left outer join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from "users" left outer join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" left outer join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from `users` left outer join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` left outer join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

rightJoin

.rightJoin(table, ~mixed~)

knex
  .select('*')
  .from('users')
  .rightJoin('accounts', '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'
    );
  });

select * from "users" right join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" right join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from [users] right join [accounts] on [users].[id] = [accounts].[user_id]

-- ----

select * from [users] right join [accounts] on [accounts].[id] = [users].[account_id] or [accounts].[owner_id] = [users].[id]

select * from `users` right join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` right join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

select * from "users" right join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" right join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from "users" right join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" right join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from "users" right join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" right join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from `users` right join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` right join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

rightOuterJoin

.rightOuterJoin(table, ~mixed~)

knex
  .select('*')
  .from('users')
  .rightOuterJoin('accounts', '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'
    );
  });

select * from "users" right outer join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" right outer join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from [users] right outer join [accounts] on [users].[id] = [accounts].[user_id]

-- ----

select * from [users] right outer join [accounts] on [accounts].[id] = [users].[account_id] or [accounts].[owner_id] = [users].[id]

select * from `users` right outer join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` right outer join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

select * from "users" right outer join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" right outer join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from "users" right outer join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" right outer join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from "users" right outer join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" right outer join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from `users` right outer join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` right outer join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

fullOuterJoin

.fullOuterJoin(table, ~mixed~)

knex
  .select('*')
  .from('users')
  .fullOuterJoin('accounts', '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'
    );
  });

select * from "users" full outer join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" full outer join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from [users] full outer join [accounts] on [users].[id] = [accounts].[user_id]

-- ----

select * from [users] full outer join [accounts] on [accounts].[id] = [users].[account_id] or [accounts].[owner_id] = [users].[id]

select * from `users` full outer join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` full outer join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

select * from "users" full outer join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" full outer join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from "users" full outer join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" full outer join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from "users" full outer join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" full outer join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from `users` full outer join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` full outer join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

crossJoin

.crossJoin(table, ~mixed~)

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

crossJoin conditions MY+SQ only

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

knex.select('*').from('users').crossJoin('accounts');
knex
  .select('*')
  .from('users')
  .crossJoin('accounts', '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'
    );
  });

select * from "users" cross join "accounts"

-- ----

select * from "users" cross join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" cross join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from [users] cross join [accounts]

-- ----

select * from [users] cross join [accounts] on [users].[id] = [accounts].[user_id]

-- ----

select * from [users] cross join [accounts] on [accounts].[id] = [users].[account_id] or [accounts].[owner_id] = [users].[id]

select * from `users` cross join `accounts`

-- ----

select * from `users` cross join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` cross join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

select * from "users" cross join "accounts"

-- ----

select * from "users" cross join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" cross join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from "users" cross join "accounts"

-- ----

select * from "users" cross join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" cross join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from "users" cross join "accounts"

-- ----

select * from "users" cross join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" cross join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"

select * from `users` cross join `accounts`

-- ----

select * from `users` cross join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` cross join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`

joinRaw

.joinRaw(sql, [bindings])

knex
  .select('*')
  .from('accounts')
  .joinRaw('natural full join table1')
  .where('id', 1);
knex
  .select('*')
  .from('accounts')
  .join(knex.raw('natural full join table1'))
  .where('id', 1);

select * from "accounts" natural full join table1 where "id" = ?

-- ----

select * from "accounts" inner join natural full join table1 where "id" = ?

select * from [accounts] natural full join table1 where [id] = ?

-- ----

select * from [accounts] inner join natural full join table1 where [id] = ?

select * from `accounts` natural full join table1 where `id` = ?

-- ----

select * from `accounts` inner join natural full join table1 where `id` = ?

select * from "accounts" natural full join table1 where "id" = ?

-- ----

select * from "accounts" inner join natural full join table1 where "id" = ?

select * from "accounts" natural full join table1 where "id" = ?

-- ----

select * from "accounts" inner join natural full join table1 where "id" = ?

select * from "accounts" natural full join table1 where "id" = ?

-- ----

select * from "accounts" inner join natural full join table1 where "id" = ?

select * from `accounts` natural full join table1 where `id` = ?

-- ----

select * from `accounts` inner join natural full join table1 where `id` = ?

OnClauses

onIn

.onIn(column, values)

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

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" in (?, ?, ?, ?)

select * from [users] inner join [contacts] on [users].[id] = [contacts].[id] and [contacts].[id] in (?, ?, ?, ?)

select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`id` in (?, ?, ?, ?)

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" in (?, ?, ?, ?)

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" in (?, ?, ?, ?)

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" in (?, ?, ?, ?)

select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`id` in (?, ?, ?, ?)

onNotIn

.onNotIn(column, values)

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

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" not in (?, ?, ?, ?)

select * from [users] inner join [contacts] on [users].[id] = [contacts].[id] and [contacts].[id] not in (?, ?, ?, ?)

select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`id` not in (?, ?, ?, ?)

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" not in (?, ?, ?, ?)

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" not in (?, ?, ?, ?)

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" not in (?, ?, ?, ?)

select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`id` not in (?, ?, ?, ?)

onNull

.onNull(column)

Adds a onNull clause to the query.

knex
  .select('*')
  .from('users')
  .join('contacts', function () {
    this.on('users.id', '=', 'contacts.id').onNull('contacts.email');
  });

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."email" is null

select * from [users] inner join [contacts] on [users].[id] = [contacts].[id] and [contacts].[email] is null

select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`email` is null

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."email" is null

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."email" is null

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."email" is null

select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`email` is null

onNotNull

.onNotNull(column)

Adds a onNotNull clause to the query.

knex
  .select('*')
  .from('users')
  .join('contacts', function () {
    this.on('users.id', '=', 'contacts.id').onNotNull('contacts.email');
  });

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."email" is not null

select * from [users] inner join [contacts] on [users].[id] = [contacts].[id] and [contacts].[email] is not null

select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`email` is not null

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."email" is not null

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."email" is not null

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."email" is not null

select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`email` is not null

onExists

.onExists(builder | callback)

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

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and exists (select * from "accounts" where users.account_id = accounts.id)

select * from [users] inner join [contacts] on [users].[id] = [contacts].[id] and exists (select * from [accounts] where users.account_id = accounts.id)

select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and exists (select * from `accounts` where users.account_id = accounts.id)

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and exists (select * from "accounts" where users.account_id = accounts.id)

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and exists (select * from "accounts" where users.account_id = accounts.id)

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and exists (select * from "accounts" where users.account_id = accounts.id)

select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and exists (select * from `accounts` where users.account_id = accounts.id)

onNotExists

.onNotExists(builder | callback)

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

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and not exists (select * from "accounts" where users.account_id = accounts.id)

select * from [users] inner join [contacts] on [users].[id] = [contacts].[id] and not exists (select * from [accounts] where users.account_id = accounts.id)

select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and not exists (select * from `accounts` where users.account_id = accounts.id)

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and not exists (select * from "accounts" where users.account_id = accounts.id)

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and not exists (select * from "accounts" where users.account_id = accounts.id)

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and not exists (select * from "accounts" where users.account_id = accounts.id)

select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and not exists (select * from `accounts` where users.account_id = accounts.id)

onBetween

.onBetween(column, range)

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

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" between ? and ?

select * from [users] inner join [contacts] on [users].[id] = [contacts].[id] and [contacts].[id] between ? and ?

select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`id` between ? and ?

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" between ? and ?

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" between ? and ?

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" between ? and ?

select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`id` between ? and ?

onNotBetween

.onNotBetween(column, range)

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

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" not between ? and ?

select * from [users] inner join [contacts] on [users].[id] = [contacts].[id] and [contacts].[id] not between ? and ?

select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`id` not between ? and ?

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" not between ? and ?

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" not between ? and ?

select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" not between ? and ?

select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`id` not between ? and ?

onJsonPathEquals

.onJsonPathEquals(column, range)

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(
      // json column in cities
      'country_name',
      // json path to country name in 'country_name' column
      '$.country.name',
      // json column in country
      'description',
      // json field in 'description' column
      '$.name'
    );
  });

select "cities"."name" as "cityName", "country"."name" as "countryName" from "cities" inner join "country" on json_extract_path("country_name", ?, ?) = json_extract_path("description", ?)

select [cities].[name] as [cityName], [country].[name] as [countryName] from [cities] inner join [country] on JSON_VALUE([country_name], ?) = JSON_VALUE([description], ?)

select `cities`.`name` as `cityName`, `country`.`name` as `countryName` from `cities` inner join `country` on json_extract(`country_name`, ?) = json_extract(`description`, ?)

select "cities"."name" "cityName", "country"."name" "countryName" from "cities" inner join "country" on json_value("country_name", ?) = json_value("description", ?)

select "cities"."name" as "cityName", "country"."name" as "countryName" from "cities" inner join "country" on jsonb_path_query_first("country_name", ?) = jsonb_path_query_first("description", ?)

select "cities"."name" as "cityName", "country"."name" as "countryName" from "cities" inner join "country" on json_extract_path_text("country_name", ?) = json_extract_path_text("description", ?)

select `cities`.`name` as `cityName`, `country`.`name` as `countryName` from `cities` inner join `country` on json_extract(`country_name`, ?) = json_extract(`description`, ?)

ClearClauses

clear

.clear(statement)

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

select * from "users"

select * from [users]

select * from `users`

select * from "users"

select * from "users"

select * from "users"

select * from `users`

clearSelect

.clearSelect()

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

knex.select('email', 'name').from('users').clearSelect();

select * from "users"

select * from [users]

select * from `users`

select * from "users"

select * from "users"

select * from "users"

select * from `users`

clearWhere

.clearWhere()

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

knex.select('email', 'name').from('users').where('id', 1).clearWhere();

select "email", "name" from "users"

select [email], [name] from [users]

select `email`, `name` from `users`

select "email", "name" from "users"

select "email", "name" from "users"

select "email", "name" from "users"

select `email`, `name` from `users`

clearGroup

.clearGroup()

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

knex.select().from('users').groupBy('id').clearGroup();

select * from "users"

select * from [users]

select * from `users`

select * from "users"

select * from "users"

select * from "users"

select * from `users`

clearOrder

.clearOrder()

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

knex.select().from('users').orderBy('name', 'desc').clearOrder();

select * from "users"

select * from [users]

select * from `users`

select * from "users"

select * from "users"

select * from "users"

select * from `users`

clearHaving

.clearHaving()

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

knex.select().from('users').having('id', '>', 5).clearHaving();

select * from "users"

select * from [users]

select * from `users`

select * from "users"

select * from "users"

select * from "users"

select * from `users`

clearCounters

.clearCounters()

Clears all increments/decrements clauses from the query.

knex('accounts')
  .where('id', '=', 1)
  .update({ email: '[email protected]' })
  .decrement({
    balance: 50,
  })
  .clearCounters();

update "accounts" set "email" = ? where "id" = ?

update [accounts] set [email] = ? where [id] = ?;select @@rowcount

update `accounts` set `email` = ? where `id` = ?

update "accounts" set "email" = ? where "id" = ?

update "accounts" set "email" = ? where "id" = ?

update "accounts" set "email" = ? where "id" = ?

update `accounts` set `email` = ? where `id` = ?

distinct

.distinct([*columns])

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

// select which eliminates duplicate rows
knex('customers').distinct();

select distinct "first_name", "last_name" from "customers"

-- ----

select distinct * from "customers"

select distinct [first_name], [last_name] from [customers]

-- ----

select distinct * from [customers]

select distinct `first_name`, `last_name` from `customers`

-- ----

select distinct * from `customers`

select distinct "first_name", "last_name" from "customers"

-- ----

select distinct * from "customers"

select distinct "first_name", "last_name" from "customers"

-- ----

select distinct * from "customers"

select distinct "first_name", "last_name" from "customers"

-- ----

select distinct * from "customers"

select distinct `first_name`, `last_name` from `customers`

-- ----

select distinct * from `customers`

distinctOn PG only

.distinctOn([*columns])

PostgreSQL only. Adds a distinctOn clause to the query.

knex('users').distinctOn('age');

select distinct on ("age") * from "users"

Error: .distinctOn() is currently only supported on PostgreSQL

Error: .distinctOn() is currently only supported on PostgreSQL

Error: .distinctOn() is currently only supported on PostgreSQL

select distinct on ("age") * from "users"

select distinct on ("age") * from "users"

Error: .distinctOn() is currently only supported on PostgreSQL

groupBy

.groupBy(*names)

Adds a group by clause to the query.

knex('users').groupBy('count');

select * from "users" group by "count"

select * from [users] group by [count]

select * from `users` group by `count`

select * from "users" group by "count"

select * from "users" group by "count"

select * from "users" group by "count"

select * from `users` group by `count`

groupByRaw

.groupByRaw(sql)

Adds a raw group by clause to the query.

knex
  .select('year', knex.raw('SUM(profit)'))
  .from('sales')
  .groupByRaw('year WITH ROLLUP');

select "year", SUM(profit) from "sales" group by year WITH ROLLUP

select [year], SUM(profit) from [sales] group by year WITH ROLLUP

select `year`, SUM(profit) from `sales` group by year WITH ROLLUP

select "year", SUM(profit) from "sales" group by year WITH ROLLUP

select "year", SUM(profit) from "sales" group by year WITH ROLLUP

select "year", SUM(profit) from "sales" group by year WITH ROLLUP

select `year`, SUM(profit) from `sales` group by year WITH ROLLUP

orderBy

.orderBy(column|columns, [direction], [nulls])

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');
knex('users').orderBy('name', 'desc');
knex('users').orderBy('name', 'desc', 'first');

select * from "users" order by "email" asc

-- ----

select * from "users" order by "name" desc

-- ----

select * from "users" order by ("name" is not null) desc

select * from [users] order by [email] asc

-- ----

select * from [users] order by [name] desc

-- ----

select * from [users] order by IIF([name] is null,0,1) desc

select * from `users` order by `email` asc

-- ----

select * from `users` order by `name` desc

-- ----

select * from `users` order by (`name` is not null) desc

select * from "users" order by "email" asc

-- ----

select * from "users" order by "name" desc

-- ----

select * from "users" order by "name" desc nulls first

select * from "users" order by "email" asc

-- ----

select * from "users" order by "name" desc

-- ----

select * from "users" order by "name" desc nulls first

select * from "users" order by "email" asc

-- ----

select * from "users" order by "name" desc

-- ----

select * from "users" order by "name" desc nulls first

select * from `users` order by `email` asc

-- ----

select * from `users` order by `name` desc

-- ----

select * from `users` order by (`name` is not null) desc

Multiple Columns:

knex('users').orderBy(['email', { column: 'age', order: 'desc' }]);
knex('users').orderBy([{ column: 'email' }, { column: 'age', order: 'desc' }]);
knex('users').orderBy([
  { column: 'email' },
  { column: 'age', order: 'desc', nulls: 'last' },
]);

select * from "users" order by "email" asc, "age" desc

-- ----

select * from "users" order by "email" asc, "age" desc

-- ----

select * from "users" order by "email" asc, ("age" is null) desc

select * from [users] order by [email] asc, [age] desc

-- ----

select * from [users] order by [email] asc, [age] desc

-- ----

select * from [users] order by [email] asc, IIF([age] is null,1,0) desc

select * from `users` order by `email` asc, `age` desc

-- ----

select * from `users` order by `email` asc, `age` desc

-- ----

select * from `users` order by `email` asc, (`age` is null) desc

select * from "users" order by "email" asc, "age" desc

-- ----

select * from "users" order by "email" asc, "age" desc

-- ----

select * from "users" order by "email" asc, "age" desc nulls last

select * from "users" order by "email" asc, "age" desc

-- ----

select * from "users" order by "email" asc, "age" desc

-- ----

select * from "users" order by "email" asc, "age" desc nulls last

select * from "users" order by "email" asc, "age" desc

-- ----

select * from "users" order by "email" asc, "age" desc

-- ----

select * from "users" order by "email" asc, "age" desc nulls last

select * from `users` order by `email` asc, `age` desc

-- ----

select * from `users` order by `email` asc, `age` desc

-- ----

select * from `users` order by `email` asc, (`age` is null) desc

orderByRaw

.orderByRaw(sql)

Adds an order by raw clause to the query.

knex.select('*').from('table').orderByRaw('col DESC NULLS LAST');

select * from "table" order by col DESC NULLS LAST

select * from [table] order by col DESC NULLS LAST

select * from `table` order by col DESC NULLS LAST

select * from "table" order by col DESC NULLS LAST

select * from "table" order by col DESC NULLS LAST

select * from "table" order by col DESC NULLS LAST

select * from `table` order by col DESC NULLS LAST

Having Clauses

Important: Knex may throw errors during SQL compilation when the query is unsound. This prevents unexpected data loss or unexpected behavior. "Having" clauses may throw when:

• A "having" clause is present on a "delete" or "truncate" statement

Examples of queries that would throw:

knex('accounts').having('login', '=', 'user').del().toSQL();
knex('logs').having('server', '=', 'dev').truncate().toSQL();

having

.having(column, operator, value)

Adds a having clause to the query.

knex('users')
  .groupBy('count')
  .orderBy('name', 'desc')
  .having('count', '>', 100);

select * from "users" group by "count" having "count" > ? order by "name" desc

select * from [users] group by [count] having [count] > ? order by [name] desc

select * from `users` group by `count` having `count` > ? order by `name` desc

select * from "users" group by "count" having "count" > ? order by "name" desc

select * from "users" group by "count" having "count" > ? order by "name" desc

select * from "users" group by "count" having "count" > ? order by "name" desc

select * from `users` group by `count` having `count` > ? order by `name` desc

havingIn

.havingIn(column, values)

Adds a havingIn clause to the query.

knex.select('*').from('users').havingIn('id', [5, 3, 10, 17]);

select * from "users" having "id" in (?, ?, ?, ?)

select * from [users] having [id] in (?, ?, ?, ?)

select * from `users` having `id` in (?, ?, ?, ?)

select * from "users" having "id" in (?, ?, ?, ?)

select * from "users" having "id" in (?, ?, ?, ?)

select * from "users" having "id" in (?, ?, ?, ?)

select * from `users` having `id` in (?, ?, ?, ?)

havingNotIn

.havingNotIn(column, values)

Adds a havingNotIn clause to the query.

knex.select('*').from('users').havingNotIn('id', [5, 3, 10, 17]);

select * from "users" having "id" not in (?, ?, ?, ?)

select * from [users] having [id] not in (?, ?, ?, ?)

select * from `users` having `id` not in (?, ?, ?, ?)

select * from "users" having "id" not in (?, ?, ?, ?)

select * from "users" having "id" not in (?, ?, ?, ?)

select * from "users" having "id" not in (?, ?, ?, ?)

select * from `users` having `id` not in (?, ?, ?, ?)

havingNull

.havingNull(column)

Adds a havingNull clause to the query.

knex.select('*').from('users').havingNull('email');

select * from "users" having "email" is null

select * from [users] having [email] is null

select * from `users` having `email` is null

select * from "users" having "email" is null

select * from "users" having "email" is null

select * from "users" having "email" is null

select * from `users` having `email` is null

havingNotNull

.havingNotNull(column)

Adds a havingNotNull clause to the query.

knex.select('*').from('users').havingNotNull('email');

select * from "users" having "email" is not null

select * from [users] having [email] is not null

select * from `users` having `email` is not null

select * from "users" having "email" is not null

select * from "users" having "email" is not null

select * from "users" having "email" is not null

select * from `users` having `email` is not null

havingExists

.havingExists(builder | callback)

Adds a havingExists clause to the query.

knex
  .select('*')
  .from('users')
  .havingExists(function () {
    this.select('*')
      .from('accounts')
      .whereRaw('users.account_id = accounts.id');
  });

select * from "users" having exists (select * from "accounts" where users.account_id = accounts.id)

select * from [users] having exists (select * from [accounts] where users.account_id = accounts.id)

select * from `users` having exists (select * from `accounts` where users.account_id = accounts.id)

select * from "users" having exists (select * from "accounts" where users.account_id = accounts.id)

select * from "users" having exists (select * from "accounts" where users.account_id = accounts.id)

select * from "users" having exists (select * from "accounts" where users.account_id = accounts.id)

select * from `users` having exists (select * from `accounts` where users.account_id = accounts.id)

havingNotExists

.havingNotExists(builder | callback)

Adds a havingNotExists clause to the query.

knex
  .select('*')
  .from('users')
  .havingNotExists(function () {
    this.select('*')
      .from('accounts')
      .whereRaw('users.account_id = accounts.id');
  });

select * from "users" having not exists (select * from "accounts" where users.account_id = accounts.id)

select * from [users] having not exists (select * from [accounts] where users.account_id = accounts.id)

select * from `users` having not exists (select * from `accounts` where users.account_id = accounts.id)

select * from "users" having not exists (select * from "accounts" where users.account_id = accounts.id)

select * from "users" having not exists (select * from "accounts" where users.account_id = accounts.id)

select * from "users" having not exists (select * from "accounts" where users.account_id = accounts.id)

select * from `users` having not exists (select * from `accounts` where users.account_id = accounts.id)

havingBetween

.havingBetween(column, range)

Adds a havingBetween clause to the query.

knex.select('*').from('users').havingBetween('id', [5, 10]);

select * from "users" having "id" between ? and ?

select * from [users] having [id] between ? and ?

select * from `users` having `id` between ? and ?

select * from "users" having "id" between ? and ?

select * from "users" having "id" between ? and ?

select * from "users" having "id" between ? and ?

select * from `users` having `id` between ? and ?

havingNotBetween

.havingNotBetween(column, range)

Adds a havingNotBetween clause to the query.

knex.select('*').from('users').havingNotBetween('id', [5, 10]);

select * from "users" having "id" not between ? and ?

select * from [users] having [id] not between ? and ?

select * from `users` having `id` not between ? and ?

select * from "users" having "id" not between ? and ?

select * from "users" having "id" not between ? and ?

select * from "users" having "id" not between ? and ?

select * from `users` having `id` not between ? and ?

havingRaw

.havingRaw(sql, [bindings])

Adds a havingRaw clause to the query.

knex('users')
  .groupBy('count')
  .orderBy('name', 'desc')
  .havingRaw('count > ?', [100]);

select * from "users" group by "count" having count > ? order by "name" desc

select * from [users] group by [count] having count > ? order by [name] desc

select * from `users` group by `count` having count > ? order by `name` desc

select * from "users" group by "count" having count > ? order by "name" desc

select * from "users" group by "count" having count > ? order by "name" desc

select * from "users" group by "count" having count > ? order by "name" desc

select * from `users` group by `count` having count > ? order by `name` desc