Do I have to write the reverse operation for migration rollback?

Question

I use knex.js and it's good query builder for PostgreSQL. I haven't found any docs to explain how to do the migration rollback in a right way.

For now, I just write the reverse migrate operation in the down function to do the migration rollback. Is this a correct way?

import * as Knex from 'knex';

exports.up = async (knex: Knex): Promise<any> => {
  await knex.schema.raw(`
    ALTER TABLE IF EXISTS "GOOGLE_CHANNEL" 
    ADD COLUMN IF NOT EXISTS google_channel_ad_group_cpc_bid INTEGER NOT NULL DEFAULT 0;
  `);

  await knex.schema.raw(`
    UPDATE "GOOGLE_CHANNEL" as gc
    SET
      google_channel_ad_group_cpc_bid = 7
    FROM "CAMPAIGN_TEMPLATE" as ct
    WHERE ct.campaign_channel_id = gc.campaign_channel_id;
  `);
};

exports.down = async (knex: Knex): Promise<any> => {
  // TODO: migration rollback
  await knex.schema.raw(``);
};

I have two concerns:

1. If there are a lot of SQL statements in up function, I have to do write a lot of SQL statements in down function too in order to rollback the migration.

2. Why doesn't knex.js do the migration rollback without writing the reverse operation for us? I mean, knex.js can take a snapshot or record a savepoint of the database.

Answer 1

Yes, to rollback you use the down function of a migration script. When you run knex migrate:rollback the down function will run. Knex has meta tables in the database that are used to figure out what migrations that have run or not.

For example:

exports.up = function (knex, Promise) {
  return knex.schema
    .createTable('role', function (table) {
      table.increments('role_id').primary();
      table.string('title').notNullable().unique();
      table.string('description');
      table.integer('level').notNullable(),
    })
    .createTable('user_account', function (table) {
      table.increments('user_id').primary();
      table.integer('role_id').references('role_id').inTable('role').notNullable();
      table.string('username').notNullable().unique();
      table.string('passwordHashed').notNullable();
      table.string('email', 50).notNullable().unique();
    });
};

exports.down = function (knex, Promise) {
  return knex.schema
    .dropTable('user_account')
    .dropTable('role');
};

Here I create two tables in the up function. The user_account has a foreign key constraint, and links with the role table, which means I have to drop the user_account table before the role table in the down function.

In your case, you use a update statement. In the down function you have to either make a new update with a hard-coded value (the old one before the migration), or make sure you store the old value in a history table.

As for your concerns:

1. Yes, if you add a lot of stuff, you also have to add a lot of code to reverse whatever you are doing. However, you can skip making the down scripts, but then you won't be able to rollback. Some (many?) choose to only go forward and never rollback. If they have to fix something they don't rollback but make a new migration script with the fix.

   I would recommend you to create the down functions in the beginning. You can consider skipping making them when the time is right. People who don't make down functions usually have to test their migrations more thoroughly in a test or staging environment before deploying to production. This is to make sure it works, because they can't rollback after all.

2. I can't really answer for the Knex creators here. However, what you are describing as a potential solution is basically a backup of the database before a migration is done. After all, a migration does more than just change the layout of the tables, etc. A migration script will typically add or remove new rows as well. You can use the backup approach, but you have to take the backups yourself.

   Knex is a fairly simple query builder. If you want the migration scripts to be written for you, you might want to go for a full-blown OR mapper.