How to make database changes when your app is in production

Note: For this article, I’m going to assume that you’re using a relational database like MySQL or PostgreSQL. 

When you’re building a new app, you can change your database schema with a lot of freedom. Usually, you’re working with a local database that you have full control over. Forgot a column? Just add one in via your SQL Edtior or CLI. Need to change the datatype? No problem.

Changing your database schema becomes more difficult when you have an application that is being used by people in the real world. This is especially important if you are introducing breaking changes.

I define a “breaking change” as any database change which breaks the current version of your application.

What you don’t want to do

If you’re using an ORM on the server side, you may have the ability to “alter” database tables on the fly. Basically, this means that any model change will automatically alter the database, so that your model and database always stay in sync.

This sounds great in theory, but fails in practice. It causes unexpected changes under the hood. There’s no way to know the underlying SQL query that gets executed. Database changes affect every user on your site so each change should be documented.

What you want to do

Use a migration library. I like using db-migrate for Express apps. If you use Sails, use sails-db-migrate.

A migration library lets you define migrations as you build out your app. It’s best to explain this with a use case.

Let’s say you have a “Users” table that contains the following columns:

• username
• email
• createdAt

Now, your app requires you to add an address column in.  Let’s use migrations to add this in.

First, create a file called database.json, with some database configuration details. More information can be found here:

{ 
  "dev": { 
    "driver": "mysql", 
    "user": "root", 
    "password": "root",
    "host": "localhost", 
    "database": "mydb"
  }
}

Then, let’s create a migration:

db-migrate create add-address-column-to-users

This will create a new directory called migrations/ inside your project directory, and a file called <date>-add-address-column-to-users.js file inside it.

Open it, and make the following changes:

var dbm = global.dbm || require('db-migrate');
var type = dbm.dataType;

exports.up = function(db, callback) {
 db.addColumn('Users', 'address', {type: 'string'}, callback);
};

exports.down = function(db, callback) {
 db.removeColumn('Users', 'address', {type: 'string'}, callback);
};

We’ve used the addColumn() and removeColumn() methods to create a new migration.

Save the file, and then run the following

db-migrate up

This will run all existing migrations (just the one we have created for now) and sync it with the database. Going forward, the database also will have a table called migrations, that will outline all migrations that have been run so far.

Migrations are great because you’re able to keep track of all of your database changes over time. If you ever need to roll back a migration, you can just run:

db-migrate down --count=1

This will roll back the last migration using the rule we defined in exports.down.

Other Benefits of Migrations

If a new team member joins a project, they can just clone a project and run all the migrations to get setup with the latest database schema. No need to share large SQL files.

We went through a really simple migration, but they can be as simple or complicated as you like. Use libraries like promises or async to bundle multiple migrations together. I have used them to create, remove, and add data.

You can learn more about migrations over on the db-migrate documentation. If you use Bedrock, you get migrations built-in for free. Check out Bedrock over at Github.

PS: I learned all this the hard way. Credit to Casen Davis who showed me the power behind migrations 2 years ago.