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 don't even mention the 2 main advantages:
- ORM lets you to use plain objects over untyped strings. I take typed anything over untyped anything, everyday
- ORM lets you to use multiple database backends. For ex, you don't need to spawn a local postgres server, then clean/migrate it after each test suit, you can just use in-memory sqlite for that. OK this has some gotchas, but that's a massive improvement in productivity
I too want my query results in an object, but thankfully libraries like sqlx for golang can do this without the extra overhead of an ORM. You give them a select query and they spit out hydrated objects.
As far as multiple DBs go, you can accomplish the same thing as long as you write ANSI standard SQL queries.
I've used ORMs heavily in the past and might still for a quick project or for the "command" side of a CQRS app. But I've seen too much bad performance once people move away from CRUD operations to reports via an ORM.
There seems to be a trend of new (old) developers who find that strong typing is nothing more than a nuisance.
History repeating itself in the IT world. I don't wanna be around to support the systems that inherit these guys.
The SQL generation is great. It means you can quickly get up and running. If the orm is well designed it should perform well for the majority of queries.
The other massive bonus is the object mapping. This can be an absolute pain in the ass. Especially between datasets and classes.
I find SQL to be easy enough to write without needing generation. It is very well documented, and it is very declarative and English-like. More than any ORM, imo.
I don't c#'s EF is brilliant
dbContext.Products.Where(p => p.Price < 50).GroupBy(p => p.Category.Id).ToArray()p.* FROM Products p WHERE p.Price < 50 GROUP BY p.Category_Id``` Meanwhile the ORM is probably generating something stupid and unnecessarily slow like this: ```SELECT p.*, c.* FROM Products p JOIN Category c USING (Category_Id) WHERE p.Price < 50 GROUP BY c.Category_Id``` Now stop using goddamn capital letters in your table and field names. And get off my lawn!
I find ORMs exist best in a mid-sized project, most valuable in a CQRS context.
For anything small, they massively over complicate the architecture. For the large enterprise systems, they always seem to choke on an already large and complex domain.
So a mid size project, maybe with less than a hundred or so data objects works best with an ORM. In that way, they've also been most productive mainly for the CUD of the CRUD approach. I'd rather write my domain logic with the speed and safety of an ORM during writes, but leverage the flexibility and expressiveness of SQL when I'm crafting efficient read queries.
Yeah, that's great, until you need to conditionally compose a query. Suddenly your pre baked queries are not enough. So you either:
- create your own shitty ORM based on string concatenation
- create your own shitty ORM
- or use a well supported ORM, those almost always support query composition and native queries
You write like it's ORM vs native. ORMs let you write native queries and execute them while also doing all the tedious work for you such as:
- mapping results to model objects
- SQL injection safety
- query composition
- connection builders
- transaction management
So if you love native queries write native queries in an ORM which will do all the tedious shit for you.
