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.