Raw SQL when needed in Ruby on Rails

Introduction

Sometimes, you need to write SQL directly to do things that are hard or slow with Rails helpers. Raw SQL lets you talk straight to the database.

When you want to run a complex query that Rails can't easily build.

When performance is critical and you need a faster query.

When you want to use database-specific features not supported by Rails.

When debugging or testing SQL queries before adding them to Rails.

When you need to run a custom update or delete that ActiveRecord doesn't support.

Syntax

Ruby on Rails

ModelName.find_by_sql("SQL QUERY HERE")

ActiveRecord::Base.connection.execute("SQL QUERY HERE")

Use find_by_sql to get model objects back from a SELECT query.

Use connection.execute for other SQL commands like INSERT, UPDATE, or DELETE.

Examples

This runs a raw SELECT query and returns User objects for users older than 30.

Ruby on Rails

User.find_by_sql("SELECT * FROM users WHERE age > 30")

This runs a raw UPDATE query to deactivate users who haven't logged in since 2023.

Ruby on Rails

ActiveRecord::Base.connection.execute("UPDATE users SET active = false WHERE last_login < '2023-01-01'")

This safely inserts parameters to avoid SQL injection.

Ruby on Rails

User.find_by_sql(["SELECT * FROM users WHERE name = ?", "Alice"])

Sample Program

This example shows how to get User records with age over 25 using raw SQL. It prints each user's name and age.

Ruby on Rails

class User < ApplicationRecord
end

# Find users older than 25 using raw SQL
users = User.find_by_sql("SELECT * FROM users WHERE age > 25")

users.each do |user|
  puts "User: #{user.name}, Age: #{user.age}"
end

Important Notes

Always sanitize inputs to avoid SQL injection when using raw SQL.

Raw SQL queries bypass some Rails features like automatic type casting.

Use raw SQL only when necessary; prefer Rails query methods for safety and readability.

Summary

Raw SQL lets you write direct database queries when Rails helpers are not enough.

Use find_by_sql for SELECT queries returning model objects.

Use connection.execute for other SQL commands.