Database migrations in Go with golang-migrate

Database migrations are essential for maintaining and evolving database schemas over time in a controlled and systematic way.

As applications grow, databases need to adapt to changing requirements, and having a reliable migration system ensures these changes are applied consistently across all environments.

In the Go ecosystem, golang-migrate has emerged as a powerful and flexible tool for managing database migrations.

This article will guide you through the fundamentals of using golang-migrate to manage your database schema changes effectively.

You'll learn how to set up, create, and run migrations, as well as handle common migration scenarios like rollbacks and error recovery. By the end of this article, you'll have a solid foundation for implementing database migrations in your Go projects.

Understanding database migrations

Database migrations represent a sequence of changes applied to a database schema to transition it from one state to another. They enable developers to evolve database structures in a predictable, repeatable manner while preserving existing data.

The concept is similar to version control for your database schema. Instead of making manual changes directly to production databases (a risky and error-prone approach), migrations provide a systematic way to track and apply changes across development, testing, and production environments.

Using migrations offers several key advantages:

Version control for database schema

Each migration represents a specific change to your database schema, with a unique version identifier. This creates a clear history of how your database has evolved over time. When you track migrations alongside your application code in a version control system, you can easily understand when and why particular changes were made.

Rollback capabilities

One of the most valuable features of a proper migration system is the ability to undo changes. Each migration typically includes both "up" operations (to apply changes) and "down" operations (to revert them). If a newly deployed feature causes issues, you can roll back to a previous database state with confidence.

Automation and CI/CD integration

Migrations can be automated and integrated into your continuous integration and deployment pipelines. This ensures that database changes are applied consistently alongside code changes, reducing the risk of deployment failures due to schema mismatches.

Consistency across environments

Migration tools help maintain consistency across various environments, from development machines to staging and production servers. This eliminates the common problem of "it works on my machine" by ensuring everyone is working with the same database structure.

Setting up golang-migrate

Golang-migrate is a database migration tool written in Go that supports multiple database types. It provides both a CLI tool for manual operation and a library that can be embedded in your Go applications.

You can install the golang-migrate CLI tool using several methods. If you already have Go installed, you can use the following command regardless of the operating system:

go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@v4.18.3

Ensure to replace postgres with the appropriate database tag(s) for the desired databases.

Alternatively, you can use the Docker image, which is particularly useful for CI/CD environments:

docker run -v $(pwd)/migrations:/migrations --network host migrate/migrate -path=/migrations -database "postgresql://user:password@localhost:5432/mydatabase?sslmode=disable" up

Once installed, create a dedicated directory to store your migration files. A common practice is to use a path like ./database/migrations/ within your project:

mkdir -p ./database/migrations

This directory will contain all your migration files, organized by version and operation type.

Creating migrations

Creating a new migration involves generating two SQL files: one for applying changes (up) and another for reverting them (down).

To create a new migration, use the create command:

migrate create -ext sql -dir ./database/migrations -seq create_users_table

This command creates two files:

• ./database/migrations/000001_create_users_table.up.sql
• ./database/migrations/000001_create_users_table.down.sql

Let's break down the command:

• -ext sql: Specifies the file extension (sql).
• -dir ./database/migrations: Specifies the directory for migration files.
• -seq: Generates sequential version numbers.
• create_users_table: The descriptive name for the migration.

The .up.sql file contains the statements needed to apply the change, while the .down.sql file contains the statements to revert it. Both files start empty and need to be populated with the appropriate SQL commands.

For example, to create a users table:

CREATE TABLE users (
 id UUID PRIMARY KEY,
 name VARCHAR(255) NOT NULL,
 email VARCHAR(255) NOT NULL UNIQUE,
 created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
 updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

And the corresponding down migration to drop the table:

DROP TABLE IF EXISTS users;

Let's add another migration to add a phone number column:

migrate create -ext sql -dir ./database/migrations -seq add_phone_to_users

This creates:

• ./database/migrations/000002_add_phone_to_users.up.sql
• ./database/migrations/000002_add_phone_to_users.down.sql

Now populate these files:

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

ALTER TABLE users DROP COLUMN IF EXISTS phone;

When writing migration scripts, follow these practices:

1. Keep migrations small and focused: Each migration should do one thing and do it well.

2. Make migrations idempotent when possible: Use conditionals like IF NOT EXISTS to prevent errors if the migration is run multiple times.

3. Always test migrations before applying them to production: Run the up and down migrations in a test environment to ensure they work as expected.

4. Include descriptive names: Use clear, descriptive names that indicate what the migration does.

5. Add comments: Document complex migrations with SQL comments to explain what's happening and why.

Running migrations with the migrate CLI

Once you've created your migration files, you need to run them against your database. To apply all pending migrations, run the command below after updating the connection string with the right credentials for your database:

migrate -path ./database/migrations -database "postgresql://<username>:<password>@localhost:5432/<mydatabase>?sslmode=disable" up

You should see the following output:

1/u create_users_table (13.398427ms)
2/u add_phone_to_users (22.337789ms)

To apply only a specific number of migrations, use:

migrate -path ./database/migrations -database "postgresql://<username>:<password>@localhost:5432/<mydatabase>?sslmode=disable" up 2

The command above would apply just the next two pending migrations.

After running migrations, it's important to verify they were applied correctly. One way is to check the schema_migrations table that golang-migrate creates automatically:

SELECT * FROM schema_migrations;

This should show a list of all applied migrations with their version numbers:

You can also verify the actual table structure to ensure it matches your expectations:

\d+ users DESCRIBE TABLE

Output might look like:

                                                    Table "public.users"
   Column   |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id         | uuid                     |           | not null |         | plain    |             |              |
 name       | character varying(255)   |           | not null |         | extended |             |              |
 email      | character varying(255)   |           | not null |         | extended |             |              |
 created_at | timestamp with time zone |           |          | now()   | plain    |             |              |
 updated_at | timestamp with time zone |           |          | now()   | plain    |             |              |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
Access method: heap

Managing migrations

Over time, you'll need to manage your migrations beyond just applying them. This includes checking status, rolling back, and handling errors.

To check which migrations have been applied and which are pending, run

migrate -path ./database/migrations -database "<connection-string>" version

This will output the current migration version of your database:

2

To roll back the most recent migration:

migrate -path ./database/migrations -database "<connection-string>" down 1

The number 1 indicates how many migrations to roll back. To roll back all migrations, don't specify a number:

migrate -path ./database/migrations -database "<connection-string>" down

Note that rolling back all migrations will completely revert your database schema, which can lead to data loss. Use this command with extreme care, especially in production environments.

Handling migration errors

If a migration fails, golang-migrate marks the database as "dirty" to prevent further migrations until the issue is resolved. You might see an error like:

error: Dirty database version 3. Fix and force version.

To resolve this:

1. Fix the issue in your migration files.
2. Determine the correct version your database should be at.
3. Use the force command to set the version:

migrate -path ./database/migrations -database "<connection-string>" force 2

This tells golang-migrate that your database is at version 2, allowing you to continue with migrations.

Migration versioning strategies

Golang-migrate supports two versioning strategies:

1. Sequential versioning (with -seq flag): Generates migration files with sequential version numbers like 000001, 000002, etc.

2. Timestamp-based versioning (default): Uses Unix timestamps as version numbers, like 1611595022.

Sequential versioning makes it easier to understand the order of migrations at a glance, while timestamp-based versioning eliminates version conflicts when multiple developers create migrations simultaneously.

Integration with Go applications

While the CLI is useful for manual operations, you can also integrate migrations directly into your Go applications using the golang-migrate package:

package main

import (
   "log"
   "os"

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

func main() {
   dbDSN := os.Getenv("DATABASE_DSN")
   if dbDSN == "" {
       log.Fatal("DATABASE_DSN environment variable not set")
   }

   m, err := migrate.New(
       "file://./database/migrations",
       dbDSN,
   )
   if err != nil {
       log.Fatalf("Migration failed to initialize: %v", err)
   }

   if err := m.Up(); err != nil && err != migrate.ErrNoChange {
       log.Fatalf("Failed to apply migrations: %v", err)
   }

   log.Println("Migrations applied successfully!")
}

Note that you need to import the specific database driver you're using (postgres in this example) and the source type (file in this example).

Automating migrations during application startup

A common pattern is to run migrations automatically when your application starts. This ensures your database schema is always up to date:

package main

import (
   "log"
   "os"

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

func main() {
   // Run migrations first
   if err := runMigrations(); err != nil {
       log.Fatalf("Failed to run migrations: %v", err)
   }

   // Then start your application
   startApplication()
}

func runMigrations() error {
   dbDSN := os.Getenv("DATABASE_DSN")
   if dbDSN == "" {
       return fmt.Errorf("DATABASE_DSN environment variable not set")
   }

   m, err := migrate.New(
       "file://./database/migrations",
       dbDSN,
   )
   if err != nil {
       return fmt.Errorf("failed to initialize migrations: %v", err)
   }

   if err := m.Up(); err != nil && err != migrate.ErrNoChange {
       return fmt.Errorf("failed to apply migrations: %v", err)
   }

   log.Println("Migrations applied successfully!")
   return nil
}

func startApplication() {
   // Your application code here
   log.Println("Application started")
}

Using wrapper libraries

Several wrapper libraries make it easier to use golang-migrate in Go applications. One popular option is Goose, which offers a higher-level API:

package main

import (
   "database/sql"
   "log"
   "os"

   "github.com/pressly/goose/v3"
   _ "github.com/lib/pq"
)

func main() {
   dbDSN := os.Getenv("DATABASE_DSN")
   if dbDSN == "" {
       log.Fatal("DATABASE_DSN environment variable not set")
   }

   // Open database connection
   db, err := sql.Open("postgres", dbDSN)
   if err != nil {
       log.Fatalf("Failed to connect to database: %v", err)
   }
   defer db.Close()

   // Set up goose
   goose.SetDialect("postgres")

   // Run migrations
   if err := goose.Up(db, "./database/migrations"); err != nil {
       log.Fatalf("Failed to apply migrations: %v", err)
   }

   log.Println("Migrations applied successfully!")
}

CI/CD integration

Integrating migrations into your CI/CD pipeline helps ensure database changes are consistently applied across environments.

Here's a simple example of how you might integrate migrations into a GitHub Actions workflow:

name: Deploy with Migrations

on:
 push:
   branches: [ main ]

jobs:
 deploy:
   runs-on: ubuntu-latest

   steps:
   - uses: actions/checkout@v3

   - name: Set up Go
     uses: actions/setup-go@v3
     with:
       go-version: '1.19'

   - name: Install migrate
     run: |
       curl -L https://github.com/golang-migrate/migrate/releases/download/v4.15.2/migrate.linux-amd64.tar.gz | tar xvz
       sudo mv migrate /usr/local/bin/migrate

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

   - name: Deploy application
     run: |
       # Your deployment commands here

Before deploying to production, it's important to test migrations in a CI environment:

name: Test Migrations

on:
 pull_request:
   branches: [ main ]

jobs:
 test-migrations:
   runs-on: ubuntu-latest

   services:
     postgres:
       image: postgres:14
       env:
         POSTGRES_USER: postgres
         POSTGRES_PASSWORD: postgres
         POSTGRES_DB: testdb
       ports:
         - 5432:5432
       options: >-
         --health-cmd pg_isready
         --health-interval 10s
         --health-timeout 5s
         --health-retries 5

   steps:
   - uses: actions/checkout@v3

   - name: Install migrate
     run: |
       curl -L https://github.com/golang-migrate/migrate/releases/download/v4.15.2/migrate.linux-amd64.tar.gz | tar xvz
       sudo mv migrate /usr/local/bin/migrate

   - name: Run migrations up
     run: |
       migrate -path ./database/migrations -database "postgresql://postgres:postgres@localhost:5432/testdb?sslmode=disable" up

   - name: Run migrations down
     run: |
       migrate -path ./database/migrations -database "postgresql://postgres:postgres@localhost:5432/testdb?sslmode=disable" down -all

This workflow sets up a PostgreSQL database service, applies all migrations, then rolls them back to ensure both operations work correctly.

Final thoughts

Database migrations are an essential part of modern application development, providing a structured way to evolve your database schema alongside your application code. Golang-migrate offers a robust and flexible solution for managing these migrations, whether you're working with a small project or a large enterprise application.

By implementing proper migration practices, you can ensure database changes are applied consistently across all environments, reduce the risk of errors and data loss, and maintain a clear history of how your database has evolved over time. When combined with CI/CD pipelines, migrations become a powerful tool for maintaining database integrity throughout your development process.

Remember that the best migrations are small, focused, and reversible. Take the time to plan your database changes carefully, and always test migrations thoroughly before applying them to production environments. With these principles in mind, you'll be well-equipped to manage database schema changes with confidence.

Got an article suggestion? Let us know

Next article

Dockerizing Go Applications: A Step-by-Step Guide

Learn how to run Go applications confidently within Docker containers either locally or on your chosen deployment platform

→

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.