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.

What You'll Learn

By the end of this lesson you will write a migration that adds a column to a table that already holds rows, fills that column from the data already there, and then verify — by counting rows before and after — that nothing was lost on the way. That verification step is the real skill. A migration that loses a quarter of your rows does not error, does not warn, and produces a table that looks entirely reasonable until someone asks why the totals changed.

This lesson has no capstone milestone of its own; what it is for is keeping the ones you already have. The warehouse schema you designed in the previous lesson will need a column it does not have yet, and every later milestone depends on that change not damaging the rows underneath it. You arrive here able to design a normalized schema with keys and constraints (Database Design), and to insert and update rows (Inserting and Updating); what is new is changing a schema that is already carrying data.

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 ORDER BY id;

-- 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 ORDER BY id;

-- 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 ORDER BY id;

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

That sentence describes what happens to the rows already in the table. The block below asks the other half of the question, about a row inserted after the migration, and it is worth committing to an answer before reading on — because whether the two halves behave the same way is exactly what decides how much you can trust the column afterwards.

CREATE TABLE members (id INTEGER PRIMARY KEY, handle TEXT NOT NULL);
INSERT INTO members VALUES (1,'ada'),(2,'bo');
ALTER TABLE members ADD COLUMN joined_on TEXT DEFAULT '2025-01-01';
ALTER TABLE members ADD COLUMN nickname TEXT;
INSERT INTO members (id, handle) VALUES (3,'cy');
SELECT id, handle, joined_on, nickname FROM members ORDER BY id;

So a DEFAULT is not only a value for future inserts; it is also what every row already in the table is given, retroactively, with no way afterwards to tell which rows meant it and which merely inherited it. A column with no default leaves the old rows NULL, which is honest — NULL says nothing is known here — but it means the column is not safe to read without handling that. Neither choice is wrong; choosing without noticing is.

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

The migration below uses two string functions you have not met yet — they are taught in String Functions, which comes after this lesson — so here is what each one does, because the migration is unreadable without them. instr(haystack, needle) returns the 1-based position where the needle first appears, and returns 0 when it does not appear at all. substr(text, start, length) returns the slice of text beginning at the 1-based position start, running for length characters, or to the end of the string if length is omitted. Composed, substr(full_name, 1, instr(full_name, ' ') - 1) says: take everything from the first character up to just before the first space. Hold on to the fact that instr returns 0 on no match — the debug block below is built entirely out of that one return value.

-- 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
ORDER BY id;

-- Step 4: Retire the old column. DROP COLUMN needs SQLite 3.35 or newer;
-- this environment runs 3.46.1, so it works here.
ALTER TABLE contacts DROP COLUMN full_name;

SELECT id, first_name, last_name, email
FROM contacts
ORDER BY id;

Run that last step and then read the two result sets together: the first still shows full_name, the second cannot, and there is no undo. DROP COLUMN is the one statement in this lesson that destroys data rather than adding to it, which is why the four steps are in this order — the new columns are populated and verified before the source column is dropped, never the other way round.

CREATE TABLE people (id INTEGER PRIMARY KEY, full_name TEXT NOT NULL);
INSERT INTO people VALUES (1,'Ada Lovelace'),(2,'Grace Hopper'),(3,'Prince');
ALTER TABLE people ADD COLUMN first_name TEXT;
UPDATE people SET first_name = substr(full_name, 1, instr(full_name, ' ') - 1);
SELECT id, full_name, first_name FROM people ORDER BY id;

3 row(s) affected
3 row(s) affected
id | full_name | first_name
---------------------------
1 | Ada Lovelace | Ada
2 | Grace Hopper | Grace
3 | Prince | Prince

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;

One more borrowed function: REPLACE(text, old, new) returns text with every occurrence of old swapped for new, so REPLACE(price, '$', '') deletes the currency symbol by replacing it with nothing. It is covered properly in String Functions; here it is doing the smallest possible job. Note the shape of this whole section: the migration writes into new columns and leaves the originals untouched, so if a format turns up that none of the three UPDATE statements matched, the evidence is still sitting in event_date where you can see it.

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)
-- COALESCE(x, fallback) returns x unless x is NULL, in which case it returns the fallback.
-- It is how the NULL name and NULL category in row 4 become values the new NOT NULL columns accept.
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 ORDER BY id;

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.

The four steps are in a forced order, and step 2 is where rows go missing. Everything the new table will ever hold has to pass through that one INSERT ... SELECT, so any filter on it — a WHERE you added while testing, a join that does not match every row — silently decides which rows survive the migration. Step 3 then destroys the evidence.

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes VALUES (1,'first'),(2,NULL),(3,'third');

Three notes are already loaded, one of them with a NULL body. The rebuild below tightens body to NOT NULL and gives the null row a placeholder on the way across.

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 ORDER BY id;

COMMIT;

Open that fence with its Open in Playground pill and press Run Code. The SELECT between the UPDATEs and the COMMIT is the whole point of the shape: inside the transaction you can look at what the migration did and still change your mind, because until COMMIT runs nothing is final. Had the verification shown something wrong, the alternative ending is ROLLBACK; instead of COMMIT;, and the table would be exactly as it was before BEGIN.

One honest caveat, because it is easy to over-read what a transaction buys you. A transaction is not an automatic undo for a statement that fails. If one statement inside BEGIN ... COMMIT errors, only that statement is abandoned; the others are still there, and a COMMIT afterwards makes them permanent. Getting all-or-nothing means deciding to roll back — the script, or the tool running it, has to issue the ROLLBACK itself. Transactions is the lesson that takes this apart properly; what matters here is that wrapping a migration in BEGIN gives you the option to undo it, not the guarantee.

Migrations Meet Untrusted Input Too

Migrations look like a place the security question does not arise, because you are writing the script yourself against your own database. Two things put it back on the table. The values a data migration moves are the values your users supplied, so any backfill that builds its UPDATE by gluing a value into the query text has the same injection surface as any other query — the value belongs in a bound parameter, the ? placeholder from Inserting and Updating, with the driver keeping it separate from the statement.

The second is sharper and is specific to this work: migration tooling often wants the table or column name to be dynamic, and a placeholder can never stand for an identifier. ALTER TABLE ? ... does not work and cannot be made to work, so a migration that takes a table name from a config file or a request has to check that name against a fixed allow-list you wrote yourself before it is spliced in. As in the earlier lessons, none of this runs here — the Run button executes a literal script with no program or driver around it.

Backward Compatibility

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

1. Expand: Add new columns/tables alongside old ones
2. Migrate: Copy data to the new structure
3. Update code: Point application to new columns
4. 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
ORDER BY a.id;

A Rebuild That Runs and Loses Rows

CREATE TABLE plans (id INTEGER PRIMARY KEY, label TEXT NOT NULL, monthly_cents INTEGER NOT NULL);
INSERT INTO plans VALUES (1,'basic',900),(2,'plus',1900),(3,'max',4900);
CREATE TABLE plans_new (id INTEGER PRIMARY KEY, label TEXT NOT NULL, monthly_cents INTEGER NOT NULL CHECK(monthly_cents > 0), yearly_cents INTEGER NOT NULL);
INSERT INTO plans_new (id, label, monthly_cents, yearly_cents)
SELECT id, label, monthly_cents, monthly_cents * 12 FROM plans WHERE monthly_cents > 1000;
DROP TABLE plans;
ALTER TABLE plans_new RENAME TO plans;
SELECT id, label, monthly_cents, yearly_cents FROM plans ORDER BY id;

3 row(s) affected
3 row(s) affected
id | label | monthly_cents | yearly_cents
-----------------------------------------
1 | basic | 900 | 10800
2 | plus | 1900 | 22800
3 | max | 4900 | 58800

Try It Yourself

The build below is a small expand-and-contract migration. The starter is the before state: a table with a text status column and nothing else. Add the new column, fill it from the old one, and report the result.

CREATE TABLE tickets (id INTEGER PRIMARY KEY, subject TEXT NOT NULL, state TEXT NOT NULL);
INSERT INTO tickets VALUES (1,'Login fails','open'),(2,'Slow export','closed'),(3,'Typo in help','open'),(4,'Card declined','closed');
-- TODO 1: add an integer column named is_open. Give it no DEFAULT, so an unfilled
--         row stays visibly NULL rather than quietly claiming to be closed.
-- TODO 2: fill is_open from state. A ticket whose state is open gets 1; every
--         other ticket gets 0. All four rows must be updated by one statement.
-- TODO 3: after the listing below, add a second query reporting one row per
--         distinct is_open value with a count named n, ordered by is_open.
--         The four tickets split two and two.
SELECT id, subject, state FROM tickets ORDER BY id;

4 row(s) affected
4 row(s) affected
id | subject | state | is_open
------------------------------
1 | Login fails | open | 1
2 | Slow export | closed | 0
3 | Typo in help | open | 1
4 | Card declined | closed | 0
is_open | n
-----------
0 | 2
1 | 2

Once it passes, try two variations and predict each before running. First, change the UPDATE to carry a WHERE clause naming only the open tickets — predict what the two closed tickets now hold in is_open, and confirm that the final count query returns a row for None rather than for 0. Second, add a fifth ticket with state escalated and rerun without touching the CASE — predict which side of the count it lands on, and decide whether the ELSE branch is still the behaviour you want once state has more than two values.

CREATE TABLE items_a (id INTEGER PRIMARY KEY, name TEXT, cents INTEGER);
INSERT INTO items_a VALUES (1,'Mouse',2500),(2,NULL,900);

-- shape A: add, then backfill
ALTER TABLE items_a ADD COLUMN label TEXT;
UPDATE items_a SET label = COALESCE(name, 'Unknown');
SELECT id, name, label FROM items_a ORDER BY id;

CREATE TABLE items_b (id INTEGER PRIMARY KEY, name TEXT, cents INTEGER);
INSERT INTO items_b VALUES (1,'Mouse',2500),(2,NULL,900);

-- shape B: rebuild
CREATE TABLE items_b_new (id INTEGER PRIMARY KEY, name TEXT, cents INTEGER, label TEXT NOT NULL);
INSERT INTO items_b_new (id,name,cents,label) SELECT id, name, cents, COALESCE(name,'Unknown') FROM items_b;
DROP TABLE items_b;
ALTER TABLE items_b_new RENAME TO items_b;
SELECT id, name, label FROM items_b ORDER BY id;

-- one more attempt, on shape A's table: a NOT NULL column with no DEFAULT
ALTER TABLE items_a ADD COLUMN note TEXT NOT NULL;

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 the structural half of SQL — from SELECT to schema evolution. The rest of the core path is about the data itself: the messy, real-world values inside your columns. The next lesson covers string functions, the tools you need to clean, format, and extract text right inside a query.