Database Programming

Database Migrations in Go with Goose: Incremental SQL Guide

Manage Postgres database migrations in Go with goose v3: install the CLI, write incremental SQL migrations, run up and down, and embed them with embed.FS.

Database schema migration is the management of incremental, reversible changes and version control to relational database schemas. The migrations basically track granular changes to your database schema which are reflected as separate scripted files.

Golang Goose is a database migration tool. It is both a CLI and a Go library, and it allows you to manage your database schema by creating incremental SQL changes or Go functions. It works with Postgres, MySQL/MariaDB, SQLite, ClickHouse, MSSQL, Spanner and more.

In this guide we will use goose v3 (the module path is github.com/pressly/goose/v3) against a Postgres database. If you need a database to follow along, check out how to run Postgres with Docker and Docker Compose and how to create a Postgres user and database.

Installation

If golang is installed locally, use this command to install goose:

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

This will install the goose binary to your $GOPATH/bin directory (usually ~/go/bin), so make sure that directory is in your $PATH. Recent goose releases require a recent Go toolchain (the current go.mod declares Go 1.26), so update Go first if go install complains. See how to install Go on Fedora and Rocky Linux if you need it.

For a lite version of the binary without the database drivers you don’t need, clone the code then use the exclusion build tags:

git clone https://github.com/pressly/goose.git
cd goose
go build -tags='no_postgres no_mysql no_sqlite3 no_ydb' -o goose ./cmd/goose

The available tags are no_azuresql, no_clickhouse, no_libsql, no_mssql, no_mysql, no_postgres, no_sqlite3, no_vertica and no_ydb. Leave out the tag for the driver you actually use (no_postgres in this guide).

Older versions of this guide used go build -i. The -i flag was removed from modern Go, so drop it.

Or go to the releases page here https://github.com/pressly/goose/releases and download the latest binary for your OS then move it to a directory in your $PATH.

For macOS users goose is available as a Homebrew Formulae:

brew install goose

Once installed, verify version

$ goose -version

goose version: v3.29.0

Using Goose

Goose provides functionality to Create migrations, apply them, undo migrations and check the status of the migration. In this guide we are going to focus on SQL migrations

Usage: goose DRIVER DBSTRING [OPTIONS] COMMAND

or

Set environment key

GOOSE_DRIVER=DRIVER
GOOSE_DBSTRING=DBSTRING
GOOSE_MIGRATION_DIR=MIGRATION_DIR

Usage: goose [OPTIONS] COMMAND

Some of the commonly used options:

  • -dir - directory with migration files (defaults to ., can also be set with GOOSE_MIGRATION_DIR).
  • -table - migrations table name (defaults to goose_db_version, can also be set with GOOSE_TABLE).
  • -s - use sequential numbering (00001_...) instead of timestamps for new migrations.
  • -allow-missing - apply missing (out-of-order) migrations.
  • -env - load environment variables from a file (defaults to .env, use -env=none to disable).

Run goose -h to see the full list of commands and options.

Creating migration

To create new file for SQL migration, use this command

$ goose create add_users_table sql

2021/10/18 21:52:11 Created new file: 20211018215211_add_users_table.sql

This will create migrations in the current directory. If you want to create the migrations in a separate directory please supply the -dir migrations argument (or set GOOSE_MIGRATION_DIR).

The generated file contains placeholder SELECT 'up SQL query'; and SELECT 'down SQL query'; statements. Edit the newly created file and replace them to define the behavior of your migration. Let us add content to create a users table in our case. For the action up, we are going to create a users table and add unique indexes for the email and username fields.

-- +goose Up
create table users (
  id bigserial primary key,
  username varchar(255) not null,
  names varchar(225) not null,
  email varchar(255) not null,
  password_hash varchar(255) not null,
  created_at timestamptz not null default clock_timestamp(),
  updated_at timestamptz
);

create unique index users_email_uniq_idx ON users(LOWER(email));
create unique index users_username_uniq_idx ON users(LOWER(username));

-- +goose Down
drop index if exists users_username_uniq_idx;
drop index if exists users_email_uniq_idx;

drop table if exists users;

The unique indexes on LOWER(email) and LOWER(username) are also used for lookups, so a second, non-unique index on the same expression would only add write overhead.

A few rules to keep in mind:

  • Each file must have exactly one -- +goose Up annotation, and it must come before the optional -- +goose Down.
  • Every statement must end with a semicolon.
  • Each migration runs in a transaction by default. Add -- +goose NO TRANSACTION at the top of the file for statements that can’t run in one, like CREATE INDEX CONCURRENTLY.
  • Wrap statements that contain semicolons themselves (for example PL/pgSQL functions) in -- +goose StatementBegin and -- +goose StatementEnd.

Let’s create another migration file to add posts

$ goose create add_posts_table sql

2021/10/18 22:04:33 Created new file: 20211018220433_add_posts_table.sql

Then add the content for up and down

-- +goose Up
create table posts (
  id bigserial primary key,
  user_id bigint not null references users(id),
  slug varchar(255) not null,
  title varchar(100) not null,
  description varchar(255),
  content text not null,
  created_at timestamptz not null default clock_timestamp(),
  updated_at timestamptz
);

create index posts_user_id_idx ON posts(user_id);

-- +goose Down
drop index if exists posts_user_id_idx;

drop table if exists posts;

Now we have two migration files

$ ls

20211018215211_add_users_table.sql
20211018220433_add_posts_table.sql

Applying migrations

Exporting variables

Before proceeding, we have to declare the database to use and the connection string to the database. Goose reads the GOOSE_DRIVER and GOOSE_DBSTRING environment variables for the database driver and database connection string respectively. Use these export commands to define

export GOOSE_DRIVER=postgres
export GOOSE_DBSTRING="user=citizix_user password=pcDFBXo5yX host=10.2.11.10 dbname=citizix_app sslmode=disable"

A URL style connection string such as postgres://citizix_user:[email protected]:5432/citizix_app?sslmode=disable works too.

Alternatively, put the same variables in a .env file in the directory you run goose from. Goose loads it by default:

GOOSE_DRIVER=postgres
GOOSE_DBSTRING=postgres://citizix_user:[email protected]:5432/citizix_app?sslmode=disable
GOOSE_MIGRATION_DIR=./migrations

Keep .env out of version control since it contains the database password. Run goose env to print the configuration goose has picked up.

status

Now that we have some files, we can check the state of our migrations using the goose status command:

$ goose status

2021/10/19 21:45:30     Applied At                  Migration
2021/10/19 21:45:30     =======================================
2021/10/19 21:45:31     Pending                  -- 20211018215211_add_users_table.sql
2021/10/19 21:45:32     Pending                  -- 20211018220433_add_posts_table.sql

up

Use the command goose up to apply all available migrations.

$ goose up

2021/10/19 21:50:36 OK    20211018215211_add_users_table.sql
2021/10/19 21:50:40 OK    20211018220433_add_posts_table.sql
2021/10/19 21:50:40 goose: no migrations to run. current version: 20211018220433

Confirm by checking status

$ goose status

2021/10/19 21:51:20     Applied At                  Migration
2021/10/19 21:51:20     =======================================
2021/10/19 21:51:21     Tue Oct 19 18:50:33 2021 -- 20211018215211_add_users_table.sql
2021/10/19 21:51:22     Tue Oct 19 18:50:37 2021 -- 20211018220433_add_posts_table.sql

You can also print only the current version of the database with goose version.

up-to

Migrate up to a specific version (including that version).

$ goose up-to 20211018220433

2021/10/19 21:56:50 OK    20211018215211_add_users_table.sql
2021/10/19 21:56:54 OK    20211018220433_add_posts_table.sql
2021/10/19 21:56:54 goose: no migrations to run. current version: 20211018220433

up-by-one

Migrate up a single migration from the current version

$ goose up-by-one

2021/10/19 21:58:47 OK    20211018215211_add_users_table.sql

down

Roll back a single migration from the current version.

$ goose down

2021/10/19 21:52:40 OK    20211018220433_add_posts_table.sql

down-to

Roll back migrations to a specific version (will not include that version).

$ goose down-to 20211018215211

2021/10/19 21:53:54 OK    20211018220433_add_posts_table.sql
2021/10/19 21:53:55 goose: no migrations to run. current version: 20211018215211

To roll back all migrations, use goose down-to 0 (careful!).

redo

Roll back the most recently applied migration, then run it again.

$ goose redo

2021/10/19 21:55:04 OK    20211018215211_add_users_table.sql
2021/10/19 21:55:08 OK    20211018215211_add_users_table.sql

reset

Rolls back all migrations

$ goose reset

2021/10/19 21:58:04 OK    20211018220433_add_posts_table.sql
2021/10/19 21:58:08 OK    20211018215211_add_users_table.sql

validate

Check the migration files for errors without running them:

goose validate

Running embedded migrations from Go

Goose is also a Go library, so your application can apply its own migrations on startup. Using Go’s embed package, the SQL files are compiled into the binary and you don’t have to ship the migrations directory alongside it.

Add goose and a Postgres driver to your Go module:

go mod init example.com/myapp
go get github.com/pressly/goose/v3
go get github.com/jackc/pgx/v5

Assuming the SQL migrations are in the migrations directory, main.go looks like this:

package main

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

	_ "github.com/jackc/pgx/v5/stdlib" // registers the "pgx" database/sql driver
	"github.com/pressly/goose/v3"
)

//go:embed migrations/*.sql
var embedMigrations embed.FS

func main() {
	db, err := sql.Open("pgx", os.Getenv("GOOSE_DBSTRING"))
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	goose.SetBaseFS(embedMigrations)

	if err := goose.SetDialect("postgres"); err != nil {
		log.Fatal(err)
	}

	if err := goose.Up(db, "migrations"); err != nil {
		log.Fatal(err)
	}

	// run app
}

We pass "migrations" as the directory to goose.Up because embedding preserves the directory structure. Embedded files are read-only, so commands like create and fix still work on the files on disk with the CLI.

For more control, goose v3 also provides a goose.NewProvider API that takes a dialect, a *sql.DB and an fs.FS and returns a provider with context-aware Up, Down and Status methods. See the goose documentation for details.

Conclusion

In this guide we managed to explore how to use golang goose to manage database migrations: installing the CLI, writing incremental SQL migrations, applying and rolling them back, and embedding them in a Go binary with embed.FS.

comments powered by Disqus
© 2020 - 2026 Citizix, Ltd.
Citizix Ltd
Built with Hugo
Theme Stack designed by Jimmy