I have code that wanted to execute some raw SQL against an ActiveRecord database. It is complicated and weird multi-table SQL (involving a postgres recursive CTE), so none of the specific-model-based API for specifying SQL seemed appropriate. It also needed to take some parameters, that needed to be properly escaped/sanitized.
At some point I decided that the right way to do this was with
Model.connection.select_all , which would create a parameterized prepared statement.
Was I right? Is there a better way to do this? The method is briefly mentioned in the Rails Guide (demonstrating it is public API!), but without many details about the arguments. It has very limited API docs, just doc’d as:
select_all(arel, name = nil, binds = , preparable: nil, async: false), “Returns an
ActiveRecord::Result instance.” No explanation of the type or semantics of the arguments.
In my code working on Rails previous to 7, the call looked like:
MyModel.connection.select_all( "select complicated_stuff WHERE something = $1", "my_complicated_stuff_name", [[nil, value_for_dollar_one_sub]], preparable: true )
- yeah that value for the
bindsis weird, a duple-array within an array, where the first value of the duple-array is just nil? This isn’t documented anywhere, I probably got that from somewhere… maybe one of the several StackOverflow answers.
- I honestly don’t know what
preparable: truedoes, or what difference it makes.
In Rails 7.0, this started failing with the error: TypeError: can’t cast Array.
I couldn’t find any documentation of that
select_all all method at all, or other discussion of this; I couldn’t find any
select_all change mentioned in the Rails Changelog. I tried looking at actual code history but got lost. I’m guessing “can’t cast Array” referes to that weird
binds value… but what is it supposed to be?
Eventually I thought to look for Rails tests of this method that used the
binds argument, and managed to eventually find one!
So… okay, rewrote that with new
binds argument like so:
bind = ActiveRecord::Relation::QueryAttribute.new( "something", value_for_dollar_one_sub, ActiveRecord::Type::Value.new ) MyModel.connection.select_all( "select complicated_stuff WHERE something = $1", "my_complicated_stuff_name", [bind], preparable: true )
- Confirmed this worked not only in Rails 7, but all the way back to Rails 5.2 no problem.
- I guess that way I was doing it previously was some legacy way of passing args that was finally removed in Rails 7?
- I still don’t really understand what I’m doing. The first arg to
ActiveRecord::Relation::QueryAttribute.newI made match the SQL column it was going to be compared against, but I don’t know if it matters or if it’s used for anything. The third argument appears to be an ActiveRecord Type… I just left it the generic
ActiveRecord::Type::Value.new, which seemed to work fine for both integer or string values, not sure in what cases you’d want to use a specific type value here, or what it would do.
- In general, I wonder if there’s a better way for me to be doing what I’m doing here? It’s odd to me that nobody else findable on the internet has run into this… even though there are stackoverflow answers suggesting this approach… maybe i’m doing it wrong?
But anyways, since this was pretty hard to debug, hard to find in docs or explanations on google, and I found no mention at all of this changing/breaking in Rails 7… I figured I’d write it up so someone else had the chance of hitting on this answer.