Right now, you are probably running a SQL query manually to create or alter a table, or using a database management software like phpMyAdmin, MysqlWorkbench, DBeaver, etc.
It might be working fine while you are developing your software locally, but once you host your website and your database online, you might quickly run into an issue when pushing new updates: you don’t know if your local and remote database schemas are synchronized and if you have done all the necessary updates to the remote database. Worst, you might forget to update your production database when updating your production code, which could lead to the whole website being offline, or even data corruption. Another very painful example is when you are working with multiple people: how to ensure that every teammate has the same database schema in local?
Fear not, for I have the perfect solution to any of those situations, and even more: database migrations. They are essentially a version control system like Git, but made for databases, in order to solve the problems mentioned above.
The benefits of using database migrations
Before showing an example of migration, let me present you what are the principles behind migrations, which hopefully will also explicit their benefits. So, in 5 points, database migrations are:
- Incremental, by only applying the new changes needed to your database. The tool will track which changes have already been applied in previous deployments, and will only run the new ones.
- Versionable, since they are defined in code, you can upload the migration files on Git, and you can choose which version of the database schema will be used easily.
- Reversible, by allowing you to define a rollback strategy, which will undo all the changes applied by each migration.
- Easily applied to multiple servers, by leveraging all the other points, which allows you to automatically have the same database schema in local, pre-production and production, or for multiple developers,
- Potentially database-agnostic, so able to run on multiple types of databases, by using non-SQL migration files for example.
Migrations really are one of those rare things that do not have any downsides, and you should use them for ANY project which uses a database. They make managing your database way easier and also more robust. Not to mention that they are often defined in code rather than in SQL, which makes it really painless to write (CREATE TABLE queries being usually a bit hard to write).
So what do they look like?
Most programming languages, and especially web frameworks, will have libraries allowing you to run migrations, but they all are different.
The common point between all of them is that you will usually have multiple files, each of them representing one type of change to your schema, for example creating a new table, or adding a column to an existing one. In this article I will show two different examples, one using Liquibase, a library with bindings for Java, and another one presenting the migrations in the PHP framework Laravel.
Migrations with Liquibase
Liquibase is a great tool to run migrations in Java. It supports most of the popular database engines, and it is really easy to use, flexible, but still powerful.
Liquibase allows you to use multiple formats to define your migrations, and I will present 2 different ones here, which are JSON and SQL (the other two formats are XML and YAML). For the purpose of the example, I will create a users table which will have an auto-increment ID as primary key, and a name and email.
First the SQL version:
--liquibase formatted sql
--changeset antoine:1-users-create
CREATE TABLE IF NOT EXISTS `users` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NULL,
`email` VARCHAR(255) NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB
And the JSON equivalent, much longer:
{
"databaseChangeLog": [
{
"changeSet": {
"id": "1-users-create",
"author": "antoine",
"changes": [
{
"createTable": {
"columns": [
{
"column": {
"autoIncrement": true,
"constraints": {
"primaryKey": true
},
"name": "id",
"type": "INT"
}
},
{
"column": {
"constraints": {
"nullable": false
},
"name": "name",
"type": "VARCHAR(45)"
}
},
{
"column": {
"constraints": {
"nullable": false
},
"name": "email",
"type": "VARCHAR(255)"
}
}
],
"tableName": "users"
}
}
]
}
}
]
}
Even though the JSON schema has benefits (it is database agnostic!), I usually use the SQL format, because it is faster to write and easier to read. I think adding support for defining migrations directly in Java would be a huge step forward for Liquibase.
Once your migrations have been written, you can either run them from the command line provided by Liquibase, or write a dozen of lines of Java code to run them automatically on the start of your application (my favorite method, which really automates migrations when you deploy them to local / pre-production / production).
Migrations with Laravel
Laravel is my go-to framework when I have to develop a website outside of my work, and it provides a great database migrations system.
First, they provide you with a command to create your migration files:
php artisan make:migration create_users_table --create=users
This will create the following scaffolding file:
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateUsersTable extends Migration
{
public function up()
{
Schema::create('users', function (Blueprint $table) {
});
}
}
And then, in only 3 lines of code, you will be able to define which fields you need, in the up method:
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('email');
});
}
Once your migrations are written, you will run them using the very simple command php artisan migrate.
Laravel migrations have a big advantage in that they are extremely easy to write and to read. I left it out of this introduction, but they provide a lot of pre-made functions (to create audit columns for your tables for example), and other useful features that make they overall way easier to write than any other migration system that I have used.
Note that the method to run migrations is called up(). As you can imagine, there is also a down() method that I left out here. As I mentioned in the list of benefits, migrations are reversible. This means that most migrations frameworks will support a way to rollback each migration, but I left it out for sakes of simplicity. You can look at the documentation of the library that you will end up using, but a simple example for Laravel, would just be to drop the table created: Schema::drop('users');.
Conclusion
I hope this article has been useful for you, and especially has made you realize that not only migrations remove a lot of pain from your development process, they are also very easy to put in place in most cases.
My last word of advice is that unless you have a very good reason to not use migrations (spoiler: you don’t have one), you should definitely invest some time in getting familiar with the different libraries and frameworks offered in your favorite language, and implement them in all your new (and existing) projects. I literally never modify my schema by hand, I always create a new migration file.