[stephen]

I'm admittedly an ORM apologist [1], but a few of his points articulated as "deal breakers" aren't that bad imo:

- "the pernicious use of foreign keys [...] links between classes are [...] foreign keys" ==> that just sounds like schema normalization, which is usually a good thing?

- "bending over backwards [...] to generate SQL that runs efficiently" ==> the huge majority of ORM-driven queries are "select * from table where id in ..."; for the queries that are more complicated than that, then yes use SQL! That's allowed!

Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction.

- "dual schema dangers" ==> he's exactly right that database should own the schema definition, but then just codegen the entities from the db schema? That's your singular source of truth, no drift. You can do this with Hibernate, ActiveRecord, Joist, many ORMs.

- "Identities" ==> ironically I think ORMs (that use the unit of work pattern) actually have net-better DX here b/c you can hook up a graph of entities with just references.

I.e. hook up a book to its author w/o knowing their ids yet, which explicitly avoids the annoyance he mentions of doing a partial commit/going to the db to figure out "what value should I INSERT into in the book.author_id column?" (but my author is new) in the middle of your business logic that just wants to "create books".

- transactions ==> agreed that "transactions via annotations" ala JPA/Hibernate are terrible, but afaiu all "internet scale" apps these days do reads outside of transactions, and just use op-locking during the singular flush/commit step to the db.

Disclaimer I am sure I won't change anyone's minds :-)

Edit: in the HN comments, we're debating "the best way to generate SQL", which is fine, but imo it overlooks the biggest value for ORMs: enforcing business invariants.

I.e. yes a simple INSERT is trivial is write, "why have the ORM to that!", but are you going to enforce the same business logic in the 10 places you do `INSERT authors` in your codebase? And if the answer is "I write an single `insertAuthor` abstraction to enforce this" then you're half-way to writing an adhoc half-specified, bug-riddled version of what a reactive ORM like Joist will do for you. [2] :-)

[1] https://joist-orm.io/

[2] https://joist-orm.io/modeling/why-entities/

[hatefulheart]

I have seen many ORM enjoyers argue the point about “you can just use SQL!” but I have never once seen an ORM enjoyer allow it, much less do it themselves in an actual codebase. They will time and time again prefer you write 100 lines of Typescript/Python for what could be achieved with 15 lines of SQL.

[jghn]

To make matters worse, most of the time I've successfully argued a project to just use SQL instead of an ORM, what has happened is that people over time built a home rolled ORM in the development language.

It's like people can't just let go.

[hparadiz]

This is inevitably what happens every single time so just use an ORM and stop being stubborn.

[jghn]

The problem is that "ORM" does a lot of heavy lifting as a term and can mean different things to different people. Like yes, obviously, one needs some sort of SQL -> data structure transition on the boundary (using "object" overfits to OOP!). But that can be extremely light weight. Let people write SQL, have a thin layer to pull the results back out into the appropriate data structures, and move on.

[hparadiz]

Every good ORM lets you write SQL. Mine for example has a getByQuery and getByWhere as standard methods. An ORM isn't just writing queries for you it's also handling type casting from lang primitives to SQL and back. In 99% of crud rest apis there should be no need to write your own SQL though.

[zadikian]

And then the 100 lines of JS/Py ends up being way slower than the manual SQL, plus the autogen'd SQL part of it is slow, plus you can't even get the SQL query to profile without running the actual thing with prints.

[hatefulheart]

You got it in one, small world huh?

[smw]

Even the 'worst' of the ORMs (according to the people in these threads) makes this very easy:

  users = User.find_by_sql(<<~SQL)
    SELECT users.*,
           COUNT(posts.id) AS posts_count
    FROM users
    LEFT JOIN posts ON posts.user_id = users.id
    GROUP BY users.id
    HAVING COUNT(posts.id) > 10
  SQL

  users.first.posts_count
  # => 17

[pjmlp]

Worse, that code will be executed on the receiving end, and waste a bunch of network traffic.

[7bit]

Great anecdote. Doesn't validate your claim

[hatefulheart]

Looks like I’m not the only one, check the thread.

[7bit]

Still just anecdotes. Who cares about those

[hatefulheart]

You’re on a forum where people share anecdotes, so presumably, you?

Are you dumb or are you just pretending? I’m going to guess the former!

[nfw2]

The reason given to use raw SQL is for the performance not the perceived code clarity.

[baq]

If you never used a CTE, maybe… The reason to use SQL is to get what you need out of a database. Performance is orthogonal to that.

[nfw2]

Obviously, this means using raw SQL instead of an ORM, as the article was discussing the trade-offs of the two and wasn't a 101 course on what SQL is

[hatefulheart]

I’m not sure why you thought I meant code clarity and not performance? It’s clear in all cases the correct SQL query will be more performant.

Confused at what you’re evening trying to say here. Are you suggesting that 100 lines of application layer code is easier to understand than 15 lines of SQL?

[nfw2]

1. Because you referred to lines of code as the way to suggest SQL is obvious better, not performance

2. No, my point was that talking about code clarity was a distraction because to talk about lines of code as a determinant of performance is clearly wrong.

3. Tangentially, yes, if some behavior takes 100 lines of general purpose code to express, I would rather read it in the general purpose language than in SQL even if the SQL was fewer lines. It's hard to imagine why this would ever be the case though.

[airstrike]

The correct SQL query will be more performant than what? The correct ORM call will build the same correct SQL query.

ORM is ultimately SQL

[hatefulheart]

So there is no CPU cycles for the ORM itself? That’s free?

[airstrike]

It's 2026. CPU goes brrr. It's absolutely trivial compared to the query execution time.

[hn_throwaway_99]

> "bending over backwards [...] to generate SQL that runs efficiently" ==> the huge majority of ORM-driven queries are "select * from table where id in ..."; for the queries that are more complicated than that, then yes use SQL! That's allowed!

This is exactly why I hate ORMs. As I always put it "ORMs make the easy stuff slightly easier, and they make the harder stuff way harder".

If you're just using an OEM for the "select * from table where ID in ...", then you're saving practically nothing by using an ORM - just learn to write SQL, because as you put it, you're going to have to use it anyway for places where it falls over. There are lighter weight options that do basic stuff like transaction management and binding result sets to object properties that are much less of a PITA than ORMs.

In practice I've seen people try to use the ORM features first for places that need complicated SQL (which is a reasonable assumption), only to waste a boatload of time before concluding the ORM makes stuff harder.

[maxwellg]

> There are lighter weight options that do basic stuff like transaction management and binding result sets to object properties that are much less of a PITA than ORMs.

Query builders like these are my personal favorite from a productivity perspective! The point of a query builder is to dynamically build SQL statements that have many subtle variations (do we want to filter by EmailID or PhoneID here? What about a subquery? Did the caller want all results, or just results where $field=X?). They're basically one level above string templating for SQL generation, and often have niceties around ser/de and transaction management as you mentioned.

Because they are primarily about query generation, it feels _very_ natural to pop off the hood and write raw queries directly when necessary. You can usually use the transaction management and ser/de parts with raw queries, too.

My personal favorite in this field is knex.js.

[hn_throwaway_99]

Knex has its own set of problems. Again, SQL is a very powerful, well-known language and there are simpler tools that make it possible to break up and reuse queries.

Years ago I was working on a project that used knex, then I serendipitously discovered slonik through this blog post, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41... (slonik has subsequently had lots of development since then). I decided to rewrite the entire persistence layer from knex to slonik over a long weekend and I'm so happy I did. I liked slonik so much that it was the only time I personally contributed to a programmer through GitHub Sponsors.

[stephen]

Disclaimer I just edited this into my OP comment, but "generating boilerplate INSERTs" is not the main reason I use ORMs -- it's business rule enforcement.

I.e. regardless of how easy it is to write `INSERT authors (...) VALUES (...)`, with an appropriately cute/ergonomic query builder to bind the variables/POJOs ... where does your business logic actually go?

Whenever you insert an author, are you always enforcing the same validation logic? Whenever you update a book, are you always updating the derived fields that need updated?

Getting the business rules right is "the actual hard stuff" imo, and nothing I've seen a query builder help with; it's always left as an exercise to the reader to reinvent their "business logic wrapped around POJOs" adhoc in their codebase.

[hn_throwaway_99]

This is an even worse argument for ORMs. Practically every system I've ever built had data access objects that were responsible for persisting and retrieving data. It's trivially easy to write the business rules plain out in whatever language I'm coding in - why would I want to unnecessarily wrap that in some opaque "rando-QL-invented-by-the-ORM-authors" than just specify it directly in code where I'm saving the object(s).

[xigoi]

> If you're just using an OEM for the "select * from table where ID in ...", then you're saving practically nothing by using an ORM

You’re saving hundreds of lines of repetitive boilerplate code. Do you enjoy writing something like

  users = [
    User(name=name, color=color)
    for name, color
    in db.query("SELECT name, color FROM user")
  ]

over and over?

[hn_throwaway_99]

The number of comments implying that ORMs are required for basic software engineering concepts like proper encapsulation and DRY is baffling.

But this gets to the heart of what I was saying. I'll grant you that ORMs save a little bit of boiler plate up front (but not much - ORMs have plenty of their own boiler plate, just instead of a universally understood language like SQL they have it in their own custom config JSON/yaml/XML), but that is where I spend a teeny fraction of my time coding. Writing "boilerplate" SQL for a decently large project (say 50-100 object types) takes me maybe an extra day in coding time. I have wasted multiples of that time trying to track down a single weird ORM bug, or poorly performing query. Plus, spending that time up front to write my queries is always the least stressful time of the project. What is most stressful is when my site is finally getting a big traffic push, but then something causes the DB to crater and the leaky abstraction of the ORM makes it ten times harder to debug.

[Kon5ole]

This might be the last year where we have to write code by hand unless we enjoy it though. ;-)

[swasheck]

> Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction

my experience is the exact opposite. People who love and advocate the merits of ORM insist that everything be executed through ORM because it introduces too much complexity for them to blend handwritten SQL with the ORM generated queries

[hparadiz]

I've written/worked on several ORMs from scratch. ORMs are the industry standard. When I see posts like this I simply can't take them seriously. All they are saying is "I won't be a team player" and "I don't actually understand the subject matter". The reality is at a certain scale there's an entire orm team that optimizes everything. But even when there's no team involved there's no way you can write anything more optimized because I'm already at the computational limit of how far something can be optimized.

There's no (good) ORM that doesn't let you simply put your own query in.

[swasheck]

I don’t understand this comment because in no way did I express that I’m not the team player. Seems like this is something of a sacred cow for you. Or maybe it’s a language barrier thing, but all I was trying to do was say that as a member of the data platform team, when I recommend handwritten SQL to address specific limitations of an orm, that is the response that I got. Hope this helps.

[hparadiz]

My reply was talking in general terms about the original post.

You wrote the exact opposite of my opinion here which is why I replied to your specifically:

> People who love and advocate the merits of ORM insist that everything be executed through ORM because it introduces too much complexity for them to blend handwritten SQL with the ORM generated queries

I believe strongly that good ORMs expose the ability to put your own queries in. But I can't possibly boil down all the reasons for this in one HN comment.

An ORM is not a query writer. It's a way to map SQL primitives to run time primitives in a static deterministic way backed by a suite of unit tests.

If you have a special query you wanna run that has 10 joins, 2 sub queries, and a derived view that's totally fine. No one says you can't. However remember that statistically 99.9% of all queries are not that.

[hatefulheart]

What optimizations are you making here when at the end of the day performance is dictated by the schema, the query planner and the network?

[bot403]

I read it as "I've optimized the orm to be minimal overhead over raw sql a lot of the time".

[hparadiz]

I've actually benchmarked the overhead for my ORM against every major PHP orm that exists.

https://the-php-bench.technex.us/runs/1

But the speed is irrelevant as long as it's good enough. Notice Laravel's Eloquent at the bottom of the list yet thousands of projects are being built with it regularly.

[hparadiz]

How can I possibly condense 24 years of deep knowledge in one comment for you?

The tldr is if you're ever concatenating strings in order to build a query you're just doing what the entire job of orm is but rolling your own and chances are you'll end up with a bunch of bugs in how you handle well.... Everything.

[hatefulheart]

I think your tone is a bit combative. You can certainly provide the cliff notes but if you want me to believe you’re at working at computational limits whilst talking to me about string concatenation in web dev backend languages I think the burden of proof is on you.

[7bit]

I don't think OP ever expected you to believe anything. He stated his experience and nothing more

[toast0]

> All they are saying is "I won't be a team player" and "I don't actually understand the subject matter".

I get the first part, but not the second.

Preferring to use SQL rather than an ORM + SQL is all about understanding the subject matter, which is the data as it exists in the database.

> The tldr is if you're ever concatenating strings in order to build a query you're just doing what the entire job of orm is but rolling your own and chances are you'll end up with a bunch of bugs in how you handle well.... Everything.

Yeah, so basically don't do this, except when you have to, like concatenating placeholders for a variable size IN query.

There's some classes of applications where it's hard to write all the queries because there's all sorts of mix and match stuff happening. Those are pretty much doomed to poor performance if the tables are large, so I would rather not play on those teams. On the bright side, the limit of a small table gets bigger every ram generation, and table scans on nvme aren't so painful either.

[hparadiz]

We're pointing out the same thing. Someone that uses an ORM knows when they shouldn't use them and I tend to trust that more than someone who simply refuses to use them and ends up recreating an ORM by accident.

[toast0]

> Someone that uses an ORM knows when they shouldn't use them

That's not been my experience. But admittedly, I've usually been brought in when the slow query is killing the database. Then I look at the query that nobody with any subject matter knowledge would have written, come up with an alternate query that will give either the same result or something close enough. Sometimes I have to then dig in and figure out how to make that happen, because the ORM user doesn't always know how to make direct queries.

But it sure did make the easy things easier, as the other poster said.

[hparadiz]

People focus on the query writing aspect of ORMs too much. That's not that primary reason you use an ORM. It's primary purpose is to hydrate objects in the runtime. If I pull a datetime from SQL there's a lot of value in having a single piece of code handle that datetime the same way across the entire stack. I can unit test that handling once across the entire code base. Very few ORMs are aware of how the data is indexed and yes a lot of people will write code that generates a complex WHERE clause against columns that aren't indexed. But that's an understanding problem. I expect someone who uses an ORM to understand SQL well. Including indexes and fixed length tables. Obviously you are encountering code made by people who don't understand this but the problem isn't the ORM. They would have made that mistake with or without an ORM.

[stephen]

Fair point, both "pro ORM" and "anti ORM" camps are prone to extreme stances.

I definitely don't agree with the "all queries must be executed through the ORM", and think that dogmatic stance has done a lot of damage to the ORM brand. :-/

[HelloNurse]

They don't consider the ORM the second class citizen it actually is: an optional simplified alternative to normal queries, that can be used for the easy cases.

[talon8635]

Believe what you want, but I would consider myself one of those allegedly mythical people

[bearjaws]

> the huge majority of ORM-driven queries are "select * from table where id in ..."; for the queries that are more complicated than that, then yes use SQL! That's allowed!

The issue is, your lowest value queries are always this type, then you get the 10-20 in any code base that are 100x more complex, and they are the ones your end users care about the most.

You end up with a 80/20 principal in the wrong way, it's great at producing queries that represent 20% of the value of your app, and awful for the 80% that define the core value of it.

[jghn]

The second issue is, if these queries are just "select * from table where id in ...", WTF bother with a library to abstract that away in the first place? It's trivially easy to handle this as SQL

[bluefirebrand]

> Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction.

I've always heard a major selling point of ORMs is "You don't have to write the actual SQL anymore"

Because of that, I tend to not trust people who use ORMs to even know how to write queries by hand in the first place

[stephen]

You're right, that has been another "pro ORM" pitch that has gone awry and, taken to the extreme, is wrong imo.

My nuanced articulation is "you don't have to write the _boilerplate_ SQL for the 90% of just-do-some-CRUD endpoints in your enterprise SaaS application, but you 100% need to 'know SQL' for the last 5-10% of ~reporting/analytics queries that the ORM is going to mess up".

[bluefirebrand]

Personally I find the 90% boilerplate SQL is easy enough to write that injecting an ORM into the process doesn't make much sense

But that's just me

[marcosdumay]

AKA making the easy parts easier while making the difficult parts harder.

[airstrike]

The difficult parts are just literally a raw SQL string so how is that any harder?

[marcosdumay]

That you somehow have to adapt the results into the same format the ORM uses. And has to adapt the parameters into taking data from the ORM. Or has to split your entire functionality from the ORM so you can actually access the database directly without one part of your code interfering with the others.

[jcgl]

No? ORMs don’t preclude writing raw SQL, so it’s just making the easy parts easier while leaving the difficult parts the same.

[bluefirebrand]

The ORMs I've tried tend to produce some pretty specific table structures that are a pain in the ass to work with outside of the ORM, imo

One of the sticking points I've found in the past is if I create a new table outside of the ORM, it doesn't know how to use it. Then if I try to add it to the ORM's model it doesn't use the existing table, it creates a conflict. Annoying stuff like that

[marcosdumay]

> the huge majority of ORM-driven queries are "select * from table where id in ..."

From my experience, you are mistaken on that. Those queries mostly come with some joins, either necessary or not to represent the object, and that often could be avoided if the data wasn't mapped into some standard object.

[DanielHB]

The main problem of mixing sql and orm together is that most orms don't provide a way to do raw queries in a type safe manner that plays well with non-raw-sql queries.