Repository files navigation

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

• 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

• 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 aschema.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

NPM

Install usingNPM:

npm install --save-dev dbmatenpx dbmate --help

macOS

Install usingHomebrew:

brew install dbmatedbmate --help

Linux

Install the binary directly:

sudo curl -fsSL -o /usr/local/bin/dbmate https://github.com/amacneil/dbmate/releases/latest/download/dbmate-linux-amd64sudo chmod +x /usr/local/bin/dbmate/usr/local/bin/dbmate --help

Windows

Install usingScoop

scoop install dbmatedbmate--help

Docker

Docker images are published to GitHub Container Registry (ghcr.io/amacneil/dbmate).

Remember to set--network=host or seethis 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'sbind 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

dbmate --help# print usage helpdbmate new# generate a new migration filedbmate up# create the database (if it does not already exist) and run any pending migrationsdbmate create# create the databasedbmate drop# drop the databasedbmate migrate# run any pending migrationsdbmate rollback# roll back the most recent migrationdbmate down# alias for rollbackdbmate status# show the status of all migrations (supports --exit-code and --quiet)dbmate dump# write the database schema.sql filedbmate load# load schema.sql file to the databasedbmatewait# wait for the database server to become available

The following options are available with all commands. You must use command line arguments in the orderdbmate [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)
• --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)

Dbmate locates your database using theDATABASE_URL environment variable by default. If you are writing atwelve-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 .envDATABASE_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 ofmysql,postgres,postgresql,sqlite,sqlite3,clickhouse
• username andpassword 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 theTEST_DATABASE_URL environment variable:

$ cat .envDATABASE_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 dropDropping: myapp_test$ dbmate -e TEST_DATABASE_URL --no-dump-schema upCreating: myapp_testApplying: 20151127184807_create_users_table.sqlApplied: 20151127184807_create_users_table.sqlin 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 usingdbmate -e TEST_DATABASE_URL overdbmate -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 thesslmode=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"

Asocket orhost 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"

Asearch_path parameter can be used to specify thecurrent schema while applying migrations, as well as for dbmate'sschema_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 theschema_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"

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

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

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

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 commonsettings likejournal_mode to improve performance, transactions need to be disabled for that migration file, e.g.

-- migrate:up transaction:falsePRAGMA journal_mode= WAL;

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

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

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

DATABASE_URL="clickhouse://username:password@127.0.0.1:9000/database_name?on_cluster&zoo_path=/zk/path/tables"

See other supported connection options.

Follow the following format forDATABASE_URL when connecting to actual BigQuery in GCP:

bigquery://projectid/location/dataset

projectid (mandatory) - Project ID

dataset (mandatory) - Dataset name within the Project

location (optional) - Where Dataset is created

NOTE: Followthis doc on how to setGOOGLE_APPLICATION_CREDENTIALS environment variable for proper Authentication

Follow the following format if trying to connect to a custom endpoint e.g.BigQuery Emulator

bigquery://host:port/projectid/location/dataset?disable_auth=true

disable_auth (optional) - Passtrue to skip Authentication, use only for testing and connecting to emulator.

Spanner support is currently limited to databases using thePostgreSQL Dialect, which must be chosen during database creation. For future Spanner with GoogleSQL support, seethis discussion.

Spanner with the Postgres interface requires that thePGAdapter is running. Use the following format forDATABASE_URL, with the host and port set to where the PGAdapter is running:

DATABASE_URL="spanner-postgres://127.0.0.1:5432/database_name?sslmode=disable"

Note that specifying a username and password is not necessary, as authentication is handled by the PGAdapter (they will be ignored by the PGAdapter if specified).

Other options of thepostgres driver are supported.

Spanner also doesn't allow DDL to be executed inside explicit transactions. You must therefore specifytransaction:false on migrations that include DDL:

-- migrate:up transaction:falseCREATE TABLE ...-- migrate:down transaction:falseDROPTABLE ...

Schema dumps are not currently supported, aspg_dump uses functions that are not provided by Spanner.

To create a new migration, rundbmate new create_users_table. You can name the migration anything you like. This will create a filedb/migrations/20151127184807_create_users_table.sql in the current directory:

-- migrate:up-- migrate:down

To write a migration, simply add your SQL to themigrate:up section:

-- migrate:upcreatetableusers (  idinteger,  namevarchar(255),  emailvarchar(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.

Rundbmate up to run any pending migrations.

$ dbmate upCreating: myapp_developmentApplying: 20151127184807_create_users_table.sqlApplied: 20151127184807_create_users_table.sqlin 123µsWriting: ./db/schema.sql

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, rundbmate migrate.

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.

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 themigrate:down section:

-- migrate:upcreatetableusers (  idinteger,  namevarchar(255),  emailvarchar(255)not null);-- migrate:downdroptable users;

Rundbmate rollback to roll back the most recent migration:

$ dbmate rollbackRolling back: 20151127184807_create_users_table.sqlRolled back: 20151127184807_create_users_table.sqlin 123µsWriting: ./db/schema.sql

dbmate supports options passed to a migration block in the form ofkey:value pairs. List of supported options:

• transaction

transaction

transaction is useful if you do not want to run SQL inside a transaction:

-- migrate:up transaction:falseALTERTYPE colors ADD VALUE'orange' AFTER'red';

transaction will default totrue if your database supports it.

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. Thewait 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:

$ dbmatewait

If the database is unavailable,wait will block until the database becomes available:

$ dbmatewaitWaitingfor 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 upWaitingfor 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=5swaitWaitingfor database.....Error: unable to connect to database: dial tcp 127.0.0.1:5432: connect: connection refused

Please note that thewait 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).

When you run theup,migrate, orrollback 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 theschema.sql file without performing any other actions, rundbmate dump. Unlike other dbmate actions, this command relies on the respectivepg_dump,mysqldump, orsqlite3 commands being available in your PATH. If these tools are not available, dbmate will silently skip the schema dump step duringup,migrate, orrollback actions. You can diagnose the issue by runningdbmate dump and looking at the output:

$ dbmate dumpexec:"pg_dump": executable file not foundin$PATH

On Ubuntu or Debian systems, you can fix this by installingpostgresql-client,mysql-client, orsqlite3 respectively. Ensure that the package version you install is greater than or equal to the version running on your database server.

Note: Theschema.sql file will contain a complete schema for your database, even if some tables or columns were created outside of dbmate migrations.

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.

Here is a simple example. Remember to import the driver you need!

package mainimport ("net/url""github.com/amacneil/dbmate/v2/pkg/dbmate"_"github.com/amacneil/dbmate/v2/pkg/driver/sqlite")funcmain() {u,_:=url.Parse("sqlite:foo.sqlite3")db:=dbmate.New(u)err:=db.CreateAndMigrate()iferr!=nil {panic(err)}}

See thereference documentation for more options.

Migrations can be embedded into your application binary using Go'sembed functionality.

Usedb.FS to specify the filesystem used for reading migrations:

package mainimport ("embed""fmt""net/url""github.com/amacneil/dbmate/v2/pkg/dbmate"_"github.com/amacneil/dbmate/v2/pkg/driver/sqlite")//go:embed db/migrations/*.sqlvarfs embed.FSfuncmain() {u,_:=url.Parse("sqlite:foo.sqlite3")db:=dbmate.New(u)db.FS=fsfmt.Println("Migrations:")migrations,err:=db.FindMigrations()iferr!=nil {panic(err)}for_,m:=rangemigrations {fmt.Println(m.Version,m.FilePath)}fmt.Println("\nApplying...")err=db.CreateAndMigrate()iferr!=nil {panic(err)}}

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 runningdbmate new create_users.Here is an example:

-- migrate:upcreatetableusers (  idinteger,  namevarchar(255),);-- migrate:downdroptable if exists users;

Both up and down migrations are stored in the same file, for ease of editing. Both up and down directives are required, even if you choose not to implement the down migration.

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.

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.

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.

Dbmate stores a record of each applied migration in table namedschema_migrations. This table will be created for you automatically if it does not already exist.

The table is very simple:

CREATETABLEIF NOT EXISTS schema_migrations (  versionVARCHAR(255)PRIMARY KEY)

You can customize the name of this table using the--migrations-table flag orDBMATE_MIGRATIONS_TABLE environment variable.

Why another database schema migration tool? Dbmate was inspired by many other tools, primarilyActive 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.

If you notice any inaccuracies in this table, pleasepropose 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