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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 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.
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'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.
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 always disliked ActiveRecord, but I figured ORMs don't have to be ActiveRecord. I created this library 14(!) years ago not too long before this article was written https://github.com/iaindooley/PluSQL
The idea is that you like SQL, but it gets repetitive writing joins and accessor code. I had always hoped it would catch on as a pattern: no boilerplate, automatic mapping to objects in your code of any query (whether generated by the ORM or passed in as a raw query) and easy to override/dynamically build bits of the query as you pass the object around.
I 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 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.
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...."
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 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.
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.
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 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.
> ...(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.
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] :-)
> "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.
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.
> 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.
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.
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?
> 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.
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.
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 ..."
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 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
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.
> 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".
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 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.
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
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.
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.
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.
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.
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.
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.
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.
I'd rather take a mess of ad-hoc interfaces. Forcing people to do domain modeling does not go well.
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.
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.
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.
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.
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.
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.
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
A query builder is not an ORM.
ORMs build queries for you, but a query builder does not need to be an ORM.
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.
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.
The problem is that there is no "SQL" — it's different for every database.
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.
For the vast majority of simple use cases the common subset of all popular SQLs is exactly the same. Otherwise… just use Postgres
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.
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.
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.
Still applies today in data science, one is expected to master SQL alongside Python and Excel.
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
That is exactly what I was thinking. There is such a low barrier to entry with an outsized payoff.
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.
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'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.
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 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.
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 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.
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...."
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
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.
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.
> 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.."
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 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?
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.
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.
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/
> "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.
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.
> 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.
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?
Worse, that code will be executed on the receiving end, and waste a bunch of network traffic.
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.
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?
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?
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!
> 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.
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!
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 ..."
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 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
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.
> 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".
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?
No? ORMs don’t preclude writing raw SQL, so it’s just making the easy parts easier while leaving the difficult parts the same.
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
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.
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.
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
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
At yet people (mostly) skip SQL and learn some ORM.
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.
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.
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.
Also, NoSQL taught me to love SQL.
Especially Dynamo DB.
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
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.
Next step is go down one more level to ditch SQL and learn LMDB and/or RocksDB.
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.