Bringing Rails-Like Migrations to JavaScript with Knex.js

The one thing you can count on with any software project is that requirements are going to change. The severity of these changes varies, but a change in requirements can necessitate major changes to the overall application structure and potentially alter the database schema.

Have no fear! This is what database migrations are for, aren’t they? If you’re working in a Rails app, you can quickly generate a migration file, specifying the columns that you want to create/drop and the way the data that is currently in the database should change to adapt to the new schema.

This is great, but many developers don’t have the pleasure of writing simple and concise Rails migrations. They’re forced to write the migrations themselves or use some bloated, overly-complex tool that promises to take care of everything, yet tends to break more often than actually help. 

Am I suggesting that other developers are doomed to a life of writing their own database migrations or fighting their tools to get minor changes implemented? Of course not, there is still hope.

I recently worked on a side project with some of my other coworkers writing a React/Redux app that communicated with a PostgreSQL database. A couple of us already had experience with Rails and decided to look for something that would allow us to generate the glorious Rails-style migrations that we knew and loved.

With a little intuition and a lot of help from Google, we stumbled upon Knex.js. Knex.js is an SQL query builder that is meant to be flexible, portable, and fun to use.

Using Knex.js

Knex.js can be added to your project using npm. From there, you can easily create a new migration like so:

./node_modules/knex/bin/cli.js migrate:make add_columns_to_table

• ./node_modules/knex/bin/cli.js – The location of the Knex.js CLI after installing via npm
• migrate:make – Specifying the option to make a new migration via the Knex.js CLI
• add_columns_to_table – The name of the migration being created

Upon running the above command, there should now be a migrations folder within the root of your project that contains a single file. The generated contents of that file will consist of two empty functions:

exports.up = function(knex, Promise) {

};
exports.down = function(knex, Promise) {

};
  

At this point, it is as simple as using the tools provided by Knex.js to build SQL queries that will alter the database schema, referencing the table(s), specifying the column types, and giving them names. Don’t forget that migrations go up and down, so it is important to write how the migration should be reversed in case you need to revert to an older version. Here is an example:

exports.up = function(knex, Promise) {
  return Promise.all([
    knex.schema.table('my_table', function(table) {
      table.string('new_column');
      table.boolean('other_new_column');
    })
  ]);
};
exports.down = function(knex, Promise) {
  return Promise.all([
    knex.schema.table('my_table', function(table) {
      table.dropColumn('new_column');
      table.dropColumn('other_new_column');
    })
  ]);
};
  

Some Considerations

Having the ability to generate database migrations in this side project was quite helpful, but there are a few things that could be improved. My main issue was that Knex.js doesn’t generate migration tests. Those tests are an important part of writing database migrations in Rails since they ensure that they code you’re writing will work as expected without any surprises.

Nonetheless, Knex.js is a great tool, and I will be excited if and when they add support to generate migration tests.

What tools do you rely on for database migrations?