Eloquent ORM, Query Builder, Raw SQL: When To Use Which?

8 min readJun 12, 2024

Using a database is a universally common way to store and manipulate data via applications. Of course we don’t want the users to put their sweat, blood, and tears into writing queries and prompts to the database where we store and manipulate the data with. Instead, we can just create a nice-looking application that allows users to do it with some neat-looking forms and a bunch of colorful buttons with each having its own functionalities.

However, database manipulation is not at all a piece of cake. Differences in the way data structures are represented often lead to challenges. To overcome them, many different tools have been discovered to stand in the front row acting as the interface between the core application and the data layer. Three of which are ORM, Query Builder, and Raw SQL. Knowing when to use which is essential because one method provides more convenience than another, but it also has drawbacks that make us think that ease of development is not forever the best option. In this article, we’ll not only be figuring out which one to use in some context, but we’ll also be reviewing each of them in terms of its definition and how to implement them. I’m gonna be using Laravel as the framework to demonstrate everything for the sake of simplicity and understandability.

Eloquent ORM (Object-Relational Mapper)

Sometimes, working with databases is a lot of work. It takes a significant amount of time to handle just a chunk of data especially when your application becomes bigger as time goes by, in proportion to the increasing number of users. Developers keep finding ways to do it more conveniently and to allocate their resources more efficiently with no additional costs. So here they are discovering one of which: Object-Relational Mapper (ORM).

ORM is a piece of software allowing developers to do the database manipulation without spending lots of time on writing complex queries. It’s pretty much a block of code utilizing the concept of object-oriented programming (OOP) to do the abstraction of such complex queries. It’s a magical tool wiping the sweat of developers.

According to the official documentation website of Laravel, Eloquent is an object-relational mapper (ORM) that makes it enjoyable to interact with your database. When using Eloquent, each database table has a corresponding “Model” that is used to interact with that table. In addition to retrieving records from the database table, Eloquent models allow you to insert, update, and delete records from the table as well.

Here’s an example of it:

use App\Models\Book;

class PostController extends Controller{
    public function index(){
        $books = Book::all();

        return $books;

        // when retrieving books from the database using eloquent, you can call the methods directly from its model
    }
}

Eloquent indeed provides convenience and readability, but it also comes with several drawbacks: it can introduce performance overhead and increased memory usage due to the abstraction layer and additional processing required to translate between objects and database records, which makes it a lot slower than raw SQL or the query builder, especially for complex queries or large datasets. There is also a learning curve for developers unfamiliar with Eloquent’s specific syntax and conventions, and the ORM might not support all advanced SQL features, limiting flexibility in certain scenarios. Debugging can be more challenging because the generated SQL queries are abstracted away, making it harder to pinpoint issues. Additionally, Eloquent may generate less efficient queries, leading to suboptimal performance in some cases where manual optimization would be beneficial.

Query Builder

A query builder is a powerful and flexible tool that allows developers to programmatically design SQL queries using a fluid, chainable interface, significantly improving code readability and maintainability. It provides a range of conditional methods, including where, orWhere, whereBetween, and more, along with intuitive methods like select, get, first, insert, update, and delete, allowing you to perform a wide range of database operations, including selecting, inserting, updating, and deleting records without writing raw SQL. It also facilitates complicated procedures such as joins, which makes working with connected data simple.

Here’s an example:

use App\Models\Post;

class PostController extends Controller {

    public function __invoke(){
        $posts = DB::table('books')
                ->select("*")
                ->get();
        
        return $posts;

        // this also returns a list of books, using query builder
    }
}

While utilizing a query builder such as Laravel’s has numerous benefits, there are some negatives to consider: it may be less expressive or more difficult to use for more complex queries than raw SQL, and it includes a slight performance overhead owing to the abstraction layer. A return to raw SQL may be necessary since the builder might not support all complex SQL functions or database-specific capabilities, and there is a learning curve for developers who are not familiar with the particular methods and syntax. Because the resulting SQL queries are abstracted away, it can be more difficult to diagnose problems during debugging. Furthermore, query builders may not always provide the best SQL queries, which could result in subpar performance in some situations when manual optimization would be preferable.

Raw SQL

Raw SQL is when you write and run SQL (Structured Query Language) statements directly, without using any fancy abstraction layer or ORM (Object-Relational Mapping) framework. With this approach, you’re basically crafting your own SQL commands to do things like selecting, inserting, updating, or deleting records in a database. Raw SQL gives you a lot of flexibility and power because you can make use of all the nifty features that SQL has to offer, like complex queries, subqueries, and stuff that’s specific to the database you’re working with. But, keep in mind that it requires a solid understanding of SQL syntax and you need to be careful because it can be more error-prone and have security issues, like SQL injection.

Take a look at this block of query below:

SELECT * FROM books;

In Laravel, we do it by using the DB facade:

use Illuminate\Support\Facades\DB;

class BookController extends Controller{
    public function rawSql(){
        $books = DB::select('SELECT * FROM books');

        return $books;

        // this method, when called, returns a list of records from "books" table"
    }
}

While raw SQL provides flexibility and a lot more options to manipulate data, There are a few disadvantages to using raw SQL: it can be more error-prone and necessitates a thorough mastery of SQL syntax, which increases the possibility of logical or syntactic errors in queries. If not handled correctly, this technique also exposes the application to security flaws like SQL injection, which makes meticulous user input sanitization necessary. When queries get more complicated, raw SQL might produce less understandable and maintainable code that is more difficult for other developers to comprehend and edit.

Performance & Memory Usage

If you’re new to the whole game, you might think that it’s all pretty much just a bunch of different methods having the same goal. Well, you’re right! However, it turns out that each of them has its own drawbacks in terms of performance and memory usage. In this section, we’ll find out how much faster one is compared to another and which one is the most efficient in terms of memory usage. Here I’m gonna be using clockwork to figure it out. If you want to keep up with this whole article, you can install it as well by running this command on your command prompt (assuming you have composer package manager installed on your PC):

composer require itsgoingd/clockwork

After running the composer command above, open your project in your favorite code editor and run this command down below:

php artisan vendor:publish

Okay, now let’s get down to the main course!

Suppose we have two tables inside our database (i.e. “posts” and “users”) with each having 10,000 data corresponding to one another across the two tables (meaning that each user has one post). There are different methods that we can use to retrieve all of the data.

Let’s do it first by Eloquent ORM. Here’s how I did it on my PC:

Write this block of code down on “PostController.php” file

use App\Models\Post;

class PostController extends Controller {
    public function eloquent(){
        $posts = Post::join('users', 'posts.user_id', '=', 'users.id')
                  ->select('*')
                  ->get();
        return $posts;
    }
}

2. Define the GET route for PostController

use Illuminate\Support\Facades\Route;

Route::get('/eloquent', "PostController@eloquent");

3. Run this artisan command on prompt:

php artisan serve

4. Hit the /eloquent endpoint that we made above. The app would try to retrieve all those 10,000 data from the database. Let’s find out how fast it is and how much memory it consumes to do it by opening clockwork that we have already installed.

Here’s the result after hitting the eloquent endpoint:

It takes around 3110 milliseconds for both app and its database to execute the ORM code we saw above, which translates to roughly 3.1 seconds. It also takes up 56 megabytes of memory.

But… what if we pare down the amount of data to just one thousand of them? Here’s the result:

Stats when eloquent ORM being executed (data limited to 1000)

Much faster and much efficient than it was when retrieving ten thousand data (common sense lol). Pretty cool, right?

Now, let’s try it out with Query Builder!

use Illuminate\Support\Facades\DB;

class PostController extends Controller {
    public function queryBuilder(){
        $posts = DB::table('posts')
          ->join('users', 'posts.user_id', '=', 'users.id')
          ->select('*')
          ->get();

        return $posts;
    }
}

Try to redo the steps above, but this time with query builder. Here’s the result:

As you can see, it turns out that query builder is much faster than Eloquent (which it’s supposed to be..) and it takes up less memory than Eloquent does. With 10,000 records returned, it only takes 407 milliseconds for both app and database and takes up 32 mb of memory to do the same thing.

That’s it for query builder. Now let’s test the Raw SQL:

use Illuminate\Support\Facades\DB;

class PostController extends Controller{
    public function rawSql(){
        $posts = DB::select("
          SELECT * 
          FROM posts 
          INNER JOIN users 
          ON posts.user_id = users.id");
        
        return $posts;
    }
}

Here’s the result after executing the code above:

Well, apparently it’s not that much faster than query builder, but it is indeed faster compared to eloquent (as expected). With 10,000 records retrieved, it takes 513 milliseconds and 32 mb of memory to execute the same query with the help of Raw SQL.

Okay, you showed us which one is faster and more efficient. So when to use which, exactly?

We saw above that query builder is the fastest (and surprisingly it is slightly faster than Raw SQL) and eloquent ORM is the slowest and takes up the most memory. However, we also found out that eloquent could be as fast if we just pare down the amount of data into one tenth. That being said, you can use Eloquent if your application is still relatively small in terms of its number of data, but you might have to consider refactoring it later to Query Builder or Raw SQL when your application gets bigger over the time. Even though it’s the slowest and the least efficient, it’s still a DML method that we can use because it provides more convenience and readability since everything is all abstracted within the implementation of OOP.

Thank you so much for reading! Looking forward to your feedback on this :)