github.com/amacneil/dbmate@v1.16.3-0.20230225174651-ca89b10d75d7/README.md (about) 1 # Dbmate 2 3 [![Release](https://img.shields.io/github/release/amacneil/dbmate.svg)](https://github.com/amacneil/dbmate/releases) 4 [![Go Report](https://goreportcard.com/badge/github.com/amacneil/dbmate)](https://goreportcard.com/report/github.com/amacneil/dbmate) 5 [![Reference](https://img.shields.io/badge/go.dev-reference-blue?logo=go&logoColor=white)](https://pkg.go.dev/github.com/amacneil/dbmate/pkg/dbmate) 6 7 Dbmate is a database migration tool that will keep your database schema in sync across multiple developers and your production servers. 8 9 It is a standalone command line tool that 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 multiple services in different languages, and want to maintain some sanity with consistent development tools. 10 11 For a comparison between dbmate and other popular database schema migration tools, please see [Alternatives](#alternatives). 12 13 ## Table of Contents 14 15 - [Features](#features) 16 - [Installation](#installation) 17 - [Commands](#commands) 18 - [Command Line Options](#command-line-options) 19 - [Usage](#usage) 20 - [Connecting to the Database](#connecting-to-the-database) 21 - [PostgreSQL](#postgresql) 22 - [MySQL](#mysql) 23 - [SQLite](#sqlite) 24 - [ClickHouse](#clickhouse) 25 - [Creating Migrations](#creating-migrations) 26 - [Running Migrations](#running-migrations) 27 - [Rolling Back Migrations](#rolling-back-migrations) 28 - [Migration Options](#migration-options) 29 - [Waiting For The Database](#waiting-for-the-database) 30 - [Exporting Schema File](#exporting-schema-file) 31 - [Library](#library) 32 - [Use dbmate as a library](#use-dbmate-as-a-library) 33 - [Embedding migrations](#embedding-migrations) 34 - [Concepts](#concepts) 35 - [Migration files](#migration-files) 36 - [Schema file](#schema-file) 37 - [Schema migrations table](#schema-migrations-table) 38 - [Alternatives](#alternatives) 39 - [Contributing](#contributing) 40 41 ## Features 42 43 - Supports MySQL, PostgreSQL, SQLite, and ClickHouse. 44 - Uses plain SQL for writing schema migrations. 45 - Migrations are timestamp-versioned, to avoid version number conflicts with multiple developers. 46 - Migrations are run atomically inside a transaction. 47 - Supports creating and dropping databases (handy in development/test). 48 - Supports saving a `schema.sql` file to easily diff schema changes in git. 49 - Database connection URL is defined using an environment variable (`DATABASE_URL` by default), or specified on the command line. 50 - Built-in support for reading environment variables from your `.env` file. 51 - Easy to distribute, single self-contained binary. 52 53 ## Installation 54 55 **macOS** 56 57 Install using [Homebrew](https://brew.sh/): 58 59 ```sh 60 $ brew install dbmate 61 ``` 62 63 **Linux** 64 65 Download the binary directly: 66 67 ```sh 68 $ sudo curl -fsSL -o /usr/local/bin/dbmate https://github.com/amacneil/dbmate/releases/latest/download/dbmate-linux-amd64 69 $ sudo chmod +x /usr/local/bin/dbmate 70 ``` 71 72 **Windows** 73 74 Install using [Scoop](https://scoop.sh) 75 76 ```pwsh 77 scoop install dbmate 78 ``` 79 80 **Docker** 81 82 Docker images are published to both Docker Hub ([`amacneil/dbmate`](https://hub.docker.com/r/amacneil/dbmate)) and Github Container Registry ([`ghcr.io/amacneil/dbmate`](https://ghcr.io/amacneil/dbmate)). 83 84 Remember to set `--network=host` or see [this comment](https://github.com/amacneil/dbmate/issues/128#issuecomment-615924611) for more tips on using dbmate with docker networking): 85 86 ```sh 87 $ docker run --rm -it --network=host ghcr.io/amacneil/dbmate --help 88 ``` 89 90 If you wish to create or apply migrations, you will need to use Docker's [bind mount](https://docs.docker.com/storage/bind-mounts/) feature to make your local working directory (`pwd`) available inside the dbmate container: 91 92 ```sh 93 $ docker run --rm -it --network=host -v "$(pwd)/db:/db" ghcr.io/amacneil/dbmate new create_users_table 94 ``` 95 96 **Heroku** 97 98 To use dbmate on Heroku, the easiest method is to store the linux binary in your git repository: 99 100 ```sh 101 $ mkdir -p bin 102 $ curl -fsSL -o bin/dbmate https://github.com/amacneil/dbmate/releases/latest/download/dbmate-linux-amd64 103 $ chmod +x bin/dbmate 104 $ git add bin/dbmate 105 $ git commit -m "Add dbmate binary" 106 $ git push heroku master 107 ``` 108 109 You can then run dbmate on heroku: 110 111 ```sh 112 $ heroku run bin/dbmate up 113 ``` 114 115 ## Commands 116 117 ```sh 118 dbmate --help # print usage help 119 dbmate new # generate a new migration file 120 dbmate up # create the database (if it does not already exist) and run any pending migrations 121 dbmate create # create the database 122 dbmate drop # drop the database 123 dbmate migrate # run any pending migrations 124 dbmate rollback # roll back the most recent migration 125 dbmate down # alias for rollback 126 dbmate status # show the status of all migrations (supports --exit-code and --quiet) 127 dbmate dump # write the database schema.sql file 128 dbmate wait # wait for the database server to become available 129 ``` 130 131 ### Command Line Options 132 133 The following options are available with all commands. You must use command line arguments in the order `dbmate [global options] command [command options]`. Most options can also be configured via environment variables (and loaded from your `.env` file, which is helpful to share configuration between team members). 134 135 - `--url, -u "protocol://host:port/dbname"` - specify the database url directly. _(env: `DATABASE_URL`)_ 136 - `--env, -e "DATABASE_URL"` - specify an environment variable to read the database connection URL from. 137 - `--migrations-dir, -d "./db/migrations"` - where to keep the migration files. _(env: `DBMATE_MIGRATIONS_DIR`)_ 138 - `--migrations-table "schema_migrations"` - database table to record migrations in. _(env: `DBMATE_MIGRATIONS_TABLE`)_ 139 - `--schema-file, -s "./db/schema.sql"` - a path to keep the schema.sql file. _(env: `DBMATE_SCHEMA_FILE`)_ 140 - `--no-dump-schema` - don't auto-update the schema.sql file on migrate/rollback _(env: `DBMATE_NO_DUMP_SCHEMA`)_ 141 - `--wait` - wait for the db to become available before executing the subsequent command _(env: `DBMATE_WAIT`)_ 142 - `--wait-timeout 60s` - timeout for --wait flag _(env: `DBMATE_WAIT_TIMEOUT`)_ 143 144 ## Usage 145 146 ### Connecting to the Database 147 148 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. 149 150 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). 151 152 If you do not already have a `.env` file, create one and add your database connection URL: 153 154 ```sh 155 $ cat .env 156 DATABASE_URL="postgres://postgres@127.0.0.1:5432/myapp_development?sslmode=disable" 157 ``` 158 159 `DATABASE_URL` should be specified in the following format: 160 161 ``` 162 protocol://username:password@host:port/database_name?options 163 ``` 164 165 - `protocol` must be one of `mysql`, `postgres`, `postgresql`, `sqlite`, `sqlite3`, `clickhouse` 166 - `host` can be either a hostname or IP address 167 - `options` are driver-specific (refer to the underlying Go SQL drivers if you wish to use these) 168 169 Dbmate can also load the connection URL from a different environment variable. 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: 170 171 ```sh 172 $ cat .env 173 DATABASE_URL="postgres://postgres@127.0.0.1:5432/myapp_dev?sslmode=disable" 174 TEST_DATABASE_URL="postgres://postgres@127.0.0.1:5432/myapp_test?sslmode=disable" 175 ``` 176 177 You can then specify this environment variable in your test script (Makefile or similar): 178 179 ```sh 180 $ dbmate -e TEST_DATABASE_URL drop 181 Dropping: myapp_test 182 $ dbmate -e TEST_DATABASE_URL --no-dump-schema up 183 Creating: myapp_test 184 Applying: 20151127184807_create_users_table.sql 185 ``` 186 187 Alternatively, you can specify the url directly on the command line: 188 189 ```sh 190 $ dbmate -u "postgres://postgres@127.0.0.1:5432/myapp_test?sslmode=disable" up 191 ``` 192 193 The only advantage of using `dbmate -e TEST_DATABASE_URL` over `dbmate -u $TEST_DATABASE_URL` is that the former takes advantage of dbmate's automatic `.env` file loading. 194 195 #### PostgreSQL 196 197 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). 198 199 ```sh 200 DATABASE_URL="postgres://username:password@127.0.0.1:5432/database_name?sslmode=disable" 201 ``` 202 203 A `socket` or `host` parameter can be specified to connect through a unix socket (note: specify the directory only): 204 205 ```sh 206 DATABASE_URL="postgres://username:password@/database_name?socket=/var/run/postgresql" 207 ``` 208 209 A `search_path` parameter can be used to specify the [current schema](https://www.postgresql.org/docs/13/ddl-schemas.html#DDL-SCHEMAS-PATH) while applying migrations, as well as for dbmate's `schema_migrations` table. 210 If the schema does not exist, it will be created automatically. If multiple comma-separated schemas are passed, the first will be used for the `schema_migrations` table. 211 212 ```sh 213 DATABASE_URL="postgres://username:password@127.0.0.1:5432/database_name?search_path=myschema" 214 ``` 215 216 ```sh 217 DATABASE_URL="postgres://username:password@127.0.0.1:5432/database_name?search_path=myschema,public" 218 ``` 219 220 #### MySQL 221 222 ```sh 223 DATABASE_URL="mysql://username:password@127.0.0.1:3306/database_name" 224 ``` 225 226 A `socket` parameter can be specified to connect through a unix socket: 227 228 ```sh 229 DATABASE_URL="mysql://username:password@/database_name?socket=/var/run/mysqld/mysqld.sock" 230 ``` 231 232 #### SQLite 233 234 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.sqlite3`: 235 236 ```sh 237 DATABASE_URL="sqlite:db/database.sqlite3" 238 ``` 239 240 To specify an absolute path, add a forward slash to the path. The following will create a database at `/tmp/database.sqlite3`: 241 242 ```sh 243 DATABASE_URL="sqlite:/tmp/database.sqlite3" 244 ``` 245 246 #### ClickHouse 247 248 ```sh 249 DATABASE_URL="clickhouse://username:password@127.0.0.1:9000/database_name" 250 ``` 251 252 or 253 254 ```sh 255 DATABASE_URL="clickhouse://127.0.0.1:9000?username=username&password=password&database=database_name" 256 ``` 257 258 [See other supported connection options](https://github.com/ClickHouse/clickhouse-go#dsn). 259 260 ### Creating Migrations 261 262 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: 263 264 ```sql 265 -- migrate:up 266 267 -- migrate:down 268 ``` 269 270 To write a migration, simply add your SQL to the `migrate:up` section: 271 272 ```sql 273 -- migrate:up 274 create table users ( 275 id integer, 276 name varchar(255), 277 email varchar(255) not null 278 ); 279 280 -- migrate:down 281 ``` 282 283 > 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. 284 285 ### Running Migrations 286 287 Run `dbmate up` to run any pending migrations. 288 289 ```sh 290 $ dbmate up 291 Creating: myapp_development 292 Applying: 20151127184807_create_users_table.sql 293 Writing: ./db/schema.sql 294 ``` 295 296 > 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`. 297 298 Pending migrations are always applied in numerical order. However, dbmate does not prevent migrations from being applied out of order if they are committed independently (for example: if a developer has been working on a branch for a long time, and commits a migration which has a lower version number than other already-applied migrations, dbmate will simply apply the pending migration). See [#159](https://github.com/amacneil/dbmate/issues/159) for a more detailed explanation. 299 300 ### Rolling Back Migrations 301 302 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: 303 304 ```sql 305 -- migrate:up 306 create table users ( 307 id integer, 308 name varchar(255), 309 email varchar(255) not null 310 ); 311 312 -- migrate:down 313 drop table users; 314 ``` 315 316 Run `dbmate rollback` to roll back the most recent migration: 317 318 ```sh 319 $ dbmate rollback 320 Rolling back: 20151127184807_create_users_table.sql 321 Writing: ./db/schema.sql 322 ``` 323 324 ### Migration Options 325 326 dbmate supports options passed to a migration block in the form of `key:value` pairs. List of supported options: 327 328 - `transaction` 329 330 **transaction** 331 332 `transaction` is useful if you need to run some SQL which cannot be executed from within a transaction. For example, in Postgres, you would need to disable transactions for migrations that alter an enum type to add a value: 333 334 ```sql 335 -- migrate:up transaction:false 336 ALTER TYPE colors ADD VALUE 'orange' AFTER 'red'; 337 ``` 338 339 `transaction` will default to `true` if your database supports it. 340 341 ### Waiting For The Database 342 343 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. 344 345 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. 346 347 If the database is available, `wait` will return no output: 348 349 ```sh 350 $ dbmate wait 351 ``` 352 353 If the database is unavailable, `wait` will block until the database becomes available: 354 355 ```sh 356 $ dbmate wait 357 Waiting for database.... 358 ``` 359 360 You can also use the `--wait` flag with other commands if you sometimes see failures caused by the database not yet being ready: 361 362 ```sh 363 $ dbmate --wait up 364 Waiting for database.... 365 Creating: myapp_development 366 ``` 367 368 You can customize the timeout using `--wait-timeout` (default 60s). If the database is still not available, the command will return an error: 369 370 ```sh 371 $ dbmate --wait-timeout=5s wait 372 Waiting for database..... 373 Error: unable to connect to database: dial tcp 127.0.0.1:5432: connect: connection refused 374 ``` 375 376 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). 377 378 ### Exporting Schema File 379 380 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. 381 382 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 your `.gitignore`, or pass the `--no-dump-schema` command line option. 383 384 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: 385 386 ```sh 387 $ dbmate dump 388 exec: "pg_dump": executable file not found in $PATH 389 ``` 390 391 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. 392 393 > 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. 394 395 ## Library 396 397 ### Use dbmate as a library 398 399 Dbmate is designed to be used as a CLI with any language or framework, but it can also be used as a library in a Go application. 400 401 Here is a simple example. Remember to import the driver you need! 402 403 ```go 404 package main 405 406 import ( 407 "net/url" 408 409 "github.com/amacneil/dbmate/pkg/dbmate" 410 _ "github.com/amacneil/dbmate/pkg/driver/sqlite" 411 ) 412 413 func main() { 414 u, _ := url.Parse("sqlite:foo.sqlite3") 415 db := dbmate.New(u) 416 417 err := db.CreateAndMigrate() 418 if err != nil { 419 panic(err) 420 } 421 } 422 ``` 423 424 See the [reference documentation](https://pkg.go.dev/github.com/amacneil/dbmate/pkg/dbmate) for more options. 425 426 ### Embedding migrations 427 428 Migrations can be embedded into your application binary using Go's [embed](https://pkg.go.dev/embed) functionality. 429 430 Use `db.FS` to specify the filesystem used for reading migrations: 431 432 ```go 433 package main 434 435 import ( 436 "embed" 437 "fmt" 438 "net/url" 439 440 "github.com/amacneil/dbmate/pkg/dbmate" 441 _ "github.com/amacneil/dbmate/pkg/driver/sqlite" 442 ) 443 444 //go:embed db/migrations/*.sql 445 var fs embed.FS 446 447 func main() { 448 u, _ := url.Parse("sqlite:foo.sqlite3") 449 db := dbmate.New(u) 450 db.FS = fs 451 452 fmt.Println("Migrations:") 453 migrations, err := db.FindMigrations() 454 if err != nil { 455 panic(err) 456 } 457 for _, m := range migrations { 458 fmt.Println(m.Version, m.FilePath) 459 } 460 461 fmt.Println("\nApplying...") 462 err = db.CreateAndMigrate() 463 if err != nil { 464 panic(err) 465 } 466 } 467 ``` 468 469 ## Concepts 470 471 ### Migration files 472 473 Migration files are very simple, and are stored in `./db/migrations` by default. You can create a new migration file named `[date]_create_users.sql` by running `dbmate new create_users`. 474 Here is an example: 475 476 ```sql 477 -- migrate:up 478 create table users ( 479 id integer, 480 name varchar(255), 481 ); 482 483 -- migrate:down 484 drop table if exists users; 485 ``` 486 487 Both up and down migrations are stored in the same file, for ease of editing. When you apply a migration dbmate only stores the version number, not the contents, so you should always rollback a migration before modifying its contents. For this reason, you can safely rename a migration file without affecting its applied status, as long as you keep the version number intact. 488 489 ### Schema file 490 491 The schema file is written to `./db/schema.sql` by default. It is a complete dump of your database schema, including any applied migrations, and any other modifications you have made. 492 493 This file should be checked in to source control, so that you can easily compare the diff of a migration. You can use the schema file to quickly restore your database without needing to run all migrations. 494 495 ### Schema migrations table 496 497 Dbmate stores a record of each applied migration in table named `schema_migrations`. This table will be created for you automatically if it does not already exist. 498 499 The table is very simple: 500 501 ```sql 502 CREATE TABLE IF NOT EXISTS schema_migrations ( 503 version VARCHAR(255) PRIMARY KEY 504 ) 505 ``` 506 507 You can customize the name of this table using the `--migrations-table` flag or `DBMATE_MIGRATIONS_TABLE` environment variable. 508 509 ## Alternatives 510 511 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. 512 513 | | [dbmate](https://github.com/amacneil/dbmate) | [goose](https://github.com/pressly/goose) | [sql-migrate](https://github.com/rubenv/sql-migrate) | [golang-migrate](https://github.com/golang-migrate/migrate) | [activerecord](http://guides.rubyonrails.org/active_record_migrations.html) | [sequelize](http://docs.sequelizejs.com/manual/tutorial/migrations.html) | [flyway](https://flywaydb.org/) | 514 | ------------------------------------------------------------ | :------------------------------------------: | :---------------------------------------: | :--------------------------------------------------: | :---------------------------------------------------------: | :-------------------------------------------------------------------------: | :----------------------------------------------------------------------: | :-----------------------------: | 515 | **Features** | 516 | Plain SQL migration files | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | | | :white_check_mark: | 517 | Support for creating and dropping databases | :white_check_mark: | | | | :white_check_mark: | | | 518 | Support for saving schema dump files | :white_check_mark: | | | | :white_check_mark: | | | 519 | Timestamp-versioned migration files | :white_check_mark: | :white_check_mark: | | :white_check_mark: | :white_check_mark: | :white_check_mark: | | 520 | Custom schema migrations table | :white_check_mark: | | :white_check_mark: | | | :white_check_mark: | :white_check_mark: | 521 | Ability to wait for database to become ready | :white_check_mark: | | | | | | | 522 | Database connection string loaded from environment variables | :white_check_mark: | | | | | | :white_check_mark: | 523 | Automatically load .env file | :white_check_mark: | | | | | | | 524 | No separate configuration file | :white_check_mark: | | | :white_check_mark: | :white_check_mark: | :white_check_mark: | | 525 | Language/framework independent | :white_check_mark: | :white_check_mark: | | :white_check_mark: | | | :white_check_mark: | 526 | **Drivers** | 527 | PostgreSQL | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | 528 | MySQL | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | 529 | SQLite | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | 530 | CliсkHouse | :white_check_mark: | | | :white_check_mark: | :white_check_mark: | :white_check_mark: | | 531 532 _If you notice any inaccuracies in this table, please [propose a change](https://github.com/amacneil/dbmate/edit/main/README.md)._ 533 534 ## Contributing 535 536 Dbmate is written in Go, pull requests are welcome. 537 538 Tests are run against a real database using docker-compose. To build a docker image and run the tests: 539 540 ```sh 541 $ make docker-all 542 ``` 543 544 To start a development shell: 545 546 ```sh 547 $ make docker-sh 548 ```