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?

Answer 1

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.

Answer 2

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.