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.
I was about to write the same thing. Really the object thing is the whole reason to use ORMs.
Using plain SQL is a compatibility and migration nightmare in medium and bigger sized projects. If anything using plain SQL is just bad software design at least in an OOP context.
Not to mention refactoring support…
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.
In my experience. ORM has its limitations.
You can only depend on ORM upto a point. Beyond that you have to go use Arel (relationship algebra in Ruby), execute prepared SQL statement, trigger and functions.
I use ORM for concise, easier to read and maintainable code. e.g. joining three or more tables in SQL is cumbersum and verbose. Writing related multiple query is too time consuming, etc.
I learnt from relational algebra, SQL, ORM to vendor specific SQL.
I usually use them only for very basic cruds operations. For everything else I just write raw sql.
and how you run your tests? Do you spawn a DB backend for test purposes?
As another option in this case:
I’ve been able to write unit tests for SQL within the database to address testing important business logic that exists in SQL. The test fixtures just become stored (version controlled) database scripts to set needed test data in place in the test DB. Then we still mock over the db call in the code for unit tests as usual.
It’s more effort up front, but I find it much easier to maintain complex DB interactions inside the DB, isolated from the downstream consumer code.
Obviously, there’s an art to knowing when this is needed, or appropriate. I’ve worked for organizations where almost everything important was a performant SQL query. In that org, maintenance got dramatically simpler and the product more reliable when we started writing SQL tests after moving important DB work directly into the DB.
Yes, I create a dedicated dB in the test phase.
Agree 100%. Especially when you’re doing more complicated queries, working with ORM adds so much complexity and obfuscation. In my experience, if you’re doing much of anything outside CRUD, they add more work than they save.
I also tend to doubt their performance claims. Especially when you can easily end up mapping much more data when using a ORM than you need to.
I think ORMs are a great example of people thinking absolutely everything needs to be object oriented. OO is great for a lot of things and I love using it, but there are also places where it creates more problems than it solves.
I wish SQL was standardized across vendors.
It is standardized. But some vendors are assholes (or incompetent).
Cough… oracle.
Check out Elixir’s Ecto. You basically do write SQL for querying, it’s just lightly wrapped in a functional approach.
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!
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.
