Connect to Databases with Node.js and Knex.js

Knex.js is a powerful SQL query builder for Node.js that simplifies interaction with relational databases.

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.

Some of its main features include:

• SQL Query Building: Allows building complex SQL queries using an easy-to-use JavaScript interface.
• Migrations: Facilitates managing changes to the database schema through migrations.
• Seeds: Allows inserting initial data into the database using seed scripts.
• Compatibility: Supports various database management systems, allowing us to switch databases without needing to rewrite our queries.

For more information and to explore the full documentation, we can visit the official Knex.js repository on GitHub.

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. We run the following command in our project directory:

npm install knex

Install the Database Client

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

# for PostgreSQL
npm install pg

# for MySQL
npm install mysql

# for SQLLite3
npm install sqlite3

Initialize Knex

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

npx knex init

This will create a knexfile.js 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 example, 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 will 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 for 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:

npx knex migrate:make migration_name

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

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:

npx knex migrate:latest

Seeds

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

npx knex seed:make seed_name

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:

npx knex seed:run