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

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

Knex is available for use under the MIT software license.

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

Thanks to all of the great contributions to the project.

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

Checkout the tarn.js library for more information.

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

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

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

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

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

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

Usage with TypeScript

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

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

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

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

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

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

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

Caveat with type inference and mutable fluent APIs

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Usage with TypeScript

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Usage with TypeScript

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

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

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

Add a "with" clause to the query. "With" clauses are supported by PostgreSQL, Oracle, SQLite3 and MSSQL.

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

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

Indentical to the with method except "recursive" is appended to "with" to make self-referential CTEs possible.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Adds a onIn clause to the query.

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

Adds a onNotIn clause to the query.

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

Adds a onNull clause to the query.

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

Adds a onNotNull clause to the query.

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

Adds a onExists clause to the query.

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

Adds a onNotExists clause to the query.

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

Adds a onBetween clause to the query.

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

Adds a onNotBetween clause to the query.

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

Clears all select clauses from the query, excluding subqueries.

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

Clears all where clauses from the query, excluding subqueries.

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

Clears all order clauses from the query, excluding subqueries.

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

Clears all having clauses from the query, excluding subqueries.

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

Clears all increments/decrements clauses from the query.

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

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

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

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

Adds a group by clause to the query.

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

Adds a raw group by clause to the query.

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

Adds an order by clause to the query. column can be string, or list mixed with string and object.

Single Column:

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

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

Multiple Columns:

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

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

Adds an order by raw clause to the query.

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

Adds a having clause to the query.

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

Adds a havingIn clause to the query.

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

Adds a havingNotIn clause to the query.

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

Adds a havingNull clause to the query.

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

Adds a havingNotNull clause to the query.

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

Adds a havingExists clause to the query.

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

Adds a havingNotExists clause to the query.

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

Adds a havingBetween clause to the query.

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

Adds a havingNotBetween clause to the query.

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

Adds a havingRaw clause to the query.

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

Adds an offset clause to the query.

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

Adds a limit clause to the query.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Utilized by PostgreSQL, MSSQL, and Oracle databases, the returning method specifies which column should be returned by the insert and update methods. Passed column parameter may be a string or an array of strings. When passed in a string, makes the SQL result be reported as an array of values from the specified column. When passed in an array of strings, makes 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 [1]
knex('books')
  .returning('id')
  .insert({title: 'Slaughterhouse Five'})
Outputs:
insert into `books` (`title`) values ('Slaughterhouse Five')

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Usage with TypeScript

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Truncates the current table.

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

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

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

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

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

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

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.

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

Returns an object with the column info about the current table, or an individual column if one is passed, returning an object with the following keys:* defaultValue: the default value for the column* type: the column type* maxLength: the max length set for the column* nullable: whether the column may be null

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

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

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

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

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

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

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

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

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

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

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

It allows to add custom function the the Query Builder.

Example:

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

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

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

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

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

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

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

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

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

Note that Amazon Redshift does not support savepoints in transactions.

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

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

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

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

Renames a table from a current tableName to another.

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

Drops a table, specified by tableName.

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

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

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

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

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

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

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

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

Run an arbitrary sql query in the schema builder chain.

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

Allows configuring a context to be passed to the wrapIdentifier hook. The context can be any kind of value and will be passed to wrapIdentifier without modification.

knex.schema.queryContext({ foo: 'bar' })
  .table('users', function (table) {
    table.string('first_name');
    table.string('last_name');
  })

The context configured will be passed to wrapIdentifier for each identifier that needs to be formatted, including the table and column names. However, a different context can be set for the column names via table.queryContext.

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

Drops a column, specified by the column's name

Drops multiple columns, taking a variable number of column names.

Renames a column from one name to another.

Adds an auto incrementing column. In PostgreSQL this is a serial; in Amazon Redshift an integer identity(1,1). This will be used as the primary key for the table. Also available is a bigIncrements if you wish to add a bigint incrementing number (in PostgreSQL bigserial).

// create table 'users' with a primary key using 'increments()'
knex.schema.createTable('users', function (table) {
  table.increments('userId');
  table.string('name');
});

// reference the 'users' primary key in new table 'posts'
knex.schema.createTable('posts', function (table) {
  table.integer('author').unsigned().notNullable();
  table.string('title', 30);
  table.string('content');

  table.foreign('author').references('userId').inTable('users');
});

Adds an integer column.

In MySQL or PostgreSQL, adds a bigint column, otherwise adds a normal integer. Note that bigint data is returned as a string in queries because JavaScript may be unable to parse them without loss of precision.

Adds a text column, with optional textType for MySql text datatype preference. textType may be mediumtext or longtext, otherwise defaults to text.

Adds a string column, with optional length defaulting to 255.

Adds a float column, with optional precision (defaults to 8) and scale (defaults to 2).

Adds a decimal column, with optional precision (defaults to 8) and scale (defaults to 2). Specifying NULL as precision creates a decimal column that can store numbers of any precision and scale. (Only supported for Oracle, SQLite, Postgres)

Adds a boolean column.

Adds a date column.

Adds a datetime column. By default PostgreSQL creates column with timezone (timestamptz type). This behaviour can be overriden by passing the useTz option (which is by default true for PostgreSQL). MySQL and MSSQL do not have useTz option.

A precision option may be passed:

table.datetime('some_time', { precision: 6 }).defaultTo(knex.fn.now(6))

Adds a time column, with optional precision for MySQL. Not supported on Amazon Redshift.

In MySQL a precision option may be passed:

table.time('some_time', { precision: 6 })

Adds a timestamp column. By default PostgreSQL creates column with timezone (timestamptz type) and MSSQL does not (datetime2). This behaviour can be overriden by passing the useTz option (which is by default false for MSSQL and true for PostgreSQL). MySQL does not have useTz option.

table.timestamp('created_at').defaultTo(knex.fn.now());

In PostgreSQL and MySQL a precision option may be passed:

table.timestamp('created_at', { precision: 6 }).defaultTo(knex.fn.now(6));

In PostgreSQL and MSSQL a timezone option may be passed:

table.timestamp('created_at', { useTz: true });

Adds created_at and updated_at columns on the database, setting each to datetime types. When true is passed as the first argument a timestamp type is used instead. Both colums default to being not null and using the current timestamp when true is passed as the second argument. Note that on MySQL the .timestamps() only have seconds precision, to get better precision use the .datetime or .timestamp methods directly with precision.

Drops the columns created_at and updated_at from the table, which can be created via timestamps.

Adds a binary column, with optional length argument for MySQL.

Adds a enum column, (aliased to enu, as enum is a reserved word in JavaScript). Implemented as unchecked varchar(255) on Amazon Redshift. Note that the second argument is an array of values. Example:

table.enu('column', ['value1', 'value2'])

For Postgres, an additional options argument can be provided to specify whether or not to use Postgres's native TYPE:

table.enu('column', ['value1', 'value2'], { useNative: true, enumName: 'foo_type' })

It will use the values provided to generate the appropriate TYPE. Example:

CREATE TYPE "foo_type" AS ENUM ('value1', 'value2');

To use an existing native type across columns, specify 'existingType' in the options (this assumes the type has already been created):

Note: Since the enum values aren't utilized for a native && existing type, the type being passed in for values is immaterial.

table.enu('column', null, { useNative: true, existingType: true, enumName: 'foo_type' })

If you want to use existing enums from a schema, different from the schema of your current table, specify 'schemaName' in the options:

table.enu('column', null, { useNative: true, existingType: true, enumName: 'foo_type', schemaName: 'public' })

Adds a json column, using the built-in json type in PostgreSQL, MySQL and SQLite, defaulting to a text column in older versions or in unsupported databases.

For PostgreSQL, due to incompatibility between native array and json types, when setting an array (or a value that could be an array) as the value of a json or jsonb column, you should use JSON.stringify() to convert your value to a string prior to passing it to the query builder, e.g.

knex.table('users')
  .where({id: 1})
  .update({json_data: JSON.stringify(mightBeAnArray)});

Adds a jsonb column. Works similar to table.json(), but uses native jsonb type if possible.

Adds a uuid column - this uses the built-in uuid type in PostgreSQL, and falling back to a char(36) in other databases.

Sets the comment for a table.

Sets the engine for the database table, only available within a createTable call, and only applicable to MySQL.

Sets the charset for the database table, only available within a createTable call, and only applicable to MySQL.

Sets the collation for the database table, only available within a createTable call, and only applicable to MySQL.

Sets the tables that this table inherits, only available within a createTable call, and only applicable to PostgreSQL.

Sets a specific type for the column creation, if you'd like to add a column type that isn't supported here.

Adds an index to a table over the given columns. A default index name using the columns is used unless indexName is specified. The indexType can be optionally specified for PostgreSQL and MySQL. Amazon Redshift does not allow creating an index.

Drops an index from a table. A default index name using the columns is used unless indexName is specified (in which case columns is ignored). Amazon Redshift does not allow creating an index.

Adds an unique index to a table over the given columns. A default index name using the columns is used unless indexName is specified.

knex.schema.alterTable('users', function(t) {
  t.unique('email')
})
knex.schema.alterTable('job', function(t) {
  t.unique(['account_id', 'program_id'])
})

Adds a foreign key constraint to a table for an existing column using table.foreign(column).references(column) or multiple columns using table.foreign(columns).references(columns).inTable(table). A default key name using the columns is used unless foreignKeyName is specified. You can also chain onDelete() and/or onUpdate() to set the reference option (RESTRICT, CASCADE, SET NULL, NO ACTION) for the operation. You can also chain withKeyName() to override default key name that is generated from table and column names (result is identical to specifying second parameter to function foreign()). Note that using foreign() is the same as column.references(column) but it works for existing columns.

knex.schema.table('users', function (table) {
  table.integer('user_id').unsigned()
  table.foreign('user_id').references('Items.user_id_in_items')
})

Drops a foreign key constraint from a table. A default foreign key name using the columns is used unless foreignKeyName is specified (in which case columns is ignored).

Drops a unique key constraint from a table. A default unique key name using the columns is used unless indexName is specified (in which case columns is ignored).

Drops the primary key constraint on a table. Defaults to tablename_pkey unless constraintName is specified.

Allows configuring a context to be passed to the wrapIdentifier hook for formatting table builder identifiers. The context can be any kind of value and will be passed to wrapIdentifier without modification.

knex.schema.table('users', function (table) {
  table.queryContext({ foo: 'bar' });
  table.string('first_name');
  table.string('last_name');
})

This method also enables overwriting the context configured for a schema builder instance via schema.queryContext:

knex.schema.queryContext('schema context')
  .table('users', function (table) {
    table.queryContext('table context');
    table.string('first_name');
    table.string('last_name');
})

Note that it's also possible to overwrite the table builder context for any column in the table definition:

knex.schema.queryContext('schema context')
  .table('users', function (table) {
    table.queryContext('table context');
    table.string('first_name').queryContext('first_name context');
    table.string('last_name').queryContext('last_name context');
})

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

Marks the column as an alter / modify, instead of the default add. Note: This only works in .alterTable() and is not supported by SQlite or Amazon Redshift. Alter is not done incrementally over older column type so if you like to add notNull and keep the old default value, the alter statement must contain both .notNull().defaultTo(1).alter(). If one just tries to add .notNull().alter() the old default value will be dropped.

Specifies a field as an index. If an indexName is specified, it is used in place of the standard index naming convention of tableName_columnName. The indexType can be optionally specified for PostgreSQL and MySQL. No-op if this is chained off of a field that cannot be indexed.

When called on a single column it will set that column as the primary key for a table. If you need to create a composite primary key, call it on a table with an array of column names instead. Constraint name defaults to tablename_pkey unless constraintName is specified. On Amazon Redshift, all columns included in a primary key must be not nullable.

Sets the column as unique. On Amazon Redshift, this constraint is not enforced, but it is used by the query planner.

Sets the "column" that the current column references as a foreign key. "column" can either be "." syntax, or just the column name followed up with a call to inTable to specify the table.

Sets the "table" where the foreign key column is located after calling column.references.

Sets the SQL command to be run "onDelete".

Sets the SQL command to be run "onUpdate".

Sets the default value for the column on an insert.

Specifies an integer as unsigned. No-op if this is chained off of a non-integer field.

Adds a not null on the current column being created.

Default on column creation, this explicitly sets a field to be nullable.

Sets the column to be inserted on the first position, only used in MySQL alter tables.

Sets the column to be inserted after another, only used in MySQL alter tables.

Sets the comment for a column.

Sets the collation for a column (only works in MySQL). Here is a list of all available collations: https://dev.mysql.com/doc/refman/5.5/en/charset-charsets.html

The batchInsert utility will insert a batch of rows wrapped inside a transaction (which is automatically created unless explicitly given a transaction using transacting), at a given chunkSize.

It's primarily designed to be used when you have thousands of rows to insert into a table.

By default, the chunkSize is set to 1000.

BatchInsert also allows for returning values and supplying transactions using transacting.

Coerces the current query builder chain into a promise state, accepting the resolve and reject handlers as specified by the Promises/A+ spec. As stated in the spec, more than one call to the then method for the current query chain will resolve with the same value, in the order they were called; the query will not be executed multiple times.

knex.select('*')
  .from('users')
  .where({name: 'Tim'})
  .then(function(rows) {
    return knex.insert({user_id: rows[0].id, name: 'Test'}, 'id').into('accounts');
  })
  .then(function(id) {
    console.log('Inserted Account ' + id);
  })
  .catch(function(error) { console.error(error); });

Coerces the current query builder into a promise state, catching any error thrown by the query, the same as calling .then(null, onRejected).

return knex.insert({id: 1, name: 'Test'}, 'id')
  .into('accounts')
  .catch(function(error) {
    console.error(error);
  }).then(function() {
    return knex.select('*')
      .from('accounts')
      .where('id', 1);
  }).then(function(rows) {
    console.log(rows[0]);
  })
  .catch(function(error) {
    console.error(error);
  });

Shorthand for calling .then(function() { return value }).

// Without return:
knex.insert(values).into('users')
  .then(function() {
    return {inserted: true};
  });

knex.insert(values).into('users').return({inserted: true});

If you'd prefer a callback interface over promises, the asCallback function accepts a standard node style callback for executing the query chain. Note that as with the then method, subsequent calls to the same query chain will return the same result.

knex.select('name').from('users')
  .where('id', '>', 20)
  .andWhere('id', '<', 200)
  .limit(10)
  .offset(x)
  .asCallback(function(err, rows) {
    if (err) return console.error(err);
    knex.select('id').from('nicknames')
      .whereIn('nickname', _.pluck(rows, 'name'))
      .asCallback(function(err, rows) {
        if (err) return console.error(err);
        console.log(rows);
      });
  });

If called with a callback, the callback is passed the stream and a promise is returned. Otherwise, the readable stream is returned.

// Retrieve the stream:
var stream = knex.select('*').from('users').stream();
stream.pipe(writableStream);

// With options:
var stream = knex.select('*').from('users').stream({highWaterMark: 5});
stream.pipe(writableStream);

// Use as a promise:
var stream = knex.select('*').from('users')
  .where(knex.raw('id = ?', [1]))
  .stream(function(stream) {
    stream.pipe(writableStream);
  })
  .then(function() { // ... })
  .catch(function(e) { console.error(e); });

Pipe a stream for the current query to a writableStream.

var stream = knex.select('*').from('users').pipe(writableStream);

A query event is fired just before a query takes place, providing data about the query, including the connection's __knexUid / __knexTxId properties and any other information about the query as described in toSQL. Useful for logging all queries throughout your application.

knex.select('*')
  .from('users')
  .on('query', function(data) {
    app.log(data);
  })
  .then(function() {
    // ...
  });

A query-error event is fired when an error occurs when running a query, providing the error object and data about the query, including the connection's __knexUid / __knexTxId properties and any other information about the query as described in toSQL. Useful for logging all query errors throughout your application.

knex.select(['NonExistentColumn'])
  .from('users')
  .on('query-error', function(error, obj) {
    app.log(error);
  })
  .then(function() { // ... })
  .catch(function(error) {
    // Same error object as the query-error event provides.
  });

A query-response event is fired when a successful query has been run, providing the response of the query and data about the query, including the connection's __knexUid / __knexTxId properties and any other information about the query as described in toSQL, and finally the query builder used for the query.

knex.select('*')
  .from('users')
  .on('query-response', function(response, obj, builder) {
    // ...
  })
  .then(function(response) {
    // Same response as the emitted event
  })
  .catch(function(error) { });

A start event is fired right before a query-builder is compiled. Note: While this event can be used to alter a builders state prior to compilation it is not to be recommended. Future goals include ways of doing this in a different manner such as hooks.

knex.select('*')
  .from('users')
  .on('start', function(builder) {
    builder
    .where('IsPrivate', 0)
  })
  .then(function(Rows) {
    //Only contains Rows where IsPrivate = 0
  })
  .catch(function(error) { });

Returns an array of query strings filled out with the correct values based on bindings, etc. Useful for debugging, but should not be used to create queries for running them against DB.

var toStringQuery = knex.select('*').from('users').where('id', 1).toString();

// Outputs: console.log(toStringQuery); 
// select * from "users" where "id" = 1

Returns an array of query strings filled out with the correct values based on bindings, etc. Useful for debugging and building queries for running them manually with DB driver. .toSQL().toNative() outputs object with sql string and bindings in a dialects format in the same way that knex internally sends them to underlying DB driver.

knex.select('*').from('users')
  .where(knex.raw('id = ?', [1]))
  .toSQL()
// Outputs:
// {
//   bindings: [1],
//   method: 'select',
//   sql: 'select * from "users" where id = ?',
//   options: undefined,
//   toNative: function () {}
// }

knex.select('*').from('users')
  .where(knex.raw('id = ?', [1]))
  .toSQL().toNative()
// Outputs for postgresql dialect:
// {
//   bindings: [1],
//   sql: 'select * from "users" where id = $1',
// }

The migration CLI accepts the following general command-line options. You can view help text and additional options for each command using --help. E.g. knex migrate:latestVersion --help.

If no seeds.directory is defined, files are created in ./seeds. Note that the seed directory needs to be a relative path. Absolute paths are not supported (nor is it good practice).

To run seed files, execute:

knex.migrate is the class utilized by the knex migrations cli.

Each method takes an optional config object, which may specify the following properties:

Creates a new migration, with the name of the migration being added.

Runs all migrations that have not yet been run.

Rolls back the latest migration group. If the all parameter is truthy, all applied migrations will be rolled back instead of just the last batch. The default value for this parameter is false.

Runs the specified (by config.name parameter) or the next chronological migration that has not yet be run.

Will undo the specified (by config.name parameter) or the last migration that was run.

Retrieves and returns the current migration version, as a promise. If there aren't any migrations run yet, returns "none" as the value for the currentVersion.

Will return list of completed and pending migrations

knex.seed is the class utilized by the knex seed CLI.

Each method takes an optional config object, which may specify the following properties:

Creates a new seed file, with the name of the seed file being added.

Runs all seed files for the current environment.

How do I help contribute?
Glad you ask! Pull requests, or feature requests, though not always implemented, are a great way to help make Knex even better than it is now. If you're looking for something specific to help out with, there's a number of unit tests that aren't implemented yet, the library could never have too many of those. If you want to submit a fix or feature, take a look at the Contributing readme in the Github and go ahead and open a ticket.

How do I debug?
Knex is beginning to make use of the debug module internally, so you can set the DEBUG environment variable to knex:* to see all debugging, or select individual namespaces DEBUG=knex:query,knex:tx to constrain a bit.

If you pass {debug: true} as one of the options in your initialize settings, you can see all of the query calls being made. Sometimes you need to dive a bit further into the various calls and see what all is going on behind the scenes. I'd recommend node-inspector, which allows you to debug code with debugger statements like you would in the browser.

At the start of your application code will catch any errors not otherwise caught in the normal promise chain handlers, which is very helpful in debugging.

How do I run the test suite?
The test suite looks for an environment variable called KNEX_TEST for the path to the database configuration. If you run the following command:

replacing with the path to your config file, and the config file is valid, the test suite should run properly.

My tests are failing because slow DB connection and short test timeouts! How to extend test timeouts?
Sometimes, e.g. when running CI on travis, test suite's default timeout of 5 seconds might be too short. In such cases an alternative test timeout value in milliseconds can be specified using the KNEX_TEST_TIMEOUT environment variable.

Can I use Knex outside of Node.js
Yes. While the WebSQL spec is deprecated, there is still an adapter that provides support. You will need to use a build tool like browserify or webpack for a browser build.

I found something broken with Amazon Redshift! Can you help?
Because there is no testing platform available for Amazon Redshift, be aware that it is included as a dialect but is unsupported. With that said, please file an issue if something is found to be broken that is not noted in the documentation, and we will do our best.

• Return more information about empty updates #3597

• Fix colors in debug logs #3592

• Use more efficient algorithm for generating internal ids #3595 #3596
• Use Buffer.alloc() instead of deprecated constructor #3574

• Fix debug logger messing up queries with % #3566
• Make logger methods mutually consistent #3567

• Add missing methods to client type #3565
• Fix queryContext function defintion #3562
• Fix QueryBuilder.extend this type #3526 #3528

• Remove bluebird.using #3552

• MSSQL, MySQL: Add connection string qs to connection params #3547

• Oracle: Fix issue retrieving BLOB from database #3545
• PostgreSQL: Timeout for postgresql use cancel instead of terminate #3518
• Make sure CLI works for namespaced knex packages #2539

• Lift up dialect specific methods in the CreateTableBuilder #3532
• Add client property to QueryBuilder type #3541
• Support 'only' option #3551

• Add support for distinct on for postgres #3513

• Make sqlite3 hasColumn case insensitive #3435

• Fix PoolConfig typing #3505
• Expand SeedsConfig types #3531
• Make the default type parameters of QueryBuilder less strict #3520
• Fix regression in older version of node when Promise#finally was not available #3507

• Declare drivers as optional peerDependencies #3081
• Dynamic connection configuration resolution #3497

• Wrap subQuery with parenthesis when it appears as table name #3496
• Fix Oracle error codes #3498

• Add interface for PG Connection object #3372
• Gracefully handle global promise pollution #3502

• orderBy accepts QueryBuilder #3491
• Add validation in .offset() #2908
• disable_migrations_list_validation feature #3448

• Fix oracledb driver v4 support #3480
• Fix some issues around seed and migration generation #3479
• Fix bugs in replacement logic used when dropping columns in SQLite #3476

• Add types to the Migrator interface #3459
• Fix typings of index and dropIndex TableBuilder methods #3486
• Fixes types for Seeder#run #3438

• Execute CI on Node.js 13
• Bluebird: remove usage of return, reflect, fromCallback methods #3483
• Bluebird: remove Bluebird.bind #3477
• Bluebird: use util.promisify instead of Bluebird.promisify #3470
• Bluebird: remove Bluebird.each #3471
• Bluebird: remove Bluebird.map and Bluebird.mapSeries #3474
• Bluebird: replace Bluebird.map with Promise.all #3469
• Update badges #3482