Rendered at 02:51:53 GMT+0000 (Coordinated Universal Time) with Cloudflare Workers.
tengbretson 10 hours ago [-]
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.
sandreas 10 hours ago [-]
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.
ElectricalUnion 8 hours ago [-]
> 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?
Charon77 4 hours ago [-]
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.
sandreas 8 hours ago [-]
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.
Kon5ole 6 hours ago [-]
>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?
sdevonoes 7 hours ago [-]
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
nesarkvechnep 5 hours ago [-]
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.
ibejoeb 9 hours ago [-]
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.
zbentley 9 hours ago [-]
> 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.
ibejoeb 8 hours ago [-]
> 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.
ElectricalUnion 8 hours ago [-]
> 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.
arwhatever 5 hours ago [-]
> 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.
frevib 8 hours ago [-]
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.
Kinrany 10 hours ago [-]
I'd rather take a mess of ad-hoc interfaces. Forcing people to do domain modeling does not go well.
j45 6 hours ago [-]
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.
recursivedoubts 9 hours ago [-]
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.
vandahm 10 hours ago [-]
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.
zbentley 9 hours ago [-]
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.
valzam 11 hours ago [-]
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.
rmunn 11 hours ago [-]
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.
NetOpWibby 11 hours ago [-]
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.
ORMs build queries for you, but a query builder does not need to be an ORM.
dadie 11 hours ago [-]
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.
ambicapter 10 hours ago [-]
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.
meindnoch 10 hours ago [-]
That's why it's called SQL aka String Query Language. The queries are just strings.
gorgonian 9 hours ago [-]
Are you being cheeky? The S stands for Structured.
pjmlp 11 hours ago [-]
Which is why one is better off using IDEs, especially those from DB vendors.
ai_slop_hater 11 hours ago [-]
The problem is that there is no "SQL" — it's different for every database.
allthetime 11 hours ago [-]
For the vast majority of simple use cases the common subset of all popular SQLs is exactly the same. Otherwise… just use Postgres
11 hours ago [-]
photios 11 hours ago [-]
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.
dagss 10 hours ago [-]
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.
threethirtytwo 11 hours ago [-]
[dead]
geophile 10 hours ago [-]
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.
runevault 10 hours ago [-]
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.
sshine 8 hours ago [-]
> 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.
jghn 9 hours ago [-]
> built one for Java, in the early 90s
So was your ORM for Oak? Java didn't hit the public sphere until 1995 IIRC
geophile 4 hours ago [-]
No it was Java. Sorry, it was late 90s.
noisy_boy 11 hours ago [-]
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.
le-mark 11 hours ago [-]
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.
saltcured 9 hours ago [-]
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.
frollogaston 10 hours ago [-]
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.
pjmlp 10 hours ago [-]
Still applies today in data science, one is expected to master SQL alongside Python and Excel.
bluefirebrand 11 hours ago [-]
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
crispyambulance 9 hours ago [-]
> 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.
bluefirebrand 8 hours ago [-]
> 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
noisy_boy 9 hours ago [-]
That is exactly what I was thinking. There is such a low barrier to entry with an outsized payoff.
robertclaus 10 hours ago [-]
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.
nodamage 5 hours ago [-]
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.
setr 5 hours ago [-]
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
bb88 3 hours ago [-]
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.
laszlokorte 10 hours ago [-]
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.
dmeijboom 10 hours ago [-]
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
frollogaston 10 hours ago [-]
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.
teliskr 11 hours ago [-]
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.
jemiluv8 8 hours ago [-]
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.
prmph 10 hours ago [-]
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.
clutter55561 11 hours ago [-]
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.
pier25 9 hours ago [-]
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.
jkdufair 9 hours ago [-]
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.
sulam 9 hours ago [-]
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!
ahartmetz 9 hours ago [-]
I haven't used stored procedures yet, but even ON DELETE CASCADE is super convenient and I suspect somewhat underused by SQL scaredy cats.
lelanthran 8 hours ago [-]
> 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!"
sulam 8 hours ago [-]
I think the stored procedure equivalent would be a "on delete, cascade these tombstones" -- both safer and cleaner.
wavemode 8 hours ago [-]
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.)
ahartmetz 8 hours ago [-]
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.
jeswin 9 hours ago [-]
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.
exabrial 10 hours ago [-]
The purpose of an orm is not to "stop writing SQL". In order to effectively use a layer abstraction, you must be able to use the layer below the abstraction.
I 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.
10 hours ago [-]
scritty-dev 11 hours ago [-]
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
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.
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.
dools 10 hours ago [-]
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.
hparadiz 10 hours ago [-]
That's a query writer. Not an ORM.
dools 6 hours ago [-]
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)
hparadiz 5 hours ago [-]
You are outputting generic QueryRow classes in your code for all results. That doesn't make it an ORM.
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.
vindex10 10 hours ago [-]
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.
pull_my_finger 10 hours ago [-]
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.
Arch-TK 10 hours ago [-]
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.
AlotOfReading 7 hours ago [-]
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.
Arch-TK 1 hours ago [-]
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.
bot403 10 hours ago [-]
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...."
sbuttgereit 11 hours ago [-]
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.
stephen 11 hours ago [-]
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.
maxwellg 10 hours ago [-]
> 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.
hn_throwaway_99 8 hours ago [-]
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.
stephen 9 hours ago [-]
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.
hn_throwaway_99 8 hours ago [-]
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).
hatefulheart 11 hours ago [-]
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.
jghn 11 hours ago [-]
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.
hparadiz 11 hours ago [-]
This is inevitably what happens every single time so just use an ORM and stop being stubborn.
jghn 10 hours ago [-]
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.
hparadiz 10 hours ago [-]
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.
zadikian 10 hours ago [-]
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.
hatefulheart 10 hours ago [-]
You got it in one, small world huh?
pjmlp 11 hours ago [-]
Worse, that code will be executed on the receiving end, and waste a bunch of network traffic.
nfw2 11 hours ago [-]
The reason given to use raw SQL is for the performance not the perceived code clarity.
baq 10 hours ago [-]
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.
hatefulheart 11 hours ago [-]
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?
airstrike 10 hours ago [-]
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
hatefulheart 10 hours ago [-]
So there is no CPU cycles for the ORM itself? That’s free?
airstrike 8 hours ago [-]
It's 2026. CPU goes brrr. It's absolutely trivial compared to the query execution time.
7bit 10 hours ago [-]
Great anecdote. Doesn't validate your claim
hatefulheart 10 hours ago [-]
Looks like I’m not the only one, check the thread.
7bit 10 hours ago [-]
Still just anecdotes. Who cares about those
hatefulheart 9 hours ago [-]
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!
swasheck 11 hours ago [-]
> 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
hparadiz 11 hours ago [-]
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.
swasheck 9 hours ago [-]
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.
hparadiz 9 hours ago [-]
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.
toast0 9 hours ago [-]
> 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.
hparadiz 9 hours ago [-]
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.
toast0 9 hours ago [-]
> 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.
hparadiz 9 hours ago [-]
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.
toast0 8 hours ago [-]
> 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.
hparadiz 7 hours ago [-]
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.
hatefulheart 11 hours ago [-]
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?
bot403 10 hours ago [-]
I read it as "I've optimized the orm to be minimal overhead over raw sql a lot of the time".
hparadiz 10 hours ago [-]
I've actually benchmarked the overhead for my ORM against every major PHP orm that exists.
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.
hparadiz 10 hours ago [-]
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.
hatefulheart 10 hours ago [-]
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.
7bit 10 hours ago [-]
I don't think OP ever expected you to believe anything. He stated his experience and nothing more
hatefulheart 10 hours ago [-]
Oh it was just a flex?
Ok then!
swasheck 9 hours ago [-]
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
stephen 11 hours ago [-]
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. :-/
HelloNurse 11 hours ago [-]
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.
talon8635 8 hours ago [-]
Believe what you want, but I would consider myself one of those allegedly mythical people
bearjaws 10 hours ago [-]
> 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.
jghn 10 hours ago [-]
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
marcosdumay 10 hours ago [-]
> 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.
DanielHB 10 hours ago [-]
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.
10 hours ago [-]
bluefirebrand 11 hours ago [-]
> 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
stephen 11 hours ago [-]
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".
marcosdumay 10 hours ago [-]
AKA making the easy parts easier while making the difficult parts harder.
airstrike 10 hours ago [-]
The difficult parts are just literally a raw SQL string so how is that any harder?
marcosdumay 8 hours ago [-]
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.
jcgl 10 hours ago [-]
No? ORMs don’t preclude writing raw SQL, so it’s just making the easy parts easier while leaving the difficult parts the same.
bluefirebrand 3 hours ago [-]
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
bluefirebrand 11 hours ago [-]
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
comrade1234 9 hours ago [-]
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.
Kaliboy 11 hours ago [-]
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?
dzonga 8 hours ago [-]
ActiveRecord does have the problem of excess joins though.
A now defunct site discussing why ORM is a poor map.
Demiurge 9 hours ago [-]
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?
Waterluvian 11 hours ago [-]
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.
zadikian 10 hours ago [-]
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.
vova_hn2 8 hours ago [-]
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.
nomilk 10 hours ago [-]
> 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.."
armdave 8 hours ago [-]
> 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.
add-sub-mul-div 11 hours ago [-]
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
flir 11 hours ago [-]
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.
mrweasel 11 hours ago [-]
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.
airstrike 10 hours ago [-]
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
3eb7988a1663 10 hours ago [-]
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.
At yet people (mostly) skip SQL and learn some ORM.
r2ob 9 hours ago [-]
ORM is a great tool for data input. Complex output I always write the old and good raw SQL query.
bob1029 11 hours ago [-]
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.
andrewstuart 10 hours ago [-]
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.
jdw64 10 hours ago [-]
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.
danlugo92 11 hours ago [-]
Also, NoSQL taught me to love SQL.
pjmlp 10 hours ago [-]
Especially Dynamo DB.
hirvi74 9 hours ago [-]
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.
ralusek 9 hours ago [-]
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.
classified 9 hours ago [-]
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.
panny 9 hours ago [-]
>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.
gedy 11 hours ago [-]
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.
11 hours ago [-]
appganvwale 10 hours ago [-]
[flagged]
ai_slop_hater 11 hours ago [-]
Next step is go down one more level to ditch SQL and learn LMDB and/or RocksDB.
yieldcrv 10 hours ago [-]
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
senfiaj 9 hours ago [-]
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.
ChicagoDave 11 hours ago [-]
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.
zsoltkacsandi 10 hours ago [-]
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.
ChicagoDave 5 hours ago [-]
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.
hoppp 5 hours ago [-]
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.
ChicagoDave 1 hours ago [-]
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.
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.
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 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?
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.
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?
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.
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.
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.
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.
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.
Ideally models are generated from SQL schemas, which you map to domain models.
One example is starting with NOSQL and proceeding to learn how to make it into a relational database.
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'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.
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
ORMs build queries for you, but a query builder does not need to be an ORM.
[1] LLMs make these very easy to handle.
The choice of DB is arguably more important than the choice of backend language.
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.
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.
So was your ORM for Oak? Java didn't hit the public sphere until 1995 IIRC
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.
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
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
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.
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.
PS: I still exported the table before deploying this fyi.
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 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 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.
Sooner or later you are going to hit enter, wait a few seconds and say "oops!"
I am not using ON DELETE CASCADE to be clever - the referenced data is genuinely required.
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.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.
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...
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.
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)
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...
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.
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.
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.
> ...(although things like Postgres’ hstore can help)...
Back when this blog post was written, this advice would have been reasonable. Today, I don't know anyone reaching for hstore since the more featureful json support was added.
- "the 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/
This is exactly why I hate ORMs. As I always put it "ORMs make the easy stuff slightly easier, and they make the harder stuff way harder".
If you're just using an OEM for the "select * from table where ID in ...", then you're saving practically nothing by using an ORM - just learn to write SQL, because as you put it, you're going to have to use it anyway for places where it falls over. There are lighter weight options that do basic stuff like transaction management and binding result sets to object properties that are much less of a PITA than ORMs.
In practice I've seen people try to use the ORM features first for places that need complicated SQL (which is a reasonable assumption), only to waste a boatload of time before concluding the ORM makes stuff harder.
Query builders like these are my personal favorite from a productivity perspective! The point of a query builder is to dynamically build SQL statements that have many subtle variations (do we want to filter by EmailID or PhoneID here? What about a subquery? Did the caller want all results, or just results where $field=X?). They're basically one level above string templating for SQL generation, and often have niceties around ser/de and transaction management as you mentioned.
Because they are primarily about query generation, it feels _very_ natural to pop off the hood and write raw queries directly when necessary. You can usually use the transaction management and ser/de parts with raw queries, too.
My personal favorite in this field is knex.js.
Years ago I was working on a project that used knex, then I serendipitously discovered slonik through this blog post, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41... (slonik has subsequently had lots of development since then). I decided to rewrite the entire persistence layer from knex to slonik over a long weekend and I'm so happy I did. I liked slonik so much that it was the only time I personally contributed to a programmer through GitHub Sponsors.
I.e. regardless of how easy it is to write `INSERT authors (...) VALUES (...)`, with an appropriately cute/ergonomic query builder to bind the variables/POJOs ... where does your business logic actually go?
Whenever you insert an author, are you always enforcing the same validation logic? Whenever you update a book, are you always updating the derived fields that need updated?
Getting the business rules right is "the actual hard stuff" imo, and nothing I've seen a query builder help with; it's always left as an exercise to the reader to reinvent their "business logic wrapped around POJOs" adhoc in their codebase.
It's like people can't just let go.
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?
ORM is ultimately SQL
Are you dumb or are you just pretending? I’m going to guess the former!
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
There's no (good) ORM that doesn't let you simply put your own query in.
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.
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.
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.
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.
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.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.
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.
Ok then!
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. :-/
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.
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.
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
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".
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
But that's just me
Can anyone that has used ActiveRecord share their opinion?
A now defunct site discussing why ORM is a poor map.
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.
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.
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.."
Can the OP expand on why this is? Just curious.
2026: people respond with indignance that they should have to learn anything now that there's a shortcut
But the current shortcut du jour is pretty damn good at writing SQL.
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.
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
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.
We do programmers always need a library?
Program the damn thing.
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.
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.
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.
I'm talking about my experience, not generalizing to all DBAs of course. And of course ORMs introduced performance issues, etc.
So I think the ORM debate could be over
postgresql is a beast
NoSQL for operational data storage is more efficient and cost effective.
ORMs were a regression test that exposed unnecessary complexity.
Relational databases are incredibly flexible even if you have a NoSQL mindset, you can do data modelling like that in Postgres too with jsonb data types.
The problem with "schema change", and I did this for decades, is that it's always a massive blocker. In some companies the data architects had to approve and implement schema changes. You could wait days for that. NoSQL allows you to modify the document surface in mostly non-breaking change ways OR it's easier to version your APIs to handle different document versions.
Simple CRUD: Any data store is fine. Complex multiple bounded contexts: Choose the appropriate data store for each bounded context accordingly.
My point was no one should be reaching for a relational database or starting with an ERD to build a system. Document behaviors. Model the system. Let the system decide what data storage it requires.