Посты / Rails + Postgres + bindings

16.01.2019 12:34
Привет друзья. Ни для кого не секрет, что работая на крупных проектах со сложной логикой, Active Record становится не помощником, а обузой. Представьте, что вам необходимо сделать очень сложный запрос для PostgreSQL нативным образом (на чистом SQL), где должно присутствовать некоторое количество переменных. Но в Rails есть одна неприятная мелочь, функционал выполнения нативных запросов не позволяет использовать именованные биндинги. Но решение есть :) Опробовано и успешно внедрено на проекте с Rails API 5.2 + Ruby 2.6.0 + Postgres 11.

Итак, чуть подробнее о проблеме. Основной метод, который позволяет вам выполнять собственные SQL запросы - это exec_query:
sql = 'SELECT id, name, desc FROM schema.news WHERE id=$1'
bindings = [[nil, 100]]
new = ActiveRecord::Base.connection.exec_query(sql, 'SQL', bindings).first
На примере выше видно, что формирование биндингов происходит, мягко говоря, через одно место, когда мы пытаемся получить из базы новость под номером 100. Биндинги не могут быть именованными, а только нумерованными. А это очень сильно усложняет чтение и поддержку нативных запросов. Как вариант можно использовать вызов метода find_by_sql для класса модели:
sql = 'SELECT id, name, desc FROM schema.news WHERE id=:id'
new
= New.find_by_sql([sql, id: 100]).first
Здесь уже все приятнее и понятнее. Но вот вопрос, это более менее приемлемо, если требуется выполнить простой запрос. А вот если запрос реально сложный, то прогонять его через модель и сам Active Record - это большая потеря в скорости (медленно) и производительности (жрет ресурсы сервера). Почему нет именованных биндингов при работе с нативными запросами, для меня загадка, но решение есть - это написать собственную небольшую обертку, которая умеет очень просто работать с именованными биндингами, что я и сделал.

Привожу код статического класса:
# Class for work with SQL query.
# Can use clean SQL with hash bindings.
# Convert JSON fields to hash.
# Can use if not need get model object!
class SqlQuery
# Create sql query with hash bindings
#
# @param [String] sql SQL query
# @param [Hash] bind bindings data for query
#
# @return [Array] executed SQL request data and return array with hashes
def self.execute(sql, bind = {})
bindings = []
bind_index = 1

# Get all bindings if exist
unless bind.empty?
bind.each do |key, value|
# Change name bind to $ bind
sql.gsub!(/(?<!:):#{key}(?=\b)/, "$#{bind_index}")
bind_index += 1

# Add new bind data
bindings << [nil, value]
end
end

# Execute query, convert to hash with symbol keys
result = ActiveRecord::Base.connection.exec_query(sql, 'SQL', bindings).map(&:symbolize_keys)

# Convert JSON data to hash
result.map do |v|
next if v.nil?

v.each do |key, val|
v[key] = json_to_hash(val)
end
end
end

# Convert JSON to hash if correct data
#
# @param [String] json string
# @return [Hash] return hash if json is correct or input data
def self.json_to_hash(json)
JSON.parse(json, symbolize_names: true) rescue json
end
end
Как видно из кода, все просто, как угол дома. Запрос работает так:
sql = 'SELECT id, name, desc FROM schema.news WHERE id=:id'
binding = { id: 100 }
new
= SqlQuery
.execute(sql, binding).first
На выходе всегда получается только хэш. Немного пояснений. Метод execute принимает в себя строку запроса и хэш с биндингами. Понятно, что биндинги в запросе и хэше должны совпадать. После чего мы проходим циклом по хэшу с биндингами и заменяем их на нумерованные переменные вида $1, $2 и т. д. в самом запросе, попутно создавая массив нумерованных значений, где первый элемент массива равен $1, второй - $2 и так далее. После чего выполняем запрос с помощью стандартного метода exec_query, пробегая по ответу мапером и конвертируя ключи в хэше на символы. После этого мы еще раз пробегаем мапером по ответу, где проверяем каждое значение поля на содержание в нем JSON. Если JSON есть и он валиден, то конвертируем его в хэш с ключами символами, если в поле не JSON, то кидаем исключение, в котором возвращаем назад значение. Вот и все.

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

Всем удачи, до новых встреч.
1

Комментарии (2):

Vorchun писал(а):
Так и не понял в чем проблема. Чем запросы вида: News.where(News.arel_table[:order_date].between(Date.current-2.year..Date.current-1.year)).where.not(owner_id: nil).pluck(:id) не подходят? ведь они будут преобразованы в один sql запрос. При желании пожно и join таблице сделать и еще where добавить.
В данном примитивном примере все вы правильно написали. Хотя такой запрос с ходу не читается и нужно прилично напрячься. А теперь попробуйте собрать вот такой запрос:
SELECT (SELECT json_agg(row_to_json(agg_notes))
FROM (SELECT n.id, n.title, n.body, to_char(n.created_at, 'DD.MM.YYYY HH24:MI') as created
FROM data.notes n
WHERE n.user_id = u.id) agg_notes) as notes,
(SELECT json_agg(row_to_json(agg_projects))
FROM (SELECT p.id,
p.name,
p.description,
to_char(p.created_at, 'DD.MM.YYYY HH24:MI') as created,
(SELECT true WHERE EXISTS(SELECT p1.id FROM data.projects p1
WHERE p1.id = p.id AND p1.report IS NOT NULL)) as report
FROM data.projects p
WHERE p.user_id = u.id) agg_projects) as projects,
(SELECT json_agg(row_to_json(agg_contacts))
FROM (SELECT c.id, c.message, c.ip, to_char(c.created_at, 'DD.MM.YYYY HH24:MI') as created
FROM data.contacts c
WHERE c.user_id = u.id) agg_contacts) as contacts
FROM data.users u
WHERE u.id = :user_id;
И прошу заметить, это нисколько несложный запрос. А очень даже самый простой, по сравнению с теми, которые встречаются в проекте (по 50 строк и более). А есть запросы, где нужно вставить с десяток биндингов.

А есть еще куча запросов с такими вещами, как полнотекстовый поиск to_tsvector, to_tsquery и т.д., работа с полями (включая логику поиска) по пересечению значений из JSONB по массиву и так далее.

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

При моем же подходе, я создаю папку репозитарий, где храню статические классы с методами, которые возвращают подготовленный запрос. Например:
# Repository for mailings
class MailingRepository
# Get all emails with LIMIT and OFFSET
def self.emails_limit
'SELECT
e.id,
e.email,
e.status,
e.action,
e.updated_at::timestamp,
(SELECT json_agg(row_to_json(agg_groups))
FROM (SELECT eg.group_id
FROM mailing.emails_in_group eg
WHERE eg.email_id = e.id) agg_groups) as groups
FROM mailing.emails e
ORDER BY e.id DESC LIMIT :limit::int OFFSET :offset::int;'
end
end
Этот запрос забирает с базы все адреса (с лимитом) и создает поле с JSON, куда поместятся все группы, к которым привязан адрес.

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

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

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

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

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

Надеюсь, что я донес свою мысль :)
Так и не понял в чем проблема. Чем запросы вида:
News.where(News.arel_table[:order_date].between(Date.current-2.year..Date.current-1.year)).where.not(owner_id: nil).pluck(:id)
не подходят? ведь они будут преобразованы в один sql запрос. При желании пожно и join таблице сделать и еще where добавить.