Готовим SQLAlchemy правильно
ORM и проблемы
ORM были призваны восполнить пробел между объектно-ориентированными языками программирования, которые предоставляют разработчикам возможность работать с сущностями путем обращения к их интерфейсам, определяемым их чертежами (интерфейсы, классы, структуры), и процедурным подходом, реализуемым движками SQL-серверов. В некоторых случаях сюда же пытаются включить и адаптеры NoSQL хранилищ, вроде MongoDB, но конкретно с ней сильно проще, поскольку документ и так, в целом, предствляет из себя вполне себе сносно организованный объект с полями, маппинг которых в объекты языка программирования весьма тривиален, по сравнению с SQL.
Другая проблема, которую пришлось решать ORM в процессе решения первой — сформировать инструмент, который позволил бы составить правильный SQL-запрос в терминах языка программирования, при этом постараться не потерять в доступных «в сыром виде» средствах выражения на соответствующем SQL-серверу диалекте.
Это, само собой, не полный список проблем, но это те, которые напрямую касаются данной статьи.
Проблемы, которые ORM поставляют вместе с решением
Главная проблема, о которой тоже речь пойдет здесь, заключается в неоптимальности решения «в лоб» способов работы с хранилищем. Как правило, ORM решения, помимо, собственно, маппера класс-поля в таблицы-колонки, предоставляют из коробки и возможность работы с этими классами и полями, как с таблицами и их колонками. Однако, серебряную пулю, позволяющую в каждом конкретном случае обеспечить автоматический выбор наиболее оптимального способа обновления данных или их выборки, они не способны.
Таблицы и их модели весьма часто имеют отношения между собой: One2One, One2Many, Many2Many. ORM, предоставляя способ декларации таких отношений в коде описания моделей, предоставляют же и способ обращения к связанным объектам из экземпляра конкретной модели. Беда в том, что оптимальные способы таких обращений отличаются в каждом конкретном случае:
Eager Load — вместе с объектами моделей, подгружаются и все их связанные сущности, как правило, через [OUTER|INNER] JOIN; в некоторых случаях это происходит каскадно для всей цепочки связей. Скажем, Address → District → City → State → Country. Однако, если в данном конкретном случае обращение к объектам моделей District и глубже не предполагается, выборка модели Address становится избыточно тяжелой, расходуя уйму ресурсов как на стороне СУБД, так и на стороне приложения, впустую
Lazy Load — проля связанных сущностей в каждом экземпляре модели становятся «активными полями», обращение к которым автоматически приводит к генерации и исполнению запросов к связанным таблицам с, как правило, тривиальными фильтрами по ключу связи. Это избавляет от тяжелых запросов в первой выборке, однако, если в конкретном случае предполагается, что будет последовательно производится доступ к объектам связанных сущностей, в данном случае запросы данных этих сущностей откладываются на потом, приводя к генерации и исполнению дополнительных запросов впоследствии. Плохо это тем, что, во-первых, сгенерировать запрос сам по себе для ORM — весьма тяжелая операция, а затем он передается в СУБД, где происходит трата ресурсов еще раз: его необходимо распарсить, провалидировать, создать план исполнения и только потом исполнить (будем считать, он тривиален, а потому время собственно исполнения запроса пренебрежимо мало), после чего сформировать ResultSet и передать его на сторону приложения. На стороне приложения при этом опять же происходит не очень, но всё еще тяжелая процедура создания экземпляров моделей из полученных от СУБД кортежей. Не очень просто, как это выглядит в коде вида
User.objects.query().filter(User.id == 1), правда?
Создатели решений ORM пытаются предоставить решения этой проблеме, конечно же. Например, предлагая модификаторы QueryObject, которые позволяют разработчику прямо на месте (пере)определить способ обработки связанных сущностей при выборке данных именно там, где это нужно. Тем самым, правда, простота использования ORM как способа прямого доступа к данным в СУБД начинает улетучиваться, а код начинает обрастать нагромождениями. Я неоднократно слышал (и даже здесь, на Хабре), что для людей, работающих с ORM, зачастую даже редко меняемый CHECK CONSTRAINT в таблице, не говоря уже о триггере и его функции — невероятно дорогой в поддержке код. Вот то же самое происходит с аннотациями протейших запросов, вроде того, что приведен выше.
Вторая проблема заключается в том, что ORM крайне избыточны. Представьте таблицу в СУБД, например, о 20 полях. Примерно половина из них — VARCHAR, по большей частью забитые строками по 2000 символов и более. Мы рассматриваем ситуацию, где все поля таблицы объявлены в модели, то есть, нет скрытых от приложения полей. Требуется проинспектировать одно поле INTEGER и поменять другое поле BOOLEAN на основании значения первого. Что делает ORM? Достаёт все двадцать полей в приложение. Это, во-первых, огромное время на формирование кортежей на стороне СУБД, затем огромное время на передачу их по сети (даже в том случае, когда СУБД локальная и общение происходит по unix socket, это далеко не бесплатная операция). Данные попадают в приложение и складируются в буфер. Он огромный. Затем буфер трансформируется в базовые сущности Row, которые еще не объекты моделей, а являются лишь отображением кортежа (строки возвращаемого набора) в объектно-ориентированную модель языка программирования: к этому моменту мы имеем минимум два объема каждого кортежа: один в буфере, второй в объектах Row. В этот момент буфер может очищаться, а может и нет, и тогда объем потребляемой памяти будет только расти дальше. А дальше начинается маппинг данных из Row в объекты класса, соответствующего таблице. Создаются экземпляры класса, затем данные из Row переносятся в поля экземпляра. Некоторые вынужденно копируются (например, числа за пределами free lists), некоторые всё же копируются по ссылке (например, строки). Итак, проведена титаническая работа, чтобы достать, скажем, 1000 строк из СУБД, по 20 полей в каждой, 10 из которых являются длинными строками, и сформировать из них 1000 экземпляров модели.
И всё это для того, чтобы затем приложение обошло коллекцию из 1000 тяжёлых объектов, посмотрело в одно поле в каждой, изменило (или нет) значение в другом поле. А затем мы начинаем сохранять. И здесь располагается еще один уровень ада.
Сохранять объекты моделей в базе можно двумя способами:
передавать как есть текущее состояние объекта модели обратно в запрос типа UPDATE или INSERT. Представили, да? Вот всё то, что мы делали при извлечении данных из СУБД, только в обратном порядке и в обратную сторону.
реализовать трекинг каждого поля в каждом экземпляре объекта модели и помечать их, если их значения меняются. Это позволит при обновлении формировать по каждому экземпляру минимальный и достаточный UPDATE, который задействует только те поля (колонки), которые действительно поменялись. А заодно позволяет пропустить те экземпляры, в которых ничего и не менялось (то есть, такие, которые приводят к пустому UPDATE). Однако, это означает, что
myobject.field = new_value— уже не такая тривиальная операция. За это приходится платить во время каждого присвоения значения объекту модели (даже новому, которого в базе еще нет).
В SQLAlchemy ORM, кстати, используется второй подход. Не без огрехов, конечно же. По крайней мере, до версии 2.0 моим излюбленным «багом» было отсутствие трекинга полей, значения которых являются ссылочными. Например, словари. Это потому, что ORM в общем случае не имеет понятия, изменилось ли значение, если ссылка (id(self.value)) не поменялась. Сравнивать через == тоже не всегда подходит. В общем, «добавить ключ в поле-словарь и гарантировать обновление этого поля в СУБД» было отдельным весельем.
А как победить?
Комбинировать. Да, это не очередная статья о том, что «только сырой SQL, только хардкор». Я не буду призывать полностью отказаться от import sqlalchemy.orm, однако, скомбинировав его и sqlalchemy.sql можно получить плюсы обоих и, по возможности, избавиться от минусов обоих же.
Однако, конечно, немного ужаса в умах приверженцев «чистого ORM» я рискую пробудить.
PoC
Для начала структура проекта, чтобы было понятно, что где находится
>--lib
>--db
| >--connection.py
| >--__init__.py
>--model
| >--user.py
| >--__init__.py
>--query
| >--compiler
| | >--compile_hacks.py
| | >--query_compiler.py
| | >--__init__.py
| | >--__main__.py
| >--user.py
| >--user_compiled.py
| >--__init__.py
>--repository
| >--user.py
| >--__init__.py
>--__init__.py
Покажи код!
Поехали.
# lib.db.connection
from sqlalchemy import create_engine
engine = create_engine("postgresql+asyncpg:///", future=True, paramstyle="named")
Тривиальный инстанс Engine. На текущий момент он нам нужен только для двух вещей:
Само собой, в продакшне здесь будет что-то, что объявляет URL не из кода, а из более защищенного хранилища данных о подключении к СУБД: KeyVault, EnvVar, что угодно. В этом случае объявление движка можно перенести напрямую в модуль query_compiler, а здесь оставить продакшн логику.
# lib.db.model
from sqlalchemy.orm import DeclarativeBase
class Base(DeclarativeBase):
pass
По учебнику в документации по SQLAlchemy, ничего особенного
# lib.db.model.user
from typing import Optional
from sqlalchemy.orm import Mapped, mapped_column
from lib.model import Base
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
name: Mapped[str] = mapped_column()
enabled: Mapped[bool] = mapped_column()
address: Mapped[Optional[str]] = mapped_column()
В общем, тоже ничего особенного. Разве что я немного удивился, насколько далеко продвинулся zzzeek в обеспечении декларативности ORM. Раньше я вручную объявлял как типы, так и сами колонки через Column (). В любом случае, это PoC, оторванная от реальности таблица (впрочем, не противоречащая ни правилам ORM, ни правилам SQL и его диалекта PostgreSQL). Просто для справки относительно того, с чем дальше будем работать.
# lib.query.compiler.__main__
from lib.query.compiler.query_compiler import query_compiler
if __name__ == '__main__':
query_compiler()
Всего лишь простейшая обёртка, которая позволит вызывать компилятор запросов как python -m lib.query.compiler
# lib.query.compiler.compile_hacks
from sqlalchemy import BindParameter
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.base import _NoArg
@compiles(BindParameter)
def _render_literal_bindparam(element, compiler, **kw):
params = dict(**kw)
if element.value is not _NoArg.NO_ARG and element.value is not None:
params["literal_binds"] = True
return compiler.visit_bindparam(element, **params)
Вот здесь уже начинается интересное. Авторы SQLAlchemy не предполагали именно такого использования библиотеки (предкомпиляция запросов в текстовый вид), впрочем, и не запрещали явно. Смысл данного «хака» заключается в следующем.
Когда пользователь строит запрос с помощью Query Builder (sa.select, sa.delete, sa.update, sa.insert), в некоторых случаях необходимо передать в запрос определенные константы. Ну, например, как будет показано дальше, константу, которой необходимо подменить значение выходной колонки, если она удовлетворяет определенным условиям. Все такие константы автоматически оборачиваются в объекты BindParameter (sa.bindparam). Однако, как будет показано дальше, у нас сами запросы строятся параметризованные, поэтому хотелось бы константы отрендерить как есть, а уже специальные параметры оформить в виде таковых, чтобы уже в рантайме иметь возможность передавать их и только их.
Это штатный механизм декларативного перехвата внутренних механизмов SQLAlchemy. Объявляем функцию, которая отвечает за компиляцию объектов BindParameter в текст SQL-запроса. В случае, если у объекта не установлено заранее значение (в константах и литералах в нашем запросе ниже значения будут), оставляем, как есть. В случае же, если значение установлено, модифицируем аргументы штатного компилятора-визитора, чтобы литералы рендерились именно литералами, а не параметрами запроса, мы ведь не хотим затем таскать с собой константы, которые и так в запросе указаны.
Здесь есть небольшой простор для багов: во-первых, значение по умолчанию у BindParameter (NO_ARG) еще во время создания подменяется на None, поэтому, конечно, проверка на него в данном случае избыточна, но я оставил; во-вторых, может так статься, что захочется передать в качестве литерала именно None (впрочем, зачем бы это делать? для NULL в sa.sql есть функция null (), которая генерирует самый настоящий SQL NULL в тексте запроса; равно как есть и методы у полей: is_(null()), isnot(null())). В общем, я предупредил. Где это править, если что — тоже показал. Напрашивается сам собой собственный Enum-marker, значение которого можно было бы использовать для индикации.
# lib.query.compiler.query_compiler
import logging
import os
from importlib import import_module
from typing import Callable, Dict, Union
from sqlalchemy import Selectable
from sqlalchemy.sql.dml import DMLWhereBase
from lib.db.connection import engine
# noinspection PyUnresolvedReferences
import lib.query.compiler.compile_hacks
def query_compiler():
dir_path = os.path.sep.join(
(
os.path.dirname(__file__),
"..",
)
)
for q_module in os.listdir(dir_path):
compiled_queries = dict()
full_path = os.path.sep.join((dir_path, q_module))
if (
not q_module.endswith(".py")
or
q_module.endswith("_compiled.py")
or
q_module.startswith("__")
or
not os.path.isfile(full_path)
):
continue
mod = import_module(f"..{q_module[:-3]}", __package__)
fn: Callable[[], Dict[str, Union[Selectable, DMLWhereBase]]]
try:
fn = getattr(mod, "generate_queries")
if not callable(fn):
continue
except AttributeError:
continue
try:
for q_name, q_val in fn().items():
if not q_name.endswith("_query"):
continue
compiled = q_val.compile(engine)
compiled_queries[
q_name.upper()
] = str(compiled)
except Exception as e:
logging.exception(e)
continue
result_module = os.path.sep.join(
(
dir_path,
q_module.replace(".py", "_compiled.py")
)
)
with open(result_module, "w", encoding="utf-8") as fd:
fd.write("from sqlalchemy import text\n\n")
fd.writelines(f"{k} = text('''\n{v}\n''')" for k, v in compiled_queries.items())
Основная наша прелесть. Знакомьтесь: это — компилятор запросов в текстовый вид.
Что он делает:
бежит по каталогу уровнем выше, ищет все модули Python, которые не каталоги, не начинаются на dunder, не имеют префикса _compiled
каждый из них отдельно импортирует и ищет в импортированном модуле функцию generate_queries ()
если находит, вызывает ее
в результате ожидает слоарь, у которого ключи строки, а значения — объекты подготовленных запросов
мы же не хотим ограничивать себя, поэтому предусмотрен следующий протокол: компиляции подлежат только те объекты запросов, ключи которых в словаре оканчиваются на _query, весь остальной «мусор» (например, промежуточные объекты, такие, как CTE и SubQuery) будут отброшены, если, конечно, их ключи в возвращаемом словаре, не подпадут под шаблон
вот тут в 49-й строке происходит, собственно, компиляция
помните, выше я определил перехватчик компилятора BindParameter? Вот он в процессе вызова этой функции и будет вызываться для соответствующих объектов
подготовленный заранее engine подается, чтобы компилятор из него достал информацию о драйвере и диалекте: диалект определяет общий синтаксис будущего запроса, а драйверу могут передаваться константы и литералы для безопасного экранирования их значений, asyncpg, по крайней мере, вызывается, psycopg2 тоже имел соответствующие интерфейсы
а дальше все скомпилированные запросы сохраняются в модуль, который от исходного отличается только суффиксом _compiled
при этом все текстовые запросы оборачиваются в sa.text (), чтобы быть готовыми к непосредственной передаче напрямую в engine.execute (); в SQLAlchemy 2.0 был изменен этот интерфейс и чистая строка в этом методе больше не принимается
при этом в дальнейшем при работе с этими запросами в рантайме модель уже участвовать не будет, равно как не будет даже импортироваться, что поможет сберечь несколько миллисекунд на старте (мелочь, а приятно)
Подобный ожидаемый интерфейс накладывает и определенные требования на реализацию собственно модулей, генерирующих запросы.
# lib.query.user
from sqlalchemy import select, case, null, func, bindparam, update
from lib.model.user import User
def generate_queries():
users_query = (
select(
User.id.label("id"),
User.name.label("name"),
case(
(User.address.isnot(null()), User.address),
else_="N/A"
).label("address"),
(
func
.row_number()
.over(
order_by=func.lower(User.name),
range_=(0, 26),
partition_by=func.lower(func.left(User.name, 1))
)
.label("cohort")
)
)
.where(
((User.id % 2) == bindparam("even_odd", type_=User.id.type))
|
(
User.name.contains(bindparam("name_includes"))
&
User.enabled.is_(bindparam("enabled_filter"))
).self_group()
)
)
some_intermediate_thing = select(User.id).subquery("aliased")
return locals()
Итак:
в силу того, что для генерации запросов необходимо вызвать вручную функцию generate_queries (), мы можем позволить себе отложить эту генерацию на неопределенное время, предоставив компилятору запросов, например, возможность, подставить некоторые дополнительные значения в контекст модуля, и уже в функции generate_queries () задействовать эти константы; патч для компилятора будет тривиальный (объявляем протокольную функцию setup_module (**kw), которая обогащает глобальную область видимости модуля значениями, ищем и вызываем ее в компиляторе, затем вызываем уже generate_queries ())
в простейшем случае достаточно вернуть из функции locals (), там уже компилятор сам подберёт нужное; впрочем, это не обязательно, можно собрать объекты в локальном контексте, а затем вручную собрать словарь с нужными ключами и их значениями-запросами; главное соблюдать протокол: то, что подлежит компиляции, должно в имени ключа соответствовать требованию «оканчивается на _query»
sa.sql. Да, как видите, Query Builder у zzzeek вышел отменный. Он пишется примерно так же, как писался бы чистый SQL запрос, с оглядкой на синтаксис Python, конечно же. Помимо прочего, присутствуют штатные обёртки над func, которые позволяют превратить произвольную функцию в коде в оконную (вызывая .over () пользователь автоматически конвертирует функцию в оконную, что влияет на то, как она отрендерится в запросе; это будет полезно тем, кто не гнушается объявлением своих функций в схеме БД)
да, я, конечно же, знаю про «коляску» (coalesce), case здесь поставлен намеренно, чтобы продемонстрировать более многосложные конструкции для сравнения с тем, как они выглядели бы в SQL, и для оценки того, насколько синтаксис похож на чистый SQL
отдельно хочется отметить наличие сахара в WHERE. Можно, конечно же, писать выражения в классическом стиле, через and_() и or_(); я продемонстрировал и возможность писать их через бинарные операторы | и &; к сожалению, иного способа заключить группу выражений в скобки в SQL явным образом, кроме вызова .self_group (), я не могу представить. Разве что, ползать по AST, что взорвало бы сложность SQLAlchemy. В целом, хоть и отличие от чистого SQL, не такое и страшное
мы же в Python, поэтому и можем себе позволить воспользоваться имеющимися средствами облегчения работы с разными типами данных, например, str.contains () гораздо удобнее и приятнее писать, чем его близкий к SQL
'%' || :param || '%'аналог. Алхимия перехватит этот вызов для mapped_column и превратит на выходе это в корректный SQLзапрос users_query будет скомпилирован, а some_intermediate_thing не будет (не соответствует шаблону в компиляторе)
Результат
# lib.query.user_compiled
from sqlalchemy import text
USERS_QUERY = text('''
SELECT users.id AS id, users.name AS name, CASE WHEN (users.address IS NOT NULL) THEN users.address ELSE 'N/A' END AS address, row_number() OVER (PARTITION BY lower(left(users.name, 1)) ORDER BY lower(users.name) RANGE BETWEEN CURRENT ROW AND 26 FOLLOWING) AS cohort
FROM users
WHERE users.id % 2 = :even_odd::INTEGER OR ((users.name LIKE '%' || :name_includes::VARCHAR || '%') AND users.enabled IS :enabled_filter::BOOLEAN)
''')Он же с подсветкой и немного отформатированный для читаемости:
SELECT
users.id AS id
, users.name AS name
, CASE
WHEN (users.address IS NOT NULL)
THEN users.address
ELSE 'N/A'
END AS address
, row_number()
OVER (
PARTITION BY lower(left(users.name, 1))
ORDER BY lower(users.name)
RANGE BETWEEN CURRENT ROW AND 26 FOLLOWING
) AS cohort
FROM users
WHERE
users.id % 2 = :even_odd::INTEGER
OR (
(users.name LIKE '%' || :name_includes::VARCHAR || '%')
AND
users.enabled IS :enabled_filter::BOOLEAN
)во-первых, данный запрос отлично вставляется в исходном виде в DataGrip и, подозреваю, многие другие средства работы с базами данных SQL; при выполнении достаточно будет прямо в DataGrip подставить значения параметров (он сам спросит)
во-вторых, не придирайтесь к ускользающему за горизонт смыслу этого запроса, его нет, это не выдержка из продакшн реализации, а всего лишь демонстрация; скажу лишь, что в прод мы бросали весьма развесистые запросы, которые на выходе брали JSON, разворачивали его в таблицу, делали UPSERT по таблицам на основе этих данных, помечали в таблице успешные и неуспешные вставки (да, приходилось CHECK CONSTRAINTS повторять кодом в условиях запросов), с указанием причины провала, и возвращали тоже JSON, который приложение уже проверяло и выдавало фронтенду маркеры строк, которые надо подсветить пользователю с указанием того, что пошло с конкретной строкой не так
в-третьих, использование в коде приложения весьма тривиальное:
from lib.db.connection import engine
from lib.query.user_compiled import USERS_QUERY
with engine.connect() as connection, connection.begin() as _:
connection.execute(
USERS_QUERY,
dict(
even_odd=0,
name_includes="baba",
enabled_filter=True
)
)
А в чём выигрыш?
Во-первых, обратите внимание, в конструкторе запроса я обращался к полям модели. Это гарантирует мне то, что при рефакторинге, да и при простом переименовании поля, мой запрос останется компилируемым. А при более серьезных изменениях, например, изменении типа поля, я через поле в модели перейду к местам, где оно используется (то есть, запросы в генераторах) и придумаю, как переписать запросы, чтобы оно продолжало работать в новой версии модели.
Во-вторых, как выше уже было замечено, ORM никуда не делся, просто ему отведена роль простого маппера класс → таблица (view). А, значит, в поддерживаемых случаях мы всё еще можем расчитывать на помощь alembic для генерации миграций.
В-третьих, конечно же, мы теперь не ограничены в средствах выражения того, что хотим сделать с помощью запроса, как это часто бывает при использовании ORM «в лоб». Запросы любой сложности, вложенности, с CTE и без, UNION, UPDATE/DELETE RETURNING, array_agg — все эти прелести вот прямо под ногами, бери и пользуйся. Для конкретно PostgreSQL в его диалекте есть и свои расширенные версии Insert, позволяющие описать ON CONFLICT … DO … Само собой, для MySQL или Oracle имеются тоже свои расширения стандартных классов.
В-четвертых, раз уж мы вольны сами теперь определять, что, как и в каком порядке делать с данными, мы, конечно же, перестанем считать row_number () на стороне приложения, по крайней мере там, где это может сделать за нас СУБД (она это сделает быстрее и эффективнее). Затем мы перестанем дёргать из базы те поля, которые в данном конкретном случае не нужны. А затем, еще больше войдя в раж, мы перестанем вообще делать SELECT, чтобы обновить данные, особенно, массово, в какой-либо таблице. Ведь условия нам известны, мы просто опишем их в запросе, каким бы сложным он ни вышел и насколько много других таблиц нам не пришлось бы подключить для фильтрации строк в таблице, и сделаем сразу UPDATE или INSERT ON CONFLICT DO UPDATE.
В-пятых, можно использовать результаты компиляции запросов в качестве детектора того, что в новой версии алхимии что-то пошло не так. Ведь, если скомпилированный запрос в новой версии отличается от него же в предыдущей версии, при условии, что обновилась только версия алхимии, то это значит, нужно срочно смотреть, что поменялось, как это влияет на приложение, и как это исправить, если требуется. Это можно сделать, например, используя двухфазный деплой. Первым делом во время деплоя компилируются запросы, затем хеши файлов со скомпилированными запросами сверяются-складываются в репозиторий деплой-инфраструктуры (в тот же стейт файл), а затем билд-деплой останавливается, если хеши отличаются от предыдущих значений с уведомлением ответственных за релиз.
Да, конечно, многим придется сломать себя, прежде чем оценить подход, использующий сырые SQL вместо «удобного и в одну строчку» ORM QueryObject. А кому-то и не удастся, ведь всегда есть возможность найти причины не делать.
Что я забыл?
Вот мой телеграм: нет его
Ну ладно, а ютуб (ставить лай, подписываться, колокольчик)? Нет его.
А что же ты можешь тогда порекламировать в лучших традициях? Ну, например, вот эту статью.
