Raw SQL with ActiveRecord

April 27, 2023

ActiveRecord is great. It lets you quickly build queries, reuse query code with named scopes, and chain multiple scopes together.

But sometimes you might need to just run some raw SQL. ActiveRecord can make that easier, too. Here are some key methods from the ActiveRecord::ConnectionAdapters::DatabaseStatements module.

execute can be used to run a SQL command. If you're using PostgreSQL this will return a PG::Result object.

result = ActiveRecord::Base.connection.execute("SELECT 'PG Example', CURRENT_DATE, 123")
 => #<PG::Result:0x00007f7f094e14e0 ...
result.values
 => [["PG Example", "2023-04-28", 123]]
result.first  # If you don't use aliases, columns can be merged together here
 => {"?column?"=>123, "current_date"=>"2023-04-28"}
result.first['current_date'].class
 => String

If you're using MySQL this will return a Mysql2::Result object.

result = ActiveRecord::Base.connection.execute("SELECT 'MySQL Example', CURRENT_DATE, 123")
 => #<Mysql2::Result:0x00007fa01bbc1ae8 ...
result.to_a
 => [["MySQL Example", Fri, 28 Apr 2023, 123]]
result.first
 => ["MySQL Example", Fri, 28 Apr 2023, 123]
result.first[1].class
 => Date

Note that both the interface for the result object and the type-casting is slightly different depending on the database.

select_all provides similar results but uses an ActiveRecord::Result.

result = ActiveRecord::Base.connection.select_all("SELECT 'abc', 123")
 => #<ActiveRecord::Result:0x00007fa0199a7728 ...
result.rows
 => [["abc", 123]]

select_rows returns an array of results.

ActiveRecord::Base.connection.select_rows("SELECT 'abc', 123")
 => [["abc", 123]]
ActiveRecord::Base.connection.select_rows("SELECT 'a' AS value UNION SELECT 'b' AS value")
 => [["a"], ["b"]]

select_one returns the first row of the query result.

ActiveRecord::Base.connection.select_one("SELECT 'a' AS value UNION SELECT 'b' AS value")
 => {"value"=>"a"}

select_values returns an array of the values of the first column in a select.

ActiveRecord::Base.connection.select_values("
  SELECT 'a1' AS value1, 'b1' AS value2
  UNION
  SELECT 'a2' AS value1, 'b2' AS value2
")
 => ["a1", "a2"]

select_value returns the first column from the first row in a select.

ActiveRecord::Base.connection.select_value("
  SELECT 'a1' AS value1, 'b1' AS value2
  UNION
  SELECT 'a2' AS value1, 'b2' AS value2
")
 => "a1"

References