DEV Community

Cover image for Why ORMs Aren't Always a Great Idea
Harsh Singh
Harsh Singh

Posted on

Why ORMs Aren't Always a Great Idea

Recently, I talked with a bunch of people across several Discord and Slack communities about their thoughts on ORMs and SQL. In this post, I'll be breaking down when we should use each, and the sacrifices we would make along the way.

Before we begin, I would like to clarify that I am not writing this post to bash your technology choices. You are welcome to disagree with me and discuss this topic constructively on Twitter or in the comments below. Feel more than comfortable to continue using what you use, this post just contains my views on why ORMs aren't always a great way to represent and model relational data.

What is an ORM?

ORM is simply an acronym for object-relational mapping. In simple terms, it's basically serves as a layer between relational databases and your applications.

The standard way for communication to your database is SQL -- structured query language. It was created in 1970s by Raymond Boyce and Donald Chamberlin who were both engineers at IBM.

So, ORMs are basically an abstraction on-top of standard SQL to make interacting with your database even simpler. With that being said, there are some consequences (positive and negative) of this abstraction that we'll talk about in this post.

The problem with ORMs

Now that you have an idea of what exactly an ORM is, let's get into potential problems we can run into while using them.

A sub-standard abstraction for relational databases

In my opinion, there are a lot of use cases where an ORM could become an anti-pattern. The fundamental problem is that an ORM tries to abstract away the underlying database, and reduces the feature set to the 'lowest common denominator' -- essentially meaning that they try to plaster a fits-all API over different database management systems.

This also raises the question of whether or not an abstraction like ORMs are even necessary. We already have a succinct language for interacting with databases, and that language is called SQL.

I think it's completely appropriate to bring in other arguments here as well. When talking with this to other people about this, a common feeling people got was:

Why code in JavaScript when you can code in Assembly? Actually, why even code in Assembly when you jut directly can code in binary?

So, essentially, this means that generally abstractions are oftentimes necessary for us as developers, which I totally agree with! Now, the problem here is that not all abstractions are good, which segues onto my next point about how ORMs abstract away features of relational databases.

Abstracting away features of databases

When talking in the context of relational databases, the primary point is that they're not arbitrarily interchangeable. Every database is unique, and has it's fair share of strengths and weaknesses.

As I mentioned above, ORMs have a tendency to plaster a general, one-size 'fits-all' API over several different types of databases, pretending that the databases are in-fact arbitrarily interchangeable. You can apply this same sort of logic onto pretending that all different types of message ques are interchangeable, or that all operating systems are interchangeable.

Please don't pretend here that all relational databases are just columns and rows. They're not, and to get a perspective of this we can compare something like MySQL and PostgreSQL. Although at a foundational level they're both relational databases, they have vastly different features.

ORMs are decent for regular CRUD operations, but in-reality those operations don't even represent the majority of database interactions for large-scale products that have any level of complexity. Thinking about it, these operations are really simple to do in SQL itself. If your application primarily just uses simple CRUD queries, is there a point in having an extra layer of abstraction unless you're at a crucial time constraint?

Simplicity isn't always good

A common reply people gave me is that ORMs help smaller-scale startups ship things. Of course, with large companies such as Meta they'd want to spend time and energy into having their engineers optimise a custom low-level solution. In fact, since we're talking about Meta, I know someone who works at Meta that has told me that they use stored procedures for just about everything, which is the other end of the extreme.

But anyways, back to the topic -- for the average startup, ORMs can help them ship their product more easily, which is something I totally agree with. HOWEVER, I also believe that this 'development time' argument is primarily used by short-sighted managers in these startups who ignore the long-term costs of their decisions. Unless you plan on throwing out your startup and your company in a few months, the time you save now will be paid tenfold in maintenance over the years as your startup grows. There's a reason that companies hire DBAs (database administrators).

Issues with performance

As mentioned above, the fact that ORMs don't give you complete control over the queries you run in your database might cause you to overcomplicate simple queries.

Let me to explain: say that you're building a social media platform and you have to fetch a list of users from your database, fetch their recent posts, do something with them, and then write them back to your database. If you were using an ORM, this would require FOUR queries and trips to your database when in reality this could be done rather easily with just a single plain SQL query! If you have to keep on doing this, then it's definitely not great for performance. Along with this, ORMs tend to produce sub-par JOINs and further reduce efficiency in that way.

To add onto that, if you have a bit of prior knowledge on ORMs, you are probably well aware of the 'N+1 selects' problem. In case you're not already familiar, I found this great Stack Overflow Answer which describes it in detail.

The N+1 query problem happens when the data access framework executed N additional SQL statements to fetch the same data that could have been retrieved when executing the primary SQL query.

The larger the value of N, the more queries will be executed, the larger the performance impact. And, unlike the slow query log that can help you find slow running queries, the N+1 issue won’t be spot because each individual additional query runs sufficiently fast to not trigger the slow query log.

The problem is executing a large number of additional queries that, overall, take sufficient time to slow down response time.

Something to note is that this problem occurs in SQL as well, but you can fix it rather easily (as the answer shows). However, if you're using an ORM, due to the fact that you have no control over the code that it generates, you simply have ZERO control over whether or not you can run into this problem.

Moreover, another issue with ORMs is that they try to hide the semantics of relational databases from the developer. Instead of writing explicit joins of various kinds and whatnot, they provide you with a linear API and try to turn that into somewhat efficient or inefficient queries which have terrible performance.

Ultimately, the only way that can be as efficient as SQL is if SQL could be reduced to that API. Worse, you have little control over the queries they do generate, and they can change between versions (or on variables hidden to you).

At a foundational level, the problem is in the name. Object-relational mapping. Relational databases aren't about objects. Treating them as such you might as well use a simple key/value store instead (and that itself is a disingenuous statement as different K/V stores have different trade-offs).

Obscurity with ORMs

ORMs tend to just obscure what is going on. If I know SQL and PostgreSQL, I can essentially understand just about ANY project that uses them, without having to learn the specifics of one of two dozen ORMs with its own idiosyncrasies. If you are a startup that uses an ORM, then essentially the developers working on your product will always interact with the database through an ORM preventing them from developing any understanding of how the underlying database technology works. Due to the nature of different databases, you will have use cases where powerful database features might come into use. The fact that an ORM gives you an advantage in migrating to other database easily is something which simply isn't that common in reality. If a company does end up doing that however, it isn't that hard to incorporate those change into your SQL. As I've mentioned countless times above, for any non-trivial use case, you will need to use SQL.

Now another argument that people bring up here is that ORMs generally ship with some sort of way to run raw code. For example, Prisma, an ORM that I personally use in almost all of my projects, ships with useful methods to execute raw queries.

My response here is while you can absolutely use an ORM with raw, you can just as easily NOT use an ORM in applications that you plan to scale 😛.

The advantages of using an ORMs

I think we've discussed the downsides of using an ORM enough now. Let's talk about the upsides. A big one is that it speeds up development.

ORMs save development time

If you look into the fun projects I've built during hackathons or other events on my GitHub, you will see that most (if not all) projects use the Prisma ORM for TypeScript.

You would say, it's pretty hypocritical for me use an ORM in my projects while writing this. I would definitely agree with you here, besides the fact that most (if not all) my projects are built during hackathons, when I actually have the motivation to finish something.

As mentioned above, ORMs probably aren't a great long-term decision, but they come with a lot of upsides. With something like Prisma, you can generate types from your schema and use them in your application, saving you a tremendous amount of development time in places like hackathons where every minute of your time is valuable.

However, as I've mentioned above, if you're a startup then this should not be a sacrifice you're willing to make unless you plan on throwing out your project within a two weeks. The time you save during development now is what you'll be paying in tenfold in maintenance as your startup scales.

ORMs make SQL simpler

Unfortunately, most beginners aren't very eager to learn another language just to communicate with their database. This leads to beginners either using NoSQL databases or using an ORM to connect with their database. Considering the simplicity applications that most beginners write, it will not matter much if they decide to use an ORM or not. In fact, if a beginner doesn't properly validate values received from their web forms or cookies before passing them onto SQL queries executed by the database server, they're at a risk for a SQL injection attack.

I think more experienced developers can definitely easily avoid SQL injections from occurring in their applications, however in the context of beginners it's very possible that they overlook a vulnerability like this if they don't have a prior understanding of what exactly it is. SQL injection is one of the easiest and potentially one of the most dangerous threats to application security. In-fact, 65.1% or two-thirds of all attacks on software between 2017-2019 were SQL injections alone.

With that being said, you probably got the idea that SQL injections aren't a joke. You face little to no risk of SQL injections using an ORM unless you use plain SQL somewhere else in your application.

Although it's worth asking yourself: considering that you don't need an ORM to prevent SQL injection, would it really a good idea to for you use an ORM just because they prevent SQL injections if you're not at a critical time constraint?

Conclusion

In the end, I think ORMs are an nice tool if used appropriately in the correct scenario. Once you balance out the positives and negatives however, it may seem that including an ORM in your stack may not be the best idea once your startup scales. I love tools which make SQL easier to work with! There's something called sqlc which basically compiles SQL to type-safe Go. Tools like this provide us with some of the same advantages as an ORM, just without effecting our actual database workflow.

The underlying issue I saw oftentimes -- especially in the JavaScript community -- was that people treated ORMs as if they were some kind of 'silver bullet'. I've seen several large influencers on Twitter (mostly those who have only a cursory understanding of databases and SQL) highly praise ORMs for being flexible. I had written this article with the aim of familiarising you about the different advantages and disadvantages that ORMs provide to help you effective decide when it would be appropriate to include them in your tech stack. I hope you found this useful!

That's all for today, until next time 👋.

Top comments (36)

Collapse
 
meatboy profile image
Meat Boy • Edited

Why not both ORM for CRUDs and raw queries where performance or complexity is required. MikroORM and Objection (both are from JS/TS world) support such behaviour and IMO it's the perfect balance between the two approaches. Also, there is something called premature optimization so I would first go to design architecture and check if optimization at the very beginning is worth time and outgoing costs.

Collapse
 
harshhhdev profile image
Harsh Singh

I mean, you can use the raw feature in ORMs but that essentially defeats the point of using an ORM for your application to some extent.

Collapse
 
meatboy profile image
Meat Boy • Edited

Nope. For example:

posts.getAll();
Enter fullscreen mode Exit fullscreen mode

or

const r = posts.getAll({fields: ['id', 'content']});
Enter fullscreen mode Exit fullscreen mode

can simplify writing queries, update fields on alter migration, give you autocomplete, transpile errors (ts) and such which below example won't give you until execution afaik:

const r = q.exec<Post>('SELECT id, content FROM posts'); 
Enter fullscreen mode Exit fullscreen mode

you can omit this problem by using IDE with db features (e.g. IntelliJ) but then it's not matter of language and code itself but developer tools used to write it.

So still, the bottom line is "it depend" what to use ;)

Thread Thread
 
harshhhdev profile image
Harsh Singh

I mean, in the end it's up to you. Is it really worth it to include an ORM in the stack if you're not at a crucial time constraint because of auto-completion? For me, the answer is no. These queries are extremely simple and anybody with an elementary understanding of SQL can write them. The advantage is that they might be safely typed, but you can just as easily define your own types or use a library to generate them for you.

Collapse
 
rehmatfalcon profile image
Kushal Niroula

While the article is well written, I do have issue with this statement.

However, if you're using an ORM, due to the fact that you have no control over the code that it generates, you simply have ZERO control over whether or not you can run into this problem.

Most ORM that I have used allow me to eager fetch data which means that the N+1 problem simply does not occur. EfCore for example by default disables lazy loading asking you to load relationships eagerly if you need, but also allows lazy loading if you need.

Collapse
 
harshhhdev profile image
Harsh Singh

I see, interesting. I have updated the statement to say 'some' ORMs. Thank you for pointing this flaw out.

Collapse
 
simcha profile image
Simcha • Edited

I think the author is right, ORM on the one hand simplifies the complex queries in the relationship, and on the other hand it loads on queries that could have been simpler if you had written them in SQL.

In ORM there is a common language between all the databases, this is both an advantage but also a disadvantage as the author wrote.

Keep in mind that there are ORMs that come with a combination of query builder, so you use the query builder while moving with the ORM so you can earn both worlds.

For example in building questions on Laravel if I would like the authors to count their posts:

Author::withCount('posts')->take(10)->get();
Enter fullscreen mode Exit fullscreen mode

The SQL result

SELECT *, 
(
   SELECT count(*) 
   FROM posts 
   WHERE posts.author_id = authors.id
) AS posts_count
FROM authors 
LIMIT 10
Enter fullscreen mode Exit fullscreen mode

On the other hand, you can use both together, and get both more pleasant language, and better performance.

Author::take(10)
  ->join('posts', 'posts.author_id', '=', 'authors.id')
  ->select('authors.*')
  ->selectRaw('count(posts.*)')
  ->get();
Enter fullscreen mode Exit fullscreen mode

The SQL result

SELECT authors .*, COUNT(posts.*)
FROM authors 
INNER JOIN posts ON cposts.author_id = authors.id
LIMIT 10
Enter fullscreen mode Exit fullscreen mode
Collapse
 
harshhhdev profile image
Info Comment hidden by post author - thread only accessible via permalink
Harsh Singh

Personal comments on me are not constructive criticism. Notice the difference in our tones? Despite my age, I had a mature conversation with you about ORMs and Raw SQL. It was you in the end who started hurling personal comments directed at me. I'm a moderator on this platform, and this is your final warning to stop with the personal comments before I report you to the moderation team.

My tone throughout the article has not been authoratitive. In the beginning of the article I clearly mention that I'm not here with an intent to bash anybody's technology choices.

You need to cool it on the over-the-top responses about hating things you don't use are not acceptable here. The comment section is a for people to discuss technology and other topics related to it. Based on which tech someone uses doesn't fit with that. You need to stop making sweeping statements like you've made in this thread, especially when you struggle to back them up with valid reasoning. Using your age is a particularly bad example to get moral high-ground.

Try to be a bit more in touch with the fact that your lack of experience is evident in the way you talk about these topics and over-indexing on being 'right' or 'the authority' coupled with statements such as will detract from your ability to connect and work with your collaborators in the future along with having constructive discussions with others on this platform.

I'm all for having constructive debate and conversations in the comments, however you've crossed the line here. You have zero authority to tell me that I'm not allowed to share my thoughts on technologies on this platform, especially when I do it in a tone that isn't authoritative like yours.

Collapse
 
bukanvalen profile image
Valentino Harpa

This is a pretty underrated topic to discuss, I totally agree with all your points! I think more developers should really consider learning about security from the start. It's very good to start learning best security practices from the get-go, rather than learning it and applying it later.

Collapse
 
harshhhdev profile image
Harsh Singh

I totally agree! Thank you for reading.

Collapse
 
kevinhickssw profile image
Kevin Hicks

You do a great job of listing some of the benefits and drawbacks of the ORM, but I think one thing that isn't as accurate is that it isn't the best idea as a startup scales.

There are definitely things raw SQL or sprocs are better suited for and a place might not want to use an ORM for everything, but many large companies use a mixture of ORMs and sprocs and raw SQL. Microsoft has a case study page showing several large companies using entity framework. I work at a place that has some usage of entity framework that processes tens of thousands of orders and millions of dollars a day.

The benefits such as saving development time, simpler SQL and not needing everyone to be a SQL expert does pay off over the drawbacks and sometimes it pays off even more for larger companies compared to smaller ones.

A small startup might not be able to afford database devs, database administrators or database reliability engineers so they need devs that can do a little bit of everything, while a large enterprise can afford those roles.

These enterprises then don't need to have everyone be an expert at SQL or avoid ORMs where it may save time because they have experts to convert the ORM queries when needed or may have standards of when to use the ORM or go to the database team to handle it another way.

As I said great article and your benefits and drawbacks are pretty accurate, but it is much more of a case by case basis to decide when an ORM is right instead of basing it off company size.

Collapse
 
philipstarkey profile image
Phil Starkey

Have you used ORMs outside of JavaScript/TypeScript?

I ask mainly because the ORM downsides you list seem much more of an issue whenever I've looked into ORMs for JS. I can't tell if this is due to JS making it hard to write syntactically nice ORMs or if it's just because they are not very mature yet.

My main comparison point is the Django (Python) ORM. It is quite trivial to avoid N+1 issues in Django. It's not obscure because the rest of the backend is usually also Django so if you hire a backend developer, you hire one that knows Django (and thus the Django ORM). It's not that simplistic - I've written reasonably efficient queries with complex annotations that derive the value from nested subqueries, entirely with the ORM.

Don't get me wrong. The problems still exist in corner cases, DBAs are important, Python in general has its own performance issues, etc. But the general feeling I've got from my own research, and reading other people's opinions, is that for ORMs, the situation is much, much worse in JS/TS.

Collapse
 
harshhhdev profile image
Harsh Singh

Interesting. I have used diesel once when I was writing Rust, but other than that no. I think regardless of what ORM you use, they tend to share many of the same problems.

 
harshhhdev profile image
Info Comment hidden by post author - thread only accessible via permalink
Harsh Singh

I'm not repeating what my dad told me. My dad doesn't even know what an ORM is. He was a DBA back in the day when these things didn't exist. Your immaturity is evident the way you're talking, and it's not acceptable here.

This post was featured on the top seven posts of this week. I don't think the people at Dev would choose to do so if I decided to speak in an authoritative tone or if the contents of this article were poorly written and incorrect. It's shameful to me that new people that're looking into the comments of the post would see this anarchy go on in the comments. We were having a constructive discussion until you crossed the line and made personal comments about me. I request you to stop.

@graciegregory I've reported this comment already, but this doesn't seem to stop. I'm tired and exhausted of this by now. Would you mind taking care of this?

Collapse
 
bstjean profile image
Benoît St-Jean • Edited

In my experience with ORMs, most of the issues you point are often times related to to what I call the "all-or-nothing" ideology. Many times I've seen projects where "all has to be coded in [insert your favorite language]" when you could optimize by using database views, stored procs, functions, CTE, windowing functions and all the tricks a database does well (and better) than your favorite language. Besides, many ORMs are really crappy. There are lots of stuff good ORMs (Like TopLink for Smalltalk or Glorp) can do intelligently (prefetching, streaming, caching, etc). But you must always keep an eye on the SQL your ORM generates and use the database "tricks" when necessary. Another problem with ORMs is that a LOT of developers are really bad at SQL. That doesn't help when you're unable to detect bad queries in the first place.

Collapse
 
harshhhdev profile image
Harsh Singh

Yeah, I totally agree with you here. ORMs essentially encourage developers to not learn SQL, and that's what ends up happening oftentimes. So you have people who don't know what the 'WHERE' clause is in SQL praising these things and encouraging other beginners to begin using them in their stacks.

Collapse
 
thexdev profile image
M. Akbar Nugroho

Just query your DB using ORM then cache it. Create a service and use raw query if you deal with complex problem. Write the documentation why yo do this so next developer also understand.

ORM just a layer of abstraction so you can access your data in OOP way. It just a tool and use it in right situation 🤷‍♂️.

 
harshhhdev profile image
Info Comment hidden by post author - thread only accessible via permalink
Harsh Singh

I'm not going to argue with a child with no professional experience. You don't know anything but what you've read.

Personal comments are totally uncalled for. Being a jerk isn't what this platform is for. If that's what you wish to do, I suggest you find some other platform to do it.

My dad is a former DBA, and I've been doing contract work with databases since I was 14 (I'm 16 now). I can go on, however I feel there is no need for me to explain to you anything for validation or acceptance.

 
harshhhdev profile image
Info Comment hidden by post author - thread only accessible via permalink
Harsh Singh • Edited

I'm not an expert, nor did I claim I was. If you had a problem with my article, you could've clearly stated that without making personal comments directed at me and I would've addressed it.

Yes, ORMs aren't always the best approach, but they often are.

This would be an example of an authoritative tone. Do you love ORMs? Great! I'm not bashing you or anybody else for their choice to use an ORM in their stack. I'm backing up, with proper evidence, my reasons as to why I would advocate that someone does not include an ORM in their stack unless they're at a crucial time constraint. Please continue using an ORM if that floats your boat.

I'm not agreeing with my dad. He has not even seen this post, nor have we talked about ORMs together. Aditionally, he was a DBA back in the early 2000's when ORMs weren't big at all. My dad has zero clue on what an ORM is.

 
harshhhdev profile image
Info Comment hidden by post author - thread only accessible via permalink
Harsh Singh

Pointing out your lack over experience / age wasn't meant as a personal attack, it is a relevant fact. Calling you a kid and mentioning your dad I do apologize for that. If I made a Python post, it'd be relevant I have basically 0 experience with Python and take my opinions with a grain of salt. That was my only intent.

That's not the problem. The issue is that you're using my age to downplay this post and frame it as uncredible. If you were using proper evidence to do this, I wouldn't have an issue.

Your example about the Python post is incorrect. If someone had zero experience with Python and wrote a post about it, it'd be ideal for you to use proper evidence rather than hurl insults at the author of the post relating to their age to discredit them or their article.

In this article, I've mentioned that I am not speaking authoritatively nor do I wish to bash your choices of technology. You have very conveniently edited the immature comments on this thread to make it seem like I'm overreacting on your comments.

The only example of a company using an ORM at scale that you've given me is Stack Overflow, and I've already pointed out that Stack Overflow doesn't do anything that'd require remotely fancy queries.

Collapse
 
sainig profile image
Gaurav Saini

Very nice article. Totally agree!

I’m also kind of the biggest advocate for tailormade solutions for anything more complex than a simple CRUD app. You wouldn’t buy your wedding suit straight off the shelf, so why do the same to software that you’re writing.

The next time some discussion like this pops up I’ll have something to point others to 😁

Collapse
 
harshhhdev profile image
Harsh Singh

Hey Gaurav! I'm glad you liked this article, and I agree with your points. The main argument is that it saves development time, although as I mentioned you pay the price of the development time you save now in tenfold over the next few years.

 
harshhhdev profile image
Info Comment hidden by post author - thread only accessible via permalink
Harsh Singh

Every company has a different approach. For instance, Facebook uses stored procedures for everything.

I've rarely seen ORMs not being used in enterprise

This statement is both anecdotal and incorrect as you fail to back it up with evidence. I do not care what you have seen, and I don't think anybody else does either. What I do care about is you backing it up with facts and proper statistics.

Also, a lot of enterprise companies use PHP for their application. Should I begin using it too? Bad analogy.

Also, what gave you the audacity to assume that I haven't worked on projects at scale? Right now, I currently do contract work as with smaller scale startups. For instance, I helped Venlo build their backend with serverless AWS Lambda functions and the Prisma ORM. I've used them at professionally, and you continue to try and downplay the post because of my age and I've asked you to stop. In this comment, you just simply assumed that I haven't worked with these technologies on projects with scale because of my age. The last sentence was completely uncalled for an unnecessary.

Some comments have been hidden by the post's author - find out more