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.
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.
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.
From experience, I don't. ORMs are usually sold as 'learn this instead of learning SQL'. For many, the ORM creates the tables, alters the tables, and queries the tables; they don't see SQL and they don't know SQL. When that works, it works, but when it falls apart, they have to debug the SQL and the abstraction layer. I'd rather have fewer unnecessary abstraction layers.
> 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.
There's value there, datetimes are very complex, but the rest of the stuff it comes with obscures the value IMHO.
> 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.
It's hard to write the kind of complex queries I've seen by hand, and I like to imagine if you out how to do that, you'll also know why it's slow and not need my help... But the ORM is part of the problem, because when you've written bad queries by hand, and I give you a better query (or sequence of queries), it's easy to apply. When you've done it with an ORM, you may not even know where the query is made.
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.
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.
Ok then!