Цель статьи — на примере библиотеки schema-keeper показать инструменты для упрощения разработки баз данных в PHP-проектах, использующих СУБД PostgreSQL.
Будут рассмотрены следующие вопросы:
- В каком виде хранить дамп структуры БД в системе контроля версий (далее по тексту — VCS)
- Как отслеживать изменения в структуре БД после сохранения дампа
- Как переносить изменения в структуре БД на другие окружения без конфликтов и гигантских файлов миграций
- Как наладить процесс параллельной работы над проектом нескольких разработчиков
- Как безопасно деплоить большее количество изменений в структуре БД на production-окружение
Информация из этой статьи, в первую очередь, будет полезна разработчикам, которые по максимуму хотят использовать возможности PostgreSQL, но сталкиваются с проблемами сопровождения бизнес-логики, вынесенной в БД.
Статья не будет описывать преимущества или недостатки хранения бизнес-логики в базе данных. Предполагается, что выбор уже сделан читателем.
SchemaKeeper заточен под работу с хранимыми процедурами, написанными на языке PL/pgSQL. Тестирование с другими языками не проводилось, соответственно использование может быть не столь эффективно, либо невозможно.
В каком виде хранить дамп структуры БД в VCS
Библиотека schema-keeper предоставляет функцию saveDump, которая сохраняет структуру объектов из БД в виде отдельных текстовых файлов. На выходе создается директория, содержащая структуру БД, разбитую на сгруппированные файлы, которые легко добавить в VCS.
Рассмотрим преобразование объектов из БД в файлы на нескольких примерах:
Тип объекта | Схема | Название | Относительный путь к файлу |
---|---|---|---|
Таблица | public | accounts | ./public/tables/accounts.txt |
Хранимая процедура | public | auth(hash bigint) | ./public/functions/auth(int8).sql |
Представление | booking | tariffs | ./booking/views/tariffs.txt |
Содержимое файла — текстовое представление структуры конкретного объекта БД. Например, для хранимых процедур содержимым файла будет полное определение хранимой процедуры, начинающееся с блока CREATE OR REPLACE FUNCTION
.
Как видно из таблицы выше, путь к файлу хранит в себе информацию о типе, схеме и названию объекта. Такой подход облегчает навигацию по дампу и code review изменений в БД.
Расширение .sql
для файлов с исходным кодом хранимых процедур выбрано для того, чтобы IDE автоматически предоставляли инструменты по взаимодействию с БД при открытии файла.
Как отслеживать изменения в структуре БД после сохранения дампа
Сохранив дамп текущей структуры БД в VCS, получаем возможность проверить вносились ли изменения в структуру базы после создания дампа. В библиотеке schema-keeper для выявления изменений структуры БД предусмотрена функция verifyDump, которая без побочных эффектов возвращает информацию о различиях.
Альтернативный способ проверки — повторно вызвать функцию saveDump
, указав ту же директорию, и проверить в VCS наличие изменений. Так как объекты из БД сохранены в отдельных файлах, то VCS покажет только изменившиеся объекты. Главный минус данного способа — необходимость перезаписи файлов, чтобы увидеть изменения.
Как переносить изменения в структуре БД на другие окружения без конфликтов и гигантских файлов миграций
Благодаря функции deployDump исходный код хранимых процедур правится так же как и остальной исходный код приложения. Модификация хранимой процедуры происходит путем внесения изменений в соответствующий файл, что автоматически отражается в системе контроля версий.
Например, для создания новой хранимой процедуры в схеме public
достаточно создать новый файл с расширением .sql
в директории public/functions
, поместить в него исходный код хранимой процедуры, включая блок CREATE OR REPLACE FUNCTION
, затем вызвать функцию deployDump
. Аналогично происходит изменение и удаление хранимой процедуры. Таким образом, код одновременно попадает и в VCS, и в базу данных.
Если в исходном коде хранимой процедуры появится ошибка, то deployDump
не выполнится, выбросив исключение. Рассогласование хранимых процедур между дампом и текущей БД невозможно при использовании deployDump
.
При создании новой хранимой процедуры нет необходимости вручную вводить правильное название файла. Достаточно, чтобы у файла было расширение.sql
. Правильное название можно получить из возвращаемого значения функцииdeployDump
, и использовать для переименования файла.
deployDump
изменяет параметры функции или возвращаемый тип без дополнительных действий, в то время как при классическом подходе пришлось было бы сначала выполнить DROP FUNCTION
, а только потом CREATE OR REPLACE FUNCTION
.
К сожалению, в некоторых ситуациях deployDump
не в состоянии автоматически применить изменения. Например, если удаляется триггерная функция, которая используется хотя бы одним триггером. Такие ситуации решаются вручную с помощью файлов миграций.
Если за перенос изменений в хранимых процедурах отвечает сам schema-keeper, то для переноса остальных изменений в структуре используются файлы миграций. Например, подойдет библиотека doctrine/migrations.
Миграции должны применяться до запуска deployDump
, чтобы внести изменения в структуру и разрешить возможные проблемные ситуации.
Работа с миграциями будет подробнее описана в следующих разделах.
Как наладить процесс параллельной работы над проектом нескольких разработчиков
Создадим скрипт полной инициализации БД, который разработчики запускают на локальных машинах, чтобы привести структуру локальных БД в соответствие с сохраненным в VCS дампом. Разделим инициализацию локальной БД на 3 шага:
- Импорт файла с базовой структурой, который будет называться, например,
base.sql
- Применение миграций
- Вызов
deployDump
base.sql
— это отправная точка, поверх которой применяются миграции и выполняетсяdeployDump
, то естьbase.sql + миграции + deployDump = актуальная структура БД
. Используетсяbase.sql
исключительно при инициализации базы данных с нуля. Формируется такой файл с помощью pg_dump.
Назовем скрипт полной инициализации БД refresh.sh
. Рабочий процесс разработчика выглядит следующим образом:
- Запуск в своем окружении
refresh.sh
, чтобы получить актуальную структуру БД - Начало работы над поставленной задачей, модификация локальной БД под нужды нового функционала (
ALTER TABLE ... ADD COLUMN
и тд) - После выполнение задачи вызов функции
saveDump
, чтобы зафиксировать в VCS изменения, сделанные в БД - Повторный запуск
refresh.sh
, затемverifyDump
, чтобы отобразить список изменений для включения в миграцию - Перенос изменений структуры в файл миграции, запуск еще раз
refresh.sh
иverifyDump
, и, если миграция составлена корректно,verifyDump
покажет отсутствие различий между локальной БД и сохраненным дампом
Описанный выше процесс совместим с принципами gitflow
. Каждая ветка в VCS содержит свою версию дампа, и при слияние веток происходит слияние дампов. Слияния выполняются без дополнительных действий, но если в ветках вносились изменения, к примеру, в одну и ту же таблицу, возможен конфликт.
Рассмотрим конфликтную ситуацию на примере ветки develop, от которой ответвлены branch1 и branch2, которые не конфликтуют с develop, но конфликтуют между собой. Стоит задача выполнить слияние branch1 и branch2 в develop. Для такого случая рекомендуется сначала выполнить слияние branch1 в develop, а затем слияние develop в branch2, разрешив при этом конфликты в branch2, после чего выполнить слияние branch2 в develop. На этапе разрешения конфликтов, возможно, придется исправить файл миграции в branch2, чтобы он соответствовал финальному дампу, включившем в себя результаты слияний.
Как безопасно деплоить большее количество изменений в структуре БД на production-окружение
Наличие в VCS дампа актуальной структуры БД позволяет проверить production-базу на точное соответствие требуемой структуре. Это гарантирует, что на production-базу перенеслись все изменения, которые задумывали разработчики.
Так как DDL в PostgreSQL является транзакционным, рекомендуется придерживаться следующего порядка деплоя, чтобы, в случае непредвиденной ошибки, «безболезненно» выполнить ROLLBACK
:
- Начать транзакцию
- В транзакции выполнить все миграции
- В этой же транзакции выполнить
deployDump
- Не завершая транзакцию, выполнить
verifyDump
. Если ошибок нет, выполнитьCOMMIT
. Если ошибки есть, выполнитьROLLBACK
Данные шаги достаточно легко встраиваются в существующие подходы к деплою приложений, в том числе zero-downtime.
Заключение
Благодаря вышеописанным методам можно выжимать максимум производительности из «PHP + PostgreSQL» проектов, жертвуя при этом относительно небольшим количеством удобства разработки в сравнении с реализацией всей бизнес-логики в основном коде приложения. Более того, обработка данных в PL/pgSQL часто выглядит более прозрачно и требует меньшего количества кода, чем тот же функционал, написанный на PHP.