ALTER TABLE and safe schema migrations

ALTER TABLE changes a table that already exists: it adds, renames and drops columns, adds and removes constraints, and changes a column’s type. On a laptop each change takes a moment. On a production table that thousands of requests read every second, the same statement can lock the table, rewrite every row, or break the version of the application that is still running. Safe schema migrations avoid that with three habits: every change is a versioned file that is applied once, in order; each step leaves a schema that both the old and the new code can use; and slow work, such as filling a new column, happens in small batches. This lesson shows what ALTER TABLE can do in each engine, where SQLite needs a table rebuild instead, how each engine treats schema changes inside a transaction, and the expand-and-contract plan that makes changes without downtime. Its examples use SQLite 3.49.1 as packaged in sql.js 1.14.2, so each one runs right here.

What ALTER TABLE can do in each engine

-- The four changes SQLite 3.49.1 can make in place, and four it refuses.
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, fax TEXT);
INSERT INTO customers (name, city) VALUES ('Asha Kulkarni', 'Pune'), ('Vikram Nair', 'Kochi');

ALTER TABLE customers ADD COLUMN phone TEXT;                              -- a new, empty column
ALTER TABLE customers ADD COLUMN tier TEXT NOT NULL DEFAULT 'basic';      -- required, so it needs a default
ALTER TABLE customers RENAME COLUMN city TO town;
ALTER TABLE customers DROP COLUMN fax;
ALTER TABLE customers RENAME TO clients;

ALTER TABLE clients ADD COLUMN state_code TEXT NOT NULL;                  -- refused: required without a default
ALTER TABLE clients ADD COLUMN joined_at TEXT DEFAULT CURRENT_TIMESTAMP;  -- refused: the default is not constant
ALTER TABLE clients ADD COLUMN email TEXT UNIQUE;                         -- refused: UNIQUE needs a new table
ALTER TABLE clients ALTER COLUMN phone SET NOT NULL;                      -- no ALTER COLUMN before SQLite 3.53.0

SELECT * FROM clients;
SELECT name, type, "notnull", dflt_value FROM pragma_table_info('clients');

┌─────────────┬───────────────┬───────┬───────┬───────┐
│ customer_id │     name      │ town  │ phone │ tier  │
├─────────────┼───────────────┼───────┼───────┼───────┤
│ 1           │ Asha Kulkarni │ Pune  │       │ basic │
│ 2           │ Vikram Nair   │ Kochi │       │ basic │
└─────────────┴───────────────┴───────┴───────┴───────┘
┌─────────────┬─────────┬─────────┬────────────┐
│    name     │  type   │ notnull │ dflt_value │
├─────────────┼─────────┼─────────┼────────────┤
│ customer_id │ INTEGER │ 0       │            │
│ name        │ TEXT    │ 1       │            │
│ town        │ TEXT    │ 0       │            │
│ phone       │ TEXT    │ 0       │            │
│ tier        │ TEXT    │ 1       │ 'basic'    │
└─────────────┴─────────┴─────────┴────────────┘

Runtime error near line 11: Cannot add a NOT NULL column with default value NULL
Runtime error near line 12: Cannot add a column with non-constant default
Parse error near line 13: Cannot add a UNIQUE column
Parse error near line 14: near "ALTER": syntax error

SQLite added two columns, renamed a column and the table, and dropped a column. It refused a required column without a default, a default that is not constant, a UNIQUE column, and ALTER COLUMN, which SQLite added only in version 3.53.0, for SET NOT NULL and DROP NOT NULL. Everything SQLite cannot change in place is done by building a new table and copying the rows across, a procedure of twelve steps that the SQLite manual spells out. PostgreSQL and MySQL change most of these in place:

Schema changes and transactions

-- In SQLite, as in PostgreSQL, schema changes are part of the transaction.
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL);

BEGIN;
ALTER TABLE customers ADD COLUMN loyalty_points INTEGER NOT NULL DEFAULT 0;
CREATE TABLE loyalty_events (event_id INTEGER PRIMARY KEY, customer_id INTEGER, points INTEGER);
INSERT INTO customers (name) VALUES ('Asha Kulkarni');
ROLLBACK;   -- the migration failed a check later on: undo all of it

SELECT name FROM sqlite_schema WHERE type = 'table';
SELECT name FROM pragma_table_info('customers');
SELECT count(*) AS customers FROM customers;

┌───────────┐
│   name    │
├───────────┤
│ customers │
└───────────┘
┌─────────────┐
│    name     │
├─────────────┤
│ customer_id │
│ name        │
└─────────────┘
┌───────────┐
│ customers │
├───────────┤
│ 0         │
└───────────┘

The rollback took back the new column, the new table and the inserted row together, so the database looked as if the migration had never started. PostgreSQL behaves the same way, which lets a migration run as one transaction that either finishes or leaves nothing behind; on PostgreSQL 18.6 a rolled-back ADD COLUMN and CREATE TABLE left no trace. MySQL does not: a CREATE TABLE or an ALTER TABLE commits the open transaction first. On MySQL 8.4.11 an INSERT made before a CREATE TABLE survived the ROLLBACK, and so did an INSERT made after it, because once the transaction has been committed the session is back in autocommit mode. A MySQL migration that fails halfway therefore stays half applied, which is why such migrations make one schema change per step.

Safe schema migrations

Migration tools differ, but safe schema migrations share one shape. Each change is a numbered file, such as 0042_add_state_code.sql, kept in the same repository as the code. The database records which files it has applied, in a table of its own or, in SQLite, in PRAGMA user_version, so every environment applies the same files in the same order exactly once. A change that the running code cannot survive, such as a rename, is split into steps that each keep both versions of the code working:

Each step can be deployed and, if something goes wrong, reversed on its own. The cost is time: a rename becomes five releases instead of one, and the database holds both columns for a while.

Two more rules keep the history of migrations trustworthy. Never edit a migration that has run anywhere, even on a colleague’s laptop: correct it with a new one, or the environments drift apart without anyone noticing. And write the reverse of each step where one exists, while treating the steps that destroy data, such as dropping a column, as one-way: they run last, after a backup, when nothing reads the old data any more.

Your turn

-- The migration: add the required column state_code and fill it in for every existing customer.


-- Leave this as it is: every customer, their state code, and whether SQLite records the column as NOT NULL.
SELECT customer_id, state_code,
       (SELECT "notnull" FROM pragma_table_info('customers') WHERE name = 'state_code') AS required
FROM customers
ORDER BY customer_id;

# Sample tests: each one creates customers and city_states, runs your migration and compares the rows it returns, in
# order.
tests:
  - name: fills the state code from the city, and marks the column NOT NULL
    ordered: true
    setup: |
      CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT);
      CREATE TABLE city_states (city TEXT PRIMARY KEY, state_code TEXT NOT NULL);
      INSERT INTO customers VALUES (1, 'Asha Kulkarni', 'Pune'), (2, 'Vikram Nair', 'Kochi'), (3, 'Meera Rathore', 'Jaipur');
      INSERT INTO city_states VALUES ('Pune', 'MH'), ('Kochi', 'KL'), ('Jaipur', 'RJ');
    expected:
      columns: [customer_id, state_code, required]
      rows:
        - [1, 'MH', 1]
        - [2, 'KL', 1]
        - [3, 'RJ', 1]
  - name: gives XX to an unknown city and to a missing one
    ordered: true
    setup: |
      CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT);
      CREATE TABLE city_states (city TEXT PRIMARY KEY, state_code TEXT NOT NULL);
      INSERT INTO customers VALUES (1, 'Tenzin Dorjee', 'Gangtok'), (2, 'Ira Sen', 'Atlantis'), (3, 'Kabir Das', NULL);
      INSERT INTO city_states VALUES ('Gangtok', 'SK'), ('Pune', 'MH');
    expected:
      columns: [customer_id, state_code, required]
      rows:
        - [1, 'SK', 1]
        - [2, 'XX', 1]
        - [3, 'XX', 1]

The SQLite table rebuild, step by step, and the shortcut that breaks it Pro: opens with a Pro, Premium or Ultimate pass

Changes without downtime: locks, costs and expand and contract, with numbers Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• ALTER TABLE adds, renames and drops columns everywhere, and adds constraints and changes types in PostgreSQL and MySQL; SQLite rebuilds the table for most other changes.
• Schema changes are transactional in PostgreSQL and SQLite, while in MySQL each one commits at once.
• Safe schema migrations are versioned files applied once, in order, whose every step works with the old and the new code.
• Expand and contract turns a breaking change, such as a rename, into steps that can each be deployed and undone.