Database Migrations

Treating databases as another piece of code.

The Problem

• Apps often need certain data to be in place in order to run. Fresh environments may not have those.
• Different environments get out of sync.
• When a developer puts his/her hands in the DB, that change will be lost forever.
• You can’t rollback your app when your data will be out of order.

Database migrations are the management of incremental, reversible changes to our databases.

Types of migrations

• Schema Changes
• Data Changes

Schema Changes

name VARCHAR(20)
owner VARCHAR(20)
species VARCHAR(20)

first_name VARCHAR(20)
last_name VARCHAR(20)
owner VARCHAR(20)
species VARCHAR(20)

Data changes

{
    ...
    is_subscribed: true
    ...
},
{
    ...
    is_subscribed: true
    ...
},
{
    ...
    is_subscribed: true
    ...
}

{
    ...
    is_subscribed: false
    ...
},
{
    ...
    is_subscribed: false
    ...
},
{
    ...
    is_subscribed: false
    ...
}

Write code, automate

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

    queryInterface.createTable(
      'nameOfTheNewTable',
      {
        id: {
          type: Sequelize.INTEGER,
          primaryKey: true,
          autoIncrement: true
        },
        createdAt: {
          type: Sequelize.DATE
        },
        updatedAt: {
          type: Sequelize.DATE
        },
        attr1: Sequelize.STRING
      },
      {
        engine: 'MYISAM', // default: 'InnoDB'
        charset: 'latin1' // default: null
      }
    )
  },
 
  down: function(queryInterface, Sequelize) {

    queryInterface.dropTable('nameOfTheNewTable');
  }
}

Database you said?

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

    request.post({
        url: 'https://some.rest.api/v1/users',
        json: true,
        body: {
            name: 'Kostas Bariotis',
            email: 'kbariotis@goodvid.io'
        }
    }, function(err, resp, body) {
        /* ... */
    });
  },
 
  down: function(queryInterface, Sequelize) {

    request.delete({
        url: 'https://some.rest.api/v1/users',
        json: true,
        body: {
            email: 'kbariotis@goodvid.io'
        }
    }, function(err, resp, body) {
        /* ... */
    });
  }
}

Migrate

$ ./scripts/migrate

$ ./scripts/migrate up 1.0

$ ./scripts/migrate down 0.8

Typical workflow

Application bootstrap

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

    queryInterface.createTable(
      'nameOfTheNewTable',
      {
        ...
      }
    );
    queryInterface.createTable(
      'nameOfAnotherTable',
      {
        ...
      }
    );
  },
 
  down: function(queryInterface, Sequelize) {
    /* ... */
  }
}

Application gets deployed and the database is being created

Requirements changes

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

    queryInterface.addField(
      'nameOfTheNewTable',
      {
        name: Sequelize.STRING
      }
    )
  },
 
  down: function(queryInterface, Sequelize) {

    queryInterface.removeField('nameOfTheNewTable', 'name');
  }
}

Database changes on the next deployment

Production

v1.0

Stage

v1.5

Local Dev Env

v2.0 - Feature #154