Проблемы контроля версий баз данных и миграций между версиями уже не раз поднимались как на Хабре (1, 2, 3 и др.), так и в Интернете (преимущественно, англоязычном).В первом разделе этой статьи я рассматриваю основные проблемы, которые возникают в командах программистов при внесении любых изменений в структуру базы данных. Во втором разделе я попытался выделить основные общие подходы к тому, в каком виде изменения структуры базы данных можно хранить и поддерживать в процессе разработки.
Терминология
База данных — совокупность всех объектов БД (таблиц, процедур, триггеров и т.д.), статических данных (неизменяемых данных, хранящихся в lookup-таблицах) и пользовательских данных (которые изменяются в процессе работы с приложением).Структура базы данных — совокупность всех объектов БД и статических данных. Пользовательские данные в понятие структуры БД не входят.
Версия базы данных — определенное состояние структуры базы данных. Обычно у версии есть номер, связанный с номером версии приложения.
Миграция, в данном контексте, — обновление структуры базы данных от одной версии до другой (обычно более новой).
В этом смысле термин миграция, похоже, используется во многих источниках (особенно этому поспособствовали миграции из gem'а Active Record, входящего в состав Ruby on Rails). Однако при использовании этого термина возникает двусмысленность: человек, который не знает контекста, скорее подумает, что речь идет о переносе базы данных с одной СУБД на другую (MySQL => Oracle), а то и вовсе о миграции процессов/данных между нодами кластера. Поэтому предлагаю в случаях, когда контекст неочевиден, использовать более точный термин: версионная миграция баз данных.
Зачем это нужно?
Разработчики, которые уже сталкивались с проблемой рассинхронизации версий БД и приложения, могут пропустить этот раздел. Здесь я напомню, почему нужно соблюдать паритет версий приложения и базы данных и какая общая проблема при этом возникает.Версия базы данных должна соответствовать версии приложения
Итак, представьте себе следующую ситуацию: команда из нескольких программистов разрабатывает приложение, которое активно использует базу данных. Время от времени приложение поставляется в продакшн — например, это веб-сайт, который деплоится на веб-сервер.
Любому программисту в процессе написания кода приложения может понадобиться изменить структуру базы данных, а также, сами данные, которые в ней хранятся. Приведу простой пример: допустим, есть необнуляемое (not nullable) строковое поле в одной из таблиц. В этом поле не всегда есть данные, и в этом случае там хранится пустая строка. В какой-то момент вы решили, что хранить пустые строки — семантически неправильно в некоторых случаях (см. 1, 2), а правильно — хранить NULL'ы. Для того, чтобы это реализовать, понадобятся следующие действия:
1. Изменить тип поля на nullable:
ALTER myTable CHANGE COLUMN myField myField VARCHAR(255) NULL DEFAULT NULL;2. Так как в этой таблице на продакшн БД уже наверняка есть пустые строки, вы принимаете волевое решение и трактуете их как отсутствие информации. Следовательно, нужно будет заменить их на NULL:
UPDATE myTable SET myField = NULL WHERE myField = '';3. Изменить код приложения так, чтобы при получении из БД данных, хранящихся в этом поле, он адекватно реагировал на NULL'ы. Записывать в это поле тоже теперь нужно NULL'ы вместо пустых строк.
Из пункта 3 можно видеть, что приложение и база данных — неразрывные части одного целого. Это означает, что при поставке новой версии приложения в продакшн, нужно обязательно обновлять и версию базы данных, иначе приложение попросту не сможет правильно работать. В данном примере, если до новой версии будет обновлено только приложение, то в какой-то момент произойдет вставка NULL в необнуляемое поле, а это очевидная ошибка.
Таким образом, обновление версии приложения требует корректной версионной миграции базы данных.
Так ли это просто?
Осознав, что паритет версий БД и приложения необходим, вам нужно удостовериться, что миграции БД до нужной версии всегда будут выполняться правильно. Но в чём здесь проблема? Ведь, на первый взгляд, сложного здесь ничего нет!
Тут снова обратимся к живому примеру. Допустим, программисты в процессе разработки записывают свои изменения структуры и данных БД в отдельный файл в виде SQL-запросов (как DDL-, так и DML-запросов). А при каждом деплое последней версии приложения вы одновременно обновляете до последней версии и базу данных, выполняя запросы из того самого SQL-файла… Но погодите, с какой версии вы обновляете БД до последней версии? «С прошлой»? Но так ли хорошо вы помните, что конкретно из себя представляла прошлая версия (её выпустили 2 месяца назад)? Если нет, то как вы собрались её обновлять? Ведь без точной информации о состоянии структуры и данных выполнить корректную миграцию невозможно: если вы непредумышленно выполните запросы, которые уже когда-то выполнялись, это может привести к потере данных или нарушению их целостности.
Простой пример — замена паролей на их MD5-суммы. Если повторно выполнить такой запрос, то данные можно будет восстановить только из бэкапа. Да и вообще, любые
UPDATE'ы, DELETE'ы, и даже INSERT'ы, выполненные повторно, могут привести к крайне нежелательным последствиям. Не говоря уже о несвоевременных TRUNCATE'ах и DROP'ах (хотя такие случаи намного менее вероятны).Кстати говоря, с этой точки зрения, недовыполнить — не меньшая опасность для работоспособности приложения, чем перевыполнить.
Таким образом, можно сделать вывод, что в процессе версионной миграции все запросы должны выполняться только один раз и, к тому же, в правильной последовательности. Последовательность важна потому, что одни изменения могут зависеть от других (как в примере с обнуляемым полем).
Общие принципы версионной миграции
В предыдущем разделе мы выделили важные критерии, которые показывают, что же требуется от процесса версионной миграции. Это:- единоразовое выполнение каждого изменения (SQL-запроса);
- строго предустановленный порядок изменений.
- чтобы любую версию базы данных можно было обновить до любой (обычно, самой последней) версии;
- чтобы набор SQL-запросов, реализующих миграцию между любыми двумя версиями, можно было получить как можно быстрее и проще;
- чтобы всегда можно было создать с нуля базу данных со структурой самой последней версии. Это очень полезно как в процессе разработки и тестирования, так и при развертывании нового продакшн-сервера;
- чтобы, в случае работы над разными ветками, при последующем их слиянии ручное редактирование файлов БД было сведено к минимуму;
- чтобы откатить БД на более раннюю версию было так же просто, как и обновить на более новую.
Основание миграции
Как оказалось, у большинства подходов есть общий принцип: им необходимо основание (baseline) — некоторое эталонное состояние БД, от которого можно отталкиваться. Эта концепция довольно хорошо описана в статье «Versioning Databases – The Baseline» Скотта Аллена.
Попросту говоря, основание — это дамп структуры базы данных для версии, которая принята за базовую. Имея на руках основание, впоследствии всегда можно будет создать БД с нуля. После применения к этой БД всех миграций, созданных в процессе разработки, получим БД со структурой самой последней версии.
Далее будут рассмотрены три подхода к организации версионной миграции баз данных.
Метод инкрементных изменений
Этот метод хорошо описан в статье «Versioning Databases – Change Scripts» все того же Скотта Аллена. Схожий подход также описан в статье «Managing SQL scripts and continuous integration» Майкла Бэйлона.Структура файлов
Пример того, как в этом случае может выглядеть папка с файлами-миграциями:
Database
|- Baseline.sql
|- 0001.03.01.sql
|- 0002.03.01.sql
|- 0003.03.01.sql
|- 0004.03.02.sql
|- 0005.03.02.sql
|- 0006.03.02.sql
'- 0007.03.02.sqlВ этом примере в папке хранятся все файлы, созданные при разработке версии 03. Впрочем, папка может быть и общей для всех версий приложения.
В любом случае, самый первый файл, который появится в такой папке, — основание (Baseline.sql). После этого любое изменение в БД сабмиттится в репозиторий в виде нового файла-миграции с именем вида
[номер файла].[версия].[подверсия].sql.Фактически, в этом примере в имени файла содержится полный номер версии БД. То есть после выполнения файла-миграции с именем
0006.03.02.sql база данных обновится с состояния, соответствующего версии 03.02.0005, до версии 03.02.0006.Хранение истории версий
Следующий шаг — добавление в базу данных специальной таблицы, в которой будет храниться история всех изменений в базе данных.
CREATE TABLE MigrationHistory
(
Id INT,
MajorVersion VARCHAR(2),
MinorVersion VARCHAR(2),
FileNumber VARCHAR(4),
Comment VARCHAR(255),
DateApplied DATETIME,
PRIMARY KEY(Id)
)
Это всего лишь пример того, как может выглядеть таблица. При необходимости, её можно как упростить, так и дополнить.
В файле
Baseline.sql в эту таблицу нужно будет добавить первую запись:INSERT INTO
MigrationHistory ( MajorVersion, MinorVersion, FileNumber, Comment, DateApplied )
VALUES ( '03', '01', '0000', 'Baseline', NOW() )
После выполнения каждого файла-миграции в эту таблицу будет заноситься запись со всеми данными о миграции.
Текущую версию БД можно будет получить из записи с максимальной датой.
Автоматическое выполнение миграций
Завершающий штрих в этом подходе — программа/скрипт, который будет обновлять БД с текущей версии до последней.
Выполнять миграцию БД автоматически довольно просто, т.к. номер последней выполненной миграции можно получить из таблицы MigrationHistory, а после этого остается только применить все файлы с бо́льшими номерами. Сортировать файлы можно по номеру, поэтому с порядком выполнения миграций проблем не будет.
На такой скрипт также возлагается задача добавления записей о выполненных миграциях в таблицу MigrationHistory.
В качестве дополнительных удобств, такой скрипт может уметь создавать текущую версию БД с нуля, сначала накатывая на БД основание, а затем выполняя стандартную операцию по миграции до последней версии.
Плюсы, минусы, выводы
Быстрое и удобное выполнение миграции до последней версии; Механизм нумерации версий. Номер текущей версии хранится прямо в БД;
Для максимального удобства нужны средства автоматизации выполнения миграций;
Неудобно добавлять комментарии к структуре БД. Если их добавлять в Baseline.sql, то в следующей версии они пропадут, т.к. основание будет сгенерировано с нуля вновь, в качестве дампа новой версии структуры. Вдобавок, такие комментарии будут быстро устаревать; Возникают проблемы в процессе параллельной разработки в нескольких ветках репозитория. Так как нумерация файлов-миграций — последовательная, то под одинаковыми номерами в разных ветках могут возникнуть файлы с разными DDL-/DML-запросами. Как следствие, при слиянии веток придется либо вручную редактировать файлы и их последовательность, либо же в новой, «слитой» ветке начинать с нового Baseline.sql, учитывающего изменения из обеих веток.Этот метод в различных формах довольно широко распространен. К тому же, он легко поддается упрощению и модификации под нужды проекта.
В интернете можно найти готовые варианты скриптов по инкрементному выполнению миграций и встроить в свой проект.
Метод идемпотентных изменений
Этот метод описан в статье «Bulletproof Sql Change Scripts Using INFORMATION_SCHEMA Views» Фила Хэка. Описание схожего подхода также изложено в ответе на этот вопрос на StackOverflow.Под идемпотентностью понимается свойство объекта оставаться неизменным при повторной попытке его изменить.
В тему вспоминается смешная сцена из «Друзей» :)
Основная идея этого подхода — написание миграционных файлов таким образом, чтобы их можно было выполнить на базе данных больше одного раза. При первой попытке выполнить любую из SQL-команд, изменения будут применены; при всех последующих попытках ничего не произойдет.
Эту идею проще всего уяснить на примере. Допустим, вам нужно добавить в БД новую таблицу. Если вы хотите, чтобы в том случае, если она уже существует, при выполнении запроса не возникло ошибки, — в MySQL для этих целей есть краткий синтаксис:
CREATE TABLE IF NOT EXISTS myTable
(
id INT(10) NOT NULL,
myField VARCHAR(255) NULL,
PRIMARY KEY(id)
);Благодаря ключевой фразе
IF NOT EXISTS, MySQL попытается создать таблицу только в том случае, если таблицы с таким именем еще не существует. Однако такой синтаксис доступен не во всех СУБД; к тому же, даже в MySQL его можно использовать не для всех команд. Поэтому рассмотрим более универсальный способ:IF NOT EXISTS
(
SELECT *
FROM information_schema.tables
WHERE table_name = 'myTable'
AND table_schema = 'myDb'
)
THEN
CREATE TABLE myTable
(
id INT(10) NOT NULL,
myField VARCHAR(255) NULL,
PRIMARY KEY(id)
);
END IF;В последнем примере роль параметра условного выражения играет запрос, который проверяет, существует ли таблица
myTable в базе данных с именем myDb. И только в том случае, если таблица отсутствует, произойдет, собственно, ее создание. Таким образом, приведенный запрос является идемпотентным.Стоит отметить, что в MySQL по какой-то причине запрещено выполнять DDL-запросы внутри условных выражений. Но этот запрет легко обойти — достаточно включить все подобные запросы в тело хранимой процедуры:
DELIMITER $$
CREATE PROCEDURE sp_tmp() BEGIN
IF NOT EXISTS
(
--
-- Условие.
--
)
THEN
--
-- Запрос, изменяющий структуру БД.
--
END IF;
END;
$$
DELIMITER;
CALL sp_tmp();
DROP PROCEDURE sp_tmp;Что за птица такая — information_schema?
Полную информацию о структуре базы данных можно получить из специальных системных таблиц, находящихся в базе данных с именем
information_schema. Эта база данных и ее таблицы — часть стандарта SQL-92, поэтому этот способ можно использовать на любой из современных СУБД. В предыдущем примере используется таблица information_schema.tables, в которой хранятся данные о всех таблицах. Подобным образом можно проверять существование и метаданные полей таблиц, хранимых процедур, триггеров, схем, и, фактически, любых других объектов структуры базы данных.Полный перечень таблиц с подробной информацией об их предназначении можно посмотреть в тексте стандарта. Краткий перечень можно увидеть в уже упоминавшейся выше статье Фила Хэка. Но самый простой способ, конечно же, — просто открыть эту базу данных на любом рабочем сервере БД и посмотреть, как она устроена.
Пример использования
Итак, вы знаете, как создавать идемпотентные SQL-запросы. Теперь рассмотрим, как этот подход можно использовать на практике.
Пример того, как в этом случае может выглядеть папка с sql-файлами:
Database
|- 3.01
| |- Baseline.sql
| '- Changes.sql
|
'- 3.02
|- Baseline.sql
'- Changes.sqlВ этом примере для каждой минорной версии базы данных создается отдельная папка. При создании каждой новой папки генерируется основание и записывается в Baseline.sql. Затем в процессе разработки в файл Changes.sql записываются все необходимые изменения в виде идемпотентных запросов.
Предположим, в процессе разработки в разное время программистам понадобились следующие изменения в БД:
a) создать таблицу myTable;
b) добавить в нее поле newfield;
c) добавить в таблицу myTable какие-то данные.
Все три изменения написаны так, чтобы не выполняться повторно. В результате, в каком бы из промежуточных состояний не находилась база данных, при выполнении файла Changes.sql всегда будет выполнена миграция до самой последней версии.
К примеру, один из разработчиков создал на своей локальной копии БД таблицу myTable, записал изменение a) в хранящийся в общем репозитории кода файл Changes.sql, и на какое-то время забыл о нём. Теперь, если он выполнит этот файл на своей локальной БД, изменение a) будет проигнорировано, а изменения b) и c) будут применены.
Плюсы, минусы, выводы
Очень удобное выполнение миграций с любой промежуточной версии до последней — нужно всего лишь выполнить на базе данных один файл (Changes.sql); Потенциально возможны ситуации, в которых будут теряться данные, за этим придется следить. Примером может служить удаление таблицы, и затем создание другой таблицы с тем же именем. Если при удалении проверять только имя, то обе операции (удаление и создание) будут происходить каждый раз при выполнении скрипта, несмотря на то, что когда-то уже выполнялись; Для того, чтобы изменения были идемпотентными, нужно потратить больше времени (и кода) на их написание.Благодаря тому, что обновить базу данных до последней версии очень просто, и делать это можно вручную, этот метод показывает себя в выгодном свете в том случае, если у вас много продакшн-серверов и их нужно часто обновлять.
Метод уподобления структуры БД исходному коду
Отдельных статей, посвященных этому подходу, я, к сожалению, не нашел. Буду благодарен за ссылки на существующие статьи, если таковые имеются. UPD: В своей статье Absent рассказывает о своем опыте реализации схожего подхода при помощи самописной diff-утилиты.Основная идея этого метода отражена в заголовке: структура БД — такой же исходный код, как код PHP, C# или HTML. Следовательно, вместо того, чтобы хранить в репозитории кода файлы-миграции (с запросами, изменяющими структуру БД), нужно хранить только актуальную структуру базы данных — в декларативной форме.
Пример реализации
Для простоты примера будем считать, что в каждой ревизии репозитория всегда будет только один SQL-файл: CreateDatabase.sql. В скобках замечу, что в аналогии с исходным кодом можно пойти еще дальше и хранить структуру каждого объекта БД в отдельном файле. Также, структуру можно хранить в виде XML или других форматов, которые поддерживаются вашей СУБД.
В файле CreateDatabase.sql будут храниться команды
CREATE TABLE, CREATE PROCEDURE, и т.д., которые создают всю базу данных с нуля. При необходимости изменений структуры таблиц, эти изменения вносятся непосредственно в существующие DDL-запросы создания таблиц. То же касается изменений в хранимых процедурах, триггерах, и т.д.К примеру, в текущей версии репозитория уже есть таблица myTable, и в файле CreateDatabase.sql она выглядит следующим образом:
CREATE TABLE myTable
(
id INT(10) NOT NULL,
myField VARCHAR(255) NULL,
PRIMARY KEY(id)
);Если вам нужно добавить в эту таблицу новое поле, вы просто добавляете его в имеющийся DDL-запрос:
CREATE TABLE myTable
(
id INT(10) NOT NULL,
myField VARCHAR(255) NULL,
newfield INT(4) NOT NULL,
PRIMARY KEY(id)
);После этого измененный sql-файл сабмиттится в репозиторий кода.
Выполнение миграций между версиями
В этом методе процедура обновления базы данных до более новой версии не так прямолинейна, как в других методах. Поскольку для каждой версии хранится только декларативное описание структуры, для каждой миграции придется генерировать разницу в виде
ALTER-, DROP — и CREATE-запросов. В этом вам помогут автоматические diff-утилиты, такие, как Schema Synchronization Tool, входящая в состав SQLyog, TOAD, доступный для многих СУБД, Dklab_pgmigrator для PostgreSQL от DmitryKoterov, а также, SQL Comparison SDK от RedGate.Чтобы выполнить миграцию с одной версии БД до другой, вам придется восстановить на двух временных БД структуру исходной и конечной версий, и затем сгенерировать миграционный скрипт. Впрочем, эта процедура может быть автоматизирована и много времени занимать не должна.
Как быть с изменениями данных?
Время от времени, при обновлении версии базы данных на продакшн-серверах, нужно обновлять не только структуру БД, но и хранящиеся в ней данные. В качестве примера можно привести перенос данных из таблицы со старой структурой в новые таблицы — в целях нормализации. Поскольку данные на продакшн-серверах уже существуют и используются, недостаточно просто создать новые таблицы и удалить старые, нужно еще и перенести имеющиеся данные.
В предыдущих методах, в контексте хранения и выполнения миграций, данные мало чем отличались от структуры БД. Но в данном методе изменения в данных стоят особняком, ведь хранить их в репозитории кода в декларативной форме невозможно: данные на всех серверах разные. А автоматически сгенерировать такие запросы для изменения данных также невозможно: это требует человеческого вмешательства.
У этой проблемы есть несколько более или менее приемлемых решений:
- хранить изменения данных согласно методу инкрементных изменений (возможно, в упрощенной форме) и добавлять их в результирующий diff-скрипт уже после его генерации, вручную;
- нигде не хранить запросы-изменения данных, и, когда diff-скрипт будет сгенерирован, анализировать его и добавлять по месту все необходимые DML-запросы. Привожу здесь это решение только потому, что мой коллега настаивает на том, что оно рабочее и не имеет недостатков; я же нахожу его слишком опасным, т.к. генерация diff-скрипта потенциально может происходить через несколько месяцев после работы над связанным с изменением участком приложения, и детали, необходимые для корректной миграции данных, могут быть уже забыты.
Плюсы, минусы, выводы
Удобно наблюдать изменения в структуре между версиями при помощи средств системы контроля версий; Как и любой исходный код, структуру БД удобно комментировать; Для того, чтобы с нуля создать чистую базу данных последней версии, нужно выполнить всего лишь один файл; Скрипты-миграции более надежны, чем в других методах, так как генерируются автоматически; Мигрировать с новых версий на старые почти так же просто, как со старых на новые (проблемы могут возникнуть только с пресловутыми изменениями данных); В случае слияния двух веток репозитория, merge структуры БД осуществляется проще, чем при использовании других подходов; Изменения данных придется хранить отдельно, и затем вручную вставлять в сгенерированные скрипты-миграции; Вручную выполнять миграции очень неудобно, необходимы автоматизированные средства.Этот метод имеет много позитивных качеств. Если вас не страшат описанные проблемы с изменениями данных, и если обновления продакшн-серверов случаются редко, рекомендую использовать именно этот метод.
Готовые решения для версионной миграции БД
Описанные выше методы могут использоваться без сторонних решений, однако существуют и готовые к использованию продукты, каждый со своей идеологией и оригинальным подходом, достойные отдельной статьи. При выборе решения версионной миграции, обязательно рассмотрите и такие продукты.Некоторые из них рассмотрены в недавней статье «Подходы для версионирования баз данных» Дениса Гладких.
Ниже перечислена лишь малая часть готовых к использованию систем версионной миграции:
- Migrator.NET;
- ECM7.Migrator — форк Migrator.NET за авторством dima117. Его статья на Хабре;
- Active Record Migrations, входящие в Ruby on Rails;
- SQL Source Control от RedGate;
- DotNetMigrations;
- Fluent Migrator. Статья на Хабре за авторством tabushi, создавшего форк проекта;
- DbDeploy.NET;
- Tarantino;
- Mygrate;
- DBUpdater;
- Wizardby за авторством ostapbender. Его статья на Хабре
