Raw SQL when needed in Ruby on Rails - Deep Dive

Overview - Raw SQL when needed

What is it?

Raw SQL means writing database queries directly in SQL language instead of using Rails' built-in tools. Rails usually helps you build queries with Active Record, but sometimes you need to write SQL yourself for special cases. This lets you do things that are hard or slow with Rails methods. Raw SQL is like speaking directly to the database in its own language.

Why it matters

Without raw SQL, you might struggle to do complex queries or get the best speed from your database. Rails' tools are great but not perfect for every situation. Raw SQL lets you solve tricky problems and optimize performance, making your app faster and more powerful. Without it, you could be stuck with slow or impossible queries.

Where it fits

Before learning raw SQL in Rails, you should know basic Rails models and Active Record queries. After mastering raw SQL, you can explore database optimization, advanced SQL features, and how to safely combine raw SQL with Rails code.

Mental Model

Core Idea

Raw SQL is directly telling the database what to do when Rails' helpers can't express the query well or fast enough.

Think of it like...

It's like ordering food at a restaurant: usually you pick from the menu (Rails helpers), but sometimes you want a special dish you describe yourself (raw SQL).

┌───────────────┐
│ Rails Helpers │
│ (ActiveRecord)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│   Raw SQL     │
│ (Direct Query)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│   Database    │
│ (PostgreSQL,  │
│  MySQL, etc.) │
└───────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding Active Record Queries

Concept: Learn how Rails normally builds database queries using Active Record methods.

Rails provides methods like `where`, `select`, and `joins` to build queries without writing SQL. For example, `User.where(age: 20)` finds users aged 20. These methods are easy and safe because Rails handles the SQL for you.

Result

You can fetch data from the database using simple Ruby code without writing SQL.

Knowing how Rails builds queries helps you see when raw SQL might be needed if these methods can't express what you want.

2

Foundation

Basics of Writing Raw SQL in Rails

Concept: Learn how to write and run raw SQL queries inside Rails code safely.

You can use `ActiveRecord::Base.connection.execute("SQL QUERY")` to run raw SQL. For example, `ActiveRecord::Base.connection.execute("SELECT * FROM users WHERE age = 20")` runs a direct SQL query. Rails returns raw results you can work with.

Result

You can run any SQL query directly on the database from Rails.

Understanding this lets you bypass Rails helpers when needed but requires care to avoid errors or security risks.

3

Intermediate

Using Raw SQL with Active Record Methods

🤔Before reading on: do you think raw SQL can be combined with Active Record query methods? Commit to your answer.

Concept: Learn how to embed raw SQL snippets inside Active Record queries for more power and flexibility.

Rails lets you use raw SQL inside methods like `where` by passing a string with placeholders. For example, `User.where("age > ?", 20)` uses raw SQL condition inside a safe method. This mixes Rails safety with SQL power.

Result

You can write complex conditions that Rails helpers can't express, while still using Active Record features.

Knowing this combination helps you write safer raw SQL and keep benefits of Rails query building.

4

Intermediate

Preventing SQL Injection with Raw SQL

🤔Before reading on: do you think writing raw SQL always risks SQL injection? Commit to your answer.

Concept: Learn how to safely include user input in raw SQL to avoid security problems.

Never insert user data directly into SQL strings. Use parameter binding like `where("name = ?", user_input)` or `sanitize_sql_array`. This tells Rails to escape inputs properly. For example, `User.where("email = ?", params[:email])` is safe.

Result

Your raw SQL queries won't allow attackers to run harmful commands through user input.

Understanding safe parameter binding is critical to avoid serious security bugs when using raw SQL.

5

Advanced

When to Choose Raw SQL Over Active Record

🤔Before reading on: do you think raw SQL is only for performance or also for complex queries? Commit to your answer.

Concept: Learn the real reasons to use raw SQL: complex queries, performance, or database-specific features.

Sometimes Active Record can't express queries like window functions, recursive queries, or complex joins. Raw SQL lets you write these directly. Also, raw SQL can be faster by avoiding Rails overhead. For example, using `find_by_sql` to run a complex query returns model objects.

Result

You can solve problems that Rails helpers can't and optimize your app's speed.

Knowing when raw SQL is the right tool prevents wasted effort trying to force Rails helpers to do everything.

6

Expert

Integrating Raw SQL Safely in Production Apps

🤔Before reading on: do you think raw SQL always makes code harder to maintain? Commit to your answer.

Concept: Learn best practices to use raw SQL in real apps without losing maintainability or safety.

Use raw SQL only when necessary. Wrap raw queries in well-named methods or scopes. Document why raw SQL is used. Test queries thoroughly. Use Rails' parameter binding to avoid injection. Monitor performance impact. For example, create a scope with `find_by_sql` for a complex report.

Result

Your app stays safe, maintainable, and fast even with raw SQL parts.

Understanding how to balance raw SQL use keeps your codebase healthy and your app performant.

Under the Hood

Rails Active Record builds SQL queries by translating Ruby method calls into SQL strings. When you use raw SQL, Rails sends your exact SQL string directly to the database without modification. The database parses and executes the SQL, returning raw results. Rails then wraps or returns these results depending on the method used. Parameter binding replaces placeholders with safely escaped values before sending to the database.

Why designed this way?

Rails was designed to make database access easy and safe with Active Record. But databases have powerful features and complex queries that can't always be expressed in Ruby methods. Allowing raw SQL gives developers full control when needed, balancing convenience with power. Parameter binding was added to prevent security risks from raw SQL.

┌───────────────┐
│ Rails Code    │
│ (ActiveRecord)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ SQL Builder   │
│ (generates SQL│
│  from methods)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Raw SQL Input │
│ (direct SQL)  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Database      │
│ (executes SQL)│
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think raw SQL is always unsafe and should be avoided? Commit to yes or no.

Common Belief: Raw SQL is dangerous and should never be used because it causes security holes.

Reality: Raw SQL can be safe if you use parameter binding and sanitize inputs properly.

Why it matters: Avoiding raw SQL entirely can limit your app's capabilities and performance unnecessarily.

Quick: Do you think Active Record can express every possible database query? Commit to yes or no.

Common Belief: Active Record can handle all queries, so raw SQL is rarely needed.

Reality: Active Record can't express some complex or database-specific queries, so raw SQL is necessary sometimes.

Why it matters: Trying to force all queries into Active Record can lead to inefficient or impossible code.

Quick: Do you think raw SQL always makes code harder to maintain? Commit to yes or no.

Common Belief: Using raw SQL always makes code messy and hard to understand.

Reality: When used carefully with clear methods and documentation, raw SQL can be maintainable and clean.

Why it matters: Avoiding raw SQL out of fear can cause developers to write complicated workarounds.

Quick: Do you think raw SQL queries always run faster than Active Record queries? Commit to yes or no.

Common Belief: Raw SQL is always faster than Active Record queries.

Reality: Raw SQL can be faster but sometimes Active Record generates efficient queries; raw SQL can also be slower if not optimized.

Why it matters: Assuming raw SQL is always faster can lead to premature optimization or worse performance.

Expert Zone

1

Raw SQL queries bypass some Rails caching and logging, so you must handle these aspects manually if needed.

2

Database-specific SQL features used in raw queries can reduce portability if you switch database systems later.

3

Combining raw SQL with Rails scopes and relations requires careful attention to avoid mixing incompatible query parts.

When NOT to use

Avoid raw SQL when Rails Active Record methods can express the query clearly and efficiently. Use raw SQL only for complex queries, performance-critical code, or database-specific features. Alternatives include Arel for building complex queries programmatically or database views for reusable complex logic.

Production Patterns

In production, raw SQL is often wrapped in model scopes or service objects with clear names. Developers use parameter binding to avoid injection. Complex reports or analytics queries are common raw SQL use cases. Monitoring query performance and testing raw SQL queries are standard practices.

Connections

ORM (Object-Relational Mapping)

Raw SQL is the low-level alternative to ORM abstractions like Active Record.

Understanding raw SQL deepens your grasp of what ORMs do behind the scenes and their limitations.

Database Indexing

Raw SQL lets you write queries that benefit from or require specific indexes.

Knowing how raw SQL interacts with indexes helps optimize query speed beyond ORM capabilities.

Natural Language Commands

Both raw SQL and natural language commands involve translating human intent into precise instructions.

Recognizing this connection highlights the importance of clear, unambiguous commands in programming and communication.

Common Pitfalls

#1 Writing raw SQL with user input directly concatenated, risking SQL injection.

Wrong approach:

User.where("name = '#{params[:name]}'")

Correct approach:

User.where("name = ?", params[:name])

Root cause:

Misunderstanding that string interpolation inserts raw text without escaping, allowing attackers to inject SQL.

#2 Using raw SQL for simple queries easily done with Active Record, adding unnecessary complexity.

Wrong approach:

ActiveRecord::Base.connection.execute("SELECT * FROM users WHERE age = 20")

Correct approach:

User.where(age: 20)

Root cause:

Not knowing Rails query methods can handle many cases safely and clearly.

#3 Mixing raw SQL fragments improperly inside Active Record queries causing syntax errors.

Wrong approach:

User.where("age > 20 AND name = 'John'").where("ORDER BY created_at DESC")

Correct approach:

User.where("age > ? AND name = ?", 20, 'John').order(created_at: :desc)

Root cause:

Confusing SQL clauses and Rails query method chaining rules.

Key Takeaways

Raw SQL lets you write direct database queries when Rails helpers can't express what you need.

Using raw SQL safely requires parameter binding to prevent security risks like SQL injection.

Combining raw SQL with Active Record methods balances power and safety for complex queries.

Raw SQL is a powerful tool but should be used thoughtfully to keep code maintainable and performant.

Understanding raw SQL deepens your knowledge of how Rails interacts with databases and when to optimize.

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