dbmate — Lightweight Database Migration Tool

Introduction

dbmate is designed to be the simplest possible database migration tool. It uses plain SQL files with up and down sections, connects via a DATABASE_URL environment variable, and runs as a standalone binary. There is no language runtime, no ORM coupling, and no configuration files to manage.

What dbmate Does

• Creates and runs SQL migrations with paired up and down sections in a single file
• Tracks applied migrations in a schema_migrations table with timestamp-based ordering
• Supports PostgreSQL, MySQL, MariaDB, SQLite, and ClickHouse databases
• Provides wait-for-db functionality to block until the database is ready during container startup
• Generates a schema dump file after each migration for version-controlled schema tracking

Architecture Overview

dbmate reads SQL migration files from a db/migrations directory. Each file contains an up section and an optional down section separated by a comment marker. On dbmate up, it connects to the database specified by DATABASE_URL, checks the schema_migrations table for applied versions, and runs any pending files in order. After applying migrations, it optionally dumps the current schema to a db/schema.sql file.

Self-Hosting & Configuration

• Single binary with no dependencies; available for Linux, macOS, Windows, and as a Docker image
• Database connection configured entirely through the DATABASE_URL environment variable
• Migration directory defaults to db/migrations but is configurable via --migrations-dir flag
• Schema dump file location configurable via --schema-file flag; set to empty to disable
• Supports .env files for loading environment variables in development

Key Features

• Minimal design with no configuration files, project files, or language dependencies
• Automatic schema dump after migrations for Git-tracked schema documentation
• Wait-for-db command useful in Docker Compose and Kubernetes init containers
• Migration files use plain SQL with no custom DSL or abstraction layer
• Strict ordering prevents out-of-order migration application by default

Comparison with Similar Tools

• golang-migrate — More database drivers and source options; slightly more complex configuration
• Flyway — Enterprise features with checksum validation; Java-based with heavier footprint
• Atlas — Declarative schema management with drift detection; more features but more complexity
• Alembic — Python and SQLAlchemy tied; dbmate is language-agnostic
• sql-migrate — Go library for SQL migrations; dbmate offers a simpler standalone CLI experience

FAQ

Q: What format do migration filenames use? A: Files are named with a timestamp prefix like 20240101120000_create_users.sql. dbmate generates this automatically with the new command.

Q: Can I use dbmate in a CI/CD pipeline? A: Yes. Use the Docker image or install the binary. Set DATABASE_URL and run dbmate up as a deployment step.

Q: Does dbmate support transactions? A: Yes, on databases that support transactional DDL (PostgreSQL, SQLite). MySQL DDL is not transactional by design.

Q: How do I handle multiple databases? A: Set different DATABASE_URL values and migration directories for each database and run dbmate separately for each.

Sources

• https://github.com/amacneil/dbmate
• https://github.com/amacneil/dbmate#readme