An Overview of SQL Migrations
In case you’re not familiar with the idea of SQL migrations, at a very high-level the concept works like this:
For every change that you want to make to your database schema (like creating a table, adding a column, or removing an unused index) you create a pair of migration files. One file is the ‘up’ migration which contains the SQL statements necessary to implement the change, and the other is a ‘down’ migration which contains the SQL statements to reverse (or roll-back) the change.
Each pair of migration files is numbered sequentially, usually
0001, 0002, 0003...or with a Unix timestamp, to indicate the order in which migrations should be applied to a database.You use some kind of tool or script to execute or rollback the SQL statements in the sequential migration files against your database. The tool keeps track of which migrations have already been applied, so that only the necessary SQL statements are actually executed.
Using migrations to manage your database schema, rather than manually executing the SQL statements yourself, has a few benefits:
The database schema (along with its evolution and changes) is completely described by the ‘up’ and ‘down’ SQL migration files. And because these are just regular files containing some SQL statements, they can be included and tracked alongside the rest of your code in a version control system.
It’s possible to replicate the current database schema precisely on another machine by running the necessary ‘up’ migrations. This is a big help when you need to manage and synchronize database schemas in different environments (development, testing, production, etc.).
It’s possible to roll-back database schema changes if necessary by applying the appropriate ‘down’ migrations.
Installing the migrate tool
To manage SQL migrations in this project we’re going to use the migrate command-line tool (which itself is written in Go).
Detailed installation instructions for different operating systems can be found here, but on macOS you should be able to install it with the command:
$ brew install golang-migrate
And on Linux and Windows, the easiest method is to download a pre-built binary and move it to a location on your system path. For example, on Linux:
$ cd /tmp $ curl -L https://github.com/golang-migrate/migrate/releases/download/v4.16.2/migrate.linux-amd64.tar.gz | tar xvz $ mv migrate ~/go/bin/
Before you continue, please check that it’s available and working on your machine by trying to execute the migrate binary with the -version flag. It should output the current version number similar to this:
$ migrate -version 4.16.2