Raw SQL when needed in Ruby on Rails - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

What is the output of this raw SQL query in Rails?

Given the following Rails code using raw SQL, what will be the output when fetching the user's name?

Ruby on Rails

user = User.find_by_sql(["SELECT name FROM users WHERE id = ?", 1]).first
user.name

A "Alice"

B nil

C 1

D ActiveRecord::RecordNotFound error

💡 Hint

The query selects the name column for user with id 1.

The raw SQL query returns an array of User objects with only the name attribute loaded. Accessing user.name returns the name string.

📝 Syntax

intermediate

Which option correctly uses raw SQL with parameter binding in Rails?

Select the option that safely executes a raw SQL query to find users older than a given age.

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

B User.find_by_sql(["SELECT * FROM users WHERE age > ?", 30])

C User.find_by_sql(["SELECT * FROM users WHERE age > :age", {age: 30}])

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

💡 Hint

Look for the correct way to pass parameters safely to avoid SQL injection.

Option B uses the array form with ? placeholder and parameter binding, which is the correct and safe way.

🔧 Debug

advanced

Why does this raw SQL query raise an error in Rails?

Examine the code below and select the reason for the error raised.

Ruby on Rails

User.find_by_sql("SELECT * FROM users WHERE name = 'O'Reilly'")

A NoMethodError because find_by_sql is undefined

B ActiveRecord::RecordNotFound because no user named O'Reilly exists

C SyntaxError due to unescaped single quote in SQL string

D TypeError because argument is not an array

💡 Hint

Look at how the single quote in the name is handled inside the SQL string.

The single quote in O'Reilly breaks the SQL string syntax causing a syntax error.

❓ state_output

advanced

What is the value of 'count' after running this raw SQL in Rails?

Consider this code snippet. What will be the value of 'count'?

Ruby on Rails

result = ActiveRecord::Base.connection.select_all("SELECT COUNT(*) FROM users WHERE active = true")
count = result.first['count']

A An array of all active users

B An integer representing the number of active users

C nil because 'count' key does not exist

D A string representing the number of active users

💡 Hint

Check the data type returned by raw SQL execution for count queries.

The raw SQL returns a hash with string keys and string values, so count is a string representing the number.

🧠 Conceptual

expert

Why might you choose raw SQL over ActiveRecord query methods in Rails?

Select the best reason to use raw SQL instead of ActiveRecord query methods.

A To perform complex queries not supported by ActiveRecord's query interface

B To avoid learning ActiveRecord syntax

C Because raw SQL is always faster than ActiveRecord queries

D Because raw SQL automatically prevents SQL injection

💡 Hint

Think about limitations of ActiveRecord and when raw SQL is necessary.

Raw SQL is used when queries are too complex or specific for ActiveRecord's methods. It is not always faster and requires care for security.

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