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.
Some ORM toolkits bundle query builders as a secondary feature, but they are still logically distinct features.
What ORMs have you seen that don't do 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?
"(Mutation cases are in reverse, but I know you are capable of understanding that without it spelt out in detail)"
Guess you were't capable after all. How do tech people manage to be so out to lunch all the time?
> Guess you were't capable after all.
Stop and think. If you actually did somehow mistakenly believe it was a one-on-one with another person, why would you but your head into the conversation? That would be completely illogical. There is a curious contraction here.
The first half of your comment does not logically precede that, though. There is no such implication.
Do you have any examples of open source ORM libraries that stick to the terminology you are advocating here? I've not seen one myself.
Maybe this is partly an ecosystem thing. What ecosystems do you mainly work in? I'm primarily Python with a bit of JavaScript, so I don't have much exposure to ORM terminology in Java or C#.
1. A feature that prepares a query to send to the database engine.
2. A feature that transforms data structures from one representation to another.
And we agree that these are independent? You can prepare a query without data transformation, and you can perform data transformation without preparing a query? I think we can prove that, if you are still unconvinced.
Okay, so that just leaves naming. What should we call these distinct features?
Here are my suggestions:
1. Query building. Building a query is the operation being performed.
2. Object-relational mapping; ORM for short. Mapping objects to relations (and vice-versa) is the operation being performed.
These descriptive names seem well suited to the task in my opinion, but I am open to better ideas. What have you got?
Now, there is something else in the wild that we haven't really talked about yet, but may be that which that you are alluding to. Another abstraction, or pattern if you will, that rests above (to use my suggested terms, bear with me) both query building and objet-relational mapping to unify them into some kind of cohesive system that actively manages records. This is where it starts to become sensible to consider (again, using my suggested terms for lack of anything better) query building and ORM intertwined.
I would suggest we call that active record. In large part because that's what we already call it. In fact, what is probably the most popular and well known active record library is literally named ActiveRecord, so-named because it was designed after the active record pattern. But, again, open to better ideas.
> What ecosystems do you mainly work in?
Python, Javascript (well, Typescript, if you want to go there).
Take Django for example:
Entry.objects.filter(created__year__gte=2024).exclude(tags__tag="python")
SELECT
"blog_entry"."id",
"blog_entry"."created",
"blog_entry"."slug",
"blog_entry"."metadata",
"blog_entry"."search_document",
"blog_entry"."import_ref",
"blog_entry"."card_image",
"blog_entry"."series_id",
"blog_entry"."title",
"blog_entry"."body",
"blog_entry"."tweet_html",
"blog_entry"."extra_head_html",
"blog_entry"."custom_template"
FROM
"blog_entry"
WHERE
(
"blog_entry"."created" >= "2021-01-01 00:00:00"
AND NOT (
EXISTS(
SELECT
1 AS "a"
FROM
"blog_entry_tags" U1
INNER JOIN "blog_tag" U2 ON (U1."tag_id" = U2."id")
WHERE
(
U2."tag" = 'python'
AND U1."entry_id" = ("blog_entry"."id")
)
LIMIT
1
)
)
)
ORDER BY
"blog_entry"."created" DESC
But the bit that builds the SQL SELECT query and the bit that populates the returned objects is pretty tightly coupled.
But, I do care about being able to precisely communicate with other people. What are we going to call the other things?
> 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.
Am I missing something?
And it's not one or the other. There's always an SQL escape hatch when you need one for complex queries.
[1] https://michaelscodingspot.com/npgsql-dapper-efcore-performa...
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.
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.
And, in some edge-cases, the relation is more useful than the object graph.
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.
With all this: is there an ORM with documentation that’s good at explaining what would I gain over plain old SQL?
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.
Copilot may be your little helper in writing code, but it is not going to consistently write the best code that YOU can write.
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.
Usually, though, I get better performance and less fragility out of leaning on carefully-crafted SQL+indexes to do the heavy lifting.
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.
ORM benefits mostly from navigation + caching.