Так вышло, что на заре моей карьеры в 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.