Изменение схемы: ALTER TABLE и миграции

Управление изменениями схемы через версионированные файлы: `ALTER TABLE` в SQLite и PostgreSQL, 12-шаговая перестройка таблицы в SQLite, таблица версий, миграции up/down, Flyway, Liquibase, Alembic, Prisma Migrate и приём expand–contract без простоя.

  • Продвинутый
  • 22 мин
  • 12 вопросов
  • Урок 14 из 28

В этом уроке ты узнаешь

  • Объяснять, зачем нужны миграции и таблица версий
  • Знать возможности и пределы ALTER TABLE в SQLite и применять 12-шаговую перестройку для остального
  • Писать файлы миграций up/down и узнавать инструменты (Flyway, Liquibase, Alembic, Prisma Migrate)
  • Планировать изменение без простоя по схеме expand–contract

В команде три программиста. Один на своём ноутбуке добавляет в customers столбец phone, другой переименовывает столбец, третий что-то правит руками на сервере. Через месяц тестовая и рабочая базы различаются, и никто не знает, как так вышло. Код версионируется в Git — схема тоже должна. Именно это делают миграции: каждое изменение — пронумерованный SQL-файл, и каждая база проходит их в одном и том же порядке. В уроке «CREATE TABLE: типы данных и ограничения» мы видели ALTER TABLE ... ADD COLUMN; этот урок показывает его пределы, что делать за ними и как управлять изменениями в команде.

Миграции и таблица версий

МиграцияОпределение

Скрипт с номером (версией), переводящий схему из одной версии в следующую и хранящийся в Git вместе с кодом. Применённую миграцию больше не редактируют.

База сама помнит, какие миграции уже применены, — в таблице версий. Инструмент миграций при каждом запуске выполняет один и тот же алгоритм:

  1. Прочитай файлы

    Отсортируй миграции в папке по версии: V1, V2, V3…

  2. Спроси базу

    Прочитай применённые версии из таблицы версий.

  3. Выполни ожидающие

    Выполни каждую новую миграцию в своей транзакции; при ошибке остановись.

  4. Запиши

    Запиши версию успешной миграции в таблицу в той же транзакции.

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;
Ожидаемый результат
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):

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');
Ожидаемый результат
columns
id, name, city, country, signup_date
pragma_table_info возвращает столбцы таблицы построчно.

У каждой операции есть условия. Добавляемый столбец не может быть PRIMARY KEY или UNIQUE; столбцу NOT NULL нужно значение по умолчанию, отличное от NULL; значение по умолчанию должно быть константой (CURRENT_TIMESTAMP нельзя). А удалить столбец нельзя, если он первичный ключ, UNIQUE, проиндексирован, входит во внешний ключ или используется в представлении, триггере или CHECK:

SQL
ALTER TABLE customers ADD COLUMN vip INTEGER NOT NULL;
Ожидаемый результат
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;
Ожидаемый результат
Error: error in index idx_customers_city after drop column: no such column: city
Сначала нужно удалить индекс: DROP INDEX idx_customers_city;
ИзменениеSQLitePostgreSQLMySQL 8
Добавить столбецда, с условиямидада
Переименовать столбецRENAME COLUMN (3.25+)RENAME COLUMNRENAME COLUMN
Удалить столбец3.35+, с условиямидада
Изменить типнет → перестройкаALTER COLUMN … TYPE … USINGMODIFY COLUMN
NOT NULL / CHECK для существующего столбцанет → перестройкаSET NOT NULL, ADD CONSTRAINTMODIFY, ADD CONSTRAINT
DDL в транзакции (ROLLBACK отменяет)даданет — DDL делает неявный COMMIT

12-шаговая перестройка в SQLite и путь PostgreSQL

Для всего остального — смены типа, добавления ограничения, изменения первичного ключа — документация SQLite предписывает перестроить таблицу: создать таблицу в новом виде, скопировать данные, удалить старую и переименовать новую. В документации это 12 шагов:

  1. 1. Выключи внешние ключи

    PRAGMA foreign_keys = OFF — до транзакции: внутри неё эта прагма ничего не делает.

  2. 2. Начни транзакцию

    BEGIN — всё произойдёт полностью или не произойдёт вовсе.

  3. 3. Запомни зависимые объекты

    SELECT sql FROM sqlite_schema WHERE tbl_name = 'X' — индексы, триггеры, представления.

  4. 4–5. Новая таблица и копирование

    CREATE TABLE new_X (...) в нужном виде, затем INSERT INTO new_X SELECT ... FROM X.

  5. 6–7. Подмени

    DROP TABLE X, затем ALTER TABLE new_X RENAME TO X.

  6. 8–9. Восстанови зависимые объекты

    Пересоздай индексы, триггеры и представления (исправь представление, если изменение его затрагивает).

  7. 10. Проверь

    PRAGMA foreign_key_check — показывает нарушенные ссылки.

  8. 11–12. Заверши

    COMMIT, затем PRAGMA foreign_keys = ON.

Добавляем правила в таблицу товаров: название уникально, цена не может быть пустой или отрицательной, остаток по умолчанию 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;
Ожидаемый результат
rows_copied | orders_still_linked
10 | 12
Скопированы 10 товаров, все 12 заказов по-прежнему ссылаются на свой товар (id сохранены). Теперь INSERT INTO products (name, price) VALUES ('Broken', -5) даёт ошибку CHECK constraint failed: price >= 0.

Почему не переименовать старую таблицу?

Задача

Путь, который кажется «естественнее»: ALTER TABLE products RENAME TO old_products, затем создать новую products, скопировать и удалить old_products. Что здесь не так?

Показать решениеСкрыть решение

Решение

1) Современный SQLite при переименовании таблицы обновляет и ссылки в других таблицах.
2) Поэтому orders.product_id становится REFERENCES "old_products"(id) (пример ниже).
3) Когда old_products удалят, заказы будут ссылаться на несуществующую таблицу — связь разорвана.
4) Порядок из документации (создать новую таблицу и переименовать её) обходит эту ловушку.
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;
Ожидаемый результат
column_name | references_table
customer_id | customers
product_id | old_products

PostgreSQL перестройка не нужна: можно сменить тип с преобразованием USING, поставить NOT NULL и добавить ограничения. На большой таблице CHECK или внешний ключ добавляют как NOT VALID, а потом отдельно выполняют VALIDATE CONSTRAINT — так блокировки короче: новые строки проверяются сразу, а старые — без блокировки записи. Код проверен в 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;

Ожидаемый результат

 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, поэтому миграция, оборвавшаяся на середине, может оставить базу в промежуточном состоянии.

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';

Ожидаемый результат

 phone_columns | products_exists
---------------+-----------------
             0 | t
(1 row)
Столбца нет, удалённая таблица на месте.

Файлы миграций: up, down и инструменты

У обратимой миграции две части: up (вперёд) и down (назад). Например, инструмент golang-migrate ожидает пары файлов:

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;
Файл down отменяет up в обратном порядке.

Но down не всегда означает «назад»: down для DROP COLUMN вернёт столбец, но не данные. Поэтому многие команды работают только вперёд (forward-only): исправляют ошибку новой миграцией, а перед рискованным изменением делают резервную копию. В Python Alembic выражает ту же идею функциями:

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")
Файл Alembic: down_revision указывает на предыдущую миграцию и образует цепочку.
ИнструментЭкосистемаМиграцияТаблица истории
FlywayJava, любой язык (CLI)V2__add_phone.sqlflyway_schema_history
LiquibaseJava, любой язык (CLI)changelog: changeSet в XML/YAML/SQLDATABASECHANGELOG
AlembicPython, SQLAlchemyфайл .py с upgrade() / downgrade()alembic_version
Prisma MigrateNode.js, TypeScriptmigrations/<time>_name/migration.sql_prisma_migrations
EF Core.NET, C#класс C# с 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
В каждом инструменте те же два действия: создать миграцию и применить ожидающие.

Изменения без простоя: expand–contract

Во время обновления старая и новая версии приложения какое-то время работают с одной базой. Переименуешь столбец за один шаг — старая версия сразу сломается. Expand–contract (расширить–сузить) разбивает изменение на несколько безопасных релизов. Делим customers.name на first_name и last_name:

  1. Расширить (expand)

    Добавь новые столбцы; старый остаётся, старый код работает.

  2. Заполни и синхронизируй

    Заполни старые строки порциями; новые записи приложение пишет в оба места (или временный триггер).

  3. Переключи чтение

    Новая версия читает только новые столбцы.

  4. Сузить (contract)

    Когда старого кода не осталось, удали триггер и старый столбец.

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;
Ожидаемый результат
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: старые строки заполнены, и новый клиент (8), записанный старым кодом, тоже разделён.
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');
Ожидаемый результат
columns
id, city, country, joined_on, first_name, last_name
Contract: старый столбец удаляют только после того, как весь код перешёл на новые.

Задание

Напиши миграцию V2: в одной транзакции добавь в customers столбец phone TEXT, переименуй joined_on в signup_date и запиши (2, 'customer_phone_and_signup_date') в schema_migrations. Затем выполни проверочный запрос из стартового кода.

Задание · 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');
Ожидаемый результат
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 ссылается на курсы!). Затем выполни проверочный запрос из стартового кода.

Задание · 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;
Ожидаемый результат
courses | credits_not_null
7 | 1

Запомни

  • Миграция — версионированное изменение схемы в Git; применённые записываются в таблицу версий.
  • ALTER TABLE в SQLite только переименовывает, добавляет и удаляет столбцы — с условиями; всё остальное — 12-шаговая перестройка.
  • При перестройке создают новую таблицу и переименовывают её; переименование старой портит ссылки.
  • В PostgreSQL и SQLite DDL транзакционный, в MySQL — неявный COMMIT.
  • Down не всегда возвращает данные; многие команды работают forward-only и делают резервные копии.
  • Expand–contract: добавить → заполнить и синхронизировать → переключить чтение → удалить старое.
Заметил ошибку или неточность в уроке?

Проверь себя

Вопросов: 12. Каждый правильный ответ приносит XP.

Вопрос 1 / 12

Вопрос 1 / 12. Что такое миграция?