The pros and cons of Object Relational Mapping (ORM)

Opinion is somewhat divided on whether ORM, or Object Relational Mapping, is a good or bad idea.

What is ORM?

An ORM is a technique that lets you query and manipulate data usually from a database using an object-oriented paradigm. To put it in an easy way to digest, it's a way of retrieving and manipulating data without having to understand the underlying data architecture. It's just another layer of abstraction in your application.

Just as a starter; let's look at how, for example, running a SQL query is different from how you might access the same data through an ORM model.

In SQL (mysql dialect for now)

SELECT `id`, `username`, `name`, `email`
FROM users
WHERE id = 1;

In PHP, using the Laravel framework

<?php
 
\App\Models\User::where('id', 1)->first();

So what are the pros and cons of getting and manipulating data through an ORM?

The Pros

Models are DRY (don't repeat yourself): You only write your model once, which makes it far easier to update and maintain. All your code points to this one reference.

A lot of things are done for you, from handling the underlying database connection(s) to localisation.

SQL injection is a lot more difficult as queries are prepared and sanitised.

It can improve the formation of the underlying SQL. Developers can sometimes be bad at forming SQL.

When you change the underlying database, you do not have to rewrite code.

Models use OOP, which means you an extend and inherit from Models.

If you take the time to learn SQL queries and the theory behind sql, there is nothing a Model cannot do. You can write very complex queries which have the same performance if you were to write them directly in sql, however not all developers take the time to learn SQL.

The Cons

While many CRUD (create, read, update and delete) queries run without performance issues, more complex queries can lead to performance issues if not written properly.

The N+1 issue. Let's say you have a collection of users (from a users table) and each user has at least one related table row in the user_roles table. If you were to loop over the user collection and then dynamically fetch the name of each role the ORM would query each and every row separately which obviously can slow down your application. The way this is bypassed is by 'bringing' the related table with the original table, reducing the calls to the database.

<?php
 
$users = User::all();
 
foreach($users as $user) {
    echo "User: " . $user->name . "<br>";
    foreach($user->roles as $role) {
        // this will make a call to the database for every user.
        echo "\tRole: " . $role->name . "<br>";
    }
}
 
/***********************************************************************/
 
$users = User::with('roles')->all();
 
foreach($users as $user) {
    echo "User: " . $user->name . "<br>";
    foreach($user->roles as $role) {
        // this already has the role information for each user so will not call the database again.
        echo "\tRole: " . $role->name . "<br>";
    }
}

It abstracts the database layer so if you don't have much understanding of the underpinning it can be a hindrance when something goes wrong.

You have to spend a bit of time learning how to use the ORM and this can have an initial impact on development. However, once you've learnt one they generally follow the same pattern.

Developers don't necessarily learn about SQL, which can be a massive issue if they remain ignorant.

Conclusion

ORMs can drastically improve development time and prevent disjointed code bases. ORMs work very well with CRUD, but developer understanding of SQL is important for more complex queries, especially looking out for the N+1 problem.

ORMs can be especially useful if you need to map additional 'computed' attributes. Also model changes can be observed in an application which makes it very useful for manipulating data further or triggering something to happen on data change.

I much prefer ORMs to having to write SQL in code, as it's harder to maintain and often ORMs get you the data you need in a much more readable format.

However, I would very much urge developers who use ORMs to understand the principles of SQL and to learn good querying techniques.