One thing that ORMs shine in is query composition. For example, starting from a base definition for retrieving a list of entities, you can _dynamically_ compose orthogonal operators that modify the base query, e.g. projection, filtering, sorting, grouping, joins, etc. without having to manually write SQL for every combination of those operators; the ORM takes care of generating the SQL code for you.
That being said, ORMs still don't enjoy the level of trust that optimizing compilers have enjoyed for decades. That's always going to be a barrier for wide adoption, especially from folks who are experienced in SQL. It's similar to how in the 1950s (and maybe somewhat 1960s) there was resistance for "automatic coding" by compilers; those who were experienced in assembly thought that compilers will always produce sub-optimal code. But it's clear that high-level languages have won (at least outside embedded/low-level drivers).
The point is: any form of code generation will always find resistance until it proves itself.
I agree they shine at dynamic query composition, but I find the sort of UI that necessitates this dynamism are universally terrible. They end up being data dumps that allow the user to filter and sort themselves instead of having a UI that actually understands their workflow and the data the need to accomplish it. It's like sending the user an excel file and saying "here, you can manipulate it how you want".
This is also something light weight orms or query generators handle fairly well, if not better.
That being said, ORMs still don't enjoy the level of trust that optimizing compilers have enjoyed for decades. That's always going to be a barrier for wide adoption, especially from folks who are experienced in SQL. It's similar to how in the 1950s (and maybe somewhat 1960s) there was resistance for "automatic coding" by compilers; those who were experienced in assembly thought that compilers will always produce sub-optimal code. But it's clear that high-level languages have won (at least outside embedded/low-level drivers).
The point is: any form of code generation will always find resistance until it proves itself.