Kostas Bariotis

 

Goodvidio Backend Product Engineer

 
  • Developer
  • Blogger
  • Podcaster
 

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

Thank you, awesome people!

 

Databases as Code

By Kostas Bariotis

Databases as Code

A database is a vital part of every complex application. Databases change very often and contain data that can even disintegrate our whole application if they get out of sync. Changing a live database, whether it's related to schema or data, can be hard. Modern apps, where Agile methodologies along with processes such as Continuous Integration apply, have raised the bar of complexity so maintaining a database separately from the application's business logic can be quite painful. Fortunately, we have Database Migrations, a technique to help us keep our database changes under control.

  • 1,012
Loading comments...

More from Kostas Bariotis