On Jan 31, 2016, I gave a small lightning talk in the Go developer room at FOSDEM. Here's an annotated version of the slides.

If you don't feel like reading and just want the slides: download them here.

To give a bit of background, I currently work for Ticketmatic, which tries to improve the state of ticketing by building fantastic ticketing software.

The ultimate source of truth in our system is a PostgreSQL database.

Because let’s face it, despite it not being the cool thing anymore, SQL is pretty awesome: strong transactions and consistency, an extremely powerful query language (which we use extensively in our reporting) and rock-solid proven behavior.

But not all of it perfect. Getting the performance right is a big challenge (we have some ways of avoiding the bottlenecks that the DB might pose).

They’re also a bit of an operation handful: unlike NoSQL databases, a real SQL (yesSQL?) database has a strong schema definition. When thing evolve in development, they have to evolve similarly in the production environment.

And that’s what sql-migrate handles: schema migrations. If you’ve used e.g. Rails (ActiveRecord), there won’t be any new concepts here. That’s by design: sql-migrate takes the elements that work and makes sure that those things work.

No magic here, just simple, transparent and rock-solid things that you can rely on. Somewhat like our beloved Go programming language.

So how does sql-migrate work?

Step 1: fire up man’s best friend, Vim, and write your migrations in plain .sql files. There’s two special conventions to adhere to: a delimiter comment that indicates what a block of statements is for. You can write multiple statements and do all the crazy operations you want.

You also need a configuration file that defines the different environments. You can have different configurations for local testing and production environments.

I recommend that you keep your local development environment as similar to production as possible, but your needs might vary.

Once that’s done, you can use the sql-migrate command-line utility to inspect the status of your database migrations.

There’s an “up” command, which applies migrations.

Which does exactly what you might expect: apply the migrations in order.

You can also unapply migrations (down) or do an up+down in one step (redo): very handy during development.

I wouldn’t recommend using a CLI tool to migrate your production environment.

Using the AssetMigrationSource, you can bundle your migrations into your single Go binary. A couple of lines of code ensures that your database is always ready to use.

The project can be found on Github.

Some other highlights:

• Support for a whole range of databases
• There’s clever algorithms in sql-migrate to ensure that migrations are applied chronologically: when multiple people start adding migrations at the same time, sql-migrate will ensure that databases are always migrated in the same order, even if that means undoing some migrations and reapplying them later on (this scenario only really happens during development)

You’re in good company! A quick bit of online research taught me that sql-migrate is used by some rather big open-source projects: there’s the continuous integration server drone.io, the CoreOS identity provider dex and CloudFoundry uses it in one of their components.

Go check it out if you use SQL. I hope sql-migrate solves some of your problems.

Thanks!