В команде три программиста. Один на своём ноутбуке добавляет в customers столбец phone, другой переименовывает столбец, третий что-то правит руками на сервере. Через месяц тестовая и рабочая базы различаются, и никто не знает, как так вышло. Код версионируется в Git — схема тоже должна. Именно это делают миграции: каждое изменение — пронумерованный SQL-файл, и каждая база проходит их в одном и том же порядке. В уроке «CREATE TABLE: типы данных и ограничения» мы видели ALTER TABLE ... ADD COLUMN; этот урок показывает его пределы, что делать за ними и как управлять изменениями в команде.
Миграции и таблица версий
МиграцияОпределение
Скрипт с номером (версией), переводящий схему из одной версии в следующую и хранящийся в Git вместе с кодом. Применённую миграцию больше не редактируют.
База сама помнит, какие миграции уже применены, — в таблице версий. Инструмент миграций при каждом запуске выполняет один и тот же алгоритм:
Прочитай файлы
Отсортируй миграции в папке по версии: V1, V2, V3…
Спроси базу
Прочитай применённые версии из таблицы версий.
Выполни ожидающие
Выполни каждую новую миграцию в своей транзакции; при ошибке остановись.
Запиши
Запиши версию успешной миграции в таблицу в той же транзакции.
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;Ожидаемый результат
version | name 1 | create_shop_tables 2 | add_customer_phone 3 | add_orders_customer_index
Чтобы читать дальше, зарегистрируйся бесплатно
Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.
ALTER TABLE в SQLite: что можно и что нельзя
В SQLite ALTER TABLE умеет всего четыре вещи: переименовать таблицу (RENAME TO), добавить столбец (ADD COLUMN), переименовать столбец (RENAME COLUMN, с 3.25) и удалить столбец (DROP COLUMN, с 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');Ожидаемый результат
columns id, name, city, country, signup_date
pragma_table_info возвращает столбцы таблицы построчно.У каждой операции есть условия. Добавляемый столбец не может быть PRIMARY KEY или UNIQUE; столбцу NOT NULL нужно значение по умолчанию, отличное от NULL; значение по умолчанию должно быть константой (CURRENT_TIMESTAMP нельзя). А удалить столбец нельзя, если он первичный ключ, UNIQUE, проиндексирован, входит во внешний ключ или используется в представлении, триггере или CHECK:
ALTER TABLE customers ADD COLUMN vip INTEGER NOT NULL;Ожидаемый результат
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;Ожидаемый результат
Error: error in index idx_customers_city after drop column: no such column: city
DROP INDEX idx_customers_city;| Изменение | SQLite | PostgreSQL | MySQL 8 |
|---|---|---|---|
| Добавить столбец | да, с условиями | да | да |
| Переименовать столбец | RENAME COLUMN (3.25+) | RENAME COLUMN | RENAME COLUMN |
| Удалить столбец | 3.35+, с условиями | да | да |
| Изменить тип | нет → перестройка | ALTER COLUMN … TYPE … USING | MODIFY COLUMN |
NOT NULL / CHECK для существующего столбца | нет → перестройка | SET NOT NULL, ADD CONSTRAINT | MODIFY, ADD CONSTRAINT |
DDL в транзакции (ROLLBACK отменяет) | да | да | нет — DDL делает неявный COMMIT |
12-шаговая перестройка в SQLite и путь PostgreSQL
Для всего остального — смены типа, добавления ограничения, изменения первичного ключа — документация SQLite предписывает перестроить таблицу: создать таблицу в новом виде, скопировать данные, удалить старую и переименовать новую. В документации это 12 шагов:
1. Выключи внешние ключи
PRAGMA foreign_keys = OFF— до транзакции: внутри неё эта прагма ничего не делает.2. Начни транзакцию
BEGIN— всё произойдёт полностью или не произойдёт вовсе.3. Запомни зависимые объекты
SELECT sql FROM sqlite_schema WHERE tbl_name = 'X'— индексы, триггеры, представления.4–5. Новая таблица и копирование
CREATE TABLE new_X (...)в нужном виде, затемINSERT INTO new_X SELECT ... FROM X.6–7. Подмени
DROP TABLE X, затемALTER TABLE new_X RENAME TO X.8–9. Восстанови зависимые объекты
Пересоздай индексы, триггеры и представления (исправь представление, если изменение его затрагивает).
10. Проверь
PRAGMA foreign_key_check— показывает нарушенные ссылки.11–12. Заверши
COMMIT, затемPRAGMA foreign_keys = ON.
Добавляем правила в таблицу товаров: название уникально, цена не может быть пустой или отрицательной, остаток по умолчанию 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;Ожидаемый результат
rows_copied | orders_still_linked 10 | 12
INSERT INTO products (name, price) VALUES ('Broken', -5) даёт ошибку CHECK constraint failed: price >= 0.Почему не переименовать старую таблицу?
Задача
Путь, который кажется «естественнее»: ALTER TABLE products RENAME TO old_products, затем создать новую products, скопировать и удалить old_products. Что здесь не так?
Показать решениеСкрыть решение
Решение
2) Поэтому
orders.product_id становится REFERENCES "old_products"(id) (пример ниже).3) Когда
old_products удалят, заказы будут ссылаться на несуществующую таблицу — связь разорвана.4) Порядок из документации (создать новую таблицу и переименовать её) обходит эту ловушку.
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;Ожидаемый результат
column_name | references_table customer_id | customers product_id | old_products
PostgreSQL перестройка не нужна: можно сменить тип с преобразованием USING, поставить NOT NULL и добавить ограничения. На большой таблице CHECK или внешний ключ добавляют как NOT VALID, а потом отдельно выполняют VALIDATE CONSTRAINT — так блокировки короче: новые строки проверяются сразу, а старые — без блокировки записи. Код проверен в 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;Ожидаемый результат
column_name | data_type | is_nullable -------------+-----------+------------- id | integer | NO name | text | NO city | text | NO joined_on | date | YES (4 rows)
В PostgreSQL DDL тоже транзакционный: незавершённые изменения упавшей миграции полностью отменяются ROLLBACK. В MySQL каждый ALTER TABLE делает неявный COMMIT, поэтому миграция, оборвавшаяся на середине, может оставить базу в промежуточном состоянии.
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';Ожидаемый результат
phone_columns | products_exists
---------------+-----------------
0 | t
(1 row)Файлы миграций: up, down и инструменты
У обратимой миграции две части: up (вперёд) и down (назад). Например, инструмент golang-migrate ожидает пары файлов:
-- 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;Но down не всегда означает «назад»: down для DROP COLUMN вернёт столбец, но не данные. Поэтому многие команды работают только вперёд (forward-only): исправляют ошибку новой миграцией, а перед рискованным изменением делают резервную копию. В Python Alembic выражает ту же идею функциями:
"""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 указывает на предыдущую миграцию и образует цепочку.| Инструмент | Экосистема | Миграция | Таблица истории |
|---|---|---|---|
| Flyway | Java, любой язык (CLI) | V2__add_phone.sql | flyway_schema_history |
| Liquibase | Java, любой язык (CLI) | changelog: changeSet в XML/YAML/SQL | DATABASECHANGELOG |
| Alembic | Python, SQLAlchemy | файл .py с upgrade() / downgrade() | alembic_version |
| Prisma Migrate | Node.js, TypeScript | migrations/<time>_name/migration.sql | _prisma_migrations |
| EF Core | .NET, C# | класс C# с 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: applyИзменения без простоя: expand–contract
Во время обновления старая и новая версии приложения какое-то время работают с одной базой. Переименуешь столбец за один шаг — старая версия сразу сломается. Expand–contract (расширить–сузить) разбивает изменение на несколько безопасных релизов. Делим customers.name на first_name и last_name:
Расширить (expand)
Добавь новые столбцы; старый остаётся, старый код работает.
Заполни и синхронизируй
Заполни старые строки порциями; новые записи приложение пишет в оба места (или временный триггер).
Переключи чтение
Новая версия читает только новые столбцы.
Сузить (contract)
Когда старого кода не осталось, удали триггер и старый столбец.
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;Ожидаемый результат
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');Ожидаемый результат
columns id, city, country, joined_on, first_name, last_name
Задание
Напиши миграцию V2: в одной транзакции добавь в customers столбец phone TEXT, переименуй joined_on в signup_date и запиши (2, 'customer_phone_and_signup_date') в schema_migrations. Затем выполни проверочный запрос из стартового кода.
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');Ожидаемый результат
schema_version | columns 2 | id, name, city, country, signup_date, phone
Задание
Сделай courses.credits NOT NULL с CHECK (credits BETWEEN 1 AND 10). SQLite не умеет этого через ALTER TABLE — примени 12-шаговую перестройку (enrollments ссылается на курсы!). Затем выполни проверочный запрос из стартового кода.
-- 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;Ожидаемый результат
courses | credits_not_null 7 | 1
Запомни
- Миграция — версионированное изменение схемы в Git; применённые записываются в таблицу версий.
ALTER TABLEв SQLite только переименовывает, добавляет и удаляет столбцы — с условиями; всё остальное — 12-шаговая перестройка.- При перестройке создают новую таблицу и переименовывают её; переименование старой портит ссылки.
- В PostgreSQL и SQLite DDL транзакционный, в MySQL — неявный
COMMIT. - Down не всегда возвращает данные; многие команды работают forward-only и делают резервные копии.
- Expand–contract: добавить → заполнить и синхронизировать → переключить чтение → удалить старое.