Database Migrations in Go With golang migrate Step by Step

golang-migrate is the other widely used migration tool for Go, next to goose. Its model is the plainest of the family: a folder of paired SQL files, a table with one row that says which version the database is at, and a flag that says whether the last attempt finished. This article uses it as a library against PostgreSQL 18 with the pgx v5 driver on Go 1.25.6, runs the whole lifecycle, and then breaks a migration on purpose to show what the dirty flag means and how to recover.

It belongs to the database cornerstone. Embedding the files in the binary is the subject of the next article, and keeping a migration safe on a busy table is covered in zero downtime migrations.

The file layout

A migration is two files with the same number and name: 000001_create_items.up.sql applies it and 000001_create_items.down.sql reverses it. The number is the version, an unsigned integer, and the runner orders by it. Sequential numbers are readable; a timestamp such as 20261027120000 avoids two branches picking the same number. Pick one scheme.

create table items601 (
    id    bigserial primary key,
    sku   text not null unique,
    price numeric(10,2) not null
);

drop table items601;

alter table items601 add column stock integer not null default 0;
insert into items601 (sku, price, stock) values ('A-100', 9.50, 12);

delete from items601 where sku = 'A-100';
alter table items601 drop column stock;

The second pair shows the habit that matters most with paired files: the down has to undo everything the up did. File two inserts a row, so its down deletes it before dropping the column. A first version of this lab dropped only the column, and the next Up then failed on a duplicate sku, because the row from the first run was still there. A down that is only half an inverse looks fine until the day somebody rolls back and forward again.

insert into items601 (sku, price, stock) values ('B-200', 4.00, 3);
alter table items601 add column nope integer not null default 'x';

select 1;

The third pair is broken on purpose. Its second statement gives an integer column a text default, which PostgreSQL rejects.

Running them from Go

The library has three parts. The core (migrate) drives the lifecycle, a source driver reads the files, and a database driver talks to the server. Both are chosen by import. This lab uses iofs, which reads any fs.FS, so the folder can be embedded with //go:embed, and the pgx/v5 database driver, which takes a URL with the scheme pgx5://.

package main

import (
	"context"
	"database/sql"
	"embed"
	"errors"
	"fmt"
	"log"

	"github.com/golang-migrate/migrate/v4"
	_ "github.com/golang-migrate/migrate/v4/database/pgx/v5"
	"github.com/golang-migrate/migrate/v4/source/iofs"
	_ "github.com/jackc/pgx/v5/stdlib"
)

//go:embed migrations/*.sql
var files embed.FS

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

func newMigrator() *migrate.Migrate {
	src, err := iofs.New(files, "migrations")
	if err != nil {
		log.Fatal(err)
	}
	m, err := migrate.NewWithSourceInstance("iofs", src,
		"pgx5://postgres:[email protected]:55432/lab?sslmode=disable&x-migrations-table=schema_migrations_601")
	if err != nil {
		log.Fatal(err)
	}
	return m
}

func report(m *migrate.Migrate, label string) {
	v, dirty, err := m.Version()
	if errors.Is(err, migrate.ErrNilVersion) {
		fmt.Printf("%-22s version none\n", label)
		return
	}
	fmt.Printf("%-22s version %d dirty=%v\n", label, v, dirty)
}

func main() {
	ctx := context.Background()
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	for _, q := range []string{"drop table if exists items601", "drop table if exists schema_migrations_601"} {
		if _, err := db.ExecContext(ctx, q); err != nil {
			log.Fatal(err)
		}
	}

	m := newMigrator()
	report(m, "start")

	if err := m.Migrate(2); err != nil {
		log.Fatal(err)
	}
	report(m, "after Migrate(2)")

	err = m.Migrate(2)
	fmt.Println("Migrate(2) again:", err, errors.Is(err, migrate.ErrNoChange))

	if err := m.Steps(-1); err != nil {
		log.Fatal(err)
	}
	report(m, "after Steps(-1)")

	if err := m.Up(); err != nil && !errors.Is(err, migrate.ErrNoChange) {
		fmt.Println("Up error:", err)
	}
	report(m, "after Up with bad 3")

	var n int
	_ = db.QueryRowContext(ctx, "select count(*) from items601").Scan(&n)
	fmt.Println("rows in items601 (the insert from file 3 should be rolled back):", n)

	err = m.Up()
	var dirty migrate.ErrDirty
	fmt.Println("Up on a dirty database:", err, errors.As(err, &dirty))

	if err := m.Force(2); err != nil {
		log.Fatal(err)
	}
	report(m, "after Force(2)")

	if err := m.Down(); err != nil {
		log.Fatal(err)
	}
	report(m, "after Down")
	src, drv := m.Close()
	fmt.Println("close errors:", src, drv)
}

start                  version none
after Migrate(2)       version 2 dirty=false
Migrate(2) again: no change true
after Steps(-1)        version 1 dirty=false
Up error: migration failed: invalid input syntax for type integer: "x" (column 0) in line 1: insert into items601 (sku, price, stock) values ('B-200', 4.00, 3);
alter table items601 add column nope integer not null default 'x';
 (details: ERROR: invalid input syntax for type integer: "x" (SQLSTATE 22P02))
after Up with bad 3    version 3 dirty=true
rows in items601 (the insert from file 3 should be rolled back): 1
Up on a dirty database: Dirty database version 3. Fix and force version. true
after Force(2)         version 2 dirty=false
after Down             version none
close errors: <nil> <nil>

The URL carries one option worth knowing: x-migrations-table renames the bookkeeping table, which defaults to schema_migrations. Two services that share a database need different names, or each will think the other's version is its own.

Read the output in order.

• start version none. Version returns migrate.ErrNilVersion before anything has run. The library creates schema_migrations on first use.
• Migrate(2). It moved forward to exactly version 2. Up goes to the newest file, Migrate(n) goes to a chosen version in either direction, and Steps(n) moves by a count, forward or back.
• Migrate(2) again. The call returned no change, and errors.Is(err, migrate.ErrNoChange) was true. This is the one error every caller must filter, because a start-up routine that runs Up on an up to date database would otherwise fail every boot after the first.
• Steps(-1). Only the latest down ran, so the version fell to 1.

The runner works one file at a time and writes the new version after each. A file with several statements runs as one multi-statement call to the server, which PostgreSQL executes as one implicit transaction. That detail decides what a failure looks like.

What a dirty version means

Up then reached file three and failed. The error carries the server's message and the line of the file that triggered it, which is the reason to read it before doing anything else: invalid input syntax for type integer: "x".

The version afterwards was 3 with dirty=true. The library sets the flag before it runs a file and clears it after the file succeeds, so a crash in the middle, a lost connection or a failing statement all leave the same mark. Every later Up refuses with Dirty database version 3. Fix and force version., and the error type is migrate.ErrDirty, so a deploy script can tell it apart from an ordinary failure.

The lab then counted the rows in items601. It found one, not two. File three inserts a row first and fails on the next statement, and because PostgreSQL ran the pair as one transaction, the insert was undone with the failed alter. That is the good case. It is not a guarantee of the tool. The driver sends the file as one call, so atomicity comes from PostgreSQL's transactional DDL. On MySQL, which commits most DDL statements implicitly, the same file would leave the first statements applied and the later ones not, and the dirty flag would be the only sign of it. Keep MySQL migrations to one change per file.

Recovering is a manual step, deliberately:

1. Read the error and look at the database. Is the schema at version 2, or partly at 3?
2. Fix the cause, which usually means correcting the file, since an unapplied or half applied file may be edited.
3. If the schema really is at version 2, call Force(2). If it is partly at 3, repair the schema by hand first. Force only rewrites the version row and the flag; it does not run any SQL.

The lab's Force(2) returned the state to version 2 dirty=false, and Down then walked back to none. A Force to a version that does not match the real schema is worse than the dirty flag, because the next down will fail on a column that was never added, or an up will try to create a table that exists. The flag is annoying on purpose.

Choosing how to run it

The library is usable from a command line tool, migrate, and as the package shown here. Which one fits depends on where migrations run.

The race at start-up is real. The PostgreSQL driver takes a session level advisory lock while it migrates, so two instances do not apply the same file at once; the second waits and then sees ErrNoChange. Do not lean on that for MySQL or on a database behind a pooler in transaction mode, where session locks do not hold. A separate migration job removes the question.

Habits that keep migrations boring

• Never edit a file that has been applied anywhere. The tool stores a version, not a checksum, so an edit silently diverges one environment from another. Add a new file.
• Write the down as part of the up. If you will not support rollbacks, say so in the repository and delete the down files rather than leaving ones that are not tested.
• Check ErrNoChange and ErrDirty by type, not by message text.
• Keep one concern per file and keep long data backfills out of schema files; run them in batches from application code.
• Run all migrations from an empty database in CI, against the same major version of the server that runs in production, as the test patterns in connecting to PostgreSQL set up.

The behaviour described here matches the library's documentation of migrations and its PostgreSQL driver notes, which list the URL options. PostgreSQL's own page on transactional DDL explains why a failed file in this lab left nothing behind.