Why the name matters
On a small team, a migration name is read twice: when you create it and when you debug an issue in production. On a big team, it's read a hundred times more: in code review, deploys, rollbacks. A clear name saves minutes every time.
The standard pattern
YYYYMMDDHHMMSS_verb_object.sql (or .rb, .php, etc).
The seconds-precision timestamp guarantees ordering even when several people create
migrations the same day. The verb describes the operation. The object is the affected
table or field.
Common verbs
- create: create a new table.
- drop: remove a table.
- add: add column or index.
- remove: remove column or index.
- rename: rename table or column.
- change: change type or constraint.
- backfill: populate data into a new column.
Per-framework conventions
Rails uses YYYYMMDDHHMMSS_create_users.rb with class
CreateUsers < ActiveRecord::Migration[7.0]. Laravel:
2024_01_15_103000_create_users_table.php. Knex:
20240115103000_create_users.js. Flyway:
V1.2.3__create_users.sql (versioned, not timestamped).
Reversible migrations
Every migration should have an up and a down. The
down reverses. This saves you in production when something goes wrong:
rollback is just running down. Irreversible migrations (drop column with
data) are a decision worth documenting.
Large migrations and zero downtime
Renaming a column on a table with millions of rows locks the database. The zero-downtime pattern: 1) add the new column, 2) write to both, 3) backfill, 4) switch reads, 5) stop writing the old one, 6) drop the old. Each step is a separate migration, and the code works against both during a sprint.
Data vs schema migrations
Keep schema migrations (DDL: CREATE, ALTER, DROP) separate from data migrations (DML:
UPDATE, INSERT). Names like backfill_users_locale or
fix_orders_currency make intent clear. DDL should be idempotent when
possible (CREATE TABLE IF NOT EXISTS).
Reviews and order
In the PR, show the reviewer the SQL that will run (EXPLAIN if complex), the
rollback plan and the estimated impact. For high-traffic tables, coordinate the deploy
for low-usage hours. Tools like gh-ost or pt-osc allow lock-free ALTERs in MySQL.