Chapter 11 · Defining Databases and Tables
ALTER TABLE and Schema Evolution
Schema evolution changes the contract between stored data and every client that reads or writes it. Safe ALTER TABLE work is therefore a compatibility migration, not merely a DDL command.
Learning outcomes
A migration changes both the schema and the contract expected by deployed clients. The safest strategy is often additive, staged, observable, and reversible rather than a single breaking ALTER statement.
Classify schema changes as compatible, conditionally compatible, or breaking.
Use SQLite rename, add-column, drop-column, and rebuild workflows.
Plan backfills and validation before making a new rule mandatory.
Apply expand-and-contract migrations across independently deployed clients.
Create migration evidence and a tested rollback or forward-fix plan.
The migration lifecycle
Expand-and-contract separates compatibility from cleanup so old and new application versions can coexist during deployment.
Classify the change before writing SQL
| Change | Typical compatibility | Risk |
|---|---|---|
| Add nullable column | Usually backward compatible. | New readers must handle NULL until backfill completes. |
| Add column with safe default | Often compatible. | Default semantics and table rewrite behavior vary by engine. |
| Rename column | Breaking for clients using the old name. | Queries, views, triggers, exports, and ORMs may fail. |
| Drop column | Breaking and destructive. | Data and dependent objects may be lost. |
| Narrow type or add CHECK | Conditionally compatible. | Existing rows or new writers may violate the rule. |
| Split one table into several | Architectural migration. | Requires dual reads/writes, backfill, and cutover. |
SQLite’s direct ALTER operations
ALTER TABLE course RENAME TO learning_course;ALTER TABLE learning_course RENAME COLUMN title TO course_title;ALTER TABLE learning_courseADD COLUMN summary TEXT;ALTER TABLE learning_courseADD COLUMN archived INTEGER NOT NULL DEFAULT 0 CHECK (archived IN (0, 1));-- Supported by modern SQLite when dependency rules permit it.ALTER TABLE learning_course DROP COLUMN summary;SQLite updates the schema text stored in sqlite_schema. Rename operations propagate to many dependent definitions in current versions, but every view, trigger, generated expression, and external client still requires verification.
Add, backfill, validate, enforce
-- Phase 1: compatible expansion.ALTER TABLE course ADD COLUMN delivery_mode TEXT;-- Phase 2: backfill existing rows.UPDATE courseSET delivery_mode = 'online'WHERE delivery_mode IS NULL;-- Phase 3: validate before stronger enforcement.SELECT COUNT(*) AS invalid_rowsFROM courseWHERE delivery_mode IS NULL OR delivery_mode NOT IN ('online', 'onsite', 'hybrid');SQLite cannot add every possible constrained column directly. To make the column formally NOT NULL with a CHECK rule, rebuild the table after validation.
The general SQLite rebuild procedure
PRAGMA foreign_keys = OFF;BEGIN IMMEDIATE;CREATE TABLE course_new ( course_id INTEGER PRIMARY KEY, department_id INTEGER NOT NULL REFERENCES department(department_id), instructor_id INTEGER REFERENCES instructor(instructor_id) ON DELETE SET NULL, course_code TEXT NOT NULL, title TEXT NOT NULL, credits INTEGER NOT NULL DEFAULT 3 CHECK (credits BETWEEN 1 AND 6), capacity INTEGER NOT NULL DEFAULT 30 CHECK (capacity > 0), published INTEGER NOT NULL DEFAULT 0 CHECK (published IN (0, 1)), delivery_mode TEXT NOT NULL DEFAULT 'online' CHECK (delivery_mode IN ('online', 'onsite', 'hybrid')), created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE (department_id, course_code)) STRICT;INSERT INTO course_new ( course_id, department_id, instructor_id, course_code, title, credits, capacity, published, delivery_mode, created_at)SELECT course_id, department_id, instructor_id, course_code, title, credits, capacity, published, COALESCE(delivery_mode, 'online'), created_atFROM course;DROP TABLE course;ALTER TABLE course_new RENAME TO course;PRAGMA foreign_key_check;COMMIT;PRAGMA foreign_keys = ON;Indexes, triggers, and views that were not included in the new table definition may need explicit recreation. Capture them from version control or sqlite_schema before rebuilding.
Preserve the original contract during transition
CREATE VIEW course_legacy ASSELECT course_id, department_id, instructor_id, course_code, title, credits, capacity, published, created_atFROM course;-- New clients use delivery_mode from course.-- Old read-only clients can temporarily use course_legacy.Compatibility views can buy time for readers. Writers usually require a more explicit dual-write or application migration strategy because writable-view behavior is vendor-specific.
Migration metadata and ordering
CREATE TABLE IF NOT EXISTS schema_migration ( version INTEGER PRIMARY KEY, migration_id TEXT NOT NULL UNIQUE, checksum TEXT NOT NULL, applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;INSERT INTO schema_migration (version, migration_id, checksum)VALUES (11, '011_add_course_delivery_mode', 'sha256:example');A migration tool should apply immutable, ordered scripts once, verify checksums, and stop on drift. Editing an already-applied migration destroys reproducibility; add a new corrective migration instead.
Reusable Chapter 11 practice schema
Run this SQLite script in a disposable database before the hands-on exercises. It establishes a small academic domain with strict tables, generated data, composite uniqueness, foreign keys, a view, and representative rows.
PRAGMA foreign_keys = ON;DROP VIEW IF EXISTS active_course_catalog;DROP TABLE IF EXISTS enrollment;DROP TABLE IF EXISTS course;DROP TABLE IF EXISTS instructor;DROP TABLE IF EXISTS department;CREATE TABLE department ( department_id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE, name TEXT NOT NULL UNIQUE, budget_cents INTEGER NOT NULL DEFAULT 0 CHECK (budget_cents >= 0), created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE instructor ( instructor_id INTEGER PRIMARY KEY, department_id INTEGER NOT NULL REFERENCES department(department_id), email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, hired_on TEXT NOT NULL CHECK (date(hired_on) IS NOT NULL), active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1))) STRICT;CREATE TABLE course ( course_id INTEGER PRIMARY KEY, department_id INTEGER NOT NULL REFERENCES department(department_id), instructor_id INTEGER REFERENCES instructor(instructor_id) ON DELETE SET NULL, course_code TEXT NOT NULL, title TEXT NOT NULL, credits INTEGER NOT NULL DEFAULT 3 CHECK (credits BETWEEN 1 AND 6), capacity INTEGER NOT NULL DEFAULT 30 CHECK (capacity > 0), published INTEGER NOT NULL DEFAULT 0 CHECK (published IN (0, 1)), display_name TEXT GENERATED ALWAYS AS (course_code || ' · ' || title) VIRTUAL, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE (department_id, course_code)) STRICT;CREATE TABLE enrollment ( course_id INTEGER NOT NULL REFERENCES course(course_id) ON DELETE CASCADE, student_id INTEGER NOT NULL, enrolled_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, status TEXT NOT NULL DEFAULT 'enrolled' CHECK (status IN ('enrolled', 'completed', 'withdrawn')), PRIMARY KEY (course_id, student_id)) STRICT, WITHOUT ROWID;CREATE VIEW active_course_catalog ASSELECT c.course_id, d.code AS department_code, c.course_code, c.title, c.credits, c.capacityFROM course AS cJOIN department AS d ON d.department_id = c.department_idWHERE c.published = 1;INSERT INTO department (department_id, code, name, budget_cents) VALUES (1, 'DATA', 'Data Engineering', 25000000), (2, 'CS', 'Computer Science', 30000000);INSERT INTO instructor (instructor_id, department_id, email, full_name, hired_on)VALUES (10, 1, 'nadia@example.edu', 'Nadia Rahimi', '2024-09-01'), (11, 2, 'omar@example.edu', 'Omar Haddad', '2023-02-15');INSERT INTO course (course_id, department_id, instructor_id, course_code, title, credits, capacity, published)VALUES (100, 1, 10, 'SQL-101', 'SQL Foundations', 3, 40, 1), (101, 1, 10, 'DE-201', 'Data Pipelines', 4, 30, 1), (102, 2, 11, 'DB-220', 'Database Systems',4, 35, 0);INSERT INTO enrollment (course_id, student_id, status) VALUES (100, 1001, 'enrolled'), (100, 1002, 'completed'), (101, 1001, 'enrolled');Hands-on: rename and add safely
BEGIN IMMEDIATE;ALTER TABLE instructor RENAME COLUMN active TO is_active;ALTER TABLE instructorADD COLUMN biography TEXT;UPDATE instructorSET biography = 'Biography pending editorial review'WHERE biography IS NULL;SELECT instructor_id, full_name, is_active, biographyFROM instructorORDER BY instructor_id;ROLLBACK;Rollback restores the original schema because SQLite schema changes participate in transactions. Still test this behavior in the exact target product and migration framework.
Preflight and postflight checklist
| Before | After |
|---|---|
| Inventory tables, views, indexes, triggers, foreign keys, generated columns, and application queries. | Compare object definitions with the intended schema. |
| Measure table size, lock duration, disk headroom, and backup recovery time. | Verify row counts, checksums or aggregates, and foreign keys. |
| Run the migration against a production-like copy. | Exercise old and new application versions where overlap is expected. |
| Define rollback and forward-fix triggers. | Record migration version, duration, operator, and validation evidence. |
Checkpoint
Plan the migration
- Why is a column rename normally breaking even when the database executes it instantly?
- What are the phases of expand-and-contract?
- Why backfill before adding a mandatory rule?
- Which objects must be captured before an SQLite table rebuild?
- Why should an applied migration remain immutable?
Review the answers
Clients still reference the old name. Expand-and-contract adds the new contract, deploys compatible code, backfills and validates, enforces the rule, then removes the old contract. Backfill prevents existing rows from violating enforcement. Capture indexes, triggers, views, foreign keys, generated expressions, and grants where applicable. Immutable migrations preserve reproducibility and drift detection.
Summary and references
- Schema evolution is a compatibility problem.
- Additive changes are usually safer than immediate renames or drops.
- SQLite supports several direct ALTER operations and a general table-rebuild workflow.
- Backfill, validate, enforce, and clean up in separate observable stages.
- Versioned immutable migrations create operational evidence.