Цель данной статьи — на примере библиотеки schema-keeper показать инструменты, которые позволяют существенно облегчить процесс разработки баз данных в рамках PHP-проектов, использующих СУБД PostgreSQL.

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

Статья не будет описывать преимущества или недостатки хранения бизнес-логики в базе данных. Предполагается, что выбор уже сделан читателем.

Будут рассмотрены следующие вопросы:

В каком виде хранить дамп структуры БД в системе контроля версий (далее по тексту — VCS)
Как отслеживать изменения в структуре БД после сохранения дампа
Как переносить изменения в структуре БД на другие окружения без конфликтов и гигантских файлов миграций
Как наладить процесс параллельной работы над проектом нескольких разработчиков
Как безопасно деплоить большее количество изменений в структуре БД на production-окружение

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 = актуальная структура БД. Сформировать такой файл можно с помощью утилиты pg_dump. Используется base.sql исключительно при инициализации базы данных с нуля.

Назовем скрипт полной инициализации БД refresh.sh. Рабочий процесс может выглядеть следующим образом:

Разработчик запускает в своем окружении refresh.sh и получает актуальную структуру БД
Разработчик начинает работу над поставленной задачей, модифицируя локальную БД под нужды нового функционала (ALTER TABLE ... ADD COLUMN и тд)
После выполнение задачи разработчик вызывает функцию saveDump, чтобы зафиксировать в VCS изменения, сделанные в БД
Разработчик повторно запускает refresh.sh, затем verifyDump, который теперь показывает список изменений для включения в миграцию
Разработчик переносит все изменение структуры в файл миграции, запускает еще раз refresh.sh и verifyDump, и, если миграция составлена корректно, verifyDump покажет отсутствие различий между локальной БД и сохраненным дампом

Описанный выше процесс совместим с принципами gitflow. Каждая ветка в VCS будет содержать свою версию дампа, и при слияние веток будет происходить слияние дампов. В большинстве случаев после слияния не нужно предпринимать никаких дополнительных действий, но если в разных ветках вносились изменения, к примеру, в одну и ту же таблицу, может возникнуть конфликт.

Рассмотрим конфликтную ситуацию на примере: есть ветка develop, от которой ответвлены две ветки: feature1 и feature2, которые не имеют конфликтов с develop, но имеют конфликты между собой. Стоит задача выполнить слияние обеих веток в develop. Для такого случая рекомендуется сначала выполнить слияние одной из веток в develop, а затем слияние develop в оставшуюся ветку, разрешив при этом конфликты в оставшейся ветке, после чего выполнить слияние последней ветки в develop. На этапе разрешения конфликтов возможно придется исправить файл миграции в последней ветке, чтобы он соответствовал финальному дампу, включившем в себя результаты слияний.

Как безопасно деплоить большее количество изменений в структуре БД на production-окружение

Благодаря наличию в VCS дампа актуальной структуры БД появляется возможность проверить production-базу на точное соответствие требуемой структуре. Это гарантирует, что на production-базу успешно перенеслись все изменения, которые задумывали разработчики.

Так как DDL в PostgreSQL является транзакционным, рекомендуется придерживаться следующего порядка деплоя, чтобы, в случае непредвиденной ошибки, «безболезненно» выполнить ROLLBACK:

Начать транзакцию
В транзакции выполнить все миграции
В этой же транзакции выполнить deployDump
Не завершая транзакцию, выполнить verifyDump. Если ошибок нет, выполнить COMMIT. Если ошибки есть, выполнить ROLLBACK

Данные шаги достаточно легко встраиваются в существующие подходы к деплою приложений, в том числе zero-downtime.

Заключение

Благодаря вышеописанным методам можно выжимать максимум производительности из «PHP + PostgreSQL» проектов, жертвуя при этом относительно небольшим количеством удобства разработки в сравнении с реализацией всей бизнес-логики в основном коде приложения. Более того, обработка данных в PL/pgSQL часто выглядит более прозрачно и требует меньшего количества кода, чем тот же функционал, написанный на PHP.

Источник: habr.com