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.simple_execute(sql_str, company_id: @company.id, user_id: @user.id)

Using Plain ActiveRecord Syntax

sql_str = <<~SQL.squish
  SELECT *
  FROM orders
  WHERE orders.company_id = :company_id AND orders.updated_by_user_id = :user_id
SQL

### must use send because this method is private is Rails 5.1 only, Public in 5.0 and 5.2
sanitized_sql = ActiveRecord::Base.sanitize_sql_array([sql_str, company_id: @company.id, user_id: @user.id])

results = ActiveRecord::Base.connection.execute(sanitized_sql)

if defined?(PG::Result) && results.is_a?(PG::Result)
  records = results.to_a
elsif defined?(Mysql2::Result) && results.is_a?(Mysql2::Result)
  records = []

  results.each do |row|
    h = {}

    results.fields.each_with_index do |field,i|
      h[field] = row[i]
    end

    records << h
  end
else
  records = results
end

return records

Testing

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