Most ORM frameworks have the ability to execute raw SQL, so using an ORM does not preclude you from using these features. ORMs tend to put you in the mindset of using simple CRUD statements most of the time - but I think that’s probably for the best, and these more advanced features should be used sparingly.
Not only does each ORM have a different API and access pattern to do this, once you hit this point, you're managing the object model ALONG WITH custom SQL for migrations.
There is also non-trivial danger in letting your ORM decide what data types your database schema should use. Got UUIDs? The ORM will store them as strings. Need start and stop timestamps? A range type with an exclusion constraint may be the right tool.
What you appear to consider "advanced features" are what some others of us consider "perfectly normal." Let's be honest: most devs consider knowing the difference between a LEFT JOIN and an INNER JOIN to be an advanced topic.
Devs will wax poetic about the marginal benefits of monads in limited scenarios, but throw up their hands in defeat when you mention a window function for a dashboard. They'd rather calculate it in the app layer with all the added latency that implies.
> Not only does each ORM have a different API and access pattern to do this
I think it is to be expected that different ORM frameworks would have differing APIs and access patterns. Similar to how different RDBMS's often have differences in the syntaxes and features that they support. It's not a big deal to look up the correct syntax in my opinion.
> you're managing the object model
Which means in the worst case that for some portion of your app you're basically back to where you were if you weren't using an ORM. Although I've found that JPA, for example, plays pretty nicely with native SQL queries (caching gets more difficult, but I think that's to be expected when moving logic out of the app and into the database regardless of whether you are using an ORM or not).
> ALONG WITH custom SQL for migrations
Personally I've always used custom SQL for all migrations. While ORMs often come with a tool to automatically generate a schema, I've never worked on a project that actually used this tool in production.
> There is also non-trivial danger in letting your ORM decide what data types your database schema should use. Got UUIDs? The ORM will store them as strings.
Some data types can be a bit tricky, but any competent ORM should at the very least have hooks to override the type on a field or implement your own custom handlers. [1]
> What you appear to consider "advanced features" are what some others of us consider "perfectly normal." Let's be honest: most devs consider knowing the difference between a LEFT JOIN and an INNER JOIN to be an advanced topic.
I said "more advanced features" - as in more advanced than basic SELECT/INSERT/UPDATE statements. I don't think any feature that you mentioned is particularly challenging to understand. And literally every entry-level developer I've ever hired has easily been able to explain the difference between LEFT and INNER joins in an interview.
