Mastering Database Migrations with golang-migrate: A Step‑by‑Step Guide

1 Project Address

https://github.com/golang-migrate/migrate

2 Usage Scenario

When a project requires database and code‑logic changes, tools like GORM cannot cover all schema modifications clearly. Using golang-migrate lets you explicitly mark each database change, making it easy for DevOps to deploy and roll back specific versions.

golang-migrate can be used via its CLI or as a Go library; this guide demonstrates the CLI.

2.1 Supported Database Types

Source drivers: github-ee, godoc-vfs, s3, bitbucket, go-bindata, gcs, file, github, gitlab

Database drivers: cockroachdb, firebird, postgresql, redshift, clickhouse, postgres, cockroach, firebirdsql, mysql, crdb-postgres, mongodb, mongodb+srv, neo4j, pgx, spanner, sqlserver, stub, cassandra

3 Usage Method

Initial tests were run on MySQL, which produced errors, so the guide switched to PostgreSQL for a successful run.

3.1 Install migrate CLI

Reference: https://github.com/golang-migrate/migrate/tree/master/cmd/migrate

Download the appropriate release binary; the CLI supports all database drivers despite some documentation suggesting otherwise.

3.2 PostgreSQL Test

3.2.1 Reference

https://github.com/golang-migrate/migrate/blob/master/database/postgres/TUTORIAL.md

3.2.2 Start PostgreSQL with Docker

docker run --name postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres:14.0

Create the test database:

psql -h localhost -U postgres -w -c "create database example;"

3.2.3 Create Migration Files

mkdir migrations # first execution, store sql files

Create a migration for a new users table:

migrate create -ext sql -dir ./migrations -seq create_users_table

This generates:

migrations/
├── 000001_create_users_table.up.sql
├── 000001_create_users_table.down.sql

Edit 000001_create_users_table.up.sql:

CREATE TABLE IF NOT EXISTS users(
   user_id serial PRIMARY KEY,
   username VARCHAR(50) UNIQUE NOT NULL,
   password VARCHAR(50) NOT NULL,
   email VARCHAR(300) UNIQUE NOT NULL
);

Edit 000001_create_users_table.down.sql:

DROP TABLE IF EXISTS users;

3.2.4 Apply a Migration

Use up to apply and down to roll back.

migrate -database 'postgres://postgres:[email protected]:5432/example?sslmode=disable' -path ./migrations up 1

After execution, the users table and schema_migrations table appear.

3.2.4.1 First Up Execution

migrate -database 'postgres://postgres:[email protected]:5432/example?sslmode=disable' -path ./migrations up 1

Resulting tables:

schema_migrations (version=1, dirty=f)
users (user_id, username, password, email)

3.2.4.2 Rollback

migrate -database 'postgres://postgres:[email protected]:5432/example?sslmode=disable' -path ./migrations down

Both users and schema_migrations are removed.

3.2.5 Multiple Sequential Migrations

Second migration adds a mood column with an enum type:

migrate create -ext sql -dir ./migrations -seq add_mood_to_users

Up script ( 000002_add_mood_to_users.up.sql)

BEGIN;
CREATE TYPE enum_mood AS ENUM ('happy','sad','neutral');
ALTER TABLE users ADD COLUMN IF NOT EXISTS mood enum_mood;
COMMIT;

Down script ( 000002_add_mood_to_users.down.sql)

BEGIN;
ALTER TABLE users DROP COLUMN IF EXISTS mood;
DROP TYPE enum_mood;
COMMIT;

Third migration adds a role_id column:

migrate create -ext sql -dir ./migrations -seq add_roleid_to_users

ALTER TABLE users ADD COLUMN IF NOT EXISTS role_id INTEGER;

Directory now contains six SQL files (three up, three down).

3.2.5.1 Applying Multiple Migrations

Use migrate up to run all pending migrations, or migrate up N to run the next N migrations after the current version.

3.2.6 Handling Failed Migrations

After introducing a syntax error in 000003_add_roleid_to_users.up.sql, the migration fails and schema_migrations shows dirty=t. Fix the script, then run:

migrate force 3   # mark version 3 as clean
migrate -database ... -path ./migrations down 1   # roll back to version 2
migrate -database ... -path ./migrations up 1       # re‑apply version 3

3.3 MySQL Test

Initial MySQL command failed because Zsh interpreted the asterisk in the URL. Adding setopt no_nomatch to ~/.zshrc or quoting the URL resolves the issue.

A subsequent error was due to an incorrect MySQL DSN format; the correct format is:

mysql://root:passwd@tcp(192.168.10.212:3306)/database

After fixing the DSN, migrate runs successfully against MySQL.