Changing a schema: ALTER TABLE and migrations

Managing schema changes as versioned files: `ALTER TABLE` in SQLite and PostgreSQL, SQLite's 12-step table rebuild, a version table, up/down migrations, Flyway, Liquibase, Alembic, Prisma Migrate, and the zero-downtime expand–contract pattern.

  • Advanced
  • 22 min
  • 12 questions
  • Lesson 14 of 28

In this lesson you will learn

  • Explain why migrations and a version table are needed
  • Know what SQLite's ALTER TABLE can and can't do and apply the 12-step rebuild for the rest
  • Write up/down migration files and recognise the tools (Flyway, Liquibase, Alembic, Prisma Migrate)
  • Plan a change without downtime using expand–contract

A team has three developers. One adds a phone column to customers on their laptop, another renames a column, the third fixes something by hand on the server. A month later the test and production databases differ and nobody knows how it happened. Code is versioned in Git — the schema must be too. That is exactly what migrations do: every change is a numbered SQL file, and every database goes through them in the same order. In the lesson «CREATE TABLE: data types and constraints» we saw ALTER TABLE ... ADD COLUMN; this lesson shows its limits, what to do beyond them, and how to manage changes in a team.

Migrations and the version table

MigrationDefinition

A script with a number (version) that moves the schema from one version to the next and is stored in Git together with the code. A migration that has been applied is never edited again.

The database itself remembers which migrations have been applied — in a version table. A migration tool runs the same algorithm every time it starts:

  1. Read the files

    Sort the migrations in the folder by version: V1, V2, V3…

  2. Ask the database

    Read the applied versions from the version table.

  3. Run the pending ones

    Run every new migration in its own transaction; stop on an error.

  4. Record it

    Write the version of a successful migration into the table in the same transaction.

SQL
CREATE TABLE schema_migrations (
  version    INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO schema_migrations (version, name) VALUES (1, 'create_shop_tables');

-- V2__add_customer_phone.sql
BEGIN;
ALTER TABLE customers ADD COLUMN phone TEXT;
INSERT INTO schema_migrations (version, name) VALUES (2, 'add_customer_phone');
COMMIT;

-- V3__add_orders_customer_index.sql
BEGIN;
CREATE INDEX idx_orders_customer ON orders (customer_id);
INSERT INTO schema_migrations (version, name) VALUES (3, 'add_orders_customer_index');
COMMIT;

SELECT version, name FROM schema_migrations ORDER BY version;
Expected output
version | name
1 | create_shop_tables
2 | add_customer_phone
3 | add_orders_customer_index
We repeat the tool's work by hand: each migration and its record share one transaction.

Sign up free to keep reading

The rest of the lesson, the quiz and your notes open with an Educora account. Signing up takes a minute, and your progress is saved on all your devices.

ALTER TABLE in SQLite: what works and what doesn't

In SQLite ALTER TABLE does only four things: renames a table (RENAME TO), adds a column (ADD COLUMN), renames a column (RENAME COLUMN, since 3.25) and drops a column (DROP COLUMN, since 3.35):

SQL
ALTER TABLE customers ADD COLUMN phone TEXT;
ALTER TABLE customers RENAME COLUMN joined_on TO signup_date;
ALTER TABLE customers DROP COLUMN phone;

SELECT group_concat(name, ', ') AS columns
FROM pragma_table_info('customers');
Expected output
columns
id, name, city, country, signup_date
pragma_table_info returns the table's columns row by row.

Each of them has conditions. An added column can't be PRIMARY KEY or UNIQUE; a NOT NULL column needs a non-NULL default; the default must be a constant (no CURRENT_TIMESTAMP). And a column can't be dropped if it is the primary key, UNIQUE, indexed, part of a foreign key, or used in a view, a trigger or a CHECK:

SQL
ALTER TABLE customers ADD COLUMN vip INTEGER NOT NULL;
Expected output
Error: Cannot add a NOT NULL column with default value NULL
SQL
CREATE INDEX idx_customers_city ON customers (city);
ALTER TABLE customers DROP COLUMN city;
Expected output
Error: error in index idx_customers_city after drop column: no such column: city
Drop the index first: DROP INDEX idx_customers_city;
ChangeSQLitePostgreSQLMySQL 8
Add a columnyes, with conditionsyesyes
Rename a columnRENAME COLUMN (3.25+)RENAME COLUMNRENAME COLUMN
Drop a column3.35+, with conditionsyesyes
Change the typeno → rebuildALTER COLUMN … TYPE … USINGMODIFY COLUMN
NOT NULL / CHECK on an existing columnno → rebuildSET NOT NULL, ADD CONSTRAINTMODIFY, ADD CONSTRAINT
DDL inside a transaction (ROLLBACK undoes it)yesyesno — DDL commits implicitly

The 12-step rebuild in SQLite, and the PostgreSQL way

For everything else — changing a type, adding a constraint, changing the primary key — the SQLite documentation prescribes rebuilding the table: create a table in the new shape, copy the data, drop the old one and rename the new one. The documented procedure has 12 steps:

  1. 1. Switch foreign keys off

    PRAGMA foreign_keys = OFF — before the transaction, because inside one this pragma does nothing.

  2. 2. Begin a transaction

    BEGIN — everything happens completely or not at all.

  3. 3. Note the dependants

    SELECT sql FROM sqlite_schema WHERE tbl_name = 'X' — indexes, triggers, views.

  4. 4–5. New table and copy

    CREATE TABLE new_X (...) in the desired shape, then INSERT INTO new_X SELECT ... FROM X.

  5. 6–7. Swap

    DROP TABLE X, then ALTER TABLE new_X RENAME TO X.

  6. 8–9. Restore the dependants

    Recreate indexes, triggers and views (fix a view if the change affects it).

  7. 10. Check

    PRAGMA foreign_key_check — lists broken references.

  8. 11–12. Finish

    COMMIT, then PRAGMA foreign_keys = ON.

We add rules to the products table: the name is unique, the price can't be empty or negative, and the stock defaults to 0:

SQL
PRAGMA foreign_keys = OFF;                         -- step 1
BEGIN;                                             -- step 2
CREATE TABLE new_products (                        -- step 4
  id       INTEGER PRIMARY KEY,
  name     TEXT NOT NULL UNIQUE,
  category TEXT,
  price    REAL NOT NULL CHECK (price >= 0),
  stock    INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);
INSERT INTO new_products (id, name, category, price, stock)   -- step 5
SELECT id, name, category, price, stock FROM products;
DROP TABLE products;                               -- step 6
ALTER TABLE new_products RENAME TO products;       -- step 7
CREATE INDEX idx_products_category ON products (category);   -- step 8
PRAGMA foreign_key_check;                          -- step 10
COMMIT;                                            -- step 11
PRAGMA foreign_keys = ON;                          -- step 12

SELECT COUNT(*) AS rows_copied,
       (SELECT COUNT(*) FROM orders AS o
        JOIN products AS p ON p.id = o.product_id) AS orders_still_linked
FROM products;
Expected output
rows_copied | orders_still_linked
10 | 12
All 10 products were copied and all 12 orders still point to their product (the ids were kept). Now INSERT INTO products (name, price) VALUES ('Broken', -5) fails with CHECK constraint failed: price >= 0.

Why not rename the old table instead?

Problem

A more “natural”-looking way: ALTER TABLE products RENAME TO old_products, then create a new products, copy and drop old_products. What is wrong with it?

Show solutionHide solution

Solution

1) Modern SQLite also updates references in other tables when a table is renamed.
2) So orders.product_id becomes REFERENCES "old_products"(id) (see the example below).
3) When old_products is dropped, the orders point to a table that doesn't exist — the link is broken.
4) The documented order (create the new table and rename it) avoids this trap.
SQL
ALTER TABLE products RENAME TO old_products;

SELECT "from" AS column_name, "table" AS references_table
FROM pragma_foreign_key_list('orders')
ORDER BY column_name;
Expected output
column_name | references_table
customer_id | customers
product_id | old_products

PostgreSQL doesn't need a rebuild: you can change a type with a USING conversion, set NOT NULL and add constraints. On a big table, adding a CHECK or a foreign key NOT VALID and then running VALIDATE CONSTRAINT separately shortens the locks: new rows are checked at once, and the old ones are checked without blocking writes. The code was checked in PGlite (PostgreSQL 18):

SQL
BEGIN;
ALTER TABLE customers
  ALTER COLUMN joined_on TYPE date USING joined_on::date,
  ALTER COLUMN city SET NOT NULL;
ALTER TABLE products
  ADD CONSTRAINT price_positive CHECK (price >= 0) NOT VALID;
ALTER TABLE products VALIDATE CONSTRAINT price_positive;
COMMIT;

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'customers'
ORDER BY ordinal_position;

Expected output

 column_name | data_type | is_nullable
-------------+-----------+-------------
 id          | integer   | NO
 name        | text      | NO
 city        | text      | NO
 joined_on   | date      | YES
(4 rows)

In PostgreSQL DDL is transactional too: the half-done changes of a failed migration are fully undone by ROLLBACK. In MySQL every ALTER TABLE commits implicitly, so a migration that breaks halfway can leave the database in an in-between state.

SQL
BEGIN;
ALTER TABLE customers ADD COLUMN phone text;
DROP TABLE products;
ROLLBACK;

SELECT count(*) AS phone_columns,
       to_regclass('products') IS NOT NULL AS products_exists
FROM information_schema.columns
WHERE table_name = 'customers' AND column_name = 'phone';

Expected output

 phone_columns | products_exists
---------------+-----------------
             0 | t
(1 row)
The column is gone and the dropped table is back.

Migration files: up, down and tools

A reversible migration has two parts: up (forward) and down (back). For example, the golang-migrate tool expects pairs of files:

SQL
-- 0004_customer_phone.up.sql
ALTER TABLE customers ADD COLUMN phone TEXT;
CREATE INDEX idx_customers_phone ON customers (phone);

-- 0004_customer_phone.down.sql
DROP INDEX idx_customers_phone;
ALTER TABLE customers DROP COLUMN phone;
The down file undoes the up file in reverse order.

But down doesn't always mean “back”: the down of a DROP COLUMN returns the column, not the data. That is why many teams work forward-only: they fix a mistake with a new migration and take a backup before a risky change. In Python, Alembic expresses the same idea with functions:

Python
"""add phone to customers

Revision ID: 3a1f9c2b7d10
Revises: 9e4d2a6c1b55
"""
from alembic import op
import sqlalchemy as sa

revision = "3a1f9c2b7d10"
down_revision = "9e4d2a6c1b55"


def upgrade():
    op.add_column("customers", sa.Column("phone", sa.String(20), nullable=True))


def downgrade():
    op.drop_column("customers", "phone")
An Alembic file: down_revision points to the previous migration and forms a chain.
ToolEcosystemMigrationHistory table
FlywayJava, any language (CLI)V2__add_phone.sqlflyway_schema_history
LiquibaseJava, any language (CLI)changelog: XML/YAML/SQL changeSetsDATABASECHANGELOG
AlembicPython, SQLAlchemya .py file with upgrade() / downgrade()alembic_version
Prisma MigrateNode.js, TypeScriptmigrations/<time>_name/migration.sql_prisma_migrations
EF Core.NET, C#a C# class with Up() / Down()__EFMigrationsHistory
Terminal
alembic revision -m "add phone to customers"   # new migration file
alembic upgrade head                           # apply all pending migrations
alembic downgrade -1                           # undo the last one
flyway migrate                                 # apply V*__*.sql files in order
npx prisma migrate dev --name add_phone        # create + apply (development)
npx prisma migrate deploy                      # apply pending (production)
dotnet ef migrations add AddPhone              # EF Core: create
dotnet ef database update                      # EF Core: apply
Every tool has the same two moves: create a migration and apply the pending ones.

Changes without downtime: expand–contract

During a deployment the old and new versions of the application run against the same database for a while. Rename a column in one step and the old version breaks at once. Expand–contract splits the change into several safe releases. We split customers.name into first_name and last_name:

  1. Expand

    Add the new columns; the old column stays and the old code keeps working.

  2. Backfill and sync

    Fill old rows in batches; for new writes the app writes both places (or a temporary trigger does).

  3. Switch reads

    The new version reads only the new columns.

  4. Contract

    When no old code is left, drop the trigger and the old column.

SQL
ALTER TABLE customers ADD COLUMN first_name TEXT;
ALTER TABLE customers ADD COLUMN last_name TEXT;
UPDATE customers
SET first_name = substr(name, 1, instr(name, ' ') - 1),
    last_name  = substr(name, instr(name, ' ') + 1)
WHERE first_name IS NULL;

-- old code still writes only "name": a temporary trigger fills the new columns
CREATE TRIGGER customers_sync AFTER INSERT ON customers
WHEN NEW.first_name IS NULL
BEGIN
  UPDATE customers
  SET first_name = substr(NEW.name, 1, instr(NEW.name, ' ') - 1),
      last_name  = substr(NEW.name, instr(NEW.name, ' ') + 1)
  WHERE id = NEW.id;
END;

INSERT INTO customers (name, city, country, joined_on)
VALUES ('Aygün Qasımova', 'Quba', 'Azerbaijan', '2025-09-01');

SELECT id, name, first_name, last_name
FROM customers WHERE id IN (1, 3, 8) ORDER BY id;
Expected output
id | name | first_name | last_name
1 | Anar Mustafayev | Anar | Mustafayev
3 | Emre Yılmaz | Emre | Yılmaz
8 | Aygün Qasımova | Aygün | Qasımova
Expand: old rows are filled, and the new customer (8) written by old code is split too.
SQL
ALTER TABLE customers ADD COLUMN first_name TEXT;
ALTER TABLE customers ADD COLUMN last_name TEXT;
UPDATE customers
SET first_name = substr(name, 1, instr(name, ' ') - 1),
    last_name  = substr(name, instr(name, ' ') + 1);

-- a later release: nothing reads "name" any more
ALTER TABLE customers DROP COLUMN name;

SELECT group_concat(name, ', ') AS columns FROM pragma_table_info('customers');
Expected output
columns
id, city, country, joined_on, first_name, last_name
Contract: the old column is dropped only after all code has moved to the new columns.

Exercise

Write migration V2: in one transaction, add a phone TEXT column to customers, rename joined_on to signup_date and record (2, 'customer_phone_and_signup_date') in schema_migrations. Then run the check query from the starter code.

Exercise · SQL
CREATE TABLE schema_migrations (version INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO schema_migrations VALUES (1, 'create_shop_tables');

-- V2 here (BEGIN ... COMMIT)

SELECT (SELECT MAX(version) FROM schema_migrations) AS schema_version,
       group_concat(name, ', ') AS columns
FROM pragma_table_info('customers');
Expected output
schema_version | columns
2 | id, name, city, country, signup_date, phone

Exercise

Make courses.credits NOT NULL with CHECK (credits BETWEEN 1 AND 10). SQLite can't do this with ALTER TABLE — apply the 12-step rebuild (enrollments refers to the courses!). Then run the check query from the starter code.

Exercise · SQL
-- rebuild courses here (PRAGMA, BEGIN, CREATE new_courses, copy, DROP, RENAME, COMMIT)

SELECT COUNT(*) AS courses,
       (SELECT "notnull" FROM pragma_table_info('courses')
        WHERE name = 'credits') AS credits_not_null
FROM courses;
Expected output
courses | credits_not_null
7 | 1

Remember

  • A migration is a versioned schema change kept in Git; applied ones are recorded in a version table.
  • SQLite's ALTER TABLE only renames, adds and drops columns — with conditions; everything else is the 12-step rebuild.
  • In a rebuild you create a new table and rename it; renaming the old table corrupts the references.
  • DDL is transactional in PostgreSQL and SQLite; MySQL commits implicitly.
  • A down doesn't always bring data back; many teams work forward-only and take backups.
  • Expand–contract: add → backfill and sync → switch reads → drop the old.
Spotted a mistake or something inaccurate in this lesson?

Check yourself

12 questions. Every correct answer earns XP.

Question 1 / 12

Question 1 / 12. What is a migration?