Database Migrations in Go

Database migrations are essential for managing schema changes in production applications. As your application evolves, your database schema must evolve with it - adding tables, modifying columns, creating indexes. This post covers migration strategies and tools for Go applications, with a focus on safe, reversible changes.

Why Migrations Matter

flowchart LR
    subgraph Problem["Without Migrations"]
        D1[Dev DB] --> |Different Schema| P1[Prod DB]
        P1 --> |Manual Changes| R1[Risk of Errors]
    end

    subgraph Solution["With Migrations"]
        M1[Migration 001] --> M2[Migration 002]
        M2 --> M3[Migration 003]
        M3 --> D2[All DBs Identical]
    end

    style Problem fill:#ffcdd2
    style Solution fill:#c8e6c9

Migration benefits:

• Version control: Track schema changes like code
• Reproducibility: Same schema across all environments
• Rollback capability: Undo changes when needed
• Team collaboration: Clear history of who changed what

Migration Tools for Go

Popular Options

Tool	Features	Best For
golang-migrate	SQL-based, many drivers	Production systems
goose	Go/SQL migrations	Flexible migrations
GORM AutoMigrate	Automatic from structs	Development only
Atlas	Declarative + versioned	Modern approach

golang-migrate Setup

# 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

Creating Migrations

Migration File Structure

migrations/
├── 000001_create_users.up.sql
├── 000001_create_users.down.sql
├── 000002_add_user_profile.up.sql
├── 000002_add_user_profile.down.sql
├── 000003_create_products.up.sql
└── 000003_create_products.down.sql

Create Migration Command

migrate create -ext sql -dir migrations -seq create_users

Example Migration: Create Users Table

000001_create_users.up.sql:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);

000001_create_users.down.sql:

DROP INDEX IF EXISTS idx_users_username;
DROP INDEX IF EXISTS idx_users_email;
DROP TABLE IF EXISTS users;

Example Migration: Add Column

000002_add_user_profile.up.sql:

ALTER TABLE users ADD COLUMN profile JSONB DEFAULT '{}';
ALTER TABLE users ADD COLUMN last_login TIMESTAMP WITH TIME ZONE;
CREATE INDEX idx_users_profile ON users USING GIN (profile);

000002_add_user_profile.down.sql:

DROP INDEX IF EXISTS idx_users_profile;
ALTER TABLE users DROP COLUMN IF EXISTS last_login;
ALTER TABLE users DROP COLUMN IF EXISTS profile;

Running Migrations

CLI Commands

# Run all pending migrations
migrate -database "postgres://user:pass@localhost:5432/myapp?sslmode=disable" \
        -path migrations up

# Run specific number of migrations
migrate -database "..." -path migrations up 2

# Rollback last migration
migrate -database "..." -path migrations down 1

# Rollback all migrations
migrate -database "..." -path migrations down

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

# Force version (use with caution!)
migrate -database "..." -path migrations force 3

Programmatic Migrations

package main

import (
    "log"

    "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, migrationsPath string) error {
    m, err := migrate.New(
        "file://"+migrationsPath,
        dbURL,
    )
    if err != nil {
        return err
    }
    defer m.Close()

    // Run all up migrations
    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        return err
    }

    version, dirty, _ := m.Version()
    log.Printf("Migration complete. Version: %d, Dirty: %v", version, dirty)

    return nil
}

func RollbackMigration(dbURL, migrationsPath string, steps int) error {
    m, err := migrate.New("file://"+migrationsPath, dbURL)
    if err != nil {
        return err
    }
    defer m.Close()

    return m.Steps(-steps)
}

Safe Migration Patterns

Adding Columns

flowchart TD
    subgraph Safe["Safe: Add Column"]
        A1[Add Column with Default] --> A2[App Handles NULL]
        A2 --> A3[Backfill Data]
        A3 --> A4[Add NOT NULL Constraint]
    end

    subgraph Unsafe["Unsafe: Direct NOT NULL"]
        B1[Add NOT NULL Column] --> B2[Migration Fails!]
    end

    style Safe fill:#c8e6c9
    style Unsafe fill:#ffcdd2

Safe approach:

-- Step 1: Add nullable column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Step 2: Backfill existing data (if needed)
UPDATE users SET phone = 'unknown' WHERE phone IS NULL;

-- Step 3: Add constraint (separate migration)
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

Renaming Columns

Don’t rename directly! Use a multi-step approach:

-- Migration 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
UPDATE users SET full_name = name;

-- Migration 2: (After app is updated to use full_name)
ALTER TABLE users DROP COLUMN name;

Removing Columns

-- Safe: Remove in multiple steps
-- Step 1: Stop using column in application
-- Step 2: Remove column
ALTER TABLE users DROP COLUMN IF EXISTS legacy_field;

Adding Indexes Concurrently

-- For large tables, use CONCURRENTLY to avoid locking
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

-- Note: Cannot be run inside a transaction

Migration in Production

Zero-Downtime Migration Strategy

sequenceDiagram
    participant App as Application
    participant DB as Database

    Note over App,DB: Phase 1: Prepare
    App->>DB: Migration: Add nullable column
    Note over App: App writes to both columns

    Note over App,DB: Phase 2: Backfill
    App->>DB: Backfill old data
    Note over App: App reads from new column

    Note over App,DB: Phase 3: Cleanup
    App->>DB: Migration: Drop old column
    Note over App: App uses only new column

Pre-deployment Checks

func ValidateMigration(db *sql.DB) error {
    // Check for pending migrations
    m, _ := migrate.New("file://migrations", dbURL)
    version, dirty, err := m.Version()
    if err != nil {
        return err
    }

    if dirty {
        return errors.New("database is in dirty state - manual intervention required")
    }

    log.Printf("Current migration version: %d", version)
    return nil
}

Rollback Procedures

func EmergencyRollback(dbURL, migrationsPath string, targetVersion uint) error {
    m, err := migrate.New("file://"+migrationsPath, dbURL)
    if err != nil {
        return err
    }
    defer m.Close()

    currentVersion, _, _ := m.Version()
    log.Printf("Rolling back from version %d to %d", currentVersion, targetVersion)

    // Migrate to specific version
    return m.Migrate(targetVersion)
}

Team Workflow

Migration Naming Convention

{version}_{description}.{up|down}.sql

Examples:
000001_create_users.up.sql
000002_add_email_verification.up.sql
000003_create_orders_table.up.sql

Git Workflow for Migrations

gitGraph
    commit id: "feature-start"
    branch feature/add-orders
    commit id: "add migration 004"
    commit id: "implement orders"
    checkout main
    branch feature/add-products
    commit id: "add migration 004"  type: HIGHLIGHT
    commit id: "implement products"
    checkout main
    merge feature/add-orders
    checkout feature/add-products
    commit id: "renumber to 005"
    checkout main
    merge feature/add-products

Conflict resolution:

1. Rebase feature branch on main
2. Renumber conflicting migrations
3. Test migration sequence locally

CI/CD Integration

# .github/workflows/migrate.yml
name: Database Migration

on:
  push:
    branches: [main]
    paths:
      - 'migrations/**'

jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2

      - name: Run migrations
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: |
          migrate -database "$DATABASE_URL" -path migrations up

      - name: Verify migration
        run: |
          migrate -database "$DATABASE_URL" -path migrations version

Recovery Scenarios

Dirty Database State

When a migration fails midway:

# Check current state
migrate -database "..." -path migrations version
# Output: 3 (dirty)

# Force to last known good version
migrate -database "..." -path migrations force 2

# Fix the migration file, then retry
migrate -database "..." -path migrations up

Data Recovery

func BackupBeforeMigration(db *sql.DB, tableName string) error {
    backupName := fmt.Sprintf("%s_backup_%s", tableName, time.Now().Format("20060102"))

    _, err := db.Exec(fmt.Sprintf(
        "CREATE TABLE %s AS SELECT * FROM %s",
        backupName, tableName,
    ))
    return err
}

func RestoreFromBackup(db *sql.DB, tableName, backupName string) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    // Truncate original
    _, err = tx.Exec(fmt.Sprintf("TRUNCATE TABLE %s", tableName))
    if err != nil {
        return err
    }

    // Restore from backup
    _, err = tx.Exec(fmt.Sprintf(
        "INSERT INTO %s SELECT * FROM %s",
        tableName, backupName,
    ))
    if err != nil {
        return err
    }

    return tx.Commit()
}

Best Practices Checklist

Practice	Description
Always write down migrations	Every up needs a down
Test rollbacks locally	Before deploying to production
Small, focused migrations	One logical change per migration
No data operations in schema migrations	Separate data migrations
Use transactions	Except for concurrent index creation
Backup before major changes	Especially for data migrations
Version control migrations	Treat as code
Review migrations in PRs	Extra scrutiny for schema changes

Summary

flowchart TD
    subgraph Lifecycle["Migration Lifecycle"]
        C[Create Migration] --> T[Test Locally]
        T --> R[Review in PR]
        R --> S[Stage Environment]
        S --> P[Production Deploy]
        P --> M[Monitor]
        M -->|Issues| RB[Rollback]
    end

    style Lifecycle fill:#e3f2fd

Aspect	Key Points
Tools	golang-migrate for SQL migrations
Structure	Numbered up/down pairs
Safety	Multi-step for breaking changes
Production	Zero-downtime strategies
Team	Clear naming, CI/CD integration
Recovery	Always have rollback plan

---

Next post: Building REST APIs with Go and Gin - Creating high-performance APIs with the Gin framework.