В данной статье мы рассмотрим проектирование системы по подходу DB-first и то, какие проблемы он помогает не просто решить, а устранить как явление.

DB-first - это принцип первичности БД перед приложением. Не приложение, а БД владеет контрактом взаимодействия с ней.

Мы смотрим на БД как на сервис с таким же API, как REST, только средством коммуникации служит не JSON, а SQL, а контрактом является не OpenAPI/Swagger, а схема БД в виде DDL. Если БД - это сервис, значит приложения, взаимодействующие с ней, являются клиентами. Клиентов может быть множество. Сервис определяет контракт, который он способен обеспечить, а не клиент.

Приступим.

Постановка

Давайте спроектируем прототип рекомендательной системы музыкального сервиса в духе Яндекс-Музыки или Spotify.

Подобного рода системы опираются на сопоставление объектов по численным критериям. Критерии определяются в процессе профилирования/разметки, реализацию которого мы вынесем за скобки нашей задачи. Зафиксируем лишь, что система профилирования размечает композиции в пространстве из 5 координат, каждая из которых равномерно распределена в диапазоне от 0.0 до 1.0. Похожие композиции имеют близкие координаты, а непохожие - далёкие. Что значат сами координаты для нас неважно.

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

Нашу задачу сфокусируем на проектировании системы подбора рекомендаций. У нас есть список функциональных требований, и мы уже выбрали в качестве хранилища Postgres.

Функциональные требования

• Поиск похожих композиций по набору композиций
  

  • Набор композиций определяется списком идентификаторов

  • Поиск должен выдавать набор композиций с координатами близкими к среднему значению координат композиций, предоставленных на входе

• Добавление/обновление профиля композиции по параметрам:
  

  • Идентификатор композиции

  • Разметка (embedding): координаты в пятимерном пространстве

• Удаление профиля композиции по идентификатору

Проектирование БД

Начнём с определения перекоса нагрузки, так как это предопределяет принципиальное устройство БД. Здравый смысл подсказывает, что операций поиска будет осуществляться на порядки больше, чем операций добавления композиций, а потому систему нужно оптимизировать под поиск. Значит и дизайн БД имеет смысл начать именно с этой операции, и уже под неё подстроить схему и остальные операции.

Поиск похожих композиций по набору композиций

Исполнение любого запроса приводит к путешествию по сети. Это задержки и трафик. Поэтому всегда имеет смысл искать возможность выразить требуемую операцию с помощью одного запроса.

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

Сделаем набросок данного запроса:

with
  reference as (
    select
      avg(f1) as f1,
      avg(f2) as f2,
      avg(f3) as f3,
      avg(f4) as f4,
      avg(f5) as f5
    from composition
    where
      id = any($composition_ids)
  )
select
  match.id,
  (
    abs(match.f1 - reference.f1) +
    abs(match.f2 - reference.f2) +
    abs(match.f3 - reference.f3) +
    abs(match.f4 - reference.f4) +
    abs(match.f5 - reference.f5) 
  ) / 5 as distance
from reference
right join composition as match on
  match.id <> all($composition_ids) and
  match.f1 >= reference.f1 - $margin and match.f1 <= reference.f1 + $margin and
  match.f2 >= reference.f2 - $margin and match.f2 <= reference.f2 + $margin and
  match.f3 >= reference.f3 - $margin and match.f3 <= reference.f3 + $margin and
  match.f4 >= reference.f4 - $margin and match.f4 <= reference.f4 + $margin and
  match.f5 >= reference.f5 - $margin and match.f5 <= reference.f5 + $margin
order by distance
limit 100

К сведению, параметрам запроса мы дали названия по формату: $имя_параметра.

В данном запросе мы подразумеваем следующую схему:

create table composition (
  id uuid not null primary key,
  f1 float4 not null,
  f2 float4 not null,
  f3 float4 not null,
  f4 float4 not null,
  f5 float4 not null,
);

Не забываем про индекс:

create index composition_features on composition (
  f1, f2, f3, f4, f5
);

Добавление/обновление профиля композиции по параметрам

Легко реализуется с помощью одного запроса с теми же требованиями к БД:

insert into composition (id, f1, f2, f3, f4, f5)
values ($id, $f1, $f2, $f3, $f4, $f5)
on conflict (id) do update
set
  f1 = $f1,
  f2 = $f2,
  f3 = $f3,
  f4 = $f4,
  f5 = $f5

Удаление профиля композиции по идентификатору

Это совсем просто:

delete from composition
where id = $id

Подключаем инструментарий

Итак, у нас на выходе из стадии проектирования появился артефакт: схема БД и конечный набор запросов к ней. Ничего из этого мы, правда, ещё не проверяли. Так что, если бы мы хотели передать этот артефакт коллегам, скажем, для осуществления задач по интегрированию с данной БД из кода, стоило бы осуществить проверки. И вот настал момент для знакомства с инструментарием dbfirst.

На сайте dbfirst.ru запросим доступ по программе Preview и получим утилиту dbfirst для командной строки.

Создадим папку для нашего проекта и сохраним артефакты в виде следующей структуры файлов:

migrations/1.sql - Файл миграции, содержащий объявления схемы БД
queries/select-similar-compositions.sql - Запрос на выборку композиций
queries/upsert-composition.sql - Запрос на создание/обновление композиции
queries/delete-composition.sql - Запрос на удаление композиции

Добавим в корень проекта файл project.dbfirst-v1.yaml со следующим содержанием:

space: spookify
name: recommendations
version: 1.0.0

Пока не будем вдаваться в детали того, что мы там написали.

Теперь, находясь в директории этого проекта, запустим утилиту dbfirst. И с порога мы получаем сообщение об ошибке:

syntax error at or near ")"
SQL state code: 42601.

Context:
  migrations/1

Точно. В файле миграции у нас была лишняя запятая перед скобкой. Исправим:

create table composition (
  id uuid not null primary key,
  f1 float4 not null,
  f2 float4 not null,
  f3 float4 not null,
  f4 float4 not null,
  f5 float4 not null
);

Прогоняем dbfirst заново и получаем новую ошибку:

relation "compositions" does not exist

Context:
  queries/select-similar-compositions

Правда. Мы же в схеме назвали таблицу composition. Внесём коррективы в запрос:

with
  reference as (
    select
      avg(f1) as f1,
      avg(f2) as f2,
      avg(f3) as f3,
      avg(f4) as f4,
      avg(f5) as f5
    from composition
    where
      id = any($composition_ids)
  )
select
  match.id,
  (
    abs(match.f1 - reference.f1) +
    abs(match.f2 - reference.f2) +
    abs(match.f3 - reference.f3) +
    abs(match.f4 - reference.f4) +
    abs(match.f5 - reference.f5) 
  ) / 5 as distance
from reference
right join composition as match on
  match.id <> all($composition_ids) and
  match.f1 >= reference.f1 - $margin and match.f1 <= reference.f1 + $margin and
  match.f2 >= reference.f2 - $margin and match.f2 <= reference.f2 + $margin and
  match.f3 >= reference.f3 - $margin and match.f3 <= reference.f3 + $margin and
  match.f4 >= reference.f4 - $margin and match.f4 <= reference.f4 + $margin and
  match.f5 >= reference.f5 - $margin and match.f5 <= reference.f5 + $margin
order by distance
limit 100

Прогоняем утилиту и видим, что результат Ok.

Что это значит? Это значит, что dbfirst проверила нашу схему и наши запросы не только на синтаксическую корректность, но и на совместимость запросов со схемой. Теперь мы можем передать запросы в разработку интеграции с чистой совестью. Однако это отнюдь не всё, что нам может дать утилита dbfirst.

Генерация кода

Дополним файл project.dbfirst-v1.yaml до следующего:

space: spookify
name: recommendations
version: 1.0.0
artifacts:
  - java-jdbc-v1
  - haskell-hasql-v1

И прогоним dbfirst ещё раз. Получаем снова Ok. Прекрасно! Но что это нам дало?

Обратите внимание на появившуюся папку artifacts. В ней вы найдёте SDK для языков Java и Haskell. Это библиотеки, предоставляющие полную интеграцию с предоставленными нами запросами. Структуры и типы данных выведены из схемы и запросов и осуществляется кодирование параметров и декодирование результатов. Всё это гарантированно совместимо со схемой и не требует тестирования.

Для наглядности вот кусок сгенерированного кода Java:

public ArrayList<SelectSimilarCompositionsResultRow> selectSimilarCompositions(UUID[] compositionIdsParam, double marginParam) throws SQLException {
  Array compositionIdsParamArray = selectSimilarCompositionsStatement.getConnection().createArrayOf("uuid", compositionIdsParam);
  selectSimilarCompositionsStatement.setArray(1, compositionIdsParamArray);
  selectSimilarCompositionsStatement.setArray(2, compositionIdsParamArray);
  selectSimilarCompositionsStatement.setDouble(3, marginParam);
  selectSimilarCompositionsStatement.setDouble(4, marginParam);
  selectSimilarCompositionsStatement.setDouble(5, marginParam);
  selectSimilarCompositionsStatement.setDouble(6, marginParam);
  selectSimilarCompositionsStatement.setDouble(7, marginParam);
  selectSimilarCompositionsStatement.setDouble(8, marginParam);
  selectSimilarCompositionsStatement.setDouble(9, marginParam);
  selectSimilarCompositionsStatement.setDouble(10, marginParam);
  selectSimilarCompositionsStatement.setDouble(11, marginParam);
  selectSimilarCompositionsStatement.setDouble(12, marginParam);
  selectSimilarCompositionsStatement.execute();

  ArrayList<SelectSimilarCompositionsResultRow> list = new ArrayList<>();

  try (ResultSet resultSet = selectSimilarCompositionsStatement.getResultSet()) {
    while (resultSet.next()) {
      UUID idCol = (UUID) resultSet.getObject(1);
      double distanceColDouble = resultSet.getDouble(2);
      OptionalDouble distanceCol = resultSet.wasNull() ? OptionalDouble.empty() : OptionalDouble.of(distanceColDouble);
      list.add(new SelectSimilarCompositionsResultRow(idCol, distanceCol));
    }
  }

  return list;
}

Полный код Java здесь.

Вот Haskell:

selectSimilarCompositions ::
  -- | Specification of how to fold the result rows.
  Fold Model.SelectSimilarCompositionsResultRow result ->
  Statement Model.SelectSimilarCompositionsParams result
selectSimilarCompositions (Fold step init extract) =
  Statement sql encoder decoder True
  where
    sql =
      "with\n\
      \  reference as (\n\
      \    select\n\
      \      avg(f1) as f1,\n\
      \      avg(f2) as f2,\n\
      \      avg(f3) as f3,\n\
      \      avg(f4) as f4,\n\
      \      avg(f5) as f5\n\
      \    from composition\n\
      \    where\n\
      \      id = any($1)\n\
      \  )\n\
      \select\n\
      \  match.id,\n\
      \  (\n\
      \    abs(match.f1 - reference.f1) +\n\
      \    abs(match.f2 - reference.f2) +\n\
      \    abs(match.f3 - reference.f3) +\n\
      \    abs(match.f4 - reference.f4) +\n\
      \    abs(match.f5 - reference.f5) \n\
      \  ) / 5 as distance\n\
      \from reference\n\
      \right join composition as match on\n\
      \  match.id <> all($1) and\n\
      \  match.f1 >= reference.f1 - $2 and match.f1 <= reference.f1 + $2 and\n\
      \  match.f2 >= reference.f2 - $2 and match.f2 <= reference.f2 + $2 and\n\
      \  match.f3 >= reference.f3 - $2 and match.f3 <= reference.f3 + $2 and\n\
      \  match.f4 >= reference.f4 - $2 and match.f4 <= reference.f4 + $2 and\n\
      \  match.f5 >= reference.f5 - $2 and match.f5 <= reference.f5 + $2\n\
      \order by distance\n\
      \limit 100"
    encoder =
      (Model.selectSimilarCompositionsParamsCompositionIds >$< (Encoders.param (Encoders.nonNullable (Encoders.array (Encoders.dimension foldl' (Encoders.element (Encoders.nullable Encoders.uuid))))))) <> (Model.selectSimilarCompositionsParamsMargin >$< (Encoders.param (Encoders.nonNullable Encoders.float8)))
    decoder =
      fmap extract . Decoders.foldlRows step init $
        Model.SelectSimilarCompositionsResultRow
          <$> (Decoders.column (Decoders.nonNullable Decoders.uuid))
          <*> (Decoders.column (Decoders.nullable Decoders.float8))

Полный код Haskell здесь.

Здесь весь демонстрационный проект и сгенерированные артефакты.

Разработка сервера

На этом этапе остаётся лишь обернуть сгенерированный SDK в REST API. Как это делать, вы, наверняка, и так знаете.

Рекомендуем лишь обратить внимание на подход Spec-first, который таким же образом отбирая первичность у кода, позволяет вам генерировать код как сервера так и клиента из спецификации контракта в OpenAPI с гарантиями соответствия контракту. На эту тему вы найдёте множество отличных постов. Например, вот, здесь же на Хабре.

В итоге, единственный код, который вам останется написать самому - это конвертация между моделями API и БД. Оставим это за рамками данного поста.

Наблюдения

Интересная практика, не так ли? За буквально часовую работу проектирования мы получили качественные артефакты, которые требуются для любого проекта, интегрирующегося с базой данных, и которые руками программистов реализуются днями или неделями, притом, с нестабильным уровнем качества.

В результате мы получили гарантию, что схема и запросы синтаксически корректны и совместимы. Также мы получили интеграционные SDK для двух очень разных языков программирования, не написав ни строчки в них. Ещё мы избавили наших коллег, отвечающих за тестирование, от необходимости проверять интеграцию на банальные вещи, вроде соответствия типов и исполняемости запросов, чем также сократили длительность конвейера сборки, исключив необходимость прогонять подобные интеграционные тесты.

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

О dbfirst

Вы можете запросить доступ к Preview нашей SaaS-платформы на сайте dbfirst.ru. Также доступна опция внедрения ПО в ваши проекты.

Мы молодой проект и всегда рады обратной связи и предложениям. Для прямой связи пишите в Telegram: @wormholio.