goose

<img align="right" width="125" src="assets/goose_logo.png">

Goose is a database migration tool. Both a CLI and a library.

Manage your database schema by creating incremental SQL changes or Go functions.

Features

• Works against multiple databases:
  • Postgres, MySQL, Spanner, SQLite, YDB, ClickHouse, MSSQL, Vertica, and more.
• Supports Go migrations written as plain functions.
• Supports embedded migrations.
• Out-of-order migrations.
• Seeding data.
• Environment variable substitution in SQL migrations.
• ... and more.

Install

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

This will install the goose binary to your $GOPATH/bin directory.

Binary too big? Build a lite version by excluding the drivers you don't need:

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

# Available build tags:
#   no_clickhouse  no_libsql   no_mssql    no_mysql
#   no_postgres    no_sqlite3  no_vertica  no_ydb

For macOS users goose is available as a Homebrew Formulae:

brew install goose

See installation documentation for more details.

Usage

<details> <summary>Click to show <code>goose help</code> output</summary>

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

Drivers:
    postgres
    mysql
    sqlite3
    spanner
    mssql
    redshift
    tidb
    clickhouse
    ydb
    starrocks
    turso

Examples:
    goose sqlite3 ./foo.db status
    goose sqlite3 ./foo.db create init sql
    goose sqlite3 ./foo.db create add_some_column sql
    goose sqlite3 ./foo.db create fetch_user_data go
    goose sqlite3 ./foo.db up

    goose postgres "user=postgres dbname=postgres sslmode=disable" status
    goose mysql "user:password@/dbname?parseTime=true" status
    goose spanner "projects/project/instances/instance/databases/database" status
    goose redshift "postgres://user:password@qwerty.us-east-1.redshift.amazonaws.com:5439/db" status
    goose tidb "user:password@/dbname?parseTime=true" status
    goose mssql "sqlserver://user:password@hostname:1433?database=master" status
    goose clickhouse "tcp://127.0.0.1:9000" status
    goose ydb "grpcs://localhost:2135/local?go_query_mode=scripting&go_fake_tx=scripting&go_query_bind=declare,numeric" status
    goose starrocks "user:password@/dbname?parseTime=true&interpolateParams=true" status

    GOOSE_DRIVER=sqlite3 GOOSE_DBSTRING=./foo.db goose status
    GOOSE_DRIVER=sqlite3 GOOSE_DBSTRING=./foo.db goose create init sql
    GOOSE_DRIVER=postgres GOOSE_DBSTRING="user=postgres dbname=postgres sslmode=disable" goose status
    GOOSE_DRIVER=mysql GOOSE_DBSTRING="user:password@/dbname" goose status
    GOOSE_DRIVER=redshift GOOSE_DBSTRING="postgres://user:password@qwerty.us-east-1.redshift.amazonaws.com:5439/db" goose status
    GOOSE_DRIVER=clickhouse GOOSE_DBSTRING="clickhouse://user:password@qwerty.clickhouse.cloud:9440/dbname?secure=true&skip_verify=false" goose status

Options:

  -allow-missing
        applies missing (out-of-order) migrations
  -certfile string
        file path to root CA's certificates in pem format (only support on mysql)
  -dir string
        directory with migration files (default ".", can be set via the GOOSE_MIGRATION_DIR env variable).
  -h    print help
  -no-color
        disable color output (NO_COLOR env variable supported)
  -no-versioning
        apply migration commands with no versioning, in file order, from directory pointed to
  -s    use sequential numbering for new migrations
  -ssl-cert string
        file path to SSL certificates in pem format (only support on mysql)
  -ssl-key string
        file path to SSL key in pem format (only support on mysql)
  -table string
        migrations table name (default "goose_db_version"). If you use a schema that is not `public`, you should set `schemaname.goose_db_version` when running commands.
  -timeout duration
        maximum allowed duration for queries to run; e.g., 1h13m
  -v    enable verbose mode
  -version
        print version

Commands:
    up                   Migrate the DB to the most recent version available
    up-by-one            Migrate the DB up by 1
    up-to VERSION        Migrate the DB to a specific VERSION
    down                 Roll back the version by 1
    down-to VERSION      Roll back to a specific VERSION
    redo                 Re-run the latest migration
    reset                Roll back all migrations
    status               Dump the migration status for the current DB
    version              Print the current version of the database
    create NAME [sql|go] Creates new migration file with the current timestamp
    fix                  Apply sequential ordering to migrations
    validate             Check migration files without running them

</details>

Commonly used commands:

create<span> • </span> up<span> • </span> up-to<span> • </span> down<span> • </span> down-to<span> • </span> status<span> • </span> version

create

Create a new SQL migration.

$ goose create add_some_column sql
$ Created new file: 20170506082420_add_some_column.sql

$ goose -s create add_some_column sql
$ Created new file: 00001_add_some_column.sql

Edit the newly created file to define the behavior of your migration.

You can also create a Go migration, if you then invoke it with your own goose binary:

$ goose create fetch_user_data go
$ Created new file: 20170506082421_fetch_user_data.go

up

Apply all available migrations.

$ goose up
$ OK    001_basics.sql
$ OK    002_next.sql
$ OK    003_and_again.go

up-to

Migrate up to a specific version.

$ goose up-to 20170506082420
$ OK    20170506082420_create_table.sql

up-by-one

Migrate up a single migration from the current version

$ goose up-by-one
$ OK    20170614145246_change_type.sql

down

Roll back a single migration from the current version.

$ goose down
$ OK    003_and_again.go

down-to

Roll back migrations to a specific version.

$ goose down-to 20170506082527
$ OK    20170506082527_alter_column.sql

Or, roll back all migrations (careful!):

$ goose down-to 0

status

Print the status of all migrations:

$ goose status
$   Applied At                  Migration
$   =======================================
$   Sun Jan  6 11:25:03 2013 -- 001_basics.sql
$   Sun Jan  6 11:25:03 2013 -- 002_next.sql
$   Pending                  -- 003_and_again.go

Note: for MySQL parseTime flag must be enabled.

Note: for MySQL multiStatements must be enabled. This is required when writing multiple queries separated by ';' characters in a single sql file.

version

Print the current version of the database:

$ goose version
$ goose: version 002

Environment Variables

If you prefer to use environment variables, instead of passing the driver and database string as arguments, you can set the following environment variables:

1. Via environment variables:

export GOOSE_DRIVER=DRIVER
export GOOSE_DBSTRING=DBSTRING
export GOOSE_MIGRATION_DIR=MIGRATION_DIR
export GOOSE_TABLE=TABLENAME

2. Via .env files with corresponding variables. .env file example:

GOOSE_DRIVER=postgres
GOOSE_DBSTRING=postgres://admin:admin@localhost:5432/admin_db
GOOSE_MIGRATION_DIR=./migrations
GOOSE_TABLE=custom.goose_migrations

Loading from .env files is enabled by default. To disable this feature, set the -env=none flag. If you want to load from a specific file, set the -env flag to the file path.

For more details about environment variables, see the official documentation on environment variables.

Migrations

goose supports migrations written in SQL or in Go.

SQL Migrations

A sample SQL migration looks like:

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

-- +goose Down
DROP TABLE post;

Each migration file must have exactly one -- +goose Up annotation. The -- +goose Down annotation is optional. If the file has both annotations, then the -- +goose Up annotation must come first.

Notice the annotations in the comments. Any statements following -- +goose Up will be executed as part of a forward migration, and any statements following -- +goose Down will be executed as part of a rollback.

By default, all migrations are run within a transaction. Some statements like CREATE DATABASE, however, cannot be run within a transaction. You may optionally add -- +goose NO TRANSACTION to the top of your migration file in order to skip transactions within that specific migration file. Both Up and Down migrations within this file will be run without transactions.

By default, SQL statements are delimited by semicolons - in fact, query statements must end with a semicolon to be properly recognized by goose.

By default, all migrations are run on the public schema. If you want to use a different