Setting Sequelize migrations in running project

Almost every backend project is baked with a database. In most cases, this will be a relational database (eg. MariaDB, or PostgreSQL). To handle its structure it’s good to have a tool which will support migrations. Migration is a set of instructions which allows applying (and undo when required) changes to the database. I find it to be one of the most critical aspects of an application. Without migrations, one is forced to rely on manual updates hoping that everything will work.

I had a chance to work on an application which didn’t have any migrations. This wasn’t anything good. The database was quite big, the structure complicated. I was going to make some changes in it and yet I had no way to push them with code. I decided to add migrations to this app.

Quite oddly this project was using Sequelize which is a powerful tool having support for database migrations. Sequelize allows migrating database in two different ways. The first one, sync(), is somewhat automated and uses definitions of models to apply them to a database. I don’t like it and frankly, consider it unreliable. The second requires sequelize-cli. I’ll focus on the latter.

Side-note: in this post, I’m going to assume that you have configured Sequelize (as I had) and the only missing thing is migrations. It’s possible to use Sequelize without the CLI. If your project is not using it make sure that it has .sequelizerc file with valid paths to the config file and migrations directory.

sequelize-cli allows changing database structure using migration files. Migration is a JS file which exports two functions: up() used to apply new changes and down() used to revert them.

'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('playlists', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      topic_id: {
        type: Sequelize.INTEGER,
        onDelete: 'CASCADE',
        references: {
          model: 'topics',
          key: 'id'
        }
      },
      title: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      active: {
        type: Sequelize.BOOLEAN,
        defaultValue: false
      },
      featured: {
        type: Sequelize.BOOLEAN,
        defaultValue: false
      },
      created_at: {
        type: Sequelize.DATE
      },
      updated_at: {
        type: Sequelize.DATE
      }
    });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('playlists');
  }
};

In a project with complicated database structure writing migration from scratch might be impossible. It’s like writing tests for existing code. You can hope that you’ve covered all cases. That’s why I decided to use a different approach: use the database dump as initial migration.

In the previous example, the migration was using Sequelize API to define the structure for the playlists table. Here I’m going to do load the SQL file, split it to single queries and then execute each of them.

Make the dump and store it somewhere in the application (I’d suggest placing it where other migrations will be). It’s important that the dump contains only the structure of the database and statements are single-lined:

CREATE TABLE playlists (    id integer NOT NULL,    title character varying(255) NOT NULL,    active boolean DEFAULT false,    featured boolean DEFAULT false,    created_at timestamp with time zone NOT NULL,    updated_at timestamp with time zone NOT NULL,    topic_id integer);
CREATE SEQUENCE playlists_id_seq    START WITH 1    INCREMENT BY 1    NO MINVALUE    NO MAXVALUE    CACHE 1;
ALTER TABLE ONLY playlists ALTER COLUMN id SET DEFAULT nextval('playlists_id_seq'::regclass);

Create the migration:

sequelize migration:generate --name initial_structure

Load the SQL file and execute queries:

'use strict';

const fs = require('fs');
const path = require('path');

module.exports = {
  up: async (queryInterface, Sequelize) => {

    // this migration can be run only on fresh database
    // if it contains any tables, skip it
    try {
      await queryInterface.describeTable('playlists');
      return;
    } catch (e) {
    }

    const sql = fs.readFileSync(path.resolve(__dirname, './structure.sql'), 'utf8')
      .split(/\r?\n/);

    for (let query of sql) {
      await queryInterface.sequelize.query(query);
    }
  },
}

The only thing missing here is down() method. One could argue if it’s really necessary. If you want to define it you basically need to remove created tables in reversed order.

module.exports = {
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('playlists', { cascade: true });

    await queryInterface.sequelize.query('DROP SEQUENCE IF EXISTS playlists_id_seq CASCADE');
  }
}

At this point, when and if everything works, you should make sure that structure.sql won’t be altered. Any future changes in a database should be defined in a separate migration file.

You should be able to find automated tools which can generate migrations based on created models. Why I didn’t want to use them? It’s a matter of… trust. Whenever application doesn’t have migrations I assume that the models may not be kept up-to-date. The database is it’s very own definition. If you have access to a database which is used in production (or at least staging) make use of it.

I hope that you won’t need this. Ever.