Dbmate is a database migration tool, to keep your database schema in sync across multiple developers and your production servers.
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.
For a comparison between dbmate and other popular database schema migration tools, please see the Alternatives table.
Table of Contents
- Supports MySQL, PostgreSQL, SQLite, and ClickHouse.
- Uses plain SQL for writing schema migrations.
- Migrations are timestamp-versioned, to avoid version number conflicts with multiple developers.
- Migrations are run atomically inside a transaction.
- Supports creating and dropping databases (handy in development/test).
- Supports saving a
schema.sqlfile to easily diff schema changes in git.
- Database connection URL is definied using an environment variable (
DATABASE_URLby default), or specified on the command line.
- Built-in support for reading environment variables from your
- Easy to distribute, single self-contained binary.
Install using Homebrew:
$ brew install dbmate
Download the binary directly:
$ sudo curl -fsSL -o /usr/local/bin/dbmate https://github.com/amacneil/dbmate/releases/latest/download/dbmate-linux-amd64 $ sudo chmod +x /usr/local/bin/dbmate
Remember to set
--network=host or see this comment for more tips on using dbmate with docker networking):
$ docker run --rm -it --network=host ghcr.io/amacneil/dbmate:1 --help
If you wish to create or apply migrations, you will need to use Docker's bind mount feature to make your local working directory (
pwd) available inside the dbmate container:
$ docker run --rm -it --network=host -v "$(pwd)/db:/db" ghcr.io/amacneil/dbmate:1 new create_users_table
To use dbmate on Heroku, the easiest method is to store the linux binary in your git repository:
$ mkdir -p bin $ curl -fsSL -o bin/dbmate https://github.com/amacneil/dbmate/releases/latest/download/dbmate-linux-amd64 $ chmod +x bin/dbmate $ git add bin/dbmate $ git commit -m "Add dbmate binary" $ git push heroku master
You can then run dbmate on heroku:
$ heroku run bin/dbmate up
dbmate --help # print usage help dbmate new # generate a new migration file dbmate up # create the database (if it does not already exist) and run any pending migrations dbmate create # create the database dbmate drop # drop the database dbmate migrate # run any pending migrations dbmate rollback # roll back the most recent migration dbmate down # alias for rollback dbmate status # show the status of all migrations (supports --exit-code and --quiet) dbmate dump # write the database schema.sql file dbmate wait # wait for the database server to become available
Command Line Options
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).
--url, -u "protocol://host:port/dbname"- specify the database url directly. (env:
--env, -e "DATABASE_URL"- specify an environment variable to read the database connection URL from.
--migrations-dir, -d "./db/migrations"- where to keep the migration files. (env:
--migrations-table "schema_migrations"- database table to record migrations in. (env:
--schema-file, -s "./db/schema.sql"- a path to keep the schema.sql file. (env:
--no-dump-schema- don't auto-update the schema.sql file on migrate/rollback (env:
--wait- wait for the db to become available before executing the subsequent command (env:
--wait-timeout 60s- timeout for --wait flag (env:
Connecting to the Database
Dbmate locates your database using the
DATABASE_URL environment variable by default. If you are writing a twelve-factor app, you should be storing all connection strings in environment variables.
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).
If you do not already have a
.env file, create one and add your database connection URL:
$ cat .env DATABASE_URL="postgres://email@example.com:5432/myapp_development?sslmode=disable"
DATABASE_URL should be specified in the following format:
protocolmust be one of
hostcan be either a hostname or IP address
optionsare driver-specific (refer to the underlying Go SQL drivers if you wish to use these)
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:
$ cat .env DATABASE_URL="postgres://firstname.lastname@example.org:5432/myapp_dev?sslmode=disable" TEST_DATABASE_URL="postgres://email@example.com:5432/myapp_test?sslmode=disable"
You can then specify this environment variable in your test script (Makefile or similar):
$ dbmate -e TEST_DATABASE_URL drop Dropping: myapp_test $ dbmate -e TEST_DATABASE_URL --no-dump-schema up Creating: myapp_test Applying: 20151127184807_create_users_table.sql
Alternatively, you can specify the url directly on the command line:
$ dbmate -u "postgres://firstname.lastname@example.org:5432/myapp_test?sslmode=disable" up
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.
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).
host parameter can be specified to connect through a unix socket (note: specify the directory only):
search_path parameter can be used to specify the current schema while applying migrations, as well as for dbmate's
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
socket parameter can be specified to connect through a unix socket:
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
To specify an absolute path, add a forward slash to the path. The following will create a database at
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:
-- migrate:up -- migrate:down
To write a migration, simply add your SQL to the
-- migrate:up create table users ( id integer, name varchar(255), email varchar(255) not null ); -- migrate:down
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.
dbmate up to run any pending migrations.
$ dbmate up Creating: myapp_development Applying: 20151127184807_create_users_table.sql Writing: ./db/schema.sql
dbmate upwill 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
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 for a more detailed explanation.
Rolling Back Migrations
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:up create table users ( id integer, name varchar(255), email varchar(255) not null ); -- migrate:down drop table users;
dbmate rollback to roll back the most recent migration:
$ dbmate rollback Rolling back: 20151127184807_create_users_table.sql Writing: ./db/schema.sql
dbmate supports options passed to a migration block in the form of
key:value pairs. List of supported options:
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:
-- migrate:up transaction:false ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';
transaction will default to
true if your database supports it.
Waiting For The Database
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.
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.
If the database is available,
wait will return no output:
$ dbmate wait
If the database is unavailable,
wait will block until the database becomes available:
$ dbmate wait Waiting for database....
You can also use the
--wait flag with other commands if you sometimes see failures caused by the database not yet being ready:
$ dbmate --wait up Waiting for database.... Creating: myapp_development
You can customize the timeout using
--wait-timeout (default 60s). If the database is still not available, the command will return an error:
$ dbmate --wait-timeout=5s wait Waiting for database..... Error: unable to connect to database: dial tcp 127.0.0.1:5432: connect: connection refused
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).
Exporting Schema File
When you run the
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.
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.
To dump the
schema.sql file without performing any other actions, run
dbmate dump. Unlike other dbmate actions, this command relies on the respective
sqlite3 commands being available in your PATH. If these tools are not available, dbmate will silenty skip the schema dump step during
rollback actions. You can diagnose the issue by running
dbmate dump and looking at the output:
$ dbmate dump exec: "pg_dump": executable file not found in $PATH
On Ubuntu or Debian systems, you can fix this by installing
sqlite3 respectively. Ensure that the package version you install is greater than or equal to the version running on your database server.
schema.sqlfile will contain a complete schema for your database, even if some tables or columns were created outside of dbmate migrations.
By default, dbmate stores a record of each applied migration in a
schema_migrations table. This table will be created for you automatically if it does not already exist. The table schema is very simple:
CREATE TABLE IF NOT EXISTS schema_migrations ( version VARCHAR(255) PRIMARY KEY )
Dbmate records only the version number of applied migrations, so you can safely rename a migration file without affecting its applied status.
You can customize the name of this table using the
--migrations-table flag or
$DBMATE_MIGRATIONS_TABLE environment variable. If you already have a table with this name (possibly from a previous migration tool), you should either manually update it to conform to this schema, or configure dbmate to use a different table name.
Why another database schema migration tool? Dbmate was inspired by many other tools, primarily Active Record Migrations, with the goals of being trivial to configure, and language & framework independent. Here is a comparison between dbmate and other popular migration tools.
|Plain SQL migration files|
|Support for creating and dropping databases|
|Support for saving schema dump files|
|Timestamp-versioned migration files|
|Custom schema migrations table|
|Ability to wait for database to become ready|
|Database connection string loaded from environment variables|
|Automatically load .env file|
|No separate configuration file|
✴️In theory these tools could be used with other languages, but a Go development environment is required because binary builds are not provided.
If you notice any inaccuracies in this table, please propose a change.
Dbmate is written in Go, pull requests are welcome.
Tests are run against a real database using docker-compose. To build a docker image and run the tests:
$ make docker-all
To start a development shell:
$ make docker-sh