migy

migy is a simple, standalone tool for managing SQL migrations on MySQL. It focuses on a plain SQL workflow and provides safety checks to ensure your migrations are reversible and consistent. It's ideal for teams and individuals who want fine-grained control over their database schema using pure SQL, without the overhead of an ORM or complex DSLs.

Features

• Pure SQL Migrations: No proprietary DSLs or ORM layers. Write your migrations in plain, portable SQL.
• Reversibility Checks: migy check runs your up and down migrations in a temporary database to guarantee that they are perfectly reversible.
• Database Snapshots: Generate a single SQL file of the entire database state, including both schema and any data inserted by migrations, at a specific version. This is useful for quickly setting up new databases or reviewing the cumulative effect of migrations.
• Standalone & Flexible: Most operations (check, snapshot, list) work without a live database connection. Apply migrations directly with migy apply or generate a file list for use in CI/CD scripts.
• Zero Configuration: Works out of the box with sensible defaults. All configuration is done via command-line flags.

Installation

go install github.com/makiuchi-d/migy@latest

Getting Started: A Tutorial

Here's a walkthrough of a typical migration workflow with migy.

1. Initialize the project

First, create a directory for your migrations and run migy init.

mkdir migrations
cd migrations
migy init

This creates 000000_init.all.sql, which contains the setup for the _migrations table used to track applied migrations.

2. Create a new migration

Next, let's create a migration to add a users table.

migy create add_users_table

This generates two files:

• 000010_add_users_table.up.sql
• 000010_add_users_table.down.sql

3. Write the migration SQL

Edit the generated files.

000010_add_users_table.up.sql:

-- Generated by migy
INSERT INTO _migrations (id, title, applied) VALUES (10, 'add_users_table', now());
-- Write your forward migration SQL statements below.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

000010_add_users_table.down.sql:

-- Generated by migy
CALL _migration_exists(10);
DELETE FROM _migrations WHERE id = 10;
-- Write your rollback SQL statements below.

DROP TABLE users;

4. Check the migration

Before applying, check that the migration is reversible.

migy check

migy will run the up and down files in a temporary database to ensure the schema returns to its original state.

5. Apply to the database

Once checked, apply the migration to your live database.

migy apply --host=localhost --user=user --password=pass dbname

migy will connect to the database, see that migration 10 is pending, and execute the up script.

6. Check the status

You can see the state of all migrations at any time with migy status.

migy status --dsn "user:pass@tcp(localhost:3306)/dbname"

# Example Output:
# 000000　　　	✅2025-09-20 10:00:00	"init"
# 000010⏫⏬　	✅2025-09-20 10:05:00	"add_users_table"

Configuration

All configuration is handled via command-line flags. There are no configuration files or environment variables. The most common flags are:

• -d, --dir <path>: Specifies the directory containing migration files (defaults to the current directory).
• Database connection flags (--host, --user, --password, --port, --dsn) are available for commands that interact with a live database (apply, status, list).

Command Reference

init

Generates the initial migration SQL file (000000_init.all.sql). This file contains the necessary SQL to create the _migrations table, which is used by migy to track the state of migrations.

Usage

migy init

Flags

• --force: Overwrite the file if it already exists.

create

Creates a new set of migration files: <number>_<title>.up.sql and <number>_<title>.down.sql.

Usage

migy create [flags] <TITLE>

Arguments

• <TITLE>: A short, descriptive title for the migration (e.g., add_users_table).

Flags

• -n, --number <int>: Specify a migration number. By default, migy will automatically determine the next sequential number.

Example

$ migy create add_users_table
# Creates:
# - 000010_add_users_table.up.sql
# - 000010_add_users_table.down.sql

check

Verifies that a migration is reversible. It does this by:

1. Creating a temporary, in-memory database.
2. Applying all migrations up to the one being checked.
3. Taking a snapshot of the schema and data.
4. Applying the .up.sql migration.
5. Applying the corresponding .down.sql migration.
6. Comparing the final schema and data to the snapshot to ensure there are no differences.

Note: If your down migration cannot perfectly restore the original data (e.g., when re-adding a dropped column), you can use a -- migy:ignore table.column comment to exclude a specific column from the data comparison. See the "Migration File Formats" section for details.

Usage

migy check [flags]

Flags

• -n, --number <int>: The migration number to check. If omitted, the latest migration is checked.
• --from <int>: Check all migrations sequentially starting from this number up to the one specified by --number (or the latest).

status

Displays the status of all migration files, showing whether they have been applied to the database.

Usage

migy status [flags] [DUMP_FILE | --host HOST DB_NAME | --dsn DSN]

Details status compares the migration files in your directory with the records in the _migrations table of a live database or a SQL dump file.

Example Output

 000000　　　	✅2023-10-27 10:00:00	"init"
 000010⏫⏬　	✅2023-10-27 10:05:00	"add_users_table"
 000020⏫⏬　	　0000-00-00 --:--:--	"add_posts_table"

• ⏫⏬: An up/down migration pair exists.
• ⏺: A snapshot (.all.sql) file exists.
• ✅: The migration has been applied.

apply

Applies migrations to a live database to bring it to the target state.

Usage

migy apply [flags] [--host HOST DB_NAME | --dsn DSN]

Details apply connects to a database, determines the current migration version from the _migrations table, and applies all necessary .up.sql or .down.sql files to reach the target version. It will prompt for confirmation before executing any changes.

Flags

• -n, --number <int>: The migration number to apply. Defaults to the latest version. Use 0 to roll back all migrations.
• -y, --yes: Skips the confirmation prompt.
• Database flags (--host, --user, --password, --port, --dsn) for connection.

Example

# Apply all pending migrations
migy apply --host=localhost --user=user --password=pass dbname

list

Lists the migration files that need to be applied to reach a target state.

Usage

migy list [flags] [DUMP_FILE | --host HOST DB_NAME | --dsn DSN]

Details This command is useful for seeing which files apply would execute, or for use in custom scripts. The output is a list of file paths.

Since the output is a simple list of SQL files, you can use it to run migrations in an environment where migy itself is not installed. You can pipe the file list to a standard mysql client to execute the migrations in the correct order.

Flags

• -n, --number <int>: The target migration number. Defaults to the latest.
• Database flags for connection.

Example

# See which files would be applied based on a schema dump file
mysqldump dbname _migrations > schema_dump.sql
migy list schema_dump.sql

# Pipe the file list to a mysql client to apply the migrations manually
migy list --dsn "user:pass@tcp(host:3306)/dbname" | xargs cat | mysql --host=localhost --user=user --password=pass dbname

snapshot

Generates a single .all.sql file that represents the entire database schema at a specific migration version.

Usage

migy snapshot [flags]

Details snapshot works by applying all migrations up to a target version in a temporary database and then dumping the resulting schema to a file named <number>_<title>.all.sql. This is useful for easily setting up new databases or for checking a migration's cumulative effect.

Flags

• -n, --number <int>: The migration number to create the snapshot for. Defaults to the latest.
• --force: Overwrite the snapshot file if it already exists.

Migration File Formats

migy uses a simple file-based system.

• 000000_init.all.sql: The initial database schema. It defines the _migrations table for tracking migration history and a _migration_exists stored procedure used in rollback scripts. Created by migy init.
• <num>_<title>.up.sql: Contains the SQL statements for a forward migration. Must also include the INSERT INTO _migrations statement.
• <num>_<title>.down.sql: Contains the SQL statements to roll back the corresponding up migration. It must begin with a call to the _migration_exists(<num>) stored procedure and a DELETE FROM _migrations statement. The stored procedure call ensures that the script will only run if the corresponding migration has been applied.
• <num>_<title>.all.sql: A complete snapshot of the database schema at a specific migration version. Generated by migy snapshot.

Ignoring Record Differences in .down.sql

migy check verifies reversibility by comparing not only the database schema but also the data within it. It ensures that the data is identical before the up migration and after the down migration.

However, a common scenario breaks this data check:

1. An up migration uses DROP COLUMN to remove a column.
2. The corresponding down migration uses ADD COLUMN to restore the schema.

In this case, the schema is correctly reverted, but the original data in that column is lost. This would cause migy check to fail because it detects a data difference in every row of the table.

To handle this, you can instruct the checker to ignore data differences in a specific column by adding a special comment to your .down.sql file:

-- migy:ignore table.column

This comment tells migy check to exclude the specified column of that table from its data comparison, allowing the check to pass while still verifying the rest of the schema and data.

Example:

Imagine an up migration drops the email column from the users table. The corresponding down.sql would look like this to pass the check:

-- Generated by migy
CALL _migration_exists(40);
DELETE FROM _migrations WHERE id = 40;
-- Write your rollback SQL statements below.

ALTER TABLE users ADD COLUMN email VARCHAR(255);
-- migy:ignore users.email