Welcome to the main community in programming.dev! Feel free to post anything relating to programming here!
Cross posting is strongly encouraged in the instance. If you feel your post or another person's post makes sense in another community cross post into it.
Hope you enjoy the instance!
Rules
Follow the wormhole through a path of communities !webdev@programming.dev
You don't even mention the 2 main advantages:
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...
TL;DR you can't be an expert at every aspect of coding, so I let the big boys handle SQL and don't torture the world with my abysmal SQL code.
I've seen enough bad SQL to claim you're wrong (I write bad SQL myself, so if you write SQL like I do, you're bad at it).
Seriously, the large majority of devs write terrible SQL and don't know how to optimise queries in any way. They just mash together a query with whichever JOIN they learned first. NATURAL JOIN? Sure, don't mind if I do! Might end up being a LEFT JOIN, RIGHT JOIN, or INNER JOIN, but at least I got my data back right?
Off the top of your head, do you know all the joins that exist, when to use which one, and which ones are aliases for another? Do you know how to write optimal JOINs when querying data with multiple relations?
When writing similar queries, do you think most are going to copy-paste something that worked and adapt it? What if you find out that it could be optimised? Then you'll have to search for all queries that look somewhat similar and fix those.
When you create an index for a table, are you going to tell me you are going to read up on the different types each time to make sure you're using the one that makes sense? Postgres has 6, MySQL only has 2 tbf depending on storage engine, but what about other DBs? If you write something for one DB and a client or user wants to host it with another, what will your code look like afterwards?
Others have brought up models in code, so that's already discussed, but what about migrations? Do you think it's time well-spent writing every single migration yourself? I had the distinct pleasure of having to deal with hand-written migrations that were copy-pasted and modified columns that had nothing to do with the changed models, weren't in a transaction, failed half-way through, and tracking down which migration had actually failed. These were seasoned developers who completely forgot to put any migration in transactions. They had to learn the hard way.
ORMs introduce a different API for making SQL queries, with the aim to make it easier.
I wouldn't say that, but instead, that they strive to keep everything contained in one language/stack/deployment workflow, with the benefit of code reusability (for instance, it's completely idiotic, if you ask me, that your models' definition and validation code get duplicated in 3 different application layers (front/API/DB) in as many different languages.
ORMs are not a 100% solution, but do wonders for the first 98% while providing escape hatches for whatever weird case you might encounter, and are overall a net positive in my book. Moreover, while I totally agree that having DB/storage-layer knowledge is super valuable, SQL isn't exactly a flawless language and there's been about 50 years of programming language research since it was invented.
You, my friend, should try EdgeDB. A database and an ORM in one.
When you change the data model, you can get to 100%, which you say is impossible for ORMs
This is a project I am already keeping a close eye on, but I would rather qualify it as a "better SQL" than as an alternative to your typical (framework's) ORM. For instance, it won't morph CRUD operations and data migrations into a language/stack that's native to the rest of the project (and by extension, imply learning another language/stack/set of tools...)
I wish SQL was standardized across vendors.
It is standardized. But some vendors are assholes (or incompetent).
Cough... oracle.
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.
Check out Elixir's Ecto. You basically do write SQL for querying, it's just lightly wrapped in a functional approach.
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?
Yes, I create a dedicated dB in the test phase.
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.
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 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:
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:
So if you love native queries write native queries in an ORM which will do all the tedious shit for you.
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!