I think ORMs compliment SQL, not replace it. Without ORMs, you end up getting a lot of clunky boilerplate trying to do simple CRUD operations to your data. It's a bit like Greenspun's 10th law: "Any sufficiently advanced RDBMS-backed program contains an ad hoc, informally-specified, bug-ridden, slow implementation of half of an ORM."
But if you're using it to increase productivity for straightforward database calls, it can be a useful tool in a lot of scenarios.
I learned SQL almost 30 years ago and used it in Oracle/Informix/Sybase/IBMDB2/MSSQL/MySQL/SQLite/etc and have written exam questions on SQL for outer-joins, nested subqueries, crosstab, etc.
All that said, concatenating raw strings to form SQL that has no compile time type-checking is tedious and error prone. Thankfully, my "expert" knowledge of SQL does not blind me to the benefits of what a well-written ORM can do for productivity. (E.g. results caching, compile-time validation, auto-completion in the IDE, etc)
The real technical reason why ORMs persist as a desirable feature is the decades-old technical concept of "working memory of field variables" that mirror the disk file table structure.
In the older languages like 1960s COBOL, and later 1980s/1990s 4GL type of business languages such as dBASE, PowerBuilder, SAP ABAP... they all have data tables/records as a 1st-class language syntax without using any quoted strings:
- In COBOL, you had RECORDS and later versions of COBOL had embedded SQL without quoted strings
- dBASE/Foxpro had SCATTER/GATHER which is sort of like a built-in ORM to pull disk-based data from dbf files into memory
- SAP ABAP has built-in SQL (no quoted raw strings required) with no manual typing of variables or structs to pull data into working memory
The issue with "general purpose" languages like Python/Javascript/C++ is that SQL is not 1st-class language syntax to read database data into memory variables and write them back out. The SCATTER/GATHER concept has to be bolted-on as a library ... hence you get the reinvention of of the "db-->memory-->db" data read/write paradigm into Python via projects like SQLAlchemy.
After tediously typing out hundreds of variable names or copy-pasting fields from SQL tables via strings such as ... db.execquery("SELECT cust_id, name, address FROM customer WHERE zipcode=12345;")
... it finally dawns on some programmers that this is mindless repetitive work when you have hundreds of tables. You end up writing a homemade ORM just to reduce the error-prone insanity.
Yes, a lot of ORM projects are bad quality, with terrible performance, bugs, etc. But that it still doesn't change the fact that non-trivial projects with hundreds of tables and thousands of fields need some type of abstraction to reduce the mindless boilerplate of raw SQL strings.
I dislike ORMs that hide database operations from the application code by pretending that in-memory application objects are in concept interchangeable with persistable objects; though just saying that hurts a bit because I don't want to think in terms of objects, but in data.
Query builders are fine if they allow you to build efficient queries in a type-safe and composable manner to get data in and out of the database in the form your application requires at the site of the database query, but I don't want to be forced to pretend I fetch "User" objects if all I really need from the database are the name and e-mail.
Like you say, I find there's way more mental friction dealing with new ORMs and wading through the documentation than there is in just going straight for raw SQL. There's less "magic" that way too which is always nice when you're debugging or optimising things. Different strokes for different folks I guess.