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:

1. 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.

2. 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.

3. You use some kind of tool or script to execute or roll back 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.

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

Additional information

Adding migrate as a module tool dependency

The migrate tool itself is written in Go, which means that — in theory — you could use the go get -tool command to add migrate as a tool dependency to your go.mod file, instead of installing it as a binary like we have done.

But we haven’t done this for two reasons:

1. At the time of writing, it’s not possible to actually run migrate as a tool dependency due to the fact that the go tool command doesn’t currently support build tags. There’s an open issue about this here.

2. The migrate codebase has a ton of child dependencies. If you add it to your go.mod as a tool dependency, and you also want to vendor dependencies (which we will do later in the book), it will bloat your repository by around 280MB.