Schema Versioning, Migrations & CQRS
You'll learn to
- -Version a schema so old and new application code can both run safely during a rolling migration
- -Separate write and read models (CQRS) and explain what problem that split actually solves
This closing chapter of the module addresses a problem every other chapter has quietly assumed away: schemas are not static. Requirements change, and a schema deployed to production needs a safe way to evolve without breaking the application currently running against it - especially in a system deployed across many servers that cannot all update atomically at once.
The Core Problem: Old Code and New Schema, Briefly, at the Same Time
A rolling deployment updates application servers one at a time - which means, for some window of time, old application code and new application code are both running simultaneously against the same database. A migration that renames a column, or makes a previously-nullable column required, breaks the old code the instant 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 general principle: prefer additive changes (new columns, new tables) that old code can simply ignore, over destructive changes (renames, drops, type changes) that old code cannot survive. When a destructive change is genuinely necessary, it needs to be split into multiple additive, individually-safe steps deployed across multiple releases, rather than attempted as one atomic schema change.
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 changeCQRS: Splitting the Write Model from the Read Model
Command Query Responsibility Segregation separates the schema optimized for writes (normalized, minimal redundancy, correctness-focused - everything this course has emphasized) from a schema optimized for reads (denormalized, pre-joined, shaped exactly around a specific query need). Colony data being written by many independent systems, but read primarily through a handful of specific, high-traffic dashboard queries, is a natural candidate: writes go into the normalized colony_records model; a separate, denormalized colony_dashboard_view table (updated asynchronously) serves reads without needing to join anything at request time.
-- 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
);This is denormalization (from the Intermediate Modeling module) taken to its logical extreme: not just duplicating a column or two, but maintaining an entirely separate schema shaped around reads, explicitly decoupled from the write schema's normalized structure - powerful, but real added complexity (two schemas to keep in sync, and reads that are only "eventually" consistent with the latest writes) that needs a genuine justification, exactly like every other technique in this module.
CQRS's asynchronous sync between write and read models means the read model can be briefly stale - a write that just happened might not show up in colony_dashboard_view for a few seconds. That eventual-consistency trade-off needs to 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.