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

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

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

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

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

   SchemaKeeper заточен под работу с хранимыми процедурами, написанными на языке PL/pgSQL. Тестирование с другими языками не проводилось, соответственно использование может быть не столь эффективно, либо невозможно.

В каком виде хранить дамп структуры БД в VCS

Βιβλιοθήκη schema-keeper предоставляет функцию saveDump, которая сохраняет структуру всех объектов из БД в виде отдельных текстовых файлов. На выходе создается директория, содержащая структуру БД, разбитую на сгруппированные файлы, которые легко добавить в VCS.

Рассмотрим преобразование объектов из БД в файлы на нескольких примерах:

Κατηγορία
Το σχέδιο
Όνομα
Относительный путь к файлу

τραπέζι
δημόσιο
λογαριασμούς
./public/tables/accounts.txt

Хранимая процедура
δημόσιο
auth(hash bigint)
./public/functions/auth(int8).sql

Εισαγωγή
κράτηση
τιμολόγια
./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 шага:

1. Импорт файла с базовой структурой, который будет называться, например, base.sql
2. Применение миграций
3. Πρόκληση deployDump

base.sql — это отправная точка, поверх которой применяются миграции и выполняется deployDumpΔηλαδή, base.sql + миграции + deployDump = актуальная структура БД. Сформировать такой файл можно с помощью утилиты pg_dump. Используется base.sql исключительно при инициализации базы данных с нуля.

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

1. Разработчик запускает в своем окружении refresh.sh и получает актуальную структуру БД
2. Разработчик начинает работу над поставленной задачей, модифицируя локальную БД под нужды нового функционала (ALTER TABLE ... ADD COLUMN και τα λοιπά)
3. После выполнение задачи разработчик вызывает функцию saveDump, чтобы зафиксировать в VCS изменения, сделанные в БД
4. Разработчик повторно запускает refresh.shτότε verifyDump, который теперь показывает список изменений для включения в миграцию
5. Разработчик переносит все изменение структуры в файл миграции, запускает еще раз refresh.sh и verifyDump, и, если миграция составлена корректно, verifyDump покажет отсутствие различий между локальной БД и сохраненным дампом

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

Рассмотрим конфликтную ситуацию на примере: есть ветка ανάπτυξη, от которой ответвлены две ветки: χαρακτηριστικό1 и χαρακτηριστικό2, которые не имеют конфликтов с ανάπτυξη, но имеют конфликты между собой. Стоит задача выполнить слияние обеих веток в ανάπτυξη. Для такого случая рекомендуется сначала выполнить слияние одной из веток в ανάπτυξη, а затем слияние ανάπτυξη в оставшуюся ветку, разрешив при этом конфликты в оставшейся ветке, после чего выполнить слияние последней ветки в ανάπτυξη. На этапе разрешения конфликтов возможно придется исправить файл миграции в последней ветке, чтобы он соответствовал финальному дампу, включившем в себя результаты слияний.

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

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

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

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

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

Συμπέρασμα

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

Πηγή: www.habr.com