SQL is so flexible. When I put myself as a user, I also prefer those tools that provide nice UI for filter/reporting, but also give me SQL interface to do advanced query, such as PostHog, Resmo, JIRA(?)
ORMs never block you from issuing raw SQL queries. But mapping the results to entities inside your programming language’s abstraction is a repetitive task, ready to be abstracted away. The reverse direction is the same way.
I feel most of the criticism of ORMs come from people who don’t actually know how to properly use one. They were never meant for OLAP, they are for OLTP.
But one issue with ORMs is also that they can bait you to try more complex queries, where eventually you might run into one slight edge case that you spend huge amount of time finding a solution for because reverting to raw SQL will not feel elegant at that point - and feels like you've failed in some way. So you might run into these edge cases and then also you might have terrible joins without really knowing. Also different ORMs have different APIs and capabilities, which means more time learning those things and being uncertain whether this particular ORM even supports what you want to do.
I think generally a lot of time will be spent in analysis paralysis and overthinking. Is this query doable with ORM? How long should I google? How far into docs I have to go, do I need to go to ORM source code to figure out how to implement this? If it's a project with other developers, then will they disapprove of me using raw SQL here, and giving up on trying to go for an elegant ORM solution.
Yeah, nice example, just overall I feel like I've spent more time on edge cases/not knowing syntax top of my head with ORMs compared to if I just went with raw sql. Especially if working with different languages, each ORM handles syntax slightly differently and it messes up muscle memory.
I still automatically generate types from the database table and use helper fns when I need them to do certain type of abstraction.
And if you only need CRUD/ORM basic functions, maybe why even need a relational database. Although I would still go with relational as my first choice even if I start out with only simple CRUD, just for future's sake, so maybe not a good point.
In an ideal World there should be some sort of type parser for an sql query though.
And first class support to analyze the SQL query within the IDE (e.g. you make a syntax typo in an sql string or expose a potential sql injection vulnerability), an automatic linting or IDE tool would alert you of it, but at the same time a mechanism to generate response type if creating a parser for the compiler/build tool/IDE doesn't seem enough.
Sometimes I do end up inventing my own "ORM" with helper fns and objects, but I still feel more confident about using this one as I know that I can get exactly as flexible as I want.