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 [email protected]
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.
Even something as ubiquitous as JSON is not handled in the same way in different databases, same goes for Dates, and UUID. I am not even mentioning migrations scripts. As soon as you start writing raw SQL, I pretty sure you will hit a compatibility issue.
I was specifically talking about python, can't argue with golang. OK you have a valid point for performance, gotta keep an eye on that. However, I am satisfied for our CRUD api
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...
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.
Better than an ORM is to use a query builder. You get the expressiveness of SQL with the safety and convenience of an ORM.
Most developers that use ORMs create poorly performing monstrosities, and most developers who write raw SQL create brittle, unsafe and unmaintainable software. There is a happy medium here.
I also find ORMs and query builders much easier to debug than most mative SQL database queries. Mostly because native SQL error messages tend to be some of the most unhelpful, most undescriptive crap out there, and ORMs help a bit with that.
Seriously, fuck MySQL error messages. 9 times out of 10 shit boils down to "you got some sort of error somewhere roughly over there, go fix".
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.
How many good orm do you have per language? 1? 2? Orm is practically locked once one chooses the language
Surely there's more than 1 ORM that is at least used commonly enough to have a decent community for every major programming language. Just search the web for ORMs in python, JS, and Go and you'll see what I mean.
Not even language choice is forever. I've seen more codebases change languages or frameworks than I have seen changing databases.
What if you change jobs, and now work with a different language or framework? What if you're just helping out a sibling team in your company, and they use something different? Having to relearn a new ORM is annoying when you already know SQL.
I am not basing my argument on any of these things having a high likelihood of changing. The main point to me is that you're abstracting an already high level and very well abstracted API, and the reasons presented don't justify it (abstracting vendors but then locking you into a more specific vendor).
Sure, there are several. But, for instance, Python is pretty much only sqlalchemy. All others are not really common.
At the end with a single framework one can use several backends. That is pretty convient
Sqlalchemy is really nice too, though I haven't used the 2.x series yet. I cannot stand the django ORM after using sqlalchemy.
I'm always curious about this particular feature/argument. From the aspect of "i can unit test easier because the interface is abstracted, so I can test with no database." Great. (though there would be a debate on time saved with tests versus live production efficiency lost on badly formed automatic SQL code)
For anything else, I have to wonder how often applications have actual back-end technologies change to that degree. "How many times in your career did you actually replace MSSQL with Oracle?" Because in 30 years of professional coding for me, it has been never. If you have that big of a change, you are probably changing the core language/version and OS being hosted on, so everything changes.
Some of us have had to support multiple database targets. So I don't know about changing a database in a running application, but a good abstraction has made it easier to extend support and add clients when we could quickly and easily add new database providerz
If you are building software where the customer is the deployer being flexible on what database can be used is a pretty big step. Without it could turn off potential customers that have already existing infastructure.
Well, developing on SQLite and deploying to Postgres is a much more common scenario than migrating your data from one DBMS to another.
Working in a data intensive context, I saw such migrations very often, from and to oracle, ms sql, postgres, sas, exasol, hadoop, parquet, Kafka. Abstraction, even further than orms, is extremely helpful.
Unfortunately in most real case scenarios companies don't value abstraction, because it takes time that cannot be justified in PI plannings and reviews. So people write it as it is quicker, and migrations are complete re write. A lot of money, time and resources wasted to reinvent the wheel.
Truth is that who pays doesn't care, otherwise they'd do it differently. They deserve the waste of money and resources.
On the other hand, now that I think of it, I've never seen a real impacting OS migration. Max os migration I've seen is from centos or suse to rhel... In the field I work on, non unix OSes are always a bad choice anyway
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.
Composable querying/pushdown is nice but transaction management is huge. It's not an easy task to correctly implement a way to share transactions between methods and between repository classes. But the alternative is, your transactions are limited to individual methods (or you don't use them, and you risk leaving your database in an inconsistent state without manual cleanup).
I had a job where we used Spring Boot with JPA on the backend. It was nice because you could just use the ORM methods for basic CRUD functionality. But on the other hand it has this @Query annotation we used for whenever we wanted to write our own queries. Probably against best practice but it worked well enough
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'm also a big fan of raw SQL. Most ORMs are fine for CRUD stuff, but the moment you want to start using the "relational" part of the database (which... that's the whole point) they start to irritate me. They also aren't free - if you're lucky, you pay at comptime (Rust's Diesel) but I think a lot of ORMs do everything at runtime via reflection and the like.
For CRUD stuff, I usually just define some interface(s) that take a query and manually bind/extract struct fields. This definitely wouldn't scale, but it's fine when you only a handful of tables and it keeps the abstraction/performance tradeoff low.
I once had a task stripping a ODM out of a large project, reverting to the native driver, because of its (extremely) poor performance. Also the fun of profiling the project to prove the ODM was to blame. I also empathize with the "supposed to make things simpler, makes them more complicated instead" point you make.
From many experiences, I hate ORM/ODMs and am immediately suspicious of anyone who likes them.
They're nice if they also migrate your db schema. That way you define your schema once and use it both to setup your db and interact with it via code. I do write raw sql for more complex queries, e.g. when there's recursion.
I really like ORMs when they are well designed. With a bad API though, it hurts me to use them over a general query string.
I built a small driver for ArangoDB that just uses AQL behind the scenes because it's so much easier to manage.
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()LINQ looks great with the query syntax:
var productsByCategory =
from p in dbContext.Products
where p.Price < 50
group by p.Category.Id
select p;
Since working with SQLAlchemy a lot (specifically it's SQL compiler, not it's ORM), I don't want to work with SQL any other way. I want to have the possibility to extract column definitions into named variables, reuse queries as columns in other queries, etc. I don't want to concatenate SQL strings ever again.
Having a DSL or even a full language which compiles to SQL is clearly the superior way to work with SQL.
SQLAlchemy is awesome, one of my favorite libraries I found since diving back into python
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.
I wish SQL was standardized across vendors.
It is standardized. But some vendors are assholes (or incompetent).
Cough... oracle.
Hmmm
I worked on one project only which used what I guess is an ORM-like pattern, and I have to say it was actually really nice. The code was Javascript, and there was a mapping:
For each class, there was a big mapping table which indicated which database-backed fields needed to exist in that class, and then there was automated code that (1) could create or update the database to match the specified schema (2) would create helper methods in each class for basic data functions -- the options being "Create me a new non-database-backed object X" "I've set up the new object, insert it into the DB" "give me an iterator of all database-backed objects matching this arbitrary query", "update the appropriate row with the changes I've made to this object", "delete this object from the DB," and "I'm doing something unusual, just run this SQL query".
I honestly really liked it, it made things smooth. Maybe it was the lack of hesitation about dropping back to SQL for stuff where you needed SQL, but I never had issues with it and it seemed to me like it made life pretty straightforward.
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.
Completely agree. Most ORMs focus on hiding SQL away (for good reasons, such as portability and type safety), but I wish there were more approaching it in the reverse. That is, have the user write schemas, queries and migrations in SQL, and generate models with typesafe APIs in return. I'm only aware of SQLDelight in this space, but it's such a great idea to have the source of truth be actual SQL, and a build time generator and validator working alongside you.
I find SQL, especially prepared statements, to be essentially a function call with a string of text containing identifiers that get swapped out, then a bunch of arguments that get swapped in.
Perhaps there are better SQL libraries that make this more fluent.
But that is so close to an ORM, I feel like I might as well use an ORM.
I used to be an ORM-hater, but my experience with Django has changed my mind, somewhat. I still think there are projects where ORM is unnecessary or even harmful, but for some projects, being able to lean on an ORM to create simple queries/updates or to handle DB migrations is a big time saver. And you can always fall back to hand-written SQL when you need to as long as the ORM allows it, which it absolutely should.
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.
I don’t like traditional ORM’s but I really enjoy using jooq. Statically typed queries are great with this library.
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.