Rails Execute SQL: Master Raw Queries for Optimized Performance

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.