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:
Keep migrations small and focused: Each migration should do one thing and do it well.
Make migrations idempotent when possible: Use conditionals like
IF NOT EXISTS
to prevent errors if the migration is run multiple times.Always test migrations before applying them to production: Run the up and down migrations in a test environment to ensure they work as expected.
Include descriptive names: Use clear, descriptive names that indicate what the migration does.
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:
- Fix the issue in your migration files.
- Determine the correct version your database should be at.
- 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:
Sequential versioning (with
-seq
flag): Generates migration files with sequential version numbers like000001
,000002
, etc.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.
Make your mark
Join the writer's program
Are you a developer and love writing and sharing your knowledge with the world? Join our guest writing program and get paid for writing amazing technical guides. We'll get them to the right readers that will appreciate them.
Write for us
Build on top of Better Stack
Write a script, app or project on top of Better Stack and share it with the world. Make a public repository and share it with us at our email.
community@betterstack.comor submit a pull request and help us build better products for everyone.
See the full list of amazing projects on github