Rails raw SQL example

rails

performance

sql

best-practices

byNikita Barsukov·Mar 3, 2025

To use raw SQL in Rails:

sql = 'SELECT * FROM users WHERE attribute = ?'
result = ActiveRecord::Base.connection.execute(sql, value)

For working with model instances:

users = User.find_by_sql([sql, value])

Remember to use placeholders (?) to deter those sneaky SQL injections. These methods hand you the direct database access keys, detouring ActiveRecord validations. Treat them like Pensieve from Harry Potter, don't fall in!

When to bust out the raw SQL

In occasions where student surpasses the master, raw SQL exhibits superior power in enhancing performance and solving complex queries, rising above ActiveRecord.

Gotta go fast!

ActiveRecord might be a hare in a tortoise race when handling large datasets or complex join operations. To bring Sonic the Hedgehog into the game, deploy raw SQL.

Don't let Heroku timeout on you

Sometimes, a database query on Heroku feels like a snail trying to cross a busy road. Got a need for speed? Tailor your raw SQL for efficient database ping-pong and prevent timeouts.

Cut through bottlenecks like butter

For those moments when ActiveRecord is the bottleneck you cannot squeeze through, unshell the raw SQLsword to exterminate performance issues and ninja-tune your database interactions.

Making data handling great with raw SQL

Merging datasets: Like chocolate and peanut butter

When it comes to stirring a pot of dataset soup, the ladle of raw SQL reigns supreme. Need a combined view of the tasty PaymentDetail and the spicy PaymentError, ordered by created_at, raw SQL is your 5-star chef.

sql = <<-SQL
  SELECT pd.*, pe.*
  FROM payment_details pd
  INNER JOIN payment_errors pe ON pd.id = pe.payment_detail_id
  ORDER BY pd.created_at DESC
SQL

results = ActiveRecord::Base.connection.execute(sql)
# Voila - Bon appétit!

Cleaning data: Better than your dishwasher

Raw SQL lets you bypass heavyweight ActiveRecord instances, allowing Cassandra the psychic to foresee the hash of values directly:

records_array = ActiveRecord::Base.connection.select_all(sql).to_a
# Like assembling Avengers in one array - ready for action!

Data procession, not a funeral!

Here's how you conduct an efficient raw SQL procession and manipulate results as if they were your marionettes:

records = ActiveRecord::Base.connection.exec_query(sql)
records.each do |record|
  # Willy Wonka at work - Manipulating each candy's shape
end

Armed with ActiveRecord::Result, access to Enumerable methods is a kid's play, enabling iterative data access.

Cache me outside... or not

Park your query results in a variable like records_array for caching. This way, you won't be hitting the database like Hulk every time, keeping your application smooth and jumpy as Spider-man.

Subtlety of ActiveRecord

Picture this:

# ActiveRecord's way
@users = User.where(age: 21)

It's like relying on a carrier pigeon 🐦 to reliably deliver your Hogwarts letter.

Rails (🚀): [📜(age: 21) -> 🐦 -> 🗄️(User)]

The clarity of raw SQL

Comparatively:

# Raw SQL's way (Direct as Cap's shield)
@users = ActiveRecord::Base.connection.execute("SELECT * FROM users WHERE age = 21")

This is akin to using a telephone 📞 for a quick connection:

Raw SQL (📞): [☎️(SELECT * FROM users WHERE age = 21) -> 🗄️(User)]

The metaphor serves to introduce comparison between the diligent but timely bridging by ActiveRecord and the swift and direct data retrieval by raw SQL.

Pro gaming moves with raw SQL

Power-tuning with raw SQL

Your SQL queries can do Heavyweight lifting too:

sql = <<-SQL
  UPDATE users
  SET active = 'false'
  WHERE last_login < ?
SQL

ActiveRecord::Base.connection.execute(sql, 1.year.ago)
# 1 year inactive? Guess sleeping beauty needed a wakeup. Bam!

In this bulk update, raw SQL wields Thor's hammer to bypass ActiveRecord callbacks like a butter through knife. Wait, reverse that.

Indexing for survival

When optimizing, ensure your database columns like created_at are indexed. You wouldn't go into a battle without your armour, would you?

# In a migration:
add_index :users, :created_at
# Like sorting your comic books - find your fav superhero in a jiffy!

Life's too short for slow loads

When dealing with larger datasets, pagination is your friendly neighbourhood Spider-man saving memory from the clutches of exhaustion:

# Using the 'will_paginate' gem
@users = User.paginate(page: params[:page], per_page: 10).to_a
# Spider-man's web - Capture data, but don't overload!

Views, not from the 6

Contemplate creating database views or materialized views for commonly used complex queries. Database is the fort holding your raw SQL; improve query times like a well-built trebuchet.