DB schema updates with knex in Node.js

Run database migration with knex

Feb 06, 2021

About

Knex is a query builder and among other things, it's an awesome tool to run DB migrations to create tables and modify them as you usually do with SQL scripts

"SQL query builder for Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift"

Official web site https://knexjs.org

Setup

Install dependencies

      
yarn add knex
yarn add sqlite3

Init the configuration

      
knex init

It will create the following knexfile.js

      
module.exports = {

  development: {
    client: 'sqlite3',
    connection: {
      filename: './dev.sqlite3'
    },
    debug: true
  },

  staging: {
    client: 'postgresql',
    connection: {
      database: 'my_db',
      user:     'username',
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  },

  production: {
    client: 'postgresql',
    connection: {
      database: 'my_db',
      user:     'username',
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  }

};

Create our first Knex script

      
npx knex migrate:make create_table_users

As a good practice, create small tasks instead of one task with all the schema. This is useful to detect problems. For example avoid create all the init schema in one task, create one task per table and even create another separate task for contrainsts such as foreign keys

Use descriptive names such as "create_table_users". Use also prefix for your actions such as "create_table_..." or "create_column_..."

Result:

migrations/20211005153317_create_table_users.js

Open the file and paste this:

      

exports.up = function(knex) {
    return knex.schema.createTable("users", (tb) => {
        tb.increments("id");
        tb.string("username", 100).notNullable();
        tb.string("email", 100).notNullable();
        tb.string("password", 100).notNullable();
    });
};

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

Save it and run:

      
knex migrate:latest

Or select a different environment

      
knex migrate:latest --env production

Handy commands

      
npx knex migrate:latest # To run all pending migrations
npx knex migrate:up # To run the next pending migration
npx knex migrate:down # To roll back the most recent migration
npx knex migrate:list # To show current state of migrations.