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:
Read the files
Sort the migrations in the folder by version: V1, V2, V3…
Ask the database
Read the applied versions from the version table.
Run the pending ones
Run every new migration in its own transaction; stop on an error.
Record it
Write the version of a successful migration into the table in the same transaction.
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
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):
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:
ALTER TABLE customers ADD COLUMN vip INTEGER NOT NULL;Expected output
Error: Cannot add a NOT NULL column with default value NULL
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 INDEX idx_customers_city;| Change | SQLite | PostgreSQL | MySQL 8 |
|---|---|---|---|
| Add a column | yes, with conditions | yes | yes |
| Rename a column | RENAME COLUMN (3.25+) | RENAME COLUMN | RENAME COLUMN |
| Drop a column | 3.35+, with conditions | yes | yes |
| Change the type | no → rebuild | ALTER COLUMN … TYPE … USING | MODIFY COLUMN |
NOT NULL / CHECK on an existing column | no → rebuild | SET NOT NULL, ADD CONSTRAINT | MODIFY, ADD CONSTRAINT |
DDL inside a transaction (ROLLBACK undoes it) | yes | yes | no — 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. Switch foreign keys off
PRAGMA foreign_keys = OFF— before the transaction, because inside one this pragma does nothing.2. Begin a transaction
BEGIN— everything happens completely or not at all.3. Note the dependants
SELECT sql FROM sqlite_schema WHERE tbl_name = 'X'— indexes, triggers, views.4–5. New table and copy
CREATE TABLE new_X (...)in the desired shape, thenINSERT INTO new_X SELECT ... FROM X.6–7. Swap
DROP TABLE X, thenALTER TABLE new_X RENAME TO X.8–9. Restore the dependants
Recreate indexes, triggers and views (fix a view if the change affects it).
10. Check
PRAGMA foreign_key_check— lists broken references.11–12. Finish
COMMIT, thenPRAGMA 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:
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
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
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.
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):
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.
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)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:
-- 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;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:
"""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")down_revision points to the previous migration and forms a chain.| Tool | Ecosystem | Migration | History table |
|---|---|---|---|
| Flyway | Java, any language (CLI) | V2__add_phone.sql | flyway_schema_history |
| Liquibase | Java, any language (CLI) | changelog: XML/YAML/SQL changeSets | DATABASECHANGELOG |
| Alembic | Python, SQLAlchemy | a .py file with upgrade() / downgrade() | alembic_version |
| Prisma Migrate | Node.js, TypeScript | migrations/<time>_name/migration.sql | _prisma_migrations |
| EF Core | .NET, C# | a C# class with Up() / Down() | __EFMigrationsHistory |
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: applyChanges 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:
Expand
Add the new columns; the old column stays and the old code keeps working.
Backfill and sync
Fill old rows in batches; for new writes the app writes both places (or a temporary trigger does).
Switch reads
The new version reads only the new columns.
Contract
When no old code is left, drop the trigger and the old column.
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
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
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.
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.
-- 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 TABLEonly 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.