# dbmate — Lightweight Database Migration Tool

> dbmate is a simple, framework-agnostic database migration tool written in Go. It works with PostgreSQL, MySQL, SQLite, and ClickHouse using plain SQL migration files and a single binary with no dependencies.

## Install

Save as a script file and run:

# dbmate — Lightweight Database Migration Tool

## Quick Use
```bash
# Install via Homebrew
brew install dbmate

# Or download the binary
curl -fsSL -o /usr/local/bin/dbmate https://github.com/amacneil/dbmate/releases/latest/download/dbmate-linux-amd64
chmod +x /usr/local/bin/dbmate

# Set database URL
export DATABASE_URL="postgres://localhost:5432/mydb?sslmode=disable"

# Create a new migration
dbmate new create_users_table

# Run pending migrations
dbmate up

# Roll back the last migration
dbmate down
```

## 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

---
Source: https://tokrepo.com/en/workflows/38618c4f-3abe-11f1-9bc6-00163e2b0d79
Author: Script Depot