ActiveRecord Simple Execute
Sanitize and Execute your raw SQL queries in ActiveRecord and Rails with a much more intuitive and shortened syntax.
Installation
gem "active_record_simple_execute"Comparison with Plain ActiveRecord
As seen here using simple_execute is much easier to remember than all the hoops plain ActiveRecord makes you jump through.
Using simple_execute
sql_str = <<~SQL.squish
SELECT * FROM orders
FROM orders
WHERE orders.company_id = :company_id AND orders.updated_by_user_id = :user_id
SQL
records = ActiveRecord::Base.connection.simple_execute(sql_str, company_id: @company.id, user_id: @user.id)
# OR use the convenience method excluding the connection portion
# ActiveRecord::Base.simple_execute(...)Using original ActiveRecord exec_query method
sql_str = <<~SQL.squish
SELECT *
FROM orders
WHERE orders.company_id = :company_id AND orders.updated_by_user_id = :user_id
SQL
sanitized_sql = ActiveRecord::Base.sanitize_sql_array([sql_str, company_id: @company.id, user_id: @user.id])
result = ActiveRecord::Base.connection.exec_query(sanitized_sql)
records = result.to_a
return recordsUsing original ActiveRecord execute method
sql_str = <<~SQL.squish
SELECT *
FROM orders
WHERE orders.company_id = :company_id AND orders.updated_by_user_id = :user_id
SQL
sanitized_sql = ActiveRecord::Base.sanitize_sql_array([sql_str, company_id: @company.id, user_id: @user.id])
result = ActiveRecord::Base.connection.execute(sanitized_sql)
# OR
result = ActiveRecord::Base.connection.exec_query(sanitized_sql)
if defined?(PG::Result) && result.is_a?(PG::Result)
records = result.to_a
result.clear # to prevent memory leak
elsif defined?(Mysql2::Result) && result.is_a?(Mysql2::Result)
records = []
result.each do |row|
h = {}
result.fields.each_with_index do |field,i|
h[field] = row[i]
end
records << h
end
else
records = result
end
return recordsTesting
bundle exec rake test
We can locally test different versions of Rails using ENV['RAILS_VERSION']
export RAILS_VERSION=7.0
bundle install
bundle exec rake test
For quicker feedback during gem development or debugging feel free to use the provided rake console task. It is defined within the Rakefile.
Credits
Created & Maintained by Weston Ganger - @westonganger