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?

Conversation
  • James Stallings says:

    “…overly-complex tool that promises to take care of everything, yet tends to break more often than actually help. ”

    — pretty accurately describes my experiences with knex.js. It seems to suffer from being new or raw, in spite of having a year or two of age and use on it; it has few or no ‘sane defaults’. I struggled with it endlessly yesterday to get it to even connect to my mariadb instance, and so far this morning I’ve managed to get past that, and now am arguing with it over whether I have a database selected. You can rest assured that I have specified a database after the fashion of the many examples found all over the internet (after all, how hard can it be to add a key/value pair to a JSON object).

    Documentation is quite extensive, but doesn’t seem to cover these problems for those who dont take the time to sit down and examine the source code of every js lib we use in forensic detail.

    From what I’ve been able to deduce from a forensic reading of the source (mind numbing) and the feedback from knex (said feedback of a markedly spartan level of brevity), the message might, in fact, refer to a database other than mine, though I have found no references to preparing such in the three different sets of similar instructions for establishing a database and associated table migrations that I’ve been able to find.

    It’s kind of sad really, this ‘tool’ occupies a position of great power and flexibility, yet by being so inflexible it fails to be at all powerful except in the hands of the blessed, and they aren’t talking about how they overcame/avoided these silly little impediments to developmental progress.

    If you have any ideas how I can move beyond ‘Error: No database selected” when I invoke ‘knex migrate:latest’ in my project root, I’d love hearing from you (yes I did npm install knex –global in order to have access to the knex cli globally.

    Thanks in advance, and as you read this far, thanks for indulging my rant ;)

    Cheers
    James

    • Alex Zurek Alex Zurek says:

      Hello James,

      Thanks for taking the time to share your perspective working with Knex.js. Your rant showcases the “not-so-happy” path of working with this library which is just as important as highlighting the “happy” path I experienced.

      No library is perfect and I’m certain there are many things that can be improved (or are currently being improved) with Knex.js. Unfortunately, I haven’t worked with MariaDB so I’m not entirely sure how much help I can offer.

      I was about to share an issue I found on GitHub about the error you’re receiving but I noticed you commented on the issue before I could reply to this post!

      Thanks,
      Alex

  • Comments are closed.