Course contentsShow
← Software Engineering
Lesson 951 of 1,93329. Object-Relational MappingFree lesson

When to Drop Down to SQL

Recognizing scenarios where raw SQL or query builders outperform ORM abstractions and implementing hybrid approaches effectively.

When to Drop Down to SQL

You'll learn: when to bypass your ORM abstraction and write raw SQL or use query builders for better performance and control.

The Core Idea

ORMs elegantly handle most application needs, but they're not always the best tool. Sometimes you need the precision of a hand-crafted SQL query. Recognizing these scenarios prevents performance bottlenecks while keeping your codebase maintainable.

Think of an ORM like an automatic car transmission: convenient for daily driving. Raw SQL is like a manual transmission—more control when you need it, like when climbing a steep hill or towing heavy loads.

When Raw SQL Makes Sense

Complex aggregations and reports: Multi-table JOINs with window functions, subqueries, and complex GROUP BY clauses often generate inefficient ORM queries or simply aren't expressible in the abstraction.

Bulk operations: Inserting or updating thousands of rows performs far better with a single INSERT INTO ... SELECT or bulk update statement than looping through ORM objects.

Database-specific features: CTEs (Common Table Expressions), full-text search, or vendor-specific optimizations like PostgreSQL's DISTINCT ON don't map cleanly to ORM abstractions.

Performance-critical paths: When query execution plans show the ORM generates suboptimal SQL, hand-writing it gives you exact control over joins, indexes, and query structure.

Implementing the Hybrid Approach

Use raw SQL or query builders for these edge cases while keeping the ORM for standard CRUD operations. Most ORMs provide escape hatches: raw query methods that still return mapped objects or simple result sets. Keep complex SQL in repository methods, not scattered throughout your codebase, maintaining a clean separation of concerns.

Key Takeaway: ORMs excel at everyday data access, but recognizing when to drop down to raw SQL or query builders for complex reports, bulk operations, and performance-critical code is essential for building efficient, maintainable systems.