Mini project: safe migration path for a breaking change

This mini project plans a safe migration path for a breaking schema change. Scenario: your shop app stores product prices on order_items.unit_price at checkout time (correct). Marketing now wants a mandatory currency_code on every order and line item for multi-currency support. Existing rows have no currency. Downtime must be minimal. Rollback must be possible.

Current schema (simplified)

CREATE TABLE orders (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id INT UNSIGNED NOT NULL,
  status VARCHAR(32) NOT NULL,
  placed_at DATETIME NOT NULL
);

CREATE TABLE order_items (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);

Target schema

-- orders.currency_code CHAR(3) NOT NULL DEFAULT 'GBP'
-- order_items.currency_code CHAR(3) NOT NULL DEFAULT 'GBP'
-- Eventually drop DEFAULT after backfill and app deploy

Phase 1 – Expand (backward compatible)

Add nullable or defaulted columns. Old app version keeps working.

ALTER TABLE orders
ADD COLUMN currency_code CHAR(3) NOT NULL DEFAULT 'GBP';

ALTER TABLE order_items
ADD COLUMN currency_code CHAR(3) NOT NULL DEFAULT 'GBP';

Deploy migration. No app change required yet – new column populated with default on existing rows.

Phase 2 – Dual write in application

Deploy app version that sets currency_code on every new order from the customer’s locale or checkout session. Reads may still ignore the column. Log when currency differs from GBP for validation.

INSERT INTO orders (customer_id, status, placed_at, currency_code)
VALUES (42, 'pending', NOW(), 'EUR');

INSERT INTO order_items (order_id, product_id, quantity, unit_price, currency_code)
VALUES (LAST_INSERT_ID(), 10, 2, 19.99, 'EUR');

Phase 3 – Backfill verification

SELECT COUNT(*) AS missing FROM orders WHERE currency_code IS NULL;
SELECT COUNT(*) AS missing FROM order_items WHERE currency_code IS NULL;

-- Mismatch check: item currency should match parent order
SELECT oi.id
FROM order_items oi
INNER JOIN orders o ON o.id = oi.order_id
WHERE oi.currency_code  o.currency_code;

Fix anomalies before enforcing NOT NULL without default in app logic.

Phase 4 – Read switch

Deploy app that reads and displays currency_code. Reporting queries updated:

SELECT currency_code, SUM(quantity * unit_price) AS revenue
FROM order_items oi
INNER JOIN orders o ON o.id = oi.order_id
WHERE o.status IN ('paid', 'shipped')
GROUP BY currency_code;

Phase 5 – Contract (optional hardening)

Once all code paths set currency explicitly, remove DEFAULT so mistakes fail loud:

ALTER TABLE orders ALTER COLUMN currency_code DROP DEFAULT;
ALTER TABLE order_items ALTER COLUMN currency_code DROP DEFAULT;

Add CHECK if you support limited set:

ALTER TABLE orders
ADD CONSTRAINT chk_orders_currency CHECK (currency_code IN ('GBP', 'EUR', 'USD'));

Rollback plan

Before Phase 4: revert app deploy; columns harmless with DEFAULT
After Phase 4: revert app to ignore currency in reads; column stays
Full rollback: drop columns only if no production data depends on them – usually avoid; leave nullable unused column instead
Take backup before each DDL phase – intermediate backup tutorial habits apply

What to document

Write a short runbook containing:

Order of deploys (migration to app dual-write to app read to contract)
Verification queries after each phase
Estimated lock time for each ALTER (test on staging copy)
Owner and rollback decision point

That finishes Going deep with MySQL. You should understand large-table strategies, replication trade-offs, locking and deadlocks, when FULLTEXT is enough, JSON pitfalls, least-privilege users, observability basics, multi-tenant schema choices, and how apps scale against MySQL without N+1 and connection storms. The deliberate optimisation and migration projects tie those threads to work you would recognise in production.