I'm not sure if ORM is a great example, as they tend to work for the vast majority of use cases. If you're not using an ORM, you're either working on something where performance is critical, or you're making a mess.
I don't disagree with you, ORMs can be a great time-saver for your average CRUD app, where the SQL queries tend to be straightforward SELECT/UPDATE/DELETEs on primary keys, assuming you don't mind the overhead of learning the DSL. But as your querying requirements get more complicated, you either have to:
- Keep the DB queries simple and do the complicated stuff (joining, aggregating, etc) in the business logic, or
- Learn how to express your complicated SQL in the ORM's DSL, or
- Drop into the ORM's "raw" mode and write plain SQL
The first option might be fine for non-performance-sensitive stuff, but otherwise isn't making good use of the database, and the latter two options reveal the SQL behind the curtains, at which point the abstraction of the ORM is more or less totally broken.
[1] Raw SQL: https://github.com/bcspragu/stronk/blob/main/db/sqldb/sqldb....
[2] sqlc: https://github.com/bcspragu/logseq-sync/blob/main/db/sqlite/...
SQL is a high level, declarative language. You need to use good libraries that ensure queries can be safely composed and arguments passed, but you are not automatically "making a mess" if you're writing SQL unless you or your tooling is incompetent.
Personally, I really like Dapper (C#) as it solves the "object mapping" and "query composition" cases in a performant and ergonomic way while letting me write the (exact) query I want.
I mean there are 2k+ issues open https://github.com/typeorm/typeorm/issues/ - yes, not all of them are bugs and limitations, but if you filter the bugs, that's still more than 1000 ...