Use of Knex JS as Node JS Database Migration Tool

Introduction

While developing web app with Node JS no framework provides proper solution to migration, For this purpose Knex JS as Node JS database migration tool seems best approach so far.

Knex.js is a query builder for relational database. It supports databases like MYSQL, Postgres, MSSQL, MariaDB, SQLite3, Oracle and Amazon Redshift.

In this article will introduce some basic concept of Knex JS as Node JS Database Migration Tool.

So I assume we have Node.js basic project is ready and reader has bit of knowledge about Node js.

Installation

To use Knex CLI options its recommended to install knex globally first

npm install knex -g

For this demo purpose we will use MySQL, so we need to install Knex and Node JS MySQL driver:

npm install knex –save
npm install mysql –save

Usage of Knex JS as Migration Tool

Knex.js is using knexfile.js for database connection and migration logic.
Create this file in root of your project using following command :

knex init

It will create knexfile.js with different environments (development, staging, production).

We have used two environments in knexfile.js, which looks like below:

module.exports = {
  development: {
    client: "mysql",
    connection: "mysql://root:123456@localhost:3306/knexdb",
    migrations: {
      directory: __dirname + "/knex/migrations",
    },
  },
  production: {
    client: "mysql",
    connection: "mysql://root:[email protected]:3306/knexdb",
  },
};

Let’s summarise options used in knexfile.js:
1. client: defines which type of adapter will be used.
2. connection: describes database connection string.
Connection param syntax look like this : For MYSQL
mysql://user:password@host:port/database
3. migrations: defines folder in which our migration files will be created.
It will default create new folder migrations at root level of project.

Please check more options here.

Migrations

To create new migration file use following command:
knex migrate:make migration_file_nameMigration

file look like this :

exports.up = function(knex, Promise) {
  return knex.schema.createTable("City", function(t) {
    t.increments("id").primary();
 
    t.specificType("cityName").notNullable();
    t.specificType("state").notNullable();
    t.specificType("country").defaultsTo("US");
 
    t.timestamp("createdAt", { useTz: true });
    t.timestamp("updatedAt", { useTz: true });
  });
};

exports.down = function(knex, Promise) {
  return knex.schema.dropTableIfExists("user");
};

Migration file generate two function up and down. Up function is used to create new table or modify existing table. Down function is use for rollback.

Run migration using following command :
knex migrate:latest knex migrate:latest --env production

Rollback migration using following command :
knex migrate:rollback knex migrate:rollback --env production