8 ms·
Smart developers both know SQL well, AND use ORM's. Sure ORM's are not going to write the most optimized code by default, and they're not going to build indexe
by LargeWu 3y ago
Smart developers both know SQL well, AND use ORM's.
Sure ORM's are not going to write the most optimized code by default, and they're not going to build indexes for you, etc. But they are a huge productivity enhancer and work well enough for a lot of the queries you're going to need. Most ORM's these days have advanced options too which enable the more fine-tuned use cases without having to drop into raw SQL.
- onlyrealcuzzo 3y agoIf performance is mission critical - ORMs seem like extra hoops to jump through. Am I missing something?
- phailhaus 3y agoORM's allow you to make changes without having to refactor every single SQL statement you've written manually. They can also be typed, unlike raw SQL strings, which helps a lot during development. It's another abstraction, just like the one between your language of choice and assembly code.
- deleted 3y ago[deleted]
- brandall10 3y agoIt's a higher level abstraction that that boosts dev speed and readability and is often good enough by default, esp. for CRUD work. More advanced ones have the ability to optimize things fairly well within the constraints of their API. And it's not one or the other. There's always an SQL escape hatch when you need one for complex queries.
- ryanjshaw 3y agoModern ORMs are pretty efficient [1]. Some developers use the ORM for the "write model" and native SQL for reads/reports, if you are read-heavy. [1] https://michaelscodingspot.com/npgsql-dapper-efcore-performance/ https://michaelscodingspot.com/npgsql-dapper-efcore-performa...
- BurningFrog 3y agoIt can be, but in real applications only a small percentage of queries are performance problems, and you're probably better off optimizing those than writing and maintaining hand written queries.
- ivanhoe 3y agoIt's Pareto principle at play, in RL you usually need to highly optimize just a very small portion of the queries - let's call it 20% for the sake of tradition, though it's realistically <5% IME - to get almost all of the speed-ups worth the trouble. ORMs then help you to write the other 80% quickly and in a maintainable fashion.
- kayodelycaon 3y agoManually writing queries has a lot of overhead cost. Your entire application now has the UI directly tied to what the query returns. You can make intermediate classes to separate the two but eventually you'll end up making a worse ORM you need to test and maintain. What an ORM gives you is a tunable level of abstraction. I know Ruby on Rails best so I'll use it as an example. ActiveRecord has a lot of tuning you can do, like partial selects and snippets of sql where you need it. If that isn't enough, there is find_by_sql as an escape hatch. If that is still not fast enough, you can call ActiveRecord::Base.connection and skip the ORM entirely. This will keep complexity limited to places where it needs to be complex. In my experience, every level of optimization you do comes with decreasing maintainability.
- randomdata 3y ago> Your entire application now has the UI directly tied to what the query returns. Not really. Without ORM, your entire application is now tied to sets of tuples (i.e. relations) instead of rich graph structures (i.e. objects), but the relation need not be equal to the relation returned by the query. You could still have a transformation from one relation to another. Not since the PHP days of embedding MySQL calls right in the middle of HTML have I seen anyone carry relations from end-to-end, though. The idea that anyone is not doing ORM these days seems like a straw man.
- lelanthran 3y ago> Not really. Without ORM, your entire application is now tied to relations (i.e. sets of tuples) instead of rich graph structures (i.e. objects), but the relation need not be equal to the relation returned by the query. And, in some edge-cases, the relation is more useful than the object graph.
- jimbokun 3y agoDepends. Clojure uses objects rarely. But still has really powerful abstractions for writing applications quickly.
- evantbyrne 3y agoYes. Every mature ORM that I'm aware of includes tools to trivialize fetching related records in an efficient manner. All people typically have to do is call the right method. In Django, for example, these methods are select_related and prefetch_related. Also, they don't have to be slow. When benchmarking my hobby golang ORM, I found the performance cost vs hand writing optimal SQL to be in the microseconds per returned row. Small price to pay for everything it gives you: schema migrations, query builder, object mapping, validation, etc. And if you happen to have a particularly hot endpoint that needs to return tens of thousands of rows at once during a HTTP lifetime, you can always dip back down to the driver level. It pains me that every time the topic of ORMs come up on HN that the comments are full of people warning not to use such a powerful category of tools. Maybe the tools they are using aren't very mature, maybe the underlying technologies are bit dated, maybe the documentation doesn't emphasize performance enough. I'm not really sure what's going on. Hopefully we can improve things so fewer people have issues and instead see performance improvements from using ORMs.
- lrem 3y agoI haven’t seriously used an ORM for the simple reason that I find SQL simple enough to not need to learn an extra DSL to hide it. Furthermore, when I’m done figuring out the query I need in a Jupyter notebook (technically Google colab, should be same), I can just paste it to the C++/Go server or the dashboard config. Raw performance didn’t ever matter that much to me. With all this: is there an ORM with documentation that’s good at explaining what would I gain over plain old SQL?
- evantbyrne 3y agoHave you ever used a rapid development framework like Django or Laravel? A good ORM is basically the core of any decent backend framework that interfaces with a RDBMS. You get to define a schema once which can then be used as a foundation for migrations, validation, queries, serialization, etc. Many ORMs may only help solve one or two of those things well, and in personal experience it is rare to see even okayish JavaScript ORMs, which may contribute to why people hate the category of tools.
- toomuchtodo 3y agoDo you have any references or resource recommendations for the migration path from ORMs to highly optimized SQL ops?
- davidw 3y agoMost good ORM's let you write your own SQL to fetch things when you realize there's a particular operation that requires it. So write the initial system with an ORM and then optimize as needs be. That might be obvious from the start, or might become evident later.
- matthewmacleod 3y agoAbsolutely this - the attitude of “never use an ORM” is an immediate red flag for me in a developer, indicating that they don’t understand the capabilities of a wide variety of ORMs and the trade-offs involved.
- BurningFrog 3y agoIt sounds like that "nerd macho" thing, where your main goal at work is to show how smart you are, not build working software systems.
- randomdata 3y agoThe bigger red flag is confusing ORM with ORM toolkits.
- deleted 3y ago[deleted]
- epgui 3y agoI think “never use X” is probably a bit far. But personally I can’t really think of a situation where I would rather have an ORM. I used to use them all the time. When I started doing pure FP I just realized I just didn’t really need my old ORM. When I came back to OOP languages (out of necessity, not pleasure), I realized I didn’t really need my old ORM there either. It’s not a strong opinion and it’s not a religious thing, it’s just a different perspective whereby you see that something isn’t really necessary. Benefits or not, you do pay quite a heavy price for the ORM, and I think most people don’t see this price because it has been normalized so much.
- xxs 3y agoa very good point. The downside is that I find most people just consider ORM a auto-sql, and the (data) modeling remains in a similar fashion. ORM benefits mostly from navigation + caching.
- bovermyer 3y agoI will use an ORM if it makes sense for the app in question, and then heavily tune it. Usually, though, I get better performance and less fragility out of leaning on carefully-crafted SQL+indexes to do the heavy lifting.
- randomdata 3y agoYour second paragraph reads more like you are talking about query builders rather than anything in the domain of ORM. ORM happens after the query has executed and you have a relation already in hand. Everything that happens before the relation is made available is outside of the ORM purview. Some ORM toolkits bundle query builders as a secondary feature, but they are still logically distinct features.
- itsdrewmiller 3y agoThe only reason ORMs are relevant to this conversation at all is because they include query generation. The vast majority for popular ones do that - one that I know of that doesn’t (dapper) labels itself a “micro ORM” because of it.
- deleted 3y ago[deleted]
- randomdata 3y agoORM never includes query generation. Some libraries often include both ORM toolkits and query builders, but that's like calling sorting 'encryption' because libraries often include functionality for both. That would be quite silly, and confusing for the reader.
- simonw 3y agoEvery ORM I've ever used (or contributed to building) has included a query builder as a core component. What ORMs have you seen that don't do that?
- randomdata 3y agoORM is the act of mapping relations (sets of tuples) to objects (graph structures). How could query building be a part of that? There are ORM toolkits that help produce the code to perform that mapping to save you from doing it by hand (although you can do it by hand, if you so wish!), but not even they could shove query building in the middle. Query execution has to happen before ORM can take place. You cannot perform ORM until you have the relation already in hand. (Mutation cases are in reverse, but I know you are capable of understanding that without it spelt out in detail) What would it even mean for ORM to include query building? If I'm reading you right, you seem to be saying that database helper libraries often include both query building and ORM toolkit features, usually along with other features like database migration management. Which is very much true. But why would you call one feature by the name of another? If you call query building ORM, is ORM best called database migration?
- gorjusborg 3y ago> Smart developers both know SQL well, AND use ORM's. > But they are a huge productivity enhancer and work well enough I disagree with the implication that using an ORM is the default that 'smart developers' always choose. Good developers choose the right tool for the job, so the tool selected depends on the job. Also, what the term ORM means (and its effects on the design) vary based on language ecosystem and library. For instance, if the application doesn't need to dynamically navigate relations, and instead can get by with just treating the database as a set of structs, an ORM like Hibernate would probably introduce more complexity than it gives benefit. In such cases, a library like Jooq or JDBI could make the overall design simpler, perform better, and make it easier to reason about the behavior of the system. I would argue those are not ORMs, yet they can yield a better product, depending on the situation.
- marcosdumay 3y agoSo... Your claim is that the ORM optimizer is better than the DBMS one? What DBMS do you use? I've used a few where that is true, and really, instead of using an ORM for that, you should stick to Postgres.
- renegade-otter 3y agoWhile my article reads like an anti-ORM manifesto, my warning is about relying on ORMs to do all the work. How am I supposed to understand advanced fine-tuning options if I do not understand how the low-level query works? Copilot may be your little helper in writing code, but it is not going to consistently write the best code that YOU can write.