Организация SQL скриптов крупного проекта

Если проект использует реляционную СУБД обязательно возникнет вопрос — как организовать скрипты (миграции) для сохранения гибкости и уменьшения трудозатрат.

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

Исходные положения

Хорошая аргументация «за» и «против» к утверждениям ниже есть в статье и, за что мы особенно ценим хабр, в комментариях к «Истина в последней инстанции» или зачем нужен Database First Design».

Исходные положения:

• в унаследованных проектах с данными и на длинных дистанциях развития проекта, когда бэк трансформируется в конгломерат приложений на разных языках, чаще встречается подход database first;

• ORM и языки описаний для кодогенерации миграций не могут использовать на 100% возможности движка конкретной СУБД. При развитии проекта не редко приходится дописывать разные костыли на SQL, что усложняет поддержку, лишает миграции чистоты и абстрактной изолированности;

• целесообразность одновременной качественной, а не для галочки,поддержки в проекте нескольких СУБД встречается редко, чаще это маркетинговые требования или одна СУБД идёт как основная, а для остальных даже не проводится регулярное нагрузочное тестирование;

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

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

Отказ от разработки и поддержки downgrade миграций, как правило, обосновывается следующими соображениями:

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

• проект сталкивается с миграцией, downgrade которой невозможен без полноценного отката на резервную копию данных (пример был выше);

• проект сталкивается с миграцией, откат которой скриптами по времени дольше, чем откат на резервную копию;

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

Содержание и логическая последовательность применения изменений к БД

На основе собственного опыта разработки и поддержки структуры скриптов в git в проектах разного масштаба и для разных систем управления миграциями, а также учитывая лучшие практики и особенности PostgreSQL, в команде выработаны подходы

• вокруг которых сломано уже не мало копий на обсуждениях;

• с которыми знакомим всех новых членов команды;

• если новый член команды готов аргументированно предложить лучший подход мы выслушиваем его, его аргументы, приводим контраргументы;

• за последние несколько лет были уточнение и органическое развитие подходов, без революций.

Разделим сущности БД и соответствующие скрипты по их отношению к данным на три группы:

• обеспечивающие хранение данных (таблицы, связанные с ними ограничения, индексы, изменения типов колонок, а так же скрипты наполнения и конвертации данных);

• выполняющие обработку данных (представления, процедуры, триггерные функции и их привязка к объектам);

• управление правами доступа (создание учетных записей, изменение прав доступа).

Нет явной привязки к системе управления миграциями, есть требования к содержанию скриптов:

• фиксируем правила именования объектов и файлов (между проектами они могут отличаться, но в рамках одного проекта — зафиксированы в доступном всем документе);

• скрипты пишем на SQL;

• пишем только upgrade скрипты;

• для всех создаваемых ограничений указываем наименование (часто это опускают, полагаясь на автоматическое именование на стороне СУБД, потом, при изменении ограничения, имя приходится вытаскивать из метаданных);

• для всех сущностей (таблиц, функций, ограничений, индексов) настоятельно рекомендовано создавать комментарий (отображаются у пользователей БД и значительно упрощают жизнь разработчиков и базистов);

• не используем конструкции вида IF EXISTS , CREATE OR REPLACE и подавление ошибок (кроме единственного скрипта для массового удаления сущностей обработки данных, ниже это опишем подробнее);

• для группы скриптов обеспечивающих хранение данных;

  • выполняются строго один раз;

  • последовательность фиксирована и не меняется в будущем;

  • запрещено изменение содержания после включения в релиз, корректировки вносятся только добавлением новых скриптов;

  • в одном файле один логически связанный набор изменений, которые выполняются в рамках одной транзакции;

  • файл может содержать и DDL и DML команды;

• для группы скриптов для сущностей выполняющих обработку данных

  • все сущности пересоздаются каждый раз при внесении изменений в БД, поэтому есть скрипт удаления и скрипт создания;

  • выделенный скрипт удаления сущностей (технически таких скриптов может быть несколько с единой точной вызова), будет выполняться перед началом любого обновления структуры БД. Единственный скрипт в котором допускается использование конструкции вида IF EXISTS (скрипт выполняется многократно, а части объектов уже не существует);

  • выделенный скрипт удаления сущностей явно учитывает последовательность удаления, чтобы корректно отрабатывали зависимости между сущностями;

  • скрипт создания сущностей (технически это единая точка вызова скриптов), явно учитывает последовательность создания, чтобы корректно отрабатывали зависимости между сущностями;

  • каждая сущность описана в отдельном файле, в файле только DDL;

  • все изменения сущности выполняются в одном файле, чтобы с помощью git можно было отследить изменения программного кода;

• для группы скриптов управления правами доступа

  • выделенный скрипт (технически таких скриптов может быть несколько с единой точной вызова), будет выполняться после любого обновления структуры БД;

  • скрипты часто содержат не только явные SQL команды, но и программный код на языке СУБД, поэтому изменения сущности выполняются в одном файле, чтобы с помощью git можно было отследить изменения программного кода;

  • в скриптах могут содержаться не только команды предоставления или отзыва прав доступа, но и проверки наличия избыточных прав или лишних учетных записей. Поведение при выявлении расхождений определяется внешними требованиями, как правило это явный вызов исключения. Структура БД уже корректно обновлена, разбирательства по выявленным нарушениям оставим за скобками статьи;

• [опционально] механизм автоматической проверки что все типы скриптов, перечисленные выше, обязательно вызываются и применяются. Ситуации, когда скрипт есть, но он ни разу не вызывается, встречаются чаще чем хотелось бы.

Последовательность применения изменений к БД

1. удаление сущностей выполняющих обработку данных (функции, представления и др.);

2. применение скриптов сущностей обеспечивающих хранение данных (таблицы, индексы, данные и др.), фактически это инкрементальные изменения, накатывается только разница;

3. создание сущностей выполняющих обработку данных (функции, представления и др.);

4. корректировка прав доступа;

5. [если есть] проверка прав доступа.

Задачи, для которых ещё предстоит найти более оптимальное решение

Рассмотренные выше подходы, к сожалению, не покрывают все 100% возникающих в процессе жизни проекта ситуаций. Для примеров ниже сейчас используются не универсальные решения, сильно завязанные на конкретный проект или группу проектов:

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

• эффективная работа с «двуликими» структурами, такими как, материализованные представления, функциональные индексы;

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

Ответы на частые вопросы

Зачем каждый раз удалять и создавать все функции заново?

Сущности выполняющие обработку данных, не только функции, но и другие, следует пересоздавать, чтобы:

• не блокировать изменения в таблицах и представлениях (не всегда получится удалить или изменить тип колонки в таблице если эта колонка используется в другом представлении или функции);

• уменьшить вероятность ошибок «рассинхронизации» типов, когда в функции или в представлении возвращаются данные из колонки, тип которой изменился. Большинство подобный ошибок проявится ещё на этапе применения изменений на окружении у разработчика;

• уменьшить вероятность ошибок «рассинхронизации» имен, когда в представлении возвращаются данные из колонки, которую переименовали, имя в таблице изменилось, а в представлении нет;

• и да, очевидно это не защищает от ошибок, если в функции SQL запросы генерируются и исполняются динамически.

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

Пересоздание таких сущностей занимает немного времени, как правило, нет необходимости его экономить на этом этапе.

Почему не стоит использовать конструкции вида IF EXISTS , CREATE OR REPLACE ?

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

• в базе есть объект, который создан не скриптами миграций. Откуда он, кто и зачем его создал? Необходимо минимизировать изменения в БД в обход миграций;

• при изменении сигнатуры функции есть вероятность не заменить её, а добавить рядом одноименную функцию с отличающимся набором параметров. В результате подобного не всегда очевидного поведения часть кода использует новую функцию, часть - старую, т.к. обе они присутствуют.

Исключение, когда использование использование конструкции вида IF EXISTS оправдано описано выше.

Ссылки на материалы

1. Alembic - Generating SQL Scripts

2. Golang-migrate - Migrations

3. Some best practices to keep in mind when using Liquibase

4. Flyway - Concepts - Migrations

5. Transactional DDL in PostgreSQL

Больше практических навыков от экспертов отрасли вы можете получить в рамках онлайн-курсов от моих коллег из OTUS. Уже 29 января коллеги проведут бесплатный вебинар про изучение NoSQL БД через замечательную Cassandra. Большая часть занятия будет посвящена реальным примерам из практики. Регистрируйтесь, будет интересно.