Database Migrations in Go With the goose Tool Step by Step

A schema changes as often as the code, and the database has to change with it, in the same order, on every environment, including the ones you do not control. The tool for that is a migration runner. goose is one of the two most used in Go (the other is golang-migrate); it applies versioned SQL or Go migrations in order, records what has run, and supports rollback. This article uses goose as a library on Go 1.25.6 against PostgreSQL 18, with the files and the Go code you need and the output of every step.

The files

Migrations are files named with a version number and a description. goose supports sequential numbers (00001_create_users.sql) and timestamps (20261027120000_create_users.sql). Sequential numbers are easy to read; timestamps avoid two developers picking the same number on different branches. Choose one scheme and keep it.

-- +goose Up
create table users600 (
    id    bigserial primary key,
    email text not null unique
);

-- +goose Down
drop table users600;

The first file creates a table. Each file has two sections marked by comments: -- +goose Up for the forward change and -- +goose Down for its inverse. goose reads these markers; the rest is plain SQL.

-- +goose Up
alter table users600 add column name text not null default '';

-- +goose Down
alter table users600 drop column name;

The second adds a column with a default, so existing rows stay valid. Its Down drops the column.

-- +goose Up
-- +goose StatementBegin
do $$
begin
    insert into users600 (email, name) values ('[email protected]', 'Ada');
end
$$;
-- +goose StatementEnd

-- +goose Down
delete from users600 where email = '[email protected]';

The third seeds a row inside a DO block. A statement that contains semicolons inside its body, such as a function or a DO block, must be wrapped in -- +goose StatementBegin and -- +goose StatementEnd, because goose otherwise splits statements at semicolons. Forgetting the markers is a common source of confusing syntax errors.

Running them from Go

package main

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

	_ "github.com/jackc/pgx/v5/stdlib"
	"github.com/pressly/goose/v3"
)

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

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

func version(db *sql.DB) int64 {
	v, err := goose.GetDBVersion(db)
	if err != nil {
		log.Fatal(err)
	}
	return v
}

func main() {
	ctx := context.Background()
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	_, _ = db.Exec(`drop table if exists users600; drop table if exists goose_db_version`)

	goose.SetBaseFS(migrations)
	goose.SetLogger(goose.NopLogger())
	if err := goose.SetDialect("postgres"); err != nil {
		log.Fatal(err)
	}

	fmt.Println("version before:", version(db))
	if err := goose.UpContext(ctx, db, "migrations"); err != nil {
		log.Fatal(err)
	}
	fmt.Println("after Up:      ", version(db))

	var email, name string
	_ = db.QueryRow(`select email, name from users600`).Scan(&email, &name)
	fmt.Printf("seeded row: %s %s\n", email, name)

	if err := goose.DownContext(ctx, db, "migrations"); err != nil {
		log.Fatal(err)
	}
	fmt.Println("after one Down:", version(db))
	var n int
	_ = db.QueryRow(`select count(*) from users600`).Scan(&n)
	fmt.Println("rows after rolling back the seed:", n)

	// Up again is a no-op for applied versions and re-applies the one rolled back.
	_ = goose.UpContext(ctx, db, "migrations")
	fmt.Println("after Up again:", version(db))

	// The constraints from migration 1 are still in force after re-applying 3.
	_, err = db.Exec(`insert into users600 (email) values ('[email protected]')`)
	fmt.Printf("unique constraint from migration 1 still enforced: %.60s\n", fmt.Sprint(err))
	_, _ = db.Exec(`drop table users600; drop table goose_db_version`)
}

go run . 2>&1 | sed -E "s/[A-Za-z]{3} [A-Za-z]{3} +[0-9]+ [0-9:]{8} [0-9]{4}/<date>/"

version before: 0
after Up:       3
seeded row: [email protected] Ada
after one Down: 2
rows after rolling back the seed: 0
after Up again: 3
unique constraint from migration 1 still enforced: ERROR: duplicate key value violates unique constraint "users

The program embeds the files and runs the lifecycle.

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

goose.SetBaseFS(migrations)
goose.SetDialect("postgres")
goose.UpContext(ctx, db, "migrations")

//go:embed puts the SQL files in the binary, so a deployment is one artefact and the code and migrations cannot get out of step. SetBaseFS tells goose to read from it, SetDialect picks the SQL it uses for its own bookkeeping, and UpContext applies every migration not yet recorded.

The output, step by step:

• Before: version before: 0. goose creates its goose_db_version table on first use and finds no applied versions.
• After Up: after Up: 3. All three files ran, each in its own transaction, and the table holds users600 with the seeded row: seeded row: [email protected] Ada.
• After one Down: after one Down: 2. DownContext rolls back only the latest migration. The seed's Down deleted the row, and the count confirms: rows after rolling back the seed: 0.
• Up again: after Up again: 3. Only the rolled-back migration ran. Applied versions are skipped.
• Constraints remain: inserting a duplicate email fails with duplicate key value violates unique constraint, from migration 1's unique column.

goose also provides Status to print what is applied, DownTo and UpTo to move to a version, Redo to roll back and reapply the latest, and Reset to roll everything back. The command line tool offers the same as goose postgres "$DSN" up, which is the usual way to run migrations in CI and as a deploy step.

Transactions and what they protect

On PostgreSQL goose wraps each migration in a transaction, so if the statement on line 12 fails, the earlier lines roll back and the version is not recorded. The database stays at the previous version, and you fix the file and rerun. This is a strong reason to prefer PostgreSQL's transactional DDL. MySQL commits DDL implicitly, so a failed migration can leave a half-applied change; keep MySQL migrations small, one change each, and idempotent where possible (if not exists).

Some statements cannot run in a transaction, such as create index concurrently in PostgreSQL. Mark the migration with -- +goose NO TRANSACTION at the top. Such a migration is not atomic, so keep it to the single statement that needs it.

Writing migrations that are safe to run

Running migrations on a live database is where the real care goes.

1. Backward compatible steps. Deploy code that works with both the old and new schema, then migrate, then deploy code that uses the new one. Add columns as nullable or with a default; drop columns only after no code reads them. The pattern is called expand and contract.
2. Avoid long locks. alter table ... add column with a constant default is fast in current PostgreSQL. A create index without concurrently blocks writes for its duration. Adding a not null constraint to a big table scans it under a lock; add it not valid first and validate constraint later.
3. Set a lock timeout in the migration (set lock_timeout = '5s') so a migration that waits on a lock fails fast instead of queueing behind a long query and blocking everything behind it.
4. Separate schema from data. Large backfills belong in batches from application code or a job, not in one transaction.
5. Never edit an applied migration. Add a new one. goose records versions, not checksums by default, so editing silently diverges environments.
6. Test the down path, at least for recent migrations, and decide whether you really support rollbacks in production; many teams roll forward only and write Down for development.

Running them in a deployment

Three arrangements are common. A separate step in the deploy pipeline runs the goose CLI or a small migrate command from the same image before the new version starts. At application start-up, the service calls goose.Up before serving. It is simple and risky with many instances: two instances start together and race. the newer Provider API (goose.NewProvider) can take a session lock, such as a PostgreSQL advisory lock, so only one instance migrates at a time; check the documentation for the version you use. A one-shot job in Kubernetes runs migrations before the rollout.

Whatever you choose, run the same code path in development, tests and production. A test suite that starts from an empty database and runs all migrations is a cheap check that they apply cleanly in order; do it in CI against the real database version, using the container setup from PostgreSQL tests.

Alternatives

golang-migrate is the other widely used tool, with sources for many databases and a similar file naming scheme. Atlas generates migrations by diffing your desired schema against the database, which is a different workflow. ORMs bring their own migrators. If you generate Go code from SQL with sqlc, migrations written with goose are a natural source of its schema. The choice matters less than using one consistently. The tool is documented in the goose repository and on pkg.go.dev, and the PostgreSQL manual covers the locking behaviour of ALTER TABLE that determines whether a migration is safe on a live table. The connection and pool side is in connecting to PostgreSQL, and the transaction semantics in the transaction article.