ALTER TABLE and Schema Migrations

The schema you designed on day one will not survive contact with reality. A new regulation requires storing employee email addresses. A product rename means the projects table needs a clearer name. A report needs a priority column that did not exist last quarter.

Changing a live database is dangerous because it already holds data you cannot afford to lose. ALTER TABLE is how you evolve a schema in place, but it comes with limits and sharp edges. This lesson covers what SQLite can change directly, what it cannot, and the safe patterns that protect your data when the database engine refuses to do the work for you.

flowchart LR
  A["ALTER TABLE"] --> B["RENAME TO"]
  A --> C["ADD COLUMN"]
  A --> D["RENAME COLUMN"]
  style A fill:#0e7490,color:#fff

SQLite's ALTER TABLE supports rename, add column, and rename column directly.

What SQLite can change directly

SQLite's ALTER TABLE is intentionally minimal compared to PostgreSQL or MySQL, but it covers the most common changes. You can rename a table, add a column, rename a column, or drop a column (in newer versions).

Adding a column is the most common operation: ALTER TABLE employees ADD COLUMN email TEXT;. The new column is appended to the right of the table, and every existing row receives a NULL for that column unless you provide a DEFAULT value. A default is especially useful when the new column is NOT NULL — SQLite requires a default for existing rows so they do not violate the constraint immediately.

Add an email column with a default value, then inspect the result.

ALTER TABLE employees
ADD COLUMN email TEXT DEFAULT 'unknown';

SELECT name, email FROM employees LIMIT 3;

Renaming tables and columns

Renaming a table is straightforward: ALTER TABLE projects RENAME TO initiatives;. Every query that referenced projects must be updated, but the data and the structure inside the table are untouched. Renaming a column uses a similar shape: ALTER TABLE employees RENAME COLUMN role TO job_title;. The column keeps all its data; only the name changes.

These operations are fast because SQLite only updates the schema catalog — the rows themselves are not rewritten. That makes renames safe to run on large tables, but it also means you must still hunt down every query in your application that used the old name and update it.

Rename a column, then query it by its new name.

ALTER TABLE employees
RENAME COLUMN role TO job_title;

SELECT name, job_title FROM employees LIMIT 3;

flowchart TD
  O["old table"] --> C["CREATE new_table<br/>with desired schema"]
  C --> I["INSERT INTO new_table<br/>SELECT * FROM old"]
  I --> D["DROP TABLE old"]
  D --> R["ALTER TABLE new_table<br/>RENAME TO old"]
  style C fill:#0e7490,color:#fff
  style R fill:#0e7490,color:#fff

When ALTER TABLE cannot make the change, use the recreate pattern: build a new table, migrate, swap.

The recreate pattern for changes SQLite cannot make

SQLite cannot add a PRIMARY KEY to an existing column, change a column's data type, or reorder columns. When you need one of these changes, you build a new table with the desired schema, copy the data over, drop the old table, and rename the new one.

This pattern is more work, but it is also safer because you can inspect the new table before you drop the old one. The steps are mechanical: create, insert, verify, drop, rename. Each step is simple; the discipline is doing them in order and never skipping the verify step.

Use the recreate pattern to add a NOT NULL column with a default.

CREATE TABLE projects_v2 (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  status TEXT,
  budget INTEGER,
  priority TEXT NOT NULL DEFAULT 'medium'
);

INSERT INTO projects_v2 (id, name, department_id, status, budget)
SELECT id, name, department_id, status, budget FROM projects;

DROP TABLE projects;
ALTER TABLE projects_v2 RENAME TO projects;

SELECT name, priority FROM projects LIMIT 2;

flowchart LR
  P["Plan target schema"] --> W["Write script"]
  W --> T["Test on copy"]
  T --> V["Verify row counts"]
  V --> R["Run on production"]
  style T fill:#0e7490,color:#fff
  style V fill:#0e7490,color:#fff

A safe migration workflow: plan, script, test on a copy, verify, then run on production.

Planning a safe migration

A schema migration is not a single command — it is a small project. Start by writing the target schema on paper and comparing it to the current one. Identify which changes SQLite can make directly and which need the recreate pattern.

Next, write the migration script as a single file that can be run in a transaction. Even though SQLite supports limited ALTER TABLE, wrapping the steps in a transaction means that if any step fails, the whole migration rolls back and the database is left untouched. Finally, verify: run a few representative queries against the new schema and confirm the row counts and the column values are exactly what you expect.

Exercise

Add a column named phone of type TEXT with a default value of 'unknown' to the employees table.

ALTER TABLE employees
-- add phone with default 'unknown'
;

ALTER TABLE employees ADD COLUMN phone TEXT DEFAULT 'unknown';

Exercise

Rename the budget column in the projects table to allocated_budget.

ALTER TABLE projects
-- rename budget to allocated_budget
;

ALTER TABLE projects RENAME COLUMN budget TO allocated_budget;

Exercise

This ALTER TABLE statement is missing the table name. Fix it so it adds a start_date column of type TEXT to the projects table.

ALTER TABLE ADD COLUMN start_date TEXT;

ALTER TABLE projects ADD COLUMN start_date TEXT;

Exercise

The projects table needs a priority column that is NOT NULL with default 'medium'. SQLite cannot add NOT NULL without a default. Use the recreate pattern: create projects_v2 with all existing columns plus priority TEXT NOT NULL DEFAULT 'medium', copy the data, drop the old table, and rename the new one.

CREATE TABLE projects_v2 (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  status TEXT,
  budget INTEGER,
  priority TEXT NOT NULL DEFAULT 'medium'
);

-- copy data, drop old, rename

CREATE TABLE projects_v2 (id INTEGER PRIMARY KEY, name TEXT NOT NULL, department_id INTEGER, status TEXT, budget INTEGER, priority TEXT NOT NULL DEFAULT 'medium'); INSERT INTO projects_v2 (id, name, department_id, status, budget) SELECT id, name, department_id, status, budget FROM projects; DROP TABLE projects; ALTER TABLE projects_v2 RENAME TO projects;

Exercise

You need to change the data type of an existing column in SQLite. ALTER TABLE does not support this directly. What is the safest approach?

Recap

ALTER TABLE ... ADD COLUMN appends a new column; use DEFAULT to populate existing rows.
ALTER TABLE ... RENAME COLUMN renames a column in place without touching its data.
ALTER TABLE ... RENAME TO renames the entire table.
When SQLite cannot make a change directly, use the recreate pattern: create, insert, verify, drop, rename.
Always test migrations on a copy and compare row counts before applying them live.

Next you will learn how to design tables from scratch so they need fewer migrations later — the rules of normalization and when to break them.

ALTER TABLE and Schema Migrations

What SQLite can change directly

Renaming tables and columns

The recreate pattern for changes SQLite cannot make

Planning a safe migration

Recap

Checkpoint quiz

Go deeper — technical resources