Исполняемые доки SQLite: Миграции

Q: Как версионировать схему в SQLite?

В SQLite на уровне базы есть встроенный 32-битный счётчик userversion, доступный через PRAGMA userversion. При старте приложения читаем его, сравниваем с номером последней миграции, известной коду, и докатываем недостающие по порядку. Отдельной таблицы не требуется, хотя многие всё же заводят её — для истории применённых миграций.

Q: Можно ли откатить миграцию в SQLite?

Заворачивайте каждую миграцию в BEGIN; ... COMMIT;. Если внутри что-то падает, ROLLBACK откатывает шаг целиком — и DDL, и данные, потому что в SQLite изменения схемы тоже транзакционные. А вот чтобы откатить уже закоммиченную миграцию, нужен отдельный down-скрипт, который вы написали сами — SQLite сам его не сгенерирует.

Q: Почему ALTER TABLE в SQLite такой ограниченный?

SQLite поддерживает ALTER TABLE ADD COLUMN, RENAME TABLE, RENAME COLUMN и DROP COLUMN, но не умеет произвольных правок — например, поменять тип колонки или ограничения. Обходной путь — рецепт из 12 шагов: создать новую таблицу нужной формы, выполнить INSERT INTO newtable SELECT ... FROM oldtable, удалить старую и переименовать новую.

Схемы меняются. Будь к этому готов.

Первая версия схемы никогда не остаётся последней. Появляются новые столбцы, таблицы дробятся, индексы переосмысливаются. Вопрос не в том, изменится ли схема, а в том, насколько чисто эти изменения докатятся до каждого ноутбука, сервера и пользовательского устройства, где уже лежит более старая копия базы.

Ровно для этого и нужны миграции sqlite: последовательность небольших упорядоченных скриптов, которые переводят базу с версии N на версию N+1. Запускаешь по порядку — и любая база догоняет текущее состояние. Забьёшь на дисциплину — получишь классические баги в духе «у меня всё работает», на отлов которых уйдёт половина дня.

В SQLite для этого есть ровно один встроенный механизм — PRAGMA user_version. Это 32-битное целое число, которое база хранит за тебя, и сам SQLite его никак не использует. Что оно означает — решаешь ты.

PRAGMA user_version;
PRAGMA user_version = 1;
PRAGMA user_version;

Свежесозданная база стартует с 0. Выставляйте сюда номер последней применённой миграции, а при запуске приложения читайте это значение, чтобы понять, на каком шаге вы находитесь.

Минимальный цикл миграций SQLite

Идея простая: каждая миграция — это пронумерованный SQL-скрипт. Приложение читает текущий user_version, по порядку прогоняет все скрипты с бо́льшим номером и после каждого обновляет user_version.

Вот первая миграция — создаём начальную схему:

BEGIN;

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);

PRAGMA user_version = 1;

COMMIT;

Здесь стоит обратить внимание на две вещи. Во-первых, всё обёрнуто в BEGIN; ... COMMIT;, то есть миграция атомарна: если CREATE TABLE упадёт, user_version не увеличится, и можно будет спокойно поправить скрипт и запустить его заново. Во-вторых, PRAGMA user_version = 1 идёт последней инструкцией перед коммитом — версия переключается только тогда, когда всё остальное отработало успешно.

Теперь представим, что нужно добавить колонку created_at. Это уже миграция 2:

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

BEGIN;

ALTER TABLE users ADD COLUMN created_at TEXT
    NOT NULL DEFAULT (datetime('now'));

PRAGMA user_version = 2;

COMMIT;

SELECT sql FROM sqlite_master WHERE name = 'users';

База с версией 0 прогонит оба скрипта. База на версии 1 — только второй. А база на версии 2 не получит ничего. Порядок здесь — это контракт.

Что умеет и чего не умеет ALTER TABLE в SQLite

ALTER TABLE в SQLite намеренно сделан скупым. Поддерживаются только четыре операции:

ADD COLUMN — добавить новый столбец, при желании со значением по умолчанию.
DROP COLUMN — удалить столбец (начиная с 3.35).
RENAME COLUMN — переименовать столбец (начиная с 3.25).
RENAME TO — переименовать саму таблицу.

И всё. Поменять тип столбца, навесить или снять NOT NULL, поправить ограничение CHECK, прицепить FOREIGN KEY к уже существующему столбцу — ничего из этого через ALTER TABLE не сделаешь.

-- Не поддерживается:
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);
ALTER TABLE users ADD CONSTRAINT users_email_check CHECK (email LIKE '%@%');

Когда SQLite не умеет внести изменение напрямую, официальный рецепт — «пересобрать таблицу». Способ многословный, зато работает железобетонно.

Пересборка таблицы для серьёзных изменений схемы

Схема такая: создаём новую таблицу нужной формы, копируем туда данные, удаляем старую, переименовываем новую на её место. Всё это — внутри одной транзакции.

BEGIN;

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);
INSERT INTO users (email, name) VALUES ('a@x.com', 'Ada'), ('b@x.com', 'Boris');

-- Теперь нам нужно ограничение CHECK на email. Перестраиваем:
CREATE TABLE users_new (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE CHECK (email LIKE '%@%'),
    name  TEXT NOT NULL
);

INSERT INTO users_new (id, email, name)
    SELECT id, email, name FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

SELECT * FROM users;

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

Важный момент: если на перестраиваемую таблицу ссылаются внешние ключи, перед миграцией выполните PRAGMA foreign_keys = OFF, а после — PRAGMA foreign_keys = ON. Иначе DROP TABLE посреди процесса нарушит ссылочную целостность.

Запускаем миграции из приложения

Учёт версий настолько простой, что его несложно реализовать самому. Вот пример на Python со стандартной библиотекой:

-- Концептуально ваше приложение делает это при запуске:
--
--   current = SELECT user_version FROM PRAGMA;
--   for i in range(current + 1, len(MIGRATIONS) + 1):
--       выполнить MIGRATIONS[i] в транзакции
--       PRAGMA user_version = i;
--
-- SQL, выполняемый на каждом шаге, выглядит как миграции выше.

PRAGMA user_version;

Ключевые инварианты:

Миграции нумеруются последовательно начиная с 1. Без пропусков и перестановок.
Каждая миграция оборачивается в транзакцию вместе с PRAGMA user_version = N.
Если миграция уже закоммичена и уехала в продакшен — её больше не трогаем. Любые правки оформляем новой миграцией.

Именно последнее правило команды нарушают чаще всего. Стоит вам отредактировать миграцию 3 после того, как у коллеги она уже применилась к его базе, — и его база навсегда тихо разъедется с вашей.

Ведём журнал миграций

PRAGMA user_version показывает, на какой версии находится база. Но он ничего не говорит ни о том, когда выполнялся каждый шаг, ни о том, что именно он сделал. Эту проблему решает небольшая служебная таблица:

CREATE TABLE schema_migrations (
    version    INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    applied_at TEXT NOT NULL DEFAULT (datetime('now'))
);

BEGIN;

CREATE TABLE posts (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    body   TEXT NOT NULL
);

INSERT INTO schema_migrations (version, name)
    VALUES (1, 'create_posts');
PRAGMA user_version = 1;

COMMIT;

SELECT * FROM schema_migrations;

Теперь у вас по одной строке на каждую миграцию — с именем и таймстампом. Жутко удобно, когда отлаживаешь вопрос «почему в базе есть колонка, о которой код ни сном ни духом?».

Источником истины для цикла по-прежнему остаётся PRAGMA user_version, а таблица — это уже для людей.

Откат миграции SQLite: что транзакции дают, а что нет

DDL в SQLite транзакционный. Допустим, миграция 5 создаёт таблицу, перекачивает в неё данные и поднимает user_version — и где-то на середине копирования всё ломается. ROLLBACK откатит всё подчистую, включая CREATE TABLE. База останется ровно в том виде, в каком была до BEGIN.

BEGIN;

CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL);
INSERT INTO accounts (id, balance) VALUES (1, 100);

-- Представим, что здесь что-то пошло не так:
ROLLBACK;

-- Таблица не существует:
SELECT name FROM sqlite_master WHERE type='table' AND name='accounts';

Это всё про провалившиеся миграции. Но бывает и другая ситуация: миграция применилась успешно, а вы потом передумали. На этот случай пишут отдельную down-миграцию — скрипт, который откатывает изменения. В SQLite нет никакого автоматического обратного действия. Если миграция 7 добавила колонку, то её down-версия эту колонку удаляет. Если миграция 7 удалила колонку, down-версия данные уже не вернёт — максимум, она пересоздаст пустую колонку.

На практике в небольших проектах down-миграции часто вообще не пишут, а в качестве «отмены» полагаются на бэкапы. Это вполне рабочий подход — при условии, что бэкапы вы реально делаете.

Несколько привычек, которые избавят от боли в будущем

Одна миграция — одно логическое изменение. Миграция, которая добавляет три не связанных между собой колонки, и ревьюится тяжелее, и откатывается сложнее, чем три отдельные миграции.
Прогоняйте миграции на копии прода. Изменения схемы на больших таблицах могут идти долго, и узнавать об этом в продакшене — удовольствие сомнительное.
Никогда не правьте уже выкаченную миграцию. Добавьте новую.
Сначала бэкап. Быстрый .backup в CLI или просто копия файла при закрытой базе — это дешёвая страховка перед любой нетривиальной миграцией.
Не забывайте про PRAGMA foreign_keys. На время пересборки таблицы выключайте, после — обратно включайте.

Для проектов покрупнее имеет смысл взять специализированный инструмент — Alembic поверх SQLAlchemy, golang-migrate, Knex, Flyway. Они берут на себя порядок применения, одновременный запуск с нескольких инстансов и командные договорённости, которые иначе пришлось бы изобретать самим. Принципы те же, что в цикле выше, — инструмент просто убирает рутину.

Дальше: режим WAL и конкурентность

Миграции обычно выполняются, когда приложение остановлено или удерживает эксклюзивную блокировку. А вот в обычном режиме база обслуживает чтения и записи сразу из нескольких соединений — и дефолтный режим журнала в SQLite подходит для этого не всегда. На следующей странице разберём режим WAL: что он меняет и когда его стоит включать.

Часто задаваемые вопросы

Как версионировать схему в SQLite?

В SQLite на уровне базы есть встроенный 32-битный счётчик user_version, доступный через PRAGMA user_version. При старте приложения читаем его, сравниваем с номером последней миграции, известной коду, и докатываем недостающие по порядку. Отдельной таблицы не требуется, хотя многие всё же заводят её — для истории применённых миграций.

Можно ли откатить миграцию в SQLite?

Заворачивайте каждую миграцию в BEGIN; ... COMMIT;. Если внутри что-то падает, ROLLBACK откатывает шаг целиком — и DDL, и данные, потому что в SQLite изменения схемы тоже транзакционные. А вот чтобы откатить уже закоммиченную миграцию, нужен отдельный down-скрипт, который вы написали сами — SQLite сам его не сгенерирует.

Почему ALTER TABLE в SQLite такой ограниченный?

SQLite поддерживает ALTER TABLE ADD COLUMN, RENAME TABLE, RENAME COLUMN и DROP COLUMN, но не умеет произвольных правок — например, поменять тип колонки или ограничения. Обходной путь — рецепт из 12 шагов: создать новую таблицу нужной формы, выполнить INSERT INTO new_table SELECT ... FROM old_table, удалить старую и переименовать новую.

Брать готовый инструмент для миграций или писать свой?

Для небольшого приложения хватит самописного цикла по пронумерованным .sql-файлам, который смотрит на PRAGMA user_version, — это строк 30 кода, и работает нормально. На крупных проектах лучше взять готовое: Alembic (Python), golang-migrate (Go) или Knex (Node) — они уже умеют сортировку, блокировки и командные сценарии, которые иначе пришлось бы писать самому.

Связанные концепции

Миграции в SQLite: версии схемы через user_version