>I feel a lot of arguments for ORMs boil down to "I can't really be arsed to learn SQL properly"
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 feel like there should be a distinction between full-blown ORMs and just query builders.
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.
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.