Migrations

What a Migration Is

A migration is a small, versioned script that changes your database schema: create a table, add a column, add an index. Each migration runs once, in order, on every environment (your laptop, staging, production).

Think of Git for your database structure. Instead of someone typing ALTER TABLE by hand on the production server and hoping everyone else remembers to do the same, every change is a file in the repo, reviewed and applied the same way everywhere.

1migrations/
2├── V1__create_orders.sql
3├── V2__add_index_orders_user_id.sql
4├── V3__add_is_veg_to_menu_items.sql
5└── V4__create_coupons.sql

How Migration Tools Work

The tool keeps a history table in the database listing which migrations already ran. On every deploy, it runs only the new ones:

1History table (flyway_schema_history / knex_migrations)
2  V1 ✓  V2 ✓  V3 ✓
3 
4Deploy with V4 added
5  → tool sees V4 is new → runs V4 → records V4 ✓
6 
7Fresh laptop database
8  → runs V1, V2, V3, V4 in order → same schema as production

Common tools:

Writing a Migration

Our menu needs an is_veg flag on every dish:

-- file: src/main/resources/db/migration/V3__add_is_veg_to_menu_items.sql
-- Flyway runs it automatically on Spring Boot startup

ALTER TABLE menu_items
ADD COLUMN is_veg BOOLEAN NOT NULL DEFAULT false;

An up step applies the change; a down step undoes it. Flyway's free version runs forward only, so teams often fix mistakes with a new migration instead of rolling back.

Rules:

• Never edit a migration that already ran in production. Write a new one.
• Commit migrations together with the code that needs them.
• Run them automatically in the deploy pipeline, not by hand.

The Hard Part: Changing a Live Database

During a deploy, old and new versions of the app run at the same time for a few minutes. A migration must work with both. Some changes also lock a big table and freeze writes.

Say we want to rename orders.amount to orders.total_amount. A direct rename breaks the old app instantly:

1Migration: RENAME amount → total_amount
2Old app still running: SELECT amount FROM orders → error: column does not exist
3→ orders fail until every server is on the new version

The safe way is expand and contract: add the new thing first, remove the old thing last, across several deploys.

1Deploy 1 (expand)    Add column total_amount
2                     App writes to BOTH amount and total_amount
3 
4Backfill             Copy old values: total_amount = amount (in batches)
5 
6Deploy 2             App reads from total_amount only
7 
8Deploy 3 (contract)  Drop column amount

At every step, both the running app versions work.

Risky Operations on Big Tables

-- don't wait forever for a lock; fail fast and retry later
SET lock_timeout = '5s';

-- build an index without blocking new orders
CREATE INDEX CONCURRENTLY idx_orders_restaurant_id ON orders (restaurant_id);

-- backfill in batches instead of one huge UPDATE
UPDATE orders SET total_amount = amount
WHERE id IN (
  SELECT id FROM orders WHERE total_amount IS NULL LIMIT 10000
);
-- repeat until no rows are updated

Interview Angle

Common mistakes:

• Renaming or dropping a column in the same deploy as the code change.
• Running CREATE INDEX without CONCURRENTLY on a busy production table.
• Thinking MongoDB needs no migrations. Old documents still need updating when the shape changes.

Summary

1Migration        → versioned script for a schema change, run once, in order
2Tool             → history table tracks what ran (Flyway, Liquibase, Knex, migrate-mongo)
3Rules            → never edit applied migrations, commit with code, run in the pipeline
4Live databases   → old + new app run together during deploy
5Expand/contract  → add new → write both → backfill → switch → drop old
6Big tables       → CREATE INDEX CONCURRENTLY, batch backfills, lock_timeout
7MongoDB          → no ALTER, but data migrations still needed

Changing data safely includes removing it safely. Next, soft delete vs hard delete compares marking rows as deleted with actually removing them.