Beyond ORM: When and Why to Use Raw Queries in Laravel

Eloquent does a fantastic job at simplifying and abstracting away how you interact with your database.But it’s not always the best choice.

In some cases, raw SQL queries can offer superior performance, flexibility, and access to database-specific features that ORM abstractions either struggle with or just don't cater for. In this post, we’ll break down when and why you should use raw queries over Eloquent—and how to do so safely.

When to Use Raw Queries

1. Performance Optimization

Eloquent is designed for developer convenience, but its abstraction can introduce performance overhead, especially when dealing with large datasets. If you notice slow query execution times due to multiple queries being executed under the hood, a well-optimized raw SQL query can significantly reduce the load.

Example: Fetching aggregated sales data across millions of records might be much faster with a raw query than using Eloquent's collection-based methods.

2. Complex queries

Some queries are too complex for Eloquent’s query builder. When dealing with multiple joins, advanced aggregations, or nested subqueries, Eloquent can become cumbersome and inefficient. Writing these queries in raw SQL can simplify implementation and improve performance.

Example:

$results = DB::select("SELECT users.id, COUNT(orders.id) as total_orders FROM users LEFT JOIN orders ON users.id = orders.user_id GROUP BY users.id HAVING total_orders > 5");

3. Database-Specific Features

Each database system (MySQL, PostgreSQL, SQLite) has unique features that Eloquent doesn't always support out of the box. Features like JSON functions, Common Table Expressions (CTEs), and full-text search may require direct SQL queries.

Example: Using MySQL’s JSON functions

$users = DB::select("SELECT id, JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.preferences.theme')) AS theme FROM users");

4. Bulk Operations

Eloquent executes queries individually for each record, which can be inefficient for bulk inserts or updates. Raw SQL allows for more efficient batch processing.

Example: Inserting multiple rows at once

DB::insert("INSERT INTO orders (user_id, total, created_at, updated_at) SELECT user_id, SUM(amount), NOW(), NOW() FROM cart_items GROUP BY user_id");

How to Safely Use Raw Queries in Laravel

1. Using DB::raw() and DB::select() Correctly

Laravel provides helper methods to execute raw queries safely while still integrating with the query builder.

Example:

$users = DB::table('users')
    ->select(DB::raw('COUNT(*) as user_count, status'))
    ->groupBy('status')
    ->get();

2. Preventing SQL Injection with Parameter Binding

Never directly concatenate user input into raw queries. Instead, use parameter binding to prevent SQL injection.

Safe example:

$users = DB::select("SELECT * FROM users WHERE email = ?", [$email]);

Or using named bindings:

$users = DB::select("SELECT * FROM users WHERE email = :email", ['email' => $email]);

3. Logging and Debugging Raw Queries

To debug raw queries, enable query logging in Laravel:

DB::listen(function ($query) {
    logger($query->sql, $query->bindings);
});

Or, manually log queries:

Log::info("Executed query: ", [
    'query' => $query,
    'bindings' => $bindings
]);

Performance Benchmarks: Eloquent vs. Raw Queries

To illustrate the performance differences, let's compare an Eloquent query vs. a raw SQL query.

Example: Fetching a report of active users with orders

In this example, we'll be comparing a dataset of 5000 users, each with 3-15 orders. The tables are well indexed with primary key's on both id columns as well as an index on the user_id column on the orders table.
The comparison used a simple web controller method to perform the query and load the default welcome view:

Route::get('users', [UserController::class, 'index']);
 
public function index()
{
    // Perform query.
 
    return view('welcome');
}

Eloquent Approach

$users = User::withCount('orders')
    ->where('orders_count', '>', 5)
    ->get();

Performance:
* Time Taken: 13.91 seconds
* Memory Usage: 10MB

Raw SQL approach:

$users = DB::select("SELECT users.id, COUNT(orders.id) as total_orders FROM users LEFT JOIN orders ON users.id = orders.user_id GROUP BY users.id HAVING total_orders > 5");

Performance:
* Time Taken: 42.2 milliseconds
* Memory Usage: <4MB

Analysis

Whilst the Eloquent approach provides a more readable and fluent syntax, using Laravel’s ORM to define the query and manage relationships. However, this comes with performance trade-offs, as Eloquent adds overhead by handling relationships, loading data, and executing multiple queries in the background. Let's take a look at the underlying query Laravel has built up:

"select "users".*, (select count(*) from "orders" where "users"."id" = "orders"."user_id") as "orders_count" from "users" where "orders_count" > 5"

As you can see, the Eloquent approach uses a sub-query to gather the orders_count value. This correlated subquery executes separately for each user in the dataset, leading to potential performance degradation as the dataset grows.

The raw SQL approach, on the other hand, performs LEFT JOIN and GROUP BY to count the orders for each user.

SELECT users.id, COUNT(orders.id) as total_orders FROM users LEFT JOIN orders ON users.id = orders.user_id GROUP BY users.id HAVING total_orders > 5

This approach will significantly improve performance as aggregation (COUNT(orders.id)) is done in a single pass. On top of this, instead of a WHERE clause, we're now using a HAVING clause, meaning the total_orders > 5 check is done after aggregation.

When Not to Use Raw SQL Queries

Whilst this post tries to focus on why and when you should use raw SQL queries in your Laravel application, there are definitely times when you shouldn't use them. You should definitely consider using Eloquent when:

• You need maintainability – Eloquent provides a cleaner and more intuitive syntax.
• You’re dealing with relationships – Eloquent simplifies eager loading and prevents N+1 query issues.
• You want to leverage Laravel’s built-in security and query abstraction.

When to Choose Which Approach

Use the table below to help decide when to use Eloquent vs. raw SQL.

Conclusion

While Eloquent is preferred for most cases due to its readability and maintainability, knowing when to use raw queries can help you build more efficient applications. Key takeaways:

• Always use parameter binding to prevent SQL injection.
• Benchmark performance when deciding between Eloquent and raw queries.
• Use raw queries when Eloquent introduces unnecessary performance overhead.
• Write raw SQL for complex queries that Eloquent struggles with.

By strategically mixing Eloquent and raw SQL, you can maintain clean code while optimizing performance where needed.