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
000002_users.up.sql upgrades to database version
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:
- Use transactions (
COMMIT;) if you need multiple queries to migrate up/down to/from a single version. This way if a query fails after
BEGIN, the other queries that ran before the failed query will be reverted. If you don’t use transactions you might end up with dirty database versions.
- You may need to alter, drop or create new tables. Make sure those queries are idempotent.
- You don’t need to repeat queries such as those that create tables if they do not exist in multiple migration files (unless you dropped them in a version before).
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;
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
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
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.