Migrations

Databases aren't designed once and left forever. Requirements change, new features arrive, and schemas must evolve. A migration is a controlled change to your database schema -- adding columns, renaming tables, or transforming data. Done well, migrations are smooth and reversible. Done poorly, they corrupt data or break applications. This lesson teaches you how to do them well.

ALTER TABLE Basics

ALTER TABLE is the primary tool for modifying an existing table's structure.

Adding Columns

-- Create an initial users table
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL
);

INSERT INTO users (id, username, email)
VALUES
    (1, 'alice', 'alice@example.com'),
    (2, 'bob', 'bob@example.com'),
    (3, 'carol', 'carol@example.com');

-- View the original structure
SELECT * FROM users;

-- Migration: add new columns
ALTER TABLE users ADD COLUMN created_at TEXT DEFAULT '2025-01-01';
ALTER TABLE users ADD COLUMN is_active INTEGER DEFAULT 1;
ALTER TABLE users ADD COLUMN bio TEXT;

-- View the updated structure
SELECT * FROM users;

-- New inserts use the new columns
INSERT INTO users (id, username, email, created_at, is_active, bio)
VALUES (4, 'david', 'david@example.com', '2025-02-15', 1, 'New user');

SELECT * FROM users;

When adding a column with a DEFAULT value, all existing rows automatically get that default. Columns without defaults get NULL.

Renaming Tables and Columns

-- Create an original table
CREATE TABLE blog_posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT,
    author TEXT,
    pub_date TEXT
);

INSERT INTO blog_posts (id, title, body, author, pub_date)
VALUES
    (1, 'Getting Started with SQL', 'SQL is great...', 'alice', '2025-01-10'),
    (2, 'Advanced Joins', 'Let me explain joins...', 'bob', '2025-01-15');

-- Rename the table
ALTER TABLE blog_posts RENAME TO articles;

-- Rename a column (SQLite 3.25+)
ALTER TABLE articles RENAME COLUMN pub_date TO published_at;
ALTER TABLE articles RENAME COLUMN body TO content;

-- Verify the changes
SELECT * FROM articles;

-- New queries use the new names
SELECT title, author, published_at
FROM articles
ORDER BY published_at DESC;

Renaming is one of the safest migrations because no data changes -- only metadata is updated.

Data Migrations

Sometimes you need to transform existing data, not just the schema. This involves reading old data and writing it in a new format.

Splitting a Column

-- Create a table with combined name field
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    full_name TEXT NOT NULL,
    email TEXT
);

INSERT INTO contacts (id, full_name, email)
VALUES
    (1, 'Alice Johnson', 'alice@example.com'),
    (2, 'Bob Smith', 'bob@example.com'),
    (3, 'Carol Lee Davis', 'carol@example.com'),
    (4, 'David Kim', 'david@example.com');

-- Step 1: Add new columns
ALTER TABLE contacts ADD COLUMN first_name TEXT;
ALTER TABLE contacts ADD COLUMN last_name TEXT;

-- Step 2: Migrate the data
-- For simple "First Last" names, split on the first space
UPDATE contacts
SET first_name = substr(full_name, 1, instr(full_name, ' ') - 1),
    last_name = substr(full_name, instr(full_name, ' ') + 1);

-- Step 3: Verify the migration
SELECT id, full_name, first_name, last_name, email
FROM contacts;

-- Step 4: In a real migration, you would eventually drop full_name
-- SQLite doesn't support DROP COLUMN in older versions,
-- but newer SQLite (3.35+) does:
-- ALTER TABLE contacts DROP COLUMN full_name;

Changing Data Types

SQLite is flexible with types, but you may still need to convert data formats.

-- Create a table with dates stored inconsistently
CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    event_date TEXT,
    price TEXT
);

INSERT INTO events (id, name, event_date, price)
VALUES
    (1, 'Conference', '01/15/2025', '$99.99'),
    (2, 'Workshop', '02-20-2025', '$49.50'),
    (3, 'Webinar', '2025-03-10', '$0'),
    (4, 'Meetup', '04/05/2025', '$25.00');

-- View the inconsistent data
SELECT * FROM events;

-- Add properly typed columns
ALTER TABLE events ADD COLUMN event_date_iso TEXT;
ALTER TABLE events ADD COLUMN price_numeric REAL;

-- Migrate dates to ISO format (YYYY-MM-DD)
-- Handle the format that's already correct
UPDATE events
SET event_date_iso = event_date
WHERE event_date LIKE '____-__-__';

-- Handle MM/DD/YYYY format
UPDATE events
SET event_date_iso = substr(event_date, 7, 4) || '-' || substr(event_date, 1, 2) || '-' || substr(event_date, 4, 2)
WHERE event_date LIKE '__/__/____';

-- Handle MM-DD-YYYY format
UPDATE events
SET event_date_iso = substr(event_date, 7, 4) || '-' || substr(event_date, 1, 2) || '-' || substr(event_date, 4, 2)
WHERE event_date LIKE '__-__-____';

-- Migrate prices: remove $ sign and convert to number
UPDATE events
SET price_numeric = CAST(REPLACE(price, '$', '') AS REAL);

-- Verify the migration
SELECT name, event_date, event_date_iso, price, price_numeric
FROM events
ORDER BY event_date_iso;

The Table Rebuild Pattern

When SQLite doesn't support the ALTER TABLE operation you need (like changing a column's constraints), you rebuild the table.

-- Original table without constraints
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    category TEXT
);

INSERT INTO products VALUES (1, 'Laptop', 999.99, 'Electronics');
INSERT INTO products VALUES (2, 'Mouse', 24.99, 'Electronics');
INSERT INTO products VALUES (3, 'Desk', 299.99, 'Furniture');
INSERT INTO products VALUES (4, NULL, -5.00, NULL);

-- Step 1: Create the new table with proper constraints
CREATE TABLE products_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL CHECK(price >= 0),
    category TEXT NOT NULL DEFAULT 'Uncategorized'
);

-- Step 2: Copy valid data (fix invalid data during copy)
INSERT INTO products_new (id, name, price, category)
SELECT
    id,
    COALESCE(name, 'Unknown Product'),
    CASE WHEN price < 0 THEN 0 ELSE price END,
    COALESCE(category, 'Uncategorized')
FROM products;

-- Step 3: Drop the old table
DROP TABLE products;

-- Step 4: Rename the new table
ALTER TABLE products_new RENAME TO products;

-- Verify the result
SELECT * FROM products;

This four-step pattern (create new, copy data, drop old, rename) is the standard approach for schema changes that ALTER TABLE can't handle directly.

Migration Best Practices

Always Use Transactions

-- Create a table to migrate
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT NOT NULL,
    total REAL NOT NULL,
    status TEXT DEFAULT 'pending'
);

INSERT INTO orders (id, customer, total, status)
VALUES
    (1, 'Alice', 150.00, 'completed'),
    (2, 'Bob', 89.99, 'pending'),
    (3, 'Carol', 250.00, 'completed'),
    (4, 'David', 175.00, 'shipped');

-- Wrap the entire migration in a transaction
BEGIN TRANSACTION;

-- Add new columns
ALTER TABLE orders ADD COLUMN completed_at TEXT;
ALTER TABLE orders ADD COLUMN shipped_at TEXT;

-- Backfill data based on status
UPDATE orders SET completed_at = '2025-01-15'
WHERE status = 'completed';

UPDATE orders SET shipped_at = '2025-01-18'
WHERE status = 'shipped';

-- Verify before committing
SELECT * FROM orders;

COMMIT;

-- If anything went wrong, we would ROLLBACK instead
-- and the database would be unchanged

Backward Compatibility

When migrating in a live application, follow the expand-and-contract pattern:

Expand: Add new columns/tables alongside old ones
Migrate: Copy data to the new structure
Update code: Point application to new columns
Contract: Remove old columns after confirming everything works

-- Phase 1 (Expand): Add new column, keep old one
CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    type TEXT NOT NULL  -- 'free', 'pro', 'enterprise'
);

INSERT INTO accounts VALUES (1, 'Startup Co', 'free');
INSERT INTO accounts VALUES (2, 'Big Corp', 'enterprise');
INSERT INTO accounts VALUES (3, 'Dev Shop', 'pro');

-- Add a richer column alongside the old one
ALTER TABLE accounts ADD COLUMN tier_id INTEGER;

-- Create the new tier reference table
CREATE TABLE tiers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL,
    max_users INTEGER
);

INSERT INTO tiers VALUES (1, 'free', 0, 5);
INSERT INTO tiers VALUES (2, 'pro', 29.99, 25);
INSERT INTO tiers VALUES (3, 'enterprise', 99.99, 999);

-- Phase 2 (Migrate): Populate new column from old data
UPDATE accounts SET tier_id = 1 WHERE type = 'free';
UPDATE accounts SET tier_id = 2 WHERE type = 'pro';
UPDATE accounts SET tier_id = 3 WHERE type = 'enterprise';

-- Both old and new structures work during transition
SELECT
    a.name,
    a.type AS old_type,
    t.name AS new_tier,
    t.price,
    t.max_users
FROM accounts a
JOIN tiers t ON a.tier_id = t.id;

Practice Exercises

Try these on your own:

Add a phone column to a users table and backfill it with a default value
Rebuild a table to add a CHECK constraint that salary must be positive
Split a location column (containing "City, Country") into separate city and country columns
Write a migration that converts a status column from text ("yes"/"no") to integer (1/0)
Create an expand-and-contract migration that replaces a text category column with a foreign key reference

Key Takeaways

ALTER TABLE ADD COLUMN is the simplest migration -- it adds columns with optional defaults
ALTER TABLE RENAME changes table or column names without touching data
Data migrations transform existing values and should always be verified before committing
The table rebuild pattern (create, copy, drop, rename) handles changes ALTER TABLE can't
Always wrap migrations in transactions so you can rollback if something goes wrong
Follow the expand-and-contract pattern for zero-downtime migrations in live applications
Test migrations on a copy of your data before running them on production

Pro Tip: Keep a record of every migration you run, including the SQL and the date. Many frameworks use numbered migration files (001_create_users.sql, 002_add_email.sql) so you can replay them to recreate the schema from scratch. This is essential for team collaboration and deployment automation.

Next Steps

You have now covered all the major SQL topics — from SELECT to schema evolution. The capstone project puts it all together: you will design a complete e-commerce database from scratch, seed it with realistic data, and write the analytical queries that a real business would need. It is the best way to solidify everything before moving on to the extended curriculum.