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

Contributing

We test multiple versions of Rails using the appraisal gem. Please use the following steps to test using appraisal.

1. bundle exec appraisal install
2. bundle exec appraisal 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