SQL Database Migrations in Go

Migration Strategy

It’s likely your Go microservice will have it’s own database. As business and product requirements evolve, inevitably so will your database schema. These evolutions to your schema can be safely managed with a robust migration strategy.

In the past I’ve successfully used the fantastic sql-migrate tool alongside pg_dump to manage database migrations for Go microservices. These tools support some key principles which make for a dependable approach to schema migration.

Principle #1: Create directional migrations

If you can apply a migration to transform your schema from state A to state B (i.e. migrate up), you should also be able to undo that change by applying a migration from state B to A (i.e. migrate down).

In sql-migrate, this can be achieved simply with special comments:

-- +migrate Up
-- SQL in section 'Up' is executed when this migration is applied
CREATE TABLE people (id int);


-- +migrate Down
-- SQL section 'Down' is executed when this migration is rolled back
DROP TABLE people;

Principle #2: Support atomic changes to the schema

The change to your schema should complete successfully in its entirety, or it should not happen at all. Bad things can happen when a migration is only partially applied.

The trick here is to wrap your migrations in a transaction. Fortunately, sql-migrate will do this for you.

In the rare case where you need to use a SQL command that cannot be nested in a transaction, there is the special notransaction syntax:

-- +migrate Up notransaction
CREATE UNIQUE INDEX people_unique_id_idx CONCURRENTLY ON people (id);

Finally, it is important that migrations are always applied in the same order. When creating a new migration, it should always be applied after any existing migrations. The sql-migrate tool will run migrations in alphabatical order, so the easiest way to do this is to prefix the filename of your migrations with the timestamp at which they were created.

Principle #3: Separate the migration and the schema definition

The migration, by definition, transforms the old to the new. For example:

Migration #1:

CREATE TABLE foo (
    id SERIAL PRIMARY KEY,
    bar TEXT
);

Migration #2:

ALTER TABLE foo ADD COLUMN baz TEXT;

However, we can no longer rely on the CREATE TABLE statement to tell us all we need to know about foo. We would also have to look at all the ALTER TABLE statements that proceed it. This is a shame - it’s handy to have a single source of truth for your database schema.

To address this, I typically create a SQL dump of the database schema (schema.sql) every time a migration gets applied using pg_dump. Now schema.sql will tell us all we need to know about foo:

CREATE TABLE foo (
    id SERIAL PRIMARY KEY,
    bar TEXT,
    baz TEXT
);

What’s more, we can simply execute schema.sql if we ever need to spin up a new database schema from scratch, e.g. when running integration tests.

Principle #4: Relate source code to schema

Your code expects the database schema to look a certain way - that is to say that your source code and schema are tightly coupled. Therefore it is important to always be able to relate your source code version with your schema version.

The solution here is simple: track your database migrations and schema dump in version control with your source code, and only commit the migration when your code is aligned.

As an aside, if you’re following these project structure guidelines, it’s best to keep your migrations in the internal folder, as they are private to your service. I typically keep them in internal/db/migrations, and the schema dump in internal/db/schema.sql.

Principle #5: Store migration history as data

It is important to know which migrations have been applied to your database. It makes sense to store this information in the database itself. Fortunately, sql-migrate does this automatically for you, using a table called migrations in the public schema by default (this can be changed). Indeed, this is how the tool knows which migration to apply next.

Conclusion

This post has largely focussed on schema migration. Database migration is a broad topic, and these principles may or may not fit your specific requirements. I’d love to hear about your own principles and experiences - hit me up on Twitter!