Raw SQL when needed in Ruby on Rails - Step-by-Step Execution

Concept Flow - Raw SQL when needed

Start ActiveRecord Query

↓

Check if complex query

↓

Use raw SQL

↓

Execute SQL

↓

Return Results

This flow shows when Rails uses raw SQL for complex queries and ActiveRecord for simpler ones, then executes and returns results.

Execution Sample

Ruby on Rails

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

This code runs a raw SQL query to get users older than 30.

Execution Table

Step	Action	SQL/AR Query	Execution Result	Notes
1	Call find_by_sql	SELECT * FROM users WHERE age > 30	Query prepared	Raw SQL string passed
2	Execute SQL	SELECT * FROM users WHERE age > 30	Database returns matching users	Direct SQL execution
3	Map results	N/A	User objects created	Results converted to model instances
4	Return results	N/A	Array of User objects	Ready for use in app
5	End	N/A	Process complete	No further action

💡 All matching users older than 30 are returned as User objects

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
sql_query	N/A	SELECT * FROM users WHERE age > 30	SELECT * FROM users WHERE age > 30	SELECT * FROM users WHERE age > 30
results_raw	N/A	DB rows matching age > 30	DB rows matching age > 30	DB rows matching age > 30
results_mapped	N/A	N/A	Array of User objects	Array of User objects

Key Moments - 2 Insights

Why do we use raw SQL instead of ActiveRecord methods here?

Because the query is complex or not easily expressed with ActiveRecord, raw SQL gives full control. See execution_table step 1 where raw SQL is passed.

Are the results from raw SQL still Rails model objects?

Yes, after execution, results are mapped to User objects as shown in execution_table step 3.

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 2?

A The raw SQL query is executed on the database

B The results are mapped to User objects

C The ActiveRecord query is built

D The method returns the results

💡 Hint

Check the 'Execution Result' column at step 2 in the execution_table

According to variable_tracker, what is the value of results_mapped after step 3?

A N/A

B Database rows matching age > 30

C Array of User objects

D SQL query string

💡 Hint

Look at the 'results_mapped' row under 'After Step 3' in variable_tracker

If we used ActiveRecord instead of raw SQL, which step would be skipped?

A Step 3: Map results

B Step 1: Call find_by_sql

C Step 2: Execute SQL

D Step 4: Return results

💡 Hint

Raw SQL is passed only in step 1; ActiveRecord builds queries differently

Concept Snapshot

Raw SQL in Rails:
Use find_by_sql("SQL") for complex queries.
Executes SQL directly on DB.
Maps results to model objects.
Use when ActiveRecord can't express query.
Returns array of model instances.

Full Transcript

In Rails, sometimes you need to write raw SQL for complex queries that ActiveRecord can't handle easily. You use the method find_by_sql with a SQL string. Rails sends this SQL directly to the database and gets back rows. Then Rails converts these rows into model objects so you can use them like normal. This process involves calling find_by_sql, executing the SQL, mapping results, and returning them. This lets you write powerful queries while still working with Rails models.

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