Dbmate

Dbmate is a database migration tool that will keep your database schema in sync across multiple developers and your production servers.

It is a standalone command line tool that can be used with Go, Node.js, Python, Ruby, PHP, Rust, C++, 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.

For a comparison between dbmate and other popular database schema migration tools, please see Alternatives.

Table of Contents

• Features
• Installation
• Commands
• Command Line Options
• Usage
• Connecting to the Database
  • PostgreSQL
  • MySQL
  • SQLite
  • ClickHouse
  • BigQuery
  • Spanner
• Creating Migrations
• Running Migrations
• Rolling Back Migrations
• Migration Options
• Waiting For The Database
• Exporting Schema File
• Library
• Use dbmate as a library
• Embedding migrations
• Concepts
• Migration files
• Schema file
• Schema migrations table
• Alternatives
• Contributing

Features

• 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.sql file to easily diff schema changes in git
• Database connection URL is defined using an environment variable (DATABASE_URL by default), or specified on the command line
• Built-in support for reading environment variables from your .env file
• Easy to distribute, single self-contained binary
• Doesn't try to upsell you on a SaaS service

Installation

NPM

Install using NPM:

npm install --save-dev dbmate
npx dbmate --help

macOS

Install using Homebrew:

brew install dbmate
dbmate --help

Linux

Install 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
/usr/local/bin/dbmate --help

Windows

Install using Scoop

scoop install dbmate
dbmate --help

Docker

Docker images are published to GitHub Container Registry (ghcr.io/amacneil/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 --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 new create_users_table

Commands

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 dump -- [...]  # optionally pass additional arguments directly to mysqldump or pg_dump
dbmate load           # load schema.sql file to the database
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: DATABASE_URL)
• --driver "driver_name" - specify the driver to use (if empty, the driver is derived from database URL scheme). (env: DBMATE_DRIVER)
• --env, -e "DATABASE_URL" - specify an environment variable to read the database connection URL from.
• --env-file ".env" - specify an alternate environment variables file(s) to load.
• --migrations-dir, -d "./db/migrations" - where to keep the migration files. (env: DBMATE_MIGRATIONS_DIR)
• --migrations-table "schema_migrations" - database table to record migrations in. (env: DBMATE_MIGRATIONS_TABLE)
• --schema-file, -s "./db/schema.sql" - a path to keep the schema.sql file. (env: DBMATE_SCHEMA_FILE)
• --no-dump-schema - don't auto-update the schema.sql file on migrate/rollback (env: DBMATE_NO_DUMP_SCHEMA)
• --strict - fail if migrations would be applied out of order (env: DBMATE_STRICT)
• --wait - wait for the db to become available before executing the subsequent command (env: DBMATE_WAIT)
• --wait-timeout 60s - timeout for --wait flag (env: DBMATE_WAIT_TIMEOUT)

Usage

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://postgres@127.0.0.1:5432/myapp_development?sslmode=disable"

DATABASE_URL should be specified in the following format:

protocol://username:password@host:port/database_name?options

• protocol must be one of mysql, postgres, postgresql, sqlite, sqlite3, clickhouse
• username and password must be URL encoded (you will get an error if you use special charactors)
• host can be either a hostname or IP address
• options are 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://postgres@127.0.0.1:5432/myapp_dev?sslmode=disable"
TEST_DATABASE_URL="postgres://postgres@127.0.0.1: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
Applied: 20151127184807_create_users_table.sql in 123µs

Alternatively, you can specify the url directly on the command line:

$ dbmate -u "postgres://postgres@127.0.0.1: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.

PostgreSQL

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).

DATABASE_URL="postgres://username:password@127.0.0.1:5432/database_name?sslmode=disable"

A socket or host parameter can be specified to connect through a unix socket (note: specify the directory only):

DATABASE_URL="postgres://username:password@/database_name?socket=/var/run/postgresql"

For passwordless authentication such as PostgreSQL peer auth, the password can be omitted (note the @ is still required):

DATABASE_URL="postgres://username@/database_name?socket=/var/run/postgresql"

If the username is also omitted, it defaults to the PGUSER environment variable if set, otherwise the OS username (via lib/pq, matching libpq behavior):

DATABASE_URL="postgres:///database_name?socket=/var/run/postgresql"

A search_path parameter can be used to specify the current schema while applying migrations, as well as for dbmate's schema_migrations table. 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.

DATABASE_URL="postgres://username:password@127.0.0.1:5432/database_name?search_path=myschema"

DATABASE_URL="postgres://username:password@127.0.0.1:5432/database_name?search_path=myschema,public"

MySQL

DATABASE_URL="mysql://username:password@127.0.0.1:3306/database_name"

A socket parameter can be specified to connect through a unix socket:

DATABASE_URL="mysql://username:password@/database_name?socket=/var/run/mysqld/mysqld.sock"

SQLite

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:

DATABASE_URL="sqlite:db/database.sqlite3"

To specify an absolute path, add a forward slash to the path. The following will create a database at /tmp/database.sqlite3:

DATABASE_URL="sqlite:/tmp/database.sqlite3"

Note that for some common settings like journal_mode to improve performance, transactions need to be disabled for that migration file, e.g.

-- migrate:up transaction:false
PRAGMA journal_mode = WAL;

Otherwise the migration will fail with "Error: cannot change into wal mode from within a transaction".

ClickHouse

Dbmate supports connecting to ClickHouse using native TCP (default) or HTTP/HTTPS.

Native (TCP)

By default, the clickhouse:// scheme uses the native protocol on port 9000.

DATABASE_URL="clickhouse://username:password@127.0.0.1:9000/database_name"

HTTP / HTTPS

You can use clickhouse+http:// (deafult port 8123) or clickhouse+https:// (default port 8443).

# HTTP (Defaults to port 8123)
DATABASE_URL="clickhouse+http://username:password@127.0.0.1:8123/database_name"

# HTTPS (Defaults to port 8443)
DATABASE_URL="clickhouse+https://username:password@127.0.0.1:8443/database_name"

Using the --driver flag

You can use the ClickHouse driver with a standard http/https/tcp URL by providing the --driver flag

# Connect via HTTP using generic URL syntax
dbmate --driver clickhouse --url "http://username:password@127.0.0.1:8123/database_name" status

dbmate --driver clickhouse --url "https://username:password@127.0.0.1:8443/database_name" status

# Better to rely on the standard clickhouse:// scheme, but this is supported
dbmate --driver clickhouse --url "tcp://username:password@127.0.0.1:9000/database_name" status

To work with ClickHouse cluster, there are 4 connection query parameters that can be supplied:

• on_cluster - Indicataion to use cluster statements and replicated migration table. (default: false) If this parameter is not supplied, other cluster related query parameters are ignored.

DATABASE_URL="clickhouse://username:password@127.0.0.1:9000/database_name?on_cluster"

DATABASE_URL="clickhouse://username:password@127.0.0.1:9000/database_name?on_cluster=true"

• cluster_macro (Optional) - Macro value to be used for ON CLUSTER statements and for the replciated migration table engine zookeeper path. (default: {cluster})

DATABASE_URL="clickhouse://username:password@127.0.0.1:9000/database_name?on_cluster&cluster_macro={my_cluster}"

• replica_macro (Optional) - Macro value to be used for the replica name in the replciated migration table engine. (default: {replica})

DATABASE_URL="clickhouse://username:password@127.0.0.1:9000/database_name?on_cluster&replica_macro={my_replica}"

• zoo_path (Optional) - The path to the table migration in ClickHouse/Zoo Keeper. (default: /clickhouse/tables/<cluster_macro>/{table})

```sh DATABASE_URL="clickhouse://username:password@127.0.0.1:9000/database_name?on_clust