Go Database Migration in Practice: Zero-Downtime Schema Version Control with golang-migrate

Go Database Migration: Why Your Project Needs Version Control

Do you manually execute SQL changes on every deployment? Do schema inconsistencies between environments cause production incidents? Are you unsure which changes to roll back when things go wrong? Database Migration is the core solution to these problems. In 2026, golang-migrate has become the most popular migration tool in the Go ecosystem, supporting 15+ databases including MySQL, PostgreSQL, and SQLite. Combined with CI/CD, it enables a full pipeline: migration scripts → version tracking → zero-downtime changes → automatic rollback.

This article walks through 5 core patterns, covering the full pipeline from migration initialization → incremental changes → zero-downtime strategy → rollback mechanism → CI/CD integration.

---

Core Concepts

Concept	Description
Migration	Versioned unit of database schema change
golang-migrate	Go database migration CLI and library
Up Migration	Forward schema change execution
Down Migration	Rollback schema change
Dirty State	Migration execution failure causing database version lock
Zero-Downtime	Schema change strategy without service interruption
Seed Data	Migration scripts for initial data
Idempotent	Migration scripts that can be re-executed without errors

---

Problem Analysis: 5 Challenges in Database Migration

1. Schema drift: Inconsistent database structures across environments
2. Migration failure: Large table ALTER TABLE locks causing service interruption
3. Rollback difficulty: Missing Down migrations or irreversible Down migrations
4. Collaboration conflicts: Multiple developers modifying Schema simultaneously
5. CI integration: Migration scripts not included in automated testing and deployment

---

Step-by-Step: 5 Core Database Migration Patterns

Pattern 1: golang-migrate Installation and Project Initialization

# macOS
brew install golang-migrate

# Linux
curl -L https://github.com/golang-migrate/migrate/releases/download/v4.18.1/migrate.linux-amd64.tar.gz | tar xvz
sudo mv migrate /usr/local/bin/

# Go install
go install -tags 'postgres mysql sqlite' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

migrate create -ext sql -dir db/migrations -seq init_users_table

-- db/migrations/000001_init_users_table.up.sql
CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

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

-- db/migrations/000001_init_users_table.down.sql
DROP INDEX IF EXISTS idx_users_username;
DROP INDEX IF EXISTS idx_users_email;
DROP TABLE IF EXISTS users;

Pattern 2: Go Code Integration and Incremental Changes

// internal/database/migrate.go
package database

import (
	"fmt"
	"log"

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

type Migrator struct {
	migrate *migrate.Migrate
}

func NewMigrator(databaseURL string) (*Migrator, error) {
	m, err := migrate.New("file://db/migrations", databaseURL)
	if err != nil {
		return nil, fmt.Errorf("failed to create migrator: %w", err)
	}
	return &Migrator{migrate: m}, nil
}

func (m *Migrator) Up() error {
	if err := m.migrate.Up(); err != nil && err != migrate.ErrNoChange {
		return fmt.Errorf("migration up failed: %w", err)
	}
	log.Println("Migrations applied successfully")
	return nil
}

func (m *Migrator) Down() error {
	if err := m.migrate.Steps(-1); err != nil && err != migrate.ErrNoChange {
		return fmt.Errorf("migration down failed: %w", err)
	}
	log.Println("Migration rolled back successfully")
	return nil
}

func (m *Migrator) Version() (uint, bool, error) {
	return m.migrate.Version()
}

func (m *Migrator) Force(version int) error {
	if err := m.migrate.Force(version); err != nil {
		return fmt.Errorf("force version failed: %w", err)
	}
	log.Printf("Forced migration version to %d", version)
	return nil
}

Pattern 3: Zero-Downtime Migration Strategy

-- db/migrations/000002_add_phone_column.up.sql
-- Step 1: Add nullable column (no table lock)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- db/migrations/000003_phone_not_null.up.sql
-- Execute after all data backfilled
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
ALTER TABLE users ALTER COLUMN phone SET DEFAULT '';

// internal/database/backfill.go
package database

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"
)

type BackfillConfig struct {
	BatchSize    int
	DelayBetween time.Duration
	DryRun       bool
}

func BackfillPhoneNumbers(ctx context.Context, db *sql.DB, config BackfillConfig) error {
	if config.BatchSize <= 0 {
		config.BatchSize = 1000
	}
	if config.DelayBetween == 0 {
		config.DelayBetween = 100 * time.Millisecond
	}

	var totalUpdated int64

	for {
		var updated int64
		err := db.QueryRowContext(ctx, `
			WITH batch AS (
				SELECT id FROM users WHERE phone IS NULL LIMIT $1
			)
			UPDATE users SET phone = ''
			WHERE id IN (SELECT id FROM batch)
			RETURNING 1
		`, config.BatchSize).Scan(&updated)

		if err != nil {
			return fmt.Errorf("backfill batch failed: %w", err)
		}

		if updated == 0 {
			break
		}

		totalUpdated += updated
		log.Printf("Backfilled %d rows (total: %d)", updated, totalUpdated)

		if config.DryRun {
			log.Println("Dry run mode, stopping after first batch")
			break
		}

		select {
		case <-ctx.Done():
			return ctx.Err()
		case <-time.After(config.DelayBetween):
		}
	}

	log.Printf("Backfill complete. Total rows updated: %d", totalUpdated)
	return nil
}

Pattern 4: Rollback Mechanism and Safety Strategy

// internal/database/safe_migrate.go
package database

import (
	"fmt"
	"log"

	"github.com/golang-migrate/migrate/v4"
)

type SafeMigrator struct {
	migrate *migrate.Migrate
	dryRun  bool
}

func NewSafeMigrator(databaseURL string, dryRun bool) (*SafeMigrator, error) {
	m, err := migrate.New("file://db/migrations", databaseURL)
	if err != nil {
		return nil, fmt.Errorf("failed to create migrator: %w", err)
	}
	return &SafeMigrator{migrate: m, dryRun: dryRun}, nil
}

func (sm *SafeMigrator) SafeUp() error {
	currentVersion, dirty, err := sm.migrate.Version()
	if err != nil && err != migrate.ErrNilVersion {
		return fmt.Errorf("failed to get current version: %w", err)
	}

	if dirty {
		return fmt.Errorf("database is in dirty state at version %d, run force first", currentVersion)
	}

	if sm.dryRun {
		log.Printf("[DRY RUN] Would apply migrations from version %d", currentVersion)
		return nil
	}

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

	newVersion, _, _ := sm.migrate.Version()
	log.Printf("Migrated from version %d to %d", currentVersion, newVersion)
	return nil
}

Pattern 5: CI/CD Integration and Automation

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

on:
  push:
    paths:
      - 'db/migrations/**'
      - 'internal/database/**'

jobs:
  migrate-dry-run:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: test_db
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-go@v5
        with:
          go-version: '1.24'

      - name: Install migrate
        run: go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

      - name: Run migrations
        env:
          DATABASE_URL: postgres://postgres:postgres@localhost:5432/test_db?sslmode=disable
        run: |
          migrate -path db/migrations -database "$DATABASE_URL" up

      - name: Verify rollback
        env:
          DATABASE_URL: postgres://postgres:postgres@localhost:5432/test_db?sslmode=disable
        run: |
          migrate -path db/migrations -database "$DATABASE_URL" down 1
          migrate -path db/migrations -database "$DATABASE_URL" up

---

Pitfall Guide

Pitfall 1: Irreversible Migration Scripts

-- ❌ Wrong: Down migration loses data
ALTER TABLE users DROP COLUMN phone;

-- ✅ Correct: Down migration preserving data
ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;
ALTER TABLE users ALTER COLUMN phone DROP DEFAULT;

Pitfall 2: Direct ALTER TABLE on Large Tables

-- ❌ Wrong: Adding NOT NULL column locks large tables
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT '';

-- ✅ Correct: Multi-step zero-downtime migration
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Backfill data in application layer
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

Pitfall 3: Unhandled Dirty State

# ❌ Wrong: Ignoring dirty state
migrate -path db/migrations -database "$DB_URL" up

# ✅ Correct: Force fix dirty state first
migrate -path db/migrations -database "$DB_URL" force <version>
migrate -path db/migrations -database "$DB_URL" up

Pitfall 4: Database-Specific Syntax in Migrations

-- ❌ Wrong: PostgreSQL-specific syntax
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

-- ✅ Correct: Use standard SQL or conditional compilation
-- PostgreSQL: ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
-- MySQL: ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NOT NULL;

Pitfall 5: Migration and Code Deployment Out of Sync

# ❌ Wrong: Deploy code first, then migrate
kubectl apply -f deployment.yaml
migrate -path db/migrations up

# ✅ Correct: Migrate first, then deploy code
migrate -path db/migrations up
kubectl apply -f deployment.yaml

---

Error Troubleshooting

#	Error Message	Cause	Solution
1	no change	Database already at latest version	Normal, no action needed
2	dirty database	Migration interrupted	Use force command to fix version
3	syntax error in migration	SQL syntax error	Check migration file SQL syntax
4	migration locked	Concurrent migration execution	Ensure only one migration process at a time
5	column already exists	Duplicate migration execution	Check version number
6	permission denied	Insufficient database user permissions	Grant CREATE/ALTER permissions
7	connection refused	Database connection failure	Check DATABASE_URL and database status
8	foreign key violation	Foreign key constraint blocking operation	Handle related data first
9	lock timeout	Large table DDL lock timeout	Use zero-downtime strategy
10	migration file not found	Migration file path error	Check migrate -path parameter

---

Advanced Optimization

1. Migration testing: Verify each migration with up→down→up in CI
2. Migration linter: Use sqlfluff or custom tools to check migration script standards
3. Multi-database support: Use build tags for different database migration files
4. Data backfill monitoring: Track progress during large table backfill with resume support
5. Blue-green deployment integration: Coordinate migrations with blue-green deployment for true zero-downtime releases

---

Comparison

Dimension	golang-migrate	Goose	Atlas	Liquibase
Go native	⭐⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐	⭐
Multi-database	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐⭐
Declarative migration	⭐⭐	⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐
Version tracking	⭐⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐⭐
Dirty fix	⭐⭐⭐	⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐
CI integration	⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐
Learning curve	Low	Low	Medium	High

---

Summary: Database migration is essential for production Go projects. golang-migrate is the top choice in 2026 with its clean CLI, Go native integration, and multi-database support. Core principles: migrate before code deployment, Down migrations must be reversible, large table changes in steps, CI/CD auto-verification.

---

Recommended Online Tools

• JSON Formatter: /en/json/format
• Hash Calculator: /en/encode/hash
• cURL to Code: /en/dev/curl-to-code