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

Introduction

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.

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
knex("users")
  .where({
    full_name: "Test User",
    is_boat: true,
  })
  .select("id");

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

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

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

// and many more chainable where options are possible
// docs @ http://knexjs.org/#Builder-wheres

Join Queries

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

// inner join with cb fn and orderby
knex
  .select("*")
  .from("users")
  .join("programs", function () {
    this.on("programs.id", "=", "users.program_id").orOn(
      "programs.owner_id",
      "=",
      "users.id"
    );
  })
  .orderBy("users.full_name", "asc")
  .limit(10);

// inner join with multiple join conditions
knex
  .select("*")
  .from("users")
  .join("programs", function () {
    this.on(function () {
      this.on("programs.id", "=", "users.program_id");
      this.orOn("programs.owner_id", "=", "users.id");
    });
  });

// leftjoin
knex
  .select("*")
  .from("users")
  .leftJoin("programs", "users.id", "programs.user_id");

//rightJoin
knex
  .select("*")
  .from("users")
  .rightJoin("programs", "users.id", "programs.user_id");

// rightOuterJoin, fullOuterJoin, crossJoin is also can be used in same way
// docs @ http://knexjs.org/#Builder-join

Insert, Update and Delete Queries

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

// multi row insert
knex("users").insert([
  { full_name: "Test User1", email: "test1@google.com" },
  { full_name: "Test User2", email: "test2@google.com" },
  { full_name: "Test User3", email: "test3@google.com" },
]);

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

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

// count rows
knex("programs").count("active");
// like this you can user .min, .max, .sum etc

Transactions

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 @ http://knexjs.org/#Schema and http://knexjs.org/#Migrations