Migration habits – changing schema safely

Migration habits: changing schema safely as your application grows. Migrations are versioned, repeatable steps that move every environment from one schema shape to the next without manual guesswork.

Why not edit production by hand

Running ALTER TABLE once on live, then forgetting to run it on staging, is how environments diverge. A migration file checked into git (or your team’s equivalent) records exactly what changed and runs in order on deploy.

You do not need a specific framework to think in migrations – plain SQL files numbered by timestamp work. Laravel, Rails, Flyway, and others formalise the pattern.

One change per migration (usually)

Small, focused migrations are easier to review and roll back mentally:

-- 20260728120000_add_orders_status_index.sql
CREATE INDEX idx_orders_status ON orders (status);

Combining unrelated changes makes failures harder to diagnose. Exception: creating a new feature’s tables together in one migration when they deploy as a unit.

Additive changes first

Safer deploy pattern for zero-downtime-ish releases:

• Add new column (nullable or with default)
• Deploy app code that writes to both old and new (dual write) or reads new with fallback
• Backfill data
• Deploy app that uses only new column
• Drop old column in a later migration

-- Step 1: add
ALTER TABLE customers ADD COLUMN phone VARCHAR(20) NULL AFTER email;

-- Step 3: backfill (batch in app or script)
UPDATE customers SET phone = legacy_phone WHERE phone IS NULL AND legacy_phone IS NOT NULL;

-- Step 5: drop (separate migration, weeks later)
ALTER TABLE customers DROP COLUMN legacy_phone;

Destructive changes need care

Renaming columns, changing types, dropping tables – assume you need a backup and a rollback plan. On large InnoDB tables, ALTER may lock or rebuild for a long time. Test on a copy with realistic row counts.

-- Renaming in MySQL 8+
ALTER TABLE orders RENAME COLUMN placed_at TO ordered_at;

-- Type change - verify data fits
ALTER TABLE products MODIFY price DECIMAL(12, 2) NOT NULL;

Tracking what ran

Frameworks keep a schema_migrations table. Roll your own if needed:

CREATE TABLE schema_migrations (
  version VARCHAR(50) PRIMARY KEY,
  applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- After successful run
INSERT INTO schema_migrations (version) VALUES ('20260728120000_add_orders_status_index');

Seed vs migration

Migrations change structure. Seeds insert reference or test data. Keep them separate – do not put required lookup rows in a migration unless they are truly schema-level and permanent (and even then, document heavily).

Review checklist

• Does it run on empty database and on production-sized copy?
• Foreign keys and indexes included in same deploy?
• Reversible or documented why not?
• Long-running ALTER scheduled off-peak with monitoring?
• App deploy order agreed (migration before code, or feature flags)?

The next tutorial covers backup and restore drills – proving you can recover before disaster strikes.