When to use/not use an ORM

ORMs ease out the pain of writing the data access layer by ourselves. But, have you ever come across a situation where an ORM is not suitable to do it's job?

Comments

[Gayan Hewa]

This is a pretty opinionated topic. In my opinion, ORM's add value to some aspects. Things like you don't need to have a deeper understanding of how SQL works to get stuff done with an ORM. Most of the query code is generated for you. On the negative side, building complex queries with an ORM requires you to be well versed with the highs and lows of the given ORM. This is different from one ORM to another. As a practice for me, for smaller projects sometimes having an ORM helps me quickly whip stuff out and get things running for production. But if I have a read-intensive project with complicated reports that require complex queries to build a result, I just use raw SQL because its easier for me to write a few helper functions and procedures and simplify the final query. But as I said this is just what works for me. So it's pretty opinionated.

The most common problem I have faced with an ORM is n+1 problem in some cases. And writing reports or complicated queries is a hassle with an ORM. Because you need to either master the ORM's API interface to know how to build or to know if you can even build what you need.

[Rumesh Madhusanka]

I guess some ORM's cannot do some complex tasks. In that case should we not use an ORM and use raw SQL OR use raw sql for the entire project?

[Gayan Hewa]

I am not sure if that is a correct assumption. I am sure most ORM's can do anything you would do with raw SQL, just that there is a learning curve to figure out how things should be done, the caveats every single ORM has to get around certain edge case scenarios, might not give a sufficient return on investment.

My personal reason to use raw SQL for complex queries is mainly that I am comfortable writing raw SQL. And in "most cases" its easier for most of the other developers in the team to understand.

So when you choose, if you are unsure just stick to the ORM until you feel like you are hitting a bottleneck. And when you do hit a problem, it's very much likely that someone has already faced this problem and either found a solution.

[Petros Stergioulas]

I was also struggling with that question.

I feel like for simple crud an orm is great and it doesn't add overhead.

If you complex relations and joins etc, maybe you should write the queries.

[Rumesh Madhusanka]

For complex tasks, functions and procedures are handy. But ORMs build the database from scratch. Do ORMs allow functions, procedures..? 🤔

[Petros Stergioulas]

I think yes. In some frameworks you can call a Procedure e.g. as a method with parameters and an output and then call it.

For example in java with spring-data-jpa (compination of Hibernate and Jpa) you can do something like this:

@Procedure(procedureName = "PROCEDURE_NAME")
boolean method0(@Param("param0") int param0);

source: logicbig.com/tutorials/spring-fram...

Also, I do not think it's wise to let ORM to build up your database (at least your production db). To build up a database, try better a migration tool.