Laravel Eloquent vs DB facade: When to use which? [closed]

Question

I did some performance tests between Laravel's DB facade query builder and Laravel's Eloquent ORM. The DB facade was much faster than Eloquent for many SQL statements (SELECT, UPDATE, DELETE, INSERT).

So why would someone use the slower Laravel Eloquent rather than the faster DB facade?

Pectoralis

Do not compare apples and oranges. Eloquent is an ORM, which means can automatically handle the relationships of your models for you. You can retrieve related models without writing complex queries. You can even retrieve database information without any kind of database knowledge at all. Also Eloquent has ton of extra features the the query builder lacks, such readability, accessors, mutators, JSON/Array conversion, hiding sensitive attributes, automatic timestams, automatic attribute casting, sofdeletes, etc... – Sebastian

Apples produce apple juice, oranges produces orange juice. But unfortunately Eloquent and Query Builder both produce the same thing, data from database. May be that's why he is comparing these two. – Salyer

@JaviStolz if you would have said 'without knowing SQL' you would be right. But "You can even retrieve database information without any kind of database knowledge at all" is not possible. Eloquent requires you know the structure of your database, what foreign keys are and how they work, and how to navigate the structure. Only the simplest of queries don't require database knowledge, and most applications will need highly complex queries. – Kight

Though apples make apple juice, and oranges make orange juice, they both are juice. Eloquent returns Collections, which are data wrapped in helpers, which make the business logic more readable. Query Builder is a piece utilized by Eloquent. Eloquent is a component in a business logic paradigm that lets you make adjustments and filter the data at each part of the flow using Closure, so your stuff reads $object->filter($something_we_just_calculated) as it works down a decision tree. You can think of Eloquent like JQuery – Ostracon

Your apple and orange example isn't valid what was pointed out a lot now. But how did you manage to edit your comment 3 times and still have so many errors in it? @JaviStolz – Knobloch

My suggestion is to use "DB facade/query builder" if you are good in SQL. – Shutt

Answer 1

Eloquent is Laravel's implementation of Active Record pattern and it comes with all its strengths and weaknesses.

Active Record is a good solution for processing a single entity in CRUD manner - that is, create a new entity with filled properties and then save it to a database, load a record from a database, or delete.

You will benefit a lot from Eloquent's features such as dirty checking (to send SQL UPDATE only for the fields which have been changed), model events (e.g. to send administrative alerts or update statistics counters when someone has created a new account), traits (timestamps, soft deletes, your custom traits) eager/lazy loading etc. You can also apply domain-driven pattern and implement some pieces of business logic in your Active Record entities, for example, validation, managing relations, calculations etc.

But, as you already know, Active Record comes with some performance price.

When you process a single record or a few records, there is nothing to worry about. But for cases when you read lots of records (e.g. for datagrids, for reports, for batch processing etc.) the plain Laravel DB methods is a better approach.

For our Laravel based applications we are using both approaches as we see appropriate. We use Laravel's Eloquent for UI forms to process a single record and use DB methods (backed by SQL views with additional database engine specific performance tweaks) to retrieve data for UI tables, export tasks etc. It also works well with RESTful APIs - Eloquent for GET, PUT, POST, DELETE with a key and DB for GET without key but with filters and sorting and paging.

Bushwhacker

Does Eager loading not solve the performance problem? – Impracticable

@Impracticable Sometimes Eager loading can help a bit, but still it remains the same Eloquent (Active Record) object with all of its "heavy stuff". Laravel offers some convenience methods on Eloquent models to make SQL queries and retrieved objects even lighter but then that is not pure Eloquent anymore but some kind of Eloquent / QueryBuilder hybrid. – Bushwhacker

Eloquent is perfect for the usual CRUD operations on a single model or simple JOINs when working with a few records, but once you start getting into complex joins and working with hundreds or thousands of records in a transaction, query builder methods are more performant; if anything, raw SQL is the most performant option being that it's a direct query. – Recalescence

Still agree with the answer, as now I face the same way that loading (SELECT) with different check/rules from eloquent is a pain but moving all those to a query builder also a lot challenge if we don't plan from the beginning of what we need to use DB query, what to do with eloquent, hope others aware about this – Diplomat

just to clarify: the problem laravel has with eloquent is the instantiation of the models. E.g. laravel does new User() for each user row returned. This maybe takes just 1ms but if oyu have 10'000 records? This is often not even necessary as sometime you just want to return say username. In that case DB::select would be faster. Notice as well: eloquent is not laravels implementation afaik. It is symphony's implementation that was adapted by laravel or was at some point if I recall correctly. – Pyrolysis

Just wondering about the very last word: "paging". You say you use DB for paging. But isn't using Eloquent perfect use case for pagination? You only load a limit number of records that you convert into your Eloquent models, right? – Microsurgery

@Microsurgery In some business ERP-like websites customers want large grids with 100 records per page, and usually all of these records essentially are read-only views, often based on a efficient fine-tuned SQL view. So, there is really no need for heavy-ish Eloquent models here, unless you want to spend some time creating separate Eloquent models for read-only versions of your entities. For large data grids its much more efficient to create an SQL view that resolves all joins etc. and returns records as simple flat maps that can be dealt with by Laravel's DB mechanisms. – Bushwhacker

Answer 2

Yes, in some case you are correct.

When you are handling more data, it is better to use Laravel's DB facade query builder than Laravel's Eloquent ORM.

From performance tests, inserting 1000 rows in a simple table takes Eloquent 1.2 seconds whereas the DB facade takes only 800 milliseconds.

So why use Eloquent at all? Eloquent is also important, because:

• It has a simpler syntax than the DB facade.

• It is easier for developers who don't know SQL. You need to know SQL to use the DB facade.

• Code written using Eloquent is more readable and thus more maintainable than code written using the DB facade:

  // With Eloquent
  $student = App\Student::find($id);
  
  // With the DB facade
  $student = DB::table('student')->where('id', $id)->first();
  

• If you want to change the database, it will be easier with Laravel Eloquent as it can handle many different databases, whereas the DB facade requires you to write SQL which may have to be rewritten for a different database.

So use Eloquent when you work on a small site with simple CRUD operations, and use the DB facade when you use many joins and other features not supported by Eloquent.

Real-life examples:

1. You're making a university website, which contains 5,000 teachers, and 10,000 students, and some notices and files. It would be better to build this site with Laravel Eloquent as it is simple and readable.

2. You're making a high-traffic site like Stack Overflow, which serves more than 100 million people every month and has more than 70 million posts. It would be better to use the DB facade as it is faster and will lead to significantly faster response times.

You can check your query performance using Laravel Debugbar.

Here is a full comparison of performance, memory consumption and code quality between Eloquent and the DB facade.

Spermic

Answer 3

Why use Laravel Eloquent instead of the DB facade:

1. You can write code that is object-oriented.

2. It is easier to use than writing raw SQL or using the DB facade.

3. There is no binding to the table schema, so for example if you want to change your table name, you don't have to touch a single Eloquent query, just change the table name in the Eloquent model.

4. Relationships between tables can be maintained in an elegant way. Just mention the type of relationship (JOIN, LEFT JOIN, RIGHT JOIN etc.) to get data from related tables.

5. Eloquent queries are more readable than raw SQL or the DB facade.

6. You can use methods, scopes, accessors, modifiers etc. inside of a model, which is a maintainable pattern.

Salyer

Answer 4

When it comes to performance and the application grows, for the sake of comparison, take a look at the following tables:

Eloquent ORM average response times:

Joins	Average (ms)
1	162.2
3	1002.7
4	1540.0

Raw SQL average response times:

Joins	Average (ms)
1	116.4
3	130.6
4	155.2

Article Reference

Bangs

Those comparisons are for extreme cases, those queries build 1000s (if not 10,000s) of eloquent ORM models, of which would only occur in the real world for bulk data processing which makes sense why it's slower. But for the vast majority of cases this is for basic crud for < 100 entries (assuming you limit you queries correctly) and its going to be a tiny impact even on large commercial applications as the bulk of the slowdown is the creation of the models. Which I think leads to the conclusion it only matters when there is a large amount of data processing. – Bicuspid

Answer 5

It is just my opinion, not a comprehensive answer. I use whatever is more convenient in a given situation.

If I come across a package or code written either in eloquent or query builder, I use whatever is being used.

I found query builder to be more intuitive if I create something from scratch so I use it more often.

When it comes to Laravel, it seems, the ease and speed of developing an app is more important then performance. I really like that they make everything very easy even for someone with little prior knowledge of php/mysql. In some cases eloquent is easier than query builder. In others vice versa. I think having many ways of doing something is what makes Laravel so easy and newbie friendly.

Roughish

Answer 6

Eloquent ORM is best suited working with fewer data in a particular table. On the other side, query builder takes less time to handle numerous data whether in one or more tables faster than Eloquent ORM.

In my case, I use ELoquent ORM in an application with tables that will hold less than 17500 entries. Whenever I anticipate the table will hold more than 17500 entries, query builder is the best.

Further, in applications with subqueries, I prefer query builder over ELoquent ORM.

Condolent

I am curious why 17500 exactly? – Brigidabrigit

Answer 7

There is many different between them

1. Builder query is much faster than ORM you test it by debugger. here is the how you can test https://scotch.io/tutorials/debugging-queries-in-laravel.
2. Builder query less time to execute when you work with large amount data like if have more 1,00,000 in your database than ORM.
3. Builder query is best suitable for large amount of data.Orm is best suitable when you work with relation because its provide many relation method to define relation between tables.
4. Builder query use join of sql but orm use relation to work 2 tables or more table.

Osbert

Answer 8

I like using query builder when building complex query from database because it seems easy to use. For working with a single table, I like eloquent.

Salol