Если проект использует реляционную СУБД обязательно возникнет вопрос — как организовать скрипты (миграции) для сохранения гибкости и уменьшения трудозатрат.
Привет хабр, меня зовут Василий, в этой статье хочу представить для широкого обсуждения опыт по организации кода поддержки структуры базы данных долговременных крупных проектов с небольшим уклоном в сторону 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 можно было отследить изменения программного кода;
в скриптах могут содержаться не только команды предоставления или отзыва прав доступа, но и проверки наличия избыточных прав или лишних учетных записей. Поведение при выявлении расхождений определяется внешними требованиями, как правило это явный вызов исключения. Структура БД уже корректно обновлена, разбирательства по выявленным нарушениям оставим за скобками статьи;
[опционально] механизм автоматической проверки что все типы скриптов, перечисленные выше, обязательно вызываются и применяются. Ситуации, когда скрипт есть, но он ни разу не вызывается, встречаются чаще чем хотелось бы.
Последовательность применения изменений к БД
удаление сущностей выполняющих обработку данных (функции, представления и др.);
применение скриптов сущностей обеспечивающих хранение данных (таблицы, индексы, данные и др.), фактически это инкрементальные изменения, накатывается только разница;
создание сущностей выполняющих обработку данных (функции, представления и др.);
корректировка прав доступа;
[если есть] проверка прав доступа.
Задачи, для которых ещё предстоит найти более оптимальное решение
Рассмотренные выше подходы, к сожалению, не покрывают все 100% возникающих в процессе жизни проекта ситуаций. Для примеров ниже сейчас используются не универсальные решения, сильно завязанные на конкретный проект или группу проектов:
поставка и актуализация общих справочников. Особенно в ситуациях, когда таблицы должны содержать и поставляемые данные и пользовательские, при этом поставляемая часть справочников должна быть синхронизирована между несколькими проектами;
эффективная работа с «двуликими» структурами, такими как, материализованные представления, функциональные индексы;
более эффективная поддержка скриптов с тестовыми данными (они попадают в группу обеспечивающих хранение данных). Здесь частично организационный вопрос по пополнению обезличенными примерами от конечных потребителей. Чем шире пользовательская база, тем более увлекательные детективные истории поиска не очевидных ошибок могла бы рассказать служба поддержки, если бы не конфиденциальность данных.
Ответы на частые вопросы
Зачем каждый раз удалять и создавать все функции заново?
Сущности выполняющие обработку данных, не только функции, но и другие, следует пересоздавать, чтобы:
не блокировать изменения в таблицах и представлениях (не всегда получится удалить или изменить тип колонки в таблице если эта колонка используется в другом представлении или функции);
уменьшить вероятность ошибок «рассинхронизации» типов, когда в функции или в представлении возвращаются данные из колонки, тип которой изменился. Большинство подобный ошибок проявится ещё на этапе применения изменений на окружении у разработчика;
уменьшить вероятность ошибок «рассинхронизации» имен, когда в представлении возвращаются данные из колонки, которую переименовали, имя в таблице изменилось, а в представлении нет;
и да, очевидно это не защищает от ошибок, если в функции SQL запросы генерируются и исполняются динамически.
У этого подхода есть и отрицательная сторона — на уровне скрипта сложнее оценить влияние изменения структуры хранения данных на представления и функции. В момент внесения изменения их уже нет, не проследить цепочку зависимостей, а потом они будут пересозданы уже с учетом изменившейся структуры хранения. Здесь целесообразно применять дополнительные инструменты, сравнивающие структуру БД до внесения изменения и после, чтобы выявить все изменения.
Пересоздание таких сущностей занимает немного времени, как правило, нет необходимости его экономить на этом этапе.
Почему не стоит использовать конструкции вида IF EXISTS , CREATE OR REPLACE ?
В каких ситуациях они нужны? Когда мы не знаем структуру базы данных, а мы её априори должны знать, если без этих конструкций скрипты не накатываются, значит где-то проблема:
в базе есть объект, который создан не скриптами миграций. Откуда он, кто и зачем его создал? Необходимо минимизировать изменения в БД в обход миграций;
при изменении сигнатуры функции есть вероятность не заменить её, а добавить рядом одноименную функцию с отличающимся набором параметров. В результате подобного не всегда очевидного поведения часть кода использует новую функцию, часть - старую, т.к. обе они присутствуют.
Исключение, когда использование использование конструкции вида IF EXISTS оправдано описано выше.
Ссылки на материалы
Больше практических навыков от экспертов отрасли вы можете получить в рамках онлайн-курсов от моих коллег из OTUS. Уже 29 января коллеги проведут бесплатный вебинар про изучение NoSQL БД через замечательную Cassandra. Большая часть занятия будет посвящена реальным примерам из практики. Регистрируйтесь, будет интересно.
Комментарии (16)
IVNSTN
15.01.2024 15:55+3Что-то я как будто середину чьего-то разговора застал... Не очень понятно, на какую дискуссию рассчитан пост без деталей реализации и примеров из жизни. Соберите минимальную репу на GitHub или скрины того, что у вас есть приложите. Как выглядит репозиторий, код пишется в Notepad++ и вы узнаете о том, что синтаксис кривой только на деплое или как это все у вас происходит.
Из описания не понял, так у вас миграции (дельты) хранятся в Git или финальные определения объектов. По таблицам больше похоже, что миграции (ALTER TABLE ADD COLUMN), по хп больше похоже что скрипт один и навсегда, т.е. смесь подходов. Как тогда рулите тем, что является дельтой, которую нужно применить к серверу? Ф-и и хп, поскольку описано безусловное пересоздание, вероятно, всегда "тупо" все накатываете. Больше костыль при отсутствии решения, чем решение.
Упоминается некий скрипт, в котором ф-и и хп в правильном порядке дропаются и создаются - вы его "на руках" держите или есть автоматизация, которая эту последовательность строит глядя в код?
У этого подхода есть и отрицательная сторона — на уровне скрипта сложнее оценить влияние изменения структуры хранения данных на представления и функции. В момент внесения изменения их уже нет, не проследить цепочку зависимостей, а потом они будут пересозданы уже с учетом изменившейся структуры хранения. Здесь целесообразно применять дополнительные инструменты, сравнивающие структуру БД до внесения изменения и после, чтобы выявить все изменения.
ай донт ферштейн че вообще происходит в этом абзаце. По "структуре" вы накатываете дельту - сами изменения. Почему вам нужно их искать дополнительными инструментами? Вопрос риторический, абзац нуждается в расшифровке и переводе на человеческий.
все сущности пересоздаются каждый раз
не самое удачное жонглирование словом "сущности" в разговоре про Relational DB. Функции и процедуры никто сущностями не называет.
Пересоздание таких сущностей занимает немного времени
но вы же, если правильно понял, дропаете их до применения миграций к таблицам, а создаете уже после. А миграции данных вполне себе могут занимать сколько угодно времени. И БД будет сидеть голенькая все это время. Для, например, добавления одной колонки в одну таблицу как-то круто сносить вообще всё.
крупных проектов с небольшим уклоном в сторону PostgreSQL
правильно ли я понимаю, что проект крупный во всем остальном, а код и структура БД как раз маленькие? Чуть больше фактов и подробностей добавьте пожалуйста, суть решения и к каким ситуациям оно применимо осталось внезапно за скобками: сколько таблиц, хранимок. Как выглядит финальный деплой-скрипт.
И решение вы оцениваете как полностью ваш локальный велосипед (без оценки качества, только уникальность) или его можно сравнить со способом работы DbUp, SqlPackage или чего-то еще?
nvv Автор
15.01.2024 15:55По таблицам больше похоже, что миграции (ALTER TABLE ADD COLUMN), по хп больше похоже что скрипт один и навсегда, т.е. смесь подходов. Как тогда рулите тем, что является дельтой, которую нужно применить к серверу?
Действительно, используется двойной подход, именно для этого и разделяем сущности по их отношению к данным - хранят (инкрементальные миграции) или только обрабатывают ("скрипт один и навсегда").
На сервере накатывается код соответствующий релизу. Он содержит и новые дельты и актуальные версии процедур/представлений.IVNSTN
15.01.2024 15:55> Как тогда рулите тем, что является дельтой, которую нужно применить к серверу? На сервере накатывается код соответствующий релизу
как вы определяете, какой код соответствует какому релизу?
nvv Автор
15.01.2024 15:55Ветка или тег релиза (что принято в проекте) совпадают для кода миграций и соответствующего кода бэка.
А как добиться соответствия кода релизов БД, нескольких приложениях бэка и фронта - отдельная тема)
IVNSTN
15.01.2024 15:55+1И как вы по тэгу в гите, который отдает вам всё содержимое репозитория, понимаете, какие из триллиона миграций не нужно, а какие нужно применять в данном конкретном релизе? Верно ли я понимаю, что автоматизации вокруг описанной системы примерно ноль и вы всё "понимаете" и собираете руками? И правильную последовательность дропов, и правильный набор миграций.
И правильно ли я понял, что остальные вопросы из первого комментария останутся без ответа?
nvv Автор
15.01.2024 15:55В статье описан подход по разделению сущностей БД, и, в зависимости от этого, разные алгоритмы организации хранения (инкременты или постоянный скрипт) и применения (строго один раз или многократно).
Подход может быть реализован разными инструментами.В текущем проекте используем liquibase, он обеспечивает очередность, проверяет контрольные суммы файлов, сколько раз скрипт должен быть выполнен, какие скрипты уже были накачены. Деплой - liquibase получает код необходимой версии и параметры коннекта к БД, обрабатывает changelog файлы.
asdfddsa
15.01.2024 15:55-1На какой сервер, куда, чего, чем накатывается? Вам же предложили сделать "минимальную репу на GitHub или скрины".
nvv Автор
15.01.2024 15:55И решение вы оцениваете как полностью ваш локальный велосипед (без оценки качества, только уникальность) или его можно сравнить со способом работы DbUp, SqlPackage или чего-то еще?
Вероятно мы говорим о разных вещах. Повторюсь, в статье описан подход по разделению сущностей БД, и, в зависимости от этого, разные алгоритмы организации хранения (инкременты или постоянный скрипт) и применения (строго один раз или многократно).
Подход может быть реализован разными инструментами и liquibase, и "своими велосипедами" (в опросе вариант "Разработали и применяем своё решение" достаточно популярен).
nvv Автор
15.01.2024 15:55Пересоздание таких сущностей занимает немного времени
но вы же, если правильно понял, дропаете их до применения миграций к таблицам, а создаете уже после. А миграции данных вполне себе могут занимать сколько угодно времени. И БД будет сидеть голенькая все это время. Для, например, добавления одной колонки в одну таблицу как-то круто сносить вообще всё.
Инкрементные миграции, которые выполняются на втором этапе, действительно могут выполняться долго. Удаление и создание хранимок и представлений, как правило, занимет мало времени, экономия не всегда целесообразна в ущерб простоте поддержки.
Для, например, добавления одной колонки в одну таблицу как-то круто сносить вообще всё.
В БД может быть от нескольких десятков view, которые зависят друг от друга, часть из них, могут иметь код вида
CREATE VIEW view_name AS SELECT table_name.*, ... FROM table_name ...Да, выборка без фиксации колонок не относится к лучшим практикам, но такой код объективно встречается, не всегда есть время и ресурсы, чтобы отрефакторить и оттестировать все подобные места в связке БД и бэка. Если
viewне пересоздать, новая колонка не попадёт изtable_nameвоview_name.Пересоздавать проще, чем каждый раз вручную отслеживать что должно/может измениться при "добавлении одной колонки в одну таблицу".
Ballins
15.01.2024 15:55Ну учитывая что огород знатный речь не о mssql. Там бы все легко решил проект в vs и sqlpackage.
Но огород все равно знатный. Зачем все дропать то?
Нужно называть объекты и файлы одинаково.
Нужно разделить типы объектов по папкам.
В альтере таблиц нужно добавлять проверки на существование или сделать что то по аналогии рефакторлога из SQL проектов.
Дропать и пересоздавать только то что нужно по зависимостям из бд.
Накатывать только те объекты что у вас в релизе. И запускать все только один раз зачем запускать что то несколько раз вообще?
nvv Автор
15.01.2024 15:55Не знаком с инструментом SqlPackage, подскажите, как с его помощью эффективно реализовать обновление из комментария выше - "добавление одной колонки в одну таблицу". SqlPackage самостоятельно выявит все объекты, в т.ч. харнимые процедуры, где поведение может измениться из-за добавления колонки? SqlPackage создает набор скриптов, которые позволяют с любой версии обновиться до актуальной?
Как разрешается ситуация, если два разработчика параллельно внесли изменения в структуру БД (в разные таблицы добавили по одной колонке) затрагивающие
view_name, получили наборы файлов миграций m1 и m2, отправили PR каждый со своими изменениями, их изменения приняли. Порядок приёма PR может повлиять на конечный видview_name?
В подходе описанном в статье, когда m1 и m2 это инкрементальные изменения, а view будет пересоздано, обе добавленные колонки будут учтены воview_name.IVNSTN
15.01.2024 15:55SqlPackage создает набор скриптов, которые позволяют с любой версии обновиться до актуальной?
Именно так: привести любого состояния БД к тому что надо, что в репозитории/в релизе.
SqlPackage самостоятельно выявит все объекты, в т.ч. харнимые процедуры, где поведение может измениться
Выявит, но только отправит на рефреш. Если хп менялись, то альтернет. "На всякий случай" ничто никогда не дропается.
CREATE VIEW view_name AS SELECT table_name.*
Подстраиваться под эти ужасы на мой взгляд гиблое направление, которое приводит к появлению "Больше костыль при отсутствии решения, чем решение" (мой коммент выше). Исходники надо линтить, подобные ужасы выявлять и устранять абсолютно все.
SELECT *имеют право существовать только в условно одноразовых говноскриптах. Если нетSELECT *, то не существует хп или вьюх, которые каким-то непостижимым образом берут и неявно меняют свое поведение, потому что где-то в таблицу колонку добавили или две. Такого не существует как явления (если навести порядок). Гугл говорит, что для postgresql какие-то линтеры есть. К SonarQube есть плагин с поддержкой postgresql.Порядок приёма PR может повлиять на конечный вид
view_name?Это уже про что-то за гранью добра и зла.
Pomudorka60
Рекомендация не использовать create or replace - только для пакетов и функций же, верно понимаю ?
А то в голове вопрос, как тогда пере собирать вью или матвью. Делать постоянно бекап и через него ?
nvv Автор
Не рекомендуется использовать
create or replaceво всех случаях. Лучше явно выполнить удаление объекта, а потом создать заново (для функций и представлений это даже делается на разных этапах). Это уменьшит риск случайной замены объектов с потерей.С удалением обычных view (на первом этапе) и созданием их заново (на третьем), как правило, меньше всего проблем. Самая часто встречающаяся - необходимость соблюдать порядок создания/удаления, который учитываем взаимозависимости.
С materialized view сложнее, т.к. они не только обрабатывают данные, но и хранят их. Как отметил в статье, для таких структур универсального подхода нет, из-за наличия данных их чаще приходится относить к инкрементальным изменениям (обрабатываются на втором этапе).
IVNSTN
Что такое "случайная замена функции" и с потерей чего? Особенно в контексте описанного сценария, где дропается вообще всё и потом создается вообще всё.
nvv Автор
Не один и не два раза в разных проектах встречалась проблема - в разных файлах создавались однотипные объекты с одинаковым именем. Причины разные, от copy-paste и забыли переименовать, до неправильного порядка при накате скриптов. Если использовать только
createвместоcreate or replace, обновление развалится при перой же попытке накатить, это быстро локализует проблему.