Skip to main content

Postgres Migrations Using Go

Installation​

brew install golang-migrate

Verification​

migrate -version

Usage​

Generate a pair of migration files​

migrate create -seq -ext=.sql -dir=./migrations create_abc_table PATH_TO_YOUR_PROJECT/migrations/000001_create_abc_table.up.sql PATH_TO_YOUR_PROJECT/migrations/000001_create_abc_table.down.sql
  • The -seq flag indicates that we want to use sequential numbering like 0001, 0002, ... for the migration files (instead of a Unix timestamp, which is the default).
  • The -ext flag indicates that we want to give the migration files the extension .sql.
  • The -dir flag indicates that we want to store the migration files in the ./migrations directory (which will be created automatically if it doesn’t already exist).
  • The name create_abc_table is a descriptive label that we give the migration

Update content of migration files​

000001_name.up.sql
CREATE TABLE IF NOT EXISTS xyz (
    id bigserial PRIMARY KEY,
    version integer NOT NULL DEFAULT 1
    created_at timestamp(0) with time zone NOT NULL DEFAULT NOW(),
);
000001_name.down.sql
DROP TABLE IF EXISTS xyz;

Executing the migrations​

migrate -path=./migrations -database=$DB_DSN up

Listing the tables​

schema_migrations table is automatically generated by the migrate tool and used to keep track of which migrations have been applied.

terminal
psql $DB_DSN

For list of tables

\dt

For contents of schema_migrations

SELECT * FROM schema_migrations;
output
| version | dirty |
| ------- | ----- |
| 1       | f     |

The version column here indicates that our migration files up to (and including) number 1 in the sequence have been executed against the database. The value of the dirty column is false, which indicates that the migration files were cleanly executed without any errors and the SQL statements they contain were successfully applied in full.

Migrating to a specific version​

migrate -path=./migrations -database=$DB_DSN version

Executing down migrations​

migrate -path=./migrations -database=$DB_DSN down

will remove all tables from the database including the schema_migrations

Roll-back by a specific number of migrations​

migrate -path=./migrations -database =$DB_DSN down 1

Forcing migration verion​

When Migration fails, what you need to do is investigate the original error and figure out if the migration file which failed was partially applied. If it was, then you need to manually roll-back the partially applied migration.

Once that’s done, then you must also ‘force’ the version number in the schema_migrations table to the correct value.

migrate -path=./migrations -database=$DB_DSN force 1

Once you force the version, the database is considered ‘clean’ and you should be able to run migrations again without any problem.