Rails’ ActiveRecord can easily support 90% of the querying we do against the
tables in our database. However, there is the occasional exceptional query
that is more easily written in SQL — perhaps that query cannot even be
written with the ActiveRecord DSL. For these instances, we need a way to
generate and execute SQL safely. The
sanitize_sql_array
method is invaluable for this.
First, let’s get a connection and some variables that we can use downstream
in our query.
> conn = ActiveRecord::Base.connection
=> #<ActiveRecord::ConnectionAdapters::PostgreSQLAdapter ...>
> one, ten = 1, 10
=> [1, 10]
Now, we are ready to safely generate our SQL query as a string. We have to
use send because it is not publicly available. Generally, this is frowned
upon, but in my opinion it is worth breaking the private interface to ensure
our SQL is sanitized.
> sql = ActiveRecord::Base.send(:sanitize_sql_array, ["select generate_series(?, ?);", one, ten])
=> "select generate_series(1, 10);"
Lastly, we can execute the query with our connection and inspect the
results.
> result = conn.execute(sql)
(0.4ms) select generate_series(1, 10);
=> #<PG::Result:0x007facd93128a0 status=PGRES_TUPLES_OK ntuples=10 nfields=1 cmd_tuples=10>
> result.to_a
=> [{"generate_series"=>1},
{"generate_series"=>2},
{"generate_series"=>3},
{"generate_series"=>4},
{"generate_series"=>5},
{"generate_series"=>6},
{"generate_series"=>7},
{"generate_series"=>8},
{"generate_series"=>9},
{"generate_series"=>10}]