Some backend libraries let you write SQL queries as they are and deliver them to the database. They still handle making the connection, pooling, etc.
ORMs introduce a different API for making SQL queries, with the aim to make it easier. But I find them always subpar to SQL, and often times they miss advanced features (and sometimes not even those advanced).
It also means every time I use a ORM, I have to learn this ORM’s API.
SQL is already a high level language abstracting inner workings of the database. So I find the promise of ease of use not to beat SQL. And I don’t like abstracting an already high level abstraction.
Alright, I admit, there are a few advantages:
- if I don’t know SQL and don’t plan on learning it, it is easier to learn a ORM
- if I want better out of the box syntax highlighting (as SQL queries may be interpreted as pure strings)
- if I want to use structures similar to my programming language (classes, functions, etc).
But ultimately I find these benefits far outweighed by the benefits of pure sql.
mapping results to model objects
I agree. If you have a relational database and an object-oriented programming language you’re going to have to map data one way or another.
That being said, using object-oriented doesn’t necessarily mean the data abstraction needs to be objects too. Python is object-oriented yet Pandas is a very popular relational abstraction for it.
SQL injection safety
Parameterized queries are native to the database engine. They’re going to be available regardless what you use on the client side.
(Well, if the database implements them… having flashbacks to back when MySQL didn’t, and it taught a couple of generations of programmers extremely bad “sanitization” practices.)
query composition
Check out the active record pattern. It’s a thin layer over SQL that lets you put together a query programatically (and nothing more).
connection builders
This is very database specific and many ORMs don’t do a great job of it. If anything this is a con for ORMs not a pro.
transaction management
Again, very hit and miss. Each database has particular quirks and you need to know so much about them to use transactions effectively that it negates any insulation that the ORM provides.
