How to Use Raw SQL in Rails: Syntax and Examples

In Rails, you can run raw SQL queries using ActiveRecord::Base.connection.execute or ActiveRecord::Base.connection.select_all. These methods let you execute any SQL directly on the database while still using Rails models and connections.

Syntax

Use ActiveRecord::Base.connection.execute(sql) to run any SQL command. For queries that return rows, select_all(sql) returns an ActiveRecord::Result object which behaves like an array of hashes. Replace sql with your raw SQL string.

• execute: Runs SQL, returns database-specific result object.
• select_all: Runs SELECT queries, returns ActiveRecord::Result.

ruby

sql = "SELECT * FROM users WHERE active = true"
result = ActiveRecord::Base.connection.execute(sql)

Example

This example shows how to fetch all active users using raw SQL and print their names.

ruby

sql = "SELECT id, name FROM users WHERE active = true"
results = ActiveRecord::Base.connection.select_all(sql)

results.each do |row|
  puts "User ID: #{row['id']}, Name: #{row['name']}"
end

Output

User ID: 1, Name: Alice
User ID: 3, Name: Bob
User ID: 7, Name: Carol

Common Pitfalls

Common mistakes include:

• Not sanitizing inputs, which risks SQL injection.
• Using execute for SELECT queries and expecting an array of hashes.
• Ignoring database-specific SQL syntax differences.

Always use parameter binding or sanitize inputs to avoid security issues.

ruby

unsafe_name = "Robert'); DROP TABLE users;--"
sql = "SELECT * FROM users WHERE name = '#{unsafe_name}'" # Unsafe!

# Safe way using sanitize_sql_array:
safe_sql = ActiveRecord::Base.send(:sanitize_sql_array, ["SELECT * FROM users WHERE name = ?", unsafe_name])
results = ActiveRecord::Base.connection.select_all(safe_sql)

Quick Reference

Method	Purpose	Returns
execute(sql)	Run any SQL command	Database-specific result object
select_all(sql)	Run SELECT queries	ActiveRecord::Result (array-like of hashes)
select_one(sql)	Run SELECT returning one row	Hash of one row
select_value(sql)	Run SELECT returning one value	Single value
sanitize_sql_array(array)	Safely insert parameters	Sanitized SQL string

Key Takeaways

Use ActiveRecord::Base.connection.execute or select_all to run raw SQL in Rails.

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

select_all returns an ActiveRecord::Result which behaves like an array of hashes; execute returns a database-specific result.

Use sanitize_sql_array to safely insert parameters into raw SQL queries.

Raw SQL is useful for complex queries but use ActiveRecord methods when possible.