---
title: "Database Migrations in Go"
url: "https://vanducng.dev/2024/03/22/Database-Migrations-in-Go/"
date: "2024-03-22"
series: "go-backend"
categories: ["Backend"]
tags: ["go","postgresql"]
---

# 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

```mermaid
flowchart TB
    subgraph Solution["With Migrations"]
        direction LR
        M1[Migration 001] --> M2[Migration 002]
        M2 --> M3[Migration 003]
        M3 --> D2[All DBs Identical]
    end

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

    classDef pinkClass fill:#E74C3C,stroke:#333,stroke-width:2px,color:#fff
    classDef greenClass fill:#27AE60,stroke:#333,stroke-width:2px,color:#fff

    class Problem pinkClass
    class Solution greenClass
```

**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

```bash
# 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

```bash
migrate create -ext sql -dir migrations -seq create_users
```

### Example Migration: Create Users Table

**000001_create_users.up.sql:**
```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:**
```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:**
```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:**
```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

```bash
# 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

```go
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

```mermaid
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

    classDef greenClass fill:#27AE60,stroke:#333,stroke-width:2px,color:#fff
    classDef pinkClass fill:#E74C3C,stroke:#333,stroke-width:2px,color:#fff

    class Safe greenClass
    class Unsafe pinkClass
```

**Safe approach:**
```sql
-- 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:

```sql
-- 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

```sql
-- 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

```sql
-- 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

```mermaid
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

```go
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

```go
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

```mermaid
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

```yaml
# .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:

```bash
# 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

```go
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

```mermaid
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

    classDef blueClass fill:#4A90E2,stroke:#333,stroke-width:2px,color:#fff

    class Lifecycle blueClass
```

| 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](https://vanducng.dev/2024/03/29/Building-REST-APIs-with-Go-and-Gin/) - Creating high-performance APIs with the Gin framework.*