> ...(although things like Postgres’ hstore can help)...
Back when this blog post was written, this advice would have been reasonable. Today, I don't know anyone reaching for hstore since the more featureful json support was added.
The big problem is that raw SQL has pretty bad type inference and linting support in most editors. A query builder can still give you a lot of type safety benefits.
ORMs do not inherently build queries. They only provide data transformations between relations (i.e. rows and columns) and objects. Hence the literal name: Object relation mapping.
Sometimes ORMs and query builders are combined into a higher order system, such as what was described by the active record pattern. This might be what you are actually thinking of?
User {
name
friends: List<Friend>
posts: List<Post>
}
Let's say we have a "MappedUser" which is derived from this type by this ORM.
I now do:
user = get_mapped_user()
for post in user.friends[0].friends[0].posts {
...
}
Ignoring "get_mapped_user()" how does our user object work?
What happens when I access `.friends`?
Does it give me an empty list, because I didn't ask for it?
I am not aware of anything that calls itself an ORM which merely does:
user: User = map_from_relational_to_user(query_user())
Not only is it difficult to conceptualise how this operation would ever meaningfully work for any non-trivial query, it's also difficult to see how it would even work for trivial queries.
ORMs, at their core, try to abstract away something like `user.friends[0].friends[0].posts` more or less into some underlying queries against a relational database. The main distinction between them being in the availability and first-class nature of the escape hatches when this operation inevitably becomes slow.
> Does it give me an empty list, because I didn't ask for it?
That depends on the rest of your code. If you are using something like the active record or data mapper pattern then it would reach out and fetch more results. If you don't have such mechanics in place then an empty list is possible. We don't have enough information here to say what happens.
> I am not aware of anything that calls itself an ORM which merely does
When your code merely does that, what do you call it?
> ORMs, at their core, try to abstract away something like `user.friends[0].friends[0].posts` more or less into some underlying queries against a relational database.
Active record/data mapper tries to abstract that. ORMs are a necessary piece of active record/data mapper, but one part of a larger system. You also need things like a query builder. ORM alone is not sufficient.
> into some underlying queries against a relational database.
Relational database? In my experience active record/data mapper, or ORM if that is what you want to call it, is almost always bound to SQL, which itself is not relational.
No, I am asking about your hypothetical "bare bones" "ORM" which explicitly _doens't_ have anything beyond "object mapping".
> When your code merely does that, what do you call it?
Certainly not _object_ mapping. It's something between regular "data mapping" and "completely worthless." If the thing you get out of it is not something representing an object from your object model.
> ORM alone is not sufficient for these patterns.
You are talking about a definition of ORM which is at odds with any definition of ORM that I am personally aware of.
Classical ORMs focus almost entirely on providing proxy objects which represent your object model and which back accesses with additional queries.
> Unlikely. SQL is mentioned in the headline for a reason. Nobody uses relational databases in the real world.
SQL is a query language for relational databases. Unless you have another definition for "SQL" or "relational database" which is at odds with common parlance.
> I understand why you might think a relational database is necessary given that ORM stands for Object Relational Mapping, but as ORM operates on data, not databases, the data can be relational even if the backing database isn't. It simply becomes another mapping step to see them become compatible.
While certainly an ORM maps between an object model and a relational model, the fact that this could be done with something other than a relational database seems completely irrelevant to anything in this discussion.
You seem to be taking the term "Object Relational Mapping" splitting it into its constituent parts, looking at the definitions of those terms, and then assuming that the definition for the whole term is just a simple combination of the individual terms.
This is akin to me claiming that OOP doesn't require a programming language or computers, and can merely involve me buying or otherwise procuring a bunch of things (objects) and then setting them up (programming) in the form of a Rube Goldberg machine in order to perform calculations.
My earlier statements regarding the example code existed to point out that the mere act of taking some relational data and somehow converting it to objects in your object model is not "mapping" in any meaningful sense because the resulting objects would be incomplete, and in some cases would not even be able to be constructed from arbitrary relational data.
The mere act of instantiating a partial object graph from relational data is _not_ "ORM", in the same sense that writing and calling functions is not functional programming.
> I am asking about your hypothetical "bare bones" "ORM"
What does "bare bones ORM" mean? That seems like saying "bare bones sort", but like sort it seems to me like it is either something that happens or something that doesn't happen. You either map objects and relations or you don't. Are you imagining that there is some way to partially map relations and objects but somehow not go all the way? I admittedly cannot picture what that would look like. What would the purpose be?
> SQL is a query language for relational databases.
No. SQL is not for relational databases. This is most obviously observed by the fact that SQL is centred around tables instead of relations. That naming isn't just a marketing gimmick. Tables are technically different from relations. Codd, inventor of the relational model, spent a lot of time writing about why SQL isn't relational if you want a more in-depth technical explanation, but suffice to say that ORMs and SQL are not directly compatible. Although obviously they can work together if you layer in additional functionality. You can make any data shape work with another if you provide some kind of mapping between them.
> I am asking _you_ what _you_ are trying to claim here.
I make no claims about anything "bare bones", so, again, you must clarify what you mean by it before I can do anything with it.
> I am trying to figure out how your described model maps it from partial information...
That's up to the implementation. ORM isn't an algorithm. Is that the source of your confusion?
> it centres around sets of tuples
Whereas SQL does not. You can, of course, map SQL structures into relations, which may be why you see SQL as being relational, but that's true of any database. You can take a document database and map it to sets of tuples too. Calling a document database a relational database because it can be mapped to sets of tuples is a stretch, however. Relational databases are natively relational, not just able to represent relations.
As someone who started their programming journey with SQL, it just feels so odd hearing about learning SQL being presented as an useful option. I get it, it just feels odd. SQL was considered table stakes in the financial IT world - if you said you didn't know SQL, people would look at you funny.
It's very strange too. You can learn something like ~90% of useful SQL in an afternoon. The remainder is stuff that you only really need for extremely performance sensitive operations
> You can learn something like ~90% of useful SQL in an afternoon.
Oh, HELL NO!
It's an ugly little language that one has to come back to and re-learn over and over at different levels of sophistication. Nothing wrong with that, but to suggest it's trivial is a gross mischaracterization.
Most of those are not necessary for 90% of use cases
I'm not taking the piss either
All most people really need to know is table CRUD, row CRUD, and a bit about indices.
For anything more advanced you'll need a DBA, but IMO you unless you are scaling like crazy you will not need much more than that for SQL knowledge. It's really, really not that complex for most use cases
I’m a DBRE, and also happen to like SQL. With that as a disclaimer, I really do not think it’s a difficult language to learn. Learning the intricacies of your RDBMS’ behavior for various functions (like MySQL’s ORDER BY and GROUP BY optimizations) is complicated, but that’s what docs are for.
> I really do not think it’s a difficult language to learn.
Neither do I, but there's huge distances between "spend-an-afternoon-intro-on-it" and "learn-it-well-enough-for-occasional-work" and "learn-it-enough-to-build-serious-databases".
Of course, everyone in HN is "advanced" so what do I know!
I think the hard part is not the syntax itself but the shift in thinking: instead of procedural state manipulation expressing the desired end result in declarative set based relation algebra. I see developers struggling with breaking down complex queries in (inline) views / CTEs, thinking they need parameters, when things can be expressed as a queries on another query. Complaining about the lack of reusability, but not knowing about views.
My first job was at a financial services software company. They put everyone through multiple weeks of training on sql. That experience has been paying dividends for 25 years.
Mine was at a book publisher, so I got the books database example applied in real life lmao. The other part of that job was for a football (as in football, not handegg) magazine, they also had a database containing pretty much all football factoids from the past 100+ years. That one was used to create an annually published football almanac that was just full of match results, player stats, transfers, tables, etc.
It should be table stakes for any SWEs working on backend, but it's not. The DB and the code directly interacting with it are way more important than anything you're going to write on top. I keep ending up in situations where I'm the only SWE in the room who really knows SQL, let alone proper schema design, and I have to speak up or else they're going to build an abomination.
But for a lot of people, the focus there is in the "write on top" layer, because they enjoy it more (I suspect). Constraints etc are tested there.
But this is caused by another shift (I didn't experience this firsthand so bear with me); early databases often had multiple clients, nowadays it's often a 1:1 relationship with one application owning the DB. Which makes putting in constraints in SQL feel clunky.
The biggest casualty of that is probably stored procedures.
> The biggest casualty of that is probably stored procedures.
Not much can beat stored procs when it is dealing with multi-step heave volume stuff. But I don't miss not having to do hacks for logging and debugging compared to the flexibility offered by non-db side.
For pretty much everything else, the poor ability to log and debug makes them a headache to manage. I
You can still make a decent DB with minimal constraints in it. The abominations I'm talking about are like, they use an ORM, they use meaningful fields as PKs that might need to be non-unique later, or "let's make Nodes and Edges tables with json payloads that can be anything."
Back in the 90s when I was in university, SQL (and databases in general) sounded like a boring topic that appealed to people who wanted to go into accounting/finance or some consultancy. I didn't study CS to learn to use an application! So, I took other practical curriculum options like operating systems, compiler writing, and graphics.
Then I went off and did distributed systems and HPC work for a decade or two, and the closest I got to "databases" was when we had to interest with LDAP. But, eventually our R&D contracts shifted and we were mixing with bioinformatics people. Then, we had a need for structured metadata management, and RDBMS seems like the right tool. So I finally had a reason to teach myself SQL, with a range of OLTP and analytics sorts of workloads on PostgreSQL.
I have found the existing ORMs in our Python landscape to be really alien and off-putting. I much prefer using the lower-level DB connector and doing my own SQL query building. We also do a bunch of generic/polymorphic work, defeating the main theses of ORMs. Mostly, our schemas are not known at development time, rather they change dynamically. There is no sense in mapping schema to classes, since a developer would have no contact with such classes. Instead, our code has to do "metaprogramming" about table definitions, keying, and reference patterns at runtime.
That was one of the needs we had during my initial days - dynamic DDLs/DMLs. It was basically a bash + SQL stack which is fairly low level. I remember discovering Perl was installed on the Sun Solaris boxes, learned it and soon everyone jumped on it and boy what a massive step-up from bash that was!
I was working with a "full stack" engineer and needed to do some ad hoc data manipulation so I wrote some SQL inserts and updates. He was like "whoa, I didn't know you could do that with SQL!" I was shocked. Like, how have you been working on projects using databases this long without knowing basic SQL? I still don't think they know about DDL at all.
the N+1 trap and having to incorporate eager loading dictates you need to pretty much understand SQL regardless. applying the object oriented paradigm to relational data created Frankenstein's monster which we unaffectionately refer to as ORMs
I use both SQL and ORMs every day. I've used hibernate since 2004. I've certainly had some difficult times with it; but overall it is a net positive. I find that it generally works well and saves a ton of time as long as I stick to my known patterns.
I'm admittedly an ORM apologist [1], but a few of his points articulated as "deal breakers" aren't that bad imo:
- "the pernicious use of foreign keys [...] links between classes are [...] foreign keys" ==> that just sounds like schema normalization, which is usually a good thing?
- "bending over backwards [...] to generate SQL that runs efficiently" ==> the huge majority of ORM-driven queries are "select * from table where id in ..."; for the queries that are more complicated than that, then yes use SQL! That's allowed!
Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction.
- "dual schema dangers" ==> he's exactly right that database should own the schema definition, but then just codegen the entities from the db schema? That's your singular source of truth, no drift. You can do this with Hibernate, ActiveRecord, Joist, many ORMs.
- "Identities" ==> ironically I think ORMs (that use the unit of work pattern) actually have net-better DX here b/c you can hook up a graph of entities with just references.
I.e. hook up a book to its author w/o knowing their ids yet, which explicitly avoids the annoyance he mentions of doing a partial commit/going to the db to figure out "what value should I INSERT into in the book.author_id column?" (but my author is new) in the middle of your business logic that just wants to "create books".
- transactions ==> agreed that "transactions via annotations" ala JPA/Hibernate are terrible, but afaiu all "internet scale" apps these days do reads outside of transactions, and just use op-locking during the singular flush/commit step to the db.
Disclaimer I am sure I won't change anyone's minds
> Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction
my experience is the exact opposite. People who love and advocate the merits of ORM insist that everything be executed through ORM because it introduces too much complexity for them to blend handwritten SQL with the ORM generated queries
> Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction.
I've always heard a major selling point of ORMs is "You don't have to write the actual SQL anymore"
Because of that, I tend to not trust people who use ORMs to even know how to write queries by hand in the first place
I have seen many ORM enjoyers argue the point about “you can just use SQL!” but I have never once seen an ORM enjoyer allow it, much less do it themselves in an actual codebase. They will time and time again prefer you write 100 lines of Typescript/Python for what could be achieved with 15 lines of SQL.
To make matters worse, most of the time I've successfully argued a project to just use SQL instead of an ORM, what has happened is that people over time built a home rolled ORM in the development language.
And then the 100 lines of JS/Py ends up being way slower than the 15 lines of SQL, plus the SQL part of it is slow, plus you can't even get the SQL query to profile without running the actual thing with prints.
Even the 'worst' of the ORMs (according to the people in these threads) makes this very easy:
users = User.find_by_sql(<<~SQL)
SELECT users.*,
COUNT(posts.id) AS posts_count
FROM users
LEFT JOIN posts ON posts.user_id = users.id
GROUP BY users.id
HAVING COUNT(posts.id) > 10
SQL
users.first.posts_count
# => 17
> the huge majority of ORM-driven queries are "select * from table where id in ..."
From my experience, you are mistaken on that. Those queries mostly come with some joins, either necessary or not to represent the object, and that often could be avoided if the data wasn't mapped into some standard object.
> "bending over backwards [...] to generate SQL that runs efficiently" ==> the huge majority of ORM-driven queries are "select * from table where id in ..."; for the queries that are more complicated than that, then yes use SQL! That's allowed!
This is exactly why I hate ORMs. As I always put it "ORMs make the easy stuff slightly easier, and they make the harder stuff way harder".
If you're just using an OEM for the "select * from table where ID in ...", then you're saving practically nothing by using an ORM - just learn to write SQL, because as you put it, you're going to have to use it anyway for places where it falls over. There are lighter weight options that do basic stuff like transaction management and binding result sets to object properties that are much less of a PITA than ORMs.
In practice I've seen people try to use the ORM features first for places that need complicated SQL (which is a reasonable assumption), only to waste a boatload of time before concluding the ORM makes stuff harder.
> There are lighter weight options that do basic stuff like transaction management and binding result sets to object properties that are much less of a PITA than ORMs.
Query builders like these are my personal favorite from a productivity perspective! The point of a query builder is to dynamically build SQL statements that have many subtle variations (do we want to filter by EmailID or PhoneID here? What about a subquery? Did the caller want all results, or just results where $field=X?). They're basically one level above string templating for SQL generation, and often have niceties around ser/de and transaction management as you mentioned.
Because they are primarily about query generation, it feels _very_ natural to pop off the hood and write raw queries directly when necessary. You can usually use the transaction management and ser/de parts with raw queries, too.
Knex has its own set of problems. Again, SQL is a very powerful, well-known language and there are simpler tools that make it possible to break up and reuse queries.
Years ago I was working on a project that used knex, then I serendipitously discovered slonik through this blog post, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41... (slonik has subsequently had lots of development since then). I decided to rewrite the entire persistence layer from knex to slonik over a long weekend and I'm so happy I did. I liked slonik so much that it was the only time I personally contributed to a programmer through GitHub Sponsors.
Disclaimer I just edited this into my OP comment, but "generating boilerplate INSERTs" is not the main reason I use ORMs -- it's business rule enforcement.
I.e. regardless of how easy it is to write `INSERT authors (...) VALUES (...)`, with an appropriately cute/ergonomic query builder to bind the variables/POJOs ... where does your business logic actually go?
Whenever you insert an author, are you always enforcing the same validation logic? Whenever you update a book, are you always updating the derived fields that need updated?
Getting the business rules right is "the actual hard stuff" imo, and nothing I've seen a query builder help with; it's always left as an exercise to the reader to reinvent their "business logic wrapped around POJOs" adhoc in their codebase.
This is an even worse argument for ORMs. Practically every system I've ever built had data access objects that were responsible for persisting and retrieving data. It's trivially easy to write the business rules plain out in whatever language I'm coding in - why would I want to unnecessarily wrap that in some opaque "rando-QL-invented-by-the-ORM-authors" than just specify it directly in code where I'm saving the object(s).
The number of comments implying that ORMs are required for basic software engineering concepts like proper encapsulation and DRY is baffling.
But this gets to the heart of what I was saying. I'll grant you that ORMs save a little bit of boiler plate up front (but not much - ORMs have plenty of their own boiler plate, just instead of a universally understood language like SQL they have it in their own custom config JSON/yaml/XML), but that is where I spend a teeny fraction of my time coding. Writing "boilerplate" SQL for a decently large project (say 50-100 object types) takes me maybe an extra day in coding time. I have wasted multiples of that time trying to track down a single weird ORM bug, or poorly performing query. Plus, spending that time up front to write my queries is always the least stressful time of the project. What is most stressful is when my site is finally getting a big traffic push, but then something causes the DB to crater and the leaky abstraction of the ORM makes it ten times harder to debug.
> the huge majority of ORM-driven queries are "select * from table where id in ..."; for the queries that are more complicated than that, then yes use SQL! That's allowed!
The issue is, your lowest value queries are always this type, then you get the 10-20 in any code base that are 300x more complex, and they are the ones your end users care about the most.
You end up with a 80/20 principal in the wrong way, it's great at producing queries that represent 20% of the value of your app, and awful for the 80% that define the core value of it.
The second issue is, if these queries are just "select * from table where id in ...", WTF bother with a library to abstract that away in the first place? It's trivially easy to handle this as SQL
The main problem of mixing sql and orm together is that most orms don't provide a way to do raw queries in a type safe manner that plays well with non-raw-sql queries.
ORMs are a horrible fit for OLAP scenarios. I've got a situation where I need to load ~40 tables with a total of 100k+ rows and I need it to happen at user-interactive speeds (less than 10 seconds).
There is nothing that an ORM can do to help with this sort of problem without reaching for the obvious escape hatch of arbitrary command text execution. The ability to map the tables to objects in my programming environment is a distracting clown show for this specific problem. What really matters is understanding the provider and its techniques for bulk loading records. No ORM will ever be able to touch these provider capabilities on their "happy" paths. At best you'll wind up using the ORM and a bunch of provider-specific SQL anyways.
ORMs for schema management is a stronger argument, but only in cases where the codebase/service has complete ownership over each respective database. Any kind of heterogenous workload says that ORM for schema management is a potential nightmare unless you do something like create a project that is only for migrating the schema, at which point I'd argue you could just maintain a source controlled folder of sql/shell scripts.
One nice thing about the rise of ORMs back in the day was it broke the stranglehold our traditional DBAs had on the data tier. I respected them and their skills, but in a product org it was really difficult to have a separate group that refused to participate in planning and wanted to design everything up front, optimize based on their performance assumptions, and then who would argue with devs when we'd need to do pretty normal things like, say, list users in a webapp.
I'm talking about my experience, not generalizing to all DBAs of course. And of course ORMs introduced performance issues, etc.
These are simply tools. The only wrong opinion is to believe that there’s a strict superiority of one over another. However, the content of this and other blogs can help people make informed decisions on when to reach for each tool.
ORMs have their place but they are leaky as hell. RDMSs are very diverse, have different languages, and require different optimisation techniques.
ORMs that try to paper over all the differences fail miserably. They become super complicated and generally produce crap SQL.
ORMs also tend to oversimplify database design. They are just tables with primary keys, right? Who needs indices? Who needs to think about collation? God forbid anyone mentions physical organisation of the data!
Having said this, I do use a very small subset of SQLAlchemy (the bits I understand) in data pipelines.
I’ve never seen any reliable service built on a NoSQL store as a primary data store. If data consistency and not losing customer data important for you, RDBMS are just fine.
Data consistency was solved in Mongo and DynamoDB years ago. CQRS is a better pattern. Read Models out of analytics (relational) data stores are better for dashboards. I stopped being "SQL First" ten years ago and never looked back. Saved clients time, money, and improved maintenance and eased feature additions.
It's sort of about your skills, if you are better at NoSQL then use that. But it doesn't mean that your experience is universal.
Relational databases are incredibly flexible even if you have a NoSQL mindset, you can do data modelling like that in Postgres too with jsonb data types.
Yes and for crud systems relational is fine because you're unlikely to over-complicated your architecture. But when a system starts talking to other systems and its bounded contexts become complex, alternate solutions should be sought.
The problem with "schema change", and I did this for decades, is that it's always a massive blocker. In some companies the data architects had to approve and implement schema changes. You could wait days for that. NoSQL allows you to modify the document surface in mostly non-breaking change ways OR it's easier to version your APIs to handle different document versions.
Simple CRUD: Any data store is fine.
Complex multiple bounded contexts: Choose the appropriate data store for each bounded context accordingly.
My point was no one should be reaching for a relational database or starting with an ERD to build a system. Document behaviors. Model the system. Let the system decide what data storage it requires.
> Document behaviors. Model the system. Let the system decide what data storage it requires.
Counterpoint: force the system to use an RDBMS to store data in properly normalized schema, because it’s the only thing guaranteeing that the data will continue to exist as you expect.
Correct me if I'm wrong, but MongoDB appears to have a 100-msec gap for its journaling behavior [0] by default. This would be akin to setting innodb_flush_log_at_trx_commit [1] = 2, which is not its default.
I also note that in their FAQs [2], they erroneously state:
"MongoDB’s data modeling best practice suggests storing related data together in a single document using a variety of data types, including arrays and embedded documents. So, a lot of the time, ACID is not required as it is a single-document transaction."
Whether or not you're operating on a single document has nothing to do with its ability to meet durability guarantees (or consistency, for that matter).
NoSQL databases make tradeoffs for performance, and making the lives of devs easier in the short term. That's fine, if and only if you accept what you're losing, and document it for others who may not be aware. If at any point you can have your application get a write ack'd and subsequently lose the write, you do not have a durable data store, and you do not have ACID compliance. Whether that's the fault of the DBMS, the operating system (Postgres' fsyncgate), or hardware (drives lying to the OS about the write's durability without the benefit of PLP) is irrelevant – you have to understand the entire chain to make those guarantees, or at the very least, trust your upstream provider to have understood it for you and made the correct decisions.
Much like I can’t take Prisma seriously because they shipped an ORM that couldn’t do JOINs, I can’t take any database seriously that can’t manage ACID. “bUt wE haVe BAsE.” Cool story. Relational databases are some of the oldest and best-tested pieces of software that exist. I trust them more than anything else - if you write it, it is persisted, full stop.
Use it where it fits, and don't use it where it doesn't.
If you don't use an ORM, you'll end up with more boilerplate from mapping code with DTOs. The reason to use an ORM is dirty checking. It's hard to impose this kind of "state" with a relational database. But fundamentally, relational data doesn't fit well with OOP. In the end, you inevitably have to create a layer that absorbs this mismatch. Both approaches have their pros and cons anyway.
Isn't it just a matter of using it where it fits and not using it where it doesn't? I wonder if we really have to frame it as "never use this" or "always use that."
Actually, on second thought, I take it back. "Right tool for the right place" is harder. If you're on a team, it's probably better to just pick one: either don't use it at all, or use it everywhere. Because either way, friction is going to happen. My earlier thinking was too shallow.
The purpose of an orm is not to "stop writing SQL". In order to effectively use a layer abstraction, you must be able to use the layer below the abstraction.
I wonder if the real problem isn't being able to write efficient queries, but that developers struggle to add (yet another) programming language. Just use AWK, just use SQL, just use jq, just use xyz. It's a lot of overhead. I would be OK to lose whatever fractional speed difference to be able to write my queries in a different scripting language. If I ever scaled so much that I needed to shave microseconds off my queries, there are already tons of DBs available, maybe just using a different tool or, even better, compile the DB with(out) different scripting support.
There are rather concrete problems that strictly prevent it from being possible to efficiently map graph (object) database access patterns to a relational database.
Do you mind going more into that? Naively, it seems like prolog/datalog describe graphs pretty well and they're inherently relational. Relational databases have typically just optimized for row-oriented OLTP uses instead of columnar OLAP, but there's nothing inherent preventing one or the other. They're duals of each other.
First, it's useful to define terms. When I talk about an ORM I talk about an ideal ORM which transparently maps ordinary object graph access patterns to relational database queries. These do exist, and they exhibit the OR mismatch problem I describe below. Some ORMs instead expose the OR mismatch and try to make the leaky abstraction a first class citizen. I would prefer not to call these ORMs, but it doesn't matter. Lastly, there are "ORMs" which are just query builders + DTOs, these are just not ORMs, but I think they're a great choice when interacting with SQL. You can accuse me of committing a no-true-Scotsman fallacy, and I can accuse you of moving the "ORM" goalposts.
Relational databases can represent graphs, and graphs naturally have relations, but in your OO language you can make choices about how to traverse an object graph based on external state, and such traversal is incremental and dynamic. Relational databases can have recursive queries, and these can be used to traverse graphs, but the shape of the query has to be known up front. Recursive queries can be dynamic over database state, but not over arbitrary external state. Even assuming some incredibly deeply integrated super-ORM, it's easy to imagine how programs that operate on graphs _and_ can be automatically mapped to an efficient set of relational queries are a limited subset.
This is the fundamental object-relational mismatch. You can use escape hatches, or you can contort your code, but every time you do this, you have to accept that you're no longer "mapping" in the transparent sense that ORMs were supposed to provide.
I think probably the easiest way to get an intuitive sense for the problem is to consider a simple object graph model:
User {
name
friends: List<User>
posts: List<Post>
}
This is a mostly natural way of structuring this data. One natural (albeit contrived) operation might be:
user.friends[0].friends[0].posts
If you had a reason to do this operation, most people wouldn't think twice about it. There's overhead from the indirection, but nobody would think of this as an excruciatingly slow operation if working with native objects.
Now, how do you create an object that is backed by a relational database while still transparently letting you perform object-graph traversals such as the one above? It's easy to see how `User` would need to be an object with a `name` field. Since the data is recursive, you probably don't want to eagerly load all friends and posts, so you'd have proxy objects that make additional queries when you access them.
It's easy to see how this leads to the classic N+1 style issue. You have your user, you load their friends. Maybe your proxy object is smart enough to only load only their first friend. You end up making a bunch of additional queries after the first one to load the user. Especially when your database is on a disk and large, or accessed over the network, you can see how this quickly gets out of hand.
In the object/graph model, the relationships are _internalised_. They're represented _within_ the object. But in the relational model, relationships are external. To "map" from one to the other efficiently, you can't just represent things as objects with some glue, because you keep running into these "look ahead" issues. When you access user.friends or even user.friends[0], your mapper has no way to know what you're going to ask for next.
Of course, one way to solve this would be with deeper integration or a DSL. Let's say you had a query language which can represent the above query, and then you analyse this query to try to map it efficiently to a relational query. Sounds like we've solved the problem? Well kind of, yes. Except we're no longer mapping the object model to the relational model. A given query leaves you with dead objects, you've just del...
I never use ORMs. But slightly before 2014, there was still kind of a reason to use them, getting/setting a whole nested bag of fields at once that you don't care about individually. Json/jsonb now handles that better.
I stopped using ORMs around 2008 because they made the easy problems easier and the hard problems harder. I wanted to just write SQL and exploit all the power the DBMS has to offer instead of fighting with an abstraction layer, so I created Pyranid in 2015 and keep it actively updated.
Yes - the JdbcClient API has a similar feel for sure. If you are using Spring, it is a better choice than Pyranid because it integrates well with the Spring txn plumbing. Outside of Spring, I think Pyranid has a lot of advantages.
I always disliked ActiveRecord, but I figured ORMs don't have to be ActiveRecord. I created this library 14(!) years ago not too long before this article was written https://github.com/iaindooley/PluSQL
The idea is that you like SQL, but it gets repetitive writing joins and accessor code. I had always hoped it would catch on as a pattern: no boilerplate, automatic mapping to objects in your code of any query (whether generated by the ORM or passed in as a raw query) and easy to override/dynamically build bits of the query as you pass the object around.
I'm not sure why people have not hit on the following hybrid architecture that works so well for me.
I make use of table-valued db functions (IMO the most underrated feature of relational DBs) to define virtual relations/tables. I implement a set of CRUD db functions per entity. Then, on the app side, I define (or generate) DTO types representing these virtual relations. Finally, I use a custom ORM I wrote myself, which defines a general and consistent storage API, to talk to the db functions, using the DTO types.
The advantages of this approach are numerous, some include:
- I have full control of the SQL that goes into constructing the virtual table, I can leverage all the goodness of SQL here. I can even define multiple virtual relations per physical table, or read-only relations, etc, all by implementing the appropriate sets of CRUD db functions
- On the ORM side, I have all the goodness of static typing, a consistent API for all CRUD methods, a full fluent query DSL, etc
- Since, unlike tables or views, db functions can be passed arguments, i am able to layey all kinds of goodness on top of the basic CRUD actions, like audit info passing, custom upsert strategies, some level of record-based authorization, etc
But this architecture does require you to know and write SQL. IMO the value of ORMs do not lie in avoiding SQL; it lies in the capability to express consistent SQL at a higher level of abstraction, but you still need to understand your SQL.
262 comments
[ 3.3 ms ] story [ 86.2 ms ] thread2026: people respond with indignance that they should have to learn anything now that there's a shortcut
> ...(although things like Postgres’ hstore can help)...
Back when this blog post was written, this advice would have been reasonable. Today, I don't know anyone reaching for hstore since the more featureful json support was added.
Sometimes ORMs and query builders are combined into a higher order system, such as what was described by the active record pattern. This might be what you are actually thinking of?
I now do:
Ignoring "get_mapped_user()" how does our user object work?What happens when I access `.friends`?
Does it give me an empty list, because I didn't ask for it?
I am not aware of anything that calls itself an ORM which merely does:
Not only is it difficult to conceptualise how this operation would ever meaningfully work for any non-trivial query, it's also difficult to see how it would even work for trivial queries.ORMs, at their core, try to abstract away something like `user.friends[0].friends[0].posts` more or less into some underlying queries against a relational database. The main distinction between them being in the availability and first-class nature of the escape hatches when this operation inevitably becomes slow.
That depends on the rest of your code. If you are using something like the active record or data mapper pattern then it would reach out and fetch more results. If you don't have such mechanics in place then an empty list is possible. We don't have enough information here to say what happens.
> I am not aware of anything that calls itself an ORM which merely does
When your code merely does that, what do you call it?
> ORMs, at their core, try to abstract away something like `user.friends[0].friends[0].posts` more or less into some underlying queries against a relational database.
Active record/data mapper tries to abstract that. ORMs are a necessary piece of active record/data mapper, but one part of a larger system. You also need things like a query builder. ORM alone is not sufficient.
> into some underlying queries against a relational database.
Relational database? In my experience active record/data mapper, or ORM if that is what you want to call it, is almost always bound to SQL, which itself is not relational.
No, I am asking about your hypothetical "bare bones" "ORM" which explicitly _doens't_ have anything beyond "object mapping".
> When your code merely does that, what do you call it?
Certainly not _object_ mapping. It's something between regular "data mapping" and "completely worthless." If the thing you get out of it is not something representing an object from your object model.
> ORM alone is not sufficient for these patterns.
You are talking about a definition of ORM which is at odds with any definition of ORM that I am personally aware of.
Classical ORMs focus almost entirely on providing proxy objects which represent your object model and which back accesses with additional queries.
> Unlikely. SQL is mentioned in the headline for a reason. Nobody uses relational databases in the real world.
SQL is a query language for relational databases. Unless you have another definition for "SQL" or "relational database" which is at odds with common parlance.
> I understand why you might think a relational database is necessary given that ORM stands for Object Relational Mapping, but as ORM operates on data, not databases, the data can be relational even if the backing database isn't. It simply becomes another mapping step to see them become compatible.
While certainly an ORM maps between an object model and a relational model, the fact that this could be done with something other than a relational database seems completely irrelevant to anything in this discussion.
You seem to be taking the term "Object Relational Mapping" splitting it into its constituent parts, looking at the definitions of those terms, and then assuming that the definition for the whole term is just a simple combination of the individual terms.
This is akin to me claiming that OOP doesn't require a programming language or computers, and can merely involve me buying or otherwise procuring a bunch of things (objects) and then setting them up (programming) in the form of a Rube Goldberg machine in order to perform calculations.
My earlier statements regarding the example code existed to point out that the mere act of taking some relational data and somehow converting it to objects in your object model is not "mapping" in any meaningful sense because the resulting objects would be incomplete, and in some cases would not even be able to be constructed from arbitrary relational data.
The mere act of instantiating a partial object graph from relational data is _not_ "ORM", in the same sense that writing and calling functions is not functional programming.
What does "bare bones ORM" mean? That seems like saying "bare bones sort", but like sort it seems to me like it is either something that happens or something that doesn't happen. You either map objects and relations or you don't. Are you imagining that there is some way to partially map relations and objects but somehow not go all the way? I admittedly cannot picture what that would look like. What would the purpose be?
> SQL is a query language for relational databases.
No. SQL is not for relational databases. This is most obviously observed by the fact that SQL is centred around tables instead of relations. That naming isn't just a marketing gimmick. Tables are technically different from relations. Codd, inventor of the relational model, spent a lot of time writing about why SQL isn't relational if you want a more in-depth technical explanation, but suffice to say that ORMs and SQL are not directly compatible. Although obviously they can work together if you layer in additional functionality. You can make any data shape work with another if you provide some kind of mapping between them.
I make no claims about anything "bare bones", so, again, you must clarify what you mean by it before I can do anything with it.
> I am trying to figure out how your described model maps it from partial information...
That's up to the implementation. ORM isn't an algorithm. Is that the source of your confusion?
> it centres around sets of tuples
Whereas SQL does not. You can, of course, map SQL structures into relations, which may be why you see SQL as being relational, but that's true of any database. You can take a document database and map it to sets of tuples too. Calling a document database a relational database because it can be mapped to sets of tuples is a stretch, however. Relational databases are natively relational, not just able to represent relations.
It's an ugly little language that one has to come back to and re-learn over and over at different levels of sophistication. Nothing wrong with that, but to suggest it's trivial is a gross mischaracterization.
Most of those are not necessary for 90% of use cases
I'm not taking the piss either
All most people really need to know is table CRUD, row CRUD, and a bit about indices.
For anything more advanced you'll need a DBA, but IMO you unless you are scaling like crazy you will not need much more than that for SQL knowledge. It's really, really not that complex for most use cases
Of course, everyone in HN is "advanced" so what do I know!
This is more about infrastructure than SQL though. You don't need to know any fancy SQL to do streaming replication or whatever, for instance.
You're correct that being good at Managing Data is a complex domain with a lot of gnarly bits, but I was talking about Writing SQL being fairly easy
But this is caused by another shift (I didn't experience this firsthand so bear with me); early databases often had multiple clients, nowadays it's often a 1:1 relationship with one application owning the DB. Which makes putting in constraints in SQL feel clunky.
The biggest casualty of that is probably stored procedures.
Not much can beat stored procs when it is dealing with multi-step heave volume stuff. But I don't miss not having to do hacks for logging and debugging compared to the flexibility offered by non-db side.
For pretty much everything else, the poor ability to log and debug makes them a headache to manage. I
Then I went off and did distributed systems and HPC work for a decade or two, and the closest I got to "databases" was when we had to interest with LDAP. But, eventually our R&D contracts shifted and we were mixing with bioinformatics people. Then, we had a need for structured metadata management, and RDBMS seems like the right tool. So I finally had a reason to teach myself SQL, with a range of OLTP and analytics sorts of workloads on PostgreSQL.
I have found the existing ORMs in our Python landscape to be really alien and off-putting. I much prefer using the lower-level DB connector and doing my own SQL query building. We also do a bunch of generic/polymorphic work, defeating the main theses of ORMs. Mostly, our schemas are not known at development time, rather they change dynamically. There is no sense in mapping schema to classes, since a developer would have no contact with such classes. Instead, our code has to do "metaprogramming" about table definitions, keying, and reference patterns at runtime.
- "the pernicious use of foreign keys [...] links between classes are [...] foreign keys" ==> that just sounds like schema normalization, which is usually a good thing?
- "bending over backwards [...] to generate SQL that runs efficiently" ==> the huge majority of ORM-driven queries are "select * from table where id in ..."; for the queries that are more complicated than that, then yes use SQL! That's allowed!
Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction.
- "dual schema dangers" ==> he's exactly right that database should own the schema definition, but then just codegen the entities from the db schema? That's your singular source of truth, no drift. You can do this with Hibernate, ActiveRecord, Joist, many ORMs.
- "Identities" ==> ironically I think ORMs (that use the unit of work pattern) actually have net-better DX here b/c you can hook up a graph of entities with just references.
I.e. hook up a book to its author w/o knowing their ids yet, which explicitly avoids the annoyance he mentions of doing a partial commit/going to the db to figure out "what value should I INSERT into in the book.author_id column?" (but my author is new) in the middle of your business logic that just wants to "create books".
- transactions ==> agreed that "transactions via annotations" ala JPA/Hibernate are terrible, but afaiu all "internet scale" apps these days do reads outside of transactions, and just use op-locking during the singular flush/commit step to the db.
Disclaimer I am sure I won't change anyone's minds
[1] https://joist-orm.io/
my experience is the exact opposite. People who love and advocate the merits of ORM insist that everything be executed through ORM because it introduces too much complexity for them to blend handwritten SQL with the ORM generated queries
I've always heard a major selling point of ORMs is "You don't have to write the actual SQL anymore"
Because of that, I tend to not trust people who use ORMs to even know how to write queries by hand in the first place
It's like people can't just let go.
From my experience, you are mistaken on that. Those queries mostly come with some joins, either necessary or not to represent the object, and that often could be avoided if the data wasn't mapped into some standard object.
This is exactly why I hate ORMs. As I always put it "ORMs make the easy stuff slightly easier, and they make the harder stuff way harder".
If you're just using an OEM for the "select * from table where ID in ...", then you're saving practically nothing by using an ORM - just learn to write SQL, because as you put it, you're going to have to use it anyway for places where it falls over. There are lighter weight options that do basic stuff like transaction management and binding result sets to object properties that are much less of a PITA than ORMs.
In practice I've seen people try to use the ORM features first for places that need complicated SQL (which is a reasonable assumption), only to waste a boatload of time before concluding the ORM makes stuff harder.
Query builders like these are my personal favorite from a productivity perspective! The point of a query builder is to dynamically build SQL statements that have many subtle variations (do we want to filter by EmailID or PhoneID here? What about a subquery? Did the caller want all results, or just results where $field=X?). They're basically one level above string templating for SQL generation, and often have niceties around ser/de and transaction management as you mentioned.
Because they are primarily about query generation, it feels _very_ natural to pop off the hood and write raw queries directly when necessary. You can usually use the transaction management and ser/de parts with raw queries, too.
My personal favorite in this field is knex.js.
Years ago I was working on a project that used knex, then I serendipitously discovered slonik through this blog post, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41... (slonik has subsequently had lots of development since then). I decided to rewrite the entire persistence layer from knex to slonik over a long weekend and I'm so happy I did. I liked slonik so much that it was the only time I personally contributed to a programmer through GitHub Sponsors.
I.e. regardless of how easy it is to write `INSERT authors (...) VALUES (...)`, with an appropriately cute/ergonomic query builder to bind the variables/POJOs ... where does your business logic actually go?
Whenever you insert an author, are you always enforcing the same validation logic? Whenever you update a book, are you always updating the derived fields that need updated?
Getting the business rules right is "the actual hard stuff" imo, and nothing I've seen a query builder help with; it's always left as an exercise to the reader to reinvent their "business logic wrapped around POJOs" adhoc in their codebase.
You’re saving hundreds of lines of repetitive boilerplate code. Do you enjoy writing something like
over and over?But this gets to the heart of what I was saying. I'll grant you that ORMs save a little bit of boiler plate up front (but not much - ORMs have plenty of their own boiler plate, just instead of a universally understood language like SQL they have it in their own custom config JSON/yaml/XML), but that is where I spend a teeny fraction of my time coding. Writing "boilerplate" SQL for a decently large project (say 50-100 object types) takes me maybe an extra day in coding time. I have wasted multiples of that time trying to track down a single weird ORM bug, or poorly performing query. Plus, spending that time up front to write my queries is always the least stressful time of the project. What is most stressful is when my site is finally getting a big traffic push, but then something causes the DB to crater and the leaky abstraction of the ORM makes it ten times harder to debug.
The issue is, your lowest value queries are always this type, then you get the 10-20 in any code base that are 300x more complex, and they are the ones your end users care about the most.
You end up with a 80/20 principal in the wrong way, it's great at producing queries that represent 20% of the value of your app, and awful for the 80% that define the core value of it.
There is nothing that an ORM can do to help with this sort of problem without reaching for the obvious escape hatch of arbitrary command text execution. The ability to map the tables to objects in my programming environment is a distracting clown show for this specific problem. What really matters is understanding the provider and its techniques for bulk loading records. No ORM will ever be able to touch these provider capabilities on their "happy" paths. At best you'll wind up using the ORM and a bunch of provider-specific SQL anyways.
ORMs for schema management is a stronger argument, but only in cases where the codebase/service has complete ownership over each respective database. Any kind of heterogenous workload says that ORM for schema management is a potential nightmare unless you do something like create a project that is only for migrating the schema, at which point I'd argue you could just maintain a source controlled folder of sql/shell scripts.
I'm talking about my experience, not generalizing to all DBAs of course. And of course ORMs introduced performance issues, etc.
These are simply tools. The only wrong opinion is to believe that there’s a strict superiority of one over another. However, the content of this and other blogs can help people make informed decisions on when to reach for each tool.
Can anyone that has used ActiveRecord share their opinion?
ORMs that try to paper over all the differences fail miserably. They become super complicated and generally produce crap SQL.
ORMs also tend to oversimplify database design. They are just tables with primary keys, right? Who needs indices? Who needs to think about collation? God forbid anyone mentions physical organisation of the data!
Having said this, I do use a very small subset of SQLAlchemy (the bits I understand) in data pipelines.
NoSQL for operational data storage is more efficient and cost effective.
ORMs were a regression test that exposed unnecessary complexity.
Relational databases are incredibly flexible even if you have a NoSQL mindset, you can do data modelling like that in Postgres too with jsonb data types.
The problem with "schema change", and I did this for decades, is that it's always a massive blocker. In some companies the data architects had to approve and implement schema changes. You could wait days for that. NoSQL allows you to modify the document surface in mostly non-breaking change ways OR it's easier to version your APIs to handle different document versions.
Simple CRUD: Any data store is fine. Complex multiple bounded contexts: Choose the appropriate data store for each bounded context accordingly.
My point was no one should be reaching for a relational database or starting with an ERD to build a system. Document behaviors. Model the system. Let the system decide what data storage it requires.
Counterpoint: force the system to use an RDBMS to store data in properly normalized schema, because it’s the only thing guaranteeing that the data will continue to exist as you expect.
I would challenge you to look at event driven architectures, CQRS, event sourcing, and how to implement and leverage read models.
It will expand your architecture toolkit.
I also note that in their FAQs [2], they erroneously state:
"MongoDB’s data modeling best practice suggests storing related data together in a single document using a variety of data types, including arrays and embedded documents. So, a lot of the time, ACID is not required as it is a single-document transaction."
Whether or not you're operating on a single document has nothing to do with its ability to meet durability guarantees (or consistency, for that matter).
NoSQL databases make tradeoffs for performance, and making the lives of devs easier in the short term. That's fine, if and only if you accept what you're losing, and document it for others who may not be aware. If at any point you can have your application get a write ack'd and subsequently lose the write, you do not have a durable data store, and you do not have ACID compliance. Whether that's the fault of the DBMS, the operating system (Postgres' fsyncgate), or hardware (drives lying to the OS about the write's durability without the benefit of PLP) is irrelevant – you have to understand the entire chain to make those guarantees, or at the very least, trust your upstream provider to have understood it for you and made the correct decisions.
0: https://www.mongodb.com/docs/manual/core/journaling/
1: https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.ht...
2: https://www.mongodb.com/resources/products/capabilities/acid...
Schema management is the single worst part of deployments.
If I build on DynamoDB vs RDS I can save 10x.
If you don't use an ORM, you'll end up with more boilerplate from mapping code with DTOs. The reason to use an ORM is dirty checking. It's hard to impose this kind of "state" with a relational database. But fundamentally, relational data doesn't fit well with OOP. In the end, you inevitably have to create a layer that absorbs this mismatch. Both approaches have their pros and cons anyway.
Isn't it just a matter of using it where it fits and not using it where it doesn't? I wonder if we really have to frame it as "never use this" or "always use that."
Actually, on second thought, I take it back. "Right tool for the right place" is harder. If you're on a team, it's probably better to just pick one: either don't use it at all, or use it everywhere. Because either way, friction is going to happen. My earlier thinking was too shallow.
So I think the ORM debate could be over
postgresql is a beast
A now defunct site discussing why ORM is a poor map.
Relational databases can represent graphs, and graphs naturally have relations, but in your OO language you can make choices about how to traverse an object graph based on external state, and such traversal is incremental and dynamic. Relational databases can have recursive queries, and these can be used to traverse graphs, but the shape of the query has to be known up front. Recursive queries can be dynamic over database state, but not over arbitrary external state. Even assuming some incredibly deeply integrated super-ORM, it's easy to imagine how programs that operate on graphs _and_ can be automatically mapped to an efficient set of relational queries are a limited subset.
This is the fundamental object-relational mismatch. You can use escape hatches, or you can contort your code, but every time you do this, you have to accept that you're no longer "mapping" in the transparent sense that ORMs were supposed to provide.
I think probably the easiest way to get an intuitive sense for the problem is to consider a simple object graph model:
This is a mostly natural way of structuring this data. One natural (albeit contrived) operation might be: If you had a reason to do this operation, most people wouldn't think twice about it. There's overhead from the indirection, but nobody would think of this as an excruciatingly slow operation if working with native objects.Now, how do you create an object that is backed by a relational database while still transparently letting you perform object-graph traversals such as the one above? It's easy to see how `User` would need to be an object with a `name` field. Since the data is recursive, you probably don't want to eagerly load all friends and posts, so you'd have proxy objects that make additional queries when you access them.
It's easy to see how this leads to the classic N+1 style issue. You have your user, you load their friends. Maybe your proxy object is smart enough to only load only their first friend. You end up making a bunch of additional queries after the first one to load the user. Especially when your database is on a disk and large, or accessed over the network, you can see how this quickly gets out of hand.
In the object/graph model, the relationships are _internalised_. They're represented _within_ the object. But in the relational model, relationships are external. To "map" from one to the other efficiently, you can't just represent things as objects with some glue, because you keep running into these "look ahead" issues. When you access user.friends or even user.friends[0], your mapper has no way to know what you're going to ask for next.
Of course, one way to solve this would be with deeper integration or a DSL. Let's say you had a query language which can represent the above query, and then you analyse this query to try to map it efficiently to a relational query. Sounds like we've solved the problem? Well kind of, yes. Except we're no longer mapping the object model to the relational model. A given query leaves you with dead objects, you've just del...
I stopped using ORMs around 2008 because they made the easy problems easier and the hard problems harder. I wanted to just write SQL and exploit all the power the DBMS has to offer instead of fighting with an abstraction layer, so I created Pyranid in 2015 and keep it actively updated.
https://www.danvega.dev/blog/spring-jdbc-client
The idea is that you like SQL, but it gets repetitive writing joins and accessor code. I had always hoped it would catch on as a pattern: no boilerplate, automatic mapping to objects in your code of any query (whether generated by the ORM or passed in as a raw query) and easy to override/dynamically build bits of the query as you pass the object around.
I make use of table-valued db functions (IMO the most underrated feature of relational DBs) to define virtual relations/tables. I implement a set of CRUD db functions per entity. Then, on the app side, I define (or generate) DTO types representing these virtual relations. Finally, I use a custom ORM I wrote myself, which defines a general and consistent storage API, to talk to the db functions, using the DTO types.
The advantages of this approach are numerous, some include:
- I have full control of the SQL that goes into constructing the virtual table, I can leverage all the goodness of SQL here. I can even define multiple virtual relations per physical table, or read-only relations, etc, all by implementing the appropriate sets of CRUD db functions
- On the ORM side, I have all the goodness of static typing, a consistent API for all CRUD methods, a full fluent query DSL, etc
- Since, unlike tables or views, db functions can be passed arguments, i am able to layey all kinds of goodness on top of the basic CRUD actions, like audit info passing, custom upsert strategies, some level of record-based authorization, etc
But this architecture does require you to know and write SQL. IMO the value of ORMs do not lie in avoiding SQL; it lies in the capability to express consistent SQL at a higher level of abstraction, but you still need to understand your SQL.
We do programmers always need a library?
Program the damn thing.