Comments 17
MS Visual Studio Database Project
Работаю несколько лет в проекте, где изменения в БД это ежеминутный процесс разработки. То есть каждая новая сборка требует изменений в структуре БД и в данных.
Реализовано примерно так:
Все изменения в БД делаются только скриптами.
Добавление полей, изменение полей, добавление и изменение процедур, вьюх и т.п. Все скрипты пишутся с проверкой — если объект не существует, то создаем, если существует — alter. Все скрипты последовательно собираются в один большой скрипт — то есть обновление накопительной и применимо к любой версии. Если объем изменений очень большой — то устанавливается граница для накопительного обновления и процесс начинается заново.
Отдельно динамически создается скрипт для изменения данных — в основном это справочники, и скрипты по обработке данных. Справочники имеют системный диапазон (грубо до 1000 строки) и пользовательский (если существует несколько продактов, то они уже могли расширить справочники своими данными). При вставке, изменении, удалении строк в таблицу проверяется наличие данных, наличие ключей, соответствие ключей ключей и корректируется поведение скрипта. То есть если нужно строку справочника удалить, но есть данные на нее ссылающиеся, то пишем в лог ошибок для ручной корректировке. Скрипты также собираются в один накопительный — применимый к любой версии.
Все разработчики направляют требования по изменению в БД одному ответственному. Это человек, который в соответствии с определенной системой формирует названия объектов и собирает скрипты для обновления.
Пока такая система работает исправно.
Реализовано примерно так:
Все изменения в БД делаются только скриптами.
Добавление полей, изменение полей, добавление и изменение процедур, вьюх и т.п. Все скрипты пишутся с проверкой — если объект не существует, то создаем, если существует — alter. Все скрипты последовательно собираются в один большой скрипт — то есть обновление накопительной и применимо к любой версии. Если объем изменений очень большой — то устанавливается граница для накопительного обновления и процесс начинается заново.
Отдельно динамически создается скрипт для изменения данных — в основном это справочники, и скрипты по обработке данных. Справочники имеют системный диапазон (грубо до 1000 строки) и пользовательский (если существует несколько продактов, то они уже могли расширить справочники своими данными). При вставке, изменении, удалении строк в таблицу проверяется наличие данных, наличие ключей, соответствие ключей ключей и корректируется поведение скрипта. То есть если нужно строку справочника удалить, но есть данные на нее ссылающиеся, то пишем в лог ошибок для ручной корректировке. Скрипты также собираются в один накопительный — применимый к любой версии.
Все разработчики направляют требования по изменению в БД одному ответственному. Это человек, который в соответствии с определенной системой формирует названия объектов и собирает скрипты для обновления.
Пока такая система работает исправно.
Для PostgreSQL пользуюсь Pyrseas. Он снимает описание со схемы в виде yaml файла, который можно сравнить с любой базой и по разнице автоматически генерируется sql миграция. За счёт этого можно работать в параллельных ветках базы и нормально сливать изменения. Поддерживаются специфичные для PG вещи (вот марица поддерживаемых свойств).
Многие недостатки п.3 исчезают, если вместо flyway использовать собственную утилиту для накатывания базы.
Что можно сделать лучше:
— вместо нумерации changeset-ов просто вести файлик со списком .sql файлов — тогда если разработчики обзовут файлы одинаково или занесут свои изменения одновременно, то будет конфликт.
— в том же файле указывать, как исполнять эти файлы — однократно (сохраняя в базу хеш файла и предупреждая, если он изменился), каждый раз или только при изменении
— дорабатывать под свои нужды — к примеру, придумать синтаксис для заливания BLOB-ов в базу из локальных файлов
— Если хочется видеть историю изменений потаблично без применения grep — никто не мешает новые ALTER-ы раскладывать по папкам с именами таблиц. Или даже использовать синтаксис, аналогичный п.4
— Этот подход также позволяет другую, крайне важную фичу — удобную интеграцию скриптов отката изменений.
Что можно сделать лучше:
— вместо нумерации changeset-ов просто вести файлик со списком .sql файлов — тогда если разработчики обзовут файлы одинаково или занесут свои изменения одновременно, то будет конфликт.
— в том же файле указывать, как исполнять эти файлы — однократно (сохраняя в базу хеш файла и предупреждая, если он изменился), каждый раз или только при изменении
— дорабатывать под свои нужды — к примеру, придумать синтаксис для заливания BLOB-ов в базу из локальных файлов
— Если хочется видеть историю изменений потаблично без применения grep — никто не мешает новые ALTER-ы раскладывать по папкам с именами таблиц. Или даже использовать синтаксис, аналогичный п.4
— Этот подход также позволяет другую, крайне важную фичу — удобную интеграцию скриптов отката изменений.
Подходы, подходы. Не хранить логику в базе, вот и все подходы.
Как мне кажется, все эти проблемы оттого, что вы пытаетесь в SQL-сервер запихнуть все возможные задачи.
База данных должна быть для данных, для остального есть языки программирования.
Ладно, ситуации конечно разные бывают. По теме.
Если надо не накатывать изменения повторно, то первый же вариант это хранить где-то уже сделанные. Таким образом, сразу приходим к пункту 3. Это не только указанная вами "flyway db". Так делается во многих PHP фреймворках, и наверно на других языках тоже. Миграции пишутся на PHP, в них есть методы up() и down() — накат и откат, в которых вызываются SQL скрипты.
Нельзя выделить историю изменения одного объекта, alter’ы на один объект разбросаны по многим скриптам, многим файликам.
Создаем отдельную папку на каждый объект. Это решает все остальные проблемы.
При параллельной работе двух программистов, они могут создать скрипты с одинаковыми номерами.
Поэтому лучше указывать время создания до секунды плюс описательное название файла.
То есть добавил строчку в скрипт версионируемого справочника, и он исполнился
Не надо существующие миграции менять, миграции должны отражать реальные изменения. Создаем новую миграцию с одним инсертом, кладем в папку объекта. В этом же коммите будут изменения в исходниках приложения.
Некоторые скрипты хотелось бы накатывать при каждом deploy’е — это, например, какая-нибудь очистка, занесение персистентных справочников, которые должны версионироваться
Если справочники версионируются, то зачем их накатывать полностью. Накатили diff из миграции и все, данные актуальны. Это как раз связано с предыдущим пунктом.
Очистка, на мой взгляд, вообще не связана с событием деплоя, это лучше по расписанию делать.
То есть пункт 3 самый оптимальный вариант.
Использую sqitch rework, но это для Postgres, MySQL, SQLite, Oracle, Firebird, Vertica.
Для MS SQL драйвер никто не написал.
Используем подход 3. только инструмент Dbmaintain http://www.dbmaintain.org/overview.html Он достаточно сильно похож на flywaydb, но оказался более удобным.
Сначала по «минусам» 3 подхода.
«нельзя» слишком сильное слово. Поиск возможен только по файлам в репозитории, или, если вы храните примененные скрипты в БД, то по таблице. Значит и получить историю изменении можно (файлы то пронумерованы и может быть даже разложены по версиям).
Отлично работает для поиска относительно недавних изменений (до года)
Более старые изменения уходят в Baseline, то есть придется попрыгать по коммитам в git чтобы откопать что-то действительно старое.
В базе — да. Однако это легко решается программным созданием скриптов из шаблона. Заодним программист сразу видит что ему надо указать кроме его любимого alter….
Нет этого минуса. Flyway DB позволяет делать повторяющиеся миграции https://flywaydb.org/documentation/migration/repeatable
Dbmaintain тоже это умеет. «добавил строчку — чексумма файла изменилась — скрипт применился»
И снова нет. Согласен, что Flyway это не умеет =-) Однако Dbmaintain позволяет добавить как скрипты, выполняющиеся ДО основных изменений, так и скрипты выполняющиеся ПОСЛЕ основных изменений. Так что это не минус подхода. это минус конкретного инструмента.
Какой же это недостаток? Cкрипты идеально расположены в порядке выполнения, который всегда (почти ;) ) одинаков. Упавший скрипт сразу дает не только информацию по конкретной ошибке, но и всю картину текущего состояния базы. Какие скрипты уже применены, какие еще не применены. Это гораздо полезнее чем разложенные по папочкам скрипты, которые не пойми применились или нет.
Считаю безусловным плюсом инкрементального подхода.
Это можно и без 4… да и структура каталогов может отталкиваться не от схем в БД ) В тоже время чем проще итоговый архив обновления, тем лучше. Если обновление зашивается в артефакты новой версии, то там при любой начальной структуре надо будет выполнять действия по правильной упаковке, с учетом платформ и специфики ПО.
добавлю к минусам подхода 4
Минус 4. Конфликты в управляющих файлах
Если предположить что у нас не 2 разработчика, а 10, то уже не избежать постоянных конфликтов при мерже данных управляющих файлов из личных веток в общий ствол.
Сначала по «минусам» 3 подхода.
Нельзя выделить историю изменения одного объекта, alter’ы на один объект разбросаны по многим скриптам, многим файликам.
«нельзя» слишком сильное слово. Поиск возможен только по файлам в репозитории, или, если вы храните примененные скрипты в БД, то по таблице. Значит и получить историю изменении можно (файлы то пронумерованы и может быть даже разложены по версиям).
Отлично работает для поиска относительно недавних изменений (до года)
Более старые изменения уходят в Baseline, то есть придется попрыгать по коммитам в git чтобы откопать что-то действительно старое.
При параллельной работе двух программистов, они могут создать скрипты с одинаковыми номерами.
В базе — да. Однако это легко решается программным созданием скриптов из шаблона. Заодним программист сразу видит что ему надо указать кроме его любимого alter….
Раз уж подход скриптоцентрированный, то не хватает фич:
— Некоторые скрипты хотелось бы накатывать при их изменении. То есть добавил строчку в скрипт версионируемого справочника, и он исполнился, и строчка в таблице появилась. В таком виде можно хранить историю изменения данных (см подход 4).
Нет этого минуса. Flyway DB позволяет делать повторяющиеся миграции https://flywaydb.org/documentation/migration/repeatable
Dbmaintain тоже это умеет. «добавил строчку — чексумма файла изменилась — скрипт применился»
— Некоторые скрипты хотелось бы накатывать при каждом deploy’е – это, например, какая-нибудь очистка, занесение персистентных справочников, которые должны версионироваться (поэтому их нельзя заносить вручную в БД).
И снова нет. Согласен, что Flyway это не умеет =-) Однако Dbmaintain позволяет добавить как скрипты, выполняющиеся ДО основных изменений, так и скрипты выполняющиеся ПОСЛЕ основных изменений. Так что это не минус подхода. это минус конкретного инструмента.
В последовательности произвольных скриптов крайне сложно разбираться. Создание таблиц, их alter’ы, добавление строчек в справочники, миграции – разбросаны практически хаотично в одной последовательности.
Какой же это недостаток? Cкрипты идеально расположены в порядке выполнения, который всегда (почти ;) ) одинаков. Упавший скрипт сразу дает не только информацию по конкретной ошибке, но и всю картину текущего состояния базы. Какие скрипты уже применены, какие еще не применены. Это гораздо полезнее чем разложенные по папочкам скрипты, которые не пойми применились или нет.
Считаю безусловным плюсом инкрементального подхода.
Хотелось бы иметь алфавитную сортировку файликов, различные папочки. Словом, хочется в скриптах видеть структуру БД. Можно, конечно, что-то придумать – сделать кучу папочек, сделать огромный bat, запускающий инструмент на эти папочки в нужном порядке… Да, это начало следующего подхода, 4го.
Это можно и без 4… да и структура каталогов может отталкиваться не от схем в БД ) В тоже время чем проще итоговый архив обновления, тем лучше. Если обновление зашивается в артефакты новой версии, то там при любой начальной структуре надо будет выполнять действия по правильной упаковке, с учетом платформ и специфики ПО.
добавлю к минусам подхода 4
Минус 4. Конфликты в управляющих файлах
Так как последовательность исполнения скриптов важна, вводим управляющие файлы, содержащие последовательность наката скриптов,
Если предположить что у нас не 2 разработчика, а 10, то уже не избежать постоянных конфликтов при мерже данных управляющих файлов из личных веток в общий ствол.
Стоит добавить, что Liquibase — это не только xml, но и yaml.
А для поклонников jvm, еще и groovy-dsl.
А для поклонников jvm, еще и groovy-dsl.
А Datical (он же платный Liquibase) кто пробовал?
Коллеги, большое спасибо за внимание, дельные комментарии, и отдельное спасибо тем, кто поделился своими подходами.
Я не претендую на знание всех инструментов — хотел бы попросить поделиться мнением тех, кто знает, насколько хорошо и что умеет MS Visual Studio Database Project? Лично я не пробовал, но априори как-то… не доверяю чтоли. Ни в коем случае не хочу это никому навязывать. Пока мне кажется, что он умеет что-то наподобие подхода 2, но со слишком большими ограничениями.
Так же очень интересно узнать чье-то практическое мнение про Datical и redgate Source Control.
Я не претендую на знание всех инструментов — хотел бы попросить поделиться мнением тех, кто знает, насколько хорошо и что умеет MS Visual Studio Database Project? Лично я не пробовал, но априори как-то… не доверяю чтоли. Ни в коем случае не хочу это никому навязывать. Пока мне кажется, что он умеет что-то наподобие подхода 2, но со слишком большими ограничениями.
Так же очень интересно узнать чье-то практическое мнение про Datical и redgate Source Control.
Database Project можно натравить на уже созданную базу и он заскриптует все ее объекты, разложив их по схемам и типам в виде sql-скриптов.Все дальнейшие действия идут над файлами проекта, а не на целевой базе, а значит любая VCS подойдет для контроля изменений.
Есть встроенный дизайнер, как в SSMS, который по сути генерирует скрипт, который также можно править ручками.
Есть встроенное сравнение схем между проектом и базой, базой и базой и т.д. Результаты сравнения видны по объектам, а также в виде diff-а sql-кода этих объектов, на одном экране.
Можно автоматом накатить изменения (не рекомендую), но лучше сгенерировать скрипт, внимательно его просмотреть (поправить при необходимости) и накатить.
Можно выбрать, какие объекты игнорировать при генерации скрипта.
Есть возможность деплоя базы разными способами.
Можно использовать отдельно сравнение схем баз, без ведения Database Project. Тогда теряется возможность контролировать изменения, но если у вас есть базы dev и prod и нет желания менять существующую схему работы — вполне годный вариант.
Скриншот
Есть встроенный дизайнер, как в SSMS, который по сути генерирует скрипт, который также можно править ручками.
Есть встроенное сравнение схем между проектом и базой, базой и базой и т.д. Результаты сравнения видны по объектам, а также в виде diff-а sql-кода этих объектов, на одном экране.
Можно автоматом накатить изменения (не рекомендую), но лучше сгенерировать скрипт, внимательно его просмотреть (поправить при необходимости) и накатить.
Можно выбрать, какие объекты игнорировать при генерации скрипта.
Есть возможность деплоя базы разными способами.
Можно использовать отдельно сравнение схем баз, без ведения Database Project. Тогда теряется возможность контролировать изменения, но если у вас есть базы dev и prod и нет желания менять существующую схему работы — вполне годный вариант.
Скриншот
В VS2017 доступны два типа DB проектов — «стандартный» и новый RedGate ReadyRoll.
Стандартный проект при развертывании использует концепцию «desired state»: что в проекте — то будет и на целевой базе.
Для развертывания изначально проект собирается или в скрипты, или в файл .dacpac. Из dacpac можно развертывать на базу с помощью sqlpackage.exe — он выявляет разницу в состоянии целевой базы и пакета, и накатывает разницу на лету (там очень много параметров и настроек — в частности целевую базу можно даже забэкапить автоматически перед накатом).
Целевой базы может не быть — тогда они будет создана «с нуля».
Можно заставить процесс развертывания удалять объекты, которых нет в проекте — очень удобно для расчистки базы от следов «прямого вмешательства программистов», оставляющих после себя "_backup" версии объектов. При этом удаление таблиц или колонок не происходит если там есть данные.
Что касается конфигурации системы — это тоже (хотя бы то, что определено как минимальная конфигурация) хорошо держать в репозитории. В проекте есть понятие pre- и post-deployment скриптов — они как раз и могут накатывать конфигурацию.
Этот тип проекта + системы контроля версий кода + разумная стратегия branching & merging = вполне жизнеспособно для вашего сценария с многими разработчиками, dev/test/prod ландшафтом итд.
Посмотрите в сторону Visual Studio Team Services или TFS 2015/2017. Это решит почти все — хранение исходников, билды, развертывание на свои сервера или в Azure.
Второй тип проекта, ReadyRoll, подразумевает, что в проекте хранятся скрипты изменений, которые необходимо накатывать по очереди.
В целевой базе создается дополнительная таблица, в которой автоматически ведется учет — какие скрипты были применены.
Подход хорош при необходимости много таскать скриптов с модификацией данных. Но мне как-то этот подход кажется более хрупким, если основная задача — управлять кодом объектов и структурой таблиц.
Стандартный проект при развертывании использует концепцию «desired state»: что в проекте — то будет и на целевой базе.
Для развертывания изначально проект собирается или в скрипты, или в файл .dacpac. Из dacpac можно развертывать на базу с помощью sqlpackage.exe — он выявляет разницу в состоянии целевой базы и пакета, и накатывает разницу на лету (там очень много параметров и настроек — в частности целевую базу можно даже забэкапить автоматически перед накатом).
Целевой базы может не быть — тогда они будет создана «с нуля».
Можно заставить процесс развертывания удалять объекты, которых нет в проекте — очень удобно для расчистки базы от следов «прямого вмешательства программистов», оставляющих после себя "_backup" версии объектов. При этом удаление таблиц или колонок не происходит если там есть данные.
Что касается конфигурации системы — это тоже (хотя бы то, что определено как минимальная конфигурация) хорошо держать в репозитории. В проекте есть понятие pre- и post-deployment скриптов — они как раз и могут накатывать конфигурацию.
Этот тип проекта + системы контроля версий кода + разумная стратегия branching & merging = вполне жизнеспособно для вашего сценария с многими разработчиками, dev/test/prod ландшафтом итд.
Посмотрите в сторону Visual Studio Team Services или TFS 2015/2017. Это решит почти все — хранение исходников, билды, развертывание на свои сервера или в Azure.
Второй тип проекта, ReadyRoll, подразумевает, что в проекте хранятся скрипты изменений, которые необходимо накатывать по очереди.
В целевой базе создается дополнительная таблица, в которой автоматически ведется учет — какие скрипты были применены.
Подход хорош при необходимости много таскать скриптов с модификацией данных. Но мне как-то этот подход кажется более хрупким, если основная задача — управлять кодом объектов и структурой таблиц.
Для mssql все есть в visual studio, вообще инструменты один из весомых плюсов mssql.
Дополню пару слов про третий вариант.
Мы используем https://github.com/db-migrate/node-db-migrate. Это аналог упомянутых инструментов на node.js. Умеет работать из коробки с mysql, postgresql, sqlite3 и mongodb.
Из плюсов:
- Миграции можно писать как в js файлах используя ORM, так и в sql файлах
- Есть scopes. То есть можно сделать несколько областей (dev, prod, test) и раскидать все миграции по соответствующим скоупам. Это дает возможность в каждом окружении запускать только необходимые миграции.
Из минусов:
- нет pre- и post- скриптов. Но это не так критично.
В итоге, у нас работа построена следующим образом:
- Мы активно используем хранимки, в коде приложений нет никаких запросов, только вызов хранимок.
- Всё, что касается БД (и структуру и хранимки) разрабатывают программисты БД.
- Каждый разработчик БД внося изменения в структуру базы, сам пишет небольшую миграцию (это довольно удобно, у db-migrate есть cli, который подготавливает для вас костяк, остаётся только заполнить 2 sql файла с миграцией вверх и вниз)
- У нас 2 репозитория касательно БД: в одном живёт вся структура с нуля, статические справочники и все хранимки (по одному файлу на каждую сущность, будь то таблица, вьюха, хранимка и прочее), в другом живут только миграции.
- Дальше у нас руками написан небольшой шелл скриптик, который подготавливает оба варианта развертывания базы для продакшн.
- В итоге, если БД разворачивается с нуля — используется один скрипт, если же происходит обновление существующей БД, то накатываются только отсутствующие миграции и перенакатываются хранимки (ну просто потому что хранимки завязаны на структуру таблиц и нет смысла их держать в миграциях, а проще после накатить актуальные сразу, которые точно будут консистентны со структурой)
Как-то так, если вкратце.
Огромная благодарность автору за отличный обзор основных методик!
И немного моего личного опыта работы с Liquibase в приложении на Spring Boot
habr.com/ru/post/460377
habr.com/ru/post/460907
И немного моего личного опыта работы с Liquibase в приложении на Spring Boot
habr.com/ru/post/460377
habr.com/ru/post/460907
Sign up to leave a comment.
Подходы к версионированию изменений БД