Rails + Postgres + bindings

    image

    Привет друзья. Ни для кого не секрет, что работая на крупных проектах со сложной логикой, 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.com/kirill-dan/active_sql_bindings
    rubygems.org/gems/active_sql_bindings

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

    Перепечатка с собственного блога. Оригинал здесь

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

    Как вы считаете, Ruby имеет будущее?

    • 54,8%Ruby жив и будет жить.46
    • 17,9%Ruby уже мертв.15
    • 15,5%Ruby доживает свои дни.13
    • 31,0%Хайп не должен стоять во главе выбора технологии.26
    Поделиться публикацией
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

    Комментарии 8

      0
      Мы же говорим о действительно сложных запросах, так? Вот немного притянутый за уши контр-пример, но проблема, думаю, понятна:
      sql = 'SELECT id, name::varchar FROM schema.news WHERE id=:var'
      binding = { var: 100 }
      new = SqlQuery.execute(sql, binding).first
        0
        Да, согласен, что поимеем проблему. Но согласитесь, что как-то странно именованный биндинг называть :var. Обычно называют исходя из контекста, например news_id. Но тот код что вы привели в пример, действительно может положить постгрес при сборке запроса. Если обертку усложнять и делать защиту от подобных проблем, то мы уже тогда сосредотачиваемся на создании конкретного решения. А мне не очень этого хотелось. Простота залог успеха, но есть нюансы, как вы правильно подметили.
          0
          Ну, согласитесь, что когда отлаживаешь сложный запрос с postgis на 20-30 строк, который написал твой предшественник 2 года назад, то такие вот неочевидные тонкости могут оказаться очень неприятным открытием.

          Я бы предложил хотя бы делать замены регуляркой типа:
          .gsub(/:var(?=\b)/, '$1')
            0
            Да, полностью согласен. Поправил в статье.
              0
              Вот так будет совсем хорошо: sql.gsub!(/(?<!:):#{key}(?=\b)/, "$#{bind_index}") Будем проверять, что слева нет еще одного двоеточия.
        +1
        Arel же, ну
          0
          api.rubyonrails.org/classes/ActiveRecord/Sanitization/ClassMethods.html Как вариант

          Базовые вещи в руби уже сто лет как кем-то написаны
            0
            sanitize_sql_array сделает подстановку в тексте в обход Postgres. Чувствуете разницу между плейсхолдерами через базу данных и через встроенный метод. Очень многие эксперты по безопасности рекомендуют использовать биндинги только в рамках запроса к базе (на уровне самой базы данных), так как это более безопасно. А так, то конечно, не было бы смысла заморачиваться со своими велосипедами :)

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

          Самое читаемое