dbmate

Migration Files

Stored in ./db/migrations by default. Files are timestamp-versioned. To modify a migration that has already been applied, you should rollback the migration first, as dbmate only tracks the version number, not the file content.

Schema File

Written to ./db/schema.sql by default. This is a complete dump of your database schema. It should be checked into source control to allow for easy diffing and quick database restoration without running every migration sequentially.

Schema Migrations Table

An internal table (default name: schema_migrations) used to record which migration versions have been applied to the database.

Dbmate uses the DATABASE_URL environment variable to locate your database. It automatically looks for a .env file in the current directory. If multiple environment variables are needed (e.g., for testing), you can specify a different one using the -e flag.

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

Supported Protocols:

• mysql
• postgres / postgresql
• sqlite / sqlite3
• clickhouse
• bigquery
• spanner-postgres

Note: username and password must be URL encoded if they contain special characters.

Migration files are plain SQL files stored in ./db/migrations by default. They use a specific syntax to separate the 'up' (applying) and 'down' (rolling back) logic. Both directives are required in every file.

Example file structure ([date]_create_users.sql):

-- migrate:up
create table users (
  id integer,
  name varchar(255),
);

-- migrate:down
drop table if exists users;

Use the following command to generate the dbmate NPM package. This is typically used in CI environments or during the build process to prepare the package for distribution.

npm run generate

To create a new migration file, use the new command. This generates a .sql file in the db/migrations/ directory with a timestamped version prefix.

Migration Structure: Each file contains -- migrate:up and -- migrate:down sections. You can include multiple migration blocks in a single file; the file succeeds or fails as a single unit.

Use the following commands to manage your database schema:

• dbmate up: Applies all pending migrations in numerical order. This command will also attempt to create the database if it doesn't exist.
• dbmate migrate: Applies pending migrations without attempting to create the database.
• dbmate rollback: Reverts the most recent migration (requires a -- migrate:down section in the file).

Note: dbmate up automatically updates the ./db/schema.sql file.

$ dbmate up
$ dbmate rollback

When using Docker, the database server might not be ready immediately. Use the wait command or the --wait flag to pause execution until the database is reachable.

• dbmate wait: Blocks until the database is available (attempts every second for up to 60 seconds).
• --wait: A flag that can be used with other commands (e.g., dbmate --wait up).
• --wait-timeout: Customizes the timeout (default is 60s).

You can install dbmate using several package managers depending on your environment:

NPM

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

macOS (Homebrew)

brew install dbmate
dbmate --help

Linux (Direct Binary)

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 (Scoop)

scoop install dbmate
dbmate --help

Docker Use the image ghcr.io/amacneil/dbmate. When running migrations, use a bind mount to make your local directory available inside the container.

# Run help
docker run --rm -it --network=host ghcr.io/amacneil/dbmate --help

# Create a new migration using a bind mount
docker run --rm -it --network=host -v "$(pwd)/db:/db" ghcr.io/amacneil/dbmate new create_users_table

Dbmate automatically maintains a ./db/schema.sql file whenever you run up, migrate, or rollback. It is recommended to check this file into version control.

To manually trigger a schema dump without running migrations, use the dump command. This command requires the underlying database client (e.g., pg_dump, mysqldump, or sqlite3) to be installed in your PATH.

You can pass additional arguments directly to the underlying tool using --.

For local development, if you do not have the dbmate binaries available on your system, you can generate the package while skipping the binary copy step using the --skip-bin flag.

npm run generate -- --skip-bin

Dbmate tracks applied migrations in a table named schema_migrations. You can customize this table name using either the CLI flag or an environment variable.

• CLI Flag: --migrations-table <name>
• Environment Variable: DBMATE_MIGRATIONS_TABLE