Database migrations in Go: practical tips and trade-offs

Database migrations in Go: practical tips and trade-offs

After 30 days of building production APIs in Go, you'll hit a problem every team faces: how do you safely evolve your database schema as requirements change? Schema drift, missing indexes, and rollback failures can cost hours of debugging or worse, downtime. In this article, we explore database migrations in Go—the tooling decisions, execution patterns, and hard-earned practices that keep your deployments predictable and your team confident.

Why migrations matter in production

A production database isn't just data; it's the contract between your application and persistence layer. When you deploy new code that expects a column that doesn't exist yet, or when you forget to drop a deprecated table, schema mismatches create silent failures or runtime panics.

Manual SQL scripts invite human error. Ad-hoc ALTER TABLE statements executed via SSH risk locking production tables. Without version control on your schema, you lose the ability to reproduce environments or audit what changed and when. Migrations solve this by treating schema changes as code: versioned, reviewed, repeatable, and reversible.

golang-migrate vs. goose: choosing your tool

The two most popular migration frameworks in Go are golang-migrate (also called migrate) and goose. Both are solid; the choice depends on your workflow and tolerance for features.

golang-migrate is minimal and fast. It stores a version number and tracks which migrations have run. It supports multiple databases (PostgreSQL, MySQL, SQLite, MongoDB, and others) through a plugin architecture. The CLI is straightforward:

migrate -path db/migrations -database postgres://user:pass@localhost/dbname up

It integrates cleanly into your Go application by importing the driver:

import _ "github.com/golang-migrate/migrate/v4/database/postgres"
import _ "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 err
    }
    return m.Up()
}

goose takes a different approach: it embeds migrations as Go functions or SQL files. You can version migrations alongside your code and avoid external dependencies during deployment. Goose also makes it easier to run migrations on application startup with built-in helpers. Here's a typical flow:

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

func init() {
    goose.AddMigration(upCreateUsers, downCreateUsers)
}

func upCreateUsers(tx *sql.Tx) error {
    _, err := tx.Exec(`
        CREATE TABLE users (
            id BIGSERIAL PRIMARY KEY,
            email VARCHAR(255) UNIQUE NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    `)
    return err
}

func downCreateUsers(tx *sql.Tx) error {
    _, err := tx.Exec("DROP TABLE users")
    return err
}

Our recommendation: use golang-migrate for teams that prefer separation of concerns and CLI tooling. Use goose if your team wants migrations baked into the binary and prefers Go code over raw SQL. Both handle concurrent migrations safely; both are production-tested.

SQL files with up and down: the anatomy of a migration

Whether you choose golang-migrate or goose, organize migrations as numbered SQL files: one pair per change. The naming convention matters because it determines execution order.

With golang-migrate, the format is YYYYMMDDHHMMSS_description.up.sql and YYYYMMDDHHMMSS_description.down.sql. The timestamp ensures deterministic ordering even if files are created out of sequence:

db/migrations/
├── 20240115100000_create_users.up.sql
├── 20240115100000_create_users.down.sql
├── 20240120143000_add_phone_to_users.up.sql
├── 20240120143000_add_phone_to_users.down.sql
└── 20240125090500_create_orders.up.sql
└── 20240125090500_create_orders.down.sql

The .up.sql file contains the schema change:

-- 20240115100000_create_users.up.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

The .down.sql file is your escape hatch—it reverses the change:

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

Down migrations are not always perfect mirrors. A down migration that deletes a table with millions of rows might be slow; a down migration that drops a column loses data permanently. This is intentional: the down migration should be possible, not necessarily painless. It's an undo lever for development and staging, not a primary recovery tool.

A practical pattern: for productions with strict schema changes, sometimes your down migration documents how to restore but isn't meant to be run. Document this clearly in comments.

Another tip: avoid data migration logic in SQL migrations if possible. If you're backfilling or transforming existing rows, do it in a separate step using your application code. SQL migrations should define schema; application code handles data transformation with better error handling and logging.

Run on deploy or startup: trade-offs

When should migrations execute? Two schools of thought dominate production teams.

Run on deploy (before or alongside application startup):

• Migrations run once, independently of application instances
• Schema is ready before any code expects it
• Deployment orchestration (Kubernetes, ECS, Lambda) owns the responsibility
• Database lock contention is predictable and time-boxed

A typical deployment step:

#!/bin/bash
# deploy.sh
set -e

echo "Running migrations..."
migrate -path db/migrations -database $DATABASE_URL up

echo "Starting application..."
./app -port $PORT

Run on startup (idempotently, in your main function):

• Schema is always up-to-date before handlers run
• No separate deployment step; the app brings its own schema
• Multiple instances can run migrations concurrently (tools handle locking)
• Simpler for containerized, auto-scaling environments

Implementation with goose:

func main() {
    db, err := sql.Open("postgres", os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Run migrations before starting the server
    if err := goose.UpContext(context.Background(), db, "db/migrations"); err != nil {
        log.Fatal("migrations failed:", err)
    }

    // Start your HTTP server, workers, etc.
    http.ListenAndServe(":8080", mux)
}

Our recommendation: for most teams, run on startup. It's simpler, safer for Kubernetes deployments, and migrations are idempotent so concurrent executions don't cause issues (the framework handles locking). Reserve deploy-time migrations for schema changes so large or risky that you want explicit control and monitoring outside the app lifecycle.

Never edit applied migrations

This is a rule, not a suggestion. Once a migration has run in any environment, treat it as immutable.

Why? Because the migration system tracks which migrations have executed. If you edit 20240115100000_create_users.up.sql after it's already run, the system won't re-run it. Your local development database might have the old schema, but staging and production have the new one. You've now created schema drift—the same version number corresponds to different schemas across environments.

Instead, if you realize a migration has a bug, create a new migration that fixes it:

-- 20240115100000_create_users.up.sql (original, DO NOT EDIT)
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 20240116084000_fix_users_add_updated_at.up.sql (fix for the above)
ALTER TABLE users ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

This pattern is called "forward-only migrations" and it's universal in production systems. It's also why down migrations are secondary—they're for undoing recent mistakes in local or staging, not for routine rollback strategies.

Common mistakes

Forgetting indexes: Migrations often create tables but skip indexes. Your app works fine on test data, then crawls in production. Always add indexes in the same migration that creates the table or column.

Long-running migrations during peak hours: An ALTER TABLE FULL on a 500GB table locks writes. Coordinate large migrations during maintenance windows or use zero-downtime techniques (create new table, backfill, swap).

Mixing schema and data migration: A migration that creates a column and backfills it with complex business logic is hard to debug and dangerous to roll back. Separate concerns: schema in migrations, data transformation in application code or separate tooling.

No local testing: Never deploy a migration you haven't run locally. Even better, run it against a production-like copy. Use Docker Compose to spin up a test database before committing.

Assuming idempotency: If you run migrations on startup, the framework ensures idempotency at the migration level. But if you write a SQL migration that isn't idempotent (e.g., INSERT INTO config VALUES ('key', 'value') without a WHERE clause), you'll corrupt data on retries. Always use idempotent operations: INSERT ... ON CONFLICT DO UPDATE, CREATE INDEX IF NOT EXISTS, etc.

Key takeaways

• Choose golang-migrate for CLI-first workflows, goose for embedded migrations in your binary. Both are battle-tested; pick based on your deployment model.
• Write numbered SQL files with clear up and down migrations. Down migrations are escape hatches, not primary recovery tools.
• Run migrations on application startup (idempotently) for most deployments. It's simpler, safer, and works naturally with container orchestration.
• Never edit a migration once it's applied. Create a new migration to fix mistakes. This prevents schema drift across environments.
• Keep migrations focused on schema, not complex data transformation. Use your application code for business logic and error handling.
• Test migrations locally against production-like databases. Long-running migrations or locks can hide until production.
• Document risky migrations. If a down migration is destructive or impractical, say so. Make it explicit.