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.
You miss the major reason of an orm, abstract vendor specif syntax, i.e. dialect and derived languages such as pl sql, t-sql, etc.
Orm are supposed to allow you to be vendor agnostic
But then you get locked into the ORM’s much more highly specific syntax.
At least the differences across SQL variants are not THAT major from my experience. The core use cases are almost the same.
Yeah, I have my own stuff that lets me do MSSQL, DynamoDB, REST/HATEAOS, regular Hash Maps, and some obscure databases (FilePro).
I throw them in a tree structure and perform depth-first searches for resources. Some of them have stuff for change data capture streaming as well, (eg:
SQLNotifications,DynamoDB Stream,WebSockets).DynamoDB was a rough one to optimize because I have to code to pick the best index. You don’t do that with SQL.
The code on backend is the same as frontend, but a different tree. Frontend queries against REST and a cache layer. Backend queries against anything, REST included.