Rails + Postgres + bindings

Hello friends. It's no secret that working on large projects with complex logic, Active Record becomes not a helper, but a burden. Imagine that you need to make a very complex query for PostgreSQL in a native way (on pure SQL), where there must be a certain number of variables. But in Rails there is one unpleasant trifle, the functionality of executing native queries does not allow the use of named binding. But there is a solution :) Tested and successfully implemented on a project with Rails API 5.2 + Ruby 2.6.0 + Postgres 11.

So, a little more about the problem. The main method that allows you to execute your own SQL queries is 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

The example above shows that the formation of binders occurs, to put it mildly, in one place, when we are trying to get news from the database at number 100. Bindings cannot be named but only numbered. And this greatly complicates the reading and support of native queries. Alternatively, you can use the find_by_sql method call for the model class:

sql = 'SELECT id, name, desc FROM schema.news WHERE id=:id'
new = New.find_by_sql([sql, id:100]).first

Here everything is more pleasant and clearer. But the question is, it is more or less acceptable if you want to perform a simple query. But if the request is really complicated, then to drive it through the model and Active Record itself is a big loss in speed (slow) and performance (it is consuming server resources). Why there are no named binders when working with native queries is a mystery to me, but the solution is to write my own little wrapper that can work very easily with named binders, which I did.

I give the code of a static class:

# 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!classSqlQuery# 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 hashesdefself.execute(sql, bind = {})
    bindings = []
    bind_index = 1# Get all bindings if existunless 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]
      endend# 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|nextif v.nil?
      v.each do|key, val|
        v[key] = json_to_hash(val)
      endendend# Convert JSON to hash if correct data## @param [String] json string# @return [Hash] return hash if json is correct or input datadefself.json_to_hash(json)
    JSON.parse(json, symbolize_names:true) rescue json
  endend

As you can see from the code, everything is as simple as the corner of the house. The query works like this:

sql = 'SELECT id, name, desc FROM schema.news WHERE id=:id'
binding = { id:100 }
new = SqlQuery.execute(sql, binding).first

The output is always only hash. A little bit of explanation. The execute method takes a query string and a hash with binding. It is clear that the binding in the query and hash must match. Then we loop over the hash with the bindings and replace them with numbered variables of the form $ 1, $ 2, etc. in the query itself, creating an array of numbered values ​​along the way, where the first element of the array is $ 1, the second is $ 2, and so on. After that, we execute the query using the standard exec_query method, running through the answer with the mapper and converting the keys in the hash into characters. After that, we once again run the mapper on the answer, where we check each field value for the content of JSON in it. If there is JSON and it is valid, then we convert it into a hash with keys with characters, if the field is not JSON, then we throw an exception in which we return the value back. That's all.

As you can see, it makes no sense to put hundreds of gems of sorts, squander the overall performance in order to get the desired result. You can write very many necessary solutions very quickly yourself, having spent a minimum of time and code. I didn’t post a class on github, because I don’t see much sense in it.

Good luck to everyone, see you soon.

Reprint from your own blog. Original here