How to write database migrations for Go

The title contains Go because the post’s main focus is Golang and the golang-migrate library but the same logic can be applied to any language/library that does step-by-step migrations. The database I use is PostgreSQL but I made sure to indicate PSQL specific features.

How does golang-migrate work?

The golang-migrate can be imported into a project or its migrate binary can be called externally. It can read migration files from remote sources, filesystem or embedded binary data. This offers great flexibility in packaging/containerizing projects. Therefore it’s our library of choice in MoneroPay.

There are up and down migration files for each version. For example 000002_users.down.sql downgrades to database version 000001 from 000002, whereas 000002_users.up.sql upgrades to database version 000002 from 000001.

The first time golang-migrate is run on a database, it will create a table called schema_migration to keep track of the current database version as well as a flag to signal whether if at some point the migration failed or not.

postgres=# select * from schema_migrations;
 version | dirty 
---------+-------
       3 | f
(1 row)

How to write migration files?

There are a few things you need to keep in mind:

moneropay/db/postgres/000001_v2.0.0.up.sql:

BEGIN;
CREATE TABLE IF NOT EXISTS metadata (
	key text UNIQUE NOT NULL,
	value bigint NOT NULL
);
INSERT INTO metadata (key, value) VALUES ('last_height', 0) ON CONFLICT DO NOTHING;
ALTER TABLE IF EXISTS subaddresses DROP CONSTRAINT IF EXISTS subaddresses_address_check;
CREATE TABLE IF NOT EXISTS subaddresses (
	index bigint PRIMARY KEY,
	address character(95) UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS receivers (
	subaddress_index bigint PRIMARY KEY REFERENCES subaddresses ON DELETE CASCADE,
	expected_amount bigint NOT NULL CHECK (expected_amount >= 0),
	description character varying(1024),
	callback_url character varying(2048) NOT NULL,
	created_at timestamp with time zone
);
DROP TABLE IF EXISTS failed_callbacks;
COMMIT;

moneropay/db/postgres/000001_v2.0.0.down.sql:

BEGIN;
ALTER TABLE IF EXISTS subaddresses ADD CONSTRAINT subaddresses_address_check CHECK (LENGTH (address) = 95);
CREATE TABLE IF NOT EXISTS failed_callbacks (
	uid serial PRIMARY KEY,
	subaddress_index bigint REFERENCES subaddresses ON DELETE CASCADE,
	request_body text NOT NULL,
	attempts smallint DEFAULT 1,
	next_retry timestamp with time zone NOT NULL
);
COMMIT;

Normally your first migration file (version 1 or 000001) should create all of your initial tables. In MoneroPay’s case database migrations were implemented at v2.0.0 release, and before that the tables were created elsewhere in the code.

Conditional logic with PL/pgSQL (PostgreSQL only)

There is almost always a better way to deal with things other than using PL/pgSQL, at least in my personal experience. But if you cannot think of another way, you can achieve logic in migration files this way.

BEGIN;
CREATE TABLE IF NOT EXISTS last_block_height (
	height bigint NOT NULL
);
DO $$
BEGIN
    IF EXISTS
        (SELECT 1 FROM information_schema.tables WHERE table_name = 'metadata')
    THEN
        INSERT INTO last_block_height (height) VALUES ((SELECT value FROM metadata WHERE key = 'last_height'));
    ELSE
        INSERT INTO last_block_height (height) VALUES (0);
    END IF;
END;
$$;
DROP TABLE IF EXISTS metadata;
COMMIT;

The above example creates a new table called last_block_height and checks if metadata table exists. If the table exists it selects a row from it and inserts the row’s value into the newly created table. If the table doesn’t exist, it inserts a row with value set to 0. At last it deletes the metadata table. But what if I told you there’s a simpler query that can do all that?

BEGIN;
CREATE TABLE IF NOT EXISTS last_block_height (
	height bigint NOT NULL DEFAULT 0
);
INSERT INTO last_block_height (height) SELECT (SELECT value FROM metadata WHERE key = 'last_height') WHERE NOT EXISTS (SELECT 1 FROM last_block_height);
DROP TABLE IF EXISTS metadata;
COMMIT;

As you can see the conditional part can be rewritten in a single line without using PL/pgSQL. Another reason not to use this language is that you may want to support another database other than PostgreSQL, then you will need to change the way you do migrations to avoid execution of this file. It’s best to avoid features and column/table names that may be reserved in some databases from the beginning. For example, having a table with a column called index is allowed in PostgreSQL, however it is not allowed in CockroachDB. Overall try not to run into the need to have database migrations, but if that happens try not to worry too much.