MyWiki | MySql | Версионная миграция структуры базы данных
Версионная миграция структуры базы данных

Проблемы контроля версий баз данных и миграций между версиями уже не раз поднимались как на Хабре (123 и др.), так и в Интернете (преимущественно, англоязычном).

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

Терминология

База данных — совокупность всех объектов БД (таблиц, процедур, триггеров и т.д.), статических данных (неизменяемых данных, хранящихся в lookup-таблицах) и пользовательских данных (которые изменяются в процессе работы с приложением).

Структура базы данных — совокупность всех объектов БД и статических данных. Пользовательские данные в понятие структуры БД не входят.

Версия базы данных — определенное состояние структуры базы данных. Обычно у версии есть номер, связанный с номером версии приложения.

Миграция, в данном контексте, — обновление структуры базы данных от одной версии до другой (обычно более новой).

В этом смысле термин миграция, похоже, используется во многих источниках (особенно этому поспособствовали миграции из gem'а Active Record, входящего в состав Ruby on Rails). Однако при использовании этого термина возникает двусмысленность: человек, который не знает контекста, скорее подумает, что речь идет о переносе базы данных с одной СУБД на другую (MySQL => Oracle), а то и вовсе о миграции процессов/данных между нодами кластера. Поэтому предлагаю в случаях, когда контекст неочевиден, использовать более точный термин: версионная миграция баз данных.
 

Зачем это нужно?

Разработчики, которые уже сталкивались с проблемой рассинхронизации версий БД и приложения, могут пропустить этот раздел. Здесь я напомню, почему нужно соблюдать паритет версий приложения и базы данных и какая общая проблема при этом возникает.
 

Версия базы данных должна соответствовать версии приложения


Итак, представьте себе следующую ситуацию: команда из нескольких программистов разрабатывает приложение, которое активно использует базу данных. Время от времени приложение поставляется в продакшн — например, это веб-сайт, который деплоится на веб-сервер.
Любому программисту в процессе написания кода приложения может понадобиться изменить структуру базы данных, а также, сами данные, которые в ней хранятся. Приведу простой пример: допустим, есть необнуляемое (not nullable) строковое поле в одной из таблиц. В этом поле не всегда есть данные, и в этом случае там хранится пустая строка. В какой-то момент вы решили, что хранить пустые строки — семантически неправильно в некоторых случаях (см. 12), а правильно — хранить 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 TABLECREATE 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, входящая в состав SQLyogTOAD, доступный для многих СУБД, Dklab_pgmigrator для PostgreSQL от DmitryKoterov, а также, SQL Comparison SDK от RedGate.

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

Как быть с изменениями данных?


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

В предыдущих методах, в контексте хранения и выполнения миграций, данные мало чем отличались от структуры БД. Но в данном методе изменения в данных стоят особняком, ведь хранить их в репозитории кода в декларативной форме невозможно: данные на всех серверах разные. А автоматически сгенерировать такие запросы для изменения данных также невозможно: это требует человеческого вмешательства.

У этой проблемы есть несколько более или менее приемлемых решений:

  • хранить изменения данных согласно методу инкрементных изменений (возможно, в упрощенной форме) и добавлять их в результирующий diff-скрипт уже после его генерации, вручную;
  • нигде не хранить запросы-изменения данных, и, когда diff-скрипт будет сгенерирован, анализировать его и добавлять по месту все необходимые DML-запросы. Привожу здесь это решение только потому, что мой коллега настаивает на том, что оно рабочее и не имеет недостатков; я же нахожу его слишком опасным, т.к. генерация diff-скрипта потенциально может происходить через несколько месяцев после работы над связанным с изменением участком приложения, и детали, необходимые для корректной миграции данных, могут быть уже забыты.

 

Плюсы, минусы, выводы


 Удобно наблюдать изменения в структуре между версиями при помощи средств системы контроля версий;
 Как и любой исходный код, структуру БД удобно комментировать;
 Для того, чтобы с нуля создать чистую базу данных последней версии, нужно выполнить всего лишь один файл;
 Скрипты-миграции более надежны, чем в других методах, так как генерируются автоматически;
 Мигрировать с новых версий на старые почти так же просто, как со старых на новые (проблемы могут возникнуть только с пресловутыми изменениями данных);
 В случае слияния двух веток репозитория, merge структуры БД осуществляется проще, чем при использовании других подходов;
 Изменения данных придется хранить отдельно, и затем вручную вставлять в сгенерированные скрипты-миграции;
 Вручную выполнять миграции очень неудобно, необходимы автоматизированные средства.

Этот метод имеет много позитивных качеств. Если вас не страшат описанные проблемы с изменениями данных, и если обновления продакшн-серверов случаются редко, рекомендую использовать именно этот метод.

 

Готовые решения для версионной миграции БД

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

Некоторые из них рассмотрены в недавней статье «Подходы для версионирования баз данных» Дениса Гладких.

Ниже перечислена лишь малая часть готовых к использованию систем версионной миграции:

 

В заключение

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

Переглядів: 45
Дата публікації: 12:23 04.06.2017