Mastering Database Migrations in Golang with Goose

As a backend developer, managing database schema changes is a critical task. In the Go ecosystem, goose is a popular and powerful tool for managing database migrations. This guide will walk you through everything you need to know to get started with Goose, from installation to advanced usage.

What is Goose?

Goose is a database migration tool for Go. It allows you to manage your database schema by writing migration files in either SQL or Go. These files define the changes to be applied to the database, and Goose provides a simple command-line interface to apply, rollback, and check the status of these migrations.

Installation

First things first, you need to install the goose binary. The easiest way is to use go install:

go install github.com/pressly/goose/v3/cmd/goose@latest

This command will download and install the goose binary into your $GOPATH/bin directory. Make sure that this directory is included in your system’s PATH environment variable so you can run goose from any terminal session.

Project Setup

In your Go project, it’s a good practice to keep your migration files organized in a dedicated directory. A common convention is to create a db/migrations folder:

mkdir -p db/migrations

Configuration

To avoid passing the same arguments to every command, Goose can be configured using environment variables or a .env file. This is the recommended way to work with Goose.

Using a .env File

Goose has built-in support for .env files. By default, it will look for a .env file in the current directory. You can define the following variables in your .env file:

# .env

# The database driver (e.g., postgres, mysql, sqlite3)
GOOSE_DRIVER=postgres

# The database connection string
GOOSE_DBSTRING="user=user password=password dbname=testdb sslmode=disable"

# The directory where your migration files are located
GOOSE_MIGRATION_DIR=./db/migrations

With this configuration, you no longer need to specify the driver, connection string, or migration directory in your commands.

Using Environment Variables

Alternatively, you can set these as system-wide environment variables. For example, in a bash shell:

export GOOSE_DRIVER=postgres
export GOOSE_DBSTRING="user=user password=password dbname=testdb sslmode=disable"
export GOOSE_MIGRATION_DIR=./db/migrations

Creating Migrations

Once you have your configuration in place, creating migrations becomes even simpler. Goose supports migrations written in both raw SQL and Go.

Organizing Schema and Data Migrations

A great way to keep your database changes organized is to distinguish between schema migrations (like CREATE TABLE) and data migrations (like INSERT or UPDATE). You can do this with a simple naming convention.

• Schema Migrations: For changing the database structure. Use .sql files for this.
• Data Migrations: For adding or changing data. This often requires logic, making .go files a good choice.

By prefixing the migration names with schema_create_ or data_populate_, their purpose becomes clear just from the filename.

SQL Migrations (for Schema)

For most schema changes, SQL migrations are sufficient and easy to write. To create a new SQL migration file for a schema change, run the following command:

goose create schema_create_users_table sql

This will generate a file like 20250912120000_schema_create_users_table.sql. The timestamp prefix ensures that migrations are applied in the correct order.

Inside this file, you’ll use special comments to define the up and down migrations:

-- +goose Up
-- SQL in this section is executed when the migration is applied.
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- +goose Down
-- SQL in this section is executed when the migration is rolled back.
DROP TABLE users;

The +goose Up section contains the SQL to apply the migration, while the +goose Down section contains the SQL to revert it.

Go Migrations (for Data)

For more complex scenarios, such as seeding initial data or transforming existing data, you can use Go migrations.

To create a Go migration file for a data change, run:

goose create data_populate_initial_roles go

This will create a file like 20250912120100_data_populate_initial_roles.go. The structure of a Go migration file looks like this:

package migrations

import (
	"database/sql"
	"github.com/pressly/goose/v3"
)

func init() {
	goose.AddMigration(upDataPopulateInitialRoles, downDataPopulateInitialRoles)
}

func upDataPopulateInitialRoles(tx *sql.Tx) error {
	// This code is executed when the migration is applied.
	_, err := tx.Exec("INSERT INTO roles (name) VALUES ('admin'), ('user');")
	if err != nil {
		return err
	}
	return nil
}

func downDataPopulateInitialRoles(tx *sql.Tx) error {
	// This code is executed when the migration is rolled back.
	_, err := tx.Exec("DELETE FROM roles WHERE name IN ('admin', 'user');")
	if err != nil {
		return err
	}
	return nil
}

Goose Commands

With your configuration in place, the Goose commands are clean and concise.

status

The status command shows you the current state of your migrations. It tells you which migrations have been applied and which are pending.

goose status

up

The up command applies all pending migrations.

goose up

You can also apply migrations up to a specific version:

goose up-to 20250912120000

down

The down command rolls back the most recently applied migration.

goose down

redo

The redo command is a convenient way to roll back the most recent migration and then apply it again. This is useful when you are developing and testing a migration.

goose redo

Conclusion

Goose is an essential tool for any Go developer working with databases. It provides a simple and effective way to manage your database schema, ensuring that your changes are consistent and reversible. By incorporating Goose into your development workflow, you can bring more structure and discipline to your database management process.