github.com/bingtel/dbmate@v1.4.1/README.md (about) 1 # Dbmate 2 3 [](https://travis-ci.org/amacneil/dbmate) 4 [](https://goreportcard.com/report/github.com/amacneil/dbmate) 5 [](https://github.com/amacneil/dbmate/releases) 6 [](http://dbmate.readthedocs.org/) 7 8 Dbmate is a database migration tool, to keep your database schema in sync across multiple developers and your production servers. 9 10 It is a standalone command line tool, which can be used with Go, Node.js, Python, Ruby, PHP, or any other language or framework you are using to write database-backed applications. This is especially helpful if you are writing many services in different languages, and want to maintain some sanity with consistent development tools. 11 12 For a comparison between dbmate and other popular database schema migration tools, please see the [Alternatives](#alternatives) table. 13 14 ## Features 15 16 * Supports MySQL, PostgreSQL, and SQLite. 17 * Powerful, [purpose-built DSL](https://en.wikipedia.org/wiki/SQL#Data_definition) for writing schema migrations. 18 * Migrations are timestamp-versioned, to avoid version number conflicts with multiple developers. 19 * Migrations are run atomically inside a transaction. 20 * Supports creating and dropping databases (handy in development/test). 21 * Supports saving a `schema.sql` file to easily diff schema changes in git. 22 * Database connection URL is definied using an environment variable (`DATABASE_URL` by default), or specified on the command line. 23 * Built-in support for reading environment variables from your `.env` file. 24 * Easy to distribute, single self-contained binary. 25 26 ## Installation 27 28 **OSX** 29 30 Install using Homebrew: 31 32 ```sh 33 $ brew tap amacneil/dbmate 34 $ brew install dbmate 35 ``` 36 37 **Linux** 38 39 Download the binary directly: 40 41 ```sh 42 $ sudo curl -fsSL -o /usr/local/bin/dbmate https://github.com/amacneil/dbmate/releases/download/v1.4.1/dbmate-linux-amd64 43 $ sudo chmod +x /usr/local/bin/dbmate 44 ``` 45 46 **Docker** 47 48 You can run dbmate using the official docker image: 49 50 ```sh 51 $ docker run --rm amacneil/dbmate --help 52 ``` 53 54 **Heroku** 55 56 To use dbmate on Heroku, the easiest method is to store the linux binary in your git repository: 57 58 ```sh 59 $ mkdir -p bin 60 $ curl -fsSL -o bin/dbmate-heroku https://github.com/amacneil/dbmate/releases/download/v1.4.1/dbmate-linux-amd64 61 $ chmod +x bin/dbmate-heroku 62 $ git add bin/dbmate-heroku 63 $ git commit -m "Add dbmate binary" 64 $ git push heroku master 65 ``` 66 67 You can then run dbmate on heroku: 68 69 ```sh 70 $ heroku run bin/dbmate-heroku up 71 ``` 72 73 **Other** 74 75 Dbmate can be installed directly using `go get`: 76 77 ```sh 78 $ go get -u github.com/amacneil/dbmate 79 ``` 80 81 ## Commands 82 83 ```sh 84 dbmate # print help 85 dbmate new # generate a new migration file 86 dbmate up # create the database (if it does not already exist) and run any pending migrations 87 dbmate create # create the database 88 dbmate drop # drop the database 89 dbmate migrate # run any pending migrations 90 dbmate rollback # roll back the most recent migration 91 dbmate down # alias for rollback 92 dbmate dump # write the database schema.sql file 93 dbmate wait # wait for the database server to become available 94 ``` 95 96 ## Usage 97 98 Dbmate locates your database using the `DATABASE_URL` environment variable by default. If you are writing a [twelve-factor app](http://12factor.net/), you should be storing all connection strings in environment variables. 99 100 To make this easy in development, dbmate looks for a `.env` file in the current directory, and treats any variables listed there as if they were specified in the current environment (existing environment variables take preference, however). 101 102 If you do not already have a `.env` file, create one and add your database connection URL: 103 104 ```sh 105 $ cat .env 106 DATABASE_URL="postgres://postgres@127.0.0.1:5432/myapp_development?sslmode=disable" 107 ``` 108 109 `DATABASE_URL` should be specified in the following format: 110 111 ``` 112 protocol://username:password@host:port/database_name?options 113 ``` 114 115 * `protocol` must be one of `mysql`, `postgres`, `postgresql`, `sqlite`, `sqlite3` 116 * `host` can be either a hostname or IP address 117 * `options` are driver-specific (refer to the underlying Go SQL drivers if you wish to use these) 118 119 **MySQL** 120 121 ```sh 122 DATABASE_URL="mysql://username:password@127.0.0.1:3306/database_name" 123 ``` 124 125 **PostgreSQL** 126 127 When connecting to Postgres, you may need to add the `sslmode=disable` option to your connection string, as dbmate by default requires a TLS connection (some other frameworks/languages allow unencrypted connections by default). 128 129 ```sh 130 DATABASE_URL="postgres://username:password@127.0.0.1:5432/database_name?sslmode=disable" 131 ``` 132 133 **SQLite** 134 135 SQLite databases are stored on the filesystem, so you do not need to specify a host. By default, files are relative to the current directory. For example, the following will create a database at `./db/database_name.sqlite3`: 136 137 ```sh 138 DATABASE_URL="sqlite:///db/database_name.sqlite3" 139 ``` 140 141 To specify an absolute path, add an additional forward slash to the path. The following will create a database at `/tmp/database_name.sqlite3`: 142 143 ```sh 144 DATABASE_URL="sqlite:////tmp/database_name.sqlite3" 145 ``` 146 147 ### Creating Migrations 148 149 To create a new migration, run `dbmate new create_users_table`. You can name the migration anything you like. This will create a file `db/migrations/20151127184807_create_users_table.sql` in the current directory: 150 151 ```sql 152 -- migrate:up 153 154 -- migrate:down 155 ``` 156 157 To write a migration, simply add your SQL to the `migrate:up` section: 158 159 ```sql 160 -- migrate:up 161 create table users ( 162 id integer, 163 name varchar(255), 164 email varchar(255) not null 165 ); 166 167 -- migrate:down 168 ``` 169 170 > Note: Migration files are named in the format `[version]_[description].sql`. Only the version (defined as all leading numeric characters in the file name) is recorded in the database, so you can safely rename a migration file without having any effect on its current application state. 171 172 ### Running Migrations 173 174 Run `dbmate up` to run any pending migrations. 175 176 ```sh 177 $ dbmate up 178 Creating: myapp_development 179 Applying: 20151127184807_create_users_table.sql 180 Writing: ./db/schema.sql 181 ``` 182 183 > Note: `dbmate up` will create the database if it does not already exist (assuming the current user has permission to create databases). If you want to run migrations without creating the database, run `dbmate migrate`. 184 185 ### Rolling Back Migrations 186 187 By default, dbmate doesn't know how to roll back a migration. In development, it's often useful to be able to revert your database to a previous state. To accomplish this, implement the `migrate:down` section: 188 189 ```sql 190 -- migrate:up 191 create table users ( 192 id integer, 193 name varchar(255), 194 email varchar(255) not null 195 ); 196 197 -- migrate:down 198 drop table users; 199 ``` 200 201 Run `dbmate rollback` to roll back the most recent migration: 202 203 ```sh 204 $ dbmate rollback 205 Rolling back: 20151127184807_create_users_table.sql 206 Writing: ./db/schema.sql 207 ``` 208 209 ### Schema File 210 211 When you run the `up`, `migrate`, or `rollback` commands, dbmate will automatically create a `./db/schema.sql` file containing a complete representation of your database schema. Dbmate keeps this file up to date for you, so you should not manually edit it. 212 213 It is recommended to check this file into source control, so that you can easily review changes to the schema in commits or pull requests. It's also possible to use this file when you want to quickly load a database schema, without running each migration sequentially (for example in your test harness). However, if you do not wish to save this file, you could add it to `.gitignore`, or pass the `--no-dump-schema` command line option. 214 215 To dump the `schema.sql` file without performing any other actions, run `dbmate dump`. Unlike other dbmate actions, this command relies on the respective `pg_dump`, `mysqldump`, or `sqlite3` commands being available in your PATH. If these tools are not available, dbmate will silenty skip the schema dump step during `up`, `migrate`, or `rollback` actions. You can diagnose the issue by running `dbmate dump` and looking at the output: 216 217 ```sh 218 $ dbmate dump 219 exec: "pg_dump": executable file not found in $PATH 220 ``` 221 222 On Ubuntu or Debian systems, you can fix this by installing `postgresql-client`, `mysql-client`, or `sqlite3` respectively. Ensure that the package version you install is greater than or equal to the version running on your database server. 223 224 > Note: The `schema.sql` file will contain a complete schema for your database, even if some tables or columns were created outside of dbmate migrations. 225 226 ### Waiting For The Database 227 228 If you use a Docker development environment for your project, you may encounter issues with the database not being immediately ready when running migrations or unit tests. This can be due to the database server having only just started. 229 230 In general, your application should be resilient to not having a working database connection on startup. However, for the purpose of running migrations or unit tests, this is not practical. The `wait` command avoids this situation by allowing you to pause a script or other application until the database is available. Dbmate will attempt a connection to the database server every second, up to a maximum of 60 seconds. 231 232 If the database is available, `wait` will return no output: 233 234 ```sh 235 $ dbmate wait 236 ``` 237 238 If the database is unavailable, `wait` will block until the database becomes available: 239 240 ```sh 241 $ dbmate wait 242 Waiting for database.... 243 ``` 244 245 You can chain `wait` together with other commands if you sometimes see failures caused by the database not yet being ready: 246 247 ```sh 248 $ dbmate wait && dbmate up 249 Waiting for database.... 250 Creating: myapp_development 251 ``` 252 253 If the database is still not available after 60 seconds, the command will return an error: 254 255 ```sh 256 $ dbmate wait 257 Waiting for database............................................................ 258 Error: unable to connect to database: pq: role "foobar" does not exist 259 ``` 260 261 Please note that the `wait` command does not verify whether your specified database exists, only that the server is available and ready (so it will return success if the database server is available, but your database has not yet been created). 262 263 ### Options 264 265 The following command line options are available with all commands. You must use command line arguments in the order `dbmate [global options] command [command options]`. 266 267 * `--env, -e "DATABASE_URL"` - specify an environment variable to read the database connection URL from. 268 * `--migrations-dir, -d "./db/migrations"` - where to keep the migration files. 269 * `--schema-file, -s "./db/schema.sql"` - a path to keep the schema.sql file. 270 * `--no-dump-schema` - don't auto-update the schema.sql file on migrate/rollback 271 272 For example, before running your test suite, you may wish to drop and recreate the test database. One easy way to do this is to store your test database connection URL in the `TEST_DATABASE_URL` environment variable: 273 274 ```sh 275 $ cat .env 276 TEST_DATABASE_URL="postgres://postgres@127.0.0.1:5432/myapp_test?sslmode=disable" 277 ``` 278 279 You can then specify this environment variable in your test script (Makefile or similar): 280 281 ```sh 282 $ dbmate -e TEST_DATABASE_URL drop 283 Dropping: myapp_test 284 $ dbmate -e TEST_DATABASE_URL --no-dump-schema up 285 Creating: myapp_test 286 Applying: 20151127184807_create_users_table.sql 287 ``` 288 289 ## FAQ 290 291 **How do I use dbmate under Alpine linux?** 292 293 Alpine linux uses [musl libc](https://www.musl-libc.org/), which is incompatible with how we build SQLite support (using [cgo](https://golang.org/cmd/cgo/)). If you want Alpine linux support, and don't mind sacrificing SQLite support, please use the `dbmate-linux-musl-amd64` build found on the [releases page](https://github.com/amacneil/dbmate/releases). 294 295 ## Alternatives 296 297 Why another database schema migration tool? Dbmate was inspired by many other tools, primarily [Active Record Migrations](http://guides.rubyonrails.org/active_record_migrations.html), with the goals of being trivial to configure, and language & framework independent. Here is a comparison between dbmate and other popular migration tools. 298 299 | | [goose](https://bitbucket.org/liamstask/goose/) | [sql-migrate](https://github.com/rubenv/sql-migrate) | [mattes/migrate](https://github.com/mattes/migrate) | [activerecord](http://guides.rubyonrails.org/active_record_migrations.html) | [sequelize](http://docs.sequelizejs.com/manual/tutorial/migrations.html) | [dbmate](https://github.com/amacneil/dbmate) | 300 | --- |:---:|:---:|:---:|:---:|:---:|:---:| 301 | **Features** ||||||| 302 |Plain SQL migration files|:white_check_mark:|:white_check_mark:|:white_check_mark:|||:white_check_mark:| 303 |Support for creating and dropping databases||||:white_check_mark:||:white_check_mark:| 304 |Support for saving schema dump files||||:white_check_mark:||:white_check_mark:| 305 |Timestamp-versioned migration files|:white_check_mark:|||:white_check_mark:|:white_check_mark:|:white_check_mark:| 306 |Ability to wait for database to become ready||||||:white_check_mark:| 307 |Database connection string loaded from environment variables||||||:white_check_mark:| 308 |Automatically load .env file||||||:white_check_mark:| 309 |No separate configuration file||||:white_check_mark:|:white_check_mark:|:white_check_mark:| 310 |Language/framework independent|:eight_pointed_black_star:|:eight_pointed_black_star:|:eight_pointed_black_star:|||:white_check_mark:| 311 | **Drivers** ||||||| 312 |PostgreSQL|:white_check_mark:|:white_check_mark:|:white_check_mark:|:white_check_mark:|:white_check_mark:|:white_check_mark:| 313 |MySQL|:white_check_mark:|:white_check_mark:|:white_check_mark:|:white_check_mark:|:white_check_mark:|:white_check_mark:| 314 |SQLite|:white_check_mark:|:white_check_mark:|:white_check_mark:|:white_check_mark:|:white_check_mark:|:white_check_mark:| 315 316 > :eight_pointed_black_star: In theory these tools could be used with other languages, but a Go development environment is required because binary builds are not provided. 317 318 *If you notice any inaccuracies in this table, please [propose a change](https://github.com/amacneil/dbmate/edit/master/README.md).* 319 320 ## Contributing 321 322 Dbmate is written in Go, pull requests are welcome. 323 324 Tests are run against a real database using docker-compose. To build a docker image and run the tests: 325 326 ```sh 327 $ make docker 328 ``` 329 330 To start a development shell: 331 332 ```sh 333 $ docker-compose run --rm dbmate bash 334 ```