Executing a raw SQL query in Rails with an array parameter against PostgreSQL

Question

I would have hoped this would work:

user_ids = [1, 154, 31908]
query = "SELECT id FROM users WHERE id = ANY (ARRAY[$1])"
ActiveRecord::Base.connection.exec_query(query, "SQL", [[nil, user_ids]])

However this results in the exception TypeError: can't cast Array.

It seems Rails can handle an array parameter when using the where method of some model but is there any way to have an array parameter when making a raw SQL query, not involving ActiveRecord (except to get the connection) or any model?

Which database is this? Are you sure your statement is correct? I.e. does `SELECT id FROM users WHERE id = ANY (ARRAY[1,154,31908])` work if you hardcode it? — hschne, Nov 18 '20 at 14:18
Is this a real world example? If so you can just change it to `ActiveRecord::Base.connection.exec_query(User.select(:id).where(id: user_ids).to_sql)`; however if this is just a basic example for the purposes of the question I would recommend looking into [`arel`](https://www.rubydoc.info/gems/arel). Arel is the underlying query assembler for rails and it will allow you to build very complex queries in code and then you can execute them or put them out as "raw SQL" (`to_sql`) — engineersmnky, Nov 18 '20 at 14:18
@engineersmnky that's a good point, but avoiding query builders and models and abstractions between the database and the application is kinda the point of the exercise. — Phil Frost, Nov 18 '20 at 14:40
@PhilFrost then why rails? Just use the pg gem directly and a lightweight framework like sinatra. — engineersmnky, Nov 18 '20 at 15:09
@engineersmnky If I were writing an application from scratch, I'd definitely do that. But Rails is what I have, so that's why. — Phil Frost, Nov 18 '20 at 17:16

score 2 · Answer 1 · answered Mar 15 '21 at 19:14

First create a reusable composite type definition that describes an Array of Integers:

# Somewhere in the static context
IntegerArray = ActiveRecord::ConnectionAdapters::PostgreSQL::OID::Array.new(ActiveModel::Type::Integer.new).freeze

Then use this type in queries:

user_ids = [1, 154, 31908]
query = "SELECT id FROM users WHERE id = ANY($1)"
params = IntegerArray.serialize(user_ids)
ActiveRecord::Base.connection.exec_query(query, "MyLogLabel", [[nil, params]])

Note that there's no need to use PG::TextEncoder directly nor PG::Connection#exec_params. There's still a need to use ActiveRecord::ConnectionAdapters::PostgreSQL since composite types are not backend-agnostic.

score 1 · Answer 2 · answered Nov 30 '20 at 22:52

Not pretty, but it's possible to get a plain PG::Connection connection like so:

connection = ActiveRecord::Base.connection.instance_variable_get(:@connection)

Unfortunately, the pg gem doesn't very elegantly support arrays either, so this only gets you halfway there, and you have to do some rather verbose, manual encoding of the array:

user_ids = [1, 154, 31908]
query = "SELECT id FROM users WHERE id = ANY ($1)"
params = [PG::TextEncoder::Array.new.encode(user_ids)]
connection.exec_params(query, params)

Depending on how pretty you want to make this, you could monkeypatch ActiveRecord::ConnectionAdapters::PostgreSQLAdapter to expose @connection, or subclass it and configure Rails to use that subclass. Also, the pg gem supports customizing typecasting behavior, which would reduce some of the tedium.

hschne · Answer 3 · 2020-11-19T08:10:52.270

0

Instead of using the array directly, you will have to convert it to a string if you want to use parameter bindings:

user_ids = [1,154,31908]
user_ids = user_ids.join(',')
query = "SELECT id FROM users WHERE id = ANY (ARRAY[$1])"
ActiveRecord::Base.connection.exec_query(query, "SQL", [[nil, user_ids]])

This results in the query SELECT id FROM users WHERE id = ANY (ARRAY[1,154,31908]).

If you are just goofing around, and trying out queries, you could also ditch bindings and use string interpolation directly:

query = "SELECT id FROM users WHERE id = ANY (ARRAY[#{user_ids.join(',')}])"

Keep in mind that this would open you up for SQL Injection and similar nasties, so I do not recommend this approach for anything that is accessible from the outside.

edited Nov 19 '20 at 08:10

answered Nov 18 '20 at 14:48

hschne

704
5
21

I don't think your first example works, as it would generate the query `SELECT id FROM users WHERE id = ANY (ARRAY['1,154,31908'])`. That is, an array with the string `'1,154,31908'`, rather than the desired array of three integers. – Phil Frost Nov 18 '20 at 17:19
Actually I take that back -- it looks identical to my example, plus defining a variable that's never used, `userr_ids`. – Phil Frost Nov 19 '20 at 05:29

score 0 · Answer 4 · answered Sep 04 '21 at 08:07

The following worked for me.

 user_ids = [1, 2, 3].to_s
 user_ids = user_ids.gsub("[", "{")
 user_ids = user_ids.gsub("]", "}")
 query = "SELECT id FROM users WHERE id = ANY ($1)"

 ActiveRecord::Base.connection.exec_query(query, "SQL", [[nil, user_ids]])

Executing a raw SQL query in Rails with an array parameter against PostgreSQL

4 Answers4