SQLAlchemy: а ведь раньше я презирал ORM

Так вышло, что на заре моей карьеры в IT меня покусал Oracle — тогда я ещё не знал ни одной ORM, но уже шпарил SQL и знал, насколько огромны возможности БД.

Знакомство с DjangoORM ввело меня в глубокую фрустрацию. Вместо возможностей — хрена с два, а не составной первичный ключ или оконные функции. Специфические фичи БД проще забыть. Добивало то, что по цене нулевой гибкости мне продавали падение же производительности — сборка ORM-запроса не бесплатная. Ну и вишенка на торте — в дополнение к синтаксису SQL надо знать ещё и синтаксис ORM, который этот SQL сгенерирует. Недостатки, которые я купил за дополнительную когнитивную нагрузку — вот уж где достижение индустрии. Поэтому я всерьёз считал, что без ORM проще, гибче и в разы производительнее — ведь у вас в руках все возможности БД.

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

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

Я занимался оптимизацией SQL-запросов. Мне удавалось добиться стократного и более уменьшения cost запросов, в основном для Oracle и Firebird. Я проводил исследования, экспериментировал с индексами. Я видел в жизни много схем БД: среди них были как некоторое дерьмо, так и продуманные гибкие и расширяемые инженерные решения.

Этот опыт сформировал у меня систему взглядов касательно БД:

• ORM не позволяет забыть о проектировании БД, если вы не хотите завтра похоронить проект

• Переносимость — миф, а не аргумент:

  • Если ваш проект работает с postgres через ORM, то вы на локальной машине разворачиваете в докере postgres, а не работаете с sqlite

  • Вы часто сталкивались с переходом на другую БД? Не пишите только «Однажды мы решили переехать…» — это было однажды. Если же это происходит часто или заявленная фича, оправданная разными условиями эксплуатации у разных ваших клиентов — милости прошу в обсуждения

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

• Структура таблиц определяется вашими данными, а не ограничениями вашей ORM

Естественно, я ещё и код вне БД писал, и касательно этого кода у меня тоже сформировалась система взглядов:

• Контроллер должен быть тонким, а лучший код — это тот код, которого нет. Код ORM — это часть контроллера. И если код контроллера спрятан в библиотеку, это не значит, что он стал тонким — он всё равно исполняется

• Контроллер, выполняющий за один сеанс много обращений к БД — это очень тонкий лёд

• Я избегаю повсеместного использования ActiveRecord — это верный способ как работать с неконсистентными данными, так и незаметно для себя сгенерировать бесконтрольное множество обращений к БД

• Оптимизация работы с БД сводится к тому, что мы не читаем лишние данные. Есть смысл запросить только интересующий нас список колонок

• Часть данных фронт всё равно запрашивает при инициализации. Чаще всего это категории. В таких случаях нам достаточно отдать только id

• Отладка всех новых запросов ORM обязательна. Всегда надо проверять, что там ORM высрала (тут пара сочных примеров), дабы не было круглых глаз. Даже при написании этой статьи у меня был косяк как раз по этому пункту

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

Все вопросы данной статьи происходят из моего опыта и системы взглядов

Знакомство в SQLAlchemy

Первое, что бросилось в глаза — возможность писать DML-запросы в стиле SQL, но в синтаксисе python:

order_id = bindparam('order_id', required=True) return \     select(         func.count(Product.id).label("product_count"),         func.sum(Product.price).label("order_price"),         Customer.name,     )\     .select_from(Order)\     .join(         Product,         onclause=(Product.id == Order.product_id),     )\     .join(         Customer,         onclause=(Customer.id == Order.customer_id),     )\     .where(         Order.id == order_id,     )\     .group_by(         Order.id,     )\     .order_by(         Product.id.desc(),     )

Этим примером кода я хочу сказать, что ORM не пытается изобрести свои критерии, вместо этого она пытается дать нечто, максимально похожее на SQL. К сожалению, я заменил реальный фрагмент ORM-запроса текущего проекта, ибо NDA. Пример крайне примитивен — он даже без подзапросов. Кажется, в моём текущем проекте таких запросов единицы.

Естественно, я сразу стал искать, как тут дела с составными первичными ключами — и они есть! И оконные функции, и CTE, и явный JOIN, и много чего ещё! Для особо тяжёлых случаев можно даже впердолить SQL хинты! Дальнейшее погружение продолжает радовать: я не сталкивался ни с одним вопросом, который решить было невозможно из-за архитектурных ограничений. Правда, некоторые свои вопросы я решал через monkey-patching.

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

Насколько крутым и гибким бы ни было API, краеугольным камнем является вопрос производительности. Сегодня вам может и хватит 10 rps, а завтра вы пытаетесь масштабироваться, и если затык в БД — поздравляю, вы мертвы.

Производительность query builder в SQLAlchemy оставляет желать лучшего. Благо, это уровень приложения, и тут масштабирование вас спасёт. Но можно ли это как-то обойти? Можно ли как-то нивелировать низкую производительность query builder? Нет, серьёзно, какой смысл тратить мощности ради увеличения энтропии Вселенной?

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

Для SQLAlchemy тоже есть обходные пути, и их сразу два, и оба сводятся к кэшированию по разным стратегиям. Первый — применение bindparam и lru_cache. Второй предлагает документация — future_select. Рассмотрим их преимущества и недостатки.

bindparam + lru_cache

Это самое простое и при этом самое производительное решение. Мы покупаем производительность по цене памяти — просто кэшируем собранный объект запроса, который в себе кэширует отрендеренный запрос. Это выгодно до тех пор, пока нам не грозит комбинаторный взрыв, то есть пока число вариаций запроса находится в разумных пределах. В своём проекте в большинстве представлений я использую именно этот подход. Для удобства я применяю декоратор cached_classmethod, реализующий композицию декораторов classmethod и lru_cache:

from functools import lru_cache  def cached_classmethod(target):     cache = lru_cache(maxsize=None)     cached = cache(target)     cached = classmethod(cached)     return cached

Для статических представлений тут всё понятно — функция, создающая ORM-запрос не должна принимать параметров. Для динамических представлений можно добавить аргументы функции. Так как lru_cache под капотом использует dict, аргументы должны быть хешируемыми. Я остановился на варианте, когда функция-обработчик запроса генерирует «сводку» запроса и параметры, передаваемые в сгенерированный запрос во время непосредственно исполнения. «Сводка» запроса реализует что-то типа плана ORM-запроса, на основании которой генерируется сам объект запроса — это хешируемый инстанс frozenset, который в моём примере называется query_params:

class BaseViewMixin:     def build_query_plan(self):         self.query_kwargs = {}         self.query_params = frozenset()      async def main(self):         self.build_query_plan()         query = self.query(self.query_params)          async with BaseModel.session() as session:             respone = await session.execute(                 query,                 self.query_kwargs,             )             mappings = respone.mappings()          return self.serialize(mappings)

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

Сколько же памяти я заплатил за это? А немного. На все вариации запросов я расходую не более мегабайта.

future_select

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

stmt = lambdas.lambda_stmt(lambda: future_select(Customer)) stmt += lambda s: s.where(Customer.id == id_)

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

class FutureSelectWrapper:     def __init__(self, clause):         self.stmt = lambdas.lambda_stmt(             lambda: future_select(clause)         )          def __getattribute__(self, name):         def outer(clause):             def inner(s):                 callback = getattr(s, name)                 return callback(clause)                          self.stmt += inner             return self         return outer

Промежуточный вывод: низкую производительность query builder в SQLAlchemy можно нивелировать кэшем запросов. Дикий синтаксис future_select можно спрятать за фасадом.

А ещё я не уделил должного внимания prepared statements. Эти исследования я проведу чуть позже.

Как я открывал для себя ORM заново

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

Модульность

Когда я реализовывал на SQL дикую аналитику, старой болью отозвалось отсутствие модульности и интроспекции. При последующем переносе на ORM у меня уже была возможность выкинуть весь подзапрос поля FROM в отдельную функцию (по факту метод класса), а в последующем эти функции было легко комбинировать и на основании флагов реализовывать паттерн Стратегия, а также исключать дублирование одинакового функционала через наследование.

Собственные типы

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

Создание собственных простых типов рассмотрено в документации:

class ColorType(TypeDecorator):     impl = Integer     cache_ok = True      def process_result_value(self, value, dialect):         if value is None:             return          return color(value)      def process_bind_param(self, value, dialect):         if value is None:             return          value = color(value)         return value.value

Сыр-бор тут только в том, что мне стрельнуло хранить цвета не строками, а интами. Это исключает некорректность данных, но усложняет их сериализацию и десериализацию.

Теперь про ENUM. Меня категорически не устроило, что документация предлагает хранить ENUM в базе в виде VARCHAR. Особенно уникальные целочисленные Enum хотелось хранить интами. Очевидно, объявлять этот тип мы должны, передавая аргументом целевой Enum. Ну раз String при объявлении требует указать длину — задача, очевидно, уже решена. Штудирование исходников вывело меня на TypeEngine — и тут вместо примеров использования вас встречает «our source code is open 24/7». Но тут всё просто:

class IntEnumField(TypeEngine):     def __init__(self, target_enum):         self.target_enum = target_enum         self.value2member_map = target_enum._value2member_map_         self.member_map = target_enum._member_map_      def get_dbapi_type(self, dbapi):         return dbapi.NUMBER      def result_processor(self, dialect, coltype):         def process(value):             if value is None:                 return              member = self.value2member_map[value]             return member.name          return process      def bind_processor(self, dialect):         def process(value):             if value is None:                 return              member = self.member_map[value]             return member.value          return process

Обратите внимание: обе функции — result_processor и bind_processor — должны вернуть функцию.

Собственные функции, тайп-хинты и вывод типов

Дальше больше. Я столкнулся со странностями реализации json_arrayagg в mariadb: в случае пустого множества вместо NULL возвращается строка "[NULL]" — что ни под каким соусом не айс. Как временное решение я накостылил связку из group_concat, coalesce и concat. В принципе неплохо, но:

1. При вычитывании результата хочется нативного преобразования строки в JSON.

2. Если делать что-то универсальное, то оказывается, что строки надо экранировать. Благо, есть встроенная функция json_quote. Про которую SQLAlchemy не знает.

3. А ещё хочется найти workaround-функции в объекте sqlalchemy.func

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

from sqlalchemy.sql.functions import GenericFunction, register_function from sqlalchemy.sql import sqltypes from sqlalchemy import func, literal_column   def register(target):     name = target.__name__     register_function(name, target)     return target   # === Database functions ===   class json_quote(GenericFunction):     type = sqltypes.String     inherit_cache = True   class json_object(GenericFunction):     type = sqltypes.JSON     inherit_cache = True   # === Macro ===   empty_string = literal_column("''", type_=sqltypes.String) json_array_open = literal_column("'['", type_=sqltypes.String) json_array_close = literal_column("']'", type_=sqltypes.String)   @register def json_arrayagg_workaround(clause):     clause_type = clause.type      if isinstance(clause_type, sqltypes.String):         clause = func.json_quote(clause)      clause = func.group_concat(clause)     clause = func.coalesce(clause, empty_string)      return func.concat(         json_array_open,         clause,         json_array_close,         type_=sqltypes.JSON,     )   def __json_pairs_iter(clauses):     for clause in clauses:         clause_name = clause.name         clause_name = "'%s'" % clause_name         yield literal_column(clause_name, type_=sqltypes.String)         yield clause   @register def json_object_wrapper(*clauses):     json_pairs = __json_pairs_iter(clauses)     return func.json_object(*json_pairs)

В рамках эксперимента я также написал функцию json_object_wrapper, которая из переданных полей собирает json, где ключи — это имена полей. Буду использовать или нет — ХЗ. Причём тот факт, что эти макроподстановки не просто работают, а даже правильно, меня немного пугает.

SELECT concat(   '[',   coalesce(group_concat(product.tag_id), ''),   ']' ) AS product_tags

SELECT json_object(   'name', product.name,   'price', product.price ) AS product,

Такие макроподстановки позволяют сгенерировать огромную кучу SQL кода, который будет выполнять логику формирования представлений. И что меня восхищает — эта куча кода работает эффективно. Ещё интересный момент — эти макроподстановки позволят прозрачно реализовать паттерн Стратегия — я надеюсь, поведение json_arrayagg пофиксят в следующих релизах MariaDB, и тогда я смогу своё костылище заменить на связку json_arrayagg+coalesce незаметно для клиентского кода.

Выводы

SQLAlchemy позволяет использовать преимущества наследования и полиморфизма (и даже немного иннкапсуляции. Флеш-рояль, однако) в SQL. При этом она не загоняет вас в рамки задач уровня Hello, World! архитектурными ограничениями, а наоборот даёт вам максимум возможностей.

Субъективно это прорыв. Я обожаю реляционные базочки, и наконец-то я получаю удовольствие от реализации хитрозакрученной аналитики. У меня в руках все преимущества ООП и все возможности SQL.