I thought I’d write a quick summary around using knex.js for migrations beyond what introduction tutorials show. The official documentation details them pretty well, plus other guides give a nice step-by-step overview on creating migrations from scratch.

One thing I’ve found is that for most guides, there’s not really any guidance on how to manage SQL migrations beyond what’s in the base tutorial. This includes:

Creating tables

Adding columns

Setting null conditions and defaults

Adding foreign key constraints

Indexes

Whenever anything custom is required, a trusty knex.raw() can be used with more complex SQL statements.

Background

The typical knex.js migrations folder structure looks something like:

/db

/migrations

20180404114353_initial.js

20180405123128_add_view.js

The file 20180404114353_initial.js could look something like:

module.exports.up = async function(knex) {

await knex.schema.createTable('products', table => {

table.increments('id').primary();

table.text('name').notNullable();

table.integer('price');

});

}; module.exports.down = async function(knex) {

await knex.schema.dropTableIfExists('products');

};

The next migration 20180405123128_add_view adds a basic view:

module.exports.up = async function(knex) {

await knex.raw(`

CREATE OR REPLACE VIEW "products_grouped" AS (

SELECT

"products".*,

"price" > 100 AS "requires_signoff"

FROM "products"

);

`);

};

module.exports.down = async function(knex) {

await knex.raw('DROP VIEW "products_grouped";');

};

Postgresql will be the SQL dialect of choice for this review, but it should be similarly applicable to other dialects. Similarly this is a node.js framework, so node.js will be the platform used when any code is required.

The problem

Imagine that this view needs to be updated later on with another column. We end up adding a new migration and now looks like:

/db

/migrations

20180404114353_initial.js

20180405123128_add_view.js

20180406132347_update_view.js

The migration file 20180406132347_update_view.js looks like:

module.exports.up = async function(knex) {

await knex.raw('DROP VIEW "products_grouped";');

await knex.raw(`

CREATE OR REPLACE VIEW "products_grouped" AS (

SELECT

"products".*,

"price" > 100 AS "requires_signoff",

EXTRACT(YEAR FROM "created_at")::TEXT AS "year"

FROM "products"

);

`);

};

module.exports.down = async function(knex) {

await knex.raw('DROP VIEW "products_grouped";');

await knex.raw(`

CREATE OR REPLACE VIEW "products_grouped" AS (

SELECT

"products".*,

"price" > 100 AS "requires_signoff"

FROM "products"

);

`);

};

The down() function of new migration has to restore what the view previously was, so we end up copying the same view creation statement into the rollback. When you’re adding functions, views, triggers and constraints this can quickly get out of hand.

Proposal

I’ve found that keeping some discrete definitions immutable works really well. Essentially this means creating versions of each function or trigger such as v1.js , v2.js and so on.

Looking back on the example above, a slightly more maintainable structure could look like:

/db

/migrations

20180404114353_initial.js

20180405123128_add_view.js

20180406132347_update_view.js

/queries

/views

/products_grouped

v1.js

v2.js

As the products_grouped view is updated, a new file v1.js or v2.js is created and is never changed.

v1.js

module.exports.up = `

DROP VIEW IF EXISTS "products_grouped";

CREATE OR REPLACE VIEW "products_grouped" AS (

SELECT

"products".*,

"price" > 100 AS "requires_signoff"

FROM "products"

);

`; module.exports.down = 'DROP VIEW "products_grouped";';

v2.js

module.exports.up = `

DROP VIEW IF EXISTS "products_grouped";

CREATE OR REPLACE VIEW "products_grouped" AS (

SELECT

"products".*,

"price" > 100 AS "requires_signoff",

EXTRACT(YEAR FROM "created_at")::TEXT AS "year"

FROM "products"

);

`; module.exports.down = require('./v1').up;

Note that v1.js has an up() function which can be immediately used as the down function in v2.js . This may not always be possible, but often the up() of the previous version can be used as the base query for rollbacks.

The first migration that creates the view ( 20180405123128_add_view ) will instead then look like:

const v1 = require('../queries/views/products_grouped/v1'); module.exports.up = async function(knex) {

await knex.raw(v1.up);

}; module.exports.down = async function(knex) {

await knex.raw(v1.down);

};

The view update migration () can then be changed accordingly to:

const v2 = require('../queries/views/products_grouped/v2'); module.exports.up = async function(knex) {

await knex.raw(v2.up);

}; module.exports.down = async function(knex) {

await knex.raw(v2.down);

};

Summary

This method of versioning blocks of SQL queries works well for many use cases and aims to follow DRY principles. Older versions of the function can be reviewed easily and the migration path to newer versions is obvious.