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 ...
Some comments have been hidden by the post's author - find out more
For further actions, you may consider blocking this person and/or reporting abuse
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.
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.
Nope. For example:
or
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:
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 ;)
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.
While the article is well written, I do have issue with this statement.
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.
I see, interesting. I have updated the statement to say 'some' ORMs. Thank you for pointing this flaw out.
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:
The SQL result
On the other hand, you can use both together, and get both more pleasant language, and better performance.
The SQL result
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.
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.
I totally agree! Thank you for reading.
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.
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.
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.
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?
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.
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.
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 🤷♂️.
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.
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.
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.
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.
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 😁
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.
Every company has a different approach. For instance, Facebook uses stored procedures for everything.
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.
You seem to keep on repeating the same points over and over again even though I've previously refuted them. The thing about keeping your schema and code in sync isn't specific to an ORM, nor is preventing data loss. There are ways to do that with SQL as well. Also good luck with schema migrations in MySQL, those can't be transactional.
Once again, I suggest you give the article a read and argue against points I've brought up on there as opposed to bringing up points that I've already addressed and refuted in the article.
Let me respond to this in parts. Before I begin, I would suggest that you actually read the article as I've already refuted many of your arguments in my article.
The point you brought up about migrations are not ORM-specific.
SQL injection? LOL. That's got nothing to do with ORMs and is all with not knowing your basic APIs for querying databases. You can very easily prevent a SQL injection from happening in your application. As I've said before, please read the article before beginning to argue with me over the points I've already addressed and refuted.
Migrating from a database to another database is something which simply does not happen much in production. If it does, it's easy enough to do in SQL. There isn't a reason that you'd want to include an ORM in your stack just because it's easier to switch databases.
I've already talked about Stack Overflow on another comment. Also, just because Stack Overflow made a poor design choice doesn't mean that all companies should do it too. It's not a hill to die on.
You forgot something. Stack Overflow doesn’t do anything that’d require remotely fancy queries. Even if they’re ‘fast enough’, they provide terrible ergonomics. They don’t make your code easier to write.
.raw()method so that you can write raw SQL for complicated senario.Pros:
Cons:
That's all.
I don't do much backend stuff but I am really intrested in knowing about ORMs
I am using mongodb for my backend purpose am I using ORM ?
You are probably not using an ORM, nope. ORMs become kind of redundant when using a NoSQL database like MongoDB as it isn't a relational database. ORMs like Prisma have support for MongoDB however it's not really an ORM at that point, it's more like an ODM.
Ok. Here's my take on this.
Database programming is mainly about Data definition and Data manipulation. Use ORMs for data definition. They make this easy, coupled with a lot of interesting extra features, yes.
When it comes to Data manipulation, use the raw query feature provided by ORMs, especially for the
SELECTstatement, as it's the most precious of all query types. You can use ORMs forINSERTandUPDATE.What do you think?
A reason to create objects that model relational data and are resolved to SQL is the queries can be standardized and reused depending on how you choose to implement a query builder.
You can standardise and re-use queries in regular SQL as well, you don't need an ORM for that.
I would argue is way more unsecure to use raw CRUDs vs using an ORM, plus any ORM can also issue RAW queries when needed and be simple when you don't need full control.
If you read the article, I've addressed why this isn't a valid reason to use an ORM. However, feel free to continue using an ORM if that's what floats your boat. I'm not here to bash your technology choices.
I did read it, I felt you got offended or just salty, it is probably the JS community in you.
Honestly your reasons are biased , I am not saying it myself, you did, you start with "In my opinion", and it does seem you came up with them by doing a hello world kind of thing.
If is just feedback, I am, indeed, here to bash your opinions, this is how we all learn.
Any way, have fun, it is a good article, you just need to learn take feedback better and reply in way that does not make you look salty, judging by this answer and the answers on other threads.
Cheers.
'Bashing' isn't an equal for constructive criticism. Constructive criticism is welcomed, bashing is not.
I don't know how one could develop such an in-depth understanding of what an ORM is or write this post with only a cursory understanding of ORMs, as you've mentioned.
I'm open to take feedback, however your comment isn't giving me any feedback on the post. If you look at other comments in this thread, people have asked me to correct smaller things and I've done so gracefully if I felt it was needed. I can confidently say I'm mature enough to handle feedback in a constructive way and enrich myself from it.