Raw SQL when needed in Ruby on Rails - Mini Project: Build & Apply

Raw SQL when needed

📖 Scenario: You are building a simple Rails app to manage a bookstore. You have a books table with columns title, author, and price. Sometimes, you need to run raw SQL queries directly to get specific data quickly.

🎯 Goal: Learn how to write raw SQL queries in Rails using ActiveRecord::Base.connection.execute to fetch data from the books table.

📋 What You'll Learn

Create a hash representing a book with exact keys and values

Create a variable holding a raw SQL query string

Use ActiveRecord::Base.connection.execute with the raw SQL query

Assign the query result to a variable

💡 Why This Matters

🌍 Real World

Sometimes Rails queries need to be very specific or optimized. Writing raw SQL lets you do exactly that when ActiveRecord methods are not enough.

💼 Career

Many Rails jobs require understanding how to run raw SQL safely and effectively for performance or complex queries.

1

Create a book hash

Create a hash called book with these exact key-value pairs: title: 'Eloquent Ruby', author: 'Russ Olsen', and price: 30.

Ruby on Rails

# Create the book hash with title, author, and price
# Your code here

Hint

Use Ruby hash syntax with keys title, author, and price.

2

Write a raw SQL query string

Create a variable called sql_query and assign it the string "SELECT * FROM books WHERE price < 40".

Ruby on Rails

book = { title: 'Eloquent Ruby', author: 'Russ Olsen', price: 30 }
# Create the raw SQL query string in sql_query
# Your code here

Hint

Assign the exact SQL string to sql_query.

3

Execute the raw SQL query

Use ActiveRecord::Base.connection.execute(sql_query) and assign the result to a variable called result.

Ruby on Rails

book = { title: 'Eloquent Ruby', author: 'Russ Olsen', price: 30 }
sql_query = "SELECT * FROM books WHERE price < 40"
# Execute the raw SQL query and assign to result
# Your code here

Hint

Use ActiveRecord::Base.connection.execute with sql_query.

4

Access the first row from the result

Assign the first row of result to a variable called first_book using result.first.

Ruby on Rails

book = { title: 'Eloquent Ruby', author: 'Russ Olsen', price: 30 }
sql_query = "SELECT * FROM books WHERE price < 40"
result = ActiveRecord::Base.connection.execute(sql_query)
# Assign the first row of result to first_book
# Your code here

Hint

Use result.first to get the first row.

Practice

1. What is the main reason to use raw SQL in a Rails application?

easy

A. To write database queries that Rails helpers cannot easily express

B. To avoid using ActiveRecord models completely

C. To speed up Rails server startup time

D. To automatically generate database migrations

Solution

1. Step 1: Understand Rails helpers limitations

   Rails helpers cover many common queries but sometimes complex queries need direct SQL.

2. Step 2: Identify why raw SQL is used

   Raw SQL is used when Rails helpers cannot express the needed query efficiently or at all.

3. Final Answer:

   To write database queries that Rails helpers cannot easily express -> Option A

4. Quick Check:

   Raw SQL = complex queries [OK]

Hint: Use raw SQL when Rails queries are too limited [OK]

Common Mistakes:

• Thinking raw SQL replaces ActiveRecord entirely
• Believing raw SQL speeds up server startup
• Confusing raw SQL with migration generation

2. Which Rails method is used to run a raw SELECT SQL query and get model objects back?

easy

A. Model.connection.execute(sql_query)

B. Model.find_by_sql(sql_query)

C. Model.where(sql_query)

D. Model.raw_query(sql_query)

Solution

1. Step 1: Identify method for raw SELECT returning models

   Rails provides find_by_sql to run raw SELECT queries returning model instances.

2. Step 2: Differentiate from execute method

   connection.execute runs SQL but returns raw results, not model objects.

3. Final Answer:

   Model.find_by_sql(sql_query) -> Option B

4. Quick Check:

   Raw SELECT with models = find_by_sql [OK]

Hint: Use find_by_sql for raw SELECT queries returning models [OK]

Common Mistakes:

• Using connection.execute for SELECT expecting models
• Confusing where with raw SQL execution
• Assuming raw_query is a valid method

3. Given the code:

users = User.find_by_sql("SELECT * FROM users WHERE age > 30")
puts users.first.name

What will this code do?

medium

A. Return an array of user IDs only

B. Raise a syntax error due to invalid SQL

C. Print the total count of users older than 30

D. Print the name of the first user older than 30

Solution

1. Step 1: Understand find_by_sql behavior

   find_by_sql runs the raw SELECT and returns model objects matching the query.

2. Step 2: Analyze the query and output

   The query selects users with age > 30, and users.first.name prints the first matching user's name.

3. Final Answer:

   Print the name of the first user older than 30 -> Option D

4. Quick Check:

   find_by_sql returns models = prints first name [OK]

Hint: find_by_sql returns model objects, so you can access attributes [OK]

Common Mistakes:

• Expecting raw SQL to return only IDs
• Thinking it raises syntax error without reason
• Confusing output with count instead of first record

4. What is wrong with this code snippet?

result = ActiveRecord::Base.connection.execute("SELECT * FROM users WHERE name = 'Alice'")
puts result.name

medium

A. The result is a raw result set, not a model, so .name is undefined

B. The SQL query syntax is incorrect

C. ActiveRecord::Base.connection.execute cannot run SELECT queries

D. The connection object is not accessible this way

Solution

1. Step 1: Understand connection.execute return type

   connection.execute returns a raw database result, not model objects.

2. Step 2: Identify why .name fails

   Raw result sets do not have model attributes like .name; you must extract data manually.

3. Final Answer:

   The result is a raw result set, not a model, so .name is undefined -> Option A

4. Quick Check:

   connection.execute returns raw results, no model methods [OK]

Hint: Use find_by_sql for models; execute returns raw results [OK]

Common Mistakes:

• Assuming execute returns model objects
• Thinking SQL syntax is wrong
• Believing connection is inaccessible

5. You want to update all users' status to 'active' using raw SQL in Rails. Which code snippet correctly does this?

hard

A. User.find_by_sql("UPDATE users SET status = 'active'")

B. User.where("status = 'active'").update_all

C. ActiveRecord::Base.connection.execute("UPDATE users SET status = 'active'")

D. User.connection.execute("SELECT * FROM users WHERE status = 'active'")

Solution

1. Step 1: Identify method for non-SELECT raw SQL

   For UPDATE queries, use connection.execute to run raw SQL commands.

2. Step 2: Check each option's correctness

   ActiveRecord::Base.connection.execute("UPDATE users SET status = 'active'") uses connection.execute with UPDATE SQL correctly. User.find_by_sql("UPDATE users SET status = 'active'") misuses find_by_sql which is for SELECT. User.where("status = 'active'").update_all uses ActiveRecord but not raw SQL. User.connection.execute("SELECT * FROM users WHERE status = 'active'") runs SELECT, not UPDATE.

3. Final Answer:

   ActiveRecord::Base.connection.execute("UPDATE users SET status = 'active'") -> Option C

4. Quick Check:

   Raw UPDATE = connection.execute [OK]

Hint: Use connection.execute for UPDATE, INSERT, DELETE raw SQL [OK]

Common Mistakes:

• Using find_by_sql for UPDATE queries
• Confusing ActiveRecord methods with raw SQL
• Running SELECT instead of UPDATE