Knex.js is a powerful SQL query builder for Node.js that makes interacting with relational databases easier.

Knex.js helps us work with databases more efficiently and keeps our code cleaner and more modular.

It offers a simple and flexible interface for building SQL queries, performing migrations, and managing our databases efficiently. Knex.js is compatible with a wide variety of database management systems (including PostgreSQL, MySQL, SQLite3, and Oracle).

How to Install Knex.js

To start using Knex.js, we first need to have Node.js and npm installed on our system. Once we have these requirements, we can install Knex.js and its client for the database we want to use.

Install Knex.js

We can add Knex.js to our project using npm. Run the following command in our project directory:

Install the Database Client

We need to install the client for the database we are going to use. For example, one of the following:

Initialize Knex

We initialize Knex in our project to create a configuration file. Run the following command:

This will create a knexfile.js file in the root of our project, where we can define the configuration for our different databases.

Configuration

First, we need to configure Knex.js in our project. For example, we can create a configuration file, for instance, db.js, with the following basic configuration:

const knex = require('knex');
const knexfile = require('./knexfile');

// We choose the configuration environment (for example, 'development')
const environment = process.env.NODE_ENV || 'development';
const config = knexfile[environment];

// We initialize Knex with the configuration
const db = knex(config);

module.exports = db;

Then, in the rest of the scripts, we only need to import this file to have the database configuration available.

Query Building

Knex.js provides a JavaScript interface for building SQL queries. Here are some examples of how to use Knex.js to perform basic operations:

Select data

const db = require('./db');

db('users')
  .select('*')
  .where('age', '>', 18)
  .then(users => {
	console.log(users);
  })
  .catch(err => {
	console.error(err);
  });

Insert data

const db = require('./db');

db('users')
  .insert({ name: 'John Doe', age: 30 })
  .then(() => {
	console.log('User added');
  })
  .catch(err => {
	console.error(err);
  });

Update data

const db = require('./db');

db('users')
  .where('id', 1)
  .update({ age: 31 })
  .then(() => {
	console.log('User updated');
  })
  .catch(err => {
	console.error(err);
  });

Delete data

const db = require('./db');

db('users')
  .where('id', 1)
  .del()
  .then(() => {
	console.log('User deleted');
  })
  .catch(err => {
	console.error(err);
  });

Migrations

Knex.js makes it easy to manage changes to the database schema through migrations. To create a new migration, we use:

This will create a migration file in the migrations directory. We edit this file to define the schema changes:

exports.up = function(knex) {
  return knex.schema.createTable('users', function(table) {
    table.increments('id');
    table.string('name');
    table.integer('age');
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('users');
};

We apply the migration with:

Seeds

To insert initial data, we can use seeds. We create a new seed file with:

Then, we define the data in the seed file:

exports.seed = function(knex) {
  // Deletes ALL existing entries
  return knex('users').del()
    .then(function () {
      // Inserts seed entries
      return knex('users').insert([
        { name: 'Alice', age: 25 },
        { name: 'Bob', age: 30 }
      ]);
    });
};

We run the seed with: