I don't disagree with any of the major gripes people have with orms and I find SQL to be much cleaner in a lot of circumstances.
That being said, if orms didn't force you to explicitly define your domain models about 60% of developers would simply never do it. And you would see differently structured, ad-hoc interfaces defined all over the code base completely entangled with whatever action they are trying to perform.
ORMs being a forcing function for domain modeling is enough benefit for me that it outweighs all of their obvious limitations.
>ORMs being a forcing function for domain modeling is enough benefit for me that it outweighs all of their obvious limitations.
That was a surprising take!
I know only a few ORM's but it seems they end up just adding another layer of DTO objects that are entirely separate from the domain classes anyway. So best case the ORM is just a detour for a good domain model. Worst case it creates a weird database-contaminated domain model that's hellish to maintain.
So I would't say ORMs force domain modeling, or even help. Are you perhaps thinking of a particular stack where the ORM is just one part of it?
It's not that your domain is different, it sounds more like you don't know how to use ORMs. ORMs don't have to manage migrations, they don't have to even write into the database. When dealing with a bad database design, it can be a legitimate tactic to use ORMs in read-only mode and have writes still as hand-rolled SQL. You can do database-first ORMs, as well as code-first, where the database design is king, not the POCO.
> The domain deals with a lot of things that are not in the database.
You can have non-serialized properties. You can even can over-ride serialization/de-serialization of individual properties
> The domain is one of many and deals with just a fraction of what is in the database
You can use different ORMs for different parts of your domain, you could even wrap multiple ORMs in a wrapper repo pattern if you want
> The domain deals with things stored in several databases
As above.
> The database was designed in the 90s and the domain is new
Tons of solutions for this, one easy one is using SQL Views, just ask Claude. The weird thing here is that I've now dealt with this IRL like 5 times and came to the opposite conclusion of you. I found wrapping a bad DB design with an ORM a great first step in fixing it, as the ORM effectively acts as an easy strangler pattern.
> It's not my database so I can't change it
You can still use ORMs, ORMs don't have to manage migrations. Though I feel sorry for you working somewhere you still have a DB guy gatekeeping the database design in 2026.
The point is, every one of your objections are pretty trivially solvable with many mature ORMs, because everyone else had the same problems two decades ago and instead of throwing up their hands and hand-rolling their SQL, the ORM tooling was improved.
You have mixed the posts you are replying to - the domain being different from the database is stipulated here.
I was giving examples of how this typically happens, and the reasons are entirely independent of whether or not an ORM is being used.
I am fully aware that you can handle any mess using an ORM as well, which is why I was surprised at the original claim that ORM's force proper domain models. I haven't observed that so I was genuinely curious.
Separately from that I have to say your suggestions of things to do to force an ORM into the situation are bad ideas. The complexity of custom serialization, various mapping hooks or attributes to bless individual properties will lead to pain and misery down the line.
Just accept the extra layer of DTO's. They're a detour over pure SQL but are at least easy to maintain and hold no surprises. They say there's a special place in hell for people who write SQL triggers and I think people who override ORM serializers are welcome there. ;-)
DTOs are one of the big code smells of a code base that does little but will be full of boilerplate. As soon as you see an automapper or a folder of DTOs you know you're in for some serious pain.
On the plus side you also know you can reduce the codebase by about 75%.
That doesn't sound at all like any ORM I've ever used. I've struggled in the past because The ones I've used are actively hostile to laying out data in the database in a way not proscribed by the ORMs philosophy. Heck of the ORMs I've used, one didn't support parameterized joins and the other didn't support joins at all.
---
It's not usually a DB guy gatekeeping, it's that multiple apps use the same database so layout changes are costly.
Except for the "multiple ORMs" part which is a level above it, it applies to the only one I've used extensively: Django for python. It has standard defaults, but just about everything overridable, and because models are python objects you can add methods or properties for extra data. There's even ways to define your own field types (the "serialization/de-serialization of individual properties"), which a decade ago people were using to provide json fields through libraries long before it was officially supported.
...and Django was like this 15 years ago when I first started using it. The core design hasn't changed, it just sounds like most other ORMs don't really know what they're doing.
>and Django was like this 15 years ago when I first started using it. The core design hasn't changed, it just sounds like most other ORMs don't really know what they're doing.
Django is an opinionated web framework that uses an ORM, not just an ORM.
Django can by all means be a great way to make a web site (I have little experience with it) but if you have a db that is accessed by various systems written in Java, dotnet, erlang or whatever else I suspect the smooth sailing of Django can run into headwinds quickly and the python plumbing you have to deal with then quickly becomes an issue in itself.
> but if you have a db that is accessed by various systems written in Java, dotnet, erlang or whatever else I suspect the smooth sailing of Django can run into headwinds quickly
Only if those systems are constantly adding/removing tables and columns. And adding isn't a problem, Django just ignores what's not specified in the models.
Django does have default table and column names based on the models that it prefers, but all of it is overridable in officially-supported ways. We're using it with mysql databases originally made for VB6 and C++ with inconsistent naming schemes that aren't even close to Django's defaults, that nowadays are also accessed by perl, php, and python. Most of our python uses are daemons that only use the models and none of the rest of the web framework - the models are defined in a common library they all use.
> Worst case it creates a weird database-contaminated domain model that's hellish to maintain.
CQRS is good for this because it forces you into using a different write and read model. My write models are domain objects and my read models are DTOs that feed the UI and via projection I can shape them without issue.
Additionally I think the migration management that most ORMs support are also a good thing. Defined and type-safe forward and backward strategies are helpful in most cases, especially if you'd like to support more than one DBMS.
I personally think that ORMs are good for management and simple CRUD cases, QueryBuilders are good for managing more complex queries while still being secure / type-safe and for everything else a thin database abstraction layer for native SQL queries with parameters / prepared statements is still required especially for performance use cases.
> ORMs are good for management and simple CRUD cases
I for one think that "simple CRUD cases" is bullshit, those applications don't exist. In practice, System-of-Records systems are rare. (and should be, their value are inversely proportional of how many of those you have in your overall system).
Because if it was "just simple CRUD", one would use the database directly? Databases are already capable of handling CRUD and much more with way less implementation bugs.
Even assuming your application "is a system-of-record", how is it giving any more value that directly using a ready-made solution like Oracle REST Data Services, or PostgREST?
No. If you have a simple line-of-business app, writing Django/Rails models is FAR easier than the equivalent SQL.
Even if you think that maintaining your domain model is easier in SQL (it’s not, for most full-stack engineers), the extra capabilities you get from an ActiveRecord framework such as full-stack admin pages, free migrations, etc. win overall.
I can believe that the gap is closing with the “api for your Postgres” frameworks but really, try reaching your frontend developers sql and see if they have a better time than learning Django/Rails.
I wrote my own ORM for exactly this reason. It's far from enterprise grade but it solves for 1) the domain model needs to stay clean and properly normalised and 2) that's not a job that can be distributed across the whole team.
One lesson I've carried for years is that most of the time the client needs denormalised views on the data model. That's the boundary; the server has the clean domain model, and the client works with views on that model. Isn't that exactly what an ORM is for?
I built mine in Dart because I want the server and client to share DTOs. Then I built a visualizer for clientside devs to be able to explore entity relationships (DDD style) and generate a JSON contract. The end result is no REST back and forth, no GraphQL complexity, just everyone in the team focusing on what they're good at.
I think the theme that ORMs are easy to start, but you pay for it with the edge cases, so good devs end up back at SQL does not apply when you're thinking about how to build a platform. Everyone has their strengths and weaknesses. Aligning the team on playing to their strengths was my goal when I reached for yet another ORM as the solution.
> extra capabilities you get from an ActiveRecord framework such as full-stack admin pages
The naive "here's a row-level view of the database records" that things like ActiveRecord/ActiveAdmin give you by default are entirely inappropriate for any line of business administrative interaction. Line of business admin sites should be workflow based and focused on surfacing specific information needed for processes outside of the admin site itself. Non-developer staff should not be expected to interpret the state of rows and relationships among the tables.
As my career progresses, I'm starting to understand just how many developers have trouble comprehending invariants and how they affect system design. If you do not comprehend invariants, then every system is CRUD.
The specific danger of CRUD is that all operations are expressible in it. If your system is CRUD, everything goes. A developer who doesn't understand the system's design might be inclined to assume an application is "just CRUD" and add all sorts of misfeatures to it that violate otherwise constrained states. They will turn the application into CRUD.
All it takes for an application to go from carefully modeled to CRUD is for people to believe it already was just CRUD.
Like, if the simple insert, read, update and delete SQL queries are forbidden then what do you guys do all day?
Are you really doing inserts exclusively based on the data of another table? You never take user input from a website? You never need to just get a list of data according to a query with some filtering?
Honestly I'm not buying it, since the opposite would basically require you to write Hasura style monster queries for pgsql all day.
An invariant is something that must always be true. The most basic example of this is a not-null foreign key, where a value in one table refers to a row in another table and that row in the other table must always exist.
> Are you really doing inserts exclusively based on the data of another table? You never take user input from a website? You never need to just get a list of data according to a query with some filtering?
Agreed. Simple CRUD is something that only shows up in the beginning of the project, everyone was told to use ORM for that purpose, business grow, and you had awkward requirements that require complex ORM features which might exist but requires deep dive into ORM library's corner case, or just straight not possible and makes you bang your head wishing you'd write SQL instead where it would have been obvious what to write.
The only good thing about ORM is the type safety, but I find rust's sqlx or java's jooq to be hitting the sweet spot.
But SQL is low level, eg. you can't dynamically pass in filters and construct statements without knowing what the query will be ahead of time. ORMs have query builders that allow you do dynamically construct SQL statement based on parameters, they allow avoiding N+1 queries by doing joins in memory and much more. It's just not possible with vanilla SQL unless you concatenate strings or have multiple versions of the same query for every situation. A good ORM is an abstraction layer above that gives you more powerful tools, it's like comparing high level OOP code vs machine code.
Query builders are a doddle to write, extremely trivial to debug, and generally far easier overall than having to shoehorn data into a structure that your ORM likes.
The problem is not that ORMs fail to expose every feature of a particular SQL database. The problem is that they encourage you to model your data in a way that is convenient for the ORM, rather than in a way that is correct for the domain.
Any sufficiently powerful ORM eventually has to provide escape hatches into SQL. At that point, the abstraction has failed: the ORM is no longer helping you understand the database, it is getting out of the way so you can use the database properly.
An ORM is a straitjacket. It pushes you toward sub-optimal structures, and those structures deny you access to the most powerful aspects of SQL: relational modelling, constraints, joins, aggregation, views, transactions, and set-based operations.
It seems like you are throwing away the baby with the bathwater. I don't think providing 90% of the structure you need is a failed abstraction. And it just doesn't follow that it is pushing you towards sub-optimal structures, not sure where this conclusion comes from. All ORMs I've seen have ways to describe relations between models, even polymorphic types, aggregates, eager loading (to avoid N+1) etc.
> I don't think providing 90% of the structure you need is a failed abstraction.
It is, when the "10%" is the actual hot queries that your system will use the most?
Code right now "is so cheap". You can provide your favourite LLM with your database schema, and some domain comments, and ask it a query to fetch/update data, and it will generate somewhat sane queries for you. You can then inspect those queries yourself, send them to another LLM or human to review and, when they look OK, ship it.
And when it comes time to debug it, you have, you know, an actual query, not some pseudo-query in a custom DSL. No need to implement runtime telemetry just to try to figure out if the ORM actually made the query you thought it was supposed to do.
Even if you replace ORM generated queries with hand generated queries or LLM generated queries you're still missing a huge chunk of functionality provided by an ORM.
For my projects I would say that the majority of the value an ORM delivers occurs after the query has returned from the database.
But for some reason everyone focuses on query generation as if it were the only feature of an ORM.
If the ORM is capable of validation or integrates with such a component, i personally think that it integrates well for these parts of an APP, where simple datamanagement is required... E.g. adding, editing and deleting DB records, that need forms and validation.
Developing apps in SwiftUI it’s extremely useful to have SwiftData, an ORM that can act like a system of records. For my own use case this is basically metadata for large scale datasets in Parquet or similar.
That said it’s still my most frequent cause of crashes, however I think mostly it’s just because this is simply a hard problem that SQLite just isn’t cut out for (although it did take Apple until macOS 27 to supply a codable decorator grrr).
Ideally databases could evolve to fit OR mapping more closely, which incidentally is what Arrow and Parquet have done to an extent.
I actually spent a majority of the last ten years on simple CRUD cases.
I mean think about it, creating a single row is CRUD, retrieving a row by ID is crud. Retrieving all the rows that belong to a user with pagination is crud, updating a specific row by ID is CRUD, deleting a specific row by ID is CRUD.
You have a settings page? CRUD
User profile? CRUD
Application form with more than 120 input fields, complex tables, split across multiple pages? CRUD
Heck I have a version of that where you get to nest multiple sub application forms into a single application with absurd amounts of nesting and it is still CRUD.
Most cases that aren't CRUD tend to be niche cases.
I mean think about it. An extended search feature that has hundreds of options is still CRUD and tends to work much better with an ORM since the query builder dynamically builds the SQL query for you instead of messing around with a static SQL query with a massive amount of feature flags.
The cases where you don't have CRUD are the rare cases. Things like reporting, batch jobs that process multiple rows at once or reconciliation that tries to find the differences between two databases.
Maybe it's not clear, but the arbitrarily complex application logic obviously is not written in SQL so even if the application is more complex than a straw man CRUD example doesn't mean that the database sees something more complicated than row creation, retrieval, updating and deletion.
I understand you mean “data” model instead? Perhaps for simple cruds, there’s no much point in differentiating between the data model and the domain model. For more complex scenarios, having orm concerns leak into the domain model is not nice
This is exactly what happens in a typical Elixir project even though Ecto is a query builder and not an ORM. People define their domain entities as database tables. The result is, from my latest project, you have user and organisation memberships which are a list of membership records. This is carried throughout the application while it should be a hash map of organisation IDs and membership data, so you can check if a user is a member of an organisation in constant time. Of course keeping ourselves coupled to the database representation is easier than defining a view, for example, which takes care of presenting the data in a useful form for the application.
This is one of the major drawbacks to ORMs imo (though it’s not necessarily the ORMs fault). People think domain and data models are the same but they most definitely are not.
ORMs often end up defining their own domain-specific language which is neither SQL nor the language itself, so they don't really solve that problem except in the simplest cases.
It's easy to read SQL code as long as the person writing the query doesn't resort to "hack" the planner.
Now to make that reusable....That's another beast and most often will defeat the purpose of writing pure SQL in the first place, let's put a OR there, let's call several queries, let's screw the data model.
No, database servers solve that problem. That the unnecessarily COBOL-like SQL ended up being the primary interface to them is simply an unfortunate accident of history.
There have to be some limitations on the language for this to work. For example, there must be no way to specify a neverending loop or to lock something and forget to unlock, an so on.
You shouldn’t use ORM entities as domain models. The domain should not depend on anything from the integration layer (db entities, REST request/ response, etc).
Ideally models are generated from SQL schemas, which you map to domain models.
I’m thinking about what Rails would look like without activemodel and activerecord. Or even just without activerecord, where we had to write the same sql every time we wrote a model but introduce the opportunity for a dev to screw it up. Imagine starting on a legacy code base and all the models had subtle differences in how they query the db. They don’t have the established conventions around _id fields, polymorphism, the nice bits around joins, and instead you have to discover bugs where you did a join but the two models each have a field called “description”…
Wouldn't you consider defining the schema doing the domain modeling?
I think ORMs do too much. I want to control the querying, or, more precisely, I want to control the SQL that goes to the planner. The good ones largely do allow for this, but I can't think of one that has innate support for vendor-specific features.
What I do appreciate is that they handle the boilerplate like managing connections, preparing statements, setting parameter values, and mapping database types back to client types.
> Wouldn't you consider defining the schema doing the domain modeling?
No, because if the schema is the only reference for data models, developers on any sufficiently large team will come up with extremely widely varied queries to access equivalent information. Those are more likely to be incorrect (someone with domain expertise on one set of tables might miss that authoritative data needs to be joined/queried from elsewhere), harder to update when schemas change (more client code changes to alter and test), and more likely to miss performant techniques to query data.
Those can all be addressed with disciplined use of views or common utility SQL snippets or functions, but ORMs also get you to that point without requiring as much ongoing discipline, care, and feeding.
> Those can all be addressed with disciplined use of views...
Totally agree. Views as a data API is the best way to take advantage of the facilities that the database itself offers and guarantees enforces consistency across disparate clients.
> developers on any sufficiently large team will come up with extremely widely varied queries to access equivalent information.
Ah yes, the famous database integration anti-pattern.
> but ORMs also get you to that point without requiring as much ongoing discipline, care, and feeding.
[citation needed]
The fact that you have being practising "database integration" won't suddenly disappear just because you used a ORM. In fact I expect even worse database integration from your average ORM user, as people that uses ORM blindly often don't care (to their own detriment) about "silly issues" like data provenance or persistence mechanical sympathy.
At some point I expect the DBAs of such database integration nightmares will have to start handling stuff like column-level security and row-level security to prevent naive users from shooting themselves in the foot.
> Wouldn't you consider defining the schema doing the domain modeling?
To an extent, yes.
But to the extent that a so-called impedance mismatch exists, this is going to put your domain model on the faraway/difficult side of that impedance mismatch.
And will result in your domain model existing in an (on average) less expressive language which is more difficult to test.
“Doing no domain modelling” is not really an option. It just means the domain model emerges accidentally from ad-hoc interfaces, conditionals, database fields, validation rules, and UI assumptions. Asking an LLM to help with domain modelling isn't ideal, but it's strictly superior to having your model designed by accident, informed primarily by the initial rough draft of your application code.
People have been making these same arguments for decades and at this point I'm convinced they are all based on the same strawman:
That ORM's absolve you from having to learn SQL.
Once you understand that was never actually true to begin with you can treat the ORM as a tool that simply helps you generate repetitive boilerplate queries and hydrates result rows back into objects for you.
Furthermore, if your objects are long lived (e.g. client-side apps) then ORMs offer you helpful features like identity mapping, unit of work, and change tracking/events.
I'm also convinced most of the people poo-pooing on ORMs just haven't worked on problems where these kinds of features are useful. I mean, if you're writing a reporting tool that just queries the database and dumps the result to a table then yeah you might not need an ORM for that. It doesn't mean that ORMs don't solve useful problems for other use cases though.
1. They pretend SQL is standardized, and support a heavily reduced featureset for any given database as a result
2. They leave awkward holes in their abstraction, leading to psychotic behaviors like N+1 and implicit type coercions to helpfully break your indexes silently
3. They make simple queries simple, and hard queries absolutely revolting
4. You end up not wanting to use the objects directly anyways, so you end up with object-object-relation, needing a mapping layer from your database-object to your business-objects, which also defeats most of the benefits from change-tracking
5. The generated SQL is periodically utterly nuts, so you have to review every generated query anyways
6. You probably dont want to actually use any of the OOP mapping features like inheritance in your DB
The correct answer is to use a query builder + database model, enabling most queries to be written with some degree of type-safety, and minimizing the abstraction from SQL itself, and toss out the rest of the featureset
Which ORMs are you basing this comment on exactly?
(1) and (3) are not really problems with an ORM that gets out of your way and lets you drop down to raw SQL when necessary, but still helps you hydrate result rows back to objects (and still provides the associated features I mentioned previously).
(2) and (5) can be interpreted as "your ORM does not absolve you from knowing SQL".
I've never personally run into a situation where doing (4) or (6) were desirable.
> The correct answer is to use a query builder + database model, enabling most queries to be written with some degree of type-safety, and minimizing the abstraction from SQL itself, and toss out the rest of the featureset
If you work on projects where a full featured ORM can be replaced by a simple query builder then cool, but the rest of the feature set is really useful for the projects I work on so why would I toss them out?
I agree completely, the features the parent is mentioning like "identity mapping, unit of work, and change tracking/events" are exactly the things I don't want out of the ORM because that is the leaky abstraction I don't want to constantly be working with and around.
If it was just a query builder we could have a conversation about the benefits of that vs sql and when one beats another. But it is all kinds of other features that are implicitly activated and then conspire to ruin your day when you were trying to solve some other problem. ORMs bring too much baggage by default. So now you have to talk about its relative merits compared to just writing SQL and the merits of always having these other features activated. Which other features? You need to read your full ORM manual because they really vary from one to another.
Your comment comes across like saying "why would I use an impact driver when my screwdriver does everything I need?"
If you don't actually need those features then obviously an ORM will offer less value to you. That doesn't mean ORMs aren't useful tools, they just aren't useful for the problems you work on.
I tend to work on projects where those features are useful and if the ORM didn't provide them out of the box then I would need to build them myself. In other words using a query builder alone does not adequately solve the problems I need to solve.
I have list of issues with SQL. Not composable. Unable to detect query errors at compile time because the schema is only loosely coupled to the code base. And as you yourself point out, SQL is not standardized, which is also terrible and leads to things like Oracle vendor lock in.
And frankly this list hasn't changed in 30 or maybe 40 years now.
And DBA's were so notoriously egregious that Martin Fowler made his "NoDBA" blog post over a decade ago now. And the movement to NoSQL definitely made things worse.
I wish the SQL community would stop treating ORM's like the vietnam paper did 20 years ago, and embrace them for what they are, as a stepping stone, and maybe as a useful tool to help people understand SQL itself.
> as a stepping stone, and maybe as a useful tool to help people understand SQL itself.
But that is not what ORMs are. They teach bad habits that make SQL harder to understand, not easier, because the power of SQL depends on good data modelling.
Perhaps the worst habit is treating the database as subservient to the application code. This assumption comes naturally to many programmers. In most programming contexts, file formats, wire protocols, and internal representations are defined by the code that consumes them. That's fine in some cases.
But in a data-centric application, the relationship should be reversed. Before writing a single line of application code, you should understand the domain model and design a schema that represents it well. The database is not just a persistence layer for objects. It is the system of record, and its structure should reflect the shape, constraints, and relationships of the real-world data. Everything else should be built to conform to it.
I mean there are plenty of projects that don't fit this description, where the database is just a persistence layer for your objects and the database should be subservient to application code.
I find the 1, 2 and 3 to be complete non issues. As in, they dont exist as issues for hibernate. If you feel like particulat query is oh so difficult, you can always use sql for that one part.
4 is in the "like so what" category.
5 - it optimizes alright for average case. You have to optimize in edge cases, but then again, you have to optimize edge cases with pure sql too.
6 - no I dont want much inheritance in db whether i am using pure sql or orm.
> 1. They pretend SQL is standardized, and support a heavily reduced featureset for any given database as a result
EF Core is provider-specific and also exposes provider-specific functionality.
> 4. You end up not wanting to use the objects directly anyways, so you end up with object-object-relation, needing a mapping layer from your database-object to your business-objects, which also defeats most of the benefits from change-tracking
> 5. The generated SQL is periodically utterly nuts, so you have to review every generated query anyways
Not universally true either. You only have to review complex queries. If you're making claims about a specific ORM it would be good to mention it as it's not universal.
> 6. You probably dont want to actually use any of the OOP mapping features like inheritance in your DB
Then don't? Since when is inheritance required for ORM-usage?
I have found a lot of the anti-ORM critiques come from either using a crappy ORM or having not used a good one in the last 5 years.
I think that journey only feels inevitable if you start from the assumption that the application object model is the centre of the system. An alternative journey:
Hmm – I should model the data according to the domain, not according to the shape my application objects happen to want.
Hmm – maybe “related objects” are not things to auto-fetch, but relationships the database engine is already built to handle.
Hmm – now that my schema matches my domain, complex problems can be solved with a few lines of SQL, saving me hundreds of lines of application code.
Hmm – in fact, now I realise that many important operations can be performed without round-tripping the data through application code at all, saving me thousands of lines of application code.
Using an ORM should not exempt you from designing your schema around the domain. If anyone thinks it's the case they're either using the ORM wrong, or using the wrong ORM, or perhaps creating the wrong ORM.
Similarly, designing your schema to match the domain does not necessarily grant you the productivity boons of an ORM.
Having (ab)used Postgres with and without ORM, I've never had a situation where the latter imposed any kind of design decisions on the schema. They're orthogonal concerns. Itself, the choice of using an ORM tends to be motivated by experience with certain requirements in the business logic. I love SQL, but when having to deal with API resources and their various representations, marshaling, validation, options, etc, it's difficult -- and to say the least, impractical -- to stay principled to the "no-ORM" and "raw-SQL" mottos.
ORMs and schema design are not fully orthogonal. A database schema and an ORM model can be conceptually separated, but in practice they influence each other. And even if you are vigilant, knowing that you'll use an ORM will cause you to obsess about things which satisfy the ORM at the expense of good modelling.
If you just want to store and retrieve objects, and then store and retrieve "related" objects, what you want is an object store, not a relational database. You can use an ORM to shoehorn it into a relational database engine, but don't fool yourself into thinking that's the same thing as using a relational database engine properly.
Obsessively cramming tabular data into objects is often unnecessary, and it bloats the code downstream of the database query. It then encourages the bad habit of performing data manipulation in code rather than directly in the database.
"Fetch related objects" is a code smell. If any related data was needed, your original query should have already fetched it.
Problem is that doesn't work nicely in a one-to-many or many-to-many relationship - fetching it in the original query means deduplicating in the application code, or not fetching it and getting related rows afterwards. And that's one of the things ORMs are really good at.
For a video game, simulation, CAD program or other stateful program, loading an object graph can be natural. In that context, an object store, document database, serialized state, or ORM-backed persistence layer may be a reasonable fit. This isn't database design, it's application design.
But in data management applications, the job is to derive specific information from stored facts. For that, SQL is not an implementation detail behind objects; it is the main abstraction. The whole point is to ask for the shape of data your application actually needs, not to arbitrarily hydrate objects and reinvent a bespoke querying engine on the client side. I can barely even remember the number of times that I've ripped an ORM out of a system because the code to interact with hydrated objects had devolved into a single-purpose database engine, a sprawling mess of code, seemingly well organised into objects, but ultimately wasteful.
Often, de-duplicating in code is a perfectly fine solution, and significantly more performant than multiple round-trips. A join that repeats parent columns is not a flaw in SQL. It is only a problem if the application insists on rebuilding a nested object graph instead of asking for the shape of data it actually needs.
If the data returned by the query does not match what you are presenting, the answer should not be to fetch a pile of related objects and interrogate them in memory. It is to use more SQL to further digest the data so that the result set more closely matches what you intend to present to the user.
I've went on a similar journey and did just end up going to back SQL.
Mapping database rows to domain objects really isn't that painful and you only do it once. So not a big deal. LLMs actually make this a non-issue now.
I actually realised I like the separation of domain objects and the data layer. It just makes things easier to think about for me. And it means my data layer is completely abstracted from the domain. Makes it easier to implement different storage/caching strategies.
And I also realised, if your hot path is needing to get related/child objects then you should probably just write an optimised query as a prepared statement or a stored procedure. It's rare that you actually have that many different ways you want to access the data.
They are really useful for speed of development though if something is completely greenfield and you don't know the full picture of how data is going to be accessed.
It's not one or the other, it's both. Sqlalchemy ORM is my favorite, followed sqlc (golang), because they are both there for you for your highs (select * from table order by created at) and your lows ([an inner join followed by 4 left joins with an aggregation function])
Yeah, exactly. I think the best approach is always to know SQL and know the ORM.
Most of the time you’ll be able to simply use the ORM, but every so often you’ll inevitably come up against a situation where a custom query gets the job done better, and you’ll still get the benefits of deserialising to objects that the ORM offers.
> you’ll inevitably come up against a situation where a custom query gets the job done better
In my experience, these are typically best turned into views (or materialized views), because they represent some fundamental relationship or property within the data that’s useful to be able to quickly reference or query directly against. KPI aggregates, for example.
As long as you restrict yourself to an ORM-compatible schema, you are restricting the power of SQL available to you. Learning SQL properly means learning to model your data correctly, and this usually makes ORMs a non-starter.
Without an ORM you have to write a bit more boilerplate code to interact with the database. But by taking advantage of the power of your database engine, you could potentially avoid writing huge amounts of data manipulation logic. In my experience, an ORM is more of a code amplifier than a code simplifier.
Generally speaking if an ORM is a good fit, the thing you're doing probably isn't database development, it's application state management disguised as database development.
Not really. ORMs (memory) and databases (disk) are distant by multiple orders of magnitude performance wise. Skipping the ORM to shave off some cycles is akin to haggling over a few pennies on your thousand dollars bill.
Why not both? ORMs for the simpler CRUD operations, SQL when it gets a little hectic.
The author basically says this in the first paragraph, but the title (and some of the language the author uses) implies that people should just use SQL.
It's a reasonable article pointing out some of the annoyances and problems of ORMs (especially in the Java world, where they tend to be overengineered) but there are still a lot of advantages to them if you are in an OO language and they used in a reasonable way.
You can optimise your schema to suit your application code, or you can optimise your schema to fit your domain model. Doing the former makes your glue code easier. Doing the latter gives you maximum performance and the maximum querying power of your database engine.
You can optimise your schema for the convenience of your application code, or you can optimise it for the truth of your domain model. The former makes glue code easier. The latter gives you stronger constraints, better performance, richer queries, and a database that can answer questions the application code never anticipated.
Overall, seems like it got more nuanced over time - even though it's still broadly in favor of SQL. Favor for ORMs (flagged also as a term that can mean many things to different people) is more in terms of type safety, mapping, migrations, etc. so more a library/utility rather than a framework that fully abstracts away the database.
I generally like ORMs but recognize that they have a lot of problems. The most common problem that I've seen is when an ORM makes it easy to select records in a way that looks efficient but really is not. Strictly speaking, this isn't a failure of the ORM itself -- it's the fault of the developer who is using the ORM and also the developer that didn't catch it in code review. But it's a case where the ORM is making work for everyone and obscuring legibility into the code instead of saving time and providing clarity.
I've written complicated stuff where an ORM isn't appropriate, but if I'm honest, a large fraction of what I've done in my career is just making boring software to automate menial clerical work, and ORMs are good enough for those kinds of projects.
Firmly agree. I wish that ORMs provided two interfaces above raw SQL: a syntactically guaranteed-to-hit-indexes set of functions, and a do-anything set (e.g. MyModel.objects.unrestricted.filter(…)) that you could lint for and audit. An unsung benefit of ORMs is that they have code-level awareness of what queries are likely to be fast, since indexes are usually defined in the ORM. I wish they took more advantage of that.
> Strictly speaking, this isn't a failure of the ORM itself -- it's the fault of the developer
You've got that backwards. If a tool obscures complexity such that a developer using it could be tricked into thinking their efficient-appearing code is actually inefficient, the problem is the tool. A well-designed tool makes inefficiencies explicit. "You're holding it wrong" is not engineering advice.
> ORMs are good enough for those kinds of projects.
It's all good as long as you have properly abstracted it away from your core application. The trouble with some ORM toolkits is that they encourage you to move database logic into the rest of the application and that's when the messes begin. The old school PHP programmers will know well that SQL in raw doesn't automatically mean proper separation of concerns either, but it is more likely to push you in that direction.
I used to love ORMs so much that I built one for Java, in the early 90s, and it was one of the main offerings of a startup that I joined. I have come around 180 degrees. My rethink started when a developer at a Wall Street bank said: having Oracle on my resume is valuable. Having your ORM on my resume is not.
And then there’s the “now you have two problems” dynamic. You not only have to write high-performing queries, but you have to get the ORM to generate that query for you. And sometimes you don’t want objects. And the schema mapping has to track schema changes.
Just write the damned SQL, it’s not that difficult.
ORMs are so incredibly finicky. I still remember using old Linq-to-SQL (not Entity Framework) and I had to write the linq query in the reverse order of what I expected or it created 3 nested subqueries instead of just joining the tables together. That was when I learned to instantly double check every ORM query I wrote.
> Linq-to-SQL (not Entity Framework) and I had to write the linq query in the reverse order
I remember those times! Had to write the LINQ, see what it compiled to, redo, until the query was efficient. Abuse LINQ subtleties in how it generated JOIN predicates since it only supported equality. Something about finding an equivalent way of expressing a query with sub-selects that is also computationally equivalent. All so I can get my efficient SQL without writing SQL. So silly.
I'm totally on board with the idea that ORMs create a variety of inefficiencies, pain points, and make it really easy to create bad queries or querying strategies. But I use them anyways because the convenience of mapping a row to a code object makes writing programs feel fast and simple. And if you know how ORMs can cause problems and how to watch out for them, you can still get a lot of mileage out of them.
That being said, what's the closest alternative that satisfies this - "mapping rows to a code object" - that doesn't suffer the same problems as an ORM? A middle ground between an ORM (like SQLAlchemy, for example) and "your rows are returned as a key/value dictionary where the column names are keys" type approach like Python's DB-API's DictCursor or PHP's mysqli_fetch_assoc. Is there a middle ground here?
The happiest middle ground I've found in .NET has been LinqToDb.
It's more of a Micro ORM, -but- has a Linq DSL, as well as DSLs for lots of DB bits. CTEs, Window functions, Bulk copy, 'treat this in memory collection as an input rowset', certain DB Specific bits... and if you need some special sauce to deal with brownfield jank [0] it's very easy to wire-up custom SQL bits into your queries via attributes if needed.
If you use method syntax rather than linq query syntax, you will have minimal surprises with the SQL generated. Typically if it does generate something I didn't expect, I dig in and what it did was indeed both correct and better than what I was trying to do anyway.
[0] - Fun nasty case I ran into on a brownfield project; 'If this number has a decimal point, it is a direct percentage rate. If the number does NOT have a decimal point, it is the FK to a lookup table that has the percentage rate'
Yes, there is a middle ground. Elixir's Ecto does this well.
Database rows map to structs. But it doesn't try to figure out how to mutate the data for you to keep the struct in sync with the database. All mutations are explicit using changesets (which can also be used for other non-database purposes, like validating user input for an API.)
There is no implicit preloading of data. You have to explicitly preload.
Data is never fetched implicitly. You have to call Repo.all or Repo.one or something.
It has a query DSL that's a thin wrapper over SQL. It's well-designed and I've never had a problem with it.
> Yes, there is a middle ground. Elixir's Ecto does this well.
Ecto is by far the closest thing to a perfect pattern for abstracting over sql that I've ever seen. I WISH other languages would create similar libraries. Its the biggest thing keeping me coming back to elixir for any kind of database project. it just makes sql so ergonomic.
As someone who works with an 8 year old 500k LOC elixir codebase with millions of users, I hate Ecto so much. The thing I hate most is that there are 2 different syntaxes.
But a close second is that it encourages composition in situations where duplication is the right choice. Having your sql query spread across 7 files makes tracking down bugs and performance issues (and fixing them) incredibly difficult.
I mean FWIW Scala has Slick (but then you're dealing with the rest of Scala ecosystem...) and there's Linq2Db on .NET side. Having dealt with all 3 on some level (Slick the least) I would say they are fairly similar in intent (i.e. differences are primarily due to language idioms.)
I'm not sure Slick is still maintained. Recently I depended on https://github.com/com-lihaoyi/scalasql#simpletable-variant-... for small CRUD stuff and liked it quite a bit. These days there's a whole boulevard through the Scala ecosystem that doesn't involve typelevel pedantry and obscure DSLs just for the love of hieroglyphics.
No, there is no middle ground. You can either maintain relations throughout the full application or you can transform them into application-native structures, the latter of which is ORM.
The article seems to be confusing ORM with query builders. Query builders are where you might avoid writing SQL. ORM is a data transformation technique.
> the convenience of mapping a row to a code object makes writing programs feel fast and simple.
Even when you had to do this manually, it was a very minor effort. A one time thing. These days of course any half decent LLM will produce this code without much fanfare. The argument just melts away.
Otherwise, ORMs just layer abstractions on abstractions. You end up with these weird half implied joins resulting in absolutely terrible actual joins happening. Unless you actually understand what you are doing, in which case you could be hammering out those joins manually. And of course the underlying SQL is usually a bit richer than this one size fits all nonsense ORMs do in order to work across sqlite, mysql, postgresql, etc. and pretend that it's all the same.
Another issue with ORMs is the object impedance mismatch where a junior wannabe coder thinks it's all just objects and classes and you end up with these gazillions of completely pointless tables that then necessitate a huge amount of joins. Often the right amount of tables is a lot smaller.
Also, if you aren't querying on it, does it really need its own column? I end up using my databases as document stores quite often. Gets you the best of both worlds. You get to query on nice indexed columns and then you deserialize the big blob of json or whatever into your rich object structure. Simple CRUD for objects shouldn't require a whole lot of engineering. It's only when every little object needs its own little table that shit gets complicated. And another benefit is that this usually results in more stable table structures that don't need a whole lot of database migrations. Getting rid of those removes a lot of needless faff from day to day deployments.
> Even when you had to do this manually, it was a very minor effort. A one time thing.
Maybe if you’re fetching data from a single table… once you start joining across multiple tables and need deduplicate your result rows it gets pretty annoying to do it by hand though.
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. You can absolutely have ORM without query building just as much as you can have query building without ORM.
Sometimes ORMs and query builders are combined into a higher order system, such as what is described by the active record pattern. This might be what you are actually thinking of instead?
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 for these patterns.
> into some underlying queries against a relational database.
Unlikely. SQL is mentioned in the headline for a reason. Nobody uses relational databases in the real world. The only remaining relational database engines that are still maintained really only exist for educational purposes. 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.
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.
Autocomplete is making me lazy. If I don't see what I'm about to type within two or three characters, I feel like the IDE isn't doing its job of helping me. So being able to type `db.Cust` and autocomplete Customers is really nice. I do know SQL, but yes, the language servers usually have a harder time connecting the SQL to my backend code, whatever language it's in, without quite a lot of config fiddling that pretty much obviates any time savings I would have gained from autocomplete.
I'm firmly on the ORM side of things, despite knowing SQL very well -- but your IDE/editor can fix this with bare sql. Try using Jetbrains Datagrip or the DB integration in Idea or one of the other language-specific IDEs.
In my database[0] you get an SDK generated from your schema. Typescript is the default and man, the autocomplete works so well.
I recently added support for SDK generation in Rust and Go, just do `disc codegen —rust` (double dash, my iPad is autocompleting the wrong dash) and you’re good to go.
It's not that different. I'd rather have a different way to do UPSERTs or a different window function here and there [1] than figure out every ORM's join syntax or its sneaky ways to SELECT N+1 me into oblivion.
Best solution: Learn SQL and understand the relational model. Learn data modelling and normalization. Then choose a good ORM which does not get in the way, but saves a bunch of boilerplate code.
An ORM only saves you boilerplate if you’re mapping relationships to objects. And if you’re doing that, you haven’t learned good data modelling and normalisation.
There's a middle ground between ORMs and raw SQL, especially if you're using a strongly typed language. My library Zapatos[1] is one example among several.
That does look like a compelling tool specifically because it isn't really an ORM. It seems more like an ergonomics layer for SQL within that particular language. It looks decent because the database schema remains the source of truth, and the code adapts to it — not the other way around.
I think ORMs mostly exist because most programming languages tend to lack an elegant way to write SQL and interact with results. Somewhat ironically, the much-maligned CFML (aka ColdFusion) got this right decades ago. It made SQL string building trivial, and it provided a native data type for tabular query results.
No other language I'm aware of has this, and it's the missing piece in many modern ecosystems. They do not need an ORM. They need better ergonomics for interacting with databases: a clean way to compose queries, execute them, and work with the result as structured relational data rather than shoehorning it into application objects.
What you do need is some kind of boundary mapping layer so that your application isn't tightly coupled to the database. That might be a an RRM instead, but if you are going to all the trouble of adding an RRM, why not an ORM? What's the difference, really?
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 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 make a decent DB with minimal constraints or stored procs, meant for regular code to use. The abominations I'm talking about are like, they use an ORM, they use meaningful fields as PKs that end up being non-unique later, or they reinvent the type system by making some tables with a json field that can mean completely different things based on some enum column.
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.
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 interact 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!
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.
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.
1. the functional/immutable nature of Elixir makes read and writes much more explicit and there is no need to magically track deep mutations of nested objects to translate them back into UPDATE/INSERT queries
2. Elixirs support for lisp-like macros allows for an ergonomic embedded query languages that is syntax and schema checked, mirrors raw SQL really well and, frees you from string-oriented query building
3. the query builder DSL addresses one of the main weaknesses of SQL query statements not being composable
4. The automatic conversion between JOINed tables (on the DB side) and nested structs (on the Elixir side) is done on the right abstraction level to work reliable and and being explicit enough to generate predictable queries.
What usually happens in my experience is, that a home-grown Data Access Layer usually turns into a bad "ORM light", because materializing results is repetitive and tiring work. You want to abstract it away.
As a .NET developer I think EF Core has made the right call here, by allowing you to write SQL where it's needed and still use its infrastructure for all the tedious work of materializing your results.
Admittedly in 2014, the time the article was written at, I've also felt using OR-Mapper is a dead-end. But in 2026 the world isn't black and white.
I always inevitably want to model something in the domain in a way that is not be supported by EF. So I have to maintain EF DTOs and basically give up on the change tracker.
There are simple "ORM"s that just map classes to tables and columns to attributes. Basically focused on serialization instead of query generation. I find those to be a good balance.
This is one of numerous things Elixir and Phoenix get right with the database layer, which on the surface looks like an ORM but is in fact a set of clever functions that write SQL using Elixir macros, as well as a system for validation and minimal changes to data being passed to said SQL.
I’m surprised more languages don’t copy this because it’s exactly what I want rather than loads of complexity that eventually always breaks down.
> on the surface looks like an ORM but is in fact a set of clever functions that write SQL
Honest question -- what's the difference?
Usually the problems with ORMs stem from the fact that they are exactly clever functions that write SQL. The cleverness abstracts features of SQL that are important for performance and also makes it easy to do things that are bad for performance.
I'm not saying that the ecosystems you mentioned aren't doing something different, I just don't know what it is from how you've described their DB layers.
The thing is that in Ecto, everything is structured around the actual underlying data. Rather than some abstract objects and stuff like that.
query =
from u in User,
where: u.age > 18
Repo.all(query)
And there is no magic (At least very little). For example, if you wanna access something that is in another table, for example, you're on a user and you wanna access their posts in many frameworks, if you try to read their posts, they would be automatically loaded from the database but in Ecto, you need to explicitly preload them. That avoids accidental and n+1 problems because you can plan your queries more. You're not gonna trigger a lot of queries without realizing it.
No ORMs try to make the concept of SQL hidden, in Elixir you will not get very far if you don’t understand the SQL you’re trying to write. So I’d probably say nothing is really being hidden from you - as little magic as possible.
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.
What's the problem with using ORMs for 95% of the cases and using raw SQL only for the remaining 5% where ORM isn't sufficient? One important benefit (aside from writing less code) of ORMs is type checking which is important for maintainability in large complex projects.
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 was against ORMs until I used EF Core in .NET which I really loved. A good ORM is amazing for productivity and when needed you can always write raw SQL.
I don't use .NET anymore but lately I've been happy with Drizzle for TS. It's very performant and expressive. After years it seems that they're finally going to release v1.0 soon.
Personally I would never go back to writing all my queries with SQL, manually mapping the results, etc.
I believe efcore is really well designed and handles the ORM tradeoffs in a very usable and mostly efficient way. And someone would have to pry LINQ out of my cold, dead hands. SQL is fine and I'm glad I know it. But I thank god I almost never have to use it.
The argument that really hits home for me, after 30+ years in this industry, is stored procedures. The “Stored Procedures are Evil” argument to me is an artifact of an industry that promotes treating engineers and infrastructure as entirely interchangeable and anything that gets in the way of that is Evil(tm). But what working at Salesforce in the 2000’s taught me is that you can do really amazing things if you’re willing to invest heavily in understanding your infrastructure and specializing the hell out of it. Of course that created Oracle lock-in for Salesforce, but that lock-in was the result of Oracle having capabilities that simply didn’t exist elsewhere that Salesforce needed to scale. I would argue Google took that same idea and 100X’d it by building the capabilities they needed when they needed them. In the case of stored procedures, I think if you find yourself fetching huge amounts of data and then doing complex manipulation to it that you can’t do with SQL, consider doing it with stored procedures in the engine and greatly simplifying your application. It may just work out!
ON DELETE CASCADE is horrendously unsafe unless you have full understanding of the entire data model - which is unlikely for the average employee within a large organization with a gigantic database. (And it's also rare to be permanently deleting data when working in such a context, so the convenience doesn't matter that much.)
It's in the context of "SQLite as local data storage for an application", and I am absolutely sure that entries in a cross-reference table make no sense anymore when one of the linked objects is gone, or entries in an auxiliary data table when the principal object is gone.
I am not using ON DELETE CASCADE to be clever - the referenced data is genuinely required.
The problem with ORMs is that they look kludgy without language support - which is why Hibernate in Java looks painful, while DotNet's EF looks like magic. I wrote something similar called TinqerJS - https://tinqerjs.org, which is like Entity Framework but for TypeScript.
There's immense value in everything being typed from the API down to the DB queries.
Of course, ORMs are not for all queries in your project, and may not be a good fit for some projects. That goes without saying. The problem with the article is that it's dismissing ORMs by looking at specific implementations.
While I do enjoy the Django ORM, for many queries SQL is just better. It's almost as if it was designed for querying database.
Once you hit a certain level of complexity in your queries, you're better of with SQL. It's not that you can't do the query in the ORMs, but you're then looking at learning their special query language and those are never better nor easier to understand than just SQL. Those ORM query languages certainly aren't transferable across ORMs, but SQL frequently is. If you can query MariaDB with SQL, you can query SQLServer and PostgreSQL. The same can't be said for e.g. Django vs. Hibernate.
For the "give me all the entries, with this one property" ORMs a much quicker and easier to work with. Once you start needing to use subselect, multiple joins, weird ranges or constructing object with data from across tables, I'd rather just write the SQL myself.
And Django makes it ridiculously easy to write those raw queries in SQL directly so it seems like you're getting lots of mileage from the ORM without giving up anything
I write SQL every day, but I cannot get onboard with liking the language. Yes, it is incredible that the language has had such staying power. No, it is not great that such a flawed design has persisted.
I enjoy this article[0] about some of the persistent warts which will seemingly never change.
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.
In my experience, boilerplate sql isn’t even something I would consider a big pain point. The biggest challenge (to me) is explicit data contract enforcement across the stack.
I haven’t personally had third-party ORM frameworks succinctly encourage synchronization and help me build against long-term divergence of data models across the stack. “Make it easy to do the right thing and hard to do the wrong thing” still leaves a lot of room for ‘gotchas’ as apps evolve over time.
Finally, I don’t understand the aversion in learning even a bit of sql. As topics go, it’s a very good (maybe even the best, if I were being provocative) effort:payoff ratio. Not sure I’d call myself a sql expert, but am always pleasantly surprised how much functionality is within reach by knowing even the very basics of sql.
My point of view (after 18 years of programming): DO use frameworks (compile-time checked queries if you can) but skip ORMs that hide/obfuscate SQL completely as it will result in slow queries, extra round-trips, etc
I don't even use frameworks. I want my SQL and my regular code to be as close as possible to make it easy to reason about. Like SQL directly inlined with my JS/Py function. Don't need to mentally translate from some query builder to SQL or deal with some native "model" object it converts into. Have never suffered from a wrong-type bug.
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.
It's not a matter of "fractional speed difference" unless your database has very few entries. OR mismatch problems often like to appear shortly after your database starts to see any real use.
The only performant way to use an ORM is to use escape hatches everywhere. Alternatively, you can use an "ORM", something which calls itself an ORM while only doing superficial data mapping into dynamic or generated native (to your language) data structures. There are a _lot_ of these, most normal people call them query generators.
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 delayed the problem while leaking abstractions. You can add proxies to those but you're now back to square one except you've maybe improved performance a little bit.
I can't tell if you're arguing against SQL or orms. But I take your argument in favor of SQL because that's the native language of all the DBS and the dozens of frameworks and systems on top of them are "just use x...."
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'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.
I think there is no one answer for this.
In some cases pure SQL is better, in other cases, you need higher level constructs to be efficient, consistent and less error prone.
We have lots of experience with ORMs based on dynamic languages (i.e. Objective-C and Ruby) and if not careful, you can indeed go sideways pretty quickly.
Recently, we've been using https://ash-hq.org. It tries to solve the same problems as an ORM, but using a pure functional language (Elixir). You are using structs instead of objects, so it can feel very close to using raw dictionaries/hashes.
It also makes it super easy to drop down to raw sql, while maintaining that struct interface at the top.
While it does take some getting used to (especially coming from a dynamic, OO language), I'm liking this alternative a lot!
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’d go with a balanced view: you need them both for any non-trivial product. I was recently reviewing a PR that renamed a model, I wanted to understand what happened under the hood. Turns out that mariadb had a rename table operation forever ago and that was used by the orm under the hood. So no need to backup the prod table. Just run migrate and be done with it.
PS: I still exported the table before deploying this fyi.
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.
For me I find it's an excellent step up from a plain SQL query builder (with an API such as `select(Foo).join(bar)`) as it lets me both effortlessly perform projections (one can write `(\e -> (e.foo, e.bar) <$> someQuery` to take a query producing rows of `E` and turn it into rows of 2-tuples built from two projected fields.
ORMs are an anti-pattern. What ends up happening on most projects is that, over time, the ORM ends up generating increasingly complex, inefficient SQL queries behind the scenes. Since some of the people who use the ORM don't understand SQL, they don't realize how inefficient their ORM logic is; it looks like a simple operation from their perspective... It's only if you look under the bonet that you realize that the SQL being generated behind the scenes is a monstrosity. Nobody would have dared write this fugly mass of SQL by hand but from the ORM layer, it looks reasonable... Just a few objects joined by dots....
Implying I use an ORM because I don't know SQL... I've reverse engineered embedded databases and written directly to the .dat files on production systems that deal with HIPAA data. I'm pretty sure I know SQL better than most people on HN. I still prefer an ORM.
Why? Because with my ORM, I can code gen faster than you can vibe code. I can build on top of the abstraction layer. The data model in the ORM is the M in MVC. The backend could be a SQL database, a file system, a REST service, that part is irrelevant. The M is the same, regardless of the backing store. View and Controller code still works.
I find most people who are anti-ORM are kinda junior and trying to flex their power to write SQL scripts as if it is impressive. That's why there's always this weird implying that ORM users don't know SQL.
ORMs do have their use but you can easily screw things up. An anecdote from an university: their was a student administration system where students could themselves enroll to classes. simple enough job, one would guess. but there was a catch: at certain times, usually when more than one student logged in, the system predictably crashed.It turned out, that when a student logged in, a join over 13 tables was performed, even classes the student attended years ago where fetched at the login. These joins were clearly from misconfigured hibernate classes, took them some time to reduce the load on the system
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.
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.
No, it's an ORM because it gives you object based iteration over your query (and the ability to use custom classes for those objects, you just don't have to create classes for every single thing if you don't need them).
EDIT: oh wait looks like I never got around to implementing the ability to use custom classes :) this is still in the to do section:
come up with a good "mix in" style to cast the objects returned from the iterator to a new class for implementing custom functionality (that one would normally include as part of the "boilerplate" class)
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 :-)
Edit: in the HN comments, we're debating "the best way to generate SQL", which is fine, but imo it overlooks the biggest value for ORMs: enforcing business invariants.
I.e. yes a simple INSERT is trivial is write, "why have the ORM to that!", but are you going to enforce the same business logic in the 10 places you do `INSERT authors` in your codebase? And if the answer is "I write an single `insertAuthor` abstraction to enforce this" then you're half-way to writing an adhoc half-specified, bug-riddled version of what a reactive ORM like Joist will do for you. [2] :-)
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.
The problem is that "ORM" does a lot of heavy lifting as a term and can mean different things to different people. Like yes, obviously, one needs some sort of SQL -> data structure transition on the boundary (using "object" overfits to OOP!). But that can be extremely light weight. Let people write SQL, have a thin layer to pull the results back out into the appropriate data structures, and move on.
Every good ORM lets you write SQL. Mine for example has a getByQuery and getByWhere as standard methods. An ORM isn't just writing queries for you it's also handling type casting from lang primitives to SQL and back. In 99% of crud rest apis there should be no need to write your own SQL though.
And then the 100 lines of JS/Py ends up being way slower than the manual SQL, plus the autogen'd 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
I’m not sure why you thought I meant code clarity and not performance? It’s clear in all cases the correct SQL query will be more performant.
Confused at what you’re evening trying to say here. Are you suggesting that 100 lines of application layer code is easier to understand than 15 lines of SQL?
1. Because you referred to lines of code as the way to suggest SQL is obvious better, not performance
2. No, my point was that talking about code clarity was a distraction because to talk about lines of code as a determinant of performance is clearly wrong.
3. Tangentially, yes, if some behavior takes 100 lines of general purpose code to express, I would rather read it in the general purpose language than in SQL even if the SQL was fewer lines. It's hard to imagine why this would ever be the case though.
Profile your code sometime; I assure you, with a properly indexed query, the actual query time is insignificant compared to everything else, unless your app is Rust, C, Nim, etc.
The overwhelming majority of OLTP queries I see running on massive prod systems execute in < 1 msec. More time is spent in network RTT than execution, let alone the ORM parsing the result.
> "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.
> 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
I've written/worked on several ORMs from scratch. ORMs are the industry standard. When I see posts like this I simply can't take them seriously. All they are saying is "I won't be a team player" and "I don't actually understand the subject matter". The reality is at a certain scale there's an entire orm team that optimizes everything. But even when there's no team involved there's no way you can write anything more optimized because I'm already at the computational limit of how far something can be optimized.
There's no (good) ORM that doesn't let you simply put your own query in.
I don’t understand this comment because in no way did I express that I’m not the team player. Seems like this is something of a sacred cow for you. Or maybe it’s a language barrier thing, but all I was trying to do was say that as a member of the data platform team, when I recommend handwritten SQL to address specific limitations of an orm, that is the response that I got. Hope this helps.
My reply was talking in general terms about the original post.
You wrote the exact opposite of my opinion here which is why I replied to your specifically:
> 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 believe strongly that good ORMs expose the ability to put your own queries in. But I can't possibly boil down all the reasons for this in one HN comment.
An ORM is not a query writer. It's a way to map SQL primitives to run time primitives in a static deterministic way backed by a suite of unit tests.
If you have a special query you wanna run that has 10 joins, 2 sub queries, and a derived view that's totally fine. No one says you can't. However remember that statistically 99.9% of all queries are not that.
But the speed is irrelevant as long as it's good enough. Notice Laravel's Eloquent at the bottom of the list yet thousands of projects are being built with it regularly.
How can I possibly condense 24 years of deep knowledge in one comment for you?
The tldr is if you're ever concatenating strings in order to build a query you're just doing what the entire job of orm is but rolling your own and chances are you'll end up with a bunch of bugs in how you handle well.... Everything.
I think your tone is a bit combative. You can certainly provide the cliff notes but if you want me to believe you’re at working at computational limits whilst talking to me about string concatenation in web dev backend languages I think the burden of proof is on you.
the amount of vitriol my comment generated was unexpected. i was sharing that my experience was the opposite of the comment I was replying to. So many people have read things into it that simply do not make sense to me, including this one. It wasn’t a flex, it was a statement of experience that was simply a different experience than the post I was replying to asserted as truth. As a senior member of the data team, I interact with developer teams regularly and suggest manual handwritten sql for particular performance edge cases, and I met with the response I mentioned. It’s not me not being the team player, it’s the development team using the ORM that has decided that the level of effort to maintain handwritten and ORM sequel is too much for their team to handle
> All they are saying is "I won't be a team player" and "I don't actually understand the subject matter".
I get the first part, but not the second.
Preferring to use SQL rather than an ORM + SQL is all about understanding the subject matter, which is the data as it exists in the database.
> The tldr is if you're ever concatenating strings in order to build a query you're just doing what the entire job of orm is but rolling your own and chances are you'll end up with a bunch of bugs in how you handle well.... Everything.
Yeah, so basically don't do this, except when you have to, like concatenating placeholders for a variable size IN query.
There's some classes of applications where it's hard to write all the queries because there's all sorts of mix and match stuff happening. Those are pretty much doomed to poor performance if the tables are large, so I would rather not play on those teams. On the bright side, the limit of a small table gets bigger every ram generation, and table scans on nvme aren't so painful either.
We're pointing out the same thing. Someone that uses an ORM knows when they shouldn't use them and I tend to trust that more than someone who simply refuses to use them and ends up recreating an ORM by accident.
> Someone that uses an ORM knows when they shouldn't use them
That's not been my experience. But admittedly, I've usually been brought in when the slow query is killing the database. Then I look at the query that nobody with any subject matter knowledge would have written, come up with an alternate query that will give either the same result or something close enough. Sometimes I have to then dig in and figure out how to make that happen, because the ORM user doesn't always know how to make direct queries.
But it sure did make the easy things easier, as the other poster said.
People focus on the query writing aspect of ORMs too much. That's not that primary reason you use an ORM. It's primary purpose is to hydrate objects in the runtime. If I pull a datetime from SQL there's a lot of value in having a single piece of code handle that datetime the same way across the entire stack. I can unit test that handling once across the entire code base. Very few ORMs are aware of how the data is indexed and yes a lot of people will write code that generates a complex WHERE clause against columns that aren't indexed. But that's an understanding problem. I expect someone who uses an ORM to understand SQL well. Including indexes and fixed length tables. Obviously you are encountering code made by people who don't understand this but the problem isn't the ORM. They would have made that mistake with or without an ORM.
> I expect someone who uses an ORM to understand SQL well.
From experience, I don't. ORMs are usually sold as 'learn this instead of learning SQL'. For many, the ORM creates the tables, alters the tables, and queries the tables; they don't see SQL and they don't know SQL. When that works, it works, but when it falls apart, they have to debug the SQL and the abstraction layer. I'd rather have fewer unnecessary abstraction layers.
> If I pull a datetime from SQL there's a lot of value in having a single piece of code handle that datetime the same way across the entire stack.
There's value there, datetimes are very complex, but the rest of the stuff it comes with obscures the value IMHO.
> Obviously you are encountering code made by people who don't understand this but the problem isn't the ORM. They would have made that mistake with or without an ORM.
It's hard to write the kind of complex queries I've seen by hand, and I like to imagine if you out how to do that, you'll also know why it's slow and not need my help... But the ORM is part of the problem, because when you've written bad queries by hand, and I give you a better query (or sequence of queries), it's easy to apply. When you've done it with an ORM, you may not even know where the query is made.
You can always make the ORM Model based on a view. Sometimes a background job compiling a simple result set table is the appropriate answer.
Almost all ORMs boil down their queries down to a single query handler so it's actually super easy to find the query.
My ORM for example:
*Read paths*
- Models/Factory/Getters/GetAllRecords.php:28 - table(...) when indexField is set.
- Models/Factory/Getters/GetAllRecords.php:31 - allRecords(...).
- Models/Factory/Getters/GetAllRecordsByWhere.php:95 - table(...) when indexField is set.
- Models/Factory/Getters/GetAllRecordsByWhere.php:98 - allRecords(...).
- Models/Factory/Getters/GetRecordByWhere.php:20 - oneRecord(...).
- Models/Factory/Getters/GetByQuery.php:9 - oneRecord(...).
- Models/Factory/Getters/GetAllByQuery.php:9 - allRecords(...).
- Models/Factory/Getters/GetTableByQuery.php:9 - table(...).
- Models/Versioning.php:122 - revision table(...).
- Models/Versioning.php:124 - revision allRecords(...).
*Write paths*
- Models/Events/Save.php:41 - insert on save() for phantom records.
- Models/Events/Save.php:53 - update on save() for existing dirty records.
- Models/Events/Delete.php:18 - delete by primary key.
- Models/Events/Destroy.php:24 - insert history row before destroy for versioned models.
- Models/Versioning.php:180 - insert history row after versioned save.
Error/retry path
- Models/Events/HandleException.php:35 - direct $connection->exec(...) for auto-creating missing tables.
- Models/Events/HandleException.php:43 - direct $connection->query(...) to rerun the failed query after table creation.
All of those eventually bottom out in IO/Database/StorageType.php:119 for non-result queries via PDO exec, or IO/Database/StorageType.php:149 for result queries via PDO query.
I used to profile all my queries in those two methods but with tools like NewRelic there's no need to slow the code down with profiling cruft.
Fair point, both "pro ORM" and "anti ORM" camps are prone to extreme stances.
I definitely don't agree with the "all queries must be executed through the ORM", and think that dogmatic stance has done a lot of damage to the ORM brand. :-/
They don't consider the ORM the second class citizen it actually is: an optional simplified alternative to normal queries, that can be used for the easy cases.
> 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 100x 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
> 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
You're right, that has been another "pro ORM" pitch that has gone awry and, taken to the extreme, is wrong imo.
My nuanced articulation is "you don't have to write the _boilerplate_ SQL for the 90% of just-do-some-CRUD endpoints in your enterprise SaaS application, but you 100% need to 'know SQL' for the last 5-10% of ~reporting/analytics queries that the ORM is going to mess up".
That you somehow have to adapt the results into the same format the ORM uses. And has to adapt the parameters into taking data from the ORM. Or has to split your entire functionality from the ORM so you can actually access the database directly without one part of your code interfering with the others.
The ORMs I've tried tend to produce some pretty specific table structures that are a pain in the ass to work with outside of the ORM, imo
One of the sticking points I've found in the past is if I create a new table outside of the ORM, it doesn't know how to use it. Then if I try to add it to the ORM's model it doesn't use the existing table, it creates a conflict. Annoying stuff like that
> 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.
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.
> ...(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.
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.
It's a bit aside, but what i love about ORM frameworks is that they try to find the universal interface to multiple database backends. For basic CRUD it's nice: test on sqlite deploy wherever.
I agree that "learn SQL" is a necessity, but I'm not sure the article makes a good argument against using ORMs.
ORMs are just a layer of abstraction. Like any abstraction, they make some tradeoffs that can get you into some sticky situations like inefficient queries mentioned in the article.
But, if you understand the tradeoffs, you can use them for what they're good for (standardization & simplification & in-codebase schema definitions & so on) and usually drop down to SQL whenever there's a particularly necessary case.
I have the same response every time I hear this: like 95% of application CRUD plumbing is much better served by an ORM. It gives your application typed versions of your data, lets you work with objects rather than rows, which are almost always more useful, is much easier to read, etc. Then for the 5% of critical/complicated queries: just use SQL there. In fact your ORM almost certainly has an escape hatch for you to do that.
I am no SQL God by any means, but I am quite proficient. Despite my SQL skills, I cannot give up EF Core.
Even when using other languages, I just pine for LINQ/EF Core. It's truly the best ORM in my opinion. Also, even if one does not want to use the LINQ or the Query syntax (I forgot what it was called), the ability to execute SQL is also still a game changer.
As someone who has historically spent a lot of my time with C#, and now spend most of my days writing python… LINQ is typically what I miss most from C#… (obviously aside from static types and compiled binaries).
I thought ORMs are trying to solve the problem of type mapping between SQL and your backend language.
Admittedly, this doesn't end up being great, but it seems hard to solve this well in other ways, as much as I wish I could write SQL and get types for free.
I've been using ORMs since the late-90s with WebObjects (I still have a running product on the internet that uses WebObjects). I've used I don't even know how many other orms. But it's always been a mix of orm and raw sql, so yes learn sql. Especially useful for reporting.
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.
Oh no, this meme again. Of course you should learn SQL. But also, you can use a library to help generate SQL based on classes and objects that you change, so you don't have to repeat yourself. Why don't you use both?
ORMs may be convenient, but only as long as you stay within their limitations. One you surpass those, things get much more complicated and messy. SQL does not have that artificial breaking point.
I don't like the title, it implies that the only reason for using an ORM is not knowing SQL, which is obviously not the case.
Every time I tried to do a project without an ORM, using only raw SQL, I inevitably ran into:
- serialization/deserialization boilerplate. Like, having to manually map values returned by the DB library to object (or named tuple, or structure) properties
- poor code reuse, having multiple very similar queries that have just one small difference
- extra pain in changing DB schema. Adding a field requires to go and manually edit many queries
Anti-ORM crowd never gives a good answer to these issues.
Instead, they push strawman attacks like "oh, you only use ORM, because you can't write raw SQL". I can absolutely assure you that this is not the case. Every time I use an ORM (SQLAlchemy mostly, the one mentioned in the article) I am 100% sure what SQL do I want it to produce and what SQL will a particular ORM invocation produce.
Does my opinion on SQL and ORMs matter anymore? What does Claude think about ORMs an SQL? So far Claude seems to be content with my existing patterns of using JPA/Hibernate. We've been having this conversation since the early 2000s. Will we have it next year? Maybe just for fun... to pretend we are still relevant :(
Something I'd like to see is for someone to finally come to the realisation that the right thing to do is to make the front-end web templating language truly polyglot and support SQL natively, without an ORM wrapper.
For example, the ASP.NET Razor syntax allows HTML and C# code to be interspersed surprisingly freely:
<ul>
@foreach (var user in Model.Users)
{
<li>@user.Name</li>
}
</ul>
Just picture the same kind of thing, but with SQL expressions freely interspersed with the programming language.
Just like how Cargo, NuGet, NPM, etc... can import packages and/or how you can cross-reference projects in build systems, web apps should be able to reference a database schema project directly, importing the SQL definitions without any explicit "mapping". If the SQL changes, the type changes, and the build system picks that up automatically without any additional manual steps.
.NET with EF Core is almost there, and I've seen some half-hearted attempts in various languages over the years, but it's like the industry has an allergy to the concept.
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.
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.
That's important. Because now days it's trivial for LLMs to translate ORM to SQL and vice-versa with ~100% accuracy. I haven't written any raw SQL (only Active Record) in about two years, and the odd time I blunder with AR and create an n+1 I find out about it via error tracking (e.g. Sentry) a few minutes later and fix it. No biggie.
There's also an additional layer of protection in that using AI on the codebase can spot SQL blunders incidentally (i.e. you ask about X, and the AI does X but also says "Not asked, but flagging for your attention: problem with SQL on line 256 etc.."
I don't disagree with any of the major gripes people have with orms and I find SQL to be much cleaner in a lot of circumstances.
That being said, if orms didn't force you to explicitly define your domain models about 60% of developers would simply never do it. And you would see differently structured, ad-hoc interfaces defined all over the code base completely entangled with whatever action they are trying to perform.
ORMs being a forcing function for domain modeling is enough benefit for me that it outweighs all of their obvious limitations.
>ORMs being a forcing function for domain modeling is enough benefit for me that it outweighs all of their obvious limitations.
That was a surprising take!
I know only a few ORM's but it seems they end up just adding another layer of DTO objects that are entirely separate from the domain classes anyway. So best case the ORM is just a detour for a good domain model. Worst case it creates a weird database-contaminated domain model that's hellish to maintain.
So I would't say ORMs force domain modeling, or even help. Are you perhaps thinking of a particular stack where the ORM is just one part of it?
Why is your database so different from your domain?
>Why is your database so different from your domain?
Usually it's due to one of these:
- The domain deals with a lot of things that are not in the database.
- The domain is one of many and deals with just a fraction of what is in the database.
- The domain deals with things stored in several databases.
- The database was designed in the 90s and the domain is new.
- It's not my database so I can't change it.
(Even for greenfield systems I don't think it's generally desirable that the database matches the domain model.)
It's not that your domain is different, it sounds more like you don't know how to use ORMs. ORMs don't have to manage migrations, they don't have to even write into the database. When dealing with a bad database design, it can be a legitimate tactic to use ORMs in read-only mode and have writes still as hand-rolled SQL. You can do database-first ORMs, as well as code-first, where the database design is king, not the POCO.
> The domain deals with a lot of things that are not in the database.
You can have non-serialized properties. You can even can over-ride serialization/de-serialization of individual properties
> The domain is one of many and deals with just a fraction of what is in the database
You can use different ORMs for different parts of your domain, you could even wrap multiple ORMs in a wrapper repo pattern if you want
> The domain deals with things stored in several databases
As above.
> The database was designed in the 90s and the domain is new
Tons of solutions for this, one easy one is using SQL Views, just ask Claude. The weird thing here is that I've now dealt with this IRL like 5 times and came to the opposite conclusion of you. I found wrapping a bad DB design with an ORM a great first step in fixing it, as the ORM effectively acts as an easy strangler pattern.
> It's not my database so I can't change it
You can still use ORMs, ORMs don't have to manage migrations. Though I feel sorry for you working somewhere you still have a DB guy gatekeeping the database design in 2026.
The point is, every one of your objections are pretty trivially solvable with many mature ORMs, because everyone else had the same problems two decades ago and instead of throwing up their hands and hand-rolling their SQL, the ORM tooling was improved.
>It's not that your domain is different
You have mixed the posts you are replying to - the domain being different from the database is stipulated here.
I was giving examples of how this typically happens, and the reasons are entirely independent of whether or not an ORM is being used.
I am fully aware that you can handle any mess using an ORM as well, which is why I was surprised at the original claim that ORM's force proper domain models. I haven't observed that so I was genuinely curious.
Separately from that I have to say your suggestions of things to do to force an ORM into the situation are bad ideas. The complexity of custom serialization, various mapping hooks or attributes to bless individual properties will lead to pain and misery down the line.
Just accept the extra layer of DTO's. They're a detour over pure SQL but are at least easy to maintain and hold no surprises. They say there's a special place in hell for people who write SQL triggers and I think people who override ORM serializers are welcome there. ;-)
No thanks.
DTOs are one of the big code smells of a code base that does little but will be full of boilerplate. As soon as you see an automapper or a folder of DTOs you know you're in for some serious pain.
On the plus side you also know you can reduce the codebase by about 75%.
>DTOs are one of the big code smells of a code base
I actually agree but think serialization overrides are even worse, and the code smell that causes both of them is the ORM. ;-)
That doesn't sound at all like any ORM I've ever used. I've struggled in the past because The ones I've used are actively hostile to laying out data in the database in a way not proscribed by the ORMs philosophy. Heck of the ORMs I've used, one didn't support parameterized joins and the other didn't support joins at all.
---
It's not usually a DB guy gatekeeping, it's that multiple apps use the same database so layout changes are costly.
To be fair, there are a lot of lousy ORMs. Research and test well before adopting.
Except for the "multiple ORMs" part which is a level above it, it applies to the only one I've used extensively: Django for python. It has standard defaults, but just about everything overridable, and because models are python objects you can add methods or properties for extra data. There's even ways to define your own field types (the "serialization/de-serialization of individual properties"), which a decade ago people were using to provide json fields through libraries long before it was officially supported.
...and Django was like this 15 years ago when I first started using it. The core design hasn't changed, it just sounds like most other ORMs don't really know what they're doing.
>and Django was like this 15 years ago when I first started using it. The core design hasn't changed, it just sounds like most other ORMs don't really know what they're doing.
Django is an opinionated web framework that uses an ORM, not just an ORM.
Django can by all means be a great way to make a web site (I have little experience with it) but if you have a db that is accessed by various systems written in Java, dotnet, erlang or whatever else I suspect the smooth sailing of Django can run into headwinds quickly and the python plumbing you have to deal with then quickly becomes an issue in itself.
But I admit it's just a guess.
> but if you have a db that is accessed by various systems written in Java, dotnet, erlang or whatever else I suspect the smooth sailing of Django can run into headwinds quickly
Only if those systems are constantly adding/removing tables and columns. And adding isn't a problem, Django just ignores what's not specified in the models.
Django does have default table and column names based on the models that it prefers, but all of it is overridable in officially-supported ways. We're using it with mysql databases originally made for VB6 and C++ with inconsistent naming schemes that aren't even close to Django's defaults, that nowadays are also accessed by perl, php, and python. Most of our python uses are daemons that only use the models and none of the rest of the web framework - the models are defined in a common library they all use.
Most commonly ime the application domain may only be some small fraction of the database which is designed/optimised for a much broader dataset.
I doubt domain logic would ever be in 2NF. My domain logic certainly doesn’t have pivot tables of IDs for join lookup
> I know only a few ORM's but it seems they end up just adding another layer of DTO objects that are entirely separate from the domain classes anyway.
Entity Framework in particular has come a long way in this regard. Particularly owned & complex entities, value converters, etc.
https://learn.microsoft.com/en-us/ef/core/modeling/
> Worst case it creates a weird database-contaminated domain model that's hellish to maintain.
CQRS is good for this because it forces you into using a different write and read model. My write models are domain objects and my read models are DTOs that feed the UI and via projection I can shape them without issue.
Mh, in NHibernate when properly modelled, your Domain classes usually match the database/tables, so there is no need to have additional DTOs
Additionally I think the migration management that most ORMs support are also a good thing. Defined and type-safe forward and backward strategies are helpful in most cases, especially if you'd like to support more than one DBMS.
I personally think that ORMs are good for management and simple CRUD cases, QueryBuilders are good for managing more complex queries while still being secure / type-safe and for everything else a thin database abstraction layer for native SQL queries with parameters / prepared statements is still required especially for performance use cases.
> ORMs are good for management and simple CRUD cases
I for one think that "simple CRUD cases" is bullshit, those applications don't exist. In practice, System-of-Records systems are rare. (and should be, their value are inversely proportional of how many of those you have in your overall system).
Because if it was "just simple CRUD", one would use the database directly? Databases are already capable of handling CRUD and much more with way less implementation bugs.
Even assuming your application "is a system-of-record", how is it giving any more value that directly using a ready-made solution like Oracle REST Data Services, or PostgREST?
No. If you have a simple line-of-business app, writing Django/Rails models is FAR easier than the equivalent SQL.
Even if you think that maintaining your domain model is easier in SQL (it’s not, for most full-stack engineers), the extra capabilities you get from an ActiveRecord framework such as full-stack admin pages, free migrations, etc. win overall.
I can believe that the gap is closing with the “api for your Postgres” frameworks but really, try reaching your frontend developers sql and see if they have a better time than learning Django/Rails.
I wrote my own ORM for exactly this reason. It's far from enterprise grade but it solves for 1) the domain model needs to stay clean and properly normalised and 2) that's not a job that can be distributed across the whole team.
One lesson I've carried for years is that most of the time the client needs denormalised views on the data model. That's the boundary; the server has the clean domain model, and the client works with views on that model. Isn't that exactly what an ORM is for?
I built mine in Dart because I want the server and client to share DTOs. Then I built a visualizer for clientside devs to be able to explore entity relationships (DDD style) and generate a JSON contract. The end result is no REST back and forth, no GraphQL complexity, just everyone in the team focusing on what they're good at.
I think the theme that ORMs are easy to start, but you pay for it with the edge cases, so good devs end up back at SQL does not apply when you're thinking about how to build a platform. Everyone has their strengths and weaknesses. Aligning the team on playing to their strengths was my goal when I reached for yet another ORM as the solution.
> extra capabilities you get from an ActiveRecord framework such as full-stack admin pages
The naive "here's a row-level view of the database records" that things like ActiveRecord/ActiveAdmin give you by default are entirely inappropriate for any line of business administrative interaction. Line of business admin sites should be workflow based and focused on surfacing specific information needed for processes outside of the admin site itself. Non-developer staff should not be expected to interpret the state of rows and relationships among the tables.
As my career progresses, I'm starting to understand just how many developers have trouble comprehending invariants and how they affect system design. If you do not comprehend invariants, then every system is CRUD.
The specific danger of CRUD is that all operations are expressible in it. If your system is CRUD, everything goes. A developer who doesn't understand the system's design might be inclined to assume an application is "just CRUD" and add all sorts of misfeatures to it that violate otherwise constrained states. They will turn the application into CRUD.
All it takes for an application to go from carefully modeled to CRUD is for people to believe it already was just CRUD.
This comment reads crazy poorly.
Like, if the simple insert, read, update and delete SQL queries are forbidden then what do you guys do all day?
Are you really doing inserts exclusively based on the data of another table? You never take user input from a website? You never need to just get a list of data according to a query with some filtering?
Honestly I'm not buying it, since the opposite would basically require you to write Hasura style monster queries for pgsql all day.
An invariant is something that must always be true. The most basic example of this is a not-null foreign key, where a value in one table refers to a row in another table and that row in the other table must always exist.
> Are you really doing inserts exclusively based on the data of another table? You never take user input from a website? You never need to just get a list of data according to a query with some filtering?
None of these go against what GP said in any way.
Agreed. Simple CRUD is something that only shows up in the beginning of the project, everyone was told to use ORM for that purpose, business grow, and you had awkward requirements that require complex ORM features which might exist but requires deep dive into ORM library's corner case, or just straight not possible and makes you bang your head wishing you'd write SQL instead where it would have been obvious what to write.
The only good thing about ORM is the type safety, but I find rust's sqlx or java's jooq to be hitting the sweet spot.
But SQL is low level, eg. you can't dynamically pass in filters and construct statements without knowing what the query will be ahead of time. ORMs have query builders that allow you do dynamically construct SQL statement based on parameters, they allow avoiding N+1 queries by doing joins in memory and much more. It's just not possible with vanilla SQL unless you concatenate strings or have multiple versions of the same query for every situation. A good ORM is an abstraction layer above that gives you more powerful tools, it's like comparing high level OOP code vs machine code.
Query builders are a doddle to write, extremely trivial to debug, and generally far easier overall than having to shoehorn data into a structure that your ORM likes.
The problem is not that ORMs fail to expose every feature of a particular SQL database. The problem is that they encourage you to model your data in a way that is convenient for the ORM, rather than in a way that is correct for the domain.
Any sufficiently powerful ORM eventually has to provide escape hatches into SQL. At that point, the abstraction has failed: the ORM is no longer helping you understand the database, it is getting out of the way so you can use the database properly.
An ORM is a straitjacket. It pushes you toward sub-optimal structures, and those structures deny you access to the most powerful aspects of SQL: relational modelling, constraints, joins, aggregation, views, transactions, and set-based operations.
It seems like you are throwing away the baby with the bathwater. I don't think providing 90% of the structure you need is a failed abstraction. And it just doesn't follow that it is pushing you towards sub-optimal structures, not sure where this conclusion comes from. All ORMs I've seen have ways to describe relations between models, even polymorphic types, aggregates, eager loading (to avoid N+1) etc.
> I don't think providing 90% of the structure you need is a failed abstraction.
It is, when the "10%" is the actual hot queries that your system will use the most?
Code right now "is so cheap". You can provide your favourite LLM with your database schema, and some domain comments, and ask it a query to fetch/update data, and it will generate somewhat sane queries for you. You can then inspect those queries yourself, send them to another LLM or human to review and, when they look OK, ship it.
And when it comes time to debug it, you have, you know, an actual query, not some pseudo-query in a custom DSL. No need to implement runtime telemetry just to try to figure out if the ORM actually made the query you thought it was supposed to do.
Even if you replace ORM generated queries with hand generated queries or LLM generated queries you're still missing a huge chunk of functionality provided by an ORM.
For my projects I would say that the majority of the value an ORM delivers occurs after the query has returned from the database.
But for some reason everyone focuses on query generation as if it were the only feature of an ORM.
[flagged]
If the ORM is capable of validation or integrates with such a component, i personally think that it integrates well for these parts of an APP, where simple datamanagement is required... E.g. adding, editing and deleting DB records, that need forms and validation.
Developing apps in SwiftUI it’s extremely useful to have SwiftData, an ORM that can act like a system of records. For my own use case this is basically metadata for large scale datasets in Parquet or similar.
That said it’s still my most frequent cause of crashes, however I think mostly it’s just because this is simply a hard problem that SQLite just isn’t cut out for (although it did take Apple until macOS 27 to supply a codable decorator grrr).
Ideally databases could evolve to fit OR mapping more closely, which incidentally is what Arrow and Parquet have done to an extent.
I actually spent a majority of the last ten years on simple CRUD cases.
I mean think about it, creating a single row is CRUD, retrieving a row by ID is crud. Retrieving all the rows that belong to a user with pagination is crud, updating a specific row by ID is CRUD, deleting a specific row by ID is CRUD.
You have a settings page? CRUD
User profile? CRUD
Application form with more than 120 input fields, complex tables, split across multiple pages? CRUD
Heck I have a version of that where you get to nest multiple sub application forms into a single application with absurd amounts of nesting and it is still CRUD.
Most cases that aren't CRUD tend to be niche cases.
I mean think about it. An extended search feature that has hundreds of options is still CRUD and tends to work much better with an ORM since the query builder dynamically builds the SQL query for you instead of messing around with a static SQL query with a massive amount of feature flags.
The cases where you don't have CRUD are the rare cases. Things like reporting, batch jobs that process multiple rows at once or reconciliation that tries to find the differences between two databases.
Maybe it's not clear, but the arbitrarily complex application logic obviously is not written in SQL so even if the application is more complex than a straw man CRUD example doesn't mean that the database sees something more complicated than row creation, retrieval, updating and deletion.
I understand you mean “data” model instead? Perhaps for simple cruds, there’s no much point in differentiating between the data model and the domain model. For more complex scenarios, having orm concerns leak into the domain model is not nice
This is exactly what happens in a typical Elixir project even though Ecto is a query builder and not an ORM. People define their domain entities as database tables. The result is, from my latest project, you have user and organisation memberships which are a list of membership records. This is carried throughout the application while it should be a hash map of organisation IDs and membership data, so you can check if a user is a member of an organisation in constant time. Of course keeping ourselves coupled to the database representation is easier than defining a view, for example, which takes care of presenting the data in a useful form for the application.
That’s a problem with ecto and not ORMs. A good ORM will be able to do that hashmap mapping.
It’s a problem of philosophy, not tooling.
This is one of the major drawbacks to ORMs imo (though it’s not necessarily the ORMs fault). People think domain and data models are the same but they most definitely are not.
SQL is pretty shitty language to write modular, reusable and easy to read code.
ORMs often end up defining their own domain-specific language which is neither SQL nor the language itself, so they don't really solve that problem except in the simplest cases.
With the big exception of Entity Framework which relies on LINQ.
It's easy to read SQL code as long as the person writing the query doesn't resort to "hack" the planner. Now to make that reusable....That's another beast and most often will defeat the purpose of writing pure SQL in the first place, let's put a OR there, let's call several queries, let's screw the data model.
It solves a hard problem. For example, it completely insulates the sender from the fact that his transaction is just one among many others.
No, database servers solve that problem. That the unnecessarily COBOL-like SQL ended up being the primary interface to them is simply an unfortunate accident of history.
There have to be some limitations on the language for this to work. For example, there must be no way to specify a neverending loop or to lock something and forget to unlock, an so on.
You shouldn’t use ORM entities as domain models. The domain should not depend on anything from the integration layer (db entities, REST request/ response, etc).
Ideally models are generated from SQL schemas, which you map to domain models.
I think you hit the nail on the head.
I’m thinking about what Rails would look like without activemodel and activerecord. Or even just without activerecord, where we had to write the same sql every time we wrote a model but introduce the opportunity for a dev to screw it up. Imagine starting on a legacy code base and all the models had subtle differences in how they query the db. They don’t have the established conventions around _id fields, polymorphism, the nice bits around joins, and instead you have to discover bugs where you did a join but the two models each have a field called “description”…
Wouldn't you consider defining the schema doing the domain modeling?
I think ORMs do too much. I want to control the querying, or, more precisely, I want to control the SQL that goes to the planner. The good ones largely do allow for this, but I can't think of one that has innate support for vendor-specific features.
What I do appreciate is that they handle the boilerplate like managing connections, preparing statements, setting parameter values, and mapping database types back to client types.
> Wouldn't you consider defining the schema doing the domain modeling?
No, because if the schema is the only reference for data models, developers on any sufficiently large team will come up with extremely widely varied queries to access equivalent information. Those are more likely to be incorrect (someone with domain expertise on one set of tables might miss that authoritative data needs to be joined/queried from elsewhere), harder to update when schemas change (more client code changes to alter and test), and more likely to miss performant techniques to query data.
Those can all be addressed with disciplined use of views or common utility SQL snippets or functions, but ORMs also get you to that point without requiring as much ongoing discipline, care, and feeding.
> Those can all be addressed with disciplined use of views...
Totally agree. Views as a data API is the best way to take advantage of the facilities that the database itself offers and guarantees enforces consistency across disparate clients.
> developers on any sufficiently large team will come up with extremely widely varied queries to access equivalent information.
Ah yes, the famous database integration anti-pattern.
> but ORMs also get you to that point without requiring as much ongoing discipline, care, and feeding.
[citation needed]
The fact that you have being practising "database integration" won't suddenly disappear just because you used a ORM. In fact I expect even worse database integration from your average ORM user, as people that uses ORM blindly often don't care (to their own detriment) about "silly issues" like data provenance or persistence mechanical sympathy.
At some point I expect the DBAs of such database integration nightmares will have to start handling stuff like column-level security and row-level security to prevent naive users from shooting themselves in the foot.
> Wouldn't you consider defining the schema doing the domain modeling?
To an extent, yes.
But to the extent that a so-called impedance mismatch exists, this is going to put your domain model on the faraway/difficult side of that impedance mismatch.
And will result in your domain model existing in an (on average) less expressive language which is more difficult to test.
> if orms didn't force you to explicitly define your domain models about 60% of developers would simply never do it
This was never the experience I had. If anything, people tend to plan too much.
I'd rather take a mess of ad-hoc interfaces. Forcing people to do domain modeling does not go well.
Pretending that domain modelling is optional does not go well.
Sure, but it's better to do no domain modelling than to pretend doing it.
“Doing no domain modelling” is not really an option. It just means the domain model emerges accidentally from ad-hoc interfaces, conditionals, database fields, validation rules, and UI assumptions. Asking an LLM to help with domain modelling isn't ideal, but it's strictly superior to having your model designed by accident, informed primarily by the initial rough draft of your application code.
Too often, the avoidance of learning SQL creates more work than learning SQL.
One example is starting with NOSQL and proceeding to learn how to make it into a relational database.
People have been making these same arguments for decades and at this point I'm convinced they are all based on the same strawman:
That ORM's absolve you from having to learn SQL.
Once you understand that was never actually true to begin with you can treat the ORM as a tool that simply helps you generate repetitive boilerplate queries and hydrates result rows back into objects for you.
Furthermore, if your objects are long lived (e.g. client-side apps) then ORMs offer you helpful features like identity mapping, unit of work, and change tracking/events.
I'm also convinced most of the people poo-pooing on ORMs just haven't worked on problems where these kinds of features are useful. I mean, if you're writing a reporting tool that just queries the database and dumps the result to a table then yeah you might not need an ORM for that. It doesn't mean that ORMs don't solve useful problems for other use cases though.
The problem with ORMs are
1. They pretend SQL is standardized, and support a heavily reduced featureset for any given database as a result
2. They leave awkward holes in their abstraction, leading to psychotic behaviors like N+1 and implicit type coercions to helpfully break your indexes silently
3. They make simple queries simple, and hard queries absolutely revolting
4. You end up not wanting to use the objects directly anyways, so you end up with object-object-relation, needing a mapping layer from your database-object to your business-objects, which also defeats most of the benefits from change-tracking
5. The generated SQL is periodically utterly nuts, so you have to review every generated query anyways
6. You probably dont want to actually use any of the OOP mapping features like inheritance in your DB
The correct answer is to use a query builder + database model, enabling most queries to be written with some degree of type-safety, and minimizing the abstraction from SQL itself, and toss out the rest of the featureset
Which ORMs are you basing this comment on exactly?
(1) and (3) are not really problems with an ORM that gets out of your way and lets you drop down to raw SQL when necessary, but still helps you hydrate result rows back to objects (and still provides the associated features I mentioned previously).
(2) and (5) can be interpreted as "your ORM does not absolve you from knowing SQL".
I've never personally run into a situation where doing (4) or (6) were desirable.
> The correct answer is to use a query builder + database model, enabling most queries to be written with some degree of type-safety, and minimizing the abstraction from SQL itself, and toss out the rest of the featureset
If you work on projects where a full featured ORM can be replaced by a simple query builder then cool, but the rest of the feature set is really useful for the projects I work on so why would I toss them out?
I agree completely, the features the parent is mentioning like "identity mapping, unit of work, and change tracking/events" are exactly the things I don't want out of the ORM because that is the leaky abstraction I don't want to constantly be working with and around.
If it was just a query builder we could have a conversation about the benefits of that vs sql and when one beats another. But it is all kinds of other features that are implicitly activated and then conspire to ruin your day when you were trying to solve some other problem. ORMs bring too much baggage by default. So now you have to talk about its relative merits compared to just writing SQL and the merits of always having these other features activated. Which other features? You need to read your full ORM manual because they really vary from one to another.
Your comment comes across like saying "why would I use an impact driver when my screwdriver does everything I need?"
If you don't actually need those features then obviously an ORM will offer less value to you. That doesn't mean ORMs aren't useful tools, they just aren't useful for the problems you work on.
I tend to work on projects where those features are useful and if the ORM didn't provide them out of the box then I would need to build them myself. In other words using a query builder alone does not adequately solve the problems I need to solve.
I have list of issues with SQL. Not composable. Unable to detect query errors at compile time because the schema is only loosely coupled to the code base. And as you yourself point out, SQL is not standardized, which is also terrible and leads to things like Oracle vendor lock in.
And frankly this list hasn't changed in 30 or maybe 40 years now.
And DBA's were so notoriously egregious that Martin Fowler made his "NoDBA" blog post over a decade ago now. And the movement to NoSQL definitely made things worse.
I wish the SQL community would stop treating ORM's like the vietnam paper did 20 years ago, and embrace them for what they are, as a stepping stone, and maybe as a useful tool to help people understand SQL itself.
> as a stepping stone, and maybe as a useful tool to help people understand SQL itself.
But that is not what ORMs are. They teach bad habits that make SQL harder to understand, not easier, because the power of SQL depends on good data modelling.
Perhaps the worst habit is treating the database as subservient to the application code. This assumption comes naturally to many programmers. In most programming contexts, file formats, wire protocols, and internal representations are defined by the code that consumes them. That's fine in some cases.
But in a data-centric application, the relationship should be reversed. Before writing a single line of application code, you should understand the domain model and design a schema that represents it well. The database is not just a persistence layer for objects. It is the system of record, and its structure should reflect the shape, constraints, and relationships of the real-world data. Everything else should be built to conform to it.
I mean there are plenty of projects that don't fit this description, where the database is just a persistence layer for your objects and the database should be subservient to application code.
I find the 1, 2 and 3 to be complete non issues. As in, they dont exist as issues for hibernate. If you feel like particulat query is oh so difficult, you can always use sql for that one part.
4 is in the "like so what" category.
5 - it optimizes alright for average case. You have to optimize in edge cases, but then again, you have to optimize edge cases with pure sql too.
6 - no I dont want much inheritance in db whether i am using pure sql or orm.
> 1. They pretend SQL is standardized, and support a heavily reduced featureset for any given database as a result
EF Core is provider-specific and also exposes provider-specific functionality.
> 4. You end up not wanting to use the objects directly anyways, so you end up with object-object-relation, needing a mapping layer from your database-object to your business-objects, which also defeats most of the benefits from change-tracking
This just isn't true for EF Core. https://learn.microsoft.com/en-us/ef/core/performance/effici...
> 5. The generated SQL is periodically utterly nuts, so you have to review every generated query anyways
Not universally true either. You only have to review complex queries. If you're making claims about a specific ORM it would be good to mention it as it's not universal.
> 6. You probably dont want to actually use any of the OOP mapping features like inheritance in your DB
Then don't? Since when is inheritance required for ORM-usage?
I have found a lot of the anti-ORM critiques come from either using a crappy ORM or having not used a good one in the last 5 years.
Best to avoid OOP jungles in general.
Feels like everyone has to go on the journey.
ORMs are bad - I’ll just use SQL.
Hmm - I need to map these results onto objects I can use.
Hmm - wouldn’t it be great if the object tracked changes and could save itself.
I need related/child objects - wouldn’t it be great if I could auto fetch them. …
I think that journey only feels inevitable if you start from the assumption that the application object model is the centre of the system. An alternative journey:
Hmm – I should model the data according to the domain, not according to the shape my application objects happen to want.
Hmm – maybe “related objects” are not things to auto-fetch, but relationships the database engine is already built to handle.
Hmm – now that my schema matches my domain, complex problems can be solved with a few lines of SQL, saving me hundreds of lines of application code.
Hmm – in fact, now I realise that many important operations can be performed without round-tripping the data through application code at all, saving me thousands of lines of application code.
Using an ORM should not exempt you from designing your schema around the domain. If anyone thinks it's the case they're either using the ORM wrong, or using the wrong ORM, or perhaps creating the wrong ORM.
Similarly, designing your schema to match the domain does not necessarily grant you the productivity boons of an ORM.
Having (ab)used Postgres with and without ORM, I've never had a situation where the latter imposed any kind of design decisions on the schema. They're orthogonal concerns. Itself, the choice of using an ORM tends to be motivated by experience with certain requirements in the business logic. I love SQL, but when having to deal with API resources and their various representations, marshaling, validation, options, etc, it's difficult -- and to say the least, impractical -- to stay principled to the "no-ORM" and "raw-SQL" mottos.
ORMs and schema design are not fully orthogonal. A database schema and an ORM model can be conceptually separated, but in practice they influence each other. And even if you are vigilant, knowing that you'll use an ORM will cause you to obsess about things which satisfy the ORM at the expense of good modelling.
TLDR: Tooling pressure is a design pressure.
None of your points remove the need to map db values to objects and to fetch related objects.
If you just want to store and retrieve objects, and then store and retrieve "related" objects, what you want is an object store, not a relational database. You can use an ORM to shoehorn it into a relational database engine, but don't fool yourself into thinking that's the same thing as using a relational database engine properly.
Obsessively cramming tabular data into objects is often unnecessary, and it bloats the code downstream of the database query. It then encourages the bad habit of performing data manipulation in code rather than directly in the database.
"Fetch related objects" is a code smell. If any related data was needed, your original query should have already fetched it.
Problem is that doesn't work nicely in a one-to-many or many-to-many relationship - fetching it in the original query means deduplicating in the application code, or not fetching it and getting related rows afterwards. And that's one of the things ORMs are really good at.
For a video game, simulation, CAD program or other stateful program, loading an object graph can be natural. In that context, an object store, document database, serialized state, or ORM-backed persistence layer may be a reasonable fit. This isn't database design, it's application design.
But in data management applications, the job is to derive specific information from stored facts. For that, SQL is not an implementation detail behind objects; it is the main abstraction. The whole point is to ask for the shape of data your application actually needs, not to arbitrarily hydrate objects and reinvent a bespoke querying engine on the client side. I can barely even remember the number of times that I've ripped an ORM out of a system because the code to interact with hydrated objects had devolved into a single-purpose database engine, a sprawling mess of code, seemingly well organised into objects, but ultimately wasteful.
Often, de-duplicating in code is a perfectly fine solution, and significantly more performant than multiple round-trips. A join that repeats parent columns is not a flaw in SQL. It is only a problem if the application insists on rebuilding a nested object graph instead of asking for the shape of data it actually needs.
If the data returned by the query does not match what you are presenting, the answer should not be to fetch a pile of related objects and interrogate them in memory. It is to use more SQL to further digest the data so that the result set more closely matches what you intend to present to the user.
I've went on a similar journey and did just end up going to back SQL.
Mapping database rows to domain objects really isn't that painful and you only do it once. So not a big deal. LLMs actually make this a non-issue now.
I actually realised I like the separation of domain objects and the data layer. It just makes things easier to think about for me. And it means my data layer is completely abstracted from the domain. Makes it easier to implement different storage/caching strategies.
And I also realised, if your hot path is needing to get related/child objects then you should probably just write an optimised query as a prepared statement or a stored procedure. It's rare that you actually have that many different ways you want to access the data.
They are really useful for speed of development though if something is completely greenfield and you don't know the full picture of how data is going to be accessed.
It's not one or the other, it's both. Sqlalchemy ORM is my favorite, followed sqlc (golang), because they are both there for you for your highs (select * from table order by created at) and your lows ([an inner join followed by 4 left joins with an aggregation function])
If you're working at any decent scale, the journey is the opposite IMO.
You stopped right before the best part. When they decide to create their own ill designed, badly tested, and undocumented mapper.
> Hmm - I need to map these results onto objects I can use.
What sql client is going to hand you raw text?
> Hmm - wouldn’t it be great if the object tracked changes and could save itself.
Lost me there.
> > Hmm - I need to map these results onto objects I can use.
> What sql client is going to hand you raw text?
In python's standard database interface you access columns by index, not name. I figured that's what they're referring to.
Entities
ultimately, there is no silver shortcut - you just have to write the damn code
Yeah, exactly. I think the best approach is always to know SQL and know the ORM.
Most of the time you’ll be able to simply use the ORM, but every so often you’ll inevitably come up against a situation where a custom query gets the job done better, and you’ll still get the benefits of deserialising to objects that the ORM offers.
> you’ll inevitably come up against a situation where a custom query gets the job done better
In my experience, these are typically best turned into views (or materialized views), because they represent some fundamental relationship or property within the data that’s useful to be able to quickly reference or query directly against. KPI aggregates, for example.
As long as you restrict yourself to an ORM-compatible schema, you are restricting the power of SQL available to you. Learning SQL properly means learning to model your data correctly, and this usually makes ORMs a non-starter.
Without an ORM you have to write a bit more boilerplate code to interact with the database. But by taking advantage of the power of your database engine, you could potentially avoid writing huge amounts of data manipulation logic. In my experience, an ORM is more of a code amplifier than a code simplifier.
All of this depends on the problems you’re solving though. There is no one size fits all approach to database development.
Generally speaking if an ORM is a good fit, the thing you're doing probably isn't database development, it's application state management disguised as database development.
ORM is compatible with any schema, but perhaps you are thinking of something like the active record pattern?
With a few extra lines and mapping objects to classes this can be done.
To have all this ease of use you give up so much in performance.
Most apps and companies never get to the point where performance matters that’s why we have ORMs.
> you give up so much in performance.
Not really. ORMs (memory) and databases (disk) are distant by multiple orders of magnitude performance wise. Skipping the ORM to shave off some cycles is akin to haggling over a few pennies on your thousand dollars bill.
SQLAlchemy vs hand rolled SQL and mapping the results it’s not even close. The overhead if you need it cuz you’re sub scale so be it.
its a good abstraction if you build it yourself.
Why not both? ORMs for the simpler CRUD operations, SQL when it gets a little hectic.
The author basically says this in the first paragraph, but the title (and some of the language the author uses) implies that people should just use SQL.
It's a reasonable article pointing out some of the annoyances and problems of ORMs (especially in the Java world, where they tend to be overengineered) but there are still a lot of advantages to them if you are in an OO language and they used in a reasonable way.
You can optimise your schema to suit your application code, or you can optimise your schema to fit your domain model. Doing the former makes your glue code easier. Doing the latter gives you maximum performance and the maximum querying power of your database engine.
You can optimise your schema for the convenience of your application code, or you can optimise it for the truth of your domain model. The former makes glue code easier. The latter gives you stronger constraints, better performance, richer queries, and a database that can answer questions the application code never anticipated.
Yes, this is the sensible answer.
Related:
What ORMs have taught me: just learn SQL - https://news.ycombinator.com/item?id=28812506 - Oct 2021 (24 comments)
What ORMs Have Taught Me: Just Learn SQL (2014) - https://news.ycombinator.com/item?id=24845300 - Oct 2020 (291 comments)
What ORMs have taught me: just learn SQL (2014) - https://news.ycombinator.com/item?id=21031187 - Sept 2019 (634 comments)
What ORMs have taught me: just learn SQL (2014) - https://news.ycombinator.com/item?id=15949144 - Dec 2017 (348 comments)
What ORMs have taught me: just learn SQL (2014) - https://news.ycombinator.com/item?id=11981045 - June 2016 (295 comments)
What ORMs have taught me: just learn SQL - https://news.ycombinator.com/item?id=8133835 - Aug 2014 (234 comments)
I was curious how have sentiments changed over time. Brief LLM-based analysis: https://ampcode.com/threads/T-019f32ac-3b1e-74be-ad63-5f175d...
Overall, seems like it got more nuanced over time - even though it's still broadly in favor of SQL. Favor for ORMs (flagged also as a term that can mean many things to different people) is more in terms of type safety, mapping, migrations, etc. so more a library/utility rather than a framework that fully abstracts away the database.
Seems like we needed an annual meditation on this topic until 2021, then we took a 5 year hiatus? What happened?
I generally like ORMs but recognize that they have a lot of problems. The most common problem that I've seen is when an ORM makes it easy to select records in a way that looks efficient but really is not. Strictly speaking, this isn't a failure of the ORM itself -- it's the fault of the developer who is using the ORM and also the developer that didn't catch it in code review. But it's a case where the ORM is making work for everyone and obscuring legibility into the code instead of saving time and providing clarity.
I've written complicated stuff where an ORM isn't appropriate, but if I'm honest, a large fraction of what I've done in my career is just making boring software to automate menial clerical work, and ORMs are good enough for those kinds of projects.
Firmly agree. I wish that ORMs provided two interfaces above raw SQL: a syntactically guaranteed-to-hit-indexes set of functions, and a do-anything set (e.g. MyModel.objects.unrestricted.filter(…)) that you could lint for and audit. An unsung benefit of ORMs is that they have code-level awareness of what queries are likely to be fast, since indexes are usually defined in the ORM. I wish they took more advantage of that.
> Strictly speaking, this isn't a failure of the ORM itself -- it's the fault of the developer
You've got that backwards. If a tool obscures complexity such that a developer using it could be tricked into thinking their efficient-appearing code is actually inefficient, the problem is the tool. A well-designed tool makes inefficiencies explicit. "You're holding it wrong" is not engineering advice.
> ORMs are good enough for those kinds of projects.
It's all good as long as you have properly abstracted it away from your core application. The trouble with some ORM toolkits is that they encourage you to move database logic into the rest of the application and that's when the messes begin. The old school PHP programmers will know well that SQL in raw doesn't automatically mean proper separation of concerns either, but it is more likely to push you in that direction.
I used to love ORMs so much that I built one for Java, in the early 90s, and it was one of the main offerings of a startup that I joined. I have come around 180 degrees. My rethink started when a developer at a Wall Street bank said: having Oracle on my resume is valuable. Having your ORM on my resume is not.
And then there’s the “now you have two problems” dynamic. You not only have to write high-performing queries, but you have to get the ORM to generate that query for you. And sometimes you don’t want objects. And the schema mapping has to track schema changes.
Just write the damned SQL, it’s not that difficult.
ORMs are so incredibly finicky. I still remember using old Linq-to-SQL (not Entity Framework) and I had to write the linq query in the reverse order of what I expected or it created 3 nested subqueries instead of just joining the tables together. That was when I learned to instantly double check every ORM query I wrote.
> Linq-to-SQL (not Entity Framework) and I had to write the linq query in the reverse order
I remember those times! Had to write the LINQ, see what it compiled to, redo, until the query was efficient. Abuse LINQ subtleties in how it generated JOIN predicates since it only supported equality. Something about finding an equivalent way of expressing a query with sub-selects that is also computationally equivalent. All so I can get my efficient SQL without writing SQL. So silly.
> built one for Java, in the early 90s
So was your ORM for Oak? Java didn't hit the public sphere until 1995 IIRC
No it was Java. Sorry, it was late 90s.
I'm totally on board with the idea that ORMs create a variety of inefficiencies, pain points, and make it really easy to create bad queries or querying strategies. But I use them anyways because the convenience of mapping a row to a code object makes writing programs feel fast and simple. And if you know how ORMs can cause problems and how to watch out for them, you can still get a lot of mileage out of them.
That being said, what's the closest alternative that satisfies this - "mapping rows to a code object" - that doesn't suffer the same problems as an ORM? A middle ground between an ORM (like SQLAlchemy, for example) and "your rows are returned as a key/value dictionary where the column names are keys" type approach like Python's DB-API's DictCursor or PHP's mysqli_fetch_assoc. Is there a middle ground here?
The happiest middle ground I've found in .NET has been LinqToDb.
It's more of a Micro ORM, -but- has a Linq DSL, as well as DSLs for lots of DB bits. CTEs, Window functions, Bulk copy, 'treat this in memory collection as an input rowset', certain DB Specific bits... and if you need some special sauce to deal with brownfield jank [0] it's very easy to wire-up custom SQL bits into your queries via attributes if needed.
If you use method syntax rather than linq query syntax, you will have minimal surprises with the SQL generated. Typically if it does generate something I didn't expect, I dig in and what it did was indeed both correct and better than what I was trying to do anyway.
[0] - Fun nasty case I ran into on a brownfield project; 'If this number has a decimal point, it is a direct percentage rate. If the number does NOT have a decimal point, it is the FK to a lookup table that has the percentage rate'
In .NET, I think Dapper comes closest to what you are describing. It does the object mapping, but you still write the queries as SQL.
https://github.com/DapperLib/Dapper
Yes, there is a middle ground. Elixir's Ecto does this well.
Database rows map to structs. But it doesn't try to figure out how to mutate the data for you to keep the struct in sync with the database. All mutations are explicit using changesets (which can also be used for other non-database purposes, like validating user input for an API.)
There is no implicit preloading of data. You have to explicitly preload.
Data is never fetched implicitly. You have to call Repo.all or Repo.one or something.
It has a query DSL that's a thin wrapper over SQL. It's well-designed and I've never had a problem with it.
> Yes, there is a middle ground. Elixir's Ecto does this well.
Ecto is by far the closest thing to a perfect pattern for abstracting over sql that I've ever seen. I WISH other languages would create similar libraries. Its the biggest thing keeping me coming back to elixir for any kind of database project. it just makes sql so ergonomic.
As someone who works with an 8 year old 500k LOC elixir codebase with millions of users, I hate Ecto so much. The thing I hate most is that there are 2 different syntaxes.
But a close second is that it encourages composition in situations where duplication is the right choice. Having your sql query spread across 7 files makes tracking down bugs and performance issues (and fixing them) incredibly difficult.
I mean FWIW Scala has Slick (but then you're dealing with the rest of Scala ecosystem...) and there's Linq2Db on .NET side. Having dealt with all 3 on some level (Slick the least) I would say they are fairly similar in intent (i.e. differences are primarily due to language idioms.)
I'm not sure Slick is still maintained. Recently I depended on https://github.com/com-lihaoyi/scalasql#simpletable-variant-... for small CRUD stuff and liked it quite a bit. These days there's a whole boulevard through the Scala ecosystem that doesn't involve typelevel pedantry and obscure DSLs just for the love of hieroglyphics.
No, there is no middle ground. You can either maintain relations throughout the full application or you can transform them into application-native structures, the latter of which is ORM.
The article seems to be confusing ORM with query builders. Query builders are where you might avoid writing SQL. ORM is a data transformation technique.
> the convenience of mapping a row to a code object makes writing programs feel fast and simple.
Even when you had to do this manually, it was a very minor effort. A one time thing. These days of course any half decent LLM will produce this code without much fanfare. The argument just melts away.
Otherwise, ORMs just layer abstractions on abstractions. You end up with these weird half implied joins resulting in absolutely terrible actual joins happening. Unless you actually understand what you are doing, in which case you could be hammering out those joins manually. And of course the underlying SQL is usually a bit richer than this one size fits all nonsense ORMs do in order to work across sqlite, mysql, postgresql, etc. and pretend that it's all the same.
Another issue with ORMs is the object impedance mismatch where a junior wannabe coder thinks it's all just objects and classes and you end up with these gazillions of completely pointless tables that then necessitate a huge amount of joins. Often the right amount of tables is a lot smaller.
Also, if you aren't querying on it, does it really need its own column? I end up using my databases as document stores quite often. Gets you the best of both worlds. You get to query on nice indexed columns and then you deserialize the big blob of json or whatever into your rich object structure. Simple CRUD for objects shouldn't require a whole lot of engineering. It's only when every little object needs its own little table that shit gets complicated. And another benefit is that this usually results in more stable table structures that don't need a whole lot of database migrations. Getting rid of those removes a lot of needless faff from day to day deployments.
> Even when you had to do this manually, it was a very minor effort. A one time thing.
Maybe if you’re fetching data from a single table… once you start joining across multiple tables and need deduplicate your result rows it gets pretty annoying to do it by hand though.
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.
A query builder is not an ORM.
ORMs build queries for you, but a query builder does not need to be an ORM.
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. You can absolutely have ORM without query building just as much as you can have query building without ORM.
Sometimes ORMs and query builders are combined into a higher order system, such as what is described by the active record pattern. This might be what you are actually thinking of instead?
Okay, so I have an object like:
Let's say we have a "MappedUser" which is derived from this type by this ORM.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.
> 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 for these patterns.
> into some underlying queries against a relational database.
Unlikely. SQL is mentioned in the headline for a reason. Nobody uses relational databases in the real world. The only remaining relational database engines that are still maintained really only exist for educational purposes. 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.
> That depends on the rest of your code.
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.
Autocomplete is making me lazy. If I don't see what I'm about to type within two or three characters, I feel like the IDE isn't doing its job of helping me. So being able to type `db.Cust` and autocomplete Customers is really nice. I do know SQL, but yes, the language servers usually have a harder time connecting the SQL to my backend code, whatever language it's in, without quite a lot of config fiddling that pretty much obviates any time savings I would have gained from autocomplete.
I'm firmly on the ORM side of things, despite knowing SQL very well -- but your IDE/editor can fix this with bare sql. Try using Jetbrains Datagrip or the DB integration in Idea or one of the other language-specific IDEs.
In my database[0] you get an SDK generated from your schema. Typescript is the default and man, the autocomplete works so well.
I recently added support for SDK generation in Rust and Go, just do `disc codegen —rust` (double dash, my iPad is autocompleting the wrong dash) and you’re good to go.
[0]: https://disc.sh
Use testcontainers and make sure you have an integration test for every query..
I think the bigger problem is that SQL is in almost every language a second-class citizen. And even calling it second-class can be seen as a stretch.
I’m a SQL-lover and ORM-hater but I don’t see why any language would support another wholly different language as a first-class citizen.
Ideally, SQL wouldn’t be a wholly different language, but a library with bindings for various languages.
Time to start using plsql, ADA with first class support for embedded SQL.
That's why it's called SQL aka String Query Language. The queries are just strings.
Are you being cheeky? The S stands for Structured.
Which is why one is better off using IDEs, especially those from DB vendors.
[dead]
[flagged]
For the vast majority of simple use cases the common subset of all popular SQLs is exactly the same. Otherwise… just use Postgres
It's not that different. I'd rather have a different way to do UPSERTs or a different window function here and there [1] than figure out every ORM's join syntax or its sneaky ways to SELECT N+1 me into oblivion.
[1] LLMs make these very easy to handle.
I would argue that is a bit like complaining there is no "backend language" and that Java, Rust, Go all have different syntax.
The choice of DB is arguably more important than the choice of backend language.
Best solution: Learn SQL and understand the relational model. Learn data modelling and normalization. Then choose a good ORM which does not get in the way, but saves a bunch of boilerplate code.
An ORM only saves you boilerplate if you’re mapping relationships to objects. And if you’re doing that, you haven’t learned good data modelling and normalisation.
ORMs are for storing objects.
SQL is for correctly modelled data.
There's a middle ground between ORMs and raw SQL, especially if you're using a strongly typed language. My library Zapatos[1] is one example among several.
[1] https://jawj.github.io/zapatos/
That does look like a compelling tool specifically because it isn't really an ORM. It seems more like an ergonomics layer for SQL within that particular language. It looks decent because the database schema remains the source of truth, and the code adapts to it — not the other way around.
I think ORMs mostly exist because most programming languages tend to lack an elegant way to write SQL and interact with results. Somewhat ironically, the much-maligned CFML (aka ColdFusion) got this right decades ago. It made SQL string building trivial, and it provided a native data type for tabular query results.
No other language I'm aware of has this, and it's the missing piece in many modern ecosystems. They do not need an ORM. They need better ergonomics for interacting with databases: a clean way to compose queries, execute them, and work with the result as structured relational data rather than shoehorning it into application objects.
> They do not need an ORM.
What you do need is some kind of boundary mapping layer so that your application isn't tightly coupled to the database. That might be a an RRM instead, but if you are going to all the trouble of adding an RRM, why not an ORM? What's the difference, really?
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 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 make a decent DB with minimal constraints or stored procs, meant for regular code to use. The abominations I'm talking about are like, they use an ORM, they use meaningful fields as PKs that end up being non-unique later, or they reinvent the type system by making some tables with a json field that can mean completely different things based on some enum column.
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.
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 interact 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!
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
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.
> different levels of sophistication
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
You should also learn joins and ordering/pagination. But that can be on day 2 :)
I consider those to be part of basic Table CRUD, but yes, absolutely. :)
[dead]
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.
Of course, everyone in HN is "advanced" so what do I know!
> learn-it-enough-to-build-serious-databases
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
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.
That is exactly what I was thinking. There is such a low barrier to entry with an outsized payoff.
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.
Still applies today in data science, one is expected to master SQL alongside Python and Excel.
In my opinion Elixir Ecto is ORM done right:
1. the functional/immutable nature of Elixir makes read and writes much more explicit and there is no need to magically track deep mutations of nested objects to translate them back into UPDATE/INSERT queries
2. Elixirs support for lisp-like macros allows for an ergonomic embedded query languages that is syntax and schema checked, mirrors raw SQL really well and, frees you from string-oriented query building
3. the query builder DSL addresses one of the main weaknesses of SQL query statements not being composable
4. The automatic conversion between JOINed tables (on the DB side) and nested structs (on the Elixir side) is done on the right abstraction level to work reliable and and being explicit enough to generate predictable queries.
What usually happens in my experience is, that a home-grown Data Access Layer usually turns into a bad "ORM light", because materializing results is repetitive and tiring work. You want to abstract it away.
As a .NET developer I think EF Core has made the right call here, by allowing you to write SQL where it's needed and still use its infrastructure for all the tedious work of materializing your results.
Admittedly in 2014, the time the article was written at, I've also felt using OR-Mapper is a dead-end. But in 2026 the world isn't black and white.
I’ve had so many frustrations with EF Core.
I always inevitably want to model something in the domain in a way that is not be supported by EF. So I have to maintain EF DTOs and basically give up on the change tracker.
There are simple "ORM"s that just map classes to tables and columns to attributes. Basically focused on serialization instead of query generation. I find those to be a good balance.
Yeah, you can use SQLAlchemy like this. It's called the data mapper pattern. The bad type is like Django or Rails "Active Record" type ORMs.
This is one of numerous things Elixir and Phoenix get right with the database layer, which on the surface looks like an ORM but is in fact a set of clever functions that write SQL using Elixir macros, as well as a system for validation and minimal changes to data being passed to said SQL.
I’m surprised more languages don’t copy this because it’s exactly what I want rather than loads of complexity that eventually always breaks down.
> on the surface looks like an ORM but is in fact a set of clever functions that write SQL
Honest question -- what's the difference?
Usually the problems with ORMs stem from the fact that they are exactly clever functions that write SQL. The cleverness abstracts features of SQL that are important for performance and also makes it easy to do things that are bad for performance.
I'm not saying that the ecosystems you mentioned aren't doing something different, I just don't know what it is from how you've described their DB layers.
The thing is that in Ecto, everything is structured around the actual underlying data. Rather than some abstract objects and stuff like that.
query = from u in User, where: u.age > 18
Repo.all(query)
And there is no magic (At least very little). For example, if you wanna access something that is in another table, for example, you're on a user and you wanna access their posts in many frameworks, if you try to read their posts, they would be automatically loaded from the database but in Ecto, you need to explicitly preload them. That avoids accidental and n+1 problems because you can plan your queries more. You're not gonna trigger a lot of queries without realizing it.
No ORMs try to make the concept of SQL hidden, in Elixir you will not get very far if you don’t understand the SQL you’re trying to write. So I’d probably say nothing is really being hidden from you - as little magic as possible.
https://ecto.hexdocs.pm/Ecto.Query.html
I think C# has a similar syntax for building queries in linq.
Elixir
from u in User, where: u.age >= 18, select: u
C# var adults = from u in users where u.Age >= 18 select u;
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.
What's the problem with using ORMs for 95% of the cases and using raw SQL only for the remaining 5% where ORM isn't sufficient? One important benefit (aside from writing less code) of ORMs is type checking which is important for maintainability in large complex projects.
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 was against ORMs until I used EF Core in .NET which I really loved. A good ORM is amazing for productivity and when needed you can always write raw SQL.
I don't use .NET anymore but lately I've been happy with Drizzle for TS. It's very performant and expressive. After years it seems that they're finally going to release v1.0 soon.
Personally I would never go back to writing all my queries with SQL, manually mapping the results, etc.
I believe efcore is really well designed and handles the ORM tradeoffs in a very usable and mostly efficient way. And someone would have to pry LINQ out of my cold, dead hands. SQL is fine and I'm glad I know it. But I thank god I almost never have to use it.
The argument that really hits home for me, after 30+ years in this industry, is stored procedures. The “Stored Procedures are Evil” argument to me is an artifact of an industry that promotes treating engineers and infrastructure as entirely interchangeable and anything that gets in the way of that is Evil(tm). But what working at Salesforce in the 2000’s taught me is that you can do really amazing things if you’re willing to invest heavily in understanding your infrastructure and specializing the hell out of it. Of course that created Oracle lock-in for Salesforce, but that lock-in was the result of Oracle having capabilities that simply didn’t exist elsewhere that Salesforce needed to scale. I would argue Google took that same idea and 100X’d it by building the capabilities they needed when they needed them. In the case of stored procedures, I think if you find yourself fetching huge amounts of data and then doing complex manipulation to it that you can’t do with SQL, consider doing it with stored procedures in the engine and greatly simplifying your application. It may just work out!
I haven't used stored procedures yet, but even ON DELETE CASCADE is super convenient and I suspect somewhat underused by SQL scaredy cats.
I think the stored procedure equivalent would be a "on delete, cascade these tombstones" -- both safer and cleaner.
> I haven't used stored procedures yet, but even ON DELETE CASCADE is super convenient and I suspect somewhat underused by SQL scaredy cats.
Sooner or later you are going to hit enter, wait a few seconds and say "oops!"
ON DELETE CASCADE is horrendously unsafe unless you have full understanding of the entire data model - which is unlikely for the average employee within a large organization with a gigantic database. (And it's also rare to be permanently deleting data when working in such a context, so the convenience doesn't matter that much.)
It's in the context of "SQLite as local data storage for an application", and I am absolutely sure that entries in a cross-reference table make no sense anymore when one of the linked objects is gone, or entries in an auxiliary data table when the principal object is gone.
I am not using ON DELETE CASCADE to be clever - the referenced data is genuinely required.
The problem with ORMs is that they look kludgy without language support - which is why Hibernate in Java looks painful, while DotNet's EF looks like magic. I wrote something similar called TinqerJS - https://tinqerjs.org, which is like Entity Framework but for TypeScript.
There's immense value in everything being typed from the API down to the DB queries.
Of course, ORMs are not for all queries in your project, and may not be a good fit for some projects. That goes without saying. The problem with the article is that it's dismissing ORMs by looking at specific implementations.2014: people respond with indignance that they should have to learn SQL now that there's a shortcut
2026: people respond with indignance that they should have to learn anything now that there's a shortcut
I like SQL. I enjoy writing SQL. I find ORMs produce crap SQL.
But the current shortcut du jour is pretty damn good at writing SQL.
While I do enjoy the Django ORM, for many queries SQL is just better. It's almost as if it was designed for querying database.
Once you hit a certain level of complexity in your queries, you're better of with SQL. It's not that you can't do the query in the ORMs, but you're then looking at learning their special query language and those are never better nor easier to understand than just SQL. Those ORM query languages certainly aren't transferable across ORMs, but SQL frequently is. If you can query MariaDB with SQL, you can query SQLServer and PostgreSQL. The same can't be said for e.g. Django vs. Hibernate.
For the "give me all the entries, with this one property" ORMs a much quicker and easier to work with. Once you start needing to use subselect, multiple joins, weird ranges or constructing object with data from across tables, I'd rather just write the SQL myself.
And Django makes it ridiculously easy to write those raw queries in SQL directly so it seems like you're getting lots of mileage from the ORM without giving up anything
I write SQL every day, but I cannot get onboard with liking the language. Yes, it is incredible that the language has had such staying power. No, it is not great that such a flawed design has persisted.
I enjoy this article[0] about some of the persistent warts which will seemingly never change.
[0]https://www.geldata.com/blog/we-can-do-better-than-sql
At yet people (mostly) skip SQL and learn some ORM.
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.
In my experience, boilerplate sql isn’t even something I would consider a big pain point. The biggest challenge (to me) is explicit data contract enforcement across the stack.
I haven’t personally had third-party ORM frameworks succinctly encourage synchronization and help me build against long-term divergence of data models across the stack. “Make it easy to do the right thing and hard to do the wrong thing” still leaves a lot of room for ‘gotchas’ as apps evolve over time.
Finally, I don’t understand the aversion in learning even a bit of sql. As topics go, it’s a very good (maybe even the best, if I were being provocative) effort:payoff ratio. Not sure I’d call myself a sql expert, but am always pleasantly surprised how much functionality is within reach by knowing even the very basics of sql.
My point of view (after 18 years of programming): DO use frameworks (compile-time checked queries if you can) but skip ORMs that hide/obfuscate SQL completely as it will result in slow queries, extra round-trips, etc
I don't even use frameworks. I want my SQL and my regular code to be as close as possible to make it easy to reason about. Like SQL directly inlined with my JS/Py function. Don't need to mentally translate from some query builder to SQL or deal with some native "model" object it converts into. Have never suffered from a wrong-type bug.
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.
It's not a matter of "fractional speed difference" unless your database has very few entries. OR mismatch problems often like to appear shortly after your database starts to see any real use.
The only performant way to use an ORM is to use escape hatches everywhere. Alternatively, you can use an "ORM", something which calls itself an ORM while only doing superficial data mapping into dynamic or generated native (to your language) data structures. There are a _lot_ of these, most normal people call them query generators.
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:
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 delayed the problem while leaking abstractions. You can add proxies to those but you're now back to square one except you've maybe improved performance a little bit.
I can't tell if you're arguing against SQL or orms. But I take your argument in favor of SQL because that's the native language of all the DBS and the dozens of frameworks and systems on top of them are "just use x...."
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'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.
I think there is no one answer for this. In some cases pure SQL is better, in other cases, you need higher level constructs to be efficient, consistent and less error prone.
We have lots of experience with ORMs based on dynamic languages (i.e. Objective-C and Ruby) and if not careful, you can indeed go sideways pretty quickly.
Recently, we've been using https://ash-hq.org. It tries to solve the same problems as an ORM, but using a pure functional language (Elixir). You are using structs instead of objects, so it can feel very close to using raw dictionaries/hashes. It also makes it super easy to drop down to raw sql, while maintaining that struct interface at the top.
While it does take some getting used to (especially coming from a dynamic, OO language), I'm liking this alternative a lot!
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’d go with a balanced view: you need them both for any non-trivial product. I was recently reviewing a PR that renamed a model, I wanted to understand what happened under the hood. Turns out that mariadb had a rename table operation forever ago and that was used by the orm under the hood. So no need to backup the prod table. Just run migrate and be done with it.
PS: I still exported the table before deploying this fyi.
If you use Java and like to write SQL, check out https://pyranid.com
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.
Is it very similar to the relatively new jdbcClient from Spring framework?
https://www.danvega.dev/blog/spring-jdbc-client
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 really enjoy using Rel8 (https://rel8.readthedocs.io/), so much so that I reimplemented it in Rust (https://github.com/simmsb/rust-rel8).
For me I find it's an excellent step up from a plain SQL query builder (with an API such as `select(Foo).join(bar)`) as it lets me both effortlessly perform projections (one can write `(\e -> (e.foo, e.bar) <$> someQuery` to take a query producing rows of `E` and turn it into rows of 2-tuples built from two projected fields.
I wrote a bit about my Rust rewrite here: https://bensimms.moe/postgres-lateral-makes-quite-a-good-dsl...
ORMs are an anti-pattern. What ends up happening on most projects is that, over time, the ORM ends up generating increasingly complex, inefficient SQL queries behind the scenes. Since some of the people who use the ORM don't understand SQL, they don't realize how inefficient their ORM logic is; it looks like a simple operation from their perspective... It's only if you look under the bonet that you realize that the SQL being generated behind the scenes is a monstrosity. Nobody would have dared write this fugly mass of SQL by hand but from the ORM layer, it looks reasonable... Just a few objects joined by dots....
>just learn SQL
Implying I use an ORM because I don't know SQL... I've reverse engineered embedded databases and written directly to the .dat files on production systems that deal with HIPAA data. I'm pretty sure I know SQL better than most people on HN. I still prefer an ORM.
Why? Because with my ORM, I can code gen faster than you can vibe code. I can build on top of the abstraction layer. The data model in the ORM is the M in MVC. The backend could be a SQL database, a file system, a REST service, that part is irrelevant. The M is the same, regardless of the backing store. View and Controller code still works.
I find most people who are anti-ORM are kinda junior and trying to flex their power to write SQL scripts as if it is impressive. That's why there's always this weird implying that ORM users don't know SQL.
ORMs do have their use but you can easily screw things up. An anecdote from an university: their was a student administration system where students could themselves enroll to classes. simple enough job, one would guess. but there was a catch: at certain times, usually when more than one student logged in, the system predictably crashed.It turned out, that when a student logged in, a join over 13 tables was performed, even classes the student attended years ago where fetched at the login. These joins were clearly from misconfigured hibernate classes, took them some time to reduce the load on the system
What Python taught me: just use C.
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.
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.
That's a query writer. Not an ORM.
No, it's an ORM because it gives you object based iteration over your query (and the ability to use custom classes for those objects, you just don't have to create classes for every single thing if you don't need them).
EDIT: oh wait looks like I never got around to implementing the ability to use custom classes :) this is still in the to do section:
come up with a good "mix in" style to cast the objects returned from the iterator to a new class for implementing custom functionality (that one would normally include as part of the "boilerplate" class)
You are outputting generic QueryRow classes in your code for all results. That doesn't make it an ORM.
By your definition PDO would qualify https://www.php.net/manual/en/class.pdorow.php
Here's a full report for you https://gist.github.com/hparadiz/a1fe30e88dbbe070878a7ea4f72...
No, PDO doesn't qualify, because it lacks the "relational mapping" part. If I want an AI opinion of my project I can always ask a chatbot myself.
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 :-)
Edit: in the HN comments, we're debating "the best way to generate SQL", which is fine, but imo it overlooks the biggest value for ORMs: enforcing business invariants.
I.e. yes a simple INSERT is trivial is write, "why have the ORM to that!", but are you going to enforce the same business logic in the 10 places you do `INSERT authors` in your codebase? And if the answer is "I write an single `insertAuthor` abstraction to enforce this" then you're half-way to writing an adhoc half-specified, bug-riddled version of what a reactive ORM like Joist will do for you. [2] :-)
[1] https://joist-orm.io/
[2] https://joist-orm.io/modeling/why-entities/
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.
It's like people can't just let go.
This is inevitably what happens every single time so just use an ORM and stop being stubborn.
The problem is that "ORM" does a lot of heavy lifting as a term and can mean different things to different people. Like yes, obviously, one needs some sort of SQL -> data structure transition on the boundary (using "object" overfits to OOP!). But that can be extremely light weight. Let people write SQL, have a thin layer to pull the results back out into the appropriate data structures, and move on.
Every good ORM lets you write SQL. Mine for example has a getByQuery and getByWhere as standard methods. An ORM isn't just writing queries for you it's also handling type casting from lang primitives to SQL and back. In 99% of crud rest apis there should be no need to write your own SQL though.
And then the 100 lines of JS/Py ends up being way slower than the manual SQL, plus the autogen'd SQL part of it is slow, plus you can't even get the SQL query to profile without running the actual thing with prints.
You got it in one, small world huh?
Even the 'worst' of the ORMs (according to the people in these threads) makes this very easy:
Worse, that code will be executed on the receiving end, and waste a bunch of network traffic.
Great anecdote. Doesn't validate your claim
Looks like I’m not the only one, check the thread.
Still just anecdotes. Who cares about those
You’re on a forum where people share anecdotes, so presumably, you?
Are you dumb or are you just pretending? I’m going to guess the former!
The reason given to use raw SQL is for the performance not the perceived code clarity.
If you never used a CTE, maybe… The reason to use SQL is to get what you need out of a database. Performance is orthogonal to that.
Obviously, this means using raw SQL instead of an ORM, as the article was discussing the trade-offs of the two and wasn't a 101 course on what SQL is
I’m not sure why you thought I meant code clarity and not performance? It’s clear in all cases the correct SQL query will be more performant.
Confused at what you’re evening trying to say here. Are you suggesting that 100 lines of application layer code is easier to understand than 15 lines of SQL?
1. Because you referred to lines of code as the way to suggest SQL is obvious better, not performance
2. No, my point was that talking about code clarity was a distraction because to talk about lines of code as a determinant of performance is clearly wrong.
3. Tangentially, yes, if some behavior takes 100 lines of general purpose code to express, I would rather read it in the general purpose language than in SQL even if the SQL was fewer lines. It's hard to imagine why this would ever be the case though.
The correct SQL query will be more performant than what? The correct ORM call will build the same correct SQL query.
ORM is ultimately SQL
So there is no CPU cycles for the ORM itself? That’s free?
It's 2026. CPU goes brrr. It's absolutely trivial compared to the query execution time.
Profile your code sometime; I assure you, with a properly indexed query, the actual query time is insignificant compared to everything else, unless your app is Rust, C, Nim, etc.
The overwhelming majority of OLTP queries I see running on massive prod systems execute in < 1 msec. More time is spent in network RTT than execution, let alone the ORM parsing the result.
1. Network transfer time depends on size of data payload and will basically always dwarf cpu operations unless something is seriously messed up
2. Query performance is dependent on the query and table size. They won't all be < 1msec. Not everything can be an indexed O(1) lookup
3. Generally speaking, network RTT and query performance is going to dwarf time for ORM to parse the result
4. A raw SQL driver ALSO needs to parse the result if you want to do anything with the data in the general purpose language
> "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.
My personal favorite in this field is knex.js.
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).
> 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
You’re saving hundreds of lines of repetitive boilerplate code. Do you enjoy writing something like
over and over?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.
This might be the last year where we have to write code by hand unless we enjoy it though. ;-)
> 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
I've written/worked on several ORMs from scratch. ORMs are the industry standard. When I see posts like this I simply can't take them seriously. All they are saying is "I won't be a team player" and "I don't actually understand the subject matter". The reality is at a certain scale there's an entire orm team that optimizes everything. But even when there's no team involved there's no way you can write anything more optimized because I'm already at the computational limit of how far something can be optimized.
There's no (good) ORM that doesn't let you simply put your own query in.
I don’t understand this comment because in no way did I express that I’m not the team player. Seems like this is something of a sacred cow for you. Or maybe it’s a language barrier thing, but all I was trying to do was say that as a member of the data platform team, when I recommend handwritten SQL to address specific limitations of an orm, that is the response that I got. Hope this helps.
My reply was talking in general terms about the original post.
You wrote the exact opposite of my opinion here which is why I replied to your specifically:
> 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 believe strongly that good ORMs expose the ability to put your own queries in. But I can't possibly boil down all the reasons for this in one HN comment.
An ORM is not a query writer. It's a way to map SQL primitives to run time primitives in a static deterministic way backed by a suite of unit tests.
If you have a special query you wanna run that has 10 joins, 2 sub queries, and a derived view that's totally fine. No one says you can't. However remember that statistically 99.9% of all queries are not that.
What optimizations are you making here when at the end of the day performance is dictated by the schema, the query planner and the network?
I read it as "I've optimized the orm to be minimal overhead over raw sql a lot of the time".
I've actually benchmarked the overhead for my ORM against every major PHP orm that exists.
https://the-php-bench.technex.us/runs/1
But the speed is irrelevant as long as it's good enough. Notice Laravel's Eloquent at the bottom of the list yet thousands of projects are being built with it regularly.
How can I possibly condense 24 years of deep knowledge in one comment for you?
The tldr is if you're ever concatenating strings in order to build a query you're just doing what the entire job of orm is but rolling your own and chances are you'll end up with a bunch of bugs in how you handle well.... Everything.
I think your tone is a bit combative. You can certainly provide the cliff notes but if you want me to believe you’re at working at computational limits whilst talking to me about string concatenation in web dev backend languages I think the burden of proof is on you.
I don't think OP ever expected you to believe anything. He stated his experience and nothing more
Oh it was just a flex?
Ok then!
the amount of vitriol my comment generated was unexpected. i was sharing that my experience was the opposite of the comment I was replying to. So many people have read things into it that simply do not make sense to me, including this one. It wasn’t a flex, it was a statement of experience that was simply a different experience than the post I was replying to asserted as truth. As a senior member of the data team, I interact with developer teams regularly and suggest manual handwritten sql for particular performance edge cases, and I met with the response I mentioned. It’s not me not being the team player, it’s the development team using the ORM that has decided that the level of effort to maintain handwritten and ORM sequel is too much for their team to handle
> All they are saying is "I won't be a team player" and "I don't actually understand the subject matter".
I get the first part, but not the second.
Preferring to use SQL rather than an ORM + SQL is all about understanding the subject matter, which is the data as it exists in the database.
> The tldr is if you're ever concatenating strings in order to build a query you're just doing what the entire job of orm is but rolling your own and chances are you'll end up with a bunch of bugs in how you handle well.... Everything.
Yeah, so basically don't do this, except when you have to, like concatenating placeholders for a variable size IN query.
There's some classes of applications where it's hard to write all the queries because there's all sorts of mix and match stuff happening. Those are pretty much doomed to poor performance if the tables are large, so I would rather not play on those teams. On the bright side, the limit of a small table gets bigger every ram generation, and table scans on nvme aren't so painful either.
We're pointing out the same thing. Someone that uses an ORM knows when they shouldn't use them and I tend to trust that more than someone who simply refuses to use them and ends up recreating an ORM by accident.
> Someone that uses an ORM knows when they shouldn't use them
That's not been my experience. But admittedly, I've usually been brought in when the slow query is killing the database. Then I look at the query that nobody with any subject matter knowledge would have written, come up with an alternate query that will give either the same result or something close enough. Sometimes I have to then dig in and figure out how to make that happen, because the ORM user doesn't always know how to make direct queries.
But it sure did make the easy things easier, as the other poster said.
People focus on the query writing aspect of ORMs too much. That's not that primary reason you use an ORM. It's primary purpose is to hydrate objects in the runtime. If I pull a datetime from SQL there's a lot of value in having a single piece of code handle that datetime the same way across the entire stack. I can unit test that handling once across the entire code base. Very few ORMs are aware of how the data is indexed and yes a lot of people will write code that generates a complex WHERE clause against columns that aren't indexed. But that's an understanding problem. I expect someone who uses an ORM to understand SQL well. Including indexes and fixed length tables. Obviously you are encountering code made by people who don't understand this but the problem isn't the ORM. They would have made that mistake with or without an ORM.
> I expect someone who uses an ORM to understand SQL well.
From experience, I don't. ORMs are usually sold as 'learn this instead of learning SQL'. For many, the ORM creates the tables, alters the tables, and queries the tables; they don't see SQL and they don't know SQL. When that works, it works, but when it falls apart, they have to debug the SQL and the abstraction layer. I'd rather have fewer unnecessary abstraction layers.
> If I pull a datetime from SQL there's a lot of value in having a single piece of code handle that datetime the same way across the entire stack.
There's value there, datetimes are very complex, but the rest of the stuff it comes with obscures the value IMHO.
> Obviously you are encountering code made by people who don't understand this but the problem isn't the ORM. They would have made that mistake with or without an ORM.
It's hard to write the kind of complex queries I've seen by hand, and I like to imagine if you out how to do that, you'll also know why it's slow and not need my help... But the ORM is part of the problem, because when you've written bad queries by hand, and I give you a better query (or sequence of queries), it's easy to apply. When you've done it with an ORM, you may not even know where the query is made.
You can always make the ORM Model based on a view. Sometimes a background job compiling a simple result set table is the appropriate answer.
Almost all ORMs boil down their queries down to a single query handler so it's actually super easy to find the query.
My ORM for example:
I used to profile all my queries in those two methods but with tools like NewRelic there's no need to slow the code down with profiling cruft.Fair point, both "pro ORM" and "anti ORM" camps are prone to extreme stances.
I definitely don't agree with the "all queries must be executed through the ORM", and think that dogmatic stance has done a lot of damage to the ORM brand. :-/
They don't consider the ORM the second class citizen it actually is: an optional simplified alternative to normal queries, that can be used for the easy cases.
Believe what you want, but I would consider myself one of those allegedly mythical people
> 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 100x 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
> 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
You're right, that has been another "pro ORM" pitch that has gone awry and, taken to the extreme, is wrong imo.
My nuanced articulation is "you don't have to write the _boilerplate_ SQL for the 90% of just-do-some-CRUD endpoints in your enterprise SaaS application, but you 100% need to 'know SQL' for the last 5-10% of ~reporting/analytics queries that the ORM is going to mess up".
Personally I find the 90% boilerplate SQL is easy enough to write that injecting an ORM into the process doesn't make much sense
But that's just me
AKA making the easy parts easier while making the difficult parts harder.
The difficult parts are just literally a raw SQL string so how is that any harder?
That you somehow have to adapt the results into the same format the ORM uses. And has to adapt the parameters into taking data from the ORM. Or has to split your entire functionality from the ORM so you can actually access the database directly without one part of your code interfering with the others.
No? ORMs don’t preclude writing raw SQL, so it’s just making the easy parts easier while leaving the difficult parts the same.
The ORMs I've tried tend to produce some pretty specific table structures that are a pain in the ass to work with outside of the ORM, imo
One of the sticking points I've found in the past is if I create a new table outside of the ORM, it doesn't know how to use it. Then if I try to add it to the ORM's model it doesn't use the existing table, it creates a conflict. Annoying stuff like that
> 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.
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.
Just one quick note...
> ...(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.
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.
It's a bit aside, but what i love about ORM frameworks is that they try to find the universal interface to multiple database backends. For basic CRUD it's nice: test on sqlite deploy wherever.
That's partly the problem ORMs. Lowest common denominator that prevents you from leveraging a lot of the power of your actual database.
I agree that "learn SQL" is a necessity, but I'm not sure the article makes a good argument against using ORMs.
ORMs are just a layer of abstraction. Like any abstraction, they make some tradeoffs that can get you into some sticky situations like inefficient queries mentioned in the article.
But, if you understand the tradeoffs, you can use them for what they're good for (standardization & simplification & in-codebase schema definitions & so on) and usually drop down to SQL whenever there's a particularly necessary case.
SQL is awesome and you’ll never get the best out of your database unless you learn to program the damn thing and bit hide behind some abstraction.
We do programmers always need a library?
Program the damn thing.
I have the same response every time I hear this: like 95% of application CRUD plumbing is much better served by an ORM. It gives your application typed versions of your data, lets you work with objects rather than rows, which are almost always more useful, is much easier to read, etc. Then for the 5% of critical/complicated queries: just use SQL there. In fact your ORM almost certainly has an escape hatch for you to do that.
Also, NoSQL taught me to love SQL.
Especially Dynamo DB.
I feel like ActiveRecord has none of these problems, but I also feel some strong confirmation bias.
Can anyone that has used ActiveRecord share their opinion?
ActiveRecord does have the problem of excess joins though.
I am no SQL God by any means, but I am quite proficient. Despite my SQL skills, I cannot give up EF Core.
Even when using other languages, I just pine for LINQ/EF Core. It's truly the best ORM in my opinion. Also, even if one does not want to use the LINQ or the Query syntax (I forgot what it was called), the ability to execute SQL is also still a game changer.
As someone who has historically spent a lot of my time with C#, and now spend most of my days writing python… LINQ is typically what I miss most from C#… (obviously aside from static types and compiled binaries).
I thought ORMs are trying to solve the problem of type mapping between SQL and your backend language.
Admittedly, this doesn't end up being great, but it seems hard to solve this well in other ways, as much as I wish I could write SQL and get types for free.
I thought this was well put. https://web.archive.org/web/20160301022121/http://www.revisi...
A now defunct site discussing why ORM is a poor map.
I've been using ORMs since the late-90s with WebObjects (I still have a running product on the internet that uses WebObjects). I've used I don't even know how many other orms. But it's always been a mix of orm and raw sql, so yes learn sql. Especially useful for reporting.
Mybatis was a thing even back then... you still need a domain model after all
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.
Oh no, this meme again. Of course you should learn SQL. But also, you can use a library to help generate SQL based on classes and objects that you change, so you don't have to repeat yourself. Why don't you use both?
ORMs may be convenient, but only as long as you stay within their limitations. One you surpass those, things get much more complicated and messy. SQL does not have that artificial breaking point.
Ecto in Elixir has a decent balance and is nice to use though Elixir doesn't have objects, but the abstraction layer is handy.
I don't like the title, it implies that the only reason for using an ORM is not knowing SQL, which is obviously not the case.
Every time I tried to do a project without an ORM, using only raw SQL, I inevitably ran into:
- serialization/deserialization boilerplate. Like, having to manually map values returned by the DB library to object (or named tuple, or structure) properties
- poor code reuse, having multiple very similar queries that have just one small difference
- extra pain in changing DB schema. Adding a field requires to go and manually edit many queries
Anti-ORM crowd never gives a good answer to these issues.
Instead, they push strawman attacks like "oh, you only use ORM, because you can't write raw SQL". I can absolutely assure you that this is not the case. Every time I use an ORM (SQLAlchemy mostly, the one mentioned in the article) I am 100% sure what SQL do I want it to produce and what SQL will a particular ORM invocation produce.
Does my opinion on SQL and ORMs matter anymore? What does Claude think about ORMs an SQL? So far Claude seems to be content with my existing patterns of using JPA/Hibernate. We've been having this conversation since the early 2000s. Will we have it next year? Maybe just for fun... to pretend we are still relevant :(
Back in the day I made few ORMs for myself exactly because I knew SQL. It's not great.
> Most of that has been with SQLAlchemy (which I quite like) and Hibernate (which I don’t)
Can the OP expand on why this is? Just curious.
What ORMs have taught me: just do not learn SQL. I don’t for 21 years of coding.
Something I'd like to see is for someone to finally come to the realisation that the right thing to do is to make the front-end web templating language truly polyglot and support SQL natively, without an ORM wrapper.
For example, the ASP.NET Razor syntax allows HTML and C# code to be interspersed surprisingly freely:
Just picture the same kind of thing, but with SQL expressions freely interspersed with the programming language.Just like how Cargo, NuGet, NPM, etc... can import packages and/or how you can cross-reference projects in build systems, web apps should be able to reference a database schema project directly, importing the SQL definitions without any explicit "mapping". If the SQL changes, the type changes, and the build system picks that up automatically without any additional manual steps.
.NET with EF Core is almost there, and I've seen some half-hearted attempts in various languages over the years, but it's like the industry has an allergy to the concept.
Ur/Web is probably the closest to the idealised concept, and I think that's what I read years ago that put the dream in my mind: https://dl.acm.org/cms/attachment/feb131ab-37e1-4638-be17-ab...
ORM is a great tool for data input. Complex output I always write the old and good raw SQL query.
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.
I use both. Gorm being my favorurite
ORMs make it hard to write code that allows SQL injection.
[flagged]
[dead]
Next step is go down one more level to ditch SQL and learn LMDB and/or RocksDB.
LLMs are better at writing raw queries now and knowing the consequences of how it fits in your architecture (if you ask)
So I think the ORM debate could be over
postgresql is a beast
Even before LLMs ORMs are good enough to cover most of the use cases. Only some complicated use cases needs raw SQL. So you can use both.
ORMs taught me that relational databases are an operational anti-pattern.
NoSQL for operational data storage is more efficient and cost effective.
ORMs were a regression test that exposed unnecessary complexity.
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.
This implies nosql data stores are not ACID capable. Mongo is fully capable and DynamoDB is mostly capable.
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.
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.
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...
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.
They are also the most expensive operational data stores and the younglings know almost no SQL or set theory.
Schema management is the single worst part of deployments.
If I build on DynamoDB vs RDS I can save 10x.
[flagged]
> August 3, 2014
That's important. Because now days it's trivial for LLMs to translate ORM to SQL and vice-versa with ~100% accuracy. I haven't written any raw SQL (only Active Record) in about two years, and the odd time I blunder with AR and create an n+1 I find out about it via error tracking (e.g. Sentry) a few minutes later and fix it. No biggie.
There's also an additional layer of protection in that using AI on the codebase can spot SQL blunders incidentally (i.e. you ask about X, and the AI does X but also says "Not asked, but flagging for your attention: problem with SQL on line 256 etc.."