DEV Community

Huseyn
Huseyn

Posted on

Database Schema Migration Cheatsheet with golang-migrate/migrate

#go #backend #backenddevelopment

This cheatsheet provides a concise guide to using the golang-migrate/migrate library for database schema migrations. It covers installation, file structure, migration commands (up, down, force down), and best practices.

1. Overview

  • What is golang-migrate/migrate?

    • A CLI tool and Go library for managing database schema migrations.
    • Supports incremental, version-controlled, and reversible changes to database schemas.
    • Compatible with databases like PostgreSQL, MySQL, SQLite, and more.
    • Migrations are written as SQL files (or Go code for advanced cases).

  • Key Concepts

    • Up Migration: Applies changes to evolve the schema (e.g., create tables, add columns).
    • Down Migration: Reverts changes to roll back the schema (e.g., drop tables, remove columns).
    • Force Down: Resolves issues with a "dirty" database by forcing a specific version.

2. Installation

Install the migrate CLI tool for your operating system.

Prerequisites

  • Go installed (for Go-based CLI compilation, optional).
  • A supported database (e.g., PostgreSQL, MySQL) with connection details.

Install via Package Managers

  • MacOS (Homebrew): 
  brew install golang-migrate
Enter fullscreen mode Exit fullscreen mode
  • Linux (via curl): 
  curl -L https://github.com/golang-migrate/migrate/releases/download/v4.17.1/migrate.linux-amd64.tar.gz | tar xvz
  sudo mv migrate /usr/local/bin/
Enter fullscreen mode Exit fullscreen mode
  • Windows (Scoop): 
  scoop install migrate
Enter fullscreen mode Exit fullscreen mode
  • Verify Installation: 
  migrate -version
Enter fullscreen mode Exit fullscreen mode

Install via Go (Optional)

If you prefer to build from source or use as a library:

go get -u github.com/golang-migrate/migrate/v4
Enter fullscreen mode Exit fullscreen mode

3. File Structure

Organize migration files in a dedicated directory for version control and clarity.

Recommended Structure 

project/
├── database/
│   ├── migrations/
│   │   ├── 202507120001_create_users_table.up.sql
│   │   ├── 202507120001_create_users_table.down.sql
│   │   ├── 202507120002_add_email_index.up.sql
│   │   ├── 202507120002_add_email_index.down.sql
│   └── schema.sql  (optional: schema dump)
├── main.go
└── Makefile  (optional: for migration shortcuts)
Enter fullscreen mode Exit fullscreen mode
  • Naming Convention: Files are named <timestamp>_<description>.<up/down>.sql.
    • Example: 202507120001_create_users_table.up.sql for creating a table.
    • Example: 202507120001_create_users_table.down.sql for dropping it.
  • Directory Path: Typically database/migrations/ (relative to project root).

Example Migration Files

  • 202507120001_create_users_table.up.sql: 
  -- Create users table
  CREATE TABLE users (
      id SERIAL PRIMARY KEY,
      username VARCHAR(50) NOT NULL,
      email VARCHAR(100) NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );
Enter fullscreen mode Exit fullscreen mode
  • 202507120001_create_users_table.down.sql: 
  -- Drop users table
  DROP TABLE IF EXISTS users;
Enter fullscreen mode Exit fullscreen mode
  • 202507120002_add_email_index.up.sql: 
  -- Add index on email column
  CREATE INDEX idx_email ON users(email);
Enter fullscreen mode Exit fullscreen mode
  • 202507120002_add_email_index.down.sql: 
  -- Drop index on email column
  DROP INDEX IF EXISTS idx_email;
Enter fullscreen mode Exit fullscreen mode

4. Usage

Run migrations using the migrate CLI with a database connection string and migration path.

Basic Commands

  • Create a New Migration: 
  migrate create -ext sql -dir database/migrations -seq create_table_name
Enter fullscreen mode Exit fullscreen mode
  • -ext sql: Specifies SQL file extension.
  • -dir: Path to migration directory.
  • -seq: Generates a sequential timestamp.

  • Example output: Creates database/migrations/202507120003_create_table_name.up.sql and .down.sql.

    • Apply Migrations (Up): 
  migrate -path database/migrations/ -database "postgresql://username:password@localhost:5432/dbname?sslmode=disable" -verbose up
Enter fullscreen mode Exit fullscreen mode
  • Applies all pending migrations in order.
  • -path: Directory containing migration files.
  • -database: Database connection string (replace with your DB details).

  • -verbose: Shows detailed output.

    • Revert Migrations (Down): 
  migrate -path database/migrations/ -database "postgresql://username:password@localhost:5432/dbname?sslmode=disable" -verbose down
Enter fullscreen mode Exit fullscreen mode
  • Reverts the last applied migration.

  • Use down N to revert the last N migrations (e.g., down 2).

    • Force Down (Fix Dirty Database): 
  migrate -path database/migrations/ -database "postgresql://username:password@localhost:5432/dbname?sslmode=disable" force VERSION
Enter fullscreen mode Exit fullscreen mode
  • Use when a migration fails and marks the database as "dirty," preventing further migrations.
  • Replace VERSION with the desired migration version (e.g., 202507120001).
  • Caution: Ensure the database state matches the forced version to avoid inconsistencies.

  • Example: If migration 202507120002 fails, fix the error in the .up.sql file, then run:

    migrate -path database/migrations/ -database "postgresql://username:password@localhost:5432/dbname?sslmode=disable" force 202507120001

    This sets the database to version 202507120001, clearing the dirty state.

    • Check Migration Status: 
  migrate -path database/migrations/ -database "postgresql://username:password@localhost:5432/dbname?sslmode=disable" version
Enter fullscreen mode Exit fullscreen mode
  • Displays the current schema version.

Using with a Makefile (Optional)

Simplify commands with a Makefile:

# Makefile
DB_URL=postgresql://username:password@localhost:5432/dbname?sslmode=disable

migration_up:
    migrate -path database/migrations/ -database "$(DB_URL)" -verbose up

migration_down:
    migrate -path database/migrations/ -database "$(DB_URL)" -verbose down

migration_force:
    migrate -path database/migrations/ -database "$(DB_URL)" force $(VERSION)
Enter fullscreen mode Exit fullscreen mode
  • Run: make migration_up, make migration_down, or make migration_force VERSION=202507120001.

5. Best Practices

  • Version Control: Commit migration files to your repository (e.g., Git) for team collaboration.
  • Test Migrations: Test migrations in a staging environment before applying to production.
  • Reversible Migrations: Always define down migrations to allow rollbacks.
  • Avoid Destructive Changes: Use non-destructive changes (e.g., add nullable columns) to minimize data loss risks.
  • Backup Database: Always back up your database before running migrations.
  • Handle Dirty Databases:
    • If a migration fails, check the error, fix the .up.sql or .down.sql file, and use force VERSION cautiously.
    • Verify the database state matches the forced version.

6. Common Issues and Solutions

  • Error: Dirty database version X. Fix and force version:
    • Cause: A migration failed, leaving the database in a "dirty" state.
    • Solution: Fix the problematic migration file, then run migrate force X to set the correct version.
  • Migration order issues: Ensure migration files are applied sequentially based on timestamps.
  • Connection errors: Verify the database connection string and ensure the database is running.

7. Example Workflow

  1. Create a migration: 
   migrate create -ext sql -dir database/migrations -seq add_posts_table
Enter fullscreen mode Exit fullscreen mode
  1. Edit database/migrations/202507120003_add_posts_table.up.sql: 
   -- Create posts table
   CREATE TABLE posts (
       id SERIAL PRIMARY KEY,
       user_id INT REFERENCES users(id),
       title VARCHAR(255) NOT NULL
   );
Enter fullscreen mode Exit fullscreen mode
  1. Edit database/migrations/202507120003_add_posts_table.down.sql: 
   -- Drop posts table
   DROP TABLE IF EXISTS posts;
Enter fullscreen mode Exit fullscreen mode
  1. Apply the migration: 
   migrate -path database/migrations/ -database "postgresql://username:password@localhost:5432/dbname?sslmode=disable" up
Enter fullscreen mode Exit fullscreen mode
  1. Revert if needed: 
   migrate -path database/migrations/ -database "postgresql://username:password@localhost:5432/dbname?sslmode=disable" down
Enter fullscreen mode Exit fullscreen mode

  1. If migration fails and database is dirty:

    • Fix the error in the migration file.
    • Force to the last successful version: 
     migrate -path database/migrations/ -database "postgresql://username:password@localhost:5432/dbname?sslmode=disable" force 202507120002

8. Additional Resources

  • Official GitHub: golang-migrate/migrate
  • Supported Databases: PostgreSQL, MySQL, MariaDB, SQLite, SQL Server, and more.
  • CLI Documentation: Run migrate -help for full command details.

Top comments (0)