Knex.js Tutorial | A Complete Guide

In this titorial, you will learn about knex.js which is a SQL query builder which supports most of the SQL databases such as Mysql, Postgresql, sqlite etc. This Knex.js Tutorial will be beginner friendly with code examples so all users can benefit most from it.

Article Contents


Knex.js is a very popular Node.js SQL query builder with supports both callback and promise based coding styles, transaction with save points support for all major SQL databases with common api interface for all queries.

Many of the the ORM are built on top of it such as bookshelf.js, objection.js etc. Source code is available on Github under MIT license.

Knex.js initiation options

list of all the available options

  • client – used to specify to db type (mysql, pg, sqlite etc)
  • connection – this can be a DB connection string and object containing db host, port, pasword, user, db name config
  • debug – boolean to show debug logs
  • pool: { min: 0, max: 5 } – to pool db connections
  • pool.afterCreate – callback fn after connection is created
  • acquireConnectionTimeout – connection acquire timeout in ms
  • migrations – object containing migrations related config such as table name
const { PG_HOST, PG_USER, PG_PASS, PG_DB } = process.env;

const knex = require("knex")({
  client: "pg",
  connection: {
    host: PG_HOST,
    user: PG_USER,
    password: PG_PASS,
    database: PG_DB,
  pool: {
    min: 0,
    max: 5,
    afterCreate: function (conn, done) {
      conn.query('SET timezone="UTC";', function (err) {
        if (err) {
          done(err, conn);
        } else {
          conn.query("SELECT set_limit(0.01);", function (err) {
            done(err, conn);
  debug: true,
  acquireConnectionTimeout: 10000,
  migrations: {
    tableName: "migrations",

Installation with DB specific drivers

as we already mentioned Knex.js supports multiple databases, below will see how to install knex.js with database specific node modules.

See also  Get List of all files in a directory in Node.js

Mysql with knex.js

# install knex and mysql packages using npm

npm install knex --save

# mysql
npm install mysql --save
# or
npm install mysql2 --save

same packages can be used with MariaDB also.

creating an knexjs instance with mysql

const knex = require('knex')({
  client: 'mysql',
  connection: {
    host : MYSQL_HOST,
    user : MYSQL_USER,
    password : MYSQL_PASSWORD,
    database : MYSQL_DB_NAME,
    port: MYSQL_PORT || 3306

// Note: here we are getting the Mysql credentials from process.env

Now we can use this instance to run all kinds of queries, which we will discuss below.

Postgresql with knex.js

Let’s install knex and pg client node modules

npm install knex --save
# and
npm install pg --save

creating knex.js instance with postgresql client.

const knex = require('knex')({
  client: 'pg',
  connection: {
    host : PG_HOST,
    user : PG_USER,
    password : PG_PASSWORD,
    database : PG_DB_NAME,
    port: PG_PORT || 5432

// we can also connect using connection string also

const knex = = require('knex')({
  client: 'pg',
  connection: PG_CONN_STR,

// it supported for all databases, so you use whichever one you prefer.

Sqlite with Knex.js

installing knex with sqlite client

npm install knex --save
# with
npm install sqlite3 --save

creating knex client with sqlite file

const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: SQLITE_FILE_PATH

Knex also supports mssql, oracledb, amazon redshift etc

initiating them is also same as above examples.

Knex.js Query Builder

Now we will see about SQL select, insert, update and delete queries with and without transcations and batch inserts etc

Select Queries

will start with some basic examples of sql select queries using knex.js

// select one user from users table where age is greater than 18
knex("users").where("age", ">", 18).first();

// filter users by multiple where columns
    full_name: "Test User",
    is_boat: true,

// select subquery
const usersSubquery = knex("users")
  .where("age", ">", 18)
  .andWhere("is_deleted", false)

knex("programs").where("id", "in", usersSubquery);

// pagination with offset and limit"*").from("orders").offset(0).limit(50);

// and many more chainable where options are possible
// docs @

Join Queries

// inner join
  .join("orders", "", "=", "orders.user_id")
  .where("users.is_deleted", false);

// inner join with cb fn and orderby
  .join("programs", function () {
    this.on("", "=", "users.program_id").orOn(
  .orderBy("users.full_name", "asc")

// inner join with multiple join conditions
  .join("programs", function () {
    this.on(function () {
      this.on("", "=", "users.program_id");
      this.orOn("programs.owner_id", "=", "");

// leftjoin
  .leftJoin("programs", "", "programs.user_id");

  .rightJoin("programs", "", "programs.user_id");

// rightOuterJoin, fullOuterJoin, crossJoin is also can be used in same way
// docs @

Insert, Update and Delete Queries

// insert
knex("users").insert({ full_name: "Test User", email: "" });

// multi row insert
  { full_name: "Test User1", email: "" },
  { full_name: "Test User2", email: "" },
  { full_name: "Test User3", email: "" },

// update
knex("orders").where("id", 7823).update({
  status: "archived",

// delete
knex("orders").where("is_deleted", true).del();

// count rows
// like this you can user .min, .max, .sum etc


const trx = await knex.transaction();

try {
  const ids = await trx.insert({ name: "test role 1" }, "id").into("roles");

  const programs = [
    { title: "test program 1", role_id: ids[0] },
    { title: "test program 2", role_id: ids[0] },
    { title: "test program 3", role_id: ids[0] },

  await knex("programs").insert(programs).transacting(trx);

  await trx.commit();
} catch (error) {
  await trx.rollback();

Knex.js also provides methods and CLI for migrations and Schema handling, please check the knex.js docs @ and

See also  Access parent of Iframe using JavaScript (same-domain or cross-domain)

Leave a Comment