github.com/gogriddy/goose@v0.0.0-20180817174216-2c751e0981c8/README.md (about) 1 # WARNING - do research on which goose repo/proj to use 2 3 [![Documentation](https://godoc.org/github.com/CloudCom/goose?status.png)](http://godoc.org/github.com/CloudCom/goose) 4 [![Build Status](https://travis-ci.org/CloudCom/goose.svg?branch=master)](https://travis-ci.org/CloudCom/goose) 5 [![Coverage Status](https://coveralls.io/repos/CloudCom/goose/badge.svg?branch=master&service=github)](https://coveralls.io/github/CloudCom/goose?branch=master) 6 7 # goose 8 9 goose is a database migration tool. 10 11 You can manage your database's evolution by creating incremental SQL or Go scripts. 12 13 ## Stability notice 14 This repo is a fork from https://bitbucket.org/liamstask/goose. There are many things that we plan to change, mostly to make the tool easier to use, and more standardized. 15 16 As such, until this notice goes away, vendoring is highly recommended. 17 18 # Install 19 20 $ go get github.com/CloudCom/goose/cmd/goose 21 22 This will install the `goose` binary to your `$GOPATH/bin` directory. 23 24 You can also build goose into your own applications by importing `github.com/CloudCom/goose/lib/goose`. Documentation is available at [godoc.org](http://godoc.org/github.com/CloudCom/goose/lib/goose). 25 26 NOTE: the API is still new, and may undergo some changes. 27 28 ## Omitting drivers 29 30 The default goose binary includes support for all available drivers. Sometimes this results in a lengthy build process. Drivers may be omitted from the build by using build tags. 31 32 For example 33 34 $ go get -tags nosqlite3 github.com/CloudCom/goose/cmd/goose 35 36 The available tags are: 37 38 * `nomymysql` 39 * `nomysql` 40 * `nopq` 41 * `nosqlite3` 42 43 # Usage 44 45 goose provides several commands to help manage your database schema. 46 47 ## create 48 49 Create a new SQL migration. 50 51 $ goose create AddSomeColumns 52 $ goose: created db/migrations/20130106093224_AddSomeColumns.sql 53 54 Edit the newly created script to define the behavior of your migration. 55 56 You can also create a Go migration: 57 58 $ goose create -type go AddSomeColumns 59 $ goose: created db/migrations/20130106093224_AddSomeColumns.go 60 61 ## up 62 63 Apply all available migrations. 64 65 $ goose up 66 $ goose: migrating db environment 'development', current version: 0, target: 3 67 $ OK 001_basics.sql 68 $ OK 002_next.sql 69 $ OK 003_and_again.go 70 71 ### option: pgschema 72 73 Use the `pgschema` flag with the `up` command specify a postgres schema. 74 75 $ goose -pgschema=my_schema_name up 76 $ goose: migrating db environment 'development', current version: 0, target: 3 77 $ OK 001_basics.sql 78 $ OK 002_next.sql 79 $ OK 003_and_again.go 80 81 ## down 82 83 Roll back a single migration from the current version. 84 85 $ goose down 86 $ goose: migrating db environment 'development', current version: 3, target: 2 87 $ OK 003_and_again.go 88 89 ## redo 90 91 Roll back the most recently applied migration, then run it again. 92 93 $ goose redo 94 $ goose: migrating db environment 'development', current version: 3, target: 2 95 $ OK 003_and_again.go 96 $ goose: migrating db environment 'development', current version: 2, target: 3 97 $ OK 003_and_again.go 98 99 ## status 100 101 Print the status of all migrations: 102 103 $ goose status 104 $ goose: status for environment 'development' 105 $ Applied At Migration 106 $ ======================================= 107 $ Sun Jan 6 11:25:03 2013 -- 001_basics.sql 108 $ Sun Jan 6 11:25:03 2013 -- 002_next.sql 109 $ Pending -- 003_and_again.go 110 111 ## dbversion 112 113 Print the current version of the database: 114 115 $ goose dbversion 116 $ goose: dbversion 002 117 118 119 `goose -h` provides more detailed info on each command. 120 121 122 # Migrations 123 124 goose supports migrations written in SQL or in Go - see the `goose create` command above for details on how to generate them. 125 126 ## SQL Migrations 127 128 A sample SQL migration looks like: 129 130 ```sql 131 -- +goose Up 132 CREATE TABLE post ( 133 id int NOT NULL, 134 title text, 135 body text, 136 PRIMARY KEY(id) 137 ); 138 139 -- +goose Down 140 DROP TABLE post; 141 ``` 142 143 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. 144 145 By default, SQL statements are delimited by semicolons - in fact, query statements must end with a semicolon to be properly recognized by goose. 146 147 More complex statements (PL/pgSQL) that have semicolons within them must be annotated with `-- +goose StatementBegin` and `-- +goose StatementEnd` to be properly recognized. For example: 148 149 ```sql 150 -- +goose Up 151 -- +goose StatementBegin 152 CREATE OR REPLACE FUNCTION histories_partition_creation( DATE, DATE ) 153 returns void AS $$ 154 DECLARE 155 create_query text; 156 BEGIN 157 FOR create_query IN SELECT 158 'CREATE TABLE IF NOT EXISTS histories_' 159 || TO_CHAR( d, 'YYYY_MM' ) 160 || ' ( CHECK( created_at >= timestamp ''' 161 || TO_CHAR( d, 'YYYY-MM-DD 00:00:00' ) 162 || ''' AND created_at < timestamp ''' 163 || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD 00:00:00' ) 164 || ''' ) ) inherits ( histories );' 165 FROM generate_series( $1, $2, '1 month' ) AS d 166 LOOP 167 EXECUTE create_query; 168 END LOOP; -- LOOP END 169 END; -- FUNCTION END 170 $$ 171 language plpgsql; 172 -- +goose StatementEnd 173 ``` 174 175 ## Go Migrations 176 177 A sample Go migration looks like: 178 179 ```go 180 package main 181 182 import ( 183 "database/sql" 184 "fmt" 185 ) 186 187 func Up_20130106222315(txn *sql.Tx) { 188 fmt.Println("Hello from migration 20130106222315 Up!") 189 } 190 191 func Down_20130106222315(txn *sql.Tx) { 192 fmt.Println("Hello from migration 20130106222315 Down!") 193 } 194 ``` 195 196 `Up_20130106222315()` will be executed as part of a forward migration, and `Down_20130106222315()` will be executed as part of a rollback. 197 198 The numeric portion of the function name (`20130106222315`) must be the leading portion of migration's filename, such as `20130106222315_descriptive_name.go`. `goose create` does this by default. 199 200 A transaction is provided, rather than the DB instance directly, since goose also needs to record the schema version within the same transaction. Each migration should run as a single transaction to ensure DB integrity, so it's good practice anyway. 201 202 203 # Configuration 204 205 goose expects you to maintain a folder (typically called "db"), which contains the following: 206 207 * a `dbconf.yaml` file that describes the database configurations you'd like to use 208 * a folder called "migrations" which contains `.sql` and/or `.go` scripts that implement your migrations 209 210 You may use the `-path` option to specify an alternate location for the folder containing your config and migrations. 211 212 A sample `dbconf.yml` looks like 213 214 ```yml 215 development: 216 driver: postgres 217 open: user=liam dbname=tester sslmode=disable 218 ``` 219 220 Here, `development` specifies the name of the environment, and the `driver` and `open` elements are passed directly to database/sql to access the specified database. 221 222 You may include as many environments as you like, and you can use the `-env` command line option to specify which one to use. goose defaults to using an environment called `development`. 223 224 The configuration may also be environment-less, with all fields at the top level. For example: 225 226 ```yaml 227 driver: postgres 228 open: user=liam dbname=tester sslmode=disable 229 ``` 230 231 You can even use a mixture of both. If a field is not specified within an environment, goose will fall back to looking at the top level. 232 233 You may also include environment variables in any field of the config. Specify them as `$MY_ENV_VAR` or `${MY_ENV_VAR}`. 234 235 ## Configless 236 237 Goose can also run without a config file, by pulling all parameters from environment variables. This mode operates exactly as if you passed the following config file: 238 239 ```yaml 240 migrationsDir: $DB_MIGRATIONS_DIR 241 driver: $DB_DRIVER 242 import: $DB_DRIVER_IMPORT 243 dialect: $DB_DIALECT 244 open: $DB_DSN 245 ``` 246 247 ## Other Drivers 248 goose knows about some common SQL drivers, but it can still be used to run Go-based migrations with any driver supported by `database/sql`. An import path and known dialect are required. 249 250 Currently, available dialects are: "postgres", "mysql", "sqlite3", and "redshift" 251 252 To run Go-based migrations with another driver, specify its import path and dialect, as shown below. 253 254 ```yml 255 myenv: 256 driver: custom 257 open: custom open string 258 import: github.com/custom/driver 259 dialect: mysql 260 ``` 261 262 NOTE: Because migrations written in SQL are executed directly by the goose binary, only drivers compiled into goose may be used for these migrations. 263 264 ## Using goose with Heroku 265 266 These instructions assume that you're using [Keith Rarick's Heroku Go buildpack](https://github.com/kr/heroku-buildpack-go). First, add a file to your project called (e.g.) `install_goose.go` to trigger building of the goose executable during deployment, with these contents: 267 268 ```go 269 // use build constraints to work around http://code.google.com/p/go/issues/detail?id=4210 270 // +build heroku 271 272 // note: need at least one blank line after build constraint 273 package main 274 275 import _ "github.com/CloudCom/goose/cmd/goose" 276 ``` 277 278 [Set up your Heroku database(s) as usual.](https://devcenter.heroku.com/articles/heroku-postgresql) 279 280 Then make use of environment variable expansion in your `dbconf.yml`: 281 282 ```yml 283 production: 284 driver: postgres 285 open: $DATABASE_URL 286 ``` 287 288 To run goose in production, use `heroku run`: 289 290 heroku run goose -env production up 291 292 # Contributors 293 294 Thank you! 295 296 * Josh Bleecher Snyder (josharian) 297 * Abigail Walthall (ghthor) 298 * Daniel Heath (danielrheath) 299 * Chris Baynes (chris_baynes) 300 * Michael Gerow (gerow) 301 * Vytautas Ĺ altenis (rtfb) 302 * James Cooper (coopernurse) 303 * Gyepi Sam (gyepisam) 304 * Matt Sherman (clipperhouse) 305 * runner_mei 306 * John Luebs (jkl1337) 307 * Luke Hutton (lukehutton) 308 * Kevin Gorjan (kevingorjan) 309 * Brendan Fosberry (Fozz) 310 * Nate Guerin (gusennan)