Cheatgoose Database migrations
Light · Postcard
102 × 152 mm · PDF
Dark · Postcard
102 × 152 mm · PDF
Ink saver · Postcard
102 × 152 mm · PDF
Light · A4 landscape
297 × 210 mm · PDF
Dark · A4 landscape
297 × 210 mm · PDF
Ink saver · A4 landscape
297 × 210 mm · PDF
Light · A3 landscape
420 × 297 mm · PDF
Dark · A3 landscape
420 × 297 mm · PDF
Ink saver · A3 landscape
420 × 297 mm · PDF
Start here
goose create <name> sql
New migration file
goose <driver> <dbstring> up
Apply all pending
goose <driver> <dbstring> status
Applied or pending?
goose <driver> <dbstring> down
Undo the last one
goose <driver> <dbstring> redo
Down, then up again
goose <driver> <dbstring> version
Current DB version
goose envShow effective config
goose validateLint files, no DB
Concepts
- create new .sql file
- Up / Down SQL annotations
- Database goose_db_version
- Verified applied list
Connect
goose <driver> <dbstring> <command>
Full invocation form
goose -dir <path> <command>
Migration directory (default .)
export GOOSE_DRIVER=<driver>
Driver from environment
export GOOSE_DBSTRING=<dbstring>
Connection string from environment
export GOOSE_MIGRATION_DIR=<path>
Directory from environment
goose -env <file> <command>
Load variables from another env file
Default is .env in the current directory; -env=none turns
loading off.
goose -table <name> <command>
Custom version table (schema.table ok)
goose -timeout <duration> <command>
Cap query duration, e.g. 1h13m
goose -v <command>
Verbose output
goose -no-color <command>
Plain output (or NO_COLOR)
goose -versionPrint goose version
goose -hHelp and driver list
Dbstring examples
| driver | dbstring |
|---|---|
sqlite3 |
./app.db |
postgres |
"user=postgres dbname=app
sslmode=disable"
|
mysql |
"user:pass@/app?parseTime=true"
|
mssql |
"sqlserver://user:pass@host:1433?database=app"
|
clickhouse |
"tcp://127.0.0.1:9000" |
redshift |
"postgres://user:pass@host:5439/db"
|
goose -ssl-cert <file> -ssl-key <file>
<command>
Client TLS files (MySQL)
goose -certfile <file> <command>
Root CA PEM (MySQL)
Create
goose create <name> sql
New SQL migration
goose create <name> go
New Go migration
goose -s create <name> sql
Sequential number, not timestamp
goose -dir <path> create <name> sql
Create in a given directory
goose fixRenumber timestamps to sequential
Order is preserved. Run it in CI only once migrations are
ready to ship.
goose validateCheck files without running them
SQL annotations
-- +goose Up
Start of forward statements
-- +goose Down
Start of rollback statements
-- +goose NO TRANSACTION
Run outside a transaction (file top)
Needed for e.g. CREATE INDEX CONCURRENTLY in Postgres.
-- +goose StatementBegin
Open a block with inner semicolons
-- +goose StatementEnd
Close that block
-- +goose ENVSUB ON
Expand ${VAR} in the SQL below
-- +goose ENVSUB OFF
Stop expanding variables
${VAR:-<default>}
Env substitution with a fallback
Migrate
goose upApply all pending migrations
goose up-by-oneApply just the next one
goose up-to <version>
Migrate up to a version
goose downRoll back the latest one
goose down-to <version>
Roll back to a version
goose down-to 0Roll back everything
goose redoRe-run the latest migration
goose resetRoll back all migrations
goose -allow-missing up
Apply out-of-order migrations
By default a file older than the applied head is rejected.
goose -no-versioning up
Run files without tracking
Inspect
goose statusList migrations and applied time
goose versionPrint current DB version
goose envPrint goose environment
Go migrations
goose.AddMigration(Up, Down)
Register in init()
func Up(tx *sql.Tx) error
Migration function signature
goose.Up(db, "migrations")
Run pending from library code
goose.UpByOne(db, "migrations")
Library: apply one
goose.SetBaseFS(embedFS)
Use embedded migrations
goose.SetDialect("postgres")
Pick a dialect in code
goose.WithAllowMissing()
Library: allow out-of-order
Install
go install
github.com/pressly/goose/v3/cmd/goose@latest
Install with Go
brew install goose
Install with Homebrew
go build -tags='no_mysql no_sqlite3'
./cmd/goose
Build a lite binary
Tags: no_postgres, no_mysql, no_sqlite3, no_clickhouse,
no_mssql, no_vertica, no_ydb, no_libsql, no_azuresql.
Recipes
export GOOSE_DRIVER=postgresexport GOOSE_DBSTRING="user=app dbname=app sslmode=disable"goose -dir db/migrations create add_users sql# edit the file, then:goose -dir db/migrations up-- +goose Up-- +goose StatementBeginCREATE FUNCTION touch() RETURNS trigger AS $$BEGIN NEW.updated = now(); RETURN NEW; END;$$ LANGUAGE plpgsql;-- +goose StatementEnd-- +goose DownDROP FUNCTION touch();goose sqlite3 ./app.db upgoose sqlite3 ./app.db statusgoose sqlite3 ./app.db redogoose -dir db/migrations validategoose -dir db/migrations fixgit add db/migrations
