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