Sometimes ActiveRecord just doesn’t cut it. Maybe you need a complex query with multiple joins, or you want to squeeze every bit of performance out of your database. That’s when raw SQL comes in handy.
Here’s everything you need to know about executing raw SQL in Rails - from the basics to avoiding common pitfalls.
When You Actually Need Raw SQL
Most of the time, ActiveRecord is perfectly fine. But here are the real-world scenarios where raw SQL makes sense:
- Complex aggregations - When you need window functions, CTEs, or complex GROUP BY operations
- Performance critical queries - When ActiveRecord’s generated SQL is inefficient
- Database-specific features - Using PostgreSQL’s JSONB operators, MySQL’s FULLTEXT search, etc.
- Bulk operations - Mass updates that would be slow with individual ActiveRecord calls
Let’s jump into the practical stuff.
A Real Example: User Analytics
Say you’re building user analytics and need to find users who signed up in the last 7 days, along with their post count. Here’s how that looks:
Three Ways to Execute Raw SQL
Rails gives you several options for running raw SQL. Here are the main ones:
1. connection.execute
- For Raw Results
Use this when you want the raw database results:
sql = <<~SQL
SELECT
u.id, u.name, u.email,
COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE u.created_at >= NOW() - INTERVAL '7 days'
GROUP BY u.id, u.name, u.email
ORDER BY post_count DESC
SQL
result = ActiveRecord::Base.connection.execute(sql)
result.each do |row|
puts "#{row['name']}: #{row['post_count']} posts"
end
2. find_by_sql
- For Model Objects
When you want actual ActiveRecord objects back:
users = User.find_by_sql([
"SELECT * FROM users WHERE created_at >= ? AND status = ?",
7.days.ago, 'active'
])
users.each { |user| puts user.email }
3. pluck
with Arel - The Hybrid Approach
For simple cases, you can still use some raw SQL with ActiveRecord:
User.where("created_at >= ?", 7.days.ago)
.pluck("name, email, DATE(created_at) as signup_date")
Don’t Get Hacked: SQL Injection Prevention
This is critical - never interpolate user input directly into SQL strings. Here’s the wrong way:
# DON'T DO THIS - vulnerable to SQL injection
email = params[:email]
User.find_by_sql("SELECT * FROM users WHERE email = '#{email}'")
Instead, use parameterized queries:
# Safe approach with parameters
email = params[:email]
User.find_by_sql(["SELECT * FROM users WHERE email = ?", email])
# Or use connection.quote for raw execute
email = params[:email]
sanitized_email = ActiveRecord::Base.connection.quote(email)
result = ActiveRecord::Base.connection.execute("SELECT * FROM users WHERE email = #{sanitized_email}")
Parameters are automatically escaped and safe. Always use them.
Performance Tips
Raw SQL is often about performance, so here are some pro tips:
Use Beekeeper Studio to analyze your queries - it’s perfect for testing and optimizing SQL before putting it in your Rails app.
Add indexes on columns you’re filtering or joining on:
# In a migration
add_index :users, :created_at
add_index :posts, [:user_id, :status]
Use EXPLAIN to understand query performance:
ActiveRecord::Base.connection.execute("EXPLAIN ANALYZE #{your_sql}")
Consider database views for complex, reusable queries instead of repeating raw SQL everywhere.
Wrapping Up
Raw SQL in Rails is like a sharp knife - powerful when you need it, but handle with care. Use it for complex queries, performance optimization, and database-specific features that ActiveRecord can’t handle.
Remember:
- Always use parameterized queries to prevent SQL injection
- Test your queries with a tool like Beekeeper Studio first
- Don’t abandon ActiveRecord entirely - use raw SQL strategically
Further Reading
- Rails Guides: Active Record Query Interface
- PostgreSQL Performance Tips
- MySQL Query Optimization
- Try Beekeeper Studio for SQL development - it’s the best way to write and test SQL queries
Got questions about database management or SQL optimization? Beekeeper Studio makes it easy to explore your data and perfect your queries before deploying them.
Beekeeper Studio Is A Free & Open Source Database GUI
Best SQL query & editor tool I have ever used. It provides everything I need to manage my database. - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studio is fast, intuitive, and easy to use. Beekeeper supports loads of databases, and works great on Windows, Mac and Linux.
What Users Say About Beekeeper Studio
"Beekeeper Studio completely replaced my old SQL workflow. It's fast, intuitive, and makes database work enjoyable again."
"I've tried many database GUIs, but Beekeeper strikes the perfect balance between features and simplicity. It just works."