image

Хочу поделиться инструментом, который родился при разработке одного веб-проекта и очень помогает мне не потеряться в море таблиц, хранимых процедур, индексов и прочих обитателей базы данных.

Сам проект написан на Django, в качестве бекенда — PostgreSQL. В самом начале работы было решено, по крайней мере, частично отказаться от использования Django ORM в пользу «сырого» SQL и хранимых процедур. Другими словами, почти вся бизнес-логика вынесена на уровень базы данных. Сразу скажу, что готовить ORM я умею, но в данном случае требовалось производить многоступенчатые вычисления, связанные с множеством выборок, а это лучше делать на сервере БД и не таскать промежуточные данные в приложение.

Столкнувшись с необходимостью поддержания структуры базы данных вручную, без приятностей Django Migrations, я выяснил, что вручную писать инкрементальные SQL патчи возможно, но трудно уследить за зависимостями объектов БД. К примеру, когда функции, которая используется где-то еще, добавляешь еще один аргумент, простого CREATE OR REPLACE недостаточно — ее нужно сначала DROP, а потом CREATE. При этом нужно предварительно удалить зависимые от нее функции, а потом создать заново (а если от этих функций еще кто-то зависит, тогда надо и их пересоздать).

Под катом краткое описание возможностей в виде туториала. Встречайте — Sqlibrist.

Надо сказать, что мою проблему уже научились решать. Например, сравнительно давно существует Sqitch. Он позволяет описывать структуру базы данных в декларативном виде на SQL. Каждая таблица, вид или функция хранится в отдельном файле, а простой DSL описывает зависимости. Утилита написана на Perl, и мне, не знакомому с разработкой на Perl и экосистемой его пакетов, пришлось очень постараться, чтобы эту утилиту скомпилировать. Возможно, в силу долгой истории разработки, Sqitch имеет многовато зависимостей, как для такой простой программы. Еще мне не понравилось запутанное описание зависимостей и работа с версиями структуры. Признаю, что я просто не захотел подстраиваться и разбираться с инструментом, который мне показался неудобным.

Создавая Sqlibrist, я вдохновлялся и Sqitch, и Django Migrations, и немного VCS. А еще хотел, чтобы он был простым и понятным в использовании. Объекты структуры БД хранятся в отдельных файлах. Каждый содержит SQL-инструкцию для создания и (не обязательно) удаления этого объекта. Зависимости между объектами описываются явно в виде директив на встроенном DSL (в нем, кстати, только три ключевых слова: REQ, UP, DOWN). Подобно системе управления версиями, Sqlibrist хранит снимки структуры БД и SQL-патч для обновления до него с предыдущего снимка.

Интеллект Sqlibrist ограничен, он не парсит SQL и не генерирует ALTER TABLE — это ваша работа. Он только отслеживает изменения в файлах и создает патчи с вашими инструкциями, а также ведет учет примененных миграций.
Все это звучит как-то абстрактно, давайте перейдем к практике.

Установка


Моя основная ОС — Linux и на сервере и на десктопе, поэтому инструкции по установке только для нее. Возможно, кто-нибудь поможет мне с Windows и Mac.

Сначала заголовочные файлы:

Ubuntu


$ sudo apt-get install python-pip python-dev libyaml-dev
$ sudo apt-get install libmysqlclient-dev  # for MySQL
$ sudo apt-get install libpq-dev  # PostgreSQL

Fedora/CentOS


$ sudo dnf install python-devel python-pip libyaml-devel
$ sudo dnf install postgresql-devel  # PostgreSQL

$ sudo dnf install mariadb-devel  # for MariaDB

или

$ sudo dnf install mysql++-devel  # for MySQL

Sqlibrist написан на Python и имеет две зависимости: PyYAML и что-то одно из psycopg2 и mysql-python.

Устанавливается с помощью pip либо в virtualenv, либо в системные библиотеки:

$ pip install sqlibrist

или

$ sudo pip install sqlibrist

После установки становится доступной команда sqlibrist.

База данных интернет-магазина


Давайте поиграемся с Sqlibrist на примере примитивного интернет-магазина.

$ mkdir shop_schema
$ cd shop_schema
$ sqlibrist init
Creating directories...
Done.

Команда init создала структуру директорий нашего проекта:

shop_schema
    sqlibrist.yaml
    migrations
    schema
        constraints
        functions
        indexes
        tables
        triggers
        types
        views

В sqlibrist.yaml конфигурация проекта для подключения к БД:

---
default:
  engine: pg
  user: <username>
  name: <database_name>
  password: <password>
# host: 127.0.0.1
# port: 5432

Чтобы проверить, что настройки верны:

$ sqlibrist test_connection
Connection OK

Дальше проинициализируем таблицу, где Sqlibrist будет хранить информацию о примененных миграциях. Эта часть идентична Django Migrations/South.

$ sqlibrist initdb
Creating db...
Creating schema and migrations log table...

Done.

Кстати, в терминологии Sqlibrist, миграция — это снимок структуры базы и патчи для применения этой миграции или отката к предыдущей.

Далее создадим файл shop_schema/schema/tables/user.sql:

--UP
CREATE TABLE "user" (
id SERIAL PRIMARY KEY,
name TEXT,
password TEXT);

Первая строка --UP означает, что следующие SQL-инструкции создают объект БД. Этого достаточно для создания таблицы.

Аналогично создадим еще два файла:

shop_schema/schema/tables/product.sql:

--UP
CREATE TABLE product (
id SERIAL PRIMARY KEY,
name TEXT,
price MONEY);

shop_schema/schema/tables/order.sql:

--REQ tables/user
--UP
CREATE TABLE "order" (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES "user"(id),
date DATE);

Обратите внимание на строку --REQ tables/user. Она означает, что текущий объект зависит от объекта в файле tables/user.sql (в REQ расширение не пишется). Это гарантирует, что при генерации патча таблица user будет создана перед таблицей order. Все --REQ должны идти в начале файла.

Еще один файл:

shop_schema/schema/tables/order_product.sql:

--REQ tables/order
--UP
CREATE TABLE order_product (
id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES "order"(id),
product_id INTEGER REFERENCES product(id),
quantity INTEGER);

Создадим первую миграцию:

$ sqlibrist makemigration -n 'initial'
Creating:
 tables/user
 tables/product
 tables/order
 tables/order_product
Creating new migration 0001-initial

Файлы миграции созданы в shop_schema/migrations/0001-initial:

up.sql
down.sql
schema.json

В up.sql содержится патч для применения миграции, down.sql в данном случае пустой, а в schema.json снимок текущей структуры БД.

Перед применением патча вы можете (и это желательно) ознакомиться с текстом патча и удостовериться, что он делает то, что нужно. Если он вас не устраивает, удалите всю директорию 0001-initial и создайте миграцию заново. Можете редактировать up.sql и down.sql, если знаете, что делаете, но не трогайте schema.json.

Теперь применим нашу первую миграцию:

$ sqlibrist migrate
Applying migration 0001-initial... done

Три таблицы созданы. Теперь нам нужен вид, который выводит заказы пользователя с суммами заказа:

shop_schema/schema/views/user_orders.sql:

--REQ tables/user
--REQ tables/order
--REQ tables/product
--REQ tables/order_product

--UP
CREATE VIEW user_orders AS SELECT
 u.id as user_id,
 o.id as order_id,
 o.date,
 SUM(p.price*op.quantity) AS total

 FROM "user" u
 INNER JOIN "order" o ON u.id=o.user_id
 INNER JOIN order_product op ON o.id=op.order_id
 INNER JOIN product p ON p.id=op.product_id

 GROUP BY o.id, u.id;

--DOWN
DROP VIEW user_orders;

После директивы --DOWN идут инструкции для удаления user_orders при его пересоздании.

Общее правило: содержащие данные объекты, например таблицы, мы обновляем вручную, поэтому их описания не содержат --DOWN, а функции, типы, индексы можно безболезненно удалять и создавать, поэтому это можно доверить автоматике.

Еще нам нужна функция, которая возвращает user_orders для заданного пользователя:

--REQ views/user_orders

--UP
CREATE FUNCTION get_user_orders(_user_id INTEGER)
RETURNS SETOF user_orders
LANGUAGE SQL AS $$

SELECT * FROM user_orders
WHERE user_id=_user_id;

$$;

--DOWN
DROP FUNCTION get_user_orders(INTEGER);

Создадим и применим следующую миграцию:

$ sqlibrist makemigration -n 'user_orders view and function'
Creating:
 views/user_orders
 functions/get_user_orders
Creating new migration 0002-user_orders view and function

$ sqlibrist migrate
Applying migration 0002-user_orders view and function... done

Таким образом, у нас 4 таблицы, один вид и одна функция.

Допустим, нам нужно добавить еще одно поле в вид user_orders. Вот какие проблемы могут возникнуть:

  • мы можем удалить и создать заново новый вид user_orders, но БД не позволит этого сделать, потому что функция get_user_orders зависит от этого вида;
  • можно схитрить и выкрутиться CREATE OR REPLACE VIEW user_orders..., но тип поля вида и тип результата функции будут отличаться. И в этом случае БД не даст нам этого сделать без пересоздания функции.

Sqlibrist как раз и предназначен разрешать такие проблемы. Добавим поле SUM(op.quantity) as order_total в вид user_orders:


--REQ tables/user
--REQ tables/order
--REQ tables/product
--REQ tables/order_product

--UP
CREATE VIEW user_orders AS SELECT
 u.id as user_id,
 o.id as order_id,
 o.date,
 SUM(p.price*op.quantity) AS total,
 SUM(op.quantity) as order_total

 FROM "user" u
 INNER JOIN "order" o ON u.id=o.user_id
 INNER JOIN order_product op ON o.id=op.order_id
 INNER JOIN product p ON p.id=op.product_id

 GROUP BY o.id, u.id;

--DOWN
DROP VIEW user_orders;

Можно посмотреть, что же изменилось:

$ sqlibrist -V diff
Changed items:
  views/user_orders
---

+++

@@ -2,7 +2,8 @@

      u.id as user_id,
      o.id as order_id,
      o.date,
-     SUM(p.price*op.quantity) AS total
+     SUM(p.price*op.quantity) AS total,
+     SUM(op.quantity) as total_quantity

      FROM "user" u
      INNER JOIN "order" o ON u.id=o.user_id

Создадим миграцию:

$ sqlibrist makemigration
Updating:
 dropping:
  functions/get_user_orders
  views/user_orders
 creating:
  views/user_orders
  functions/get_user_orders
Creating new migration 0003-auto

Вы видите, что сначала удаляется зависящий объект — функция get_user_orders, потом сам вид. Далее вид создается с новой структурой, после восстанавливается функция. Такая схема будет работать для зависимостей произвольной глубины (но не циклической зависимости — Sqlibrist попросит исправить ее).

Применим эту миграцию:

$ sqlibrist migrate
Applying migration 0003-auto... done

Наконец, давайте внесем изменение в таблицу. Поскольку файлы с определениями таблиц не содержат --DROP, будем работать руками:

  1. Изменим инструкцию CREATE TABLE;
  2. Сгенерируем новую миграцию той же командой makemigration;
  3. Добавим в up.sql необходимый ALTER TABLE.

Добавим новое поле «type» text в таблицу product:

shop_schema/schema/tables/product.sql:

--UP
CREATE TABLE product (
id SERIAL PRIMARY KEY,
name TEXT,
"type" TEXT,
price MONEY);

Это пункт 1. Теперь создадим миграцию:

$ sqlibrist makemigration -n 'new product field'
Updating:
 dropping:
  functions/get_user_orders
  views/user_orders
 creating:
  views/user_orders
  functions/get_user_orders
Creating new migration 0004-new product field

Обратите внимание, что несмотря на то, что мы изменили определение таблицы product, tables/product не присутствует в журнале миграции, НО все зависимые от него объекты пересоздаются. Это пункт 2.

Теперь пункт 3: откройте в редакторе shop_schema/migrations/0004-new product field/up.sql и найдите строку 12 с текстом — ==== Add your instruction here ====. Это логическая середина миграции. В этот момент все зависимые объекты удалены и мы можем вставить наш ALTER TABLE.

Вставьте следующее:
ALTER TABLE product
ADD COLUMN "type" TEXT;

Наш up.sql будет выглядеть так:

-- begin --
DROP FUNCTION get_user_orders(INTEGER);
-- end --


-- begin --
DROP VIEW user_orders;
-- end --


-- begin --
-- ==== Add your instruction here ====
ALTER TABLE product
ADD COLUMN "type" TEXT;
-- end --


-- begin --
CREATE VIEW user_orders AS SELECT
     u.id as user_id,
     o.id as order_id,
     o.date,
     SUM(p.price*op.quantity) AS total,
     SUM(op.quantity) as total_quantity

     FROM "user" u
     INNER JOIN "order" o ON u.id=o.user_id
     INNER JOIN order_product op ON o.id=op.order_id
     INNER JOIN product p ON p.id=op.product_id

     GROUP BY o.id, u.id;
-- end --


-- begin --
CREATE FUNCTION get_user_orders(_user_id INTEGER)
    RETURNS SETOF user_orders
    LANGUAGE SQL AS $$

    SELECT * FROM user_orders
    WHERE user_id=_user_id;

    $$;
-- end --

Можно применить этот патч:

$ sqlibrist migrate

Applying migration 0004-new product field... done

На этом моменте оставим в покое наш интернет-магазин.

Еще Sqlibrist умеет интегрироваться в проект Django, я им пользуюсь именно в этом контексте.

Сайт проекта — здесь, баг-репорты приветствуются.

Комментарии (20)


  1. zelenin
    21.04.2016 15:17

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


    1. fevral13
      21.04.2016 15:26

      если будете писать на вендоро-независимом SQL, то почему бы и нет


      1. zelenin
        21.04.2016 15:33

        о том и речь, что миграции должны быть описаны абстракцией над запросами, а не sql.
        SqlAlchemy, как я понимаю, как раз так написана.


    1. QuickJoey
      21.04.2016 16:06

      там, где у posgres CREATE OR REPLACE FUNCTION, у ms sql ALTER PROCEDURE. у ms sql параметры к процедуре начинаются с символа "@", который postgres в префиксе параметра использовать запрещает, поэтому абстрагироваться от вендора не получится.
      безболезненные инкрементные патчи, которые учитывают зависимости – уже благо. хотя интересно, как этот инструмент поступает с перегруженными функциями (одинаковое имя, разное количество или тип входных параметров).


      1. zelenin
        21.04.2016 16:12

        вы просто погуглите — найдете во всех ЯП подобные миграции. Ну а фолбэки на голос sql всегда тоже можно.


        1. QuickJoey
          21.04.2016 16:18

          гуглил, и что уж там, пытался мигрировать с ms sql, на, как раз postgres. если в БД хранимые процедуры измеряются тысячами, это малореальная задача. и мне кажется, что в более-менее больших проектах, лучше использовать особенности каждого вендора по максимуму, чтобы не было ситуации «работает везде, но не очень».


          1. zelenin
            21.04.2016 16:22

            то есть в вашем случае бибилиотека, предоставляющая абстракцию над миграциями, не работала? issue написали на гитхабе?
            или мы вообще не о библиотеках, а о миграции с одной СУБД на другую без миграций, описанных в статье?


            1. fevral13
              21.04.2016 16:27

              Я имел в виду не миграции с движка на движок. Sqlibrist решает задачу изменений структуры БД при, скажем, разработке и переносе этих изменений с девелоперской на другие экземпляры базы: тестовую, продакшен. Когда нужно написать патч, меняющий/добавляющий объекты базы.


              1. zelenin
                21.04.2016 16:32

                и я имел это же в виду. Абстрактные миграции позволяют не только диффы переносить, но и в целом безболезненно мигрировать на другую СУБД. Например SqlAlchemy для python это умеет (да и вообще любая популярная бибилиотека мигнраций на любом языке).


                1. fevral13
                  21.04.2016 16:33

                  Алхимия, а вернее Alembic, умеет не все. Хранимые процедуры ему не под силу


                  1. zelenin
                    21.04.2016 16:36

                    вполне может быть. всегда найдутся какие-то vendor-specific features, которые используются в 5% проектов. Что не отменяет общего посыла треда.


            1. QuickJoey
              21.04.2016 16:38

              именно, миграция с одной СУБД на другую.


              1. zelenin
                21.04.2016 16:44

                ну так а это уже вопрос другой. Мы тут про библиотеки, позволяющие безболезненно мигрировать. Описываешь абстрактным языком все изменения в схеме, переносишь на другую СУБД, вводишь команду миграции и вуаля. Без библиотек миграции миграция на другую субд — это боль конечно.
                Да, есть специфические вещи, не поддающиеся вендор-абстракции.


                1. QuickJoey
                  21.04.2016 16:52

                  да, теперь понятно, я почему-то ваш первый комментарий истолковал, как миграцию с одного вендора в девелоперской версии в, например, другого вендора в тестовой версии.


      1. fevral13
        21.04.2016 16:14

        Поступает, как следует. Например, есть две перегруженные функции: test(a integer) и test(b text). Описываем каждую в отдельном файле:

        test_int.sql:

        --UP
        create function test(a integer) 
        returns boolean 
        as $$ 
        return true; 
        $$ 
        language plpgsql;
        
        --DOWN
        drop function test(integer);
        


        test_text.sql:

        --UP
        create function test(b text) 
        returns boolean 
        as $$ 
        return false; 
        $$ 
        language plpgsql;
        
        --DOWN
        drop function test(text);
        


        Теперь, если вы меняете одну из них, скажем, первую, то она сначала удалится инструкцией из раздела --DOWN, а потом пересоздастся. При этом DROP FUNCTION отличаются типами аргументов, поэтому путаницы не будет.


  1. 2ruslank
    21.04.2016 18:49

    >> К примеру, когда функции, которая используется где-то еще, добавляешь еще один аргумент, простого CREATE OR REPLACE недостаточно — ее нужно сначала DROP, а потом CREATE.

    достаточно. добавляется аргумент с волшебным словом default.


    1. fevral13
      21.04.2016 18:53

      Это костыль и только для этого конкретного случая. Если надо поменять тип существующего аргумента, тип результата, без DROP-CREATE не обойтись


      1. 2ruslank
        21.04.2016 20:55

        это не костыль а пример которой опровергает Ваше утверждение «простого CREATE OR REPLACE недостаточно — ее нужно сначала DROP, а потом CREATE».


  1. sim3x
    22.04.2016 11:55

    Думал ли над тем, чтоб обернуть код в up/down.sql в транзакцию, если там нет «необратимых» инструкций?

    stackoverflow.com/questions/13462475/is-it-possible-to-wrap-ddl-changes-in-a-transaction-in-postgresql


    1. fevral13
      22.04.2016 11:57

      Так и есть. Каждый патч исполняется внутри транзакции (только в PostgreSQL). Если при накатывании патча произошла ошибка, транзакция откатывается