goose

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

This will install the

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

For macOS users

See installation documentation for more details.

Usage

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

Commonly used commands:

create •  up •  up-to •  down •  down-to •  status •  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

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:

2. Via

Loading from

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:

Each migration file must have exactly one

Notice the annotations in the comments. Any statements following

By default, all migrations are run within a transaction. Some statements like

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 schema, specify the schema name using the table option like

More complex statements (PL/pgSQL) that have semicolons within them must be annotated with

Goose supports environment variable substitution in SQL migrations through annotations. To enable this feature, use the

This feature is disabled by default for backward compatibility with existing scripts.

For

Embedded sql migrations

Go 1.16 introduced new feature: compile-time embedding files into binary and corresponding filesystem abstraction.

This feature can be used only for applying existing migrations. Modifying operations such as

Make sure to configure the correct SQL dialect, see dialect.go for supported SQL dialects.

Example usage, assuming that SQL migrations are placed in the

Note that we pass

Go Migrations

1. Create your own goose binary, see example
2. Import
   github.com/pressly/goose
3. Register your migration functions
4. Include your
   migrations
   package into Go build: in
   main.go
   ,
   import _ "github.com/me/myapp/migrations"
5. Run goose command, ie.
   goose.Up(db *sql.DB, dir string)

A sample Go migration 00002_users_add_email.go file looks like:

Note that Go migration files must begin with a numeric value, followed by an underscore, and must not end with

Hybrid Versioning

Please, read the versioning problem first.

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

However, we strongly recommend adopting a hybrid versioning approach, using both timestamps and sequential numbers. Migrations created during the development process are timestamped and sequential versions are ran on production. We believe this method will prevent the problem of conflicting versions when writing software in a team environment.

To help you adopt this approach,

Credit

The gopher mascot was designed by Renée French / CC 3.0. For more info check out the Go Blog. Adapted by Ellen.

License

Licensed under MIT License