Schema Versioning, Migrations & CQRS
You'll learn to
- -Explain why a column rename can crash a live system, and use an additive, multi-step migration instead
- -Record applied migrations so running one twice is harmless
- -Describe CQRS as separate write and read models, and the eventual-consistency price you accept
This closing chapter of the module covers a problem every other chapter quietly assumed away: schemas change. Requirements change, and a schema already running in production needs a safe way to evolve without breaking the application that is using it, especially when that application runs on many servers that cannot all update at the same instant.
An Analogy: Renovating a Restaurant That Stays Open
Imagine renovating a restaurant without closing it. You cannot bolt up the door the waiters use and tell them to find the new one afterwards. The safe way is to build the new door first, let waiters use either door for a while, move everyone over, and only then remove the old door. A schema migration is the same.
The Core Problem: Old Code and a New Schema at the Same Time
A rolling deployment updates application servers one at a time. For a while, old code and new code both run against the same database. A migration that renames a column, or makes a nullable column required, breaks the old code the moment it runs, before the rollout even finishes.
Additive, Backward-Compatible Migrations
-- Adding a new column with a default is safe: old code that doesn't
-- know about it simply never references it, and continues working unchanged.
ALTER TABLE colony_records ADD COLUMN oxygen_reserve_pct DECIMAL(5,2) DEFAULT 100.00;
-- Renaming a column is NOT safe in one step - old code referencing the
-- old name breaks immediately. The safe pattern is a multi-phase migration:
-- 1) add the new column, 2) backfill it and dual-write to both columns,
-- 3) migrate all readers to the new column, 4) only then drop the old one.The demo plays that out with two versions of the code. First the column is renamed in one step, and the old code crashes at once because the column it asks for no longer exists. Then the new column is added alongside the old one, and both versions of the code keep working.
The general principle is to prefer additive changes, such as new columns and new tables, that old code can simply ignore, over destructive ones such as renames, drops and type changes, that old code cannot survive. When a destructive change is truly needed, split it into several additive steps spread over several releases: add the new column, backfill it and write to both, move all readers over, and only then drop the old one.
Tracking Schema Version Explicitly
CREATE TABLE schema_migrations (
version INTEGER PRIMARY KEY,
description TEXT NOT NULL,
applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- every migration script checks this table before running, so
-- re-running the same migration twice is a safe no-op, not a repeated changeEach migration script checks this table first, so re-running one is a harmless no-op and not a repeated change.
CQRS: Splitting the Write Model From the Read Model
Think of a restaurant's kitchen and its menu board. The kitchen is organized for cooking correctly, with ingredients stored once. The board is organized for customers reading quickly, with everything pre-shaped. Command Query Responsibility Segregation (CQRS) does the same for data. The write side is the normalized schema everything in this course has favored, built for correctness. The read side is a separate, denormalized, pre-joined shape built for one specific query, updated asynchronously, meaning a moment after the write, not in the same step.
-- Write model: normalized, correctness-focused
CREATE TABLE colony_records (id INTEGER PRIMARY KEY, resource_type TEXT, quantity INTEGER, colony_id INTEGER);
-- Read model: denormalized, pre-shaped exactly for the dashboard query,
-- updated asynchronously (by an event handler, a scheduled job, etc.)
CREATE TABLE colony_dashboard_view (
colony_id INTEGER PRIMARY KEY,
colony_name TEXT NOT NULL, -- duplicated from a colonies table
total_oxygen DECIMAL(10,2),
total_food DECIMAL(10,2),
last_updated TIMESTAMP
);The demo shows the catch. Two oxygen readings are written to the normalized model. The dashboard is checked straight away and has nothing yet. After the sync runs, it shows the total of 65.
This is denormalization taken to its limit: not just a duplicated column, but a whole separate schema shaped around reads. It is powerful, but it is real added complexity, with two schemas to keep in sync and reads that are only eventually consistent. It needs a genuine reason, like every technique in this module.
CQRS's asynchronous sync means the read model can be briefly stale: a write that just happened might not show on the dashboard for a few seconds. That eventual-consistency trade-off must be an explicit, acceptable choice for the use case, not a surprise discovered later.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Design Colony Data, Quantum Data, and the Cooper Station capstone schema in the LLD Lab's Interstellar act.
Discussion0
Join the Discussion
Sign in to leave comments, reply to others, or like insights.
No comments yet. Be the first to start the thread!