Database Migrations in Go

When transitioning to Go from another language that relies heavily on a framework, it can quickly start to feel like Go is lacking. Like it just doesn’t have the power or the tools that you are accustomed to. The end result is the language can feel confusing and clunky.

One example of this is database migration tooling.

If you are coming from a framework like Rails, Django, Flask, Laravel, or really any web framework, you very likely have an idea of what I am talking about. You are accustomed to tools that make it easy to create new tables, to modify existing tables, and to even modify data in your database as your columns change.

For instance, in Ruby on Rails you might alter a users table to add a new receive_newsletter field with the following code:

class AddReceiveNewsletterToUsers < ActiveRecord::Migration
  def up
    change_table :users do |t|
      t.boolean :receive_newsletter, :default => false
    end
    User.update_all ["receive_newsletter = ?", true]
  end

  def down
    remove_column :users, :receive_newsletter
  end
end

After creating the migration, you also expect to be able to run the migration and to roll it back as necessary.

In Go, none of this tooling exists out of the box.

To be fair, none of this tooling exists in Ruby out of the box either. The framework - Ruby on Rails - adds that functionality. And if you really want to use a framework in Go, many offer migration tooling (eg Buffalo offers migration tooling).

If you are here, I am going to assume you are not using a framework in Go, so how exactly is everyone handling migrations if their tooling doesn’t provide a solution?

Option 1: Use a third party library

The first option is probably what you will hear most often - just grab a third party library that handles this for you.

A few examples of these include:

• pressly/goose
• golang-migrate/migrate

In almost all of these cases you write migration files in SQL or Go, then use a binary installed by the tool to run migrations. For instance, goose supports both SQL and Go migrations (though Go migrations may require some additional setup), and you can run migrations with code like:

# run the migrations
goose up

# Output:
OK    001_basics.sql
OK    002_next.sql
OK    003_and_again.go

And there are options for running migrations up to a specific one, rolling back migrations, etc. There are often even commands in the binary for generating a migration file.

Pretty much all of these tools are pretty quick and easy to get started with, and technically you don’t even need to use a tool written in Go if you just write SQL migrations.

The only real downside here is that you are investing time into another library that may or may not fit your needs long term, and at that point you will either need to work around the library’s limitations, or all your time invested into that tool will end up being wasted.

My experience says that these migration tools are flexible enough that they will fit most needs, but it is still worth considering.

Option 2: Write your own library

The second option is probably not for everyone, but I do love discussing it because it shows very clearly how we often make a big deal out of a relatively small problem.

While migrations sound complex at first glance, what most of us need really isn’t that complex. My needs can typically be summed up as:

1. I need a way to run migrations, skipping any that have already run.
2. I need a way to run rollbacks, skipping any migrations that were never run.
3. I need a way to order migrations for both migrating and rolling back.

Most migration tools solve these three by prepending the current date to migration files, then sorting migration files lexicographically and running them in order. Identifiers are created from each file name to make it easier to track which migrations have and have not been run. From there it is a matter of creating a migrations table that tracks those identifiers.

Knowing that this is all we really need for a migration tool, how hard is it to write something that just does the bare minimum that we need?

I was curious, so I wrote a tool I called migrate that does really basic database migrations.

To use the library, you first create a Sqlx instance with an array of SqlxMigration. Rather than sorting these, I use the order provided in the array to determine the order. This leaves ordering up to the end user.

SqlxMigration can be created either via a sql file, or developers can construct them by hand defining a func(tx *sqlx.Tx) error function for migration and for rollback. This makes it pretty easy to do additional work, like converting data after adding a new column.

sqlMigration := migrate.Sqlx{
  Migrations: []migrate.SqlxMigration{
    migrate.SqlxFileMigration("001_init", "migrations/001_init.sql", "migrations/001_init.undo.sql"),
    migrate.SqlxMigration{
      ID: "002_add_currency",
      Migrate: func(tx *sqlx.Tx) error {
        // add currency field, then fill existing entries with "USD"
        return nil
      },
      Rollback: func(tx *sqlx.Tx) error {
        // drop the currency field
        return nil
      },
    },
  },
}

Because my migrations are just functions that need a sqlx.Tx to run, the Migrate function was pretty straight forward. It first makes sure there is a migrations table, then for each migration it checks to see if there is an entry in the table with the ID of each migration. If the entry exists, the migration is skipped. Otherwise it is run and the ID is inserted into the table.

Rollback works pretty similarly, but with the opposite logic. If an entry exists, the rollback is run and the entry is deleted.

While I’m not naive enough to believe this code covers 100% of the cases I might encounter, I have found that migrations concern me a lot less after realizing that they aren’t a massive undertaking, especially if I simply focus on the few features I need to get started.

The next time the lack of a tool is preventing you from moving forward, I highly recommend spending a little time to see how plausible it is to write a simplified version for yourself. Sometimes you will surprise yourself 😀