Knex.js is a query builder for Postgres, MySQL and SQLite3, 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, full featured query and schema builders, transaction support, connection pooling and standardized responses between different query clients and dialects.

The project is hosted on GitHub, and the annotated source code is available, 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.

Special thanks to Taylor Otwell and his work on the Laravel Query Builder, from which much of the code and syntax is derived.

Latest Release: 0.5.13 - Change Log

Current Develop — Travis Badge


All dependencies are specified in package.json file but include lodash, bluebird, and the generic-pool-redux libraries. You then need to install either mysql, pg, or sqlite3 from npm if you wish to use one of these databases with node.js, or create your own client adapter and specify it in knex.initialize.

$ npm install knex

# Then add one of the following:
$ npm install mysql
$ npm install pg
$ npm install sqlite3

Upgrading from 0.4.x

Minor changes from the 0.4.x series, include the fact that aggregate functions are no longer aliased as "aggregate", but rather left as-is, unless explicitly aliased. See this ticket for more info.


Knex.initialize is the initializing function that must be called prior to using Knex, accepting a few parameters. The client parameter is required and determines which client adapter will be used with the library.

var knex = Knex.initialize({
  client: 'mysql',
  connection: {
    host     : '',
    user     : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test',
    charset  : 'utf8'

An initialize should only ever happen once in your application, as it creates a connection pool for the current database, you should use the instance returned from the initialize call throughout your library.

The options are passed directly to the appropriate database client to create the connection. Acceptable charset values may vary between clients. MySQL requires a key from its predefined list (e.g. UTF8_GENERAL_CI) to be passed as the charset in Knex.

You'll need to store this instance created by the initialize somewhere in the application you can reference it. A common pattern to follow is to set the client as a property on the Knex library when your application starts, so you can easily reference it later:

// When the app starts
var Knex  = require('knex');
Knex.knex = Knex.initialize({
  client: 'pg',
  connection: {
    // your connection config

// elsewhere, to use the client:
var knex = require('knex').knex;

knex('table').where('id', 1).select().then(...
Note: When you use the SQLite3 adapter, there is a filename required, not a network connection. For example:
var knex = Knex.initialize({
  client: 'sqlite3',
  connection: {
    filename: "./mydb.sqlite"


The knex.builder is the interface used for building and executing standard SQL queries, such as select, insert, update, delete. The query is run by specifying a tableName, adding additional query parameters, and executing the query with one of the public interface methods.

The Knex query builder starts off by specifying a tableName you wish to query against, which returns a chainable interface, similar to a jQuery chain. You can then call any additional query builder methods needed to construct the query, eventually calling either then or exec, to execute the query with a promise or a callback, respectively.[*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.

  // select "title", "author", "year" from "books"
  Knex('books').select('title', 'author', 'year');

  // select * from "books"

Specifically set the columns to be selected on a select query, taking an array or a list of of column names.

  // select "title", "author", "year" from "books"
  Knex('books').column('title', 'author', 'year').select();

  // or:
  Knex('books').column(['title', 'author', 'year']).select();

from.from([tableName]) Alias: into
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.

There are several helpers for creating dynamic where clauses on queries. Take a look at a few examples to see how these may be mixed and matched to create fluent constraints on the query.

// Basic Uses:

// objects
  first_name: 'Test',
  last_name:  'User'

// key, value
Knex('users').where('id', 1).select('first_name', 'last_name').then(...

// operators: '=', '<', '>', '<=', '>=', 'like', 'not like', 'between', 'ilike'
Knex('users').where('votes', '>', 100).exec(function(err, resp) { ... });

// chained with "andWhere" / "orWhere"
Knex('users').where('votes', '>', 100)
             .andWhere('status', 'active')
             .orWhere('name', 'John')
             .then(function(resp) { ... })

// Even more where types (see list below):

Knex('users').whereBetween('votes', [1, 100]).exec(...

Knex('users').whereIn('id', [1, 2, 3]).then(...

Knex('users').whereNotIn('id', [1, 2, 3]).then(...


Grouped Where Clauses:

// select * from users where name = 'John' or (votes > 100 and title <> 'Admin')
  .where('name', '=', 'John')
  .orWhere(function() {
    this.where('votes', '>', 100).andWhere('title', '<>', 'Admin');
  .then(function() {...

Exists Statements:

  .whereExists(function() {
        .whereRaw('orders.user_id =');

Most of the where clauses may also accept a function as the second argument, to generate a sub-select on the query:

// select author_id, content from comments where author_id in (select id from accounts where type = 'admin')
Knex('comments').whereIn('author_id', function() {'id').from('accounts').where('type', 'admin');

}).select('author_id', 'content').then(...

These different where clauses may be joined together in any number of ways to make valid SQL statements.

Sets a distinct clause on the query.

// select distinct 'first_name' from customers
  .distinct('first_name', 'last_name')

join.join(table, first, operator, second, [type])
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. The last argument is optional and specifies the type of join.

  .join('contacts', '', '=', 'contacts.user_id')
  .join('orders', '', '=', 'orders.user_id', 'outer')
  .select('', '', 'orders.price')
  .then(function() { ... });

For grouped joins, specify a function as the second argument for the join query, and use on and orOn to create joins that are grouped with parentheses.

  .join('contacts', function() {
      this.on('', '=', 'contacts.user_id').orOn(...);
  .exec(function(err, resp) { ... });

Adds a group by clause to the query.

orderBy.orderBy(column, [direction])
Adds an order by clause to the query.

having.having(column, operator, value)
Adds a having clause to the query.

  .orderBy('name', 'desc')
  .having('count', '>', 100)

Adds an offset clause to the query.

Adds a limit clause to the query.


Creates a union query, taking a callback to build the union statement.

Creates a union all query, with the same method signature as the union method.

knex('users').whereNull('last_name').union(function() {'*').from('users').whereNull('first_name');

}).select().then(function() { ... });

insert.insert(data, [returning])
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. Resolves the promise / fulfills the callback with an array containing the first insert id of the inserted model, or an array containing all inserted ids for postgresql.

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

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

Only utilitzed by PostgreSQL databases, the returning method specifies which column should be returned by the insert method.

  // Returns [1]
    .insert({title: 'Slaughterhouse Five'})

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

update.update(data) / .update(key, value)
Creates an update query, taking either a single property / value, or a hash of properties to be updated based on the other query constraints. Resolves the promise / fulfills the callback with the number of affected rows for the query.

  .where('published_date', '<', 2000)
    status: 'archived'

del / delete.del()
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.

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

    .insert({name: 'Old Books'})
    .then(function(row) {

      return Promise.all([
        {title: 'Canterbury Tales'},
        {title: 'Moby Dick'},
        {title: 'Hamlet'}
      ], function(info) {

        info.row_id =;

        // Some validation could take place here.
        return knex('book').transacting(t).insert(info);

    .then(function() {
      t.commit('You saved 3 books');
    }, t.rollback);

}).then(function(resp) {
}, function(err) {

Dynamically added after a transaction is specified, the forUpdate adds a FOR UPDATE in PostgreSQL and MySQL during a select statement.

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.

Performs a count on the specified column.

Gets the minimum value for the specified column.

Gets the maximum value for the specified column.

Retrieve the sum of the values of a given column.

Retrieve the average of the values of a given column.

increment.increment(column, amount)
Increments a column value by the specified amount.

  .where('userid', '=', userid)
  .increment('balance', 10);

decrement.decrement(column, amount)
Decrements a column value by the specified amount.

  .where('userid', '=', userid)
  .decrement('balance', 5);

Truncates the current table.

Turns on debugging for the current query chain.

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

  knex('accounts as a1')
    .join('accounts as a2', function() {
      this.on('', '<>', '');
    }, 'left')
    .select(['', ''])
    .where(Knex.Raw(' = 1'))
    .option({ nestTables: true, rowMode: 'array' })

Builder Interface Methods:

then.then(onFulfilled, onRejected)
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.

If you'd prefer a callback interface over promises, the exec 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.
A passthrough to Bluebird's map implementation with the result set.

reduce.reduce(reducer, [initialValue])
A passthrough to Bluebird's reduce implementation with the result set.

tap.tap(onFulfilled, onRejected)
Similar to the tap functionality in when.js, this will execute side effects on the resolved response, ultimately returning a promise that fulfills with the original value. A thrown error or rejected promise will cause the promise to transition into a rejected state.

// Using only .then()
query.then(function(x) {
    return x;

// Using .tap()

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

Returns an array of query strings filled out with the correct values based on bindings, etc. Useful for debugging.


Transactions are handled by passing a handler function into knex.transaction. The handler function accepts a single argument, the promise for committing or rolling back the transaction. This argument is then passed into any queries which are involved in the current transcaction, working by explicitly passing the .

var Promise = require('bluebird');

knex.transaction(function(t) {

    .insert({name: 'Old Books'})
    .then(function(row) {

      return Promise.all([
        {title: 'Canterbury Tales'},
        {title: 'Moby Dick'},
        {title: 'Hamlet'}
      ], function(info) {

        info.row_id =;

        // Some validation could take place here.
        return knex('book').transacting(t).insert(info);

    .then(t.commit, t.rollback);

}).then(function() {
  console.log('3 new books saved.');
}, function() {
  console.log('Error saving the books.');


createTableknex.schema.createTable(tableName, callback)
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) {
}).then(function () {
  console.log('Users Table is Created!');

renameTableknex.schema.renameTable(from, to)
Renames a table from a current tableName to another.

Drops a table, specified by tableName.

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.string('first_name', 100);
      t.string('last_name', 100);

hasColumnknex.schema.hasColumn(tableName, columnName)
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.

tableknex.schema.table(tableName, callback)
Chooses a database table, and then modifies the table, using the Schema Building functions inside of the callback.

knex.schema.table('users', function (table) {
}).then(function () {
  console.log('Users Table is Updated!');

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

Schema Building:

Drops a column, specified by the column's name

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

renameColumntable.renameColumn(from, to)
Renames a column from one name to another.

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

Adds an integer column.

In MySQL or PostgreSQL, adds a bigint column, otherwise adds a normal integer.

texttable.text(name, [textType])
Adds a text column, with optional textType for MySql text datatype preference.
textType may be mediumtext or longtext, otherwise defaults to text.

stringtable.string(name, [length])
Adds a string column, with optional length defaulting to 255.

floattable.float(column, [precision], [scale])
Adds a float column, with optional precision and scale.

decimaltable.decimal(column, [precision], [scale])
Adds a decimal column, with optional precision and scale.

Adds a boolean column.
Adds a date column.

Adds a dateTime column.

Adds a time column.

Adds a timestamp column.

Adds a created_at and updated_at column on the database, setting these each to dateTime types.

Adds a binary column.

enum / enutable.enu(col, values)
Adds a enum column, (aliased to enu, as enum is a reserved word in javascript).

Adds a json column, using the built-in json type in postgresql, defaulting to a text column in older versions of postgresql or in unsupported databases.

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.

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

Chainable Methods:

The following three methods may be chained on the schema building methods, as modifiers to the column.

Specifies an field as an index. No-op if this is chained off of a field that cannot be indexed.

Sets the field as the primary key for the table. To create a compound primary key, pass an array of column names: table.primary(['column1', 'column2']).

Sets the column as unique.

Sets the "column" that the current column references as a foreign key.

Sets the "table" where the foreign key column is located.

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 after another, only used in MySQL alter tables.

Sets the comment for a column.

knex.schema.createTable('accounts', function() {


Raw Expressions:

Sometimes you may need to use a raw expression in a query. These expressions will be injected into the query as strings, so be careful not to create any SQL injection points! To create a raw expression, you may use the knex.raw function.

  .select(knex.raw('count(*) as user_count, status'))
  .where('status', '<>', 1)

Raw Queries:

The knex.raw may also be used to build a full query and execute it, as a standard query builder query would be executed. The benefit of this is that it uses the connection pool and provides a standard interface for the different client libraries. Note that the response will be whatever the underlying sql library would typically return on a normal query, so you may need to look at the documentation for the base library the queries are executing against to determine how to handle the response.

knex.raw('select * from users where id = 1').then(function(resp) {


knex.migrate is the class utilized by the knex migrations cli. Each method takes an optional config object, which may specify specifies the database, directory, and tableName for the migrations. Check here for more information about the migration CLI tool.

makeknex.migrate.make(name, [config])
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.

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.


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

Knex uses it's own copy of the "bluebird" promise library, you can read up here for more on debugging these promises... but in short, adding:

process.stderr.on('data', function() {

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: $ export KNEX_TEST='/path/to/your/knex_config.js', replacing with the path to your config file, and the config file is valid, the test suite should run with npm test. If you're going to add a test, you may want to follow similar patterns, used in the test suite, setting $ export KNEX_DEV=1 to save the outputs data from the tests into the shared/output.js file.

Can I use Knex outside of Node.js
While there isn't a client adapter yet, it should be possible to run it could be adapted to work with other javascript environments supporting a sqlite3 database, by providing a custom Knex adapter.

Change Log

0.5.13April 2, 2014Diff
Fix regression in array bindings for postgresql (#228).

0.5.12Mar 31, 2014Diff
Add more operators for where clauses, including && (#226).

0.5.11Mar 25, 2014Diff

0.5.10Mar 19, 2014Diff
Add the .exec method to the internal promise shim.

0.5.9Mar 18, 2014Diff
Remove error'ed connections from the connection pool (#206), added support for node-postgres-pure (pg.js) (#200).

0.5.8Feb 27, 2014Diff
Fix for chaining on forUpdate / forShare, adding map & reduce from bluebird.

0.5.7Feb 18, 2014Diff
Fix for a null limit / offset breaking query chain (#182).

0.5.6Feb 5, 2014Diff
Bump bluebird dependency to ~1.0.0, fixing regression in Bluebird 1.0.2 (#176).

0.5.5Jan 28, 2014Diff

0.5.4Jan 7, 2014Diff
Fix for using raw statements in defaultTo schema builder methods (#146).

0.5.3Jan 2, 2014Diff
Fix for incorrectly formed sql when aggregates are used with columns (#144).

0.5.2Dec 18, 2013Diff
Adding passthrough "catch", "finally" to bluebird implementations, use bluebird's "nodeify" internally for exec.

0.5.1Dec 12, 2013Diff

0.5.0Nov 25, 2013DiffDocs

0.4.13Oct 31, 2013Diff
Fix for aggregate methods on toString and clone, (#98).

0.4.12Oct 29, 2013Diff
Fix incorrect values passed to float in MySQL and decimal in PostgreSQL.

0.4.11Oct 15, 2013Diff
Fix potential sql injection vulnerability in orderBy, thanks to @sebgie.

0.4.10Oct 14, 2013Diff

0.4.9Oct 7, 2013Diff

0.4.8Oct 2, 2013Diff
Connections are no longer pushed back into the pool if they never existed to begin with (#85).

0.4.7Sep 27, 2013Diff
The column is now a documented method on the builder api, and takes either an individual column or an array of columns to select.

0.4.6Sep 25, 2013Diff
Standardizing handling of errors for easier debugging, as noted in (#39).

0.4.5Sep 24, 2013Diff
Fix for hasTable always returning true in MySQL (#82), fix where sql queries were duplicated with multiple calls on toSql with the schema builder.

0.4.4Sep 22, 2013Diff
Fix for debug method not properly debugging individual queries.

0.4.3Sep 18, 2013Diff
Fix for underscore not being defined in various grammar files.

0.4.2Sep 17, 2013Diff
Fix for an error being thrown when an initialized ClientBase instance was passed into Knex.initialize. pool.destroy now optionally accepts a callback to notify when it has completed draining and destroying all connections.

0.4.1Sep 16, 2013Diff
Cleanup from the 0.4.0 release, fix a potential exploit in "where" clauses pointed out by Andri Möll, fix for clients not being properly released from the pool #70, fix for where("foo", "<>", null) doing an "IS NULL" statement.

0.4.0Sep 13, 2013DiffDocs
Breaking Changes:

Schema: Added hasColumn, renameColumn, bigInteger, specificType. General: Refactoring the library to support UMD and eventual use on the client. Added options method for adding additional parameters specific to individual database libraries, #64. Bugfixes: Case insensitive where operators (e.g.) LIKE, BETWEEN, NOT LIKE are now supported.

0.2.6Aug 29, 2013DiffDocs
Reject the transaction promise if the transaction "commit" fails, (#50).

0.2.5Aug 25, 2013
Fix error if a callback isn't specified for exec, (#49).

0.2.4Aug 22, 2013
Fix SQLite3 delete not returning affected row count, (#45).

0.2.3Aug 22, 2013
Fix insert with default values in PostgreSQL and SQLite3, (#44).

0.2.2Aug 20, 2013
Allowing Raw queries to be passed as the primary table names.

0.2.1Aug 13, 2013
Fix for an array passed to insert being mutated.

0.2.0Aug 7, 2013
Breaking changes:

Restored basic binding support on Raw queries (#19). Added support for the JSON and UUID datatypes in postgres (#20). Fix enabling enum in postgresql (#21). Added foreign key support (#24). Bugfix for column ordering on insert (#31). Other minor bugfixes, documentation cleanup, & tweaks.

0.1.8July 7, 2013
Somehow missing the != operator. Using _.find rather than _.where in getCommandsByName(#22).

0.1.7June 12, 2013
Ensures unhandled errors in the exec callback interface are re-thrown.

0.1.6June 9, 2013
Renaming beforeCreate to afterCreate. Better handling of errors in the connection pooling.

0.1.5June 9, 2013
Added the ability to specify beforeCreate and beforeDestroy hooks on the initialize's options.pool to perform any necessary database setup/teardown on connections before use (#14). where and having may now accept Knex.Raw instances, for consistency (#15). Added an orHaving method to the builder. The ability to specify bindings on Raw queries has been removed.

0.1.4May 22, 2013
defaultTo now accepts "false" for boolean columns, allows for empty strings as default values.

0.1.3May 18, 2013
Enabling table aliases (#11). Fix for issues with transactions not functioning (#12).

0.1.2May 15, 2013
Bug fixes for groupBy (#7). Mysql using collation, charset config settings in createTable. Added engine on schemaBuilder specifier (#6). Other doc fixes, tests.

0.1.1May 14, 2013
Bug fixes for sub-queries, minor changes to initializing "main" instance, adding "pg" as a valid parameter for the client name in the connection settings.

0.1.0May 13, 2013
Initial Knex release.