Migration — Schema Version Management with golang-migrate & goose

Managing database schemas like code is essential for team collaboration and deployment automation. We will explore golang-migrate and goose, representative migration tools in the Go ecosystem.

---

What is Migration?

Migration is a method of managing database schema changes as ordered files.

001_create_users.up.sql   ← Apply (up)
001_create_users.down.sql ← Rollback (down)
002_add_email_index.up.sql
002_add_email_index.down.sql
...

Why is it Necessary?

• Ensures all team members maintain the same DB schema state.
• Automates schema changes during deployment.
• Enables rollback to a previous state when problems occur.
• Tracks schema change history in the code repository.

---

golang-migrate

Installation

# Install CLI
go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

# Install Library
go get -u github.com/golang-migrate/migrate/v4
go get -u github.com/golang-migrate/migrate/v4/database/postgres
go get -u github.com/golang-migrate/migrate/v4/source/file

Writing Migration Files

db/migrations/
├── 000001_create_users.up.sql
├── 000001_create_users.down.sql
├── 000002_create_posts.up.sql
├── 000002_create_posts.down.sql
├── 000003_add_user_role.up.sql
└── 000003_add_user_role.down.sql

-- 000001_create_users.up.sql
CREATE TABLE users (
    id         BIGSERIAL PRIMARY KEY,
    name       TEXT        NOT NULL,
    email      TEXT        UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 000001_create_users.down.sql
DROP TABLE IF EXISTS users;

-- 000002_create_posts.up.sql
CREATE TABLE posts (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT      NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title      TEXT        NOT NULL,
    content    TEXT,
    published  BOOLEAN     NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_posts_user_id ON posts(user_id);

-- 000002_create_posts.down.sql
DROP TABLE IF EXISTS posts;

-- 000003_add_user_role.up.sql
ALTER TABLE users ADD COLUMN role TEXT NOT NULL DEFAULT 'member';
CREATE INDEX idx_users_role ON users(role);

-- 000003_add_user_role.down.sql
DROP INDEX IF EXISTS idx_users_role;
ALTER TABLE users DROP COLUMN IF EXISTS role;

Executing Migrations with CLI

# Apply all migrations (up)
migrate -path db/migrations -database "postgres://user:pass@localhost/mydb?sslmode=disable" up

# Apply N steps
migrate -path db/migrations -database "..." up 1

# Rollback (down)
migrate -path db/migrations -database "..." down 1

# Move to a specific version
migrate -path db/migrations -database "..." goto 2

# Check current version
migrate -path db/migrations -database "..." version

# Force clear dirty state (after migration failure)
migrate -path db/migrations -database "..." force 3

Migration in Go Code

package db

import (
    "fmt"

    "github.com/golang-migrate/migrate/v4"
    _ "github.com/golang-migrate/migrate/v4/database/postgres"
    _ "github.com/golang-migrate/migrate/v4/source/file"
)

func RunMigrations(dbURL string) error {
    m, err := migrate.New(
        "file://db/migrations",
        dbURL,
    )
    if err != nil {
        return fmt.Errorf("failed to initialize migration: %w", err)
    }
    defer m.Close()

    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        return fmt.Errorf("failed to apply migration: %w", err)
    }

    version, dirty, _ := m.Version()
    fmt.Printf("Migration completed: version %d (dirty: %v)\n", version, dirty)
    return nil
}

// Rollback to a specific version
func RollbackTo(dbURL string, version uint) error {
    m, err := migrate.New("file://db/migrations", dbURL)
    if err != nil {
        return err
    }
    defer m.Close()

    return m.Migrate(version)
}

Automatic Migration on Application Startup

func main() {
    cfg := loadConfig()

    // Run DB migrations first
    if err := db.RunMigrations(cfg.DatabaseURL); err != nil {
        log.Fatalf("migration failed: %v", err)
    }

    // Connect to DB
    database, err := sql.Open("postgres", cfg.DatabaseURL)
    if err != nil {
        log.Fatalf("failed to connect to DB: %v", err)
    }
    defer database.Close()

    // Start server
    startServer(database)
}

---

goose

goose is a more flexible migration tool that also supports writing migrations in Go code.

Installation

go install github.com/pressly/goose/v3/cmd/goose@latest
go get github.com/pressly/goose/v3

goose File Formats

db/migrations/
├── 20240101000001_create_users.sql
├── 20240101000002_create_posts.sql
└── 20240101000003_seed_data.go   ← Go code migration possible!

goose allows choosing between timestamp or sequence methods.

-- 20240101000001_create_users.sql
-- +goose Up
CREATE TABLE users (
    id         BIGSERIAL PRIMARY KEY,
    name       TEXT NOT NULL,
    email      TEXT UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- +goose Down
DROP TABLE IF EXISTS users;

-- 20240101000002_create_posts.sql
-- +goose Up
-- +goose StatementBegin
CREATE TABLE posts (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT NOT NULL REFERENCES users(id),
    title      TEXT NOT NULL,
    content    TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_posts_user_id ON posts(user_id);
-- +goose StatementEnd

-- +goose Down
DROP TABLE IF EXISTS posts;

Migration with Go Code (Data Transformation, etc.)

// 20240101000003_seed_data.go
package migrations

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

    "github.com/pressly/goose/v3"
)

func init() {
    goose.AddMigrationContext(upSeedData, downSeedData)
}

func upSeedData(ctx context.Context, tx *sql.Tx) error {
    // Insert initial data
    _, err := tx.ExecContext(ctx, `
        INSERT INTO users (name, email) VALUES
        ('Administrator', 'admin@example.com'),
        ('Test User', 'test@example.com')
    `)
    if err != nil {
        return fmt.Errorf("failed to insert initial data: %w", err)
    }
    return nil
}

func downSeedData(ctx context.Context, tx *sql.Tx) error {
    _, err := tx.ExecContext(ctx, `DELETE FROM users WHERE email IN ('admin@example.com', 'test@example.com')`)
    return err
}

goose CLI Commands

# DB connection with environment variables
export GOOSE_DRIVER=postgres
export GOOSE_DBSTRING="host=localhost user=myuser password=mypass dbname=mydb"
export GOOSE_MIGRATION_DIR=./db/migrations

# Apply all
goose up

# Apply one by one
goose up-by-one

# Rollback
goose down

# Check current status
goose status

# Create a new migration file
goose create add_user_avatar sql
goose create seed_roles go

Using goose in Go Code

package db

import (
    "database/sql"
    "fmt"

    "github.com/pressly/goose/v3"
)

func RunGooseMigrations(db *sql.DB) error {
    goose.SetDialect("postgres")

    if err := goose.Up(db, "./db/migrations"); err != nil {
        return fmt.Errorf("migration failed: %w", err)
    }

    version, err := goose.GetDBVersion(db)
    if err != nil {
        return err
    }
    fmt.Printf("Current migration version: %d\n", version)
    return nil
}

---

Migration Strategies and Best Practices

Safe Migration Checklist

-- ✅ Good Pattern: New columns must be NULLable or have a DEFAULT value
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- ❌ Risky: Adding NOT NULL locks the table on large tables
ALTER TABLE users ADD COLUMN score INT NOT NULL DEFAULT 0;

-- ✅ Alternative: Add as NULLable, backfill, then add NOT NULL constraint
ALTER TABLE users ADD COLUMN score INT;
UPDATE users SET score = 0 WHERE score IS NULL;
ALTER TABLE users ALTER COLUMN score SET NOT NULL;
ALTER TABLE users ALTER COLUMN score SET DEFAULT 0;

Zero-Downtime Migration during Deployment

Step 1 (Currently running app): Add new column (NULLable)
Step 2 (Deploy new app): Read/Write to new column
Step 3 (Next deployment): Remove existing column

Automating Migration with Makefile

MIGRATE=migrate -path db/migrations -database $(DATABASE_URL)

.PHONY: migrate-up migrate-down migrate-version

migrate-up:
	$(MIGRATE) up

migrate-down:
	$(MIGRATE) down 1

migrate-version:
	$(MIGRATE) version

migrate-create:
	$(MIGRATE) create -ext sql -dir db/migrations -seq $(name)

---

golang-migrate vs goose Comparison

Criteria	golang-migrate	goose
File Format	Separate up/down files	Single file (+goose Up/Down)
Go Code Migration	Not supported	Supported
Timestamp-based	Not supported	Supported
Active Maintenance	✅	✅
Embedded Migration	Supported	Supported

Recommendation:

• Simple SQL migrations → golang-migrate
• Need complex data transformations or Go code migrations → goose

---

Key Takeaways

• Never modify migration files — always add a new file.
• Always write down migrations (for rollback preparation).
• Essential to test migrations in staging before production deployment.
• Approach schema changes for large tables with a Zero-Downtime strategy.