Database migrations and why you should use them

What are migrations?

You’ve probably heard the word migration before. When you migrate something you go through a change. Migrating from x to y simply means to change from x to y.
Database migrations are used to keep databases in sync across numerous servers and clients; a migration is simply a change in the database structure, so you migrate (change) from the old structure to the new.

Imagine we have a database with a users table and in that table, we have a name and an email_address column.

Users Table

Along our project we realise that we need a profile_picture column as well, so we simply access Sequel Pro, and add the column; Simple, right?

SQL for adding a profile_picture column

Now we just send this SQL to everybody and ask them to run it, right?
Well, that approach could work for development, albeit not ideal if you work more than one person on the project, as you’d constantly have to pass back and forth the edited SQL dump or send messages around with “add x column to the users table”, not an ideal solution by far.
We will also run into problems when a project needs to scale and have multiple databases because manually touching databases in production is a big NO-NO.

So what can we do about this?

Firstly, to solve our issue with working multiple people on the same team, we store migrations in our version-control system, alongside any changes that’s made to the source-code, and whenever co-worker #1 pushes his changes, they’re available to everyone else, and all they have to do is run the migration command, and their databases are now in sync, layout wise, but their test-data remains intact.

A migration to add profile_picture to the users table

The two ways to add a column to the database generates the exact same layout, but one of them can be added to the version-control and run automatically, whereas the other one needs to be run manually on every database.

The resulting database

Whenever we push to production, the deployment script will take care of running the migrations, and thus keep the database updated to the latest version, without any opportunity for human mistakes to affect it.
This will also be the case with systems that rely on multiple databases, as they’ll all be kept in sync at the version that matches their codebase.
If a member of the development team would have to manually make the change to every single database, which is both time-consuming and error-prone, chances are great they wouldn’t be exactly identical.

How does this work?

Migrations work because they’re committed to the version-control along with any changes you make to the code. Thus the project at any given commit will always include the exact database layout for that commit.

Migrations also keeps a record of which migrations have already run on that specific database, and will only run new ones, in the order they are created; So if you’re afraid of data-loss because it’ll rerun all migrations, fret not, as this is something the migrations will take care of, and only run new migrations.

Migrations can effectively increase the collaboration in a team by removing the need to pass around SQL files with changes, as it all lives in version-control and a single console command can be used to bring a database at any given version, up to the newest.
They also remove any risk involved in adding changes to a database in production, as this is all optimised and tested both in development and staging.
There is no excuse for not using them anymore, there are some great packages out there that you can include in your own packages, and any major framework already include them for you.


Acknowledgements

The images in the article shows screenshots from MySQL Workbench, and the migration package used is Phinx