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.
4 thoughts on “Rails7 connection.select_all is stricter about it’s arguments in backwards incompat way: TypeError: Can’t Cast Array”
Ooh, I wasn’t aware of `connection.select_all`. I’ve been using `connection.find_by_sql` in Panlexicon, which I recently updated to Rails 6.1 (not yet 7 so maybe those bind params will bite me; I’ve been using named params): https://github.com/bensheldon/panlexicon-rails/blob/24c9a74b4a59f4c6db35c60a492e8f514f17ce8e/app/models/search.rb#L83
I learned the same lesson about bind params with GoodJob when I added support for Rails 7: https://github.com/bensheldon/good_job/pull/461
I also don’t feel confident in the approach myself, but I appreciate you posting about it because we independently arrived at the same usage of binds.
Thanks for feedback and info and links to your code!
Looks like arguments and return value in exec_query in your example is almost exactly the same as select_all in mine — down to the change in `binds` param needed for Rails 7. I really don’t know what the difference between select_all and exec_query is! I notice your `find_by_sql` example isn’t `connection.find_by_sql`, it’s `Model.find_by_sql` — and I think returns Model objects? That seems a bit “higher level” and nice where it does what you need — as it doesn’t use that weird `binds` argument of the same form as exec_query and select_all, I don’t believe it will be subject to this backwards incompat that we both saw in select_all/exec_query
Interesting! Digging into the implementation, it looks like `select_all` allows conversion of Arel to SQL, and then calls `select` (which directly delegates to `exec_query`). Calling `exec_query` directly requires SQL. Odd!
Also see a bit more at reddit thread discussing this post. https://www.reddit.com/r/rails/comments/tqhqs5/rails7_connectionselect_all_is_stricter_about_its/