I've followed ORM like/hate flamewars as long as I've been in the industry, and I think I'll be following them for as long as I continue. It's really an interesting and ultimately irresolvable tug of war between abstraction, automation, and a number of other issues.
I think people who have used ORMs a long time do not see them as a SQL replacement or as a total database abstraction layer, but as an automation tool for CRUD operations, with some capabilities of doing interesting things with querying, and potentially a methodology for managing database schema and migrations (depending on the tool). At their best, ORMs can tremendously reduce one's requirements for unit tests in particular areas because they have enough structural metadata to typecheck (automatically or compiler time) all the way down to forms in interfaces.
BUT, without a doubt, ORMs are indeed a tremendously leaky abstraction. That said, I would argue that every data store interface is, in that sense, a leaky abstraction. No matter the datastore you're using, be it RDBMS or a specialized NoSQL / search store, you need to learn the hows and whys of how it is structured. Not only should a professional learn SQL, but they should get at least a layperson's knowledge of how the database underneath the SQL works. However, the argument still stands, because when debugging a complex ORM query, you are debugging how it puts SQL together, and then you need to debug what the generated SQL is doing. So you're now multiple layers away from the actual thing you're managing.
Hence the 'final form' of my day-to-day ORM stance: I like to have an ORM around because of the massive automation around entity manipulation, but I think of the ORM in terms of the database and how the database should be structured, rather than as a generic domain model that happens to be mapped to some behind-the-scenes database. Furthermore, I believe it is quite valid to drop down to SQL for the very complicated stuff, especially if you want to trust to the query planning of the database. Once you do, you've broken the total safety of the abstraction, hence my ultimately seeing ORMs as automation tools rather than abstraction interfaces. I have no blame for people who refuse to utilize them, though I'd argue in a design meeting for their use, and hope I wouldn't have to take on all the CRUD queries if I lost the argument, because I'd start writing code to generate them, which then would become a terribly under-engineered faux-ORM :).
I think people who have used ORMs a long time do not see them as a SQL replacement or as a total database abstraction layer, but as an automation tool for CRUD operations, with some capabilities of doing interesting things with querying, and potentially a methodology for managing database schema and migrations (depending on the tool). At their best, ORMs can tremendously reduce one's requirements for unit tests in particular areas because they have enough structural metadata to typecheck (automatically or compiler time) all the way down to forms in interfaces.
BUT, without a doubt, ORMs are indeed a tremendously leaky abstraction. That said, I would argue that every data store interface is, in that sense, a leaky abstraction. No matter the datastore you're using, be it RDBMS or a specialized NoSQL / search store, you need to learn the hows and whys of how it is structured. Not only should a professional learn SQL, but they should get at least a layperson's knowledge of how the database underneath the SQL works. However, the argument still stands, because when debugging a complex ORM query, you are debugging how it puts SQL together, and then you need to debug what the generated SQL is doing. So you're now multiple layers away from the actual thing you're managing.
Hence the 'final form' of my day-to-day ORM stance: I like to have an ORM around because of the massive automation around entity manipulation, but I think of the ORM in terms of the database and how the database should be structured, rather than as a generic domain model that happens to be mapped to some behind-the-scenes database. Furthermore, I believe it is quite valid to drop down to SQL for the very complicated stuff, especially if you want to trust to the query planning of the database. Once you do, you've broken the total safety of the abstraction, hence my ultimately seeing ORMs as automation tools rather than abstraction interfaces. I have no blame for people who refuse to utilize them, though I'd argue in a design meeting for their use, and hope I wouldn't have to take on all the CRUD queries if I lost the argument, because I'd start writing code to generate them, which then would become a terribly under-engineered faux-ORM :).