[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. ;-)