Category software development

Since Rails v4.1 is coming to its end of life, we recently upgraded one of our clients to Rails v4.2.8. And while most of the upgrade tasks were pretty straight forward, we did run into an interesting issue when using find_by_sql to execute a complex sql query. With the introduction of AdequateRecord, Rails is trying to improve query performance throughout the platform by using prepared statements as often as possible. Postgresql, which is what the application uses, can take advantage of prepared statements to cache query execution plans and query results from the database.

The application has a report that takes a variable number of filters from the UI and aggregates data across multiple tables. To ensure the report stays performant, we’ve crafted a pretty large sql query to make the database do all of the heavy lifting when computing the results. To generate the full query, we create Rails scopes from the filters selected in the UI and then call to_sql on them and substitute them into the main query. In 4.1, the translation of the scopes evaluated as you would expect. For example, if we wanted to limit the result set to only products that are “active”, we would create the scope where(active: true).to_sql and that would become: where(active = 't'), we then inject that into our larger query: select * from products, left join ..., inner join ... where <filters>.

After the upgrade to v4.2.8, the Rails translation of our scopes when using to_sql changed to use the prepared statement format of where(active = $1). When we substitute that filter into our larger query, we started getting the error ERROR: bind message supplies 0 parameters, but prepared statement "" requires 1. So if Rails is now going to start generating prepared statements from our scopes, how can we continue to use our big sql query?

A couple of different options come to mind. We can do our own string substitution on the generated filters before injecting them into the final one sql_query.gsub('$1', 42). But in doing that, we lose all of the benefits of the existing Rails libraries. Modules that translate true and false to ‘t’ or ‘f’ or that escapes words with apostrophes. So taking advantage of the existing Rails infrastructure is definitely the right direction.

The method signature for find_by_sql allows for an array of bind parameters. These bind parameters will go through the normal Rails substitution and escaping phases so let’s take advantage of that. However, the official documentation doesn’t provide examples on the format of the bind parameters when the query includes prepared statements. It documents the other common query formats, such as using the question mark (?) ["select company.* from companies where name = ?", 'Haught'] or named parameters [select companies.* from companies where name = :company_name, { company_name: 'Haught'}].

Now that we have determined our approach, let’s find the data we need to submit as the bind parameter. After inspecting the Rails scopes that we are creating, we found the data is accessible using the bind_values method. Let’s bind them together and let Rails generate our final query.

As I mentioned previously, Rails documentation for find_by_sql doesn’t have an example for passing in bind variables for prepared statements. I tried passing several different variations to the binds parameter for that method. I listed the values as entries in the array: [true, 42, 'Haught', tried named substitution in a hash {'$1'=>true,'$2'=>42} but none worked. So with a lot of help from Google, I stumbled on a post by Al Tenhumfield who found the correct parameter format. Rails wants a double entry array for each variable substitution. The first value can be the name of the field you are querying, but is not required, so nil works. The second value in the array is what gets injected. So after iterating over the scopes to capture the bind_values and injecting a nil value for each value returned, the parameter we send into the method looks like => [[nil,true],[nil,42],[nil,'Haught']]. Rails does it’s magic translating the values to be database centric and we are back in business. On a side note, our clients can submit the report without passing any additional filters. In that case, our code generates a single, empty array, which find_by_sql accepts without problem.

So after some Internet sleuthing and a dive into the ActiveRecord internals, we were able to reconfigure our approach so that we can still use our complex sql query while taking advantage of all of the query preparation that Rails has to offer. And to save the next guy some time when it comes to using prepared statements when using find_by_sql we’ve submitted a pull request with updates to the Rails documentation for v4.2.8 and v5.x.