Why ORMs are not always the way to go

Introduction

For many developers, using an Object Relational Mapper (ORM), like hibernate in Java, has become almost automatic. While ORMs are incredibly powerful and useful tools, their use should be carefully considered in certain contexts. In this article, we will take a look at the advantages and disadvantages of ORMs and the cases in which their use could be relevant.

What is an ORM ?

An ORM (Object Relational Mapper) is a tool that allows programmers to represent data from a relational database into objects.

An ORM sits between your application and your database, and is capable of mapping the entities in your database into objects. ORMs are also capable of generating queries very easily, without necessarily using your database's query language.

Let's imagine we want to build a search engine for airlines. This application would let us search for airlines and get an overview of their fleet. To build our app, we will be using this model :

This database could be represented as the following Java classes :

Getting an airline from its ID would be as simple as :

ORMs bright side

Traditionally, to retrieve data from a relational database and use it in an object-oriented language is quite cumbersome.

As an example, let’s try and retrieve all airlines in the database from my example above, with Java/Spring and without an ORM. First, let’s create an AirlinesRepository , with a JdbcTemplate that will allow us to query our database.

After calling the AirlinesRepositoryWithoutOrm.findAll function, I am getting the following list :

Ok so what’s wrong ? It looks perfectly fine to me…
Well, yes it is. But let’s look at the ORM version. One of the most popular Java ORMs, and the one we will use is Hibernate.
Creating a Hibernate repository with Spring is very straightforward :

Compared to our previous implementation :

Wait, I’m confused, what is a JpaRepository ?
JPA (Jakarta Persistence API) is a programming interface that describes how relational data is handled in Java applications. Hibernate is nothing more than one implementation of this interface.

Let’s go back to our example. All we need to do now is add a small annotation over our Airline class : @Entity. It basically allows us to define this class as a persistence class, meaning it is mapped to our database model. Lastly, we must add two annotations over our id, like so:

What we are telling Hibernate here, with @Id is that the field “id” is a primary key. The GeneratedValue annotation allows us to define the way the primary key must be generated. And that’s all we need to do.
Now, we can just call AirlinesRepository.findAll and the corresponding SQL query will be automatically generated by Hibernate.

Note that in this example, even without an ORM, the mapping was relatively straightforward, thanks to spring boot BeanPropertyRowMapper. In other languages or without Spring, this can be much (much) more painful.

But that’s not it. What if, for example, you want to find all airlines whose names contain a certain word ?
By using Spring Data with any ORM (like Hibernate), all you need to do is declare your function in your repository, like so :

That’s it ! Spring Data will be able to automatically generate your SQL query.

By calling findAirlinesByNameContaining("Airlines") you will get :

Without an ORM, you would need to create the following function :

Much more painful, isn’t it ?

But it doesn’t end here. What if one day, I decide that I want to change a column naming. For example, I would like to rename the “name” column of my “airline” table to “airlineName”. With JPA, all I need to do is change the corresponding attribute in my class:

String name;

to

String airlineName;

But without an ORM, I would have to go through every single query using my airline table “name” column and change it to “airlineName”. In this example, I only have two queries, but imagine this with a huge API, where you have tens or even hundreds of queries retrieving your airline name.

ORMs limitations

So, are ORMs always the way to go ?
Sorry to disappoint you but not really.

Let’s take a look at a more advanced example.
If you take a look at my db diagram, you notice that aircrafts belong to airlines.

To represent that relationship with JPA, and be able to access aircrafts belonging to an airline, you must add the following attribute to your Airline class :

The OneToMany annotation indicates JPA that for one Airline, you have multiple aircrafts. mappedBy = "airline" means that in the “aircraft” table, the column “airline” will be the one to look for when fetching aircrafts belonging to an airline.

Let’s try and retrieve the airline with ID 1 and its associated aircrafts :

Now let’s take a look at the logs :

Wait… Why is Hibernate executing two queries ? Couldn’t it just make a single query with a join ?

The reason why Hibernate is doing this is that by default, OneToMany associations are fetched lazily. It means that while the attribute is not used, it will not be fetched from the database. This behavior is rather logical : it would be quite a waste to always retrieve all our joined tables from the database even though we are using them in specific cases. But what if in my case, I don’t want to waste an additional query, and perform a single query with a join, instead of two separated ones ?

It is possible, all I need to do is add fetch = FetchType.EAGER in my OneToMany annotation, like so :

By doing this, all associated aircrafts will be fetched eagerly.

I can confirm this by calling again :

And looking at my logs :

Ok, so now Hibernate is only executing a single query with a join.

Using eager fetching can be very useful when you have big performance needs and many associated entities. It can sometimes save you tenths of additional queries to your database.

Now, remember our previous function findAirlinesByNameContaining ? Let’s use it again to build our airline search engine autocomplete bar.

When our user searches for “Airlines”, for example, we will be calling findAirlinesByNameContaining("Airlines")
Because we used eager fetching for our aircrafts attribute, we now have additional fields in our response, compared to earlier :

That seems to be a bit heavy doesn’t it ? And in this example, we only have 3-4 aircrafts in every airline. What if we had hundreds (like with real airlines !) ? They are completely useless for our autocomplete feature, 90% of our returned data won’t be used to only display airline names. So we end up having two choices :

• Using lazy fetching and not taking advantage of joins, which are a very powerful SQL feature.
• Using eager fetching and fetching way too much data in most of our cases.

Nonetheless, ORM’s allow you to write raw SQL queries For example, we could add the following function declaration to our AirlinesRepository :

But using this method, you loose a few advantages of Hibernate that I mentioned above :

• Automatic query building.
• Automatic column name checking. I could introduce a mistake in this query by, let’s say, change “aircraft” to “aTableThatDoesntExist”. My app would run without any issues until the query is ran.

This is also not ideal, as it would mean that I query my database in multiple different ways according to the specific use case, which is pretty inconsistent and not very scalable.

You cloud also use JPA EntityGraph but it is also not trivial to use and inevitably add significant complexity to your code.

This whole example is somehow specific to JPA but it illustrates one of the major drawbacks of ORMs, which is the lack of flexibility. ORMs allow you to query your database without manipulating SQL queries but this comes with a cost.

As expected, ORMs are not a prefect world.

Pros and cons

As a quick recap, let’s take a look at ORMs pros and cons

Pros :

• Faster development time : ORMs save you the time needed to write SQL queries or mappers or to manage connection pools, for instance.
• More maintainable code : ORMs encapsulate database tables within classes, which allows for a better separation of concerns, and easier management.
• Database-agnostic : ORMs allow you to easily switch from one database system to another needing to rewrite parts of your application.
• Mitigates the risk of SQL injections, as queries are automatically sanitized.

Cons

• Can be slower than raw SQL query, especially if you know SQL well. This is not always true. For example, Hibernate has a caching system that allows query to sometimes be faster than with raw SQL.
• Learning curve : all ORMs work in a different way and have different syntax and features, to which developers must familiarize themselves.
• Sometimes very limited : Prisma, for example, one of the most used Node.js ORMs, does not support geospatial features.
• Vendor lock-in : ORMs allow you to be independent of your database system, but the trade-off is you become dependent on the ORM you use, which is not necessarily a good thing.
• Harder to debug : ORMs can make debugging certain issues much harder, as you sometimes need to dive into what is happening under the hood.

When should you use an ORM

To conclude, using an ORM or not depends on your needs.
If you don’t need particularly high performance and you don’t perform very complex queries, using an ORM can be a good idea, as it will allow an easier and faster development, and make your application scale well.

If your project needs very high performance and your team is experienced with SQL, you might want to use raw SQL.

You could also take advantage of ORM alternatives such as Jooq in Java. It allows to easily generate SQL queries from its API and convert queries results to Java objects rather effortlessly.

Another possibility would be to use an ORM in your project either way, let it generate simple queries, and use raw SQL queries for complexe or performance-critical queries. You would lose some benefits of your ORM but you would still take advantage of its main purpose which is to map table rows into objects. As mentioned above, this strategy might be a bit confusing as you would be fetching data in two different ways in your app.

Using an ORM or not also depends on your language : in Java, ORMs like Hibernate are much more stable and performant than in Node.js, where many of them cohabit and have their own big flaws. If you are on Node.js, using a query builder could be a relevant choice.

Sources

https://hibernate.org/orm/what-is-an-orm/

https://medium.com/interleap/problems-with-hibernate-one-to-many-and-their-solutions-8f32af216b95

https://medium.com/@mithunsasidharan/should-i-or-should-i-not-use-orm-4c3742a639ce

https://dev.to/harshhhdev/why-orms-arent-always-a-great-idea-41kg

https://www.jooq.org/