# 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](https://github.com/golang-migrate/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. [ad-logs] ## Understanding database migrations ![ChatGPT Image Apr 28, 2025, 07_39_49 AM.png](https://imagedelivery.net/xZXo0QFi-1_4Zimer-T0XQ/aa97c1c5-4110-4826-7ff8-b360e12fc700/lg2x =1536x1024) 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](https://github.com/golang-migrate/migrate/tree/master/cmd/migrate). If you already have Go installed, you can use the following command regardless of the operating system: ```command 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: ```command 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: ```command 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: ```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: ```sql [label database/migrations/000001_create_users_table.up.sql] 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: ```sql [label database/migrations/000001_create_users_table.down.sql] DROP TABLE IF EXISTS users; ``` Let's add another migration to add a phone number column: ```command 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: ```sql [label database/migrations/000002_add_phone_to_users.up.sql] ALTER TABLE users ADD COLUMN phone VARCHAR(20); ``` ```sql [label database/migrations/000002_add_phone_to_users.down.sql] 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: ```command migrate -path ./database/migrations -database "postgresql://:@localhost:5432/?sslmode=disable" up ``` You should see the following output: ```text [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: ```command migrate -path ./database/migrations -database "postgresql://:@localhost:5432/?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: ```sql SELECT * FROM schema_migrations; ``` This should show a list of all applied migrations with their version numbers: ![1.png](https://imagedelivery.net/xZXo0QFi-1_4Zimer-T0XQ/7e6b31fb-d9c0-4b92-e5dd-c7cf59643700/orig =770x272) You can also verify the actual table structure to ensure it matches your expectations: ```sql \d+ users DESCRIBE TABLE ``` Output might look like: ```text [output] 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 ``` ![2.png](https://imagedelivery.net/xZXo0QFi-1_4Zimer-T0XQ/5c40bcce-d00f-4fe1-a4f4-7abe1b550000/md1x =1910x502) ## 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 ```command migrate -path ./database/migrations -database "" version ``` This will output the current migration version of your database: ```text [output] 2 ``` To roll back the most recent migration: ```command migrate -path ./database/migrations -database "" down 1 ``` The number `1` indicates how many migrations to roll back. To roll back all migrations, don't specify a number: ```command migrate -path ./database/migrations -database "" 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: ```text [output] 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: ```command migrate -path ./database/migrations -database "" 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: ```go [label Running migrations from Go code] 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: ```go 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](https://github.com/pressly/goose), which offers a higher-level API: ```go 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: ```yaml 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: ```yaml 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.