Running Migrations with Knex

Knex can take a lot of the grunt work out of working with SQL databases in NodeJS. On example is using Knex' migration functionality to set up and modify your table setup.

Set up a knexfile

First step to getting started is to globally install knex.

npm install -g knex

Next inside your root directory, run:

knex init

This will set up a knexfile.js which will hang on to the configuration information for setting up your SQL db. In this case, I'm going to wire it up to use MySQL.

// Update with your config settings.

module.exports = {

  development: {
    client: 'mysql',
    connection: {
      host: '127.0.0.1',
      user: 'root',
      password: '',
      database: '<YOUR TEST DB NAME>',
      charset: 'utf8'
    }
  },

  staging: {
    ...
  },

  production: {
    ...
  }

};

In the knexfile you have the option of altering your configurations depending on your environment. When running your migrations, you have the option of passing a --env flag in command line to specify which environment you want to use. ie:

knex migrate:latest --env production

For this example, I'll just write in an example using a local MySQL database. The default knexfile is a good reference, and you can check out more about configuring the connection in the Knex docs.

Creating a Migration

After setting up your knexfile.js to incorporate your preferred SQL flavor, pop open your terminal and point it to your project's root directory.

In there, run:

knex migrate:make setup

This will create a migrations directory and place a a migration file inside of it.

Open up that file and add something like the following:

exports.up = function(knex, Promise) {  
  return Promise.all([
    knex.schema.createTable('users', function(table){
      table.string('username');
      table.string('password');
      table.timestamps();
    })
  ])
};

exports.down = function(knex, Promise) {  
  return Promise.all([
    knex.schema.dropTable('users')
  ])
};

Each migration is expecting two functions on its API, up and down. up is called when the migration is applied, and down is called on a migration rollback.

In those functions you can use any of Knex' Schema Functions.

To make it easier to run rollbacks, your down function should 'undo' your up function. In this case, because we are creating a table in up, we need to remove that table in down.

To apply your new migration, in the terminal, run:

knex migrate:latest

Updated Your Tables

To make a change to your tables, create another migration.

knex migrate:make step1

In the new migration file, you can make changes to your table, again making use of any of Knex' schema functions.

exports.up = function(knex, Promise) {  
  return Promise.all([
    knex.schema.table('users', function(table){
      table.string('twitter');
    })
  ])
};

exports.down = function(knex, Promise) {  
  return Promise.all([
    knex.schema.table('users', function(table){
      table.dropColumn('twitter');
    })
  ])
};

Here because we add a new column in up, we'll remove it in down.

Rollback

To remove the changes we've made so far, in the terminal run:

knex migrate:rollback

All the migrations you've made will be rolled back.

Using Knex' migrate functionality, creating and modifying your table schemas is a lot easier. So unless you really dig writing out longhand SQL statements, Knex will make your life a lot easier.

Menu