1# goose
2
3<img align="right" width="125" src="assets/goose_logo.png">
4
5[](https://github.com/pressly/goose/actions/workflows/ci.yaml)
7[](https://pkg.go.dev/github.com/pressly/goose/v3)
9[](https://goreportcard.com/report/github.com/pressly/goose/v3)
11
12Goose is a database migration tool. Both a CLI and a library.
13
14Manage your **database schema** by creating incremental SQL changes or Go functions.
15
16#### Features
17
18- Works against multiple databases:
19 - Postgres, MySQL, SQLite, YDB, ClickHouse, MSSQL, Vertica, and
20 more.
21- Supports Go migrations written as plain functions.
22- Supports [embedded](https://pkg.go.dev/embed/) migrations.
23- Out-of-order migrations.
24- Seeding data.
25- Environment variable substitution in SQL migrations.
26- ... and more.
27
28# Install
29
30```shell
31go install github.com/pressly/goose/v3/cmd/goose@latest
32```
33
34This will install the `goose` binary to your `$GOPATH/bin` directory.
35
36Binary too big? Build a lite version by excluding the drivers you don't need:
37
38```shell
39go build -tags='no_postgres no_mysql no_sqlite3 no_ydb' -o goose ./cmd/goose
40
41# Available build tags:
42# no_clickhouse no_libsql no_mssql no_mysql
43# no_postgres no_sqlite3 no_vertica no_ydb
44```
45
46For macOS users `goose` is available as a [Homebrew
47Formulae](https://formulae.brew.sh/formula/goose#default):
48
49```shell
50brew install goose
51```
52
53See [installation documentation](https://pressly.github.io/goose/installation/) for more details.
54
55# Usage
56
57<details>
58<summary>Click to show <code>goose help</code> output</summary>
59
60```
61Usage: goose [OPTIONS] DRIVER DBSTRING COMMAND
62
63or
64
65Set environment key
66GOOSE_DRIVER=DRIVER
67GOOSE_DBSTRING=DBSTRING
68GOOSE_MIGRATION_DIR=MIGRATION_DIR
69
70Usage: goose [OPTIONS] COMMAND
71
72Drivers:
73 postgres
74 mysql
75 sqlite3
76 mssql
77 redshift
78 tidb
79 clickhouse
80 vertica
81 ydb
82 starrocks
83
84Examples:
85 goose sqlite3 ./foo.db status
86 goose sqlite3 ./foo.db create init sql
87 goose sqlite3 ./foo.db create add_some_column sql
88 goose sqlite3 ./foo.db create fetch_user_data go
89 goose sqlite3 ./foo.db up
90
91 goose postgres "user=postgres dbname=postgres sslmode=disable" status
92 goose mysql "user:password@/dbname?parseTime=true" status
93 goose redshift "postgres://user:password@qwerty.us-east-1.redshift.amazonaws.com:5439/db" status
94 goose tidb "user:password@/dbname?parseTime=true" status
95 goose mssql "sqlserver://user:password@hostname:1433?database=master" status
96 goose clickhouse "tcp://127.0.0.1:9000" status
97 goose vertica "vertica://user:password@localhost:5433/dbname?connection_load_balance=1" status
98 goose ydb "grpcs://localhost:2135/local?go_query_mode=scripting&go_fake_tx=scripting&go_query_bind=declare,numeric" status
99 goose starrocks "user:password@/dbname?parseTime=true&interpolateParams=true" status
100
101 GOOSE_DRIVER=sqlite3 GOOSE_DBSTRING=./foo.db goose status
102 GOOSE_DRIVER=sqlite3 GOOSE_DBSTRING=./foo.db goose create init sql
103 GOOSE_DRIVER=postgres GOOSE_DBSTRING="user=postgres dbname=postgres sslmode=disable" goose status
104 GOOSE_DRIVER=mysql GOOSE_DBSTRING="user:password@/dbname" goose status
105 GOOSE_DRIVER=redshift GOOSE_DBSTRING="postgres://user:password@qwerty.us-east-1.redshift.amazonaws.com:5439/db" goose status
106 GOOSE_DRIVER=clickhouse GOOSE_DBSTRING="clickhouse://user:password@qwerty.clickhouse.cloud:9440/dbname?secure=true&skip_verify=false" goose status
107
108Options:
109
110 -allow-missing
111 applies missing (out-of-order) migrations
112 -certfile string
113 file path to root CA's certificates in pem format (only support on mysql)
114 -dir string
115 directory with migration files (default ".", can be set via the GOOSE_MIGRATION_DIR env variable).
116 -h print help
117 -no-color
118 disable color output (NO_COLOR env variable supported)
119 -no-versioning
120 apply migration commands with no versioning, in file order, from directory pointed to
121 -s use sequential numbering for new migrations
122 -ssl-cert string
123 file path to SSL certificates in pem format (only support on mysql)
124 -ssl-key string
125 file path to SSL key in pem format (only support on mysql)
126 -table string
127 migrations table name (default "goose_db_version")
128 -timeout duration
129 maximum allowed duration for queries to run; e.g., 1h13m
130 -v enable verbose mode
131 -version
132 print version
133
134Commands:
135 up Migrate the DB to the most recent version available
136 up-by-one Migrate the DB up by 1
137 up-to VERSION Migrate the DB to a specific VERSION
138 down Roll back the version by 1
139 down-to VERSION Roll back to a specific VERSION
140 redo Re-run the latest migration
141 reset Roll back all migrations
142 status Dump the migration status for the current DB
143 version Print the current version of the database
144 create NAME [sql|go] Creates new migration file with the current timestamp
145 fix Apply sequential ordering to migrations
146 validate Check migration files without running them
147```
148
149</details>
150
151Commonly used commands:
152
153[create](#create)<span> • </span> [up](#up)<span> • </span> [up-to](#up-to)<span> • </span> [down](#down)<span> • </span> [down-to](#down-to)<span> • </span> [status](#status)<span> • </span> [version](#version)
154
155## create
156
157Create a new SQL migration.
158
159 $ goose create add_some_column sql
160 $ Created new file: 20170506082420_add_some_column.sql
161
162 $ goose -s create add_some_column sql
163 $ Created new file: 00001_add_some_column.sql
164
165Edit the newly created file to define the behavior of your migration.
166
167You can also create a Go migration, if you then invoke it with [your own goose
168binary](#go-migrations):
169
170 $ goose create fetch_user_data go
171 $ Created new file: 20170506082421_fetch_user_data.go
172
173## up
174
175Apply all available migrations.
176
177 $ goose up
178 $ OK 001_basics.sql
179 $ OK 002_next.sql
180 $ OK 003_and_again.go
181
182## up-to
183
184Migrate up to a specific version.
185
186 $ goose up-to 20170506082420
187 $ OK 20170506082420_create_table.sql
188
189## up-by-one
190
191Migrate up a single migration from the current version
192
193 $ goose up-by-one
194 $ OK 20170614145246_change_type.sql
195
196## down
197
198Roll back a single migration from the current version.
199
200 $ goose down
201 $ OK 003_and_again.go
202
203## down-to
204
205Roll back migrations to a specific version.
206
207 $ goose down-to 20170506082527
208 $ OK 20170506082527_alter_column.sql
209
210Or, roll back all migrations (careful!):
211
212 $ goose down-to 0
213
214## status
215
216Print the status of all migrations:
217
218 $ goose status
219 $ Applied At Migration
220 $ =======================================
221 $ Sun Jan 6 11:25:03 2013 -- 001_basics.sql
222 $ Sun Jan 6 11:25:03 2013 -- 002_next.sql
223 $ Pending -- 003_and_again.go
224
225Note: for MySQL [parseTime flag](https://github.com/go-sql-driver/mysql#parsetime) must be enabled.
226
227Note: for MySQL
228[`multiStatements`](https://github.com/go-sql-driver/mysql?tab=readme-ov-file#multistatements) must
229be enabled. This is required when writing multiple queries separated by ';' characters in a single
230sql file.
231
232## version
233
234Print the current version of the database:
235
236 $ goose version
237 $ goose: version 002
238
239# Environment Variables
240
241If you prefer to use environment variables, instead of passing the driver and database string as
242arguments, you can set the following environment variables:
243
244**1. Via environment variables:**
245
246```shell
247export GOOSE_DRIVER=DRIVER
248export GOOSE_DBSTRING=DBSTRING
249export GOOSE_MIGRATION_DIR=MIGRATION_DIR
250```
251
252**2. Via `.env` files with corresponding variables. `.env` file example**:
253
254```env
255GOOSE_DRIVER=postgres
256GOOSE_DBSTRING=postgres://admin:admin@localhost:5432/admin_db
257GOOSE_MIGRATION_DIR=./migrations
258```
259
260Loading from `.env` files is enabled by default. To disable this feature, set the `-env=none` flag.
261If you want to load from a specific file, set the `-env` flag to the file path.
262
263For more details about environment variables, see the [official documentation on environment
264variables](https://pressly.github.io/goose/documentation/environment-variables/).
265
266# Migrations
267
268goose supports migrations written in SQL or in Go.
269
270## SQL Migrations
271
272A sample SQL migration looks like:
273
274```sql
275-- +goose Up
276CREATE TABLE post (
277 id int NOT NULL,
278 title text,
279 body text,
280 PRIMARY KEY(id)
281);
282
283-- +goose Down
284DROP TABLE post;
285```
286
287Each migration file must have exactly one `-- +goose Up` annotation. The `-- +goose Down` annotation
288is optional. If the file has both annotations, then the `-- +goose Up` annotation **must** come
289first.
290
291Notice the annotations in the comments. Any statements following `-- +goose Up` will be executed as
292part of a forward migration, and any statements following `-- +goose Down` will be executed as part
293of a rollback.
294
295By default, all migrations are run within a transaction. Some statements like `CREATE DATABASE`,
296however, cannot be run within a transaction. You may optionally add `-- +goose NO TRANSACTION` to
297the top of your migration file in order to skip transactions within that specific migration file.
298Both Up and Down migrations within this file will be run without transactions.
299
300By default, SQL statements are delimited by semicolons - in fact, query statements must end with a
301semicolon to be properly recognized by goose.
302
303More complex statements (PL/pgSQL) that have semicolons within them must be annotated with `--
304+goose StatementBegin` and `-- +goose StatementEnd` to be properly recognized. For example:
305
306```sql
307-- +goose Up
308-- +goose StatementBegin
309CREATE OR REPLACE FUNCTION histories_partition_creation( DATE, DATE )
310returns void AS $$
311DECLARE
312 create_query text;
313BEGIN
314 FOR create_query IN SELECT
315 'CREATE TABLE IF NOT EXISTS histories_'
316 || TO_CHAR( d, 'YYYY_MM' )
317 || ' ( CHECK( created_at >= timestamp '''
318 || TO_CHAR( d, 'YYYY-MM-DD 00:00:00' )
319 || ''' AND created_at < timestamp '''
320 || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD 00:00:00' )
321 || ''' ) ) inherits ( histories );'
322 FROM generate_series( $1, $2, '1 month' ) AS d
323 LOOP
324 EXECUTE create_query;
325 END LOOP; -- LOOP END
326END; -- FUNCTION END
327$$
328language plpgsql;
329-- +goose StatementEnd
330```
331
332Goose supports environment variable substitution in SQL migrations through annotations. To enable
333this feature, use the `-- +goose ENVSUB ON` annotation before the queries where you want
334substitution applied. It stays active until the `-- +goose ENVSUB OFF` annotation is encountered.
335You can use these annotations multiple times within a file.
336
337This feature is disabled by default for backward compatibility with existing scripts.
338
339For `PL/pgSQL` functions or other statements where substitution is not desired, wrap the annotations
340explicitly around the relevant parts. For example, to exclude escaping the `**` characters:
341
342```sql
343-- +goose StatementBegin
344CREATE OR REPLACE FUNCTION test_func()
345RETURNS void AS $$
346-- +goose ENVSUB ON
347BEGIN
348 RAISE NOTICE '${SOME_ENV_VAR}';
349END;
350-- +goose ENVSUB OFF
351$$ LANGUAGE plpgsql;
352-- +goose StatementEnd
353```
354
355<details>
356<summary>Supported expansions (click here to expand):</summary>
357
358- `${VAR}` or $VAR - expands to the value of the environment variable `VAR`
359- `${VAR:-default}` - expands to the value of the environment variable `VAR`, or `default` if `VAR`
360 is unset or null
361- `${VAR-default}` - expands to the value of the environment variable `VAR`, or `default` if `VAR`
362 is unset
363- `${VAR?err_msg}` - expands to the value of the environment variable `VAR`, or prints `err_msg` and
364 error if `VAR` unset
365- ~~`${VAR:?err_msg}` - expands to the value of the environment variable `VAR`, or prints `err_msg`
366 and error if `VAR` unset or null.~~ **THIS IS NOT SUPPORTED**
367
368See
369[mfridman/interpolate](https://github.com/mfridman/interpolate?tab=readme-ov-file#supported-expansions)
370for more details on supported expansions.
371
372</details>
373
374## Embedded sql migrations
375
376Go 1.16 introduced new feature: [compile-time embedding](https://pkg.go.dev/embed/) files into
377binary and corresponding [filesystem abstraction](https://pkg.go.dev/io/fs/).
378
379This feature can be used only for applying existing migrations. Modifying operations such as `fix`
380and `create` will continue to operate on OS filesystem even if using embedded files. This is
381expected behaviour because `io/fs` interfaces allows read-only access.
382
383Make sure to configure the correct SQL dialect, see [dialect.go](./dialect.go) for supported SQL
384dialects.
385
386Example usage, assuming that SQL migrations are placed in the `migrations` directory:
387
388```go
389package main
390
391import (
392 "database/sql"
393 "embed"
394
395 "github.com/pressly/goose/v3"
396)
397
398//go:embed migrations/*.sql
399var embedMigrations embed.FS
400
401func main() {
402 var db *sql.DB
403 // setup database
404
405 goose.SetBaseFS(embedMigrations)
406
407 if err := goose.SetDialect("postgres"); err != nil {
408 panic(err)
409 }
410
411 if err := goose.Up(db, "migrations"); err != nil {
412 panic(err)
413 }
414
415 // run app
416}
417```
418
419Note that we pass `"migrations"` as directory argument in `Up` because embedding saves directory
420structure.
421
422## Go Migrations
423
4241. Create your own goose binary, see [example](./examples/go-migrations)
4252. Import `github.com/pressly/goose`
4263. Register your migration functions
4274. Run goose command, ie. `goose.Up(db *sql.DB, dir string)`
428
429A [sample Go migration 00002_users_add_email.go file](./examples/go-migrations/00002_rename_root.go)
430looks like:
431
432```go
433package migrations
434
435import (
436 "database/sql"
437
438 "github.com/pressly/goose/v3"
439)
440
441func init() {
442 goose.AddMigration(Up, Down)
443}
444
445func Up(tx *sql.Tx) error {
446 _, err := tx.Exec("UPDATE users SET username='admin' WHERE username='root';")
447 if err != nil {
448 return err
449 }
450 return nil
451}
452
453func Down(tx *sql.Tx) error {
454 _, err := tx.Exec("UPDATE users SET username='root' WHERE username='admin';")
455 if err != nil {
456 return err
457 }
458 return nil
459}
460```
461
462Note that Go migration files must begin with a numeric value, followed by an underscore, and must
463not end with `*_test.go`.
464
465# Hybrid Versioning
466
467Please, read the [versioning
468problem](https://github.com/pressly/goose/issues/63#issuecomment-428681694) first.
469
470By default, if you attempt to apply missing (out-of-order) migrations `goose` will raise an error.
471However, If you want to apply these missing migrations pass goose the `-allow-missing` flag, or if
472using as a library supply the functional option `goose.WithAllowMissing()` to Up, UpTo or UpByOne.
473
474However, we strongly recommend adopting a hybrid versioning approach, using both timestamps and
475sequential numbers. Migrations created during the development process are timestamped and sequential
476versions are ran on production. We believe this method will prevent the problem of conflicting
477versions when writing software in a team environment.
478
479To help you adopt this approach, `create` will use the current timestamp as the migration version.
480When you're ready to deploy your migrations in a production environment, we also provide a helpful
481`fix` command to convert your migrations into sequential order, while preserving the timestamp
482ordering. We recommend running `fix` in the CI pipeline, and only when the migrations are ready for
483production.
484
485## Credit
486
487The gopher mascot was designed by [Renée French](https://reneefrench.blogspot.com/) / [CC
4883.0.](https://creativecommons.org/licenses/by/3.0/) For more info check out the [Go
489Blog](https://go.dev/blog/gopher). Adapted by Ellen.
490
491## License
492
493Licensed under [MIT License](./LICENSE)