Содержание

Реляционные базы данных

В этой главе мы поговорим о том, как работать с реляционными базами данных в Clojure. Большую часть описания займет библиотека clojure.java.jdbc и дополнения к ней. Вы узнаете, какие проблемы вас ждут в работе базам и как их решать в Clojure.

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

Базы данных, или сокращенно БД, бывают разных видов. Они различаются в архитектуре, способе хранения информации, способе работы с клиентом. Некоторые базы работают только на клиенте, то есть в рамках одного компьютера. Другие хранят только текст и оставляют вывод типов клиенту. Встречаются базы, где данные хранятся в оперативной памяти и пропадают после выключения.

В этой главе мы не ставим цель охватить как можно больше баз и способов работы с ними. Наоборот, сфокусируем внимание на том, что вас ждет в реальном проекте. Скорей всего это будет классическая реляционная база данных вроде PostgreSQL или MySQL. О них мы и будем говорить.

Реляционные базы данных называют так из за модели реляционной алгебры. Это изящный математическая модель с набором операций, например выборкой, проекцией, декартовым произведением и другими. Из модели следуют строгие правила о том, как работает та или иная операция. Поэтому базы данных работают не спонтанно, а по четким алгебраическим правилам. Из этих же правил следуют нормальные формы (первая, вторая и третья), доказать которые можно аналитически, а не на глаз.

Мы будем учить реляционную алгебру с самых азов. Обратитесь к статье в Википедии или книгам, где ее рассматривают без привязки к конкретной БД.

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

У записи есть особое поле, которое называют первичным ключом, Primary key. Ключ однозначно идентифицирует запись в таблице. Не может быть двух записей с одинаковым ключом. Чаще всего роль ключа играет число с автонумерацией, но иногда это строка, например электронная почта или полное имя. В редких случаях ключ может быть составным, то есть определяться парой полей, например полным именем и годом рождения. В этом случае мы допускаем, что в таблице могут быть полные тезки разных годов рождения или люди одного года с разными именами, но не то и другое вместе.

Внешним ключом называется поле, которые ссылается на первичный ключ другой таблицы. Для краткости его называют ссылкой. Примером ссылки может быть поле user_id в таблице профилей, которое указывает на поле id из таблицы пользователей.

CREATE TABLE users(
    id   serial primary key,
    name text not null
);

CREATE TABLE profiles(
    id      serial primary key,
    user_id integer not null references users(id),
    avatar  text
);

Cсылка отличается от простого числового поля: она гарантирует, что пользователь, на которого ссылаются, действительно существует. Обычное число этой гарантии не дает. Говорят, что ссылки поддерживают целостность базы. Целостность означает, в базе нет ссылок на несуществующие данные.

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

Из университета мы знаем, что связи бывают разных типов: один к одному или ко многим, многие ко многим. Профиль, который ссылается на пользователя — пример связи один к одному. Несколько заказов у пользователя — один ко многим. Тип связи легко задать ограничением на поле ссылки. Если в таблице профилей сделать поле user_id уникальным, не получится создать два профиля с одинаковым user_id. Это именно то, что нам требуется — чтобы у одного пользователя не могло быть два профиля. Связь “многие ко многим” сторят через таблицу-мост, которая содержит ссылки на другие таблицы.

Запросы

База данных обращается с миром через SQL (Structured Query Language) — структурированный язык запросов. Это текст, в котором описаны наши намерения — прочитать таблицу, добавить запись, обновить поле. Запросы имеют четкую структуру, которая чаще всего зависит от главного оператора. К ним относятся SELECT, INSERT, UPDATE, DELETE и другие операции над таблицами.

Существуют несколько стандартов SQL, обозначенных годами, когда они были приняты: SQL’92, ‘99, ‘2003 и другие. Каждая база данных поддерживает стандарт определенного года включая предыдущие. Вдобавок базы предлагают расширения — возможности, которые не входят в стандарт. При чтении документации обращайте внимание на но, относится ли конкретная возможность к стандарту или же это частное решение.

В аббревиатуре SQL последняя буква означает language, язык. Важно помнить, что это не язык программирования: на нем нельзя выразить алгоритм задачи. Говоря точнее, SQL не является полным по Тьюрингу языком: вы не сможете построить SQL-выражение на нем самом. Поэтому сложные запросы строят по частям в полноценном (полным по Тьюрингу) языке, например Java, Python, Clojure.

Современные базы данных предлагают встроенные языки и операторы, чтобы это исправить. Так, в PostgreSQL вам доступен блок DO, где работают переменные, циклы и даже перехват исключений. Но это частное решение, которое не входит в стандарт.

Доступ из Clojure

Теперь когда мы освежили теорию, перейдем к практической части — к Clojure. По умолчанию язык не предлагает доступа к базам данных. Чтобы работать с ними, подключают библиотеку clojure.java.jdbc — тонкую обертку над JDBC. Так называется встроенный в Java пакет для реляционных баз данных.

JDBC (Java Database Connectivity) отсчитывает свой возраст с 1997 года. Главная задача JDBC — предоставить общий API для разных типов баз. Для этого JDBC спроектирован из нескольких слоев. Потребители используют общий API, который не меняются в зависимости от “бекенда” — конкретной СУБД.

Для связи JDBC с бекендом служат драйверы. Каждый драйвер реализует бинарный протокол, по которому работает база. На сегодняшний день JDBC поддерживает все известные реляционные СУБД: PostgreSQL, MySQL, Oracle, SQLite и другие. Существуют драйверы для файловых хранилищ, например Excel, XML и CSV.

Для работы с какой-либо базой вам понадобится только драйвер к ней. Драйверы поставляются отдельно и должны быть объявлены в зависимостях проекта. JDBC автоматически находит и подключает драйвер; программисту не нужно заботиться об этом.

Основные сущности JDBC:

  • DriverManager — класс для управления драйвером конкретной БД. Получает соединение с базой, по которому в дальнейшем идет обмен данными.

  • PreparedStatement — подготовленное выражение. Так называется запрос, который прошел стадию подготовки в базе и теперь может быть вызван с разными параметрами.

  • ResultSet — источник, из которого читают результат запроса. Обычно приходит из метода executeQuery подготовленного выражения.

Пример запроса на Java:

import java.sql.*;

public class JDBCExample {

    static final String DB_URL = "jdbc:postgresql://127.0.0.1/test";
    static final String USER = "book";
    static final String PASS = "book";
    static final String QUERY = "SELECT * FROM users";

    public static void main(String[] args) {

        try {

            Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(QUERY);

            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id"));
                System.out.println("First name: " + rs.getString("fname"));
                System.out.println("Last name: " + rs.getString("lname"));
                System.out.println("Email: " + rs.getString("email"));
                System.out.println("Age: " + rs.getInt("age"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Доступ к базе данных из Java сводится к созданию этих классов и вызову их методов. В идеале при смене базы Java-код остается прежним, а меняется только синтаксис SQL. И хотя это верно лишь отчасти, JDBC решает основную задачу — предлагает единый доступ к разным бекендам.

Похожий стандарт существует и в других языках, например в Python. Там он называется DB API. Его задача такая же — обозначить минимальный набор правил, которым должны следовать драйверы баз данных.

Знакомство с clojure.java.jdbc

Из примера на Java видно, что даже простой запрос к базе требует многих строк кода. В Clojure принято упрощать рутинные задачи, и библиотека clojure.java.jdbc создана именно для этого. Она создает тонкую обертку вокруг классов DriverManager, PreparedStatement и других, чтобы получить результат буквально парой строк. Добавьте библиотеку и драйвер PostgreSQL в проект:

;; project.clj
[org.clojure/java.jdbc "0.7.8"]
[org.postgresql/postgresql "42.1.3"]

Предположим, сервер PostgreSQL работает локально на вашей машине. Вот как выполнить в нему запрос:

(def db {:dbtype "postgresql"
         :dbname "test"
         :host "127.0.0.1"
         :user "book"
         :password "book"})

(jdbc/query db "select 1 as value")

({:value 1})

Мы обозначили параметры подключения и передали их в функцию jdbc/query. Наш запрос возвращает постоянное число, поэтому он сработает, даже если в базе нет таблиц.

Пока мы не ушли вперед, разберем, что произошло. Переменная db называется JDBC-спекой. Это словарь с различными полями: хост, порт, пользователь и другими. Когда мы передаем словарь в функцию jdbc/query, библиотека получает из него соединение с базой. По завершении функции соединение закрывается. JDBC-спека принимает разные формы, и позже мы рассмотрим их.

Зависимость [org.postgresql/postgresql "42.1.3"] нужна для работы с базой PostgreSQL. JDBC-драйвер к ней выложен в репозитории Maven. Чтобы не распылять внимание на разные типы баз, остановим выбор на PostgreSQL до конца главы. В пользу PostgreSQL говорит зрелость и мощь проекта, его открытый код и бесплатный доступ. В России действует официальный вендор PostgresPro. Их силами написаны полезные расширения, переведена документация на русский язык.

Тот факт, что мы используем именно PostgreSQL, JDBC поймет из поля dbtype со значением “postgresql”. Он автоматически загрузит драйвер, так что импортировать его в Clojure не нужно. Чтобы запрос выполнился, у вас должен работать локальный сервер PostgreSQL. Наиболее быстрый способ запустить его с нужными настройками — вызывать Docker-образ. Команда

> docker run --rm -it -e POSTGRES_PASSWORD=pass postgres

запустит сервер на локальном порту 5432. Имя базы, пользователь и пароль определяются переменными окружения. Запуск этого образа мы обсуждали в прошлой книге о Clojure, а именно в четвертой и седьмой главах. Если у вас возникли сложности, изучите примеры оттуда.

Итак, если все настроено правильно, и вы увидели результат jdbc/query, примите поздравления. Первый этап пройден, и вы готовы двигаться дальше.

Основы clojure.java.jdbc

Пакет clojure.java.jdbc предлагает несколько функций для работы с базой. Ниже мы рассмотрим наиболее важные из них.

Чтение

Чаще всего вам придется работать с query, функцией выполнения запроса. Она принимает jdbc-спеку и запрос. Последний может быть либо строкой, например:

(jdbc/query db "select * from users")

либо вектором, где первый элемент — запрос с подстановками, а остальные элементы — параметры. Подобную запись еще называют SQL-вектором.

(jdbc/query db ["select * from users where id = ?" 1])

Знаки вопроса играют роль подстановок; позже параметры становятся на их место. Далее мы узнаем, в чем разница между вписанным в запрос значением и установкой параметра. Кроме запроса, query принимает параметры того, как обработать результат. Эти тонкости мы тоже обсудим позже.

Функция get-by-id предлагает быстрый доступ к записи по ее первичному ключу. От нас ожидают имя таблицы и значение ключа. Если в таблице users первичный ключ называется id, то чтобы получить первого пользователя, выполним:

(jdbc/get-by-id db :users 1)

{:id 1, :fname "John", :lname "Smith", :email "test@test.com", :age 25}

Обратите внимание, что функция вернет либо одну запись, либо nil. Это важное отличие от query, результат которой всегда коллекция.

По умолчанию get-by-id считает, что первичный ключ называется id. Если это не так, укажите его имя после значения:

(jdbc/get-by-id db :users 1 "account_id")

Функция find-by-keys выполняет отбор по нескольким полям. Предположим, мы хотим выбрать пользователей с именем John, которым 25 лет. Для этого укажем в find-by-keys имя таблицы и словарь вида {поле => значение}:

(jdbc/find-by-keys db :users {:fname "John" :age 25})

В базу уйдет следующий запрос:

SELECT * FROM users WHERE fname = $1 AND age = $2
parameters: $1 = 'John', $2 = '25'

Функция принимает любое число полей в словаре. Все они будут соединены оператором AND, потому что это наиболее частый случай. Для более тонкого отбора, например с помощью OR, функция не подойдет. Что делать в этом случае мы рассмотрим позже.

Заметим, что find-by-keys вернет список, даже если нашлась только одна запись. В некоторых случаях мы намеренно ищем первую запись, которая подходит отбору. Для этого заведем функцию find-first с теми же аргументами, которая оборачивает результат в first.

(defn find-first [db table filters]
  (first (jdbc/find-by-keys db table filters)))

В этом случае результат будет либо первой записью, либо nil:

(find-first db :users {:fname "John" :age 25})
{:id 1, :fname "John", :lname "Smith", :email "test@test.com", :age 25}

Вставка

Функция insert! добавляет запись в таблицу. Восклицательный знак на конце указывает на то, что вызов меняет состояние. Insert! принимает таблицу, в которую происходит запись. Значения могут быть переданы в разном виде. Наиболее частый сценарий — словарь вида {поле => значение}, например:

(jdbc/insert! db :users {:fname "Ivan"
                         :lname "Petrov"
                         :email "ivan@test.com"
                         :age 87})

Второй вариант функции ожидает отдельно списки полей и значений. Их длины должны быть равны:

(jdbc/insert! db :users
              [:fname :lname :email :age]
              ["Andy" "Stone" "andy@test.com" 33])

Оба вызова сводятся к подобному запросу:

INSERT INTO users ( fname, lname, email, age ) VALUES ( $1, $2, $3, $4 )
parameters: $1 = 'Andy', $2 = 'Stone', $3 = 'andy@test.com', $4 = '33'

Результат insert! отличается от типа базы, с которой вы работаете. Большинство баз отвечает на INSERT списком первичных ключей, которые появились в результате вставки. Имена этих полей тоже отличаются. MySQL вернет набор записей с ключом generated_key. PostgreSQL вернет запись целиком за счет оператора RETURNING *. Драйвер JDBC добавляет его в конец запроса автоматически.

Хотя insert! работает только с одной записью, он не оборачивает результат в first. В итоге вы получите список с одним словарем. Если новый ключ нужен для дальнейших действий, добраться до него можно так:

(let [get-pk
      (some-fn :id :generated_key)

      fields
      {:fname "Ivan"
       :lname "Petrov"
       :email "ivan@test.com"
       :age 87}

      db-result
      (jdbc/insert! db :users fields)]

  (-> db-result first get-pk))

Обновление

Функция update! обновляет таблицу. Она принимает JDBC-спеку, имя таблицы, словарь полей и WHERE-часть. Рассмотрим пример:

(jdbc/update! db :users {:age 50} ["id = ?" 7])

Третий параметр {:age 50} означает новые значения полей. Их может быть больше одного, например, вместе с возрастом мы решили обновить имя и почту. Последний параметр функции — SQL-вектор, который будет добавлен к запросу после оператора WHERE. В примере выше update! порождает следующий запрос:

UPDATE users SET age = $1 WHERE id = $2
parameters: $1 = '50', $2 = '7'

В векторе может быть несколько условий, соединенных с помощью AND или OR:

(jdbc/update! db :users {:email "test@test.com"} ["id = ? OR id = ?" 1 3])

Что равнозначно:

UPDATE users SET email = $1 WHERE id = $2 OR id = $3
parameters: $1 = 'test@test.com', $2 = '1', $3 = '3'

Заметим, что SQL-вектор для WHERE обязательный: у функции нет формы, чтобы вызвать ее без WHERE. Возникает вопрос, как выполнить UPDATE на всю таблицу? Редко, но случается, что мы хотим обновить все записи. Для этого передают вектор ["true"], что означает истину для каждой записи.

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

ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT false;

Чтобы выставить истину всем пользователям из Clojure, выполним:

(jdbc/update! db :users {:is_active true} ["true"])

Что равносильно:

UPDATE users SET is_active = $1 WHERE true
parameters: $1 = 't'

Функция update! возвращает список с одним числом. Оно означает число записей, которые были обновлены. Если вы не знаете заранее, сколько записей обновите, добавляйте это число в лог:

(let [[num-updated]
      (jdbc/update! db :users {:is_active true} ["true"])]
  (log/infof "%s records updated" num-updated))

;; 2021-07-05 09:47:19,581 INFO  my.module - 11 records updated

Удаление

Функция delete! удаляет записи из таблицы. Ее сигнатура похожа на update!: параметры подключения, имя таблицы и WHERE-часть. Вот как удалить пользователей старше 50 лет:

(jdbc/delete! db :users ["age >= ?" 50])

Итоговый запрос:

DELETE FROM users WHERE age >= $1
parameters: $1 = '50'

По аналогии с update!, в WHERE могут быть сложные условия с AND или OR. Результат функции — число удаленных записей.

Выполнение

Еще одна важная функция называется execute!. Она выполняет любой запрос, отличный от SELECT. Особенность SELECT в том, что он возвращает результат, который нужно прочитать и обработать. Функция execute!, напротив, выполняет команду без чтения результата.

На роль команды без результата подходит создание индекса. Вот как добавить индекс в таблицу пользователей по их имени:

(jdbc/execute! db "create index users_fname on users(fname);")

и удалить его:

(jdbc/execute! db "drop index users_fname;")

Другой пример — очистить таблицы после прогона тестов. Специальная команда TRUNCATE делает это более эффективно, чем DELETE. Она поддерживает несколько таблиц в одном запросе и каскадную очистку: все таблицы, что ссылаются на перечисленные, тоже очищаются.

(jdbc/execute! db "truncate users cascade;")

Еще один сценарий для execute! — временно отключить триггеры на время тестов или миграций. Предположим, мы завели триггер на обновление пользователей. Чтобы не порождать события в тестах, напишем фикстуру :once, которая отключит их перед прогоном:

(jdbc/execute! db "ALTER TABLE users DISABLE TRIGGER on_users_update")

а затем включит похожей командой, но с ENABLE вместо DISABLE.

Практика

Выше мы разобрали базовые CRUD-операции (Create, Read, Update, Delete) и служебную функцию execute!. Вместе они покрывают большую часть задач, которые ждут вас производстве. Чтобы закрепить знания, советуем отложить книгу и попрактиковаться.

Создайте новый проект и подключите к нему библиотеку clojure.java.jdbc. Настройте локальную базу данных (в Docker или нативно) и подключитесь к ней. Создайте простую таблицу и опробуйте изученные нами действия: вставку данных, выборку, поиск по ID и полям, обновление, удаление. Добавьте индекс на некоторые поля. Убедитесь, что все это можно сделать силами Clojure и SQL, не прибегая к сторонним программам (pgAdmin, DataGrip и другим).

Подробнее о запросах

Выше мы упоминали, что функция query принимает либо строку запроса, либо вектор с параметрами. Разберемся, в чем разница между подходами.

Представим, что нужно найти пользователя по известному id, например 13. Приведем варианты со строкой и вектором. В первом выражении значение 13 “вшито” в запрос, а во втором выступает параметром.

(jdbc/query db "select * from users where id = 13")

(jdbc/query db ["select * from users where id = ?" 13])

Строгое правило: если запрос зависит от внешних условий, используйте второй вариант с параметрами. Подставлять значение в строку функциями format или str считается грубой ошибкой и влечет риски. На это есть две причины: SQL-инъекции и подготовленные выражения. Обе важны и нуждаются в объяснении.

SQL-инъекции

Инъекцией называют случай, когда в запрос попадает вредоносный текст. Это возможно при ручной подстановке параметров в шаблон. Пик этой уязвимости пришелся на середину нулевых годов в эпоху расцвета PHP. Новый язык резко сократил порог входа в веб-разработку, и ей занялись в том числе те, кто не имел представлений о безопасности. Типичный запрос в базу на PHP выглядел так (пример из интернета):

$user_id = 13;
$query = sprintf("SELECT * FROM users WHERE id = %s", $user_id);
$result = mysql_query($query);

Разберемся, в чем проблема в выражением выше. Для начала перепишем его на Clojure:

(def user-id 13)

(jdbc/query db (format "select * from users where id = %s" user-id))

До тех пор, пока в user-id находится число, запрос работает без ошибок. Но что если параметр получили из адресной строки браузера? Предположим, кто-то исправил URL следующим образом:

http://site.com/users?user-id=13+OR+TRUE

Хакер понимает, что перед 13 идет выражение WHERE id = .... Идея в том, чтобы через оператор OR добавить условие, которое истинно всегда, например TRUE или 1=1. В результате даже если первое выражение ложно, второе будет истиной. Оператор OR сделает выбор в пользу второго, и запись попадет в выборку. В результате запрос вернет все записи из таблицы без отбора по id. Так вы раскроете чужие данные.

Ради интереса изменим user-id и выполним запрос с форматированием. Получим все записи из таблицы users.

(def user-id "13 OR TRUE")

(jdbc/query db (format "select * from users where id = %s" user-id))

;; ({:id 1, ...}, {:id 2, ...}, ...)

В интернете популярен комикс xkcd об SQL-инъекциях. В нем учитель спрашивает родителя, почему они назвали сына Robert'; DROP TABLE students;. Легко догадаться, что на сервере был следующий код:

(let [fname "Robert"
      query (str "select * from users where fname = '" fname "'")]
  (jdbc/query db query))

;; select * from users where fname = 'Robert';

Однако если задать переменной fname другое значение:

(let [fname "Robert'; DROP TABLE users;--"]
  ...)

, то получим:

select * from users where fname = 'Robert'; DROP TABLE users;--'

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

(let [fname (-> request :params "first_name")]
  ...)

Если подставить ее в запрос и выполнить, это приведет сначала к поиску по имени Robert, а затем к удалению таблицы пользователей. Опробуйте подобный трюк на локальной базе.

Атаки с UNION

На практике задача хакера не в том, чтобы удалить данные — их легко восстановить из резервной копии. Его цель — заполучить данные, к которым нет доступа. Рассмотрим все тот же пример с поиском пользователя. На этот раз вместо DROP TABLE мы напишем другой вредоносный SQL, а именно объединение исходного запроса с таким же, но без отбора. Для этого служит оператор UNION:

(let [fname "Robert' UNION select * from users--"
      query (str "select * from users where fname = '" fname "'")]
  (jdbc/query db query))

Результат:

select * from users where fname = 'Robert' UNION select * from users--'

Итоговый запрос вернет всех пользователей независимо от того, сколько их нашлось в первой части. Конечно, для подобных атак нужно знать структуру таблицы. Однако и эти данные можно получить SQL-инъекцией, добавив UNION с чтением таблиц information_schema.tables, pg_user и других. Таблицы из схемы information_schema хранят метаданные о других таблицах: их имена, состав и типы колонок. Если хакер доберется до этих сведений через инъекции, он узнает о базе все.

Кавычки

Даже если переменная приходит от надежного источника, вы не застрахованы от проблем с кавычками. Например, сотрудник вносит в базу данных фильм “Д’Артаньян и три мушкетёра”. Поскольку на клавиатуре нет знака апострофа, вполне вероятно, что после буквы Д сотрудник поставит верхнюю одинарную кавычку. В большинстве баз данных она служит для строковых литералов. При форматировании такой строки вы получите ошибку синтаксиса:

(def movie "Д'Артаньян и три мушкетёра")

(def sql (format "insert into movies (name) values ('%s')" movie))

;; insert into movies (name) values ('Д'Артаньян и три мушкетёра')

С точки зрения SQL строка заканчивается сразу после Д, и дальше идет что-то ошибочное.

Подстановка параметров сводит на нет эти угрозы. Прежде всего, JDBC следит за их типом. Если мы ожидаем поиск по ID, то при передаче строки получим ошибку даже если строка содержит только цифры. При этом ошибка возникнет на стороне JDBC до того, как запрос ушел в базу:

(jdbc/query db ["select * from users where id = ?" "1"])


Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2476).
ERROR: operator does not exist: integer = character varying
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 30

При подстановке строки драйвер экранирует ввод, то есть предваряет опасные символы особой последовательностью. В PostgreSQL для этого служит все та же одинарная кавычка. Вот как безопасно выбрать из базы всех Д’Артаньянов (обратите внимание на двойную кавычку в параметре $1):

(jdbc/query db ["select * from users where fname = ?" "Д'Артаньян"])

;; select * from users where fname = $1
;; parameters: $1 = 'Д''Артаньян'

Заметим, что параметры доступны не в любом месте. Например, нельзя задать параметром имя таблицы или состав полей. Поэтому запрос:

(jdbc/query db ["select * from ?" "users"])

не сработает из-за ошибки синтаксиса. А если вы хотите задать имена полей параметрами:

(jdbc/query db ["select ?, ?, ? from users" "id" "fname" "email"])

, то вас ждет разочарование. В результате окажутся именно строки “id” “fname” “email”, а не значения одноименных полей. Чтобы строить запросы, где таблицы и поля зависят от условий, прибегают к специальным библиотекам. Позже мы рассмотрим наиболее популярные из них.

Подготовленные выражения

Вторая причина в пользу параметров в том, с ними база получит выигрыш в скорости. Следует помнить, что даже простой запрос

select * from users where id = 1

включает несколько шагов. В целом они напоминают действия компилятора: для выражения 3 + 2 * 2 он выполняет лексический разбор и строит условное дерево:

[+ 3 [* 2 2]]

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

База данных тоже выполняет разбор и анализ запроса. На выходе получается так называемое Prepared Statement, подготовленное выражение. Это объектное представление запроса, который прошел анализ и теперь может быть выполнен. Схематично представим его на Clojure:

{:type :select
 :tables [:users]
 :fields [:id :fname :lname :email :age]
 :where [[:= :id ?]]
 :params [:integer]}

Смысловые части запроса разложены по слотам объекта. Обратите внимание, что литерал звёздочки замен на список полей. Известно число параметров и их тип. Осталось вызвать подготовленное выражение с конкретным ID, чтобы получить результат.

Из структуры выше понятно, почему таблица не может быть параметром. Если бы она менялась динамически, пришлось бы каскадно вычислять набор полей и их тип, что рушит идею Prepared Statement — вычислить все один раз. Наоборот, подстановка чисел 5, 15 или 99 на место id не влечет перестройку выражения.

Покажем, как работать с подготовленными выражениями в Clojure. Чтобы создать его, вызывают функцию jdbc/prepare-statement. Первым параметром она ожидает открытое TCP-соединение с базой (не то же самое, что JDBC-спека). Соединение получают функцией jdbc/get-connection из спеки:

(def conn
  (jdbc/get-connection db))

(def prep-stmt
  (jdbc/prepare-statement conn "select * from users where id = ?"))

Второй аргумент jdbc/prepare-statement — произвольный запрос со знаками вопроса на месте параметров. В переменной prep-stmt окажется объект с типом PreparedStatement. Чтобы выполнить его, передайте в query вектор, где первый элемент — подготовленное выражение:

(jdbc/query db [prep-stmt 1])

По аналогии напишем выражение для вставки записей и выполним его через execute!:

(def stmt-insert
  (jdbc/prepare-statement conn "insert into users (fname, lname, age, email) values (?, ?, ?, ?)"))

(jdbc/execute! db [stmt-insert "John" "Smith" 20 "john@test.com"])
;; (1)

Подготовленные выражения несут состояние, поэтому их нельзя объявлять на верхнем уровне модуля. Выражения адаптируют под систему, на которой основан проект. Например, если это Component, объявляют компонент, который зависит от базы. При запуске компонент объявляет нужные выражения и записывает в слоты, а в момент остановки закрывает их.

Напишем подобный компонент. Для экономии места заполним только одно выражение для поиска пользователя по номеру:

(defrecord UserManager
    [db
     conn
     stmt-get-by-id]

  component/Lifecycle

  (start [this]
    (let [conn (jdbc/get-connection db)

          stmt-get-by-id
          (jdbc/prepare-statement conn "select * from users where id = ?")]

      (assoc this
             :conn conn
             :stmt-get-by-id stmt-get-by-id)))

  (stop [this]
    (.close conn)
    (.close stmt-get-by-id)))

Теперь добавим протокол для действий с пользователями. Пока что в нем один метод для поиска по ID:

(defprotocol IUserManager
  (get-by-id [this id]))

Расширим тип UserManager этим протоколом. В теле get-by-id вызываем функцию jdbc/query, при этом соединение и подготовленное выражение приходят из слотов менеджера.

IUserManager

(get-by-id [this id]
  (first (jdbc/query conn [stmt-get-by-id id])))

Компонент в действии:

(def user-mgr
  (-> {:db db}
      map->UserManager
      component/start))

(get-by-id user-mgr 1)
(get-by-id user-mgr 2)

Запросы, выполненные через подготовленные выражения, как правило работают быстрее обычных, потому что не требуют разбора. Для нагруженных проектов это может оказаться критично.

Результат запроса

Рассмотрим, что предлагает библиотека для работы с ответом. По умолчанию функция query возвращает последовательность словарей. Структура каждого словаря одинакова: его ключи — поля выборки, приведенные к типу Keyword, а значения — то, что пришло для этого поля из базы. Пример с таблицей пользователей:

(jdbc/query db "select * from users")

({:id 1
  :fname "Ivan"
  :lname "Petrov"
  :email "test@test.com"
  :age 42}
 {:id 2
  :fname "John"
  :lname "Smith"
  :email "john@test.com"
  :age 20})

Функция jdbc/query оборачивает результат ResultSet в функцию jdbc/result-set-seq. Последняя приводит его к ленивой коллекции “кложурных” значений. Поскольку список словарей — наиболее удобная структура, именно этот способ задан по умолчанию. В особых случаях поведение query меняют так, чтобы получить данные в другом виде. Но прежде чем мы узнаем, как это сделать, вспомним, как устроен объект ResultSet.

Этот класс напоминает источник данных со внутренним указателем. По умолчанию он установлен до первого элемента. У ResultSet несколько методов, чтобы сдвигать указатель, но самый простой называется .next(). Результат метода означает, удался ли сдвиг (если мы в конце, получим ложь). Таким образом, обход ResultSet сводится к шаблону:

(while (.next rs)
  (println (.getInt rs "id"))
  (println (.getString rs "fname")))

Методы getInt, getString и другие вернут значения поля по его имени (если вызваны со строкой) или индексу (с числом).

Поскольку по умолчанию указатель стоит до первого элемента, начальный вызов .next сдвигает его на первый. Это существенно упрощает код. Если бы указатель стоял сразу на первом элементе, пришлось бы обработать его до входа в цикл, а потом скопировать тот же код внутрь цикла:

(println (.getInt rs "id"))
(println (.getString rs "fname"))

(while (.next rs)
  (println (.getInt rs "id"))
  (println (.getString rs "fname")))

Методы getInt и getString выше говорят о том, что мы знаем структуру ответа заранее. Это не всегда так. В общем случае данные о полях можно получить из объекта ResultSetMetaData. Он возвращает состав полей, их имена и типы. С его помощью задают функцию, которая принимает очередной элемент ResultSet и переводит его в словарь.

Модуль clojure.core предлагает функцию resultset-seq, чтобы преобразовать произвольный ResultSet в данные Clojure. В свободное время изучите ее код: он занимает 13 строк. Версия JDBC работает схожим образом, но учитывает больше параметров. Особенно изящно выглядит это место:

(let [...
      records
      (fn thisfn []
        (when (.next rs)
          (cons (zipmap keys (row-values)) (lazy-seq (thisfn)))))]
  (records))

Это ленивая коллекция, где известен только текущий элемент, а остальная часть — отложенный вызов (thisfn). Переменная keys в данном случае ссылается на вектор с именам полей, который получили из метаданных ResultSet. В Clojure ленивые коллекции часто описывают при помощи cons и рекурсивного вызова, обернутого в lazy-seq. Прием можно назвать паттерном; ознакомьтесь с ним ближе, если не понимаете, что происходит.

По умолчанию query “прогоняет” ленивую выборку через doall. В результате все элементы ResultSet будут прочитаны. В зависимости от логики программы чтением можно управлять, например специально не вычислять последовательность. При таком подходе от вас требуется ручное закрытие объекта ResultSet. О ленивости и ее влиянии на результат мы скажем чуть ниже.

Теперь когда мы знакомы с ResultSet, рассмотрим, в каком еще виде можно читать данные из базы.

Массивы

Список словарей удобен, но иногда требуется список векторов, например:

[[1 "Ivan" "Petrov" "test@test.com"]
 [2 "Ivan" "Petrov" "ivan@test.com"]
 [3 "John" "Smith" "john@test.com"]]

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

Параметр {:as-arrays? true} в query означает, что элемент списка будет вектором. При этом первый ряд содержит имена колонок:

(jdbc/query db "select * from users" {:as-arrays? true})

[[:id :fname :lname :email :age]
 [1 "Ivan" "Petrov" "test@test.com" 42]
 [2 "Ivan" "Petrov" "ivan@test.com" 87]
 [3 "John" "Smith" "john@test.com" 20]]

Предположим, мы хотим выгрузить пользователей в файл CSV. Для этого используем библиотеку clojure.data.csv и стандартный модуль IO:

;; [clojure.data.csv :as csv]
;; [clojure.java.io :as io]

(with-open [writer (io/writer "users.csv")]
  (->> (jdbc/query db "select * from users" {:as-arrays? true})
       (csv/write-csv writer)))

В папке проекта вы найдете файл users.csv. Проверьте содержимое в табличном редакторе. Напишите универсальную функцию для выгрузки любой таблицы. Она принимает имя таблицы, путь к CSV-файлу и, по желанию, набор полей на тот случай, если нужны только некоторые из них.

Список векторов полезен не только для CSV, но и для матричных данных или работы со статистикой.

Ключи

По умолчанию JDBC приводит имена полей к типу Keyword одноименной функцией. Если в выборке было поле user_name, в словаре окажется ключ :user_name. Это именно то, что нужно в большинстве случаев, поскольку Clojure поощряет кейворды. Но и здесь порой нужна тонкая настройка.

Параметр {:keywordize? false} означает, нужно ли приводить поля к кейвордам. По умолчанию он истина, но если передать ложь, получим строки:

(jdbc/query db "select * from users" {:keywordize? false})

({"id" 1
  "fname" "Ivan"
  "lname" "Petrov"
  "email" "test@test.com"
  "age" 42}
 {"id" 3
  "fname" "John"
  "lname" "Smith"
  "email" "john@test.com"
  "age" 20})

Этот параметр удобно использовать в паре с {:as-arrays? true}. С ним имена таблиц станут строками, и в CSV-файле у заголовков не будет двоеточия.

В поле :identifiers передают функцию для обработки исходного имени поля. По умолчанию используется clojure.string/lower-case, то есть приведение к нижнему регистру. С ним поле "VAL" становится :val:

(jdbc/query db "select 1 as VAL")
;; ({:val 1})

Вы можете передать функцию, которая оставляет регистр прежним или заменяет подчеркивание на дефис, чтобы получить :user-name из "user_name".

Параметр qualifier задает пространство полям выборки. Например, если мы выбираем пользователей, удобно назначить им пространство :user, чтобы позже не перепутать с другими сущностями.

(first (jdbc/query db "select * from users" {:qualifier "user"}))

{:user/id 2
 :user/fname "Ivan"
 :user/lname "Petrov"
 :user/email "ivan@test.com"
 :user/age 87}

Из того, что JDBC преобразует ключи к кейвордам, можно извлечь пользу. Когда в запросе участвует несколько таблиц с оператором JOIN, в результат попадают поля разных сущностей. Чтобы одноименные поля не перемешивались, их наделяют префиксом, например, user_ для полей пользователя и profile_ для профиля:

SELECT
  u.id     AS user_id,
  u.fname  AS user_fname,
  u.lname  AS user_lname,
  p.avatar AS profile_avatar
FROM users u
JOIN profiles p ON p.user_id = u.id;

Работать с такими ключами в Clojure неудобно. Вместо префикса и подчеркиваний мы ожидаем пространства и дефис. Если назначить полям псевдонимы вида "сущность/поле", получим нужные ключи. Однако псевдоним с косой чертой нужно взять в двойные кавычки, чтобы не нарушить синтаксис SQL:

SELECT
  u.id     AS "user/id",
  u.fname  AS "user/fname",
  u.lname  AS "user/lname",
  p.avatar AS "profile/avatar"
FROM users u
JOIN profiles p ON p.user_id = u.id;

Вывод psql:

 user/id | user/fname | user/lname | profile/avatar
---------+------------+------------+----------------
       1 | Ivan       | Petrov     | kitten.jpg

Если передать запрос в jdbc/query, получим удобные ключи:

({:user/id 1
  :user/fname "Ivan"
  :user/lname "Petrov"
  :profile/avatar "kitten.jpg"})

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

(jdbc/query db "
select
u.id as \"user/id\",
u.fname as \"user/fname\",
...
")

Ниже мы узнаем, как исправить эти недостатки.

Ленивость

По умолчанию результат jdbc/query не ленив, то есть полностью вычислен. Когда вы получили его, он уже не зависит от соединения с базой и объекта ResultSet. Если точнее, сперва из ResultSet получают ленивую последовательность, которая позже вычисляется с помощью doall (для списка словарей) или vec (когда :as-arrays? истина).

Ключ :result-set-fn определяет, что именно будет предпринято с ленивой коллекцией. По умолчанию это функция doall или vec в зависимости от параметров. Предположим теперь, мы хотим получить истинно ленивый результат без вычисления. Очевидно, в поле :result-set-fn просится функция identity. Но с ней запрос бросит исключение:

(jdbc/query db "select * from users" {:result-set-fn identity})

idError printing return value (PSQLException) at org.postgresql.jdbc.PgResultSet/checkClosed (PgResultSet.java:2758).
This ResultSet is closed.

Оно возникло потому, что после выполнения запроса JDBC закрывает объект ResultSet. А поскольку коллекция ленива, она пытается прочесть данные из ResultSet уже после его закрытия, что приводит к ошибке.

Функция :result-set-fn полезна в тех случаях, когда нужно обработать элементы по одному, не дожидаясь всего ответа. Часто это связано с операциями ввода-вывода. Представим, что нужно записать результат в файл. Будет неэффективно выгружать все данные в память и только затем в файл — гораздо лучше читать записи по одной и сразу записывать. В этом случае, даже если записей миллионы, мы не израсходуем память.

(jdbc/query db "select * from users"
            {:as-arrays? true
             :keywordize? false
             :result-set-fn
             (fn [rows]
               (with-open [writer (io/writer "users.csv")]
                 (csv/write-csv writer rows)))})

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

В редких случаях вам понадобится ручная работа с ResultSet. Доступ к нему предоставляет функция jdbc/db-query-with-resultset, которая принимает необработанный объект ResultSet. Вы можете сделать с ним что угодно, но помните: после выхода из функции он будет закрыт.

Как правило, работа с ResultSet сводится к итерации (while (.next ...)) и чтению нужных полей. Такой подход даже более эффективен, чем result-set-fn, потому что не производит ленивую коллекцию, которая тоже потребляет ресурсы.

(jdbc/db-query-with-resultset
 db "select * from users"
 (fn [rs]
   (while (.next rs)
     (let [id (.getInt rs "id")
           fname (.getString rs "fname")
           lname (.getString rs "lname")]
       (println id fname lname)))))

В примере выше мы просто печатаем данные на экран. На практике мы бы отправили их в сеть, в файл или поток.

Транзакции

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

Приведем типичные случаи с транзакциями. Это может быть сбор денег на проект (краудфандинг). Каждый раз, когда кто-то совершил платеж, итоговая сумма пересчитывается. Если делать это не в транзакции, можно пропустить платеж, который случился во время пересчета, и итог будет неверным.

Даже если с базой работает один человек, транзакции необходимы. Представим каталог фотографий. Каждая из них может быть в нескольких альбомах, иметь свои теги, ссылки на места и людей, запись в поисковом индексе. Теперь мы удаляем фотографию. Очистка должна произойти либо во всех таблицах, либо нигде. Представьте, что фотография ушла из альбома, но появляется в поиске — это раздражает.

Технически транзакция означает, что два и более запроса окружены командами BEGIN и COMMIT. Приведем сеанс psql со вставкой данных в транзакции. В первый запрос мы добавили оператор RETURNING, чтобы получить созданный базой первичный ключ. Мы ссылаемся на него во втором запросе.

BEGIN;
INSERT INTO users (fname, email) VALUES ('Ivan', 'ivan@test.com') RETURNING id;
-- 59
INSERT INTO profiles (user_id, avatar) VALUES (59, 'cat.jpg');
COMMIT;

Если хотя бы один запрос потерпит неудачу, COMMIT не сработает, и обе таблицы останутся нетронутыми.

Рассмотрим, как управлять транзакциями в Clojure. Если выполнить два запроса в лоб:

(jdbc/insert! db :users {:fname "Ivan"})
(jdbc/insert! db :users {:fname "Huan"})

, в логах PostgreSQL увидим следующие записи:

BEGIN
INSERT INTO users ( fname ) VALUES ( $1 )
parameters: $1 = 'Ivan'
COMMIT

BEGIN
INSERT INTO users ( fname ) VALUES ( $1 )
parameters: $1 = 'Huan'
COMMIT

Каждый запрос оказался в отдельной транзакции, что совсем не то, что мы ожидали. Так происходит потому, что функции на изменение (с восклицательным знаком на конце) принимают необязательный аргумент :transaction?. По умолчанию он истина, что значит выполнить запрос в отдельной транзакции. Если мы укажем ложь для этого флага:

(jdbc/insert! db :users {:fname "Ivan"} {:transaction? false})
(jdbc/insert! db :users {:fname "Huan"} {:transaction? false})

, транзакций не будет совсем:

INSERT INTO users ( fname ) VALUES ( $1 )
parameters: $1 = 'Ivan'

INSERT INTO users ( fname ) VALUES ( $1 )
parameters: $1 = 'Huan'

Еще одна причина, по которой у нас не выходит транзакция, состоит в том, что запросы должны протекать в рамках одного TCP-соединения. По умолчанию JDBC открывает новое соединение на запрос, что неэффективно и не позволяет управлять транзакциями. Это значит, мы должны открыть соединение, начать в нем транзакцию и направить на него все запросы. Тело транзакции должно быть обернуто в try/catch, чтобы в случае исключения выполнить ROLLBACK. Если тело прошло без ошибок, выполнить COMMIT. В конце закрыть соединение.

Очевидно, если каждый раз писать все это вручную, мы допустим ошибку. Должна быть единая точка (макрос или фукнкция), которая управляет процессом. Обертка JDBC на Clojure предлагает макрос with-db-transaction. Он принимает JDBC-спеку, параметры транзакции и произвольный код. Коротко его логику можно описать так:

  • получить TCP-соединение из JDBC-спеки.
  • Сохранить его прежние флаги autocommit, readonly и другие.
  • Принудительно задать autocommit=false.
  • Связать транзакционное соединение с символом из макроса.
  • Выполнить тело макроса в блоке let с этой переменной. Все запросы должны использовать новое соединение.
  • Если не было исключений, вызвать COMMIT.
  • Если было исключение, вызвать ROLLBACK и снова бросить его.
  • В блоке finally вернуть соединению начальные флаги.

Быстрая проверка макроса:

(jdbc/with-db-transaction [tx db]
  (jdbc/insert! tx :users {:fname "Ivan"})
  (jdbc/insert! tx :users {:fname "Huan"}))

На этот раз оба запроса окажутся в одной транзакции:

BEGIN
INSERT INTO users ( fname ) VALUES ( $1 )
parameters: $1 = 'Ivan'

INSERT INTO users ( fname ) VALUES ( $1 )
parameters: $1 = 'Huan'
COMMIT

Свойства транзакции определяет первый параметр макроса. Это вектор, от которого ожидают по крайней мере два символа по принципу let. Первый символ — имя, с которым будет связано транзакционное соединение. Второй символ указывает на текущую JDBC-спеку. Транзакционное соединение принято называть tx или trx. Буква “t” указывает на транзакцию.

Типичная ошибка в работе с макросом — установить транзакцию, но пользоваться прежним соединением. В этом случае пользы от макроса нет.

(jdbc/with-db-transaction [tx db]
  (jdbc/insert! db :users {:fname "Ivan"})
  (jdbc/insert! db :users {:fname "Huan"}))

Более сложный пример, когда запросы зависят друг от друга. Сперва создаем пользователя, затем профиль со ссылкой на него:

(jdbc/with-db-transaction [tx db]

  (let [[{user-id :id}]
        (jdbc/insert! tx :users {:fname "Ivan"})

        [{profile-id :id}]
        (jdbc/insert! tx :profiles {:user_id user-id :avatar "cat.jpg"})]

    {:user/id user-id
     :profile/id profile-id}))

Код вернет словарь с номерами нового пользователя и его профиля. Для ясности мы добавили ключам пространства.

Третьим параметром макрос принимает словарь опций транзакции. Наиболее интересен ключ :isolation, уровень изоляции. Он определяет, как управлять данными в параллельных транзакциях, например, на каком этапе каждая их них обнаружит результат другой.

Предположим, одновременно открылись две транзакции. Первая изменила запись, но еще не выполнила COMMIT. Вторая читает эту же запись. Получит ли она исходную запись, потому что еще не было COMMIT, или измененную?

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

Уровень изоляции управляет подобными случаями. Современные базы данных выделяют следующие уровни:

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

Мы расположили их по нарастанию безопасности в работе с параллельными транзакциями. Например, с уровнем READ UNCOMMITTED вы прочтете изменения той транзакции, которая еще не была зафиксирована и, возможно, подлежит откату. Полагаться на эти данные будет ошибочно.

Максимальный уровень SERIALIZABLE гарантирует полную безопасность с транзакциями. Когда он включен, транзакции выполняются так, словно следуют одна за другой, а не параллельно. Промежуточные уровни защищают от определенных проблем, которые в терминах баз данных называют “фантомное чтение”, “неповторяющееся чтение” и другие.

Сказанное выше не значит, что нужно выставлять наивысший уровень SERIALIZABLE. Хоть он и гарантирует безопасность, но замедляет обработку данных. По умолчанию базы данных SQL Server и PostgreSQL используют READ COMMITTED, которого хватает в большинстве случаев. Меняйте уровень изоляции только если четко понимаете, чем вас не устраивает текущий.

В этой главе мы не рассматриваем особенности каждого уровня. Их изучение требует длинных примеров с таблицами и параллельными запросами. Кроме того, эта тема не касается Clojure, потому что не зависит языка программирования. Для быстрого знакомства прочтите статью в Википедии. Еще глубже материал изложен в документации PostgresPro, в том числе на русском языке.

Разберем, как задать уровень изоляции в Clojure. Макрос with-db-transaction принимает ключ :isolation. Его возможные значения — кейводы:

  • :none
  • :read-committed
  • :read-uncommitted
  • :repeatable-read
  • :serializable

Внутри макроса эти значения транслируются в константы Java. Пример с уровнем, заданным по требованию:

(jdbc/with-db-transaction
  [tx db {:isolation :serializable}]
  ...)

Если выполнить код, в логах PostgreSQL увидим записи:

SHOW TRANSACTION ISOLATION LEVEL
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN
...
COMMIT
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED

Макрос временно заменяет уровень изоляции на указанный. После фиксации транзакции или ее отката восстанавливается прежний уровень. Уровень по умолчанию можно задать в настройках базы данных. В этом случае не придется передавать ключ :isolation в макрос каждый раз.

JDBC-спека с состоянием

Читатель заметит, что в словаре спеки нет состояния, и не ясно, откуда оно приходит. Это особенность Clojure-обертки над JDBC: с базой можно работать как с состоянием, так и без него. Но чтобы ваш код был эффективным, вы должны понимать, что происходит с состоянием спеки.

Под состоянием понимают TCP-соединение, по которому происходит обмен данных. В словаре

(def db {:dbtype "postgresql"
         :dbname "test"
         ...})

никакого соединения нет. Если передать словарь в функцию query, произойдет следующее:

  • из словаря библиотека построит JDBC URL — длинную строку, похожую на адрес страницы в интернете. Для конфигурации выше получим что-то похожее на “jdbc:postgresql://127.0.0.1:5432/test”.
  • По этому адресу получим открытое TCP-соединение. Через него функция query отправит и прочитает данные.
  • После работы query соединение закрывается.

Аналогично работают функции update!, delete! и другие. Все они сводятся к единой точке, где создается новое соединение, происходит обмен данных по нему, затем соединение закрывается.

Открывать соединение на каждый запрос расточительно. Чтобы использовать его повторно, JDBC предлагает макрос with-db-connection. Макрос связывает обычную спеку db с ее копией, где открыто постоянное соединение. Внутри макроса мы передаем копию в функции JDBC.

(jdbc/with-db-connection [conn db]
  (jdbc/query conn "select 1"))

Макрос открывает соединение и добавляет его в спеку под ключом :connection. Функция, которая строит соединение по словарю, проверяет этот ключ. Если в нем уже есть соединение, функция вернет его же. Вместе это работает как кэш. Исследуем переменную conn и убедимся, что в ней хранится открытое соединение:

(jdbc/with-db-connection [conn db]
  (print conn))

{:dbtype "postgresql"
 :dbname "test"
 ...
 :connection #object[org.postgresql.jdbc.PgConnection 0x46b37678 "org.postgresql.jdbc.PgConnection@46b37678"]}

Чтобы оценить издержки на соединение с базой, напишем замер. Выполним примитивный запрос многократно, в первом случае — каждый раз с новым соединением, во втором — с одним и тем же.

(time (dotimes [_ 1000]
        (jdbc/query db "select 1")))
;; "Elapsed time: 19097.466607 msecs"

(time
 (jdbc/with-db-connection [conn db]
   (dotimes [_ 1000]
     (jdbc/query conn "select 1"))))
;; "Elapsed time: 1680.252291 msecs"

Разница в одиннадцать раз в пользу постоянного соединения. Очевидно, что первый вариант — соединение на запрос — подойдет только разовым скриптам. Для веб-приложений и долгоиграющих систем нужно постоянное соединение. Это подводит нас к JDBC-спеке с состоянием.

Чтобы управлять спекой с минимальными усилиями, обратимся к библиотеке Mount. Мы познакомились с ней в первой книге в главе про системы. Объявим компонент, который при запуске открывает TCP-соединение и дописывает его в поле :connection спеки:

(require '[mount.core :as mount :refer [defstate]])

(defstate DB
  :start
  (assoc db :connection
         (jdbc/get-connection db))

В фазе остановки мы закрываем соединение и возвращаем исходную спеку без состояния:

  :stop
  (let [{:keys [connection]} DB]
    (when connection
      (.close connection))
    db))

Вызовем (mount/start), чтобы включить компонент. Теперь каждый запрос к базе использует постоянное соединение, что быстрее:

(jdbc/query DB "select 1")

Пул соединений

У открытого соединения одна проблема: его легко потерять. Проведем эксперимент: когда компонент базы включен, перезапустите сервер базы данных. Компонент придет в негодность: при попытке использовать соединение мы получим ошибку о том, соединение закрыто.

Execution error (PSQLException) at org.postgresql.jdbc.PgConnection/checkClosed (PgConnection.java:766).
This connection has been closed.

Чтобы исправить компонент, придется перезапустить его — вручную вызвать функции start и stop, которые откроют новое соединение.

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

О пулах мы говорили в первой книге, поэтому приведем только пример. Подключите библиотеку HikariCP в проект:

;; [hikari-cp "2.8.0"]
(require '[hikari-cp.core :as cp])

Функция cp/make-datasource принимает конфигурацию пула и строит объект типа DataSource. Его записывают в словарь спеки с ключом :datasource. По аналогии с :connection, JDBC проверяет это поле, прежде чем открывать соединение. Если источник задан, библиотека берет соединение из него. В случае с :datasource мы получим уже открытое соединение, и запрос пройдет так же быстро, как в случае с :connection. Новый компонент:

(def pool-config ;; truncated
  {:minimum-idle       10
   :maximum-pool-size  10
   :adapter            "postgresql"
   :username           "book"
   :server-name        "127.0.0.1"
   :port-number        5432})

(defstate DB
  :start
  (let [pool (cp/make-datasource pool-config)]
    {:datasource pool})
  :stop
  (-> DB :datasource cp/close-datasource))

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

Состояние поддерживают не только с помощью Mount. Это может быть и другой фреймворк, например Component. Вот как задать JDBC-спеку, которая реагирует на методы start и stop:

(def db
  (with-meta
    {:dbtype "postgresql"
     :dbname "test"
     :host "127.0.0.1"
     :user "book"
     :password "book"}

    {'com.stuartsierra.component/start
     (fn [this]
       (assoc this :connection
              (jdbc/get-connection this)))

     'com.stuartsierra.component/stop
     (fn [{:as this :keys [connection]}]
       (when connection
         (.close connection))
       (dissoc this :connection))}))

Компонент включают и передают в функции JDBC, затем отключают.

(def db-started (component/start db))
(jdbc/query db-started "select 1")
(component/stop db-started)

Выше мы привели ручной запуск компонента, но на практике им управляет система. Если вы не уверены в том, что происходит, обратитесь к главе о системах из первой книги. По аналогии напишите компонент для пула соединений.

SQLite

Особое место в нашем обзоре занимает SQLite. Это не серверная, а клиентская база данных. Ее главное отличие от PostgreSQL или MySQL в том, что в SQLite нет центрального сервера, который обслуживает клиентов. SQLite распространяется как библиотека; при компиляции проекта она становится его частью.

SQLite хранит данные в файлах по принципу один файл — одна база, что удобно для обмена. Например, внутренняя система раз в сутки выгружает базу SQLite с абонентами и их последними платежами. Эти данные показывает веб-приложение, и ему не нужно обращаться ко внутренней системе.

SQLite подходит для хранения настроек или историй действий пользователя. Браузеры и другие настольные программы ведут несколько SQLite-баз. Ради интереса исследуйте папки профилей Google Chrome, Firefox или Telegram — в каждой их них вы найдете файлы с расширением *.db или *.sqlite.

SQLite ведет себя как обычная реляционная база данных. Ее основные сущности — таблицы, первичные ключи и индексы. База поддерживает транзакции, когда изменения коснулись либо нескольких таблиц за раз, либо ни одной. Несколько клиентов могут читать данные без ограничений, но для записи это не так: только один клиент может изменять данные. При попытке сделать это второй клиент получит ошибку, что база заблокирована на запись. Поэтому SQLite используют в основном в программах, рассчитанных на одного пользователя. В этом случае данные меняются по очереди, а не параллельно.

Особенность SQLite в том, что она может хранить данные в памяти без обращения к диску. Это полезно в случаях, когда программа интенсивно читает данные. Если загрузить их в память целиком, доступ к данным будет быстрее. Иногда программа выполняет долгий расчет и использует базу в памяти как временное хранилище. Позже ее можно сбросить на диск.

Как и в PostgreSQL, работа с SQLite сводится к функциям query, insert!, update! и другим. Разница в том, что, во-первых, нужно подключить драйвер базы, а во-вторых, изменить JDBC-спеку. Добавьте драйвер SQLite в проект:

[org.xerial/sqlite-jdbc "3.36.0"]

Скорее всего, на вашем компьютере установлен браузер Chrome или Firefox. Чтобы не создавать SQLite-базу вручную, позаимствуем ее из профиля браузера. Google Chrome хранит историю в файле History (без расширения). Путь к профилю отличается в зависимости от системы:

  • Windows 10: C:\Users<username>\AppData\Local\Google\Chrome\User Data\Default
  • MacOS: /Users//Library/Application Support/Google/Chrome/Default
  • Linux: /home//.config/google-chrome/default

В нашем случае спека JDBC выглядит так:

(def db
  {:classname "org.sqlite.JDBC"
   :subprotocol "sqlite"
   :subname "/Users/ivan/Library/Application Support/Google/Chrome/Default/History"})

Заметим, что для доступа к базе Chrome должен быть выключен, иначе вы получите ошибку.

Для начала узнаем, какие таблицы содержит база. Прочитаем их из главной таблицы sqlite_master, в которой записан реестр всех сущностей (таблиц, индексов, триггеров и так далее).

(->> (jdbc/query db "SELECT name FROM sqlite_master WHERE type = 'table'")
     (mapv :name))

Этот запрос вернет список строк:

["meta"
 "urls"
 "sqlite_sequence"
 "visits"
 "visit_source"
 "keyword_search_terms"
 "downloads"
 "downloads_url_chains"
 "downloads_slices"
 "segments"
 "segment_usage"
 "typed_url_sync_metadata"
 "content_annotations"
 "context_annotations"
 "clusters"
 "clusters_and_visits"]

Чтобы увидеть последние сайты, что вы посещали, выполните запрос:

(jdbc/query db "SELECT * FROM urls order by last_visit_time desc limit 10")

Приведем один из элементов списка. В нем указан адрес страницы, ее заголовок, число посещений и время последнего посещения в формате наносекунд.

{:id 98,
 :url "https://github.com/igrishaev",
 :title "igrishaev (Ivan Grishaev)",
 :visit_count 5,
 :typed_count 0,
 :last_visit_time 13261760285085441,
 :hidden 0}

Таблица downloads хранит данные о загруженных файлах:

(jdbc/query db "SELECT * FROM downloads limit 10")

Очистка этих таблиц приведет к удалению истории в браузере. Когда вы нажимаете на кнопку “Очистить историю”, браузер выполняет запросы DELETE FROM ко всем таблицам базы.

Пользуясь случаем, исследуйте другие таблицы истории Chrome. Обратите внимание, насколько легко сторонней программе получить доступ к истории браузера. Именно так устроены некоторые приложения: взамен на бесплатную установку они читают историю браузера и передают ее фирмам, которые показывают рекламу.

База в памяти

При работе с SQLite в режиме in-memory возникают следующие особенности. Для начала измените путь к файлу на строку :memory: Это особое значение, которое предписывает драйверу переключиться на другой тип хранилища. Далее вас ждет странность: если создать таблицу и записать в нее значение, получим ошибку, что таблицы не существует.

(def db
  {:classname   "org.sqlite.JDBC"
   :subprotocol "sqlite"
   :subname     ":memory:"})

(jdbc/execute! db "create table users (id integer)")

(jdbc/query db "select * from users")
;; [SQLITE_ERROR] SQL error or missing database (no such table: users)

Дело в том, что база в памяти существует только в рамках открытого соединения. Если в JDBC-спеке нет состояния, то каждый запрос протекает в новом соединении, которое тут же закрывается. Драйвер SQLite устроен так, что с закрытием соединения пропадает база, созданная в памяти. В примере выше мы создали и уничтожили две базы данных.

Чтобы все работало правильно, установим соединение один раз. Для этого объявим переменную db* (со звездочкой), “заряженную” постоянным соединением:

(def db
  {:classname   "org.sqlite.JDBC"
   :subprotocol "sqlite"
   :subname     ":memory:"})

(def db*
  (assoc db :connection
         (jdbc/get-connection db)))

Теперь запросы обращаются к одной и той же базе:

(jdbc/execute! db* "create table users (id integer)")
(jdbc/insert! db* :users {:id 1})
(jdbc/query db* "select * from users")

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

Сложные типы

Когда драйвер базы читает данные, он приводит их типам того языка, с которым вы работаете. Для простых типов, которые еще называют скалярными, эта связь очевидна: тип int становится условным Integer, text — String и так далее.

Гораздо интересней комплексные типы, то есть те, что состоят из нескольких значений. К ним относятся точки на плоскости, географические объекты, хэш-таблицы, JSON. Как правило, нет четких правил о том, как согласовать передачу этих данных, а если и есть, то они меняются от проекта к проекту.

Конечно, даже самый сложный объект можно хранить в виде строки. Для этого нужны две функции: привести объект к строке и восстановить его. Предположим, есть таблица платежей. У платежей может быть много полей: личные данные отправителя и получателя, реквизиты их банков и так далее. Чтобы не создавать колонку под каждое поле, обозначим только основные поля, а остальные данные запишем в виде JSON.

(jdbc/execute! db "CREATE TABLE payments (id INTEGER, sum INTEGER, meta TEXT)")

В поле meta окажутся все поля, для которых нет выделенных колонок. Подготовим функции для конвертации:

(require '[cheshire.core :as json])

(defn meta->str [meta-info]
  (json/generate-string meta-info))

(defn str->meta [db-string]
  (json/parse-string db-string keyword))

Запишем в базу платеж с произвольными метаданными:

(jdbc/insert!
 db :payments
 {:id 1
  :sum 99
  :meta (meta->str {:year "2021"
                    :from "test@test.com"
                    :BIK "332233"
                    :alerts 0})})

Вот как восстановить поле :meta после выборки:

(let [result
      (jdbc/query db "select * from payments")]
  (for [row result]
    (update row :meta str->meta)))

({:id 1
  :sum 99
  :meta {:year "2021" :from "test@test.com" :BIK "332233" :alerts 0}})

Недостаток в том, что функции meta->str и str->meta нужно вызывать каждый раз, когда мы пишем в базу объект или читаем его. Если в запросе участвуют другие сложные типы, это усложнит код. Можно забыть эти функции и получить вместо объекта строку, вызовет ошибку в дальнейшем коде.

Удачный подход в том, чтобы найти бутылочное горлышко, через которое проходят все значения из базы и обратно. Если изменить поведение там, функции query, insert! и другие сработают одинаково в любом месте проекта. JDBC предлагает такие горлышки для чтения и записи. Рассмотрим их на примере дат и JSON.

Сначала проверим, что случится, если выбрать из базы дату. Для этого обратимся к переменной PostgreSQL с именем current_timestamp:

(-> (jdbc/query db "SELECT current_timestamp AS now")
    first
    :now
    type)

;; java.sql.Timestamp

Тип значения будет Timestamp. Это класс-обертка над обычным java.util.Date, чтобы привести его к SQL-виду. Если мы хотим передать дату в параметрах, она тоже должна быть объектом Timestamp. Класс Date вызовет ошибку типов:

(jdbc/query db ["select ? as now" (new java.util.Date)])

;; Can't infer the SQL type to use for an instance of java.util.Date.
;; Use setObject() with an explicit Types value to specify the type to use.

Но не всегда удобно работать с Timestamp. Многие проекты на Java и Clojure опираются на другие классы для работы со временем. Удачным примером станет JodaTime, наиболее продвинутая в этой области библиотека. Ее классы не имеют ничего общего с Timestamp, и при попытке передать их получим похожее исключение.

Рассмотрим, как подружить JDBC с другим типом времени. Роль бутылочного горлышка играют протоколы IResultSetReadColumn, ISQLParameter и ISQLValue. Вместе они определяют, как обработать значение на пути из базы и обратно. Протокол IResultSetReadColumn отвечает за чтение. Он реализует метод с тремя параметрами:

(result-set-read-column [val rsmeta idx])

Аргументы:

  • val: исходное значение, которое пришло из базы данных;
  • rsmeta: метаданные результата: сводная информация о наборе полей, их именах и типах. Экземпляр класса ResultSetMetaData;
  • idx: числовой индекс текущего поля (считается от нуля).

При чтении результата JDBC прогоняет все поля через этот метод. По умолчанию протокол реализует Object, главный класс в дереве наследования. Для него функция result-set-read-column просто вернет val. Это значит, все значения останутся в том виде, в котором пришли из драйвера JDBC.

Изменим протокол так, чтобы для типа Timestamp мы бы получили экземпляр DateTime из библиотеки JodaTime. Для этого импортируем класс и расширим протокол:

(:import org.joda.time.DateTime)

(extend-protocol jdbc/IResultSetReadColumn
  java.sql.Timestamp
  (result-set-read-column [val _rsmeta _idx]
    (new DateTime (.getTime val))))

Поскольку мы не заинтересованы в параметрах rsmeta и idx, предварим их подчеркиваниями. Из переменной val с типом Timestamp получим экземпляр DateTime. Проще всего это сделать передачей миллисекунд. Метод getTime класса Timestamp вернет их из val; далее передаем их в конструктор DateTime. Теперь любая дата, выбранная из базы, придет в новом виде. Проверим себя:

(first (jdbc/query db "SELECT current_timestamp AS now"))

{:now #object[org.joda.time.DateTime 0x49a269a4 "2021-07-22T10:23:51.136+03:00"]}

С той же легкостью работает установка параметра. Для этой цели служит протокол ISQLValue и его метод sql-value. Он принимает значение и должен вернуть его JDBC-совместимый вариант. Если мы передали DateTime, на выходе должен быть Timestamp. Расширим протокол с обратным преобразованием:

(extend-protocol jdbc/ISQLValue
  DateTime
  (sql-value [val]
    (new java.sql.Timestamp (.getMillis val))))

Для проверки добавим в таблицу платежей поле с датой:

(jdbc/execute! db "ALTER TABLE payments ADD COLUMN created_at TIMESTAMP WITH TIME ZONE")

Теперь запишем в нее дату и прочитаем ее:

(jdbc/insert! db :payments {:id 2 :sum 100 :created_at (DateTime/now)})

Теперь можно спокойно читать и писать даты с типом, отличным от Timestamp. Если понадобится другой тип даты, его легко добавить протоколом. Предположим, вместо пакета JodaTime вы предпочитаете встроенный класс java.time.Instant. По аналогии расширьте протоколы JDBC для этого класса.

Третий протокол ​​ISQLParameter полезен в случаях, когда установка параметра происходит особым образом. По умолчанию JDBC вызывает метод .setObject подготовленного выражения, чего хватает в большинстве случаев. Однако в будущем мы рассмотрим передачу массива в параметры, где вместо .setObject должен быть другой метод. Там мы и познакомимся с третьим протоколом.

Организация кода

Расширяйте протокол в отдельном модуле: подобный код не должен переплетаться с логикой приложения. Это глобальное действие, которое затрагивает систему в целом. Кроме того, сопоставление типов базы и языка меняются от проекта к проекту. Для наших дат будет правильно создать отдельные модули project.time-joda и project.time-instant. В зависимости от того, какой тип даты мы используем в проекте, подключим нужный модуль.

Импорт одного из этих модулей должен быть в коде, который загружается всегда, например в .core-модуле проекта. Будет хорошим тоном добавить комментарий о том, что импорт расширяет протокол:

(ns project.core
  (:require
   project.time-joda ;; extends JDBC protocols

   [cheshire.core :as json]
   ...))

Без комментария импорт станет неочевидным. Кажется, что этим модулем когда-то пользовались, но со временем забыли убрать из секции :require. Ваш коллега может удалить импорт и затем бороться со странным поведением базы. Если вы проверяете код линтером, добавьте пространство имен в особый список. У линтера Joker опция называется :ignored-unused-namespaces:

{:ignored-unused-namespaces [project.time-joda
                             ...]}

При запуске линтера вы не получите предупреждения о том, что модуль project.time-joda подключен, но не используется в коде. Похожая настройка встречается и в других линтерах.

В теле require модули, которые изменяют состояние, обычно помещают без квадратных скобок и псевдонимов (имени после :as). Полезно отделить их пустой строкой от других модулей, чтобы выделить на общем фоне в секции ns.

Поддержка JSON

Как видно из примеров с датами, управлять типами в JDBC нетрудно. Теперь мы пойдем дальше и добавим мощную функциональность: построим связь между JSON-типом в PostgreSQL и Clojure. Но сначала вспомним, в чем особенность этого типа и где он полезен.

JSON — это запись данных на языке, близком к JavaScript. Кроме простых типов вроде чисел и строк, стандарт поддерживает коллекции — массивы и объекты — и их произвольную вложенность. С помощью JSON данные передают по сети и хранят в файлах. Формат используют для конфигурации приложений.

Поддержка JSON на уровне базы означает, что отныне это уже не строка, а сложный тип. Для примера запишем один и тот же объект в виде JSON с разным порядком ключей. Если сравнить две строки в лоб, получим ложь из-за отличия в символах:

select '{"foo": 1, "bar": 2}' = '{"bar": 2, "foo": 1}';
-- false

Если же указать строкам тип jsonb, получим истину, потому что порядок ключей в объектах не важен.

select '{"foo": 1, "bar": 2}'::jsonb = '{"bar": 2, "foo": 1}'::jsonb;
-- true

Кроме сравнения, тип json(b) предлагает операторы поиска по отдельному полю, объединения и пересечения объектов и массивов и многое другие. С ними вы можете выполнить SQL-запрос, не прибегая к последующей обработке на Clojure.

Предположим, поле attributes товара содержит JSON-объект с характеристиками, которые часто меняются:

create table items (id serial, title text, attrs jsonb);

insert into items values (1, 'Cap', '{"size": "XL", "color": "red", "country": "China"}');
insert into items values (2, 'Hoodie', '{"size": "L", "color": "black", "country": "Germany"}');

Чтобы выбрать товары красного цвета, добавьте условие в запрос как в примере ниже. Оператор ->> означает извлечь поле color из объекта в виде текста.

select id, title from items where attrs->>'color' = 'red';

 id | title
----+-------
  1 | Cap

PostgreSQL поддерживает индексы по отдельным полям JSON. Если вы часто ищете товары цвету, добавьте похожий индекс, и скорость чтения возрастет:

CREATE INDEX idx_items_color ON items((attrs->>'color'));

Тип json(b) в PostgreSQL полезен для хранения нечетких данных, то есть таких, чья структура меняется и зависит от многих факторов. Один из примеров мы уже рассмотрели — это характеристики товаров. В зависимости от типа товара (техника, одежда, еда) у него могут быть самые разные характеристики. Если держать их в отдельной таблице, это породит лишние связи и таблицы-мосты, что усложнит запросы. Поэтому иногда дешевле хранить одно JSON-поле и проверять его спекой в Clojure.

Другой случай, когда полезен JSON — объемные и сложные документы. Это может быть бухгалтерский договор или история болезни пациента. В виде JSON эти сущности занимают несколько экранов с глубокой вложенностью. Разложить их на отдельные таблицы стоит времени и сил, а также влечет рост числа таблиц в базе. Проще хранить сущность как неделимый объект и изменять его целиком.

База PostgreSQL поддерживает два типа JSON: одноименный (json) и с частичкой “b” на конце (jsonb). Последнее означает binary, двоичный JSON. Разница между типами в их внутреннем устройстве. Тип json работает с данными наивно: он хранит строку в исходном виде (с пробелами и переносами строк) и на каждую операцию разбирает ее. Наоборот, jsonb анализирует строку и хранит ее в двоичном виде, что не требует разбора при обращении. Это несколько снижает запись JSON-данных, но ускоряет выборку. Скорее всего, подойдет jsonb: он поддерживает больше операций включая сравнение, что иногда необходимо.

Если выбрать из базы поле с типом JSON, получим загадочный PGObject:

(def attrs
  (-> (jdbc/query db "SELECT * from items")
      first
      :attrs))

(type attrs)
;; org.postgresql.util.PGobject

Этот класс служит для данных, специфичных для PostgreSQL. У класса всего для поля: тип значения в базе и значение в виде строки. Исследуем эти данные:

(.getType attrs)
;; "jsonb"

(.getValue attrs)
;; "{\"size\": \"XL\", \"color\": \"red\", \"country\": \"China\"}"

Кроме json(b), в PGObject могут быть данные другого типа, например сетевой адрес:

(first (jdbc/query db "select '127.0.0.1'::inet as IP"))

{:ip #object[org.postgresql.util.PGobject 0x721ff1be "127.0.0.1"]}

Разбор PGObject протекает в два этапа. Во-первых, расширим протокол IResultSetReadColumn:

(extend-protocol jdbc/IResultSetReadColumn
  org.postgresql.util.PGobject
  (result-set-read-column [pg-obj _rsmeta _idx]
    ...))

Пока что не ясно, что писать на месте многоточия. Как мы выяснили, PGobject служит для разных типов. Поэтому проверим тип поля и для каждого варианта вызовем свой обработчик. Проще всего это сделать оператором case:

(extend-protocol jdbc/IResultSetReadColumn
  org.postgresql.util.PGobject
  (result-set-read-column [pg-obj _rsmeta _idx]

    (let [pg-val (.getValue pg-obj)
          pg-type (.getType pg-obj)]

      (case pg-type
        ("json" "jsonb")
        (json/parse-string pg-val keyword)

        "inet"
        (java.net.InetAddress/getByName pg-val)

        ;; else
        pg-obj))))

Обратите внимание, что реализации json и jsonb совпадают, поэтому для них служит одно тело. В данном случае мы парсим значение библиотекой Chesire. Проверим теперь, что типы читаются корректно:

(first (jdbc/query db "SELECT * from items"))

{:id 1, :title "Cap", :attrs {:size "XL", :color "red", :country "China"}}

Для сетевого адреса получим экземпляр Inet4Address:

(first (jdbc/query db "select '127.0.0.1'::inet as IP"))

{:ip #object[java.net.Inet4Address 0x38d6ba76 "/127.0.0.1"]}

Форма case выше не совсем удобна: если понадобится новый тип, придется дописывать тело case. Возможен более гибкий разбор. Создайте мультиметод pg->clojure, чья функция-диспетчер возвращает тип поля:

(defmulti pg->clojure (fn [pg-obj]
                        (.getValue pg-obj)))

Теперь код result-set-read-column сводится к вызову мультиметода:

(result-set-read-column [pg-obj _rsmeta _idx]
  (pg->clojure pg-obj))

Добавьте реализации мультиметода для json(b); позже вы расширите его другими типами.

Запись JSON

Теперь когда мы читаем JSON из базы, позаботимся о записи. Для этого расширим протокол jdbc/ISQLValue так, чтобы любая коллекция Clojure становилась объектом PGobject. Но для этого нужно понять, что мы имеем в виду под термином “любая коллекция”.

Протоколы в Clojure устроены на базе интерфейсов и иерархии классов. Если мы хотим, чтобы вектор и словарь отзывались на какое-то действие, нужно найти в их родословной общий интерфейс и расширить его протоколом. Чтобы увидеть интерфейсы, которые реализует конкретная коллекция, выполните выражение:

(supers (type {}))

#{clojure.lang.IMeta
  java.lang.Runnable
  java.io.Serializable
  ...
  clojure.lang.IPersistentCollection}

Вы получите множество интерфейсов Java. Среди прочих нас интересует последний элемент, IPersistentCollection. Вы найдете его в результате supers для типа вектора, списка или ленивой последовательности:

(supers (type []))
(supers (type ()))
(supers (type (repeat 1)))

Расширим ISQLValue этим протоколом, чтобы любая Clojure-коллекция становилась объектом PGobject с типом jsonb:

(extend-protocol jdbc/ISQLValue
  clojure.lang.IPersistentCollection
  (sql-value [val]
    (doto (new org.postgresql.util.PGobject)
      (.setType "jsonb")
      (.setValue (json/generate-string val)))))

Теперь атрибуты товара можно задать обычным словарем:

(jdbc/insert! db :items
              {:id 5
               :title "The Catcher in the Rye"
               :attrs {:author "J. D. Salinger"
                       :genre "novel"
                       :year 1951}})

То же самое методом execute!: поместим словарь в SQL-вектор:

(jdbc/execute! db ["INSERT INTO items VALUES (?, ?, ?)"
               5
               "The Catcher in the Rye"
               {:year 1951 :genre "novel" :author "J. D. Salinger"}])

Предположим теперь, что мы храним теги товара — короткие строки с его описанием. Проще всего это сделать полем tags с типом jsonb, в который запишем вектор строк:

alter table items add column tags jsonb;

Обновим теги у только что созданной книги:

(jdbc/update! db :items
              {:tags ["book" "novel"]}
              ["id = ?" 5])

и прочитаем ее:

(jdbc/get-by-id db :items 5)

{:id 5
 :title "The Catcher in the Rye"
 :attrs {:year 1951 :genre "novel" :author "J. D. Salinger"}
 :tags ("book" "novel")}

Технически возможно записать в теги словарь, а в атрибуты — список. С точки зрения базы это будут правильные типы JSON. Чтобы избежать ошибки, проверяйте данные на уровне Clojure до записи в базу. Проще всего это сделать отдельной функцией insert-item, которая сверяет аргументы по спеке.

Для начала объявим спеку полей таблицы items:

​​(require '[clojure.spec.alpha :as s])

(s/def ::item-fields
  (s/keys :req-un [::id ::title]
          :opt-un [::attrs ::tags]))

Затем расширим необязательные поля атрибутов и тегов:

(s/def ::attrs
  (s/map-of keyword? any?))

(s/def ::tags
  (s/coll-of string?))

В функции insert-item мы задали pre-выражение, в котором проверяем поля спекой макросом s/assert.

(defn insert-item [db fields]
  {:pre [(s/assert ::item-fields fields)]}
  (jdbc/insert! db :items fields))

По умолчанию макрос отключен, потому что предназначен для отладки. Включите его выражением

(s/check-asserts true)

и вызовите функцию с неправильными полями:

(insert-item db {:id 5
                 :title "The Catcher in the Rye"
                 :attrs {:author "J. D. Salinger"
                         :genre "novel"
                         :year 1951}
                 :tags ["book" "novel" nil]})

Вы получите ошибку спеки, и запись в базу не произойдет. То же самое можно сделать макросом fdef и функцией instrument из модуля спеки. Мы подробно разобрали эти возможности в первой книге во второй главе. Обратитесь к ней, если вам не понятны термины, о которых мы говорим.

Проблема nil в JSON

При записи JSON в базу проверяйте значение на nil. Если этого не делать, произойдет следующее: значение nil станет строкой "null" и займет место в базе. Предположим теперь, вы хотите найти записи с пустыми атрибутами. Если выполнить запрос

select id from items where attrs is null

, вы не получите записи с полем attrs, равным "null", потому что это по-прежнему тип json(b), отличный от NULL. Другими словами, вы ввели новое пустое значение. Запрос с двойной проверкой выглядит сложнее:

select id from items where attrs is null or attrs = 'null';

Очевидно, одна проверка удобней двух, особенно если json(b) встречается в разных таблицах. Следите за тем, чтобы пустое значение было выражено одним способом. Лучше всего для этого подходит стандартный NULL. С точки зрения Clojure это значит, что проведение к PGObject происходит только если исходное значение отлично от nil.

Для начала проверим текущее поведение ->pg-object в случае nil:

(->pg-object nil)

#object[org.postgresql.util.PGobject 0x7c24e232 "null"]

Получим PGobject с "null", что нас не устраивает. С проверкой на some? аргумент nil вернет nil:

(defn ->pg-object [data]
  (when (some? data)
    (doto (new PGobject)
      (.setType "json")
      (.setValue (json/generate-string data)))))

(->pg-object nil) ;; nil

Типизированные записи

Мы установили правило: при передаче параметров любая коллекция становится объектом JSON. Однако PostgreSQL предлагает и другие типы данных, например точки, линии и прямоугольники. Рассмотрим, что вернет база данных, если выбрать точку:

(first (jdbc/query db "select '(1, 2)'::point"))

{:point #object[org.postgresql.geometric.PGpoint 0x28ebf39e "(1.0,2.0)"]}

Получим объект класса PGpoint. Логично привести его в словарю {:x 1.0, :y 2.0}, чтобы упростить дальнейшую работу. Однако при записи точки в базу такой словарь будет преобразован в JSON, из-за чего получим ошибку типов.

Наиболее простое решение в том, чтобы задать типизированную запись, или рекорд. Такая запись повторяет все свойства словаря, но при этом объявляет новый Java-класс. Если расширить протоколы JDBC этим классом, мы сохраним функциональность JSON, потому что класс стоит ниже протокола IPersistentCollection в цепочке наследников.

Объявим запись с двумя слотами координат:

(defrecord Point [x y])

Расширим протокол IResultSetReadColumn типом PGpoint. Импортируем его, чтобы не указывать весь путь. Метод result-set-read-column вернет экземпляр записи Point с заполненными слотами:

(import 'org.postgresql.geometric.PGpoint)

(extend-protocol jdbc/IResultSetReadColumn
  PGpoint
  (result-set-read-column [pg-point _rsmeta _idx]
    (new Point (.-x pg-point) (.-y pg-point))))

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

(first (jdbc/query db "select '(1, 2)'::point"))

{:point {:x 1.0, :y 2.0}}

Расширим ISQLValue для перевода точки в тип JDBC. Обратите внимание: поскольку запись ведет себя как словарь, мы распаковали поля x и y на уровне сигнатуры.

(extend-protocol jdbc/ISQLValue
  Point
  (sql-value [{:keys [x y]}]
    (new PGpoint x y)))

Передать точку в запрос можно несколькими способами. Первый — вызвать конструктор класса оператором new. Второй — преобразовать обычный словарь в запись функций map-><RecordName>. Эта функция появится в том же пространстве имен, где объявлена запись. Третий — предварить словарь тегом с полным именем записи. Все три способа вернут экземпляр Point.

(jdbc/query db ["select ? as point" (new Point 1 2)])
(jdbc/query db ["select ? as point" (map->Point {:x 1 :y 2})])
(jdbc/query db ["select ? as point" #book.db.Point{:x 1 :y 2}])

По аналогии легко сопоставить другие типы базы с записями Clojure. В качестве тренировки расширьте протоколы JDBC для работы с линиями и прямоугольниками.

Проблемы SQL

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

(let [events (get-events http-client)
      sql "select * from users u
join profiles p on p.user_id = u.id
where u.is_active
and p.created_at > ..."
      result (jdbc/query db sql)]
  ...)

Длинные запросы в коде смотрятся чужеродно. При его чтении нам приходится переключать контекст, чтобы понять, что происходит в запросе. Ситуация ухудшается с копированием: часто необходим такой же запрос, но с небольшими правками. Это усложнит поддержку, ведь однажды критерии выборки могут измениться для всех запросов.

Предположим, в какой-то момент в таблице появился флаг отложенного удаления. Пользователь закрыл учетную запись, и теперь нужно удалить ее из системы. Отныне при выборке пользователя нужно добавить условие “не удален”:

SELECT ... FROM users WHERE ... AND NOT is_deleted

Если мы скопировали прежний запрос во многие места, придется искать их вручную и дописывать AND NOT is_deleted.

SQL-выражения со временем растут. Когда мы только запустили проект, в нем мало сущностей и таблиц, но с развитием бизнеса их число увеличивается. Например, выборка популярных товаров с их категориями и оценками может занять экран. Если вы вставили подобный SQL прямо в код, то навредите проекту: станет тяжелее поддерживать и код, и SQL.

Мы подводим читателя к вопросу о том, как управлять запросами в промышленных масштабах. В разных языках и фреймворках приняты разные подходы. Наиболее популярен способ с промежуточным слоем, который преобразует данные из базы в объекты. Например, выборка из таблицы users вернет список объектов User. И наоборот, методы этого класса преобразуются в SQL-запросы. Если изменить поля модели и вызвать условный метод .save(), сработает запрос UPDATE (если это текущая запись) или INSERT (если это новая модель, а не полученная из базы).

Подход с сопоставлением базы и объектов называется ORM (Object–relational mapping). Также встречается его разновидность ActiveRecord (сокращенно AR). Как правило, любой современный язык предлагает библиотеки для ORM или AR.

Попытки создать ORM были и в Clojure, но они не получили широкого распространения. Практика показывает, что в Clojure легко работать с базой на низкому ровне. Причина в том, что коллекции Clojure обладают важными свойствами: они неизменяемы и на них действуют сотни функций стандартной библиотеки. В Clojure список словарей уже достаточен для дальнейшей работы, и нет смысла приводить их к другим сущностям.

Библиотеки, которые упрощают работу с SQL, можно разделить на две группы: шаблоны и построители. Рассмотрим каждую группу, их преимущества и недостатки.

Шаблоны

Подход с шаблоном состоит из двух этапов. Сначала вы создаете файл с расширением .sql и пишете в нем запросы. Каждый запрос предваряется шапкой из нескольких закомментированных строк. В них указаны имя запроса, описание и характеристики. На месте параметров стоят именованные метки. Пример подобного файла:

-- :name list-users :?
-- :doc Get all the users
select * from users;

-- :name get-user-by-id :? :1
-- :doc Get a single user by ID
select * from users
where id = :id;

Специальный код читает этот файл. Для каждого запроса он порождает одноименную функцию, которая принимает JDBC-спеку и словарь параметров. Результат функции станет результатом выполнения запроса. Так, для запроса get-user-by-id получим функцию, вызвав которую с подключением и параметром, увидим результат:

(get-user-by-id db {:id 1})

{:id 1, :fname "Ivan" ...}

Способ с сырым SQL, который порождает функции, несет несколько преимуществ. Прежде всего, в вашем проекте единый источник SQL. Все запросы собраны в одном файле, и не нужно искать их по всему проекту.

Некоторые фирмы проводят аудит запросов к базам данных, чтобы выявить узкие места. DBA-инженеру будет удобно посмотреть .sql файл и оптимизировать его. Если же вы обращаетесь к базе с помощью ORM, придется искать конечные запросы в логах базы. При этом учтите, что запросы, составленные программой, читаются хуже, чем написанные человеком.

Если вам потребуется особый оператор, например слияние JSON-объектов или пересечение интервалов, вы скопируете его из документации в файл. В случае с ORM вам придется расширять классы или прибегать к условному методу .raw, в котором вы напишете тот самый SQL.

Наконец, запросы в исходном виде удобны для отладки. Если вы составили сложный запрос с помощью ORM, то должны убедиться, что получили именно то, что ожидали. Когда вы сразу пишете SQL, этот шаг отпадает. Любой запрос можно выполнить в PGAdmin или схожей программе с минимальными усилиями.

Принцип “SQL как шаблон” реализует библиотека HugSQL. Подключите ее в проект (понадобится свежая версия tools.reader):

[org.clojure/tools.reader "1.3.6"]
[com.layerware/hugsql "0.5.1"]

Будем работать с пользователями. Создайте файл resources/sql/queries.sql с теми же запросами, что мы рассмотрели:

-- :name list-users :?
-- :doc Get all the users
select * from users;

-- :name get-user-by-id :? :1
-- :doc Get a single user by ID
select * from users
where id = :id;

Параметр :name означает имя будущей функции. По желанию добавьте :doc с описанием, и тогда редактор покажет справку к функции в месте ее вызова. Особенно интересны параметры :? и :1, что указаны после :name. Это флаги, которые определяют, как обработать запрос и его результат.

Знак вопроса означает, что SQL-выражение следует выполнить функцией jdbc/query. Библиотека JDBC отличает чтение от исполнения. Чтение предполагает обработку результата, в то время как для исполнения его может либо не быть, либо он нам не интересен. Поэтому мы указываем :? в запросах на чтение.

Флаги :* и :1 означают сколько записей прочитать из ответа: все или одну. Когда мы ищем заведомо одну запись, логично указать :1, чтобы получить либо ее, либо ничего. В противном случае вам придется оборачивать результат в (first...), что неудобно. Если не задана единица, по умолчанию получим все записи.

Преобразуем запросы в функции: вызов def-db-fns принимает путь к файлу и наполняет текущее пространство новыми функциями.

(require '[hugsql.core :as hugsql])
(hugsql/def-db-fns "sql/queries.sql")

Выполним их:

(list-users db)
;; ({:id 1 :fname "Ivan", ...}, ...)

(get-user-by-id db {:id 1})
;; {:id 1 :fname "Ivan", ...}

Теперь подготовим вставку пользователей:

-- :name create-user :i!
insert into users (fname, lname, email, age)
values (:fname, :lname, :email, :age);

После изменений в файле сгенерируйте функции еще раз. Появится новая функция create-user:

(create-user db {:fname "Ioann" :lname "Smith" :email "test@test.com" :age 30})
;; {:id 76, :fname "Ioann", ...}

В примере со вставкой флаг :i! служит для оператора INSERT. Он означает вернуть первичные ключи записей, которые произвела база данных. Схожий флаг :<! работает с выражением вида INSERT ... RETURNING ..., что значит “вставить и вернуть”. Она полезна, когда полям таблицы заданы значения по умолчанию.

Эти и другие флаги служат синтаксическим сахаром для параметра :command. Если вам трудно запомнить флаги, запишите их в явном виде:

​– :command :query:command :insert:command :execute:command :returning-execute

Если нужно создать несколько записей в транзакции, обернем функции в макрос with-db-transaction и укажем tx первым аргументом:

(jdbc/with-db-transaction [tx db]
  (create-user tx {:fname "User1" ...})
  (create-user tx {:fname "User2" ...}))

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

HugSQL предлагает множество полезных функций. Среди прочих отметим сниппеты — повторяющиеся участки кода. Если один и тот же фрагмент SQL встречается много раз, можно задать его отдельно и позже сослаться на него. Предположим, мы хотим контролировать, какие поля пользователя получит клиент. Объявим их тегом :snip:

-- :snip user-fields
id, fname, lname

Запрос на выборку пользователя изменится. На месте звездочки (все поля) укажем :snip:user-fields:

-- :name get-user-by-id :? :1
-- :doc Get a single user by ID
select :snip:user-fields from users
where id = :id;

Чтобы выполнить запрос, передайте в параметр :user-fields вызов сниппета. Так мы получим только те поля, что объявили.

(get-user-by-id db {:id 1 :user-fields (user-fields)})

;; {:id 1, :fname "Ivan", :lname "Petrov"}

В сниппете может быть что угодно: набор WHERE-условий, постраничная навигация с помощью LIMIT и OFFSET и так далее. Однако помните, что чем больше сниппетов, тем менее декларативен ваш SQL. С ними вы теряете преимущества чистого SQL, о которых мы говорили.

Выражения

Другая полезная функция HugSQL — шаблонная система в запросах. С помощью особого синтаксиса в запрос можно добавить код на Clojure, который “склеивает” отдельные его части. Прием полезен, когда запрос меняется в зависимости от параметров.

Предположим, мы пишем поиск пользователей. В поиске участвуют несколько полей: имя, город, год рождения, при этом обязательно только имя. В этом случае мы не можем составить запрос как в примере ниже:

-- :name find-users :?
SELECT * FROM users
WHERE fname = :name
  AND city = :city
  AND year_birth = :year-birth
LIMIT 10

Если не указать город или год рождения в параметрах, получим ошибку:

~~~clojure(find-users db {:name “Ivan”})

;; Execution error (ExceptionInfo) at hugsql.core/validate-parameters! (core.clj:83). ;; Parameter Mismatch: :city parameter data not found.


Очевидно, отбор по опциональным полям должен быть только если их значения не пустые. Версия запроса с условием выглядит так:

~~~sql
-- :name find-users :?
select * from users
where fname = :name
--~ (when (:city params) "and city = :city")
--~ (when (:year-birth params) "and year_birth = :year-birth")
limit 10

Синтаксис --~ предваряет однострочное выражение на Clojure. Оно должно вернуть строку или nil. Внутри доступна переменная params — словарь параметров, которые передали в запрос. Выше мы проверяем: если заданы поля с городом или возрастом, добавить к WHERE отбор по ним.

Выражение

(find-users db {:name "Ivan" :city "Chita"})

порождает следующий запрос:

SELECT * FROM users
WHERE fname = $1
  AND city = $2
LIMIT 10
parameters: $1 = 'Ivan', $2 = 'Chita'

Обратите внимание, что он в точности совпадает с тем, что мы написали в файле. Это полезно при чтении логов и в отладке.

Кроме однострочных выражений, HugSQL предлагает многострочные по следующим правилам:

/*~ (if ... */
SQL if true
/*~*/
SQL if false
/*~ ) ~*/

Форма /*~*/ означает переход к следующей форме Clojure. Обратите внимание, что последняя строка несет закрывающую скобку для верхнего if. Контроль за скобками остается таким же, как и в остальном коде на Clojure.

Расширим выборку пользователей следующим правилом: если не задан сниппет полей, выберем их все. Для этого проверим параметр :user-fields на пустоту и подставим либо :snip:user-fields, либо *.

-- :name get-user-by-id :? :1
-- :doc Get a single user by ID
select
/*~ (if (:user-fields params) */
:snip:user-fields
/*~*/
*
/*~ ) ~*/
from users
where id = :id;

Результат:

(get-user-by-id db {:id 1})

;; {:id 1 :fname "Ivan" :lname "Petrov" :email "...", :age ...}


(get-user-by-id db {:id 1 :user-fields (user-fields)})

;; {:id 1 :fname "Ivan" :lname "Petrov"}

Более сложный пример с шаблонами — связанные таблицы по условию. Предположим, в списке фильтров появилось галочка “только с фотографией”. В базе данных фотографии хранятся в отдельной таблице photos, которая связана с пользователями внешним ключом. Чтобы фильтровать пользователей по фотографии, в запросе должно быть внутреннее соединение таблиц (INNER JOIN). При этом не всегда, а только если установлен параметр. Все вместе дает нам шаблон:

-- :name find-users2 :?
select * from users u
/*~ (when (:with-photo? params) */
join photos p on p.user_id = u.id
/*~ ) ~*/

Если выполнить запрос с флагом, таблица photos окажется в запросе:

(find-users2 db {:with-photo? true})

;; select * from users u
;; join photos p on p.user_id = u.id

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

-- :snip user-fields
/*~ (if (:root? params) */
*
/*~*/
id, fname, lname
/*~ ) ~*/

Теперь запрос с флагом вернет все поля. Обратите внимание, что параметр :root? передан именно снипетту, а не основному запросу. Это гарантирует, что одноименные поля запроса и снипетта не вступят в конфликт.

(get-user-by-id db {:id 1 :user-fields (user-fields {:root? true})})

HugSQL предлагает многие другие возможности. Предлагаем ознакомиться с ними на странице проекта.

Завершая раздел, отметим похожие библиотеки. Yesql предлагает аналогичный подход: по файлу запросов библиотека порождает функции Clojure. Главное отличие в том, что при генерации вы указываете JDBC-спеку по умолчанию. В этом случае ее не нужно передавать в каждую функцию явно. Для транзакций и особых случаев спеку указывают через словарь опций. Yesql портирована на Python, Ruby, Golang и другие популярные языки.

Вторая библиотека называется Conman. Это обертка над HugSQL с некоторыми упрощениями. Conman входит в состав фреймворка Luminus и является важной его частью. Вы можете использовать Conman в любом проекте, не обязательно построенном на Luminus.

Построители

Для SQL существует иной подход, известный как Query Builder или построитель запросов. Это объект, который хранит запрос в структурированном виде: список полей, таблиц, условий и так далее. Вызывая условные методы .addWhere, .addJoin и другие, мы наполняем состояние запроса. Метод .toString или .render построит SQL из его частей. Ниже — пример построителя запросов на Java:

String sql = QueryBuilder.select("u.*", "p.*")
.from("users", "u")
.join("profiles", "p").on("p.user_id = u.id")
.where("not u.is_deleted")
.toString();

Похоже устроены библиотеки SQLAlchemy и Knex.js в Python и JavaScript.

Принцип работает и в Clojure с той разницей, что вместо объекта используют привычные вектор и словарь. Специальная функция обходит структуру и строит по ней строку SQL. Поскольку данные неизменяемы, каждая операция над запросом вернет новый запрос. За счет этого снижается риск ошибки, что мы случайно изменим запрос, на который ссылается другой код.

Библиотека HoneySql работает по принципу “SQL как данные”. Добавьте ее в проект:

[com.github.seancorfield/honeysql "2.0.0-rc5"]

Создайте простой запрос в виде словаря:

(def query
  {:select [:*]
   :from [:users]
   :where [:= :id 1]})

Очевидно, ключи :select, :from и :where означают одноименные части SQL. Важно, что переменная query имеет структуру. В нее легко добавить новое поле или условие функциями assoc-in и update-in.

Функция sql/format принимает подобный словарь и возвращает SQL-вектор. Первый элемент этого вектора — строка запроса, остальные элементы — параметры. В нашем случае мы получим вектор:

(require '[honey.sql :as sql])

(sql/format query)
;; ["SELECT * FROM users WHERE id = ?" 1]

Обратите внимание, что значение 1 стало параметром, а не частью запроса. Другими словами, запрос SQL не оказался таким:

SELECT * FROM users WHERE id = 1

Как мы обсуждали в начале главы, внедрение значений в запрос чревата ошибками. Скорей всего, значение 1 пришло со стороны и поэтому может содержать махинации для инъекций.

В HoneySql любое значение, отличное от символа и кейворда, означает параметр. Числа, булево и строки станут параметрами запроса, а в SQL на их месте будет знак вопроса. Позже параметры установит драйвер JDBC, что мы уже обсуждали. Пример со вставкой пользователя:

(def query
  {:insert-into :users
   :columns [:id :fname :email]
   :values [[99 "Ivan" "test@test.com"]]})

(sql/format query)

["INSERT INTO users (id, fname, email) VALUES (?, ?, ?)"
 99
 "Ivan"
 "test@test.com"]

Передайте результат в jdbc/execute!, чтобы выполнить вставку:

(jdbc/execute! db (sql/format query))

Чтобы сослаться на другое поле в условии, поставьте на его место кейворд или символ. Предположим, мы храним число бонусных баллов пользователя. Очередная покупка прибавляет еще сто баллов. Вот как начислить их пользователю с номером 99:

(def query
  {:update :users
   :set {:bonus_points [:+ :bonus_points 100]}
   :where [[:= :id 99]]})

(sql/format query)

["UPDATE users SET bonus_points = bonus_points + ? WHERE (id = ?)" 100 99]

По аналогии работают операторы удаления, группировки, JOIN, UNION и другие. Для каждого из них служит одноименный ключ словаря, который принимает другой словарь или вектор. Справку по всем операторам вы найдете на странице проекта.

Кроме словаря, библиотека предлагает другой способ составить запрос. В модуле honey.sql.helpers находятся функции, совместимые со стрелочными оператором. Каждая функция принимает предыдущий запрос и возвращает дополненный новым полем. Перепишем пример с бонусными баллами:

(require '[honey.sql.helpers :as h])

(def query
  (-> (h/update :users)
      (h/set {:bonus_points [:+ :bonus_points 100]})
      (h/where [[:= :id 99]])))

Некоторые предпочитают такой способ записи, хотя технически он сводится к наполнению словаря.

Локальные обертки

Выше мы проделываем одну и ту же работу: получаем SQL-вектор и передаем его в JDBC. Совместим эти действия в функции. Она принимает два словаря — запрос и параметры — и возвращает результат обращения к базе. Напишем функцию map-query:

(defn map-query [db-spec map-sql & [map-params]]
  (jdbc/query db-spec (sql/format map-sql {:params map-params})))

Словарь запроса (параметр map-sql) слегка отличается от прежней записи:

(def query
  {:select [:*]
   :from [:users]
   :where [:= :id :?id]})

Обратите внимание на параметр :?id: это кейворд со знаком вопроса впереди. HoneySQL считает его именным параметром. Значение параметра следует в словаре с ключом :params в аргументах sql/format. Вот как вызвать новый запрос:

(map-query db query {:id 1})

С новой функцией код сократился. Поместите сложные запросы в отдельный модуль, чтобы позже ссылаться на них. По аналогии напишите функцию map-execute, которая выполняет INSERT, UPDATE и другие операции, отличные от чтения.

Логирование и форматирование

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

Предположим, в режиме разработки мы бы хотели видеть все запросы, что посылаем к базе. Добавим отладочный лог:

(defn map-query [db-spec map-sql & [map-params]]
  (let [sql-vec (sql/format map-sql {:params map-params})]
    (log/infof "Query: %s" (first sql-vec))
    (jdbc/query db-spec sql-vec)))

В консоли мы увидим запись:

(map-query db query {:id 1})
;; 2021-08-06 10:29:25,702 INFO  book.db - Query: SELECT * FROM users WHERE id = ?

К сожалению, HoneySql не поддерживает “красивые” (pretty-printed) запросы с отступами и переносами строк. Для больших запросов это становится проблемой: длинную строку трудно разобрать. На время разработки подключим Java-библиотеку SqlFormatter для форматирования SQL. Добавьте зависимость в проект:

[com.github.vertical-blank/sql-formatter "2.0.1"]

Импортируйте одноименный класс:

(import 'com.github.vertical_blank.sqlformatter.SqlFormatter)

Чтобы получить форматированный запрос, передайте строку SQL в статический метод SqlFormatter/format. Доработаем логирование в функции map-query:

(defn map-query [db-spec map-sql & [map-params]]
  (let [sql-vec (sql/format map-sql {:params map-params})]
    (log/infof "Query:\n%s" (SqlFormatter/format (first sql-vec)))
    (jdbc/query db-spec sql-vec)))

Теперь в консоли появится оформленный SQL:

(map-query db query {:id 1})

;; 2021-08-06 10:40:24,062 INFO  book.db - Query:
;; SELECT
;;  *
;; FROM
;;   users
;; WHERE
;;  id = ?

По желанию его можно как-то выделить, например предварить каждую строку угловой скобкой или вертикальной чертой. Однако помните, что форматирование отнимает ресурсы и снижает скорость работы map-query. Сделайте так, чтобы логирование включалось по требованию или в зависимости от среды, например только при разработке.

Доработайте map-query так, чтобы в опциях можно было задать факт логирования, его уровень, признак форматирования и другое поведение.

Расширения

Библиотеку Honeysql легко расширить оператором, которого нет в поставке по умолчанию. Технически это значит вызвать функцию sql/register-clause!. Она меняет внутреннее состояние библиотеки, где хранятся известные выражения. Функция принимает другую функцию, которая вернет вектор SQL по словарю. Позже этот вектор будет включен в итоговый результат.

Приведем пример с выражением CREATE INDEX для PostgreSQL. Это сложная команда со множеством полей и условий. Вот лишь малая часть ее синтаксиса:

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table [ USING method ] ...

Наша задача — сделать так, чтобы индекс можно было задать словарем. Этим мы снизим риск ошибки при написании индексов вручную. Например:

(sql/format {:create-index {:if-not-exists? true
                            :name "idx_user_lname"
                            :on-table :users
                            :on-field :lname}})

Сперва учтем только основные поля индекса: его имя, уникальность, имя таблицы и некоторые другие. Ниже — черновая версия кода, которая строит индекс:

(sql/register-clause!
 :create-index
 (fn [_ {idx-name :name
         :keys [unique?
                if-not-exists?
                on-table
                on-field
                using]}]

   [(clojure.string/join
     " "
     ["CREATE"
      (when unique? "UNIQUE")
      "INDEX"
      (when if-not-exists? "IF NOT EXISTS")
      (name idx-name)
      "ON"
      (name on-table)
      "(" (name on-field) ")"
      (when using "USING")
      (when using using)])])
 nil)

Проверим, что вернет форматирование:

(sql/format {:create-index {:if-not-exists? true
                            :name "idx_user_lname"
                            :on-table :users
                            :on-field :lname}})

["CREATE  INDEX IF NOT EXISTS idx_user_lname ON users ( lname )  "]

Если передать этот вектор в jdbc/execute!, мы действительно получим новый индекс. Доработайте ключ :create-index так, чтобы он поддерживал больше полей и условий, в том числе специфичных для некоторых баз данных.

Кроме выражений, HoneySQL можно расширить SQL-функциями и операторами. Например, сделать так, чтобы условие

{:where [:= [:json->> :attributes :color] "red"]}

становилось

["... WHERE attributes ->> 'color' = ?" "red"]

С таким подходом у вас будет больше гибкости в запросах. Если вы часто используете нестандартные операторы, зарегистрируйте их в HoneySQL, чтобы не копировать код и оперативно править их в одном месте. Изучите примеры с вызовом sql/register-fn! и sql/register-op! из документации проекта.

Сырой SQL

По умолчанию HoneySql покрывает почти все операторы SQL. Но иногда вам понадобится оператор, которого нет в библиотеке. Для этого служит последнее средство — вкрапление “сырого” SQL. Ключ :raw принимает произвольный текст и внедряет его в запрос без какой-либо обработки.

Приведем несколько примеров. Пользователи, созданные за последний день:

{:select [:*]
 :from [:users]
 :where [:>= :created_at [:raw "now() - interval '1 day'"]]}

Более сложный запрос, где JSON-атрибуты товаров дополняют цветом и размером. Оператор || объединяет два объекта JSON.

(map-query
 db
 {:update :items
  :set {:attrs [:raw ["attrs || " [:param :new-attrs]]]}
  :returning [:id]}
 {:new-attrs {:color "red" :size "XL"}})

Обратите внимание, что в ключе :raw указана не строка, а вектор элементов (тоже строк или векторов), который позже соединяется в строку. С вектором проще поддерживать структуру запроса.

Вставляйте сырой SQL только ключом :raw, но ни в коем случае не конкатенацией строк. Сюда же относятся автозамена, разбиение и другие операции со строками. Все они будут ошибкой

Повторное использование запросов

В разделе про SQL-шаблоны мы выяснили, как повторно использовать части запросов. Для HugSQL мы использовали сниппеты — именованные выражения. В HoneySQL это делается проще, потому что сводится к работе с неизменяемыми коллекциями.

Вернемся к таблице пользователей. Приложение часто обращается к ним: читает всех пользователей, ищет по имени или городу, находит конкретного пользователя по номеру. Все эти запросы имеют общую часть: имя таблицы и список полей, которые получат клиенты. Вынесем общую часть в переменную, чтобы позже ссылаться на ее. Переменную еще называют “тушкой” запроса:

(def user-base
  {:select [:*]
   :from [:users]})

Если выполнить “тушку”, мы просто получим всех пользователей. Чтобы задать отбор по имени и ограничение на длину выборки, добавим эти критерии с помощью assoc:

(map-query db (assoc user-base
                     :where [:= :fname "Ivan"]
                     :limit 9))

Переменная user-base не пострадает от новых ключей и не повлияет на дальнейшие запросы.

Усложним задачу поиском по многим полям. В зависимости от того, заполнено поле или нет, мы должны добавить его в WHERE. Обратимся к макросу cond->, который отлично справится с задачей. Он пропускает данные через серию предикатов и форм, при этом каждая форма получает результат предыдущей.

Предположим, при поиске пользователя его имя обязательно для заполнения, а почта и возраст не обязательны. В этом случае мы составим запрос query на основе user-base с отбором по имени. Затем, если заданы почта и возраст, наполним секцию WHERE:

(let [q-fname "Ivan" ;; from params
      q-email "test@test.com" ;; from params
      q-age nil ;; from params

      query
      (assoc user-base :where [:and [:= :fname q-fname]])]

  (cond-> query

    q-age
    (update :where conj [:= :age q-age])

    q-email
    (update :where conj [:= :email q-email])))

Результат:

{:select [:*]
 :from [:users]
 :where [:and
         [:= :fname "Ivan"]
         [:= :email "test@test.com"]]}

Похоже работает условное соединение таблиц. Если задан флаг “с фотографией”, добавим в запрос ключ :join с таблицей photos:

(let [q-fname "Ivan" ;; from params
      with-photo? true ;; from params

      query
      (assoc user-base :where [:and [:= :fname q-fname]])]

  (cond-> query

    with-photo?
    (assoc :join [:photos [:= :users.id :photos.user_id]])))

Форма выше вернет запрос:

SELECT * FROM users
INNER JOIN photos ON users.id = photos.user_id
WHERE (fname = 'Ivan')

До сих пор мы использовали только равенство, но на практике нас интересуют диапазоны и частичные совпадения. Например, цена от и до при поиске товара или вхождение слова в полное название. Доработайте код так, чтобы для возраста можно было задать границы. Теперь мы ожидаем не одно, а два поля: age__min и age__max. Обратите внимание на двойное подчеркивание: оно отделяет имя поля (age) от оператора сравнения (min, больше или max, меньше). Для почты, города и других строковый полей напишите оператор вхождения: name__contains, city__contains и другие.

Аналоги

Коротко перечислим аналоги HoneySQL. Это SQLingvo, Korma, Lobos, Toucan и другие. Все они так или иначе выражают SQL с помощью данных или макросов, которые позже становятся строкой. Познакомившись HoneySQL, вы без труда освоите библиотеки с тем же подходом, где запросы строят из структур данных.

Отдельно стоит отметить Korma: эта библиотека делает шаг в сторону ORM. В ней вы объявляете сущности базы данных, по которым позже библиотека строит запросы. Korma будет интересна тем, кто работал с фреймворками Django или Rails и теперь ищет что-то похожее.

Шаблоны или данные

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

Вариант с шаблонами хорош тем, что мысленно вы остаетесь на уровне базы данных. Запрос легко скопировать в PGAdmin, выполнить и оценить результат. Затем там же отладить его и перенести обратно с минимальными усилиями. Если в фирме есть специалист по базам данных, он проведет аудит ваших запросов: найдет медленные выражения, не оптимальные операторы WHERE или JOIN.

Наоборот, если запросы описаны словарями, за ними плохо видна логика SQL. Для больших и сложных запросов это становится проблемой. Иногда такие запросы пишут в исходном (сыром) виде, долго тестируют и только затем приводят к “кложурному” виду. Получается двойная работа.

С другой стороны, запросы в виде данных легче использовать повторно. Если в нескольких запросах повторяется одно выражение, легко вынести его в переменную. Словари и списки удобней наращивать формами assoc-in и cond->, нежели добавлять Clojure-код в шаблоны SQL.

Оба способа равнозначны, и нельзя с уверенностью сказать, что лучше. Чтобы определиться, испытайте оба подхода на практике.

Структура и группировка

Техники, что мы рассмотрели выше, только составляют запрос, но не влияют на его обработку. Независимо от того, предпочитаете ли вы SQL в шаблонах или в виде коллекций, вы столкнетесь с тем, что недостаточно только составить и выполнить запрос. Часто ответ базы данных нуждается в обработке из-за вложенных данных. Рассмотрим, что это за проблема и как ее решить.

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

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

;; authors
[{:id 1 :name "Ivan Petrov"}
 {:id 2 :name "Ivan Rublev"}]

;; posts
[{:id :title "Introduction to Python" :author-id 1}
 {:id :title "Thoughts on LISP" :author-id 2}]

Мы должны выразить их структуру:

[{:id 1
  :name "Ivan Petrov"
  :posts [{:id
           :title "Introduction to Python"
           :author-id 1}]}
 {:id 2
  :name "Ivan Rublev"
  :posts [{:id
           :title "Thoughts on LISP"
           :author-id 2}]}]

На этом месте реляционные базы вступают в конфликт с нашими требованиями. Без особых ухищрений нельзя получить ответ базы именно в том виде, что в примере выше. Проверим это на практике: создадим таблицы и выберем из них данные.

Первый шаг, таблицы:

create table authors (
  id serial primary key,
  name text not null
);

create table posts (
  id serial primary key,
  title text not null,
  author_id integer references authors(id)
);

На втором шаге добавим минимальный набор данных. Для каждого автора по две статьи:

INSERT INTO authors (id, name) VALUES
  (1, 'Ivan Petrov'),
  (2, 'Ivan Rublev');

INSERT INTO posts (id, author_id, title) VALUES
  (10, 1, 'Introduction to Python'),
  (20, 1, 'Thoughts on LISP'),
  (30, 2, 'Learning Clojure'),
  (40, 2, 'Working on my pet project');

Третий шаг: напишем запрос на выборку авторов и их статей:

SELECT *
FROM authors a
JOIN posts p ON p.author_id = a.id;

Результат:

 id |    name     | id |            title            | author_id
----+-------------+----+-----------------------------+-----------
  1 | Ivan Petrov | 10 | Introduction to Python      |         1
  1 | Ivan Petrov | 20 | Thoughts on LISP            |         1
  2 | Ivan Rublev | 30 | Learning Clojure            |         2
  2 | Ivan Rublev | 40 | Working on my pet project   |         2

Видим, что родительская сущность (автор) повторяется столько раз, столько у нее дочерних записей (статей). Результат нельзя выводить в плоском виде, потому что пользователь ожидает другую структуру. Наверняка он хочет увидеть вложенный список:

  • Ivan Petrov
    • Introduction to Python
    • Thoughts on LISP
  • Ivan Rublev
    • Learning Clojure
    • Working on my pet project

Ситуация ухудшается, если у дочерних записей есть другие дочерние, например оценки или комментарии. Это приведет к еще большему дублированию родителей.

Проблема прямоугольника, как ее называет автор, не так проста, как кажется на первый взгляд. Она может серьезно задержать разработку. Как правило, когда мы формируем данные для клиента, от нас ожидают вложенную структуру. Клиент не должен перестраивать данные перед выводом, иначе это замедлит его работу.

Очевидно, требуется общий подход к решению проблемы. Мы рассмотрим несколько способов: на уровне кода и базы данных.

Выборка по слоям

Наиболее простой способ в том, чтобы выбрать главную сущность и подчиненные по отдельности. Предположим, мы знаем номер автора, которого нужно найти. Тогда первый запрос вернет его по первичному ключу:

(def author-id 1)

(jdbc/get-by-id db :authors author-id)
;; {:id 1 :name "Ivan Petrov"}

Дочерние записи получим запросом, где ссылка на автора равна его номеру:

(jdbc/find-by-keys db :posts {:author_id author-id})

({:id 10, :title "Introduction to Python", :author_id 1}
 {:id 20, :title "Thoughts on LISP", :author_id 1})

Осталось соединить результаты, и ответ готов:

(let [author-id 1
      author (jdbc/get-by-id ...)
      posts (jdbc/find-by-keys ...)]
  (assoc author :posts posts))

Хоть это и наивное решение, оно имеет право на жизнь. Мы производим строго два запроса, что не нагрузит базу данных. При желании их можно запустить параллельно.

Отдельные запросы неудобны в случаях, когда записи ищут не по первичным ключам, а более общим признакам: имени, городу. В этом случае сперва выберем родительские записи (их будет несколько), а затем дочерние, что на них ссылаются.

Поиск авторов по имени:

(jdbc/query db ["SELECT * FROM authors WHERE name = ?" "Ivan"])

;; ({:id 1 ...} {:id 2 ...})

Получили несколько записей. Возникает вопрос, как найти дочерние. Если делать это в цикле для каждого родителя, как в примере ниже:

(for [author authors]
  (let [{:keys [id]} author
        posts (jdbc/find-by-keys db :posts {:author_id id})]
    (assoc author :posts posts)))

, мы произведем столько запросов, что и родителей. Это неэффективно: с ростом выборки число запросов будет расти линейно. Если запрос по имени вернул тысячу авторов, мы тысячу раз обратимся к базе.

Другой способ в том, чтобы выбрать номера авторов и составить запрос вида:

...FROM posts WHERE author_id IN (?, ?, ...)

, где знаков вопроса столько же, сколько авторов. Получится один запрос к базе. Если добавить индекс на поле posts.author_id, это ускорит выборку.

Заметим, что выражение IN (...) требует особых усилий в построении запроса. Это не один, а несколько параметров, разделенных запятой. При этом их позиция должна совпадать с позицией значения в векторе SQL. Если поиск по имени нашел десять авторов, вот как будет выглядеть вектор SQL с поиском статей:

(jdbc/query db ["SELECT * FROM posts WHERE author_id IN (?,?,?,?,?,?,?,?,?,?)"
                1, 2, 3, 4, 5, 6, 7, 8, 9, 10])

Библиотеки предлагают разные способы облегчить подобную запись. В HugSQL для этого служит оператор where id in (:v*:ids) — частичка :v* означает вставить список параметров с именем ids. HoneySQL предлагает форму :in с похожей семантикой.

В современных ORM такая выборка называется prefetch related, извлечь связанные записи. Например, в Django ORM у объекта QuerySet есть одноименный метод prefetch_related. С его помощью данные извлекают послойно: сперва сущности первого порядка, затем те, что ссылаются на них, затем потомки потомков и так далее. Вы можете управлять глубиной вложенности, типом сущностей и другими параметрами.

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

(defn fetch-related
  [db-spec table fk-name fk-vals]
  ...)

Пример ее вызова:

(fetch-related db :posts :author_id [1..10])

Последний параметр — номера авторов — получим формой (mapv :id authors) из предыдущего результата. В свободное время напишите функцию fetch-related самостоятельно.

Будьте осторожны с оператором IN. Когда параметров много, это плохо сказывается на производительности. Поиск авторов может вернуть сто тысяч записей, что породит запрос с таким же числом параметров. Трудно обозначить конкретный порог для IN, потому что он зависит от настроек базы данных и окружения. В одном из проектов, над которым работал автор, таким порогом было число 100. Другими словами, в запрос с оператором WHERE id IN (...) разрешалось передать не более ста параметров.

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

Проще всего сделать выборку через loop или reduce:

(defn by-chunks [coll n]
  (partition n n [] coll))

(reduce
 (fn [result ids-chunk]
   (let [rows (fetch-related ... ids-chunk)]
     (into result rows)))
 []
 (by-chunks ids-all 100))

Попробуйте также выборку в наполовину параллельном режиме через pmap. Функция строит ленивую коллекцию футур, которые срабатывают по мере чтения последовательности. Окно такой коллекции (chunk size) равно числу ядер компьютера, умноженному на два.

(let [ids-chunks (by-chunks ids-all 100)
      futs (doall
            (for [ids-chunk ids-chunks]
              (future
                (fetch-related ... ids-chunk))))]
  (reduce
   (fn [result fut]
     (into result @fut))
   []
   futs))

Еще один способ оптимизировать оператор IN — записать его в виде условия ANY с массивом. Этот синтаксис читается как “поле id равно хотя бы одному элементу”:

SELECT * FROM authors WHERE id = ANY('{1,2,3,4,5}');

Следующий код показывает, как передать массив в параметры запроса. Для начала получим соединение с базой:

(def ^java.sql.Connection conn
  (jdbc/get-connection db))

Соединение нужно для того, чтобы построить объект массива. Метод .createArrayOf принимает тип элемента и массив объектов Object с номерами сущностей.

(def array
  (.createArrayOf conn "INTEGER" (object-array [1 2 3 4 5])))

Чтобы установить массив в параметр, расширим протокол ISQLParameter для типа java.sql.Array. По умолчанию при установке параметра JDBC вызывает метод .setObject, что не подходит для массива. Реализуем метод set-parameter так, что у подготовленного выражения вызывается метод .setArray:

(extend-protocol jdbc/ISQLParameter
  java.sql.Array
  (set-parameter [val ^java.sql.PreparedStatement stmt ix]
    (.setArray stmt ix val)))

После всех подготовок выполните запрос с массивом. Теперь он будет правильно передан в запрос.

(def array
  (.createArrayOf conn "INTEGER" (object-array [1 2 3 4 5])))

(jdbc/query db ["select * from authors where id = ANY(?)" array])

;; ({:id 1 :name "Ivan Petrov"} {:id 2 :name "Ivan Rublev"})

Код выше можно сократить за счет служебных функций, например условной make-db-array. По спеке, типу и вектору значений эта функция вернет экземпляр java.sql.Array:

(defn make-db-array [db-spec db-type values]
  (let [conn (jdbc/get-connection db)]
    (.createArrayOf conn db-type (object-array values))))


(make-db-array db "integer" [1 2 3])

;; #object[org.postgresql.jdbc.PgArray 0x7d1d3233 "{\"1\",\"2\",\"3\"}"]

Группировка результата

Другой способ работы со вложенными данными заключается в операторе JOIN. С ним мы получим данные одним запросом. Так поступают, чтобы снизить нагрузку на базу данных, особенно когда у нее много клиентов. В этом случае наша задача — как можно быстрее забрать данные и освободить базу другим потребителям. Даже если данные не совпадают с ожидаемой структурой, их приводят к нужному виду последующей обработкой.

Пример запроса, который вернет плоские данные. Обратите внимание на дубликаты авторов:

SELECT *
FROM authors a
JOIN posts p ON p.author_id = a.id;
 id |    name     | id |            title            | author_id
----+-------------+----+-----------------------------+-----------
  1 | Ivan Petrov | 10 | Introduction to Python      |         1
  1 | Ivan Petrov | 20 | Thoughts on LISP            |         1
  2 | Ivan Rublev | 30 | Learning Clojure            |         2
  2 | Ivan Rublev | 40 | Working on my pet project   |         2

Первый шаг к группировке — задать полям уникальные имена. Сейчас в результате два поля id, и может возникнуть путаница где чей номер. Чтобы ее избежать, явно укажем псевдонимы с пространством имен. Из-за символа / в псевдонимах их нужно заключить в двойные кавычки.

SELECT
  a.id        as "author/id",
  a.name      as "author/name",
  p.id        as "post/id",
  p.title     as "post/title",
  p.author_id as "post/author-id"
FROM authors a
JOIN posts p ON p.author_id = a.id;

Польза новых имен в том, что мы получим словари с ключами :author/id, :post/id и другими, что удобно для работы в Clojure.

Каждая запись выборки содержит несколько “слипшихся” сущностей, например автора и публикацию. Нужны функции, чтобы разделить их. Самый простой способ — явно задать ключи в вызове select-keys:

(defn get-author [db-row]
  (select-keys db-row [:author/id :author/name]))

(defn get-post [db-row]
  (select-keys db-row [:post/id :post/title :post/author-id]))

Быстрая проверка этих функций:

(def row
  {:author/id 1
   :author/name "Ivan Petrov"
   :post/id 10
   :post/title "Introduction to Python"
   :post/author-id 1})

(get-author row)
;; #:author{:id 1, :name "Ivan Petrov"}

(get-post row)
;; #:post{:id 10, :title "Introduction to Python", :author-id 1}

Выполним обход всех записей. На каждом шаге мы разделяем запись на сущности и наполняем пустой словарь.

(reduce
 (fn [result row]
   (let [author    (get-author row)
         post      (get-post row)
         author-id (:author/id author)
         post-id   (:post/id post)]
     (-> result
         (update-in [:authors author-id] merge author)
         (update-in [:authors author-id :author/posts post-id] merge post))))
 {}
 db-result)

Распечатаем результат. Видим, что статьи сгруппированы по авторам:

{:authors
 {1 #:author{:id 1
             :name "Ivan Petrov"
             :posts {10 #:post{:id 10 :title "Introduction to Python" :author-id 1}
                     20 #:post{:id 20 :title "Thoughts on LISP" :author-id 1}}}
  2 #:author{:id 2
             :name "Ivan Rublev"
             :posts {30 #:post{:id 30 :title "Learning Clojure" :author-id 2}
                     40 #:post{:id 40 :title "Working on my pet project" :author-id 2}}}}}

Эти данные можно отдать клиенту в виде JSON. Для их отрисовки в браузере понадобится только один обход. Заметим, что на сервере мы выполнили тоже один обход функцией reduce, что немаловажно. Если алгоритм требует два и более обходов результата, с ростом выборки ее обработка замедлится.

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

create table comments (
  id serial primary key,
  text text not null,
  post_id integer not null references posts(id)
);

INSERT INTO comments (id, post_id, text) VALUES
  (100, 10, 'Thanks for sharing this!'),
  (200, 10, 'Nice reading, it was useful.'),
  (300, 30, 'TL;DR: you must learn lisp');

Новый запрос включает и статьи, и комментарии к ним. Обратите внимание на разницу в операторах JOIN. Для таблицы posts это (INNER) JOIN. С ним мы пропустим авторов, у которых нет статей. Но для comments используем LEFT JOIN, чтобы оставить статьи без комментариев.

SELECT
  a.id        as "author/id",
  a.name      as "author/name",
  p.id        as "post/id",
  p.title     as "post/title",
  p.author_id as "post/author-id",
  c.id        as "comment/id",
  c.text      as "comment/text"
FROM authors a
JOIN posts p ON p.author_id = a.id
LEFT JOIN comments c ON c.post_id = p.id;

Дубликатов станет еще больше:

 author/id | author/name | post/id |         post/title          | post/author-id | comment/id |         comment/text
-----------+-------------+---------+-----------------------------+----------------+------------+------------------------------
         1 | Ivan Petrov |      10 | Introduction to Python      |              1 |        100 | Thanks for sharing this!
         1 | Ivan Petrov |      10 | Introduction to Python      |              1 |        200 | Nice reading, it was useful.
         2 | Ivan Rublev |      30 | Learning Clojure            |              2 |        300 | TL;DR: you must learn lisp
         1 | Ivan Petrov |      20 | Thoughts on LISP            |              1 |            |
         2 | Ivan Rublev |      40 | Working on my pet project   |              2 |            |

Доработаем наш прошлый код. Прежде всего, в функциях get-author, get-post и других нет смысла. Очевидно, сущность легко выбрать по пространству ключей, например, все ключи с пространством :author/ относятся к авторам. Напрашивается общая функция get-entity, которая принимает пространство и запись базы данных.

(defn get-entity [entity db-row]
  (reduce-kv
   (fn [result k v]
     (if (= (namespace k) entity)
       (assoc result k v)
       result))
   {}
   db-row))

С ней мы получим любую сущность:

(get-entity "author" row)
#:author{:id 1 :name "Ivan Petrov"}

(get-entity "post" row)
#:post{:id 10 :title "Introduction to Python" :author-id 1}

Еще более универсальное решение: сгруппируем запись в словарь вида пространство => сущность. Это делает функция на пять строк:

(defn row->entities [db-row]
  (reduce-kv
   (fn [result k v]
     (assoc-in result [(namespace k) k] v))
   {}
   db-row))

Пример ее работы:

(row->entities row)

{"author" #:author{:id 1 :name "Ivan Petrov"}
 "post" #:post{:id 10 :title "Introduction to Python" :author-id 1}}

Чтобы извлечь сущности в переменные, применим синтаксис :strs для разбиения словаря с ключами-строками. Это значительно сэкономит код.

(let [{:strs [author
              post
              comment]} (row->entities row)]
  ...)

Новый обход с учетом комментариев:

(reduce
 (fn [result row]

   (let [{:strs [author post comment]}
         (row->entities row)

         {author-id :author/id}   author
         {post-id :post/id}       post
         {comment-id :comment/id} comment]

     (cond-> result
       :then
       (update-in [:authors author-id] merge author)

       :then
       (update-in [:authors author-id :author/posts post-id] merge post)

       comment-id
       (update-in [:authors author-id :author/posts post-id :post/comments comment-id] merge comment))))
 {}
 db-result)

Обратите внимание: вместо линейного оператора -> мы используем условный cond->. С ним комментарий присоединяется к результату только если поле comment-id не пустое. Иначе получим уродливые словари, где все поля равны nil.

Результат:

{:authors
 {1 #:author{:id 1
             :name "Ivan Petrov"
             :posts {10 #:post{:id 10
                               :title "Introduction to Python"
                               :author-id 1
                               :comments {100 #:comment{:id 100
                                                        :text "Thanks for sharing this!"
                                                        :post-id 10}
                                          200 #:comment{:id 200
                                                        :text "Nice reading, it was useful."
                                                        :post-id 10}}}
                     20 #:post{:id 20 :title "Thoughts on LISP" :author-id 1}}}
  2 #:author{:id 2
             :name "Ivan Rublev"
             :posts {30 #:post{:id 30
                               :title "Learning Clojure"
                               :author-id 2
                               :comments {300 #:comment{:id 300
                                                        :text "TL;DR: you must learn lisp"
                                                        :post-id 30}}}
                     40 #:post{:id 40 :title "Working on my pet project" :author-id 2}}}}}

Из примеров напрашивается общая функция группировки. Они принимает результат базы и метаданные. Под метаданными имеем в виду описание сущностей: их поля и связи между родителем и потомком. Функция обходит выборку согласно структуре и наполняет словарь результата. Полный код с учетом всех тонкостей будет велик, поэтому оставим ее читателю на самостоятельную работу.

Порядок записей

У нашего обхода один недостаток. Поскольку мы группируем сущности в словаре по ключу, мы теряем порядок их следования в выборке. Иногда порядок неважен, но в некоторых случая критичен. Например, авторы должны следовать в алфавитном порядке, их статьи — по дате публикации, а комментарии — по их оценке пользователями. Если мы обяжем клиентов сортировать данные, это замедлит их.

Одно из решений в том, чтобы каждая сущность помнила номер записи, в которой она появилась. Для этого назначим сущности поле :db/index — ее позицию в выборке. Чтобы знать индекс на каждом шаге итерации, напишем служебную функцию enumerate. Она превращает коллекцию в список пар, где первый элемент — индекс от нуля:

(def enumerate
  (partial map-indexed vector))

(enumerate ["a" "b" "c"])
;; ([0 "a"] [1 "b"] [2 "c"])

Группировка сущностей тоже изменится: теперь она принимает индекс и добавляет его к каждой сущности.

(defn row->entities [idx db-row]
  (reduce-kv
   (fn [result k v]
     (update result
             (namespace k)
             assoc
             k v
             :db/index idx))
   {}
   db-row))

Пример:

(row->entities 3 {:post/id 1 :author/id 2})

{"post" {:post/id 1 :db/index 3}
 "author" {:author/id 2 :db/index 3}}

Обход выборки из базы отличается тем, что reduce принимает результат enumerate. Индекс каждой записи передается в row->entities:

(reduce
 (fn [result [idx row]]
   (let [{:strs [author post comment]}
         (row->entities idx row)]
     ...))
 {}
 (enumerate db-result))

Приведем малую часть результата:

{:authors
 {1 {:author/id 1
     :db/index 3
     :author/posts
     {10 {:post/id 10
          :db/index 1
          :post/comments
          {100 {:comment/id 100
                :db/index 0
                :comment/post-id 10}
           200 {:comment/id 200
                :db/index 1
                :comment/post-id 10}}}}}}}

Видим, что в каждой сущности появилось поле :db/index. Изменим дерево так, чтобы словари сущностей стали вектором, упорядоченным по этому полю. Проще всего это сделать функциями из пакета clojure.walk. Подготовим проверку на элемент словаря:

(def entry?
  (partial instance? clojure.lang.MapEntry))

Укажем теги, которые нуждаются в сортировке:

(def nested-tags
  #{:authors :author/posts :post/comments})

Вот как выглядит функция “ремаппинга” сущностей. Мы используем префикс remap вместо sort потому, что функция не только сортирует сущности, но и меняет их структуру со словаря на вектор.

(defn remap-entities
  [form]
  (if (entry? form)
    (let [[k v] form]
      (if (contains? nested-tags k)
        [k (->> v vals (sort-by :db/index) vec)]
        form))
    form))

Подключим модуль clojure.walk и вызовем функцию prewalk:

(require '[clojure.walk :as walk])

(:authors
 (walk/prewalk remap-entities result-grouped))

Результат:

[{:author/id 1
  :db/index 3
  :author/name "Ivan Petrov"
  :author/posts
  [{:post/id 10
    :db/index 1
    :post/title "Introduction to Python"
    :post/author-id 1
    :post/comments
    [{:comment/id 100
      :db/index 0
      :comment/text "Thanks for sharing this!"
      :comment/post-id 10}
     {:comment/id 200
      :db/index 1
      :comment/text "Nice reading, it was useful."
      :comment/post-id 10}]}
   {:post/id 20
    :db/index 3
    :post/title "Thoughts on LISP"
    :post/author-id 1}]}
 {:author/id 2
  :db/index 4
  :author/name "Ivan Rublev"
  :author/posts
  [{:post/id 30
    :db/index 2
    :post/title "Learning Clojure"
    :post/author-id 2
    :post/comments
    [{:comment/id 300
      :db/index 2
      :comment/text "TL;DR: you must learn lisp"
      :comment/post-id 30}]}
   {:post/id 40
    :db/index 4
    :post/title "Working on my pet project"
    :post/author-id 2}]}]

Эта структура в точности подходит устроит клиентов, и ее можно отправить в теле JSON. Разве что поле :db/index не нужно клиенту, поэтому удалите его после того, как сущности отсортировали. Доработайте remap-entities так, чтобы после (sort-by :db/index) шла форма с удалением этого поля. Искушенные читатели могут объединить сортировку и удаление в трансдьюсер.

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

(into {} (for [x (range 8)]
           [x x]))

{0 0, 1 1, 2 2, 3 3, 4 4, 5 5, 6 6, 7 7}

Когда элементов больше, порядок нарушается:

(into {} (for [x (range 9)]
           [x x]))

{0 0, 7 7, 1 1, 4 4, 6 6, 3 3, 2 2, 5 5, 8 8}

Так происходит потому, что в зависимости от числа элементов Clojure использует классы ArrayMap и HashMap. Они основаны на разных структурах данных и поэтому различаются в порядке обхода. Сейчас мы не будет углубляться в детали реализации. Важно то, что если сущностей не более восьми, их не нужно сортировать: мы и так добавляем их в порядке появления в выборке.

Технически это значит, что в функции remap-entities появится условие: если (-> v vals count) больше восьми, то следующая форма (sort-by...) пропускается. Доработайте remap-entities и проверьте ее на малых и больших выборках. Помните, что подход зависит от внутреннего устройства коллекций в Clojure и со временем может измениться. Добавить комментарий о том, зачем вы проверяете длину коллекции, и напишите тесты.

Сохранить порядок записей можно в том числе при помощи словаря из библиотеки Ordered. Функция ordered-map порождает упорядоченный словарь, который помнит, в каком порядке в него добавили ключи. Подключим библиотеку и проверим, так ли это:

[org.flatland/ordered "1.5.9"]              ;; project
[flatland.ordered.map :refer [ordered-map]] ;; ns

(into (ordered-map)
      (for [x (range 16)]
        [x x]))

{0 0, 1 1, 2 2, ... 13 13, 14 14, 15 15}

Действительно, порядок сохраняется. Подумайте, как изменить обход записей, чтобы сортировка с помощью clojure.walk не понадобилась. Подсказка: доработайте то место, где при помощи update-in мы дополняем итоговый словарь. По умолчанию update-in порождает обычные словари для несуществующих ключей. Возможно, понадобится своя версия update-in*, которая использует assoc*. Определим assoc* с помощью fnil: если первый аргумент nil (нет ключа), вернем пустой сортированный словарь, а не обычный.

(def assoc*
  (fnil assoc (ordered-map)))

Идея в том, чтобы расставить сортированные словари в ключи :authors, :author/posts и :post/comments. Как только это сделано, вы можете использовать update-in и assoc-in для насыщения результата, при этом порядок сохранится.

Заметим, что для сохранения порядка на клиенте мы должны заменить словари сущностей на массивы. Если этого не сделать, сортированный словарь будет записан в JSON и прочитан в JavaScript-объект, который не гарантирует порядка ключей. Поэтому даже с ordered-map вам понадобится обход для изменения структуры.

Итог

Подход fetch related означает, что связанные записи извлекают слоями. Сперва находят основные сущности, затем те, что ссылаются на них и так далее. Подобные запросы содержат оператор IN с набором первичных ключей. Чтобы случайно не передать огромное их количество, дочерние записи собирают в цикле с шагом в сто элементов. Для вашего проекта это число может быть другим.

Способ, когда из базы выбирают все за раз, называется select related. Одноименный метод в некоторых ORM означает, что в запрос добавляют оператор (LEFT) JOIN с дочерними таблицами, которые ссылаются на основную. Позже, когда вы обращаетесь к полям-ссылкам, система находит их из выборки без обращения к базе.

Каждый подход несет преимущества и недостатки. С select related снижается число обращений к базе. Это важно, потому что, как правило, база данных одна, а экземпляров приложения несколько. В отличии от базы, их число легко увеличить и тем самым распределить работу по обработке результата между многими машинами.

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

Группировка в базе

Рассмотрим третий способ сгруппировать данные — сделать это запросом на уровне базы. С помощью функций json(b)_agg и json(b)_object_agg мы получим вложенные записи. Тип результата будет json(b). Если расширить протоколы JDBC типом JSON, как мы делали выше, вызов jdbc/query вернет привычные коллекции Clojure.

Агрегация JSON не входит в стандарт SQL и поэтому реализована не во всех базах. Однако спрос на эту возможность привел к тому, что в том или ином виде она доступна не только в PostgreSQL, но и в MySQL или Sqlite. Далее по тексту мы будем работать именно с PostgreSQL.

Очевидно, группировка в базе несет как преимущества, так и недостатки. Запрос можно составить так, что данным не понадобится последующая обработка — они сразу готовы к отправке. За счет этого уходит код, что мы писали для обхода и группировки. С другой стороны, база тратит больше ресурсов на запрос с группировкой, что может не подойти для нагруженных проектов. Усложняется синтаксис SQL: когда в нем больше двух уровней вложенности, его трудно читать и поддерживать.

Функция jsonb_agg принимает колонку и возвращает массив JSON, где каждое значение так же приведено к типу jsonb. Еще одна функция row_to_json принимает произвольную запись и возвращает объект JSON, где ключи — имена полей записи. В сочетании эти функции способны группировать целые таблицы и подзапросы, а не только колонки.

Вернемся к примеру с авторами, статьями и комментариями. Для начала выберем статьи с комментариями:

SELECT
  p.id        as "post/id",
  p.title     as "post/title",
  p.author_id as "post/author-id",
  json_agg(row_to_json(c)) FILTER (WHERE c IS NOT NULL) as "post/comments"
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id;

Результат:

 post/id |         post/title          | post/author-id |                                                       post/comments
---------+-----------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------
      10 | Introduction to Python      |              1 | [{"id":100,"text":"Thanks for sharing this!","post_id":10}, {"id":200,"text":"Nice reading, it was useful.","post_id":10}]
      20 | Thoughts on LISP            |              1 |
      30 | Learning Clojure            |              2 | [{"id":300,"text":"TL;DR: you must learn lisp","post_id":30}]
      40 | Working on my pet project   |              2 |

Обратите внимание, что статья, у которой нет комментариев, осталась в выборке. Поле post/comments носит тип json; внутри него массив объектов, где каждый объект представляет запись таблицы comments.

Выражение FILTER после json_agg нужно для того, чтобы отсечь NULL. Если этого не сделать, получим массив с одним элементом NULL, что семантически неверно.

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

SELECT
  a.id                         as "author/id",
  a.name                       as "author/name",
  json_agg(row_to_json(posts)) as "author/posts"
FROM
  authors a,
  (SELECT
    p.id        as "post/id",
    p.title     as "post/title",
    p.author_id as "post/author-id",
    json_agg(row_to_json(c)) FILTER (WHERE c IS NOT NULL) as "post/comments"
  FROM posts p
  LEFT JOIN comments c ON c.post_id = p.id
  GROUP BY p.id
) AS posts
WHERE a.id = posts."post/author-id"
GROUP BY a.id;

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

Если поместить запрос в переменную и выполнить jdbc/query, получим:

[#:author{:id 1
          :name "Ivan Petrov"
          :posts [#:post{:id 10
                         :title "Introduction to Python"
                         :author-id 1
                         :comments [{:id 100 :text "Thanks for sharing this!" :post_id 10}
                                    {:id 200
                                     :text "Nice reading, it was useful."
                                     :post_id 10}]}
                  #:post{:id 20
                         :title "Thoughts on LISP"
                         :author-id 1
                         :comments nil}]}
 #:author{:id 2
          :name "Ivan Rublev"
          :posts [#:post{:id 30
                         :title "Learning Clojure"
                         :author-id 2
                         :comments [{:id 300 :text "TL;DR: you must learn lisp" :post_id 30}]}
                  #:post{:id 40
                         :title "Working on my pet project"
                         :author-id 2
                         :comments nil}]}]

Результат готов к отправке клиенту: ему не требуется ни сортировка, ни обработка полей, ни что бы то еще.

Библиотеки HugSQL и HoneySQL помогают строить сложные запросы по частям. В обоих случаях можно вынести внутреннюю часть (статьи и комментарии) в отдельную сущность и позже сослаться на нее. Например, в HugSQL мы бы вынесли подзапрос в сниппет, а в HoneySQL поместили бы его в переменную. С таким подходом главный запрос выглядит чище и удобней для чтения.

Рассмотрим другой пример группировки. Предположим, таблица хранит данные о товарах: артикул, название и так далее. У каждого товара могут быть десятки характеристик: жанр книги, размер талии для одежды и разъем питания для техники. Чтобы бесконечно не добавлять столбцы к товарам, вынесем характеристики в отдельную таблицу с именем характеристики, ее значением и ссылкой на товар.

Подготовим таблицы:

CREATE TABLE goods (
  id serial primary key,
  title text not null
);

CREATE TABLE good_attrs (
  id serial primary key,
  good_id integer not null references goods(id),
  attr text not null,
  val jsonb
);

Добавим данные для тестов — несколько товаров с характеристиками:

INSERT INTO goods (id, title)
VALUES (1, 'iPhone 99x'),
       (2, 'Galaxy 33.plus'),
       (3, 'G. Orwell 1984');

INSERT INTO good_attrs (good_id, attr, val)
VALUES (1, 'phone.display.diag', '145'),
       (1, 'phone.wifi.support', 'true'),
       (3, 'book.pages', '215'),
       (3, 'book.genre', '"dystopia"');

Обратите внмание, что значение характеристики носит тип jsonb. Это значит, нам доступно все богатство его типов. Например, можно указать размер одежды буквой (L, M); количество портов натуральным числом (2, 3); поддержку протокола (истина или ложь); список ссылок на сторонние ресурсы (массив строк).

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

[{:id 1
  :title "iPhone 99x"
  :attrs {:phone.display.diag 145
          :phone.wifi.support true}}
 {:id 2
  :title "Galaxy 33.plus"
  :attrs nil}
 {:id 3
  :title "G. Orwell 1984"
  :attrs {:book.genre "dystopia"
          :book.pages 215}}]

Однако при выборке в лоб через JOIN двух таблица получим задвоение товаров, что потребует обработки результата.

Функция json_object_agg принимает списки ключей и значений и строит словарь (в терминах JSON и JavaScript — объект). Напишем вложенный запрос, который группирует колонки attr и val таблицы good_attrs:

SELECT
  ga.good_id,
  jsonb_object_agg(ga.attr, ga.val) as attrs
FROM good_attrs ga
  GROUP BY ga.good_id;

Результат:

 good_id |                          attrs
---------+---------------------------------------------------------
       3 | {"book.genre": "dystopia", "book.pages": 215}
       1 | {"phone.display.diag": 145, "phone.wifi.support": true}

Теперь поместим его в подзапрос и присоединим при помощи LEFT JOIN к товарам:

SELECT
  g.id,
  g.title,
  a.attrs
FROM
  goods g
LEFT JOIN (
  SELECT
    ga.good_id,
    jsonb_object_agg(ga.attr, ga.val) as attrs
  FROM good_attrs ga
    GROUP BY ga.good_id
) a ON a.good_id = g.id;
 id |     title      |                          attrs
----+----------------+---------------------------------------------------------
  1 | iPhone 99x     | {"phone.display.diag": 145, "phone.wifi.support": true}
  2 | Galaxy 33.plus |
  3 | G. Orwell 1984 | {"book.genre": "dystopia", "book.pages": 215}

Обратите внимание, что товар, у которого нет характеристик, остался в выборке. Если передать итоговый запрос в jdbc/query, получим именно ту структуру данных Clojure, что мы приводили.

В этом преимущество json(b): PostgreSQL предлагает много функций над этим типом, в том числе для группировки. Любой json(b)-результат будет прочитан в Clojure за счет протокола. Это может быть массив, словарь и любая комбинация их вложенности.

Еще один прием, связанный с группировкий — использовать row_to_json в сочетании с оператором UNION. Этот оператор объединяет несколько запросов по вертикали. При этом обязательно, чтобы состав полей (их число и типы) был одинаков во всех запросах.

select 1 as id, 'foo' as name
UNION
select 2 as id, 'bar' as name;
 id | name
----+------
  1 | foo
  2 | bar

Иногда в проекте бывает несколько схожих сущностей в разных таблицах. Например, отдельно бухгалтерские и юридические документы; администраторы и пользователи; крупные и малые поставщики. Из-за разной структуры их нельзя объединить в один запрос. Но поскольку функция row_to_json приводит любую запись к типу json, можно объединить ее результаты, полученные от любых записей.

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

CREATE TABLE admins (
  id serial,
  full_name text NOT NULL,
  email text
);

и несколько записей:

INSERT INTO admins (full_name, email)
VALUES ('Petr Smirnov', 'petr@test.com'),
       ('Oleg Ivanov', 'oleg@test.com');

Ниже — запрос, который выберет обе сущности. Обратите внимание на поля id и type в обеих частях. Первичный ключ (id) нужен настолько часто, что логично вынести его на верхний уровень запроса. Поле type содержит постоянную строку с типом сущности: “user” для пользователя и “admin” для администратора. По этому полю легко понять, что находится в entity.

SELECT
  u.id AS id,
  'user' AS type,
  row_to_json(u) AS entity
FROM
  users u
UNION ALL
SELECT
  a.id AS id,
  'admin' AS type,
  row_to_json(a) AS entity
FROM
  admins a;

Результат в консоли psql:

 id | type  |                                                                           entity
----+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 | user  | {"id":1,"fname":"Ivan","lname":"Petrov","email":"test@test.com","age":30,"city":null,"year_birth":null,"created_at":"2021-08-10T07:36:03.934029+00:00"}
  3 | user  | {"id":3,"fname":"Huan","lname":null,"email":null,"age":null,"city":null,"year_birth":null,"created_at":"2021-08-10T07:36:03.934029+00:00"}
  1 | admin | {"id":1,"full_name":"Petr Smirnov","email":"petr@test.com"}
  2 | admin | {"id":2,"full_name":"Oleg Ivanov","email":"oleg@test.com"}

и в Clojure:

[{:id 1
  :type "user"
  :entity
  {:id 1
   :fname "Ivan"
   :lname "Petrov"
   :email "test@test.com"
   :age 30
   :city nil
   :year_birth nil
   :created_at "2021-08-10T10:36:03.934029+03:00"}}
 {:id 3
  :type "user"
  :entity
  {:id 3
   :fname "Huan"
   :lname nil
   :email nil
   :age nil
   :city nil
   :year_birth nil
   :created_at "2021-08-10T10:36:03.934029+03:00"}}
 {:id 1
  :type "admin"
  :entity {:id 1
           :full_name "Petr Smirnov"
           :email "petr@test.com"}}
 {:id 2
  :type "admin"
  :entity {:id 2
           :full_name "Oleg Ivanov"
           :email "oleg@test.com"}}]

При обходе выборки проверяйте поле type оператором case:

(doseq [{:keys [id type entity]} result]
  (case type
    "user" (process-user ...)
    "admin" (process-admin ...)))

и в зависимости от типа вызывайте нужный обработчик записи.

Еще одно замечание касается оператора UNION. Обратите внимание, что мы использовали его -ALL версию. Если опустить частицу ALL после UNION, база попытается убрать дубликаты записей. Чтобы проверить запись на повтор, ее сравнивают с уже накопленными записями. А поскольку тип json не поддерживает сравнение, вы получите ошибку:

SELECT
  u.id AS id,
  'user' AS type,
  row_to_json(u) AS entity
FROM
  users u
UNION
SELECT
  a.id AS id,
  'admin' AS type,
  row_to_json(a) AS entity
FROM
  admins a;

-- ERROR:  could not identify an equality operator for type json
-- LINE 4:   row_to_json(u) AS entity

В нашем случае пользователи и администраторы очевидно не пересекаются, поэтому нет смысла убирать дубликаты обычным UNION. Но если вам действительно нужно отсечь дубликаты, используйте to_jsonb вместо row_to_json. Эта функция вернет результат с типом jsonb, который поддерживает сравнение. Как следствие, оператор UNION ALL не вызовет ошибку для этого типа.

Группировка в базе может быть удачным решением, которое сэкономит время и код. Однако она зависит от объема данных и их структуры. Обязательно опробуйте выборку на больших данных (сотни тысяч и больше) и оцените время и план исполнения. Если данных нет, сгенерируйте их спекой. В первой книге о Clojure мы рассмотрели, как это сделать модулем gen.

Миграции

С развитием проекта в базе появляются новые таблицы, перечисления и индексы. Чтобы автоматизировать изменения в базе, придумана концепция миграций. В этом разделе мы рассмотрим ее общие принципы и как они работают в Clojure.

Технически миграция выглядят как .sql-файл. В нем находится код, который создает таблицы и другие сущности базы. Различают up и down миграции, они же прямые и обратные. Как правило, прямые служат для создания сущностей, например, создают таблицу и индексы для нее. Down-миграция описывает обратное действие: удаление индекса и таблицы. В редких случаях отмена не требуется, и тогда миграция состоит только из up-части.

Каждая миграция имеет уникальный идентификатор. Чаще всего это дата в формате ISO или время Unix. Идентификатор должен поддаваться сортировке, чтобы однозначно определить порядок миграций. По этой причине на роль идентификатора не подойдет строка UUID.

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

Сведения о миграциях хранится в самой базе. Как правило, движок миграций создает таблицу migrations примерно такой структуры:

create table migrations (
  migration_id text primary key,
  created_at timestamp,
  description text
);

Таблица запоминает, какие миграции и когда мы применили. При запуске миграций программа читает эту таблицу и сканирует файлы на диске. Файлы называют по принципу <time/id>-<description>.<up/down>.sql. Объясним значения в угловых скобках:

  • <time/id> — время или идентификатор, по которому сортируются миграции;
  • <description> — короткое описание миграции;
  • <up/down> — признак прямой или обратной миграции.

Примеры файлов:

20210830075251-create-users-table.up.sql
20210830075251-create-users-table.down.sql

20210831064602-create-profiles-table.up.sql
20210831064602-create-profiles-table.down.sql

Движок миграции группирует файлы в примерно такую структуру:

(def migrations
  [{:id 20210830075251
    :description "Create users table"
    :up "20210830075251-create-users-table.up.sql"
    :down "20210830075251-create-users-table.down.sql"}
   {:id 20210831064602
    :description "Create profiles table"
    :up "20210831064602-create-profiles-table.up.sql"
    :down "20210831064602-create-profiles-table.down.sql"}])

В зависимости от того, какая версия базы сейчас и что мы хотим сделать, включаются разные способы применения миграций. Наиболее частый сценарий — migrate forward, то есть применить все те миграции, что еще не были обработаны. В этом случае библиотека применяет только те прямые (up) мигации, чей идентификатор больше прочитанного из базы. Технически это значит отфильтровать список миграций по id и выбрать имена up-файлов (переменная last-db-id означает текущий идентификатор):

(->> migrations
     (filter (fn [{:keys [id]}]
               (> id last-db-id)))
     (sort-by :id)
     (map :up))

;; ("20210831064602-create-profiles-table.up.sql"...)

Каждую миграция выполняется в транзакции. Если изменения прошли без ошибок, одновременно меняется таблица migrations: в нее попадает идентификатор той миграции, которую только что выполнили. Даже если миграция “упала” где-то на полпути, не останется промежуточных следов ее работы.

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

(->> migrations
     (filter (fn [{:keys [id]}]
               (and (>= id target_id)
                    (< id current-db-id))))
     (sort-by :id)
     (reverse)
     (map :down))

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

Случается, что изменения в программе были неудачны, и это выяснилось после запуска в проде. Тогда изменения в базе открывают обратной миграцией. Наконец, когда схема базы описана в файле, это означает контроль за ней. Новичкам будет проще понять структуру базы по этим файлам.

Clojure предлагает несколько библиотек для миграций. Мы рассмотрим две из них: Migratus и Ragtime.

Migratus

Проект Migratus состоит из двух частей: самой библиотеки и плагина к lein. Проще всего начать работу при помощи плагина. Добавьте его в проект:

:plugins [... [migratus-lein "0.7.3"]]
:dependencies [... [migratus "1.3.5"]]

Напишем несколько миграций. В папке resources/migrations создайте пока что пустые файлы:

> mkdir -p resources/migrations
> cd resources/migrations

touch 20210830075251-create-users-table.up.sql
touch 20210830075251-create-users-table.down.sql

Теперь наполним файлы. Содержимое up-миграции:

CREATE TYPE user_status AS ENUM ('active', 'pending', 'blocked');

--;;

CREATE TABLE users(
  id serial primary key,
  name text not null,
  status user_status not null,
  email text
);

--;;

CREATE INDEX idx_users_name ON users(name);

Это прямая (up) половина первой миграции. Она создает тип-перечисление, таблицу users и индекс на имя пользователя. Чтобы откатить эти шаги, поместите в down-файл следующий код:

DROP INDEX idx_users_name;

--;;

DROP TABLE users;

--;;

DROP TYPE user_status;

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

Когда миграции готовы, применим их к базе. Для этого сперва укажем параметры подключения. В файле project.clj добавьте ключ :migratus со словарем:

{:store :database
 :migration-dir "migrations"
 :db {:dbtype "postgresql"
      :dbname "migration_test"
      :host "127.0.0.1"
      :user "book"
      :password "book"}}

Ключ :migration-dir указывает папку относительно ресурсов, в которой хранятся миграции. В нашем случае это resources/migrations. В поле :db записана знакомая нам JDBC-спека. Выполните в терминале:

> lein migratus migrate

Вы увидите список миграций, которые плагин применил к базе данных. Проверьте содержимое базы с помощью psql или другой утилиты — появится таблица users. Кроме того, исследуйте таблицу миграций под именем schema_migrations:

SELECT * FROM schema_migrations;

В ней должна быть запись с датой и именем миграции.

         id     |         applied         |      description
----------------+-------------------------+-----------------------
 20210830075251 | 2021-08-28 09:28:26.911 | create-users-table

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

> lein migratus rollback

Действие rollback означает откатить последнюю транзакцию. После нее у нас не останется таблицы users и связанных с ней сущностей. Из таблицы schema_migrations исчезнут сведения о миграции 20210830075251, и ее можно будет применить снова. Применение и откат миграций можно повторять сколько угодно раз. Конечно, при условии, что откат удаляет все изменения прямой части.

Синтаксис

Обратите внимание на выражение --;;, которое разделяет запросы в файлах миграций. С точки зрения SQL это обычный комментарий. Однако мы поставили его не для красоты, а с умыслом.

По умолчанию Migratus рассматривает файл как запрос, который нужно выполнить с помощью jdbc/execute!. Но миграция редко укладывается в одно SQL-выражение, и выражение --;; служит разделителем запросов. Migratus разбивает прочитанный файл разделителем и выполняет полученные части в одной транзакции. За счет этого миграция может состоять из нескольких запросов, и нам не придется создавать их несколько по принципу “один файл — один запрос”.

Тонкая настройка

При запуске миграций через lein вам доступны все опции этого инструмента, в том числе профили. Вынесем Migratus в dev-зависимости, чтобы при сборке проекта он не стал его частью:

:profiles
{:dev {:dependencies [[migratus "1.3.5"]]}}

Это логично, потому что миграции относятся к инфраструктуре, а не к логике приложения. Соберите uberjar и убедитесь, что в нем нет следов библиотеки.

Еще один трюк с профилями — миграции, которые видны только во время тестов. В них помещают данные, необходимые для проверки действий с базой. При обычном запуске эти миграции не видны Migratus.

Создайте структуру папок env/test/resources/migrations. Это так называемая env-директория, о который мы говорили в первой книге. Ее первый уровнь — имя профиля (по аналогии с test у вас могут быть папки dev, uberjar и другие). Укажите профилю test дополнительный путь миграций:

:profiles
{:test {:resource-paths ["env/test/resources"]}}

Теперь создайте в env-директории migrations файл

20210830075415-add-test-data.up.sql

Поместим в него нескольких пользователей для тестов:

INSERT INTO users(id, name, status, email) VALUES
(1, 'Test1', 'active',  'test1@test.com'),
(2, 'Test2', 'pending', 'test2@test.com'),
(3, 'Test3', 'blocked', 'test3@test.com');

Обратная миграция просто очистит таблицу:

DELETE FROM users;

Теперь запустите migratus с параметром with-profile +test (плюс означает добавить профиль к текущему):

> lein with-profile +test migratus migrate

В списке миграций вы увидите новую тестовую. Ее откат работает аналогично командой rollback с указанием профиля. Без параметра with-profile эта миграция не появится в списке доступных.

Параметры подключения

В наших примерах мы явно указали подключение к базе в файле project.clj. Это подойдет для локальной разработки, но не промышленного запуска. Чтобы запустить миграции на боевой базе, передайте ее параметры в переменных среды. Для этого изменим ключ :migratus так, чтобы имя и пользователь базы приходили из среды:

{:store :database
 :migration-dir "migrations"
 :db {:dbtype "postgresql"
      :dbname "migration_test"
      :host "127.0.0.1"
      :user ~(System/getenv "DB_USER")
      :password ~(System/getenv "DB_PASSWORD")}}

Синтаксис ~<form> работает внутри defproject, потому что это макрос. Чтобы запустить миграции, укажите переменные процессу lein:

> DB_USER=book DB_PASSWORD=**** lein migratus migrate

Мы подробно разбирали конфигурацию системы и переменные среды в пятой главе первой книги.

Миграции в коде

Иногда миграциями управляют не командой lein, а кодом на Clojure. Для этого Migratus должен быть основных, а не dev-зависимостях. Приведем минимальный код, чтобы запустить миграции в коде:

(ns ...
  (:require
   [migratus.core :as migratus]))

(def config
  {:store :database
   :migration-dir "migrations"
   :db db})

(migratus/init config)

(migratus/migrate config)
(migratus/rollback config)

Код сводится к простым шагам: объявить конфигурацию, инициировать библиотеку и вызывать функции migrate или rollback с конфигурацией.

Ручной запуск миграций помещают в начало работы программы. Это гарантирует, что при запуске программа получит последнюю версию базы. Если серверная программа запущена на нескольких машинах, выполнять миграции должна только одна из них. Проще всего задать этот признак в переменной среды: всем ложь и только одной машине — истину.

Если ваш проект основан на компонентах (библиотеки Component и Integrant), создайте компонент, который зависит от базы данных. При запуске (метод start или init-key) он вызывает migratus/migrate с конфигурацией. В поле :db конфигурации помещают компонент базы.

Программные миграции

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

Технические подобные миграции — это EDN файлы со словарем. Ключи :up и :down указывают на функции Clojure. Эти функции принимают один параметр — конфигурацию — и выполняют действия с базой. Код функции остается на ваше усмотрение. С помощью сторонних библиотек вы можете читать данные из сети или файлов и записывать их в базу. Обратная программная миграция должна удалить то, что внесла ее прямая часть.

Пример: в таблице запросов оседают данные о просмотрах страниц. У нас есть только IP-адрес пользователя, но хотелось бы знать его страну, город и другие данные. Исходная таблица:

SELECT * FROM requests;
 id |       ip       | country_code
----+----------------+--------------
  1 | 24.198.249.0   |
  2 | 187.33.237.120 |

Создайте файл 20210831070224-update-country-by-ip.edn со следующим словарем внутри:

{:ns book.migrations.update-country
 :up-fn migrate-up
 :down-fn migrate-down}

Поле :ns указывает на пространство, а :up-fn и :down-fn — на функции прямой и обратной миграций. Технически возможно держать несколько миграций в одном Clojure-модуле, но лучше резделять их.

В файл book/migrations/update_country.clj поместим модуль миграции. Начнем с ее шапки:

(ns book.migrations.update-country
  (:require
   [clojure.java.jdbc :as jdbc]
   [clj-http.client :as client]))

Код страны мы получим с помощью сервиса IPLocation по HTTP API. Сервис принимает POST-запрос с адресом и выдает JSON с информацией о нем. Обернем это действие функцию:

(defn get-ip-info [ip]
  (:body
   (client/post "https://iplocation.com"
                {:form-params {:ip ip}
                 :as :json})))

Прямая миграция выбирает записи с пустым кодом страны и обходит их в цикле. Для каждого IP-адреса она выполняет HTTP-запрос и обновляет запись с найденным кодом.

(defn migrate-up [{:keys [db]}]
  (let [rows (jdbc/query db "select id, ip from requests where country_code is null")]
    (doseq [{:keys [id ip]} rows]
      (let [{:keys [country_code]} (get-ip-info ip)]
        (jdbc/update! db :requests
                      {:country_code country_code}
                      ["id = ?" id])))))

Обратная миграция сбрасывает коды стран в NULL:

(defn migrate-down [{:keys [db]}]
  (jdbc/update! db :requests
                {:country_code nil}
                ["true"]))

Запустите миграции в терминале. По окончании в таблице requests появятся коды стран:

SELECT * FROM requests;
 id |       ip       | country_code
----+----------------+--------------
  1 | 24.198.249.0   | US
  2 | 187.33.237.120 | BR

При откате миграции коды пропадут из таблицы.

Код миграции можно улучшить: добавить логирование и перехват ошибок, вести запросы параллельно при помощи pmap. В запрос SELECT дописать оператор FOR UPDATE, чтобы заблокировать запись на время транзакции. Предлагаем читателю поработать над кодом в свободное время.

Ragtime

Коротко рассмотрим вторую библиотеку миграций под названием Ragtime. Она во многом повторяет Migratus, но все же имеет особенности, которые могут быть полезны.

Общий принцип библиотеки тот же, что мы рассмотрели выше. Это .sql-файлы с кодом на изменение базы, их чтение и обход, таблица с именами обработанных миграций. Но в отличие от Migratus, у Ragtime более гибкий дизайн. Если первый привязан к реляционным базам данных и JDBC-драйверу, то Ragtime основан на протоколах, которые легко адаптировать под нужды любой базы.

Дизайн Ragtime состоит из двух уровней. Первый — абстрактные протоколы DataStore и Migration, которые описывают хранилище данных и миграцию. Второй — реализация протоколов под конкретную базу данных. Встроенный пакет ragtime.jdbc расширяет их для JDBC-совместимых баз, как это делает Migratus.

За счет столь гибкого дизайна Ragtime можно применить к самым разным базам, в том числе не реляционным. Например, к Cassandra, MongoDB или Datomic. Работая в Exoscale, автор написал библиотеку для миграций Cassandra на базе Ragtime. Присмотритесь к этой библиотеке, если в проекте участвуют не только JDBC-совместимые базы.

Мы не будем рассматривать Ragtime так же подробно, как делали это с Migratus. Полагаем, к этому моменту читатель уже достаточно опытен, чтобы разобраться с библиотекой самостоятельно. Отложим знакомство с Ragtime на будущее: в главе про Datomic мы построим систему миграций на ее основе.

Next.JDBC

Библиотека Next.JDBC — это следующий этап в адаптации JDBC для Clojure. Проект ставит следующие цели:

  • ускорить обработку результата запроса. Автор считает неоптимальным то число действий, что совершает clojure.java.jdbc с объектом ResultSet. На больших выборках его обработка действительно замедляется.
  • Обеспечить более тесную связь с Clojure за счет протокола Datafy. Этот протокол появился в Clojure 1.11 и служит для того, чтобы переводить объекты Java в данные Clojure. Он упростит связь между классами JDBC и данными.

  • Предложить более простой и понятный API. Прежняя библиотека различает функции query, execute!, db-do-commands и другие; вдобавок она обязывает помнить об их особенностях. Next.JDBC планирует сократить разнообразие API.

Коротко опробуем библиотеку. Подключим ее к проекту:

;; project.clj
[com.github.seancorfield/next.jdbc "1.2.709"] ;; project.clj

;; module.clj
(ns ...
  (:require
   [next.jdbc :as jdbc]
   [next.jdbc.sql :as jdbc.sql]
   [next.jdbc.prepare :as jdbc.prepare]
   [next.jdbc.result-set :as jdbc.rs]))

В отличии от предшественника, Next.JDBC не следует правилу “все в одном модуле”. Смысловые части библиотеки разнесены по отдельным файлам.

Роль источника данных играет объект datasource, который получают из JDBC-спеки. Однажды получив, его передают в остальные функции первым аргументом.

(def db {:dbtype "postgresql" :dbname "test" ...})

(def ds (jdbc/get-datasource db))

Объект datasource хранит открытое соединение с базой данных. Next.JDBC не будет открывать новое соединение на каждый запрос: это нежелательное поведение, которое легко допустить в обычной clojure.java.jdbc. Содержимое datasource легко проверить функцией bean:

(bean ds)

{:class next.jdbc.connection$url_PLUS_etc$...
 :connection #object[...PgConnection 0x352c5229...]
 :loginTimeout 0}

Прочитаем знакомую нам таблицу пользователей:

(first (jdbc/execute! ds ["SELECT * FROM users"]))

#:users{:id 1
        :fname "Ivan"
        :lname "Petrov"
        :email "test@test.com"
        :age 30
        :created_at #inst "2021-08-10T07:36:03.934029000-00:00"}

Обратите внимание, что ключи словарей квалифицированы, то есть содержат пространство с именем таблицы. За счет пространств легко понять, какой сущности принадлежит поле. Такие словари удобней в дальнейшей работе в Clojure.

Пространства работают в том числе для связанных таблиц. Убедимся в этом на примере соединения с таблицей профилей:

(first (jdbc/execute! ds ["SELECT * FROM users u, profiles p WHERE p.user_id = u.id"]))

{:users/id 1
 :users/lname "Petrov"
 :users/fname "Ivan"
 :users/age 30
 :users/email "test@test.com"
 :users/created_at #inst "2021-08-10T07:36:03.934029000-00:00"
 :profiles/id 1
 :profiles/user_id 1
 :profiles/avatar "kitten.jpg"}

Видим, что поля из разных таблиц обрели разные пространства. Это освобождает нас от расстановки псевдонимов вручную:

SELECT
  u.id     AS "user/id",
  u.fname  AS "user/fname",
  ...

Важно понимать, что поиск таблицы для поля не бесплатен, а требует отдельного запроса в базу. Пример выше с пользователем и профилем порождает следующий запрос:

SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname,
a.attnotnull OR (t.typtype = 'd' AND t.typnotnull),
pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM
pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n
ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a
ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid
= t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid =
a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 32824 AS oid , 1
AS attnum UNION ALL SELECT 32824, 2 UNION ALL SELECT 32824, 3
UNION ALL SELECT 32824, 4 UNION ALL SELECT 32824, 5 UNION ALL
SELECT 32824, 6 UNION ALL SELECT 32824, 8 UNION ALL SELECT 32824,
9 UNION ALL SELECT 32835, 1 UNION ALL SELECT 32835, 2 UNION ALL
SELECT 32835, 3) vals ON (c.oid = vals.oid AND a.attnum =
vals.attnum)

Который, в свою очередь, дает сведения о колонках и таблицах:

  oid  | attnum |  attname   | relname  | nspname | ?column? | ?column?
-------+--------+------------+----------+---------+----------+----------
 32835 |      3 | avatar     | profiles | public  | f        |
 32835 |      2 | user_id    | profiles | public  | t        |
 32835 |      1 | id         | profiles | public  | t        | t
 32824 |      9 | created_at | users    | public  | f        |
 32824 |      8 | year_birth | users    | public  | f        |
 32824 |      6 | city       | users    | public  | f        |
 32824 |      5 | age        | users    | public  | f        |
 32824 |      4 | email      | users    | public  | f        |
 32824 |      3 | lname      | users    | public  | f        |
 32824 |      2 | fname      | users    | public  | f        |
 32824 |      1 | id         | users    | public  | t        | t

Если данный подход кажется вам расточительным, отключите квалификацию полей параметром builder-fn:

(jdbc/execute!
 ds
 ["SELECT * FROM users ..."]
 {:builder-fn jdbc.rs/as-unqualified-maps})

Кроме функции as-unqualified-maps, вам доступна as-arrays для выборки векторов и некоторые другие обработчики ResultSet.

Вспомогательные функции get-by-id, find-by-keys, update!, insert! и аналоги переместились в модуль next.jdbc.sql. Они отличаются только тем, что принимают первым параметром datasource:

(jdbc.sql/get-by-id ds :users 1)

(jdbc.sql/insert! ds :users {:id 199 :fname "Test"})

(jdbc.sql/query ds ["select * from users"])

Макрос with-transaction тоже почти не изменился. Он принимает вектор, где первый элемент — символ, а второй — текущий datasource. С первым символом будет связан новый datasource, внутри которого транзакционное соединение. В примере ниже мы открываем транзакцию с двумя запросами. Первый добавляет товар, второй записывает для него скидку 33%.

(jdbc/with-transaction [tx ds]
  (let [{:items/keys [id]}
        (jdbc.sql/insert! tx :items {...})]
    (jdbc.sql/insert! tx :sales {:item_id id :ratio 0.66})))

Как мы выяснили, поддержка JSON открывает новые горизонты в работе с базой. Next.JDBC предлагает протоколы для чтения и установки нестандартных типов. Подготовим функцию для перевода PGobject в Clojure:

(defn pg->clj [^PGobject pg-obj]
  (let [pg-val (.getValue pg-obj)
        pg-type (.getType pg-obj)]
    (case pg-type
      ("json" "jsonb")
      (json/parse-string pg-val keyword)
      ;; else
      pg-obj)))

и расширим с ее помощью протокол:

(extend-protocol jdbc.rs/ReadableColumn
  PGobject
  (read-column-by-label [v _]
    (pg->clj v))
  (read-column-by-index [v _ _]
    (pg->clj v)))

Обратите внимание, что у протокола два метода: получить поле по индексу и имени. В нашем случае реализации совпадают. При чтении колонки с типом json(b) получим коллекцию Clojure:

(first (jdbc.sql/query ds ["select * from items"]))

#:items{:id 1
        :title "Cap"
        :attrs {:size "XL" :color "red" :country "China"}}

Расширим установку параметров. Любая коллекция Clojure станет объектом PGobject с типом json. Вспомогательная функция:

(defn ->pg-object [data]
  (doto (new PGobject)
    (.setType "json")
    (.setValue (json/generate-string data))))

и протокол:

(extend-protocol jdbc.prepare/SettableParameter
  clojure.lang.IPersistentCollection
  (set-parameter [v ^java.sql.PreparedStatement pr-st idx]
    (.setObject pr-st idx (->pg-object v))))

Вставка товара с JSON-атирибутами:

(jdbc.sql/insert! ds :items {:title "t-shirt" :attrs {:size "XXL" :brand "Abibas"}})

#:items{:id 1
        :title "t-shirt"
        :attrs {:size "XXL" :brand "Abibas"}}

Как видите, интерфейс Next.JDBC в целом похож на предшественника. Большинство функций ведут себя так же, но дают больше контроля за происходящим. Если у вас долгий опыт работы с clojure.java.jdbc, перейти на Next.JDBC будет несложно. Тонкости перехода описаны в Wiki-документации проекта. Там же вы узнаете о других возможностях JDBC.next.

Заключение

Реляционные базы данных основаны на модели реляционной алгебры. Модель определяет отношения между сущностями и операции над ними: проекция, пересечение, объединение и другие. В базах данных эта модель представлена таблицами и операторами SQL. Как правило, оператор принимает несколько таблиц и возвращает табличный результат.

SQL — строго декларативный язык: в нем нет циклов, переменных и прочих возможностей, доступных в языках программирования. Говорят, что SQL не полон по Тьюрингу. Существуют несколько стандартов SQL, датированных годами: 92, 99, 2008 и другие. Кроме стандартных возможностей, современные базы предлагают расширенные операторы и типы данных.

В мире Java доступ к базам обеспечивает библиотека JDBC. Это универсальный интерфейс, который учитывает особенности каждой базы и приводит их к общему виду. JDBC нуждается в драйвере — библиотеке для конкретной базы данных, которая связывает высокоуровневый API с вводом-выводом. JDBC-драйверы доступны не только для классических баз PostgreSQL или MySQL, но и экзотических решений вроде файлов CSV и Excel.

Clojure предлагает легковесную обертку над JDBC — пакет clojure.java.jdbc. С ней вам доступны емкие и удобные функции для выборки, вставки и других операций с базой. Макросы помогают сократить код, но при этом не упустить важные моменты вроде установки и отката свойства autocommit. Библиотека содержит функции для типовых случаев: найти по id, найти по ключам, вставить запись по словарю и другие.

Чтобы связать типы данных базы и Clojure, расширьте протоколы. С их помощью легко сделать так, чтобы тип Timestamp, считанный из базы, был преобразован в DateTime из пакета JodaTime. Еще важнее обеспечить бесшовную связь с JSON, потому что этот тип несет колоссальные преимущества. Связь типов работает в обе стороны. Для установки параметров служит отдельный протокол, которые переводит типы Clojure в PGObject и другие объекты, известные JDBC.

Наибольшая опасность в SQL исходит от инъекций. Так называют прием, когда в запрос попадает вредоносный текст, например чтение системных таблиц или всегда истинное условие. Вероятность инъекции высока, если вы строите запросы конкатенацией (“склейкой”) строк, а также внедряете параметры в тело запроса. Тем же опасен и текст с кавычками: их умелая расстановка может нарушить логику запроса. Чтобы обезопасить запрос от кавычек, библиотеки экранируют их на этапе установки параметров.

В боевых проектах вы столкнетесь с тем, что запросы занимают много места и затрудняют поддержку кода. Кроме того, SQL неудобно строить по условию, когда заранее неизвестно, нужно ли делать отбор по некоторому полю или нет. Здесь вам помогут библиотеки. Некоторые из них выносят запросы в файл и порождают функции, которые исполняют одноименные запросы. Другие строят SQL из коллекций — комбинаций списков и словарей.

Как правило, реляционные базы возвращают данные в виде прямоугольных таблиц. На практике нас интересуют вложенные данные, например магазин и его товары, публикации и комментарии к ним. Существует два способа сгруппировать данные. Первый — получить плоский ответ базы и обработать на стороне Clojure. Второй — выполнить группировку в базе функциями array_agg или аналогами. Выбор способа зависит от окружения, объема данных и ваших навыков: в какой среде вы чувствуете себя уверенней.

Миграциями называют историю изменений базы. Как правило, это sql-файлы, которые добавляют таблицы, поля или индексы. Специальный код обходит эти файлы и применяет к базе. Отдельная таблица хранит имена обработанных миграций, чтобы не выполнять их повторно. Миграции автоматизируют рутину, позволяют оперативно откатить неудачные изменения. В локальной разработке ими переключают схему в соответствии с версией кода.

Библиотека clojure.java.jdbc — не единственная для работы с базой данных в Clojure. Обратите внимание на ее преемника JDBC.next. Новая библиотека работает с ответом базы более эффективно, что заметно на больших выборках. JDBC.next тесно связана с Clojure за счет встроенного протокола Datafy. Ее функции дают больше контроля за тем, что происходит с базой.