goose

By default, goose will error if you attempt to apply missing (out-of-order) migrations.

• CLI: Use the -allow-missing flag.
• Library: Use the goose.WithAllowMissing() functional option when calling Up, UpTo, or UpByOne.

Goose supports expanding environment variables in SQL files. This feature is disabled by default.

To enable it, use the -- +goose ENVSUB ON annotation. It remains active until -- +goose ENVSUB OFF is encountered.

Supported expansions:

• ${VAR} or $VAR: expands to the value of VAR.
• ${VAR:-default}: expands to VAR, or default if VAR is unset or null.
• ${VAR-default}: expands to VAR, or default if VAR is unset.
• ${VAR?err_msg}: expands to VAR, or prints err_msg and errors if VAR is unset.

-- +goose Up
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION test_func()
RETURNS void AS $$
-- +goose ENVSUB ON
BEGIN
	RAISE NOTICE '${SOME_ENV_VAR}';
END;
-- +goose ENVSUB OFF
$$ LANGUAGE plpgsql;
-- +goose StatementEnd

SQL migrations in goose use specific annotations to define forward (Up) and rollback (Down) logic.

• Each file must have exactly one -- +goose Up annotation.
• The -- +goose Down annotation is optional.
• If both are present, -- +goose Up must come first.
• By default, migrations run within a transaction. To skip transactions (e.g., for CREATE DATABASE), add -- +goose NO TRANSACTION to the top of the file.
• Statements must end with a semicolon (;).
• To use a different schema for the version table, use the -table option: -table='schemaname.goose_db_version.

-- +goose Up
CREATE TABLE post (
    id int NOT NULL,
    title text,
    body text,
    PRIMARY KEY(id)
);

-- +goose Down
DROP TABLE post;

Manage your database schema state using the following commands:

• up: Apply all available migrations.
• up-to VERSION: Migrate up to a specific version.
• up-by-one: Migrate up by exactly one migration.
• down: Roll back the single most recent migration.
• down-to VERSION: Roll back migrations to a specific version (use down-to 0 to roll back all).
• redo: Re-run the latest migration.
• reset: Roll back all migrations.

You can build a custom version of the goose CLI that includes your Go-based migrations directly within the binary. This is useful for distributing a single executable that contains all necessary migration logic. After building, you can use the custom binary to manage migrations (status, up, down, etc.) just like the standard goose CLI.

$ go build -o goose-custom *.go

$ ./goose-custom sqlite3 ./foo.db status
$ ./goose-custom sqlite3 ./foo.db up

You can install the goose binary using go install or via Homebrew on macOS.

To install the full version via Go:

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

To install via Homebrew:

brew install goose

To maintain a clean project structure when using Go migrations, follow these steps to separate your migration logic from your CLI entry point:

1. Move your main.go file into a cmd/ directory.
2. Rename the package name in all your migration files (*_.go) from main to migrations.
3. Import the migrations package in your cmd/main.go using a blank import (_) to ensure the init() functions within the migrations package are executed and registered with goose.

import (
    // Invoke init() functions within migrations pkg.
    _ "github.com/pressly/goose/example/migrations-go"
)

Use these commands to inspect the current state of your database migrations:

• status: Dumps the migration status (shows which migrations are applied and which are pending).
• version: Prints the current version number of the database.

The goose CLI follows the pattern: goose DRIVER DBSTRING [OPTIONS] COMMAND.

Alternatively, you can set the following environment variables to avoid passing arguments every time:

• GOOSE_DRIVER
• GOOSE_DBSTRING
• GOOSE_MIGRATION_DIR

Supported Drivers

postgres, mysql, sqlite3, spanner, mssql, redshift, tidb, clickhouse, ydb, starrocks, turso.

If the default binary is too large, you can build a custom version by excluding drivers you do not need using build tags.

Available build tags to exclude drivers:

• no_clickhouse
• no_libsql
• no_mssql
• no_mysql
• no_postgres
• no_sqlite3
• no_vertica
• no_ydb

Example: Building a version without Postgres, MySQL, SQLite3, and YDB:

go build -tags='no_postgres no_mysql no_sqlite3 no_ydb' -o goose ./cmd/goose

Instead of SQL files, you can write migrations as Go functions.

1. Create a package for your migrations.
2. Use goose.AddMigration(Up, Down) in an init() function.
3. Migration files must start with a numeric value followed by an underscore (e.g., 00001_init.go) and must not end in _test.go.
4. Import the migration package with a blank identifier (_) in your main.go to ensure they are registered.

package migrations

import (
	"database/sql"

	"github.com/pressly/goose/v3"
)

func init() {
	goose.AddMigration(Up, Down)
}

func Up(tx *sql.Tx) error {
	_, err := tx.Exec("UPDATE users SET username='admin' WHERE username='root';")
	return err
}

func Down(tx *sql.Tx) error {
	_, err := tx.Exec("UPDATE users SET username='root' WHERE username='admin';")
	return err
}

For complex statements like PL/pgSQL functions that contain internal semicolons, you must wrap the statement with -- +goose StatementBegin and -- +goose StatementEnd so goose can recognize the entire block as a single unit.

-- +goose Up
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION my_func() RETURNS void AS $$
BEGIN
  -- complex logic with semicolons
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd