Dbmate: Database Migration Tool

2.32.0 · active · verified Sun Apr 19

Dbmate is a standalone, framework-agnostic command-line tool designed for managing database schema migrations. It supports a variety of databases including MySQL, PostgreSQL, SQLite, ClickHouse, BigQuery, and Spanner. The tool uses plain SQL for writing migrations, employs timestamp-versioning to prevent conflicts in collaborative environments, and runs migrations atomically within transactions. Beyond basic migration, it can also create and drop databases (useful for development/testing workflows) and export a `schema.sql` file to easily track schema changes in version control. Currently at version 2.32.0, Dbmate maintains an active and consistent release cadence, frequently releasing patch updates for bug fixes and dependency bumps, with minor versions arriving every few weeks to months. Its primary differentiator is its independence from any specific programming language or framework, making it an ideal choice for polyglot microservice architectures where a consistent database migration strategy across different technology stacks is desired.

Common errors

```
Error: Command failed: dbmate [command]
```
cause A generic error indicating that the underlying `dbmate` binary failed to execute its command, often due to an invalid database connection, malformed SQL, or an inaccessible database.

fix Carefully review the complete error message that follows. Common culprits include incorrect `DATABASE_URL`, a non-existent database (run `npx dbmate create`), or syntax errors within your SQL migration files.
```
Error: DATABASE_URL not set. Set with --url or DATABASE_URL env var.
```
cause The essential `DATABASE_URL` environment variable or command-line `--url` argument, which specifies the database connection string, was not provided before execution.

fix Set the `DATABASE_URL` environment variable in your shell (e.g., `export DATABASE_URL="postgres://user:pass@host:port/db"`) or provide it directly to the command: `npx dbmate --url "your_connection_string" up`.
```
Error: database doesn't exist (code 100)
```
cause You are attempting to connect to a database specified in `DATABASE_URL` that has not yet been created on the database server.

fix First, execute `npx dbmate create` (ensure `DATABASE_URL` points to the correct server) to create the database, then proceed with applying migrations using `npx dbmate up`.
```
Syntax Error: near "TABLE" (or similar SQL syntax error)
```
cause There is a syntax error in the SQL statements within one of your migration files (e.g., a typo, incorrect keyword, or database-specific syntax mismatch).

fix Inspect the migration file indicated in the error message for any SQL syntax mistakes. Pay close attention to the specific SQL dialect of your target database (e.g., MySQL, PostgreSQL, SQLite).

Warnings

gotcha When running Dbmate inside a Docker container, ensure proper network configuration (`--network=host` or specific bridge/user-defined networks) to allow the container to connect to your database server. Incorrect networking is a very common cause of connection failures.
Fix: Use `docker run --rm -it --network=host ghcr.io/amacneil/dbmate [command]` or configure explicit Docker networking and ensure the database host is reachable from within the container.
breaking The TypeScript/JavaScript wrapper for Dbmate (used for programmatic API access) strictly switched to ESM imports. Projects attempting to use `require()` for library functions will fail.
Fix: Migrate your import statements from CommonJS `const { Dbmate } = require('dbmate')` to ESM `import { Dbmate } from 'dbmate'`. Ensure your Node.js project is configured to run as an ESM module (e.g., via `"type": "module"` in `package.json`).
gotcha Dbmate critically depends on the `DATABASE_URL` environment variable (or the `--url` command-line flag) to establish a database connection. Overlooking or incorrectly setting this variable is a frequent source of errors.
Fix: Before running any Dbmate command, ensure `DATABASE_URL` is correctly set in your shell environment (e.g., `export DATABASE_URL="your_db_connection_string"`) or explicitly pass it with `npx dbmate --url "your_db_connection_string" [command]`.
gotcha Earlier versions experienced issues connecting to Supavisor (a PostgreSQL connection pooler) due to a breaking change in the underlying `lib/pq` Go driver v1.11.1.
Fix: Upgrade Dbmate to version 2.30.0 or higher, which includes a fix addressing the compatibility issues with `lib/pq` and Supavisor.
gotcha Newer versions of PostgreSQL (e.g., 17.6) introduced `\restrict` and `\unrestrict` meta-commands in `pg_dump` output, which could lead to issues when using `dbmate dump-schema` with those PostgreSQL versions.
Fix: Upgrade to Dbmate v2.29.0 or later, which includes specific handling for these new PostgreSQL `pg_dump` commands to ensure correct schema dumping.

Install

npm install dbmate npm
yarn add dbmate yarn
pnpm add dbmate pnpm

Imports

Dbmate
wrong:
```
const Dbmate = require('dbmate')
```
correct:
```
import { Dbmate } from 'dbmate'
```
The `dbmate` NPM package provides a TypeScript wrapper for interacting with the underlying Go binary programmatically. Use named imports for the `Dbmate` class or helper functions. CommonJS `require` is generally not supported for the library API since v2.29.5 due to a focus on ESM compatibility.
resolveBinary
```
import { resolveBinary } from 'dbmate'
```
A utility function exported by the TypeScript wrapper to programmatically locate the `dbmate` executable binary. This is useful for advanced programmatic control or debugging issues related to the binary path.
CLI execution
wrong:
```
dbmate [command]
```
correct:
```
npx dbmate [command]
```
When `dbmate` is installed as a local `devDependency` in a Node.js project, it is best practice to execute its commands using `npx` to ensure the locally installed version from `node_modules/.bin` is used. Alternatively, you can define `dbmate` commands directly in your `package.json` scripts, where `npm`/`yarn` will handle the path resolution automatically.

Quickstart

This quickstart demonstrates the core workflow of Dbmate, including installation as a development dependency, initializing the migration setup, creating new SQL migration files, applying pending migrations, rolling back the last migration, and dumping the schema. It highlights the crucial role of the `DATABASE_URL` environment variable.

npm install --save-dev dbmate

# Set your database connection string as an environment variable.
# Replace with your actual database details.
# For PostgreSQL:
export DATABASE_URL="postgres://user:password@localhost:5432/mydatabase?sslmode=disable"
# For SQLite:
# export DATABASE_URL="sqlite://./data/application.db"

# Initialize dbmate: Creates a 'migrations' directory and an initial schema file.
# This should typically be run once per project.
npx dbmate init

# Create a new, timestamped migration file for a new table.
# This generates an empty SQL file in the 'migrations' directory.
npx dbmate new create_products_table

# Edit the generated migration file (e.g., migrations/20260419123456_create_products_table.sql).
# Add your 'UP' and 'DOWN' SQL statements:
# -- MIGRATION UP --
# CREATE TABLE products (
#   id SERIAL PRIMARY KEY,
#   name VARCHAR(255) NOT NULL,
#   price DECIMAL(10, 2) NOT NULL
# );
# -- MIGRATION DOWN --
# DROP TABLE products;

# Apply all pending migrations to your database.
npx dbmate up

# To roll back the very last applied migration:
npx dbmate down

# To dump the current database schema to 'schema.sql' for version control:
npx dbmate dump-schema

view raw JSON →