Миграции базы данных без потери данных

У приложения, которое копит данные годами, миграция схемы — самая опасная операция из всех регулярных. Не потому, что SQL сложный, а потому, что ошибка обнаруживается не сразу и не полностью: часть данных превратилась во что-то другое, а вы узнаете об этом через две недели, когда старые копии уже уехали по ротации.

Главное правило, которое стоит за всем остальным: миграция, проверенная на пустой базе, не проверена вообще. Пустая база не содержит строк, нарушающих ваше новое ограничение уникальности. Не содержит NULL там, где вы объявили NOT NULL. Не содержит миллиона строк, на которых перестроение полнотекстового индекса займёт не секунду, а сорок минут — с заблокированной на всё это время записью.

#Что понадобится

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

#Почему нужен журнал, а не «прогнать все файлы»

Самый распространённый самодельный вариант — раннер, который при каждом старте применяет все файлы миграций подряд, полагаясь на IF NOT EXISTS. Он работает ровно до первой миграции, которая делает что-то содержательное.

У нас такой раннер был, и вот чем он оказался плох:

Перестроение полнотекстового индекса выполнялось при каждом запуске. На пустой базе — мгновенно, на рабочей — дорого и с блокировкой.
Миграции с копированием таблицы (создать новую, перелить, переименовать) при повторном прогоне работают не так, как в первый раз, и «идемпотентность через IF NOT EXISTS» их не спасает.
Настройки, отредактированные вручную, затирались значениями из миграции, которая их изначально засеивала.

Замена — журнал применённого. Таблица в самой базе, где на каждую миграцию есть строка: порядковый номер, имя файла, контрольная сумма, статус, время, длительность.

CREATE TABLE IF NOT EXISTS schema_migration (
    migration_order INTEGER NOT NULL,
    name            TEXT PRIMARY KEY,
    checksum        TEXT NOT NULL,
    status          TEXT NOT NULL CHECK (status IN ('applied','failed')),
    started_at      TEXT NOT NULL,
    finished_at     TEXT,
    duration_ms     INTEGER,
    error           TEXT
);

Четыре свойства, ради которых всё это существует:

Файл миграции неизменяем после применения. Контрольная сумма считается после нормализации переводов строк, иначе одна и та же ревизия даст разные суммы на Windows и Linux, и вы получите ложное расхождение на ровном месте.
Тело миграции и строка журнала фиксируются в одной транзакции. Иначе возможно состояние «схема поменялась, а журнал об этом не знает» — самое неприятное из всех.
Расхождение суммы или порядка блокирует запуск. Приложение не должно стартовать на схеме, происхождение которой не подтверждено.
Одновременный старт нескольких процессов выбирает одного мигратора через блокировку записи, остальные ждут и потом проверяют готовый журнал. Без этого два процесса применяют одну миграцию параллельно.

#Регламент из шести шагов

Снять копию рабочей базы корректным способом и убедиться, что она проходит проверку целостности.
Прогнать миграцию на этой копии тем самым коммитом, который поедет в прод. Не «похожим», а тем же.
Проверить результат по чек-листу из следующего раздела.
Замерить длительность. Если миграция идёт минуты, у вас не выкладка, а работы с простоем, и планировать их надо иначе.
Выложить и запустить один процесс, дождаться, пока журнал покажет applied на всех строках, и только потом поднимать остальные.
Проверить второй запуск. Он обязан выполнить ноль тел миграций. У нас проверка журнала на втором старте укладывается в четверть секунды на рабочей машине; ваша цифра будет своей, но порядок такой же — второй запуск почти бесплатный.

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

#Чек-лист проверки на копии реальных данных

Проверяйте не «миграция прошла», а конкретные утверждения о данных. Каждое — запросом.

Количество строк совпадает до и после в каждой затронутой таблице (кроме тех, где изменение объёма — цель миграции).
Новая колонка заполнена там, где обязана. SELECT COUNT(*) FROM t WHERE new_col IS NULL — и осмысленный ответ, а не «ну, нули».
Новое ограничение уникальности не нарушено. Проверьте до миграции: SELECT key, COUNT(*) FROM t GROUP BY key HAVING COUNT(*) > 1. Это самая частая причина падения на реальных данных и одновременно самая невидимая на пустых.
Внешние ключи целы: PRAGMA foreign_key_check; — пустой вывод.
Целостность: PRAGMA integrity_check; — ok.
Индексы на месте: сравните список индексов до и после. Перестроение таблицы уносит индексы, о которых вы забыли.
Приложение стартует и открывает ключевые экраны на этой базе. Схема может быть формально корректной и несовместимой с кодом.
Обратная совместимость на время выкладки. Если старый и новый код какое-то время работают одновременно, миграция обязана быть совместимой с обоими.

Последний пункт — причина, по которой опасные изменения разносят на два выпуска: сначала добавить колонку и начать в неё писать, потом, следующим выпуском, перестать читать старую и только затем её удалить.

#Правила для самих файлов миграций

Никогда не править уже применённый файл. Исправление оформляется новой миграцией. Контрольная сумма для того и нужна, чтобы это правило было не пожеланием, а проверкой.
Порядковый номер уникален и не переиспользуется. Номер — это позиция в истории, а не идентификатор задачи.
Никаких сетевых вызовов и обращений к языковой модели внутри миграции. Миграция обязана быть детерминированной и конечной; вызов внешнего сервиса делает её ни тем, ни другим, а заодно держит блокировку записи всё время ожидания — про то, чем это заканчивается, в разборе WAL и конкурентного доступа.
Заполнение данных — ограниченными порциями и с записью прогресса. Обновление миллиона строк одним оператором внутри транзакции — это долгая блокировка и невозможность понять, где вы находитесь.
Перестроение таблицы документируется вместе с тем, как проверялась перелитая копия и что делать при откате.

#Что делать, если миграция упала

Тело миграции уже откачено — оно применялось в транзакции. Осталась строка со статусом failed, и она намеренно блокирует запуск: приложение не должно работать на схеме в неопределённом состоянии.

Порядок:

Приложение не поднимать.
Посмотреть, что именно случилось: SELECT name, status, error FROM schema_migration WHERE status='failed';
Проверить целостность базы.
Снять ещё одну копию — прямо сейчас, в этом состоянии.
Починить миграцию (если она нигде не применялась) или восстановить файл байт в байт (если проблема в испорченной рабочей копии кода).
В отдельном обслуживающем окне удалить только проверенную упавшую строку и запустить один процесс.

Чего делать нельзя: помечать упавшую строку как применённую. Тело откачено, схема не изменена, и такая пометка означает «мы соврали журналу» — следующая миграция, рассчитывающая на результат предыдущей, упадёт уже непонятно почему.

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

#Проверка результата

-- 1. все строки применены, ни одной failed
SELECT status, COUNT(*) FROM schema_migration GROUP BY status;

-- 2. последняя применённая соответствует выпуску
SELECT migration_order, name, applied_at, duration_ms
FROM schema_migration ORDER BY migration_order DESC LIMIT 5;

-- 3. целостность и внешние ключи
PRAGMA integrity_check;
PRAGMA foreign_key_check;

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

#Частые вопросы

#Нужен ли готовый инструмент миграций или хватит своего?

Готовый инструмент даёт журнал, порядок и откат «из коробки» и почти всегда предпочтителен. Свой оправдан, когда нужны специфические вещи — например, опциональные объекты, зависящие от наличия расширения базы, которые нельзя считать обязательными. Но собственный раннер обязан иметь всё то же самое: журнал, контрольные суммы, транзакционность и блокировку запуска при расхождении.

#Как перевести существующую базу без журнала на журнал?

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

#Можно ли применять миграции на работающем приложении?

Технически да, практически — нежелательно, потому что миграция берёт блокировку записи, а приложение в это время пытается писать. Для короткой миграции это доли секунды и никто не замечает; для перестроения таблицы это простой, который лучше объявить.

#Что делать, если миграция идёт слишком долго?

Разбить на порции и вынести заполнение данных из схемной части: сначала быстрая миграция структуры, потом фоновое дозаполнение с записью прогресса. Так вы получите возможность прервать процесс и продолжить, а не начинать заново.

#Как быть с миграциями при переезде на другую машину?

Переезжает база, а не схема: на новой машине должна оказаться та же версия приложения, которая работала на старой, и уже потом обновление. Порядок с приёмкой — в гайде про перенос экземпляра на другой диск или машину.

#Это всё не слишком сложно для личного проекта?

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