Is ORM still an anti-pattern?

I've been doing ORM on Java since Hibernate was new, and it has always sucked. One of the selling points, which is now understood to be garbage, is that you can use different databases. But no-one uses different databases. Another selling point which is "you don't need to know SQL", is also garbage. Every non-trivial long-lived application will require tweaks to individual queries at the string level. The proper way to build a data layer is one query at a time, as a string, with string interpolation. The closer you are to raw JDBC the better.

Oh yeah, another bad reason for ORM: to support the "Domain Model". Which is always, and I mean always, devoid of any logic. The so-called "anemic domain model" anti-pattern. How many man-hours have been wasted on ORM, XML, annotations, debugging generated SQL, and so on? It makes me cry.

In any sufficiently complex application, something like a query builder that allows different parts of the application to work together to lazily compose queries (vs combining/filtering/sorting the data in memory) will be created at some point.

ORM is a tool that can make this easier, it's also a tool that can make it easier to shoot yourself in the foot (ie by making it easy to create N+1 queries without knowing). Like all tools, there are tradeoffs that need to be accounted for together with the actual use case to make a decision. Operating on SQL statement strings is not something I'd recommend in any case.

My dislike of ORMs mainly stems from the tendency to treat modern SQL engines as glorified dumb bit buckets.

Where a CTE or LATERAL join or RETURNING clause would simplify processing immensely or (better yet) remove the possibility of inconsistent data making its way into the data set, ORMs are largely limited to simplistic mappings between basic tables and views to predefined object definitions. Even worse when the ORM is creating the tables.

SQL is at its heart a transformation language as well as a data extraction tool. ORMs largely ignore these facets to the point where most developers don't even realize anything exists in SQL beyond the basic INSERT/SELECT/UPDATE/DELETE.

Pivot tables. Temporal queries. CUBE/ROLLUP. Window functions. Set-returning functions. Materialized views. Foreign tables. JSON processing. Date processing. Exclusion constraints. Types like ranges, intervals, domains. Row-level security. MERGE.

It's like owning a full working tool shed but hiring someone to hand you just the one hammer, screwdriver, and hacksaw and convincing you it's enough. Folks go their whole careers without knowing they had a full size table saw, router, sander, and array of wedges just a few meters away.

ActiveRecord has always felt great to me: yes there are some corner cases to deal w/ but it hits the 80/20 spot, let's you kick out to SQL when you need to, adds a lot of excellent life-cycle related hooks that clean up domain logic, and is pretty easy to reason about

pragmatic ORMs that don't try to hide everything behind a "bundle" or introduce their own query language are great, it's the ones that try to totally "solve" the ORM problem that are hard to work with

That's begging the question: it never was.

Some ORMs are awful to work with, but that doesn't mean the genre is a bad idea. I've loved working with SQLAlchemy over the years because it doesn't try to re-invent the whole idea of SQL. Instead, it gives you a convenient layer for building queries, passing them around, etc., while it concentrates on getting the details right so you don't have to futz around with them.

As with anything, there are situations where it works well and where it doesn't, and the tech can be easily abused or misused. ORMs (in e.g. dotnet Entity Framework), particularly when working database-first, can be a great asset.

If you are working in DBs with hundreds of tables and all setup with proper foreign key relationships, an ORM can help navigate a complex schema and provide compile time sense checking when things change. Embedded SQL is a double edged swords with perceived performance benefits but with very difficult to maintain "hard coded" verbose SQL particularly for joins. I'm talking about when there are hundreds of tables, not when you have 5-10 tables.

I've no ambition to convince anyone one way or the other, but don't believe everything your read on the internet about best practices especially since people may be talking about something quite different to what you're looking at. Some people will be pretty shocking developers regardless of which tech is being used.

A point I'm missing is: without using an ORM (or however else you're calling your database interface) you will eventually end up writing and maintaining one yourself, at least for CRUD stuff, and it will be filled with weird bugs, edge cases and security holes.

> A common complaint about ORMs is that they break two of the SOLID rules. If you aren’t familiar with SOLID, it is an acronym for the principles that are taught in college classes about software design.

And that right here is the problem. Academia is teaching people stuff that makes sense in an academic environment where time, maintainability, money and effort aren't of a concern (as long as you have grant money, but that's not relevant here) - and that leads to a very nasty collision when fresh graduates enter the workforce. They're used to shaving yaks and reinvent wheels all day long in the quest for a "perfect" solution, and if the company doesn't have (enough and qualified) senior/lead developers to catch that, you end up with ... very interesting products, budget overruns and/or dumpster fires. Or a combination of all of them.

ETA: There's something that can go even worse: when stuff designed by and for academics enters public availability. The best/worst example for that is OpenStack... enough knobs and twists to support the specialized environment of CERN and probably hundreds of universities worldwide, with components modularized to an insane degree, but almost impossible to get started with because it's so byzantine.

IMO SQL is one case where you should "just program the damn thing".

There's no point at all in learning some SQL or ORM library. Learn and write SQL directly.

Database access is a fundamental thing you'll be doing for your entire career probably. Best to learn the actual technology directly.

This is true for certain other technologies too - CSS for example - don't learn a CSS library - learn and use CSS. In fact that's true really for almost all aspects of front end development - don't use a "forms library", program the damn forms APIs in the browser.

Also my personal experience is that SQL is much easier to learn than certain ORMs anyway.

Don't waste the cost of learning the wrong thing.

I’m a big fan of the “middle ground” micro-ORMs like Dapper or Diesel.

Write your queries in SQL, but you get to have your strong typing and automatic result set to objects mappings. Productivity combined with the full-featured “real” query language.

Also, I’ve become a fan of using the object mappers only for read-only queries. Updates occur via the command pattern — stored procedures. These can be mapped through to look like native methods.

This thread only underscores the total mindshare dominance of ORMs. The implicit alternative to ORMs in almost every comment is raw sql in string literals in app code.

For 6 years now, we've had tools that let you use sql as a language, then generate the wrapper exposing your query to your app as a method. (queryfirst, pgtyped, pugsql, sqlc) This is (or could be) a paradigm shift. It's clearly, demonstrably a superior way of working, but it exists on the fringes because ORMs (and this unchanging discussion) have sucked all the oxygen.

It's not an antipattern and it will never be. Like many solutions there are scenarios in which using an ORM is the simplest thing to do.

Also there are many different implementations of ORMs each with different features (Martin Fowler has a list from his Patterns of Enterprise Application Architecture: https://www.martinfowler.com/eaaCatalog/index.html). For example in the JVM world, jOOQ and Hibernate provide very different solutions.

* jOOQ provides a table data gateway (when used without records)

* Hibernate: unit of Work, Lazy load, multiple inheritance strategies, Query object, Versioning, lifecycle events, and much more. Lot of the grief about Hibernate comes mainly from people who never bothered RTFM.

Having said the above, if one needs flexibility and high performance (with complex queries), using vanilla SQL is invariably the best way.

I may be in the minority here (based off of other comments), but using raw SQL commands has been more performant, easier to write, easier to understand, easier to learn, and easier to build complex queries/ commands than an ORM has ever provided.

Updating some records in a CTE and using the results in a subsequent SELECT? It may take a minute or so on stack overflow, but significantly less than it would with SQLAlchemy.

Want to make a composite primary key? I have no idea how you would do it with most ORMs, but it’s relatively straightforward with SQL.

A lot of times the stuff you want to do won’t even be available in your ORM, but the person lobbying for using it always pleads “it’s okay, it still allows you to make raw queries! We have an escape hatch!” Then when you start writing raw queries, your PRs get rejected: “Please stick to the ORM”.

I don’t understand people’s love of ORMs instead of simply learning SQL, it seems to bring on myriad headaches for nearly zero benefit.

There seem to be two kinds of ORM's.

One is based on the domain models and will either generate tables and columns or has some kind of mapping from domain model -> database.

The second type does the inverse approach and generates domain models from an existing database.

I vastly prefer the second version as the database is and should be the source of truth and generating types from the existing database as well as queries (you write sql and it generates type safe wrappers for your language).

The first approach has been done quite a lot and is very error prone and there will be drift from your domain model compared to the database which can lead to subtle errors.

The second approach is very flexible and you can still write SQL as you would have otherwise done but you can integrate it easily in the code as the type safe wrappers are generated automatically. Also your domain model (database -> model) is always up to date.

ORMs are all problematic, but some are less problematic than others. Some years ago I started writing more Elixir with Phoenix and Ecto, and I quickly realized just how much better Ecto is when compared to ActiveRecord. I wrote about it back then [1] but the gist of is it: SQL > Query Builders with some ORM elements > ORM qua ORM, and the less you blur the boundary between your application and the database the better.

1: https://blog.shrikanthup.com/2021-12-04-ecto-data/

I never understood the rant on ORMs. From my point of view the main problem they solve is get rid of overinflated data access objects. In big project with complex data structure in database you can get hundreds of tables and you end up with data access objects with more than hundred methods like:

"getOrderById",

"getOrderByUser",

"getOrderByIdWithItems",

"getOrdersBySupplierWithOrderItemsAndUserAddressIAmTiredOfMakingNewNamesForMethodsPleaseKillMe"

ORMs elegantly get rid of most of these stuff and you and up with code like user.getOrders().with("item") or something like that. The downside of that is it becomes a bit harder to profile if some offender method is doing hundreds thousands queries to a database but still possible with a little bit of more work.

There are situations when you need something more complex than "SELECT ... JOIN ... WHERE ...". For example you might want to force server to use some particular index or you use subqueries or CTEs. In that case you can use native query functionality when you pass a raw SQL query to ORM and it just populates data objects with the results of a that raw SQL. Most of ORMs have such functionality. But it's very rare when it's needed, usually you have several occasions per service if it's complicated enough.

I love the Django ORM. It's concise, generates good queries and just works.

However, there's a danger - for complex applications, you want neatly separated domain objects and data layer. Django makes it really easy to make your model your domain object, and at some point that starts to hurt.

But a large part of any app is just objects backed 1-on-1 by a database table, and CRUD actions on them. It's fine.

Just watch out for those parts where the separation is needed, and separate them.

People will try to reinvent the same thing over-and-over - mostly because of missing core knowledge. So the cycle will go this way: discard ORM and use native SQL queries. Make your own management of what has changed and what not. Make your own (object) relationship/collection management. Make your own caches of loaded objects. And - the most non-obvious but the real deal-breaker: deal with complex updates where MANY objects are involved. Then they will come and start praising ORM. Because they learned the core knowledge...

A problem that I haven’t seen a good solution to is that most (all?) ORMs are also caches. And once you step outside the object box, cache invalidation is incredibly hard. Taking an example from this post:

    await postRepository
      .createQueryBuilder()
      .update(Post)
      .set({ status: 'archived' })
      .where("authorId IN (SELECT id FROM author WHERE company = :company)", { company: 'Hooli' })
      .execute();

What happens to all the pre-existing Post objects when you do this?

(Maybe some ORMs try to get this right. It’s a very hard problem. Certainly the ORMs I’ve used don’t, but I admit I haven’t used very many.)

"My big complex application is big and complex! It must be my (orm|monlith|OO|dynamic typing|database)!"

No, it is just a big complex app is complex. You can shuffle the complexity around, but you can't magic it away. You can change a pattern here or a tool there and make some subset easier to work on, but you've probably just made some other subset harder to work on.

I have inly used Django's ORM and I don't like writing queries in it but I think it's fantastic because

* It generates the sql to create the DB very competently.

* It creates migration scripts

* Let's me use SQLite on my dev machine but something else on my server

and this means that

* Version control is very easy

* Testing and bisecting older versions is not a problem

That makes it all worth it for me.

My experience is that ORMs are like training wheels for fledgling teams/developers. They're a fantastic way to make sure everyone is following the same pattern/rules and that changes will more-or-less work over time without too much headache. If you have a team with developers who are actually skilled in SQL and use things like Dapper when writing code, you will actively detest the notion that the raw SQL should be abstracted away.

I also believe that you are more likely to completely go off rails if constrained at domain modeling time into an ORM-compatible schema. The real world usually has some cranky things like circular dependencies and self-joins. Modeling these effectively with an ORM can be a nightmare (in my experience). If you have access to SQL, determining how to manage a circular dependency is much more obvious & direct. The effect is that you are free to model you domain in such a manner that it more closely resembles actual reality. In turn, this makes it easier to talk about and write the SQL.

There is 100% a virtuous cycle here. The more you force yourself to endure the SQL, the better every aspect around it will become. You will feel the fire of a bad schema the moment you see if it you have been spending thousands of hours troubleshooting queries over bad schemas. Knowing when an arrangement of tables is "smelly" before they see any data can save you so much frustration. I think a lot of people who hide from SQL aren't hiding from SQL. They're hiding from someone else's horrible interpretation of a problem domain.

The “automagic” nature of ORM frameworks makes it easy for developers to inadvertently implement “N+1 selects” access patterns. Most ORM frameworks will also offer a number of ways to mitigate this issue, though, and if you are going to use an ORM you must thoroughly read the documentation to understand how it works and what options are available to accessing data.

That being said, the worst codebase I ever worked on was created by a team who felt that ORMs were “evil”. It was littered with N+1 select issues from developers trying to reuse snippets of code without thinking through the performance implications.

So I’d say it really comes down to the skill/experience of the engineers to use tools correctly rather than what tools you use.

The issue of pattern/anti-pattern is, I think, redundant. It's a little bit like calling something an "ism" to shutdown discussion.

The problem is one of use case: is using an ORM the best decision for my use case? I've seen both sides of that decision play out, and success and failure for each of those decisions. I've built ORMs in support of those decisions (1/10 would not recommend).

If the problem you have in front of you is a simple CRUD use case with Fat Client then it makes a tonne of sense to use an ORM. As other people mention, you may find yourself with a non-trivial problem created by the ORM; there are well defined approaches to those problems.

But, if the model you are dealing with is inherently complex, and there are a good number of business rules working across objects, consider not using an ORM and going down an abstraction layer so you have greater control over the system. It's more code; you may see some duplicate query logic here and there; IDE support isn't as good. But you get control.

The big issue I have when talking with engineers is an understanding of the trade offs and taking a business-rules/domain model first approach. Often it's just "I know SQLAlchemy so that's what I'm using".

One additional point of note: it is also very easy to break encapsulation with an ORM, meaning logic gets sharded between controllers, views, models, services etc. This is a personal bug bear of mine. A bad tradesperson blames their tools and all that, but it's something I see a lot, where engineers treat the objects as Records and manipulate state outside of the object boundary.

I love ORMs. I recently wrote a little script to scrape a website and put some data in a database. I used Sequelize and it's excellent. I don't want to write an

INSERT INTO blah (A, B, C, D, E, F, G, ...) VALUES (a, b, c, d, f, e, g, ...)

statement where, whoops, I transposed two values! Will SQL throw a cryptic error because the types don't match, or will it silently accept my awful query because the types do match? Perhaps I'll find out after a frustrating night of querying data that doesn't seem to make sense.

"But post-it, why don't you write a loop that builds a query string?"

I could! But if I'm already abstracting away the misery that is SQL, I may as well use a library.

"But post-it, with an ORM I can't do <X thing to speed up my query>!"

I'm never going to need that. 99% of people using SQL are never going to need it. They're going to INSERT < 100 million things and then they're going to do a bunch of SELECTS and some JOINs and they won't care if the query takes 150 ms or 50 ms. And if they do, they'll optimize and write that one thing in raw SQL.

Modern databases offer incredibly useful and powerful functionality that abstraction layers typically don't implement.

Postgres has so many useful features and functions that ORMs just don't usually surface - that's not what ORMs are about.

So it's not just about "learning SQL", it's about learning the database itself and what it has to offer.

Search engines, JSON in/JSON out, features like SKIP LOCKED for queueing, arrays, the list goes on and on, and if your view of the database is through an abstraction library you'll likely never even know what's in there.

So don't just learn SQL, learn the database itself.

No, good ORMs are awesome. As their name says, the primarily map objects and object-trees to relational database tables both ways. They provide a standardized and type safe way to do so.

They often include a query builder that generated SQL. An important feature for an ORM is, that you can inspect the generated SQL and that you are also able to use them with SQL queries. Some ORMs even let you mix SQL into their generated queries.

Generating code or generating database schemas from code is also a common feature, including migrations. It may satisfy your need, or it may not. So use it, or don't use it. It should be easy to change the strategy at any point.

The big mistakes only happen if the developers think they don't have to know or care about SQL anymore. That's just wrong, because an ORM is just a tool to work with a SQL database. Like the Tesla Autopilot, it helps you drive in many situations, but you still need to know how to drive, supervise it all the time and correct it in many situations.

It took me a long time to realise this but I discovered the only thing I really needed from an ORM was a type safe query builder. Essentially a drop in replacement for trivial SQL queries, which is most of them.

This article sums up the difference between old and new quite nicely. Prisma is fantastic for rapid development and type safety but doesn't require you to completely change the way you write code as it is still basically just a query generator, unlike traditional ORMs which use hidden magic to try to do everything for you with surprising consequences.

https://www.prisma.io/docs/concepts/more/comparisons/prisma-...

i feel like i'm old enough to see every new generation of people come in talking about how ORM is the devil lol. balance in everything, i maintain that ORM for 90% of the people out there is the right solution (basic or complex CRUD apps).

is it the most efficient? a big no, but at most people's scale it's fine and nothing that a caching layer can't solve if you really need it. you can always drive down to the raw SQL as well.

i can't tell you how many times in my career i thought i was a purist for writing raw queries or using even a query builder and ended up creating some frankenstein "ORM" in the end. if you're app has any sort of complexity involving relationships you are in for a bad time trying to cobble this shit together yourself.

My favorite ORM is TypeORM. A client insisted we use that, mostly because they were already using it.

Somehow, a few left joins led to ridiculous (talking tens of gigabytes of ram when the whole database itself was at most a hundred megabytes) memory leaks, blowing up the instances.

Turns out, it is a known issue [0], and you’re better off joining by hand.

I won’t complain though. I got a nice blog post out of it.

Now, this wont’ be specific to ORMs, and any dependency could blow up your service by leaking all over the place. But so far it’s been the only one to do this to me.

[0]: https://github.com/typeorm/typeorm/issues/4499#issuecomment-...

Yes.

> This is mostly false. ORMs are far more efficient than most programmers believe. However, ORMs encourage poor practices because of how easy it is to rely on host language logic (i.e., JavaScript or Ruby) to combine data.

The ORM encourages this, unfortunately.

> Now don’t get me wrong, ORMs are not as efficient as raw SQL queries. They are often a bit more inefficient, and in some choice cases, very inefficient.

Maybe a bit pedantic, but this and what's above contradict each other.

The problem is essentially this: you shouldn't use the abstraction until you understand what you're abstracting. But that's not how we write software typically.

I personally am thankful for ORM in Laravel's Eloquent.

I have requirements to always have a changing query which if done with raw sql can increase the maintenance effort drastically.

I speak this because I also am maintaining a legacy project which uses raw SQL and it takes a tremendous effort to even add a single feature which I know can be done in minutes with ORM.

Overall, I'm with the ORM gang, however, I also respect the other view and I know well enough the freedom they have with using raw sql.

But I guess it's fine for me to be constrained with ORM, especially with Laravel's Eloquent ORM. It greatly improves my productivity working as a software engineer, and I also get to know more edge cases of raw SQL by using ORM.

Imho, ORMs make the easy queries easier (terser) but simply do not cut it for complex queries. So you need some solution for that too.

You could drop down to SQL-as-string-queries, but then you loose all type safety on the queries. We use jOOQ (sort of LINQ on the JVM) which brings significant type safety to the database interaction layer.

Would I use an ORM if I had to start over? No.

Would I use a db + jOOQ if I had to start over? In many cases, yes.

To me they are not so much a anti pattern, but usually come with many anti-patterns, like coupling validation to entities to caching to ...

To me, the article doesn't make very compelling observations or arguments.

There are some interesting things to note about object-relational mapping:

  * They map data to objects (so they are really tied to OO languages)
  * They generally trade control for convenience
  * Over the maturity of projects that use them, the convenience gets eroded, due emergence of system issues that require control to be wielded.

Honestly, if you are debating the use of an ORM, I would ask why. What is it that you are trying to get out of introducing an opaque abstraction between you and your data? It better be worth it, now and in the future, because it will probably take a good amount of effort to move away from it later.

The trade of control for convenience is usually a good trade at first (during prototyping and early stages of feature development). Later, there is usually an inflection point where it becomes more of a hinderance than good. The trouble is, at that point, migration away can be too risky/costly to justify.

Finally, I have noticed that, generally, mature, well-functioning code bases tend to move away from traditional OO-patterns. Specifically, code and data tend to separate as code bases grow and age because it is easier to reason about. This tends to diminish the value of ORM further.

Overall, I've moved away from ORM on the whole, in favor of query -> struct mapping.

My prior/current experiences with most ORMs are that developers feel like they need to know less about how a relational database functions than they should because they can focus on the abstraction. This leads to schema designs that aren't ideal or not understanding where it is efficient to perform an action (should this be filtered in the database or application, when does a query need to be optimized, where is caching happening, indexes?).

Great for initial productivity, but without monitoring, can run into some serious issues down the line. I've seen lambda expressions hitting ORM interfaces where it wasn't immediately obvious how much data was being grabbed, cached, and pulled back. Then when the database was moved just a few milliseconds further away (network hops) from the application server, it caused significant issues because of the number of round trips happening magnified that small latency increase.

This is ORM specific, but not all of them are great at handling transaction blocks. In such cases dropping into a database stored procedure may be ideal, but then again you are no longer using the ORM and having to take your object and do mapping into a different structure (the stored procedure).

My personal preference is for just enough quality of life tools around the database access. In Java land, jdbi for quality of life plus flywaydb for migration (written in raw SQL) is my current preference. jdbi fixes many basic mapping/binding issues JDBC doesn't handle and flywaydb provides a good foundation for keeping environments in a consistent state.

It never was. Using ORM made the pool of candidates to work on a codebase bigger, and it made applications better on average (not because of some ultimate performance or security metric but because it evens out all the horrible hacks).

In a vacuum, on a super specialised team or in an academic setting it might be something one could write such an article about, but the average CRUD-app-programmer tends to not have the bandwidth or specialisation to spend effort on manual data wrangling. The same applies the other way as well: as soon as saving object-level details or fetching related data becomes something that requires thinking, plenty of junior and medior engineers end up forcing loads and saves by hand. Not because it's the best way or the only thing they could do, but because it works, it delivers the features and makes the company money, and everyone else also working on the code understands what is happening. In a way, 'bad' code that works and can be worked with is not all that 'bad'...

My main issue with ORM's is it tends to treat the persistence of data as an after thought rather than being the really important thing.

The data is often much more significant that the app - in two ways - the value of the data outlives the life time of the app, and often the data has connections or uses beyond the app.

ORM's tend to result in an app centric, rather than data-centric view of data persistence.

Now you don't have to use it like that - but even if you design the schema and mappings yourself, you still have the problem of the ORM assuming it soley owns the access to the data - and if you turn off that assumption performance is often terrible.

If I had an app with modest performance requirements, where the data and the app where one - then that's probably the ORM sweet spot.

But if the data is important, has multiple uses ( and so you might want multiple views in terms of objects ), and will likely outlast the application then I'd stay well clear.

SQL is already a powerful abstraction - learn it instead.

One thing I really like is using Entity Framework inside of LINQPad (C#) for ad-hoc sysadmin-style queries.

They are much, much easier to write; and because most sysadmin-style queries aren't performance bound, it hits a great sweet spot where the ergonomics of an ORM are a win.

I'd really like a universal ORM, though. I'd like to be able to write queries in a SQL-like language and have my classes automatically generated for me, no matter what programming language I'm using. I'd like to write joins in a SQL-like language and have the data structures automatically populated, no matter what programming language I'm using.

I think this exposes the real flaw in ORMs and SQL: ORMs are too tied to the programming language, and SQL is too tied to its own domain. For example, if I could write a set of joins that returned structured JSON, and a JSON schema, perhaps some of the ORMs could be replaced by a simple JSON parser?

Comparing a JavaScript ORM to a C# ORM like Entity Framework feels like comparing C macros to Lisp macros.

You can't really have a reasonable conversation about ORMs without defining the feature set your ORM has. If an ORM doesn't apply type safety to SQL queries and translate typed code expressions to equivalent SQL expressions, it's not performing two of the key functions I associate with ORMs. If I didn't take these features for granted, I would probably question how useful ORMs are too!

I think the reason it's so hard to have a conversation about ORMs in general is that we're often lumping radically different systems together and then making a blanket judgement. Again, it's like working with C macros, getting annoyed with them, and then judging all macro systems to be bad.

I feel like if your query ends up being quite large in complexity then maybe it is a time to create a view or stored procedure depending on the use-case and have ORM call that one instead.

To me ORM is quite useful to have some data classes that I can use with it to automatically handle various generic operations and maybe some relationships. Many ORM frameworks will also auto-create tables with the relationship constraints, provided you setup your data classes correctly.

The moment you start linking up too many ORM operations together makes me want to abstract it away into a view/procedure. It will be easier to debug that view/procedure rather than inspecting what your ORM has generated and how to fix it.

I am interested to hear if someone has other insight into how to utilize ORM frameworks properly.

I think that if you want the most out of a RDBMS, then ORM is never the way to go. The problem being that objects and database rows are not the same thing. Not even a little bit. If you squeeze that square peg through that round hole, you shave off 80% of the point in doing so. Object Relational Mapping is basically a contradiction.

If you are only using the subset where the cost of using an ORM is negligible, you're probably not really using the RDBMS to its full extent and maybe you should consider a document oriented database instead. Relational databases are stupidly powerful if you let them do their thing. Kind of a pity to reduce them to a k/v store.

That said, there's totally a segment of CRUD:y non-performance-critical applications where ORM is a non-terrible choice.

I’m an ex-ORM advocate.

In short, you are very very unlikely to ever change your database.

DBs are full of useful features that an ORM hides.

Instead, I now pick a DB that has the features I want and then use the hell out of the features.

I remember a pre-ORM time of so many manually created db side configs, stored procs, views, triggers, etc. that a DBA would have to manage. I think these unmanageable messes were one of the catalysts to getting people on board with ORMs.

To avoid going back that far. I make sure all config is part of code. Managed by migrations. Or otherwise checked into a repo and managed by a CI/CD pipeline. Now I get to use a full featured DB again. Actually use all its features. And things don’t turn into unmanageable messes.

If the code is handwritten or not does not really matter when you are looking at the generated execution plans and so on for troubleshooting.

I worked with several systems not using ORMs that still have the N+1 problem. And if a schema generator also is used it won't forget to add indexes.

My main issue with ORMs is that schema changes over time becomes more and more scary. Another thing is that many libs comes with a bit of magic. A seemingly innocent change can move things from SQL to the client or the other way around. Ending up with large performance impacts.

EDIT: And probably 100% of all the security related issues I have fixed in SQL would have been avoided with an ORM framework.

I can’t speak for all ORMs but I’ve found over the last couple years that I really like Mongoose. It’s proven to consistently be one of the least troublesome parts of our stack, and once you get to know the ins and outs, it provides a ton of useful things that are organized in a very logical way. Custom query helpers in particular were a revelation for me. Being able to reliably compose different pieces of a query to standardize how you access things across different parts of an application is really cool and incredibly useful.

I didn’t choose to use Mongo, but having Mongoose has made it mostly pleasant, at least after taking the time to really understand the library.

You don't have to listen to what other people "consider harmful" or think are "anti-patterns" or "clean code". You don't Gotta Catch 'em All the design patterns or figure out if coffeescript is the flavour of the week or plug in event-sourcing because it was on HN yestreday. You can just solve the problem you have. Nobody will know. I don't why programming is so vulnerable to these memes and false consensus and blog fad driven architecture.

I have some styles I like and I use them. Some of them aren't compatible with each other so I don't use them all at the same time. I don't wait for John Carmack or Bob Martin to tell me if it's okay. You shouldn't either.

So, in summary, ORMs are only bad when you use the ORM parts and not when you mostly ignore the ORM parts and only primarily use the query generator and basic abstraction parts.

Well yes, that's because whenever things get hairy, you're no longer dealing with an ORM, you're dealing with the query generator. You don't have to translate your complex graph queries into their relational variants by manipulating the output of an ORM.

And I have to agree that query generators have a hell of a lot more value and are way less likely cause trouble than ORMs.

So yes, ORMs are no longer an anti-pattern, because most modern ORMs aren't really ORMs anymore, and when they are, they either swallow the performance hit and resulting issues when trying to compensate or don't encourage you use the ORM parts except for very specific cases, and instead encourage you to use the far more powerful query generator parts and all the random mild abstraction parts.

The question left is, is it better to just learn SQL including the specific variant of SQL you're targeting, or is it better to instead learn a specific query generator and its database specific parts?

Addendum:

Really, at this point I don't think it matters what people use, the number of people who actually know how to design a database is insignificant compared to the people who are tasked with designing them. I am now all but convinced that the reason ORMs are popular is because they make it slightly quicker and easier to deal with a fucked up database design than having to do the same with pure SQL or a query generator.

The thing I always disliked about ActiveRecord ORMs is that you need to learn "their way" of writing SQL too much, because everything beyond the "Getting Started" tutorial is too complicated to use the built-in query building features.

The thing I like about ActiveRecord ORMs is the way you can iterate over a data set. The thing I dislike about how this works in practise a lot of the time is that it takes up quite a bit of memory because the relationships are built and held as objects.

Another thing I like about ActiveRecord ORMs is that I can have classes that have functionality, but get their attributes from the database. The thing I dislike about ActiveRecord ORMs is having to create a class for each of my database entities even if I don't need them (and/or having classes automatically created for them, still just as bad if you ask me).

I created PluSQL, a non-ActiveRecord ORM because I prefer to write SQL most of the time, but want to minimise the amount of boilerplate, and also because I like iterating using objects but don't like holding the entire object tree in memory and only want to create classes to represent a database entity when I need one:

https://github.com/iaindooley/PluSQL

It’s not an abstraction, it’s an indirection, and that is why so many people get frustrated with using them.

Stick to the conventions and patterns and you’re happy: you don’t have to bother yourself with making thousands of decisions about schemas, relations, indexes, constraints, naming conventions for all of these things, patterns and so on.

They wouldn’t be so frustrating if they were a proper abstraction: if they had a denotational meaning and simple, elegant theorems about them could be established. But they don’t have that: you get a layer of indirection between a parser and some structures in your code. You get an evaluator that builds strings from some definitions and expressions. Nothing about them is formalized and gall of it is convention and hand-waving. So the way each library does it is different and how it works is left as an exercise to the reader.

So is it an anti-pattern? I think I agree with the article: it depends. If your data follows common, repeatable patterns then it might be best to take advantage of an ORM to generate and manage a bunch of code. They’re not bad-by-default.

But I’ve been around since the 90s and people have been writing ORMs, swearing off ORMs, and writing new ORMs in waves. It’s never going to end I suspect until someone formalizes it and builds a proper abstraction by giving a denotational meaning and proving theorems (which some folks have been working on by using category theory as the underlying formalism).

Until then I tend to avoid them myself as I find that many applications I work on have access patterns and structures that don’t fit the mould.

The abstract concept of ORM is fine, unfortunately the particular ORMs we use suffer from pretending all objects and their relationships exist in RAM, there's a single instance of every entity and it's always up to date. An abstraction that isn't merely wrong, but falls apart in your face at every opportunity.

This problem is not unique to mapping objects to SQL databases, but mapping objects to anything remote at all, say a GraphQL or a REST API.

OOP as we presently interpret it is an inherently synchronous, reference (or handle, rather) based paradigm, that only works when all your state is local and in RAM.

This is why distributed object protocols keep failing. They'll keep failing until OOP programming reorients to use values for messages and makes references explicit, so their impact is seen and felt (and it's especially seen and felt when you reference an entity on another machine halfway around the world, in terms of lag, fragility, eventual consistency and everything).

We see hints of this with value types in Swift and .NET, unfortunately I'd say rather rudimentary so far. But it's coming. The ideal example of such a set up is Erlang. A language that Joe Armstrong has called "probably the only OOP language in the world". A statement Alan Kay agrees with (source: https://www.quora.com/What-does-Alan-Kay-think-about-Joe-Arm... ).

SQL is much more powerful than most people think, and taking an afternoon to learn everything you can do with it is a great investment.

After you do that, you will probably not WANT an ORM anymore, finding that it makes your code less readable than plain sql, not simpler.

Actually, you'll even realize that you may not need anything BUT sql to write your application, using something like (shameless plug) SQLPage [1]

[1] https://sql.ophir.dev

There is a common problem - some fundamental technical problem is complex and has a steep learning curve. In comes the ORM or Library or Plugin or ... These things are all layers on top of the fundamental technology that simplifies the underlying thin. Or that provides a layer that hides the complexity of multiple implementations of a technology. So you adopt it. And you get running. Then you run into a problem with the layer. It does not implement a fundamental part. Or it has a bug, or the implementation you need is not covered.

At this point you try to fix the ORM or adapt to it or fix it or something. Because you still don't know the underlying technology.

I'm not saying this is a mistake. Sometimes the ORM works and sometimes it does not. It depends on how fundamental this technology is to your product and a host of other issues. BUT! It is always worthwhile to learn and understand the fundamental technology. SQL is not that hard. It is worth understanding. Even if you want and can use and ORM, not knowing SQL will hurt you. And knowing an ORM is just an extra thing to learn.

I actually like SQL. I find it hard, a different way to think, but fun. I use React a lot. It helps except when it doesn't - and then being able to work in html, css and POJ saves the day. Even though I never code in assembly language (ever!) knowing how registers, CPU's and instructions work makes me a better coder.

So when faced with the choice of a simpler way to do something that provides a layer over a fundamental technology, I am now extremely skeptical. Sometimes it works out, but most often it is not a good choice for me.

I can see that people have already heaped plenty of fuel on the fire, but.. The amount of time I have wasted babysitting Microsoft's EntityFramework stuff :-/. That is stackoverflow-googling 'why does EF suddenly behave this way, and how to I get it to stop?'. In my most recent adventure, it had suddenly started to fight with some of our foreign-key delete-cascades. That is, even though EF had itself generated migrations that told SQL-Server to use delete-cascade, this would catch EF off guard, and it whould suddenly scream 'there used to be rows in this table but now they are gone!' Stackoverflowing suggested to bypass it by specifying an alternate delete-cascade policy (presumably one where db was allowed to cascade and client would pretend everything was OK.) That .. sort of worked, but now our unit-tests were failing, because they didn't like that new cascade-rule.. So now we have conditional compiles, where our unit tests compile with the rule set one way, and production code sets it the other way.. I have no idea what our tests now claim about our code, relative to production..

Hooray for ORMs. Not.

The article leaves out a few weaknesses of ORMs

1. inability to represent arbitrary query results. Results get mapped back into 'domain-objects'. Now you've removed yourself from the extreme expressiveness of the SELECT statement as well as GROUP BY, pivot tables etc. etc. The best you can hope for is domain objects that are only partially initialized with values.

2. lazy loading of attributes - certainly configurable, but if you rely on SELECT statements being generated while you are navigating a 'persistent' object, you will run into unexpected performance issues. To solve those, you then start to eagerly load domain data for certain access patterns. More annotations, more configurations and more ... stuff - where a nice SELECT query would have sufficed to get the data you need.

3. Troubleshooting is a pain. Especially if you rely on dynamically building queries. Have fun getting the actual SQL that was run and then translate back and forth between SQL and the query builder syntax to optimize it - if you can even optimize it.

4. Doesn't treat data as data. First of all data coming from SQL queries is stale the moment it arrives in your objects. Trying to pretend otherwise and having an object graph that pretends to be the living counterpart of the state in your DB is foolishness.

5. No/limited control over writes/updates. The moment you rely on your ORM to create UPDATEs and INSERTS to reflect the state of your object graph back into the DB, good luck with your chosen locking strategy and good luck optimizing those. Pessimistic locking is the best worst you can hope for.

Stay away from ORMs (as in Hibernate and its ilk) and treat data as data. Use structs/records.

I want a few things for database support libraries:

- Elimination of boilerplate to load data from a table into a type - A reasonable query mechanism that avoids SQL injection - A migration tool

Every ORM I've used solves the first problem (for which I'm grateful). Some do the others. Most do way too much more.

I still use an ORM in every non-trivial project because I don't want to do these things by hand. But it could be much better.

ORMs are like any category of tool. Some are good. Some are bad. Either way, you need to master it (in additional to SQL) to get the most of out it.

These days, my approach is to start with some repository interface/abstraction that exposes well defined access patterns/operations and models. Then I hand code the implementation with just normal SQL that I hand code. You can get really far with this. Eventually, if it starts feeling unwieldy, I'll look at reimplementing it with an ORM. This rarely happens. In fact, its only really happened twice that I can think of.

That said, if I joined a team that was set on using a particular ORM and they had experience with it, I wouldn't think twice. The only thing that is a deal breaker for me is using any ORM feature like Hibernate's auto flush insanity (something that figures out which objects you've dirtied and automatically generates updates and inserts when you close the session). It will be very difficult to ever untangle that if you need to. Far better to explicitly have to persist things than rely on some auto-magic like that.

I don’t really understand the N+1 issue. Most ORMs I’ve used make it very easy to generate the exact query you’d generate without it, with the added benefit that you define the relationships at the model level. This helps a lot when you need to make changes like renaming a field without having to worry about propagating these changes in every query and migration in your app. When you build a statement with an ORM, you can also output it and tweak it to be what you would write as raw sql. You can look at generated migrations and tweak your models until migrations are efficient, etc. if you’re generating N+1 queries I don’t know if you would do better with raw sql. Besides, most ORMs today let you switch between 3 levels, default ORM, query builder and raw sql. You also get to choose if it converts the results into your models or if it just returns raw results. Maybe I’m missing something but so far it seems like the problem with ORMs lie on not understanding when and how to use them?.

Someone should tell me how they think relations map to objects.

At runtime, you submit a query like "SELECT foo, bar FROM baz". At compile time, you make an object that can hold foo and bar. This, unfortunately, is backwards. Runtime happens after compile time, but runtime is when you know what objects you would have needed to compile to understand the result.

I have a feeling that ORMs have most of their popularity in dynamic languages where there is no type system. You get whatever the database thinks you should get today, and defensively code around that (or crash hard when a query starts returning an integer instead of a string).

I feel like the maximum amount of syntax sugar you can put around a database is something like sqlx.ScanStruct. If you have a query that returns "foo int32, bar string", then sure, an API like `result := struct{ Foo int32, Bar string }; row.ScanStruct(&result)` seems fair to me. Anything more than that, I think you're just creating problems for yourself.

P.S. I have no idea what the link has to do with ORMs.

I had an irrational hatred of ORM's for a long time until I used them for a while.

They are great for CRUD applications, they simplify a lot of your application boilerplate. Yes, you still should know and learn SQL, and know how your database works, and yes for any kind of complex query you will need to reach for raw queries, but that's fine.

We are definitely going to look back on this era and wonder why we stuck to archaic tech for so long.

It's clear that the database needs to be more integrated in our applications.

SQL is not a good fit for most apps. The reporting/analytics features are great, but that's not what most of our apps are doing these days. This kind of stuff is moved off into other databases most of the time anyway. The core idea of the "relational model" is not a good fit for most apps. Interactive user interfaces are about listening to changes in single models...we don't really care about "relations". Relations are forced upon us to allow automatically optimizing query execution in the database. But this optimization doesn't take into account what other queries we run, so we have to handle caching ourselves, and completely breaks reactivity. People fight against the query planner all the time.

I think the solution is to have control of the query plan.

Without a database, people write code that is similar to what a database is doing, often without realizing it.

Take: `users.map(user => ({user, posts: postsByUserId[user.id]})`. We have created an index, and we're doing an apparent hash join.

Now this usually turns into a tangled mess because of all these adhoc hashmaps we create and pass around. You will see this kind of spaghetti all through modern web apps. The more of these adhoc transformations you have, the more complexity you have because we don't have good tools to visually trace these transformations throughout your app. Your app becomes hard to change. But it also gives us a lot more flexibility when it comes to caching query results.

What would be nice is if our sql dbs were broken into layers, and we had access to the query planner so we can write reactive and cacheable queries. And also that our sql dbs could be run in the same memory space as our app code...which means running in a web browser.

Frankly, I am cynical. ORMs produce bad queries, but the SQL queries I've seen in some code bases are also nothing to write home about. If the choice is between terrible spaghetti-string-concatenation-manual-sql-from-hell and sometimes-inefficient-but-fixable-when-needed ORM code, I prefer that people stay with their ORMS.

In the world of Java enterprise applications it is not anti-pattern but a best practice. It is not just encouraged but often mandated by Enterprise Architects. At least in my experience of 18 years it brings the developers who are neither good in SQL nor in Java and result in stunningly crappy applications.

It's common to isolate all database calls to a specific part of the code. Usually each potential database operation has its own function, such as "update_user", or "get_spending_report", etc. This is good.

However, I think it's an anti-pattern to use both an ORM and still isolate your database code. It's like a double-abstraction where only one is needed. You have a write a bunch of CRUD functions and you have to use the ORM inside those functions. ORMs are complex, but they can be convenient to use, but if you're isolating all the database calls then the convenience of the ORM is wasted inside those half-baked CRUD functions. You end up getting the worst of both worlds.

So I say either (1) use raw SQL isolated inside CRUD functions, or (2) use an ORM and use the ORM abstraction freely throughout your code.

Seems like most people are commenting in the context of web apps but I find ORMs are most useful on the client side (mobile/desktop apps) where objects can be long lived and you want to take advantage of ORM specific patterns like identity maps (https://martinfowler.com/eaaCatalog/identityMap.html) so you don't have multiple conflicting versions of an object in memory.

If you're writing a web app and just pulling data from a database and dumping it to HTML or JSON then yeah it matters way less whether those database rows get materialized into objects before getting rendered to output, because you're throwing the objects out at the end of the request anyway.

In my career, the biggest problem with ORMs I’ve used is something the author touched on:

They can be terribly leaky abstractions. It seems that many ORMs end up re-implementing SQL in their own domain-specific language.

However, an understanding of SQL is often needed to debug and optimize ORM queries.

So why not just use SQL directly then?

Martin Fowler's Patterns of Enterprise Application Architecture has a section called "Data Source Architectural Patterns" with some data access patterns. It doesn't explicitly call out "ORMs" but they tend to combine multiple patterns. It also has a section on "Query Objects" which cover some of the method -> SQL magic ORMs do. It also lists pros and cons of these patterns

As with many things, ORMs come with pros and cons. Imo they can be especially productive for simple CRUD applications and prototyping but may quickly increase complexity with object or data models that use more complex query patterns.

https://martinfowler.com/eaaCatalog/

As a fun exercise, I suggest trying to build a mapper that gets a URL with the `type1/id1/type2/id2/type3` pattern and spits out a set of objects of type3 that have a parent of type2 and id2, under a type1 with id1. Now do that with arbitrary types and depth.

With a good ORM, this is so trivial to write that, most of the time, you don’t even need to: you can rely on decorations or any similar mechanism. I have codebases that have barely any code devoted to implementing that, and that will happily build the query on the fly for any type in the model it’s allowed to use.

Some problems fit ORMs really well. Some don’t, of course, and it’s fine - knowing when to use a tool and when not to is key with any tool.

I believe the underlying truth and cause one uses an ORM is the implicit mapping from tables to models/types and the near hidden exposure to the database.

There is, I believe, a positive gain with the use of a slim ORM which at the same time exposes an API for querying with real SQL.

Dapper/Linq/Fluentmigrator is my favorite stack for interacting with a database from system code. It's slim, fast and very near the database.

Whatever the arguments are around SQL and hiding it, I believe that to be the real anti pattern. If you must interact with a database in your system, make the use of SQL as absolutely easy as possible. It's a great nd immensely powerful language which no ORM can substitute in a nice manner.

SQLAlchemy is one of my favorite libraries of all time. I actively choose Python for projects because I get to use it. If you’ve never used this ORM you don’t know what you’re missing. And I say this as someone who for years actively campaigned against the pattern.

ORMs are fine so long as they are used exactly what they are intended for, which is to make your job easier. As soon as the ORM [inevitably] gets in the way, just bypass it and write straight SQL.

What drives me nuts is when I work with engineers who insist that we do all database queries through the ORM, no exceptions, just in case (TM) we someday switch from MySQL to PostgreSQL or whatever. That day has never come anywhere I've worked. It's generally a fantasy.

If you're going to invest in a tool, invest in the database, not the ORM. You're more likely to save time writing complex queries in SQL than in the highly unlikely circumstance you need to suddenly switch database technologies.

Overall, I think the article is pretty good. ORMs fall under the same category as garbage collection or try/catch exceptions: it is a tool. Tools solve a lot of common cases, but that doesn't relieve the developer from understanding what is actually happening under the hood.

* Garbage collection solves pointer problems, but the developer has to know what is being allocated and when to prevent churning memory. * Try/catch exceptions solves breaking the tie between where exceptions can occur and where exceptions can be handled, but the developer still has to determine if all of the exceptions are being handled. * An ORM solves relating objects to data persistence, but the developer still has to know what relations weren't covered in the original query after the objects are used.

> Active Record struggles with this, and that’s why we refactored our billing > subscription query. Whenever we got results we didn’t expect, we’d have to > inspect the rendered SQL query, rerun it, and then translate the SQL error > into Active Record changes. This back-and-forth process undermined the > original purpose of using Active Record, which was to avoid interfacing > directly with the SQL database.

This is where I disagree. ActiveRecord was meant to map objects you have to your relational database, hence Object Relational Mapper. It is not a reporting interface. But it is reasonable to get around this. One way is to get a lot more specific in building the query with `arel`. Alternatively, open the raw connection and execute SQL with `ActiveRecord::Base.connection.execute`.

The disconnect here is believing that ActiveRecord solves billing/reporting SQL statements. It does not. It solves mapping objects. If your access pattern doesn't match (reports, N+1, GraphQL) then you have to understand the tool well enough to create your own mapping solution (custom SQL, preloading/scoping, data loader).

I remember when Java was considered really, really slow. The guy used a code example to prove a point. I rewrote the code to stop relying on the GC allocating/freeing data structures over and over and sped the program up by 400% on that alone.

I don’t mind query builders. They help you fall into the pit of success and reuse useful queries. The thing I don’t like about ORMs is how you get back a graph of objects, and you can mutate any of them and call `.save()` on any of them. They pretend to be a live representation of what‘s in the database, but they are a proxy. And so when saving, the ORM has to sync the alterations you made to your objects with what it believes is in the database. It treats database tables as just a bag of attributes.

I prefer to work with values and be explicit about what’s been read and what needs updating. If you need optimistic concurrency control be explicit about that too.

In my view, ORM replace some type of bugs (SQL injections, wrong table/column names) with different type of bugs (inefficient queries, hard to debug).

One of the things I really do hate about ORMs is that they usually try to be DB-agnostic, thus preventing you from actually leveraging powers of the underlying database. And that they always end up being really complex.

On the other hand, I hate string manipulation (that you sometimes need to do if you do some types of queries) and in general that by dropping to SQL, you are switching to a different language, that kind of sucks (nobody sane actually enjoys SQL syntax, I think).

For those who didn't know, ORM stands for "object relational mapping".[1][2]

It's always good to define acronyms up front, especially if you're trying to attract a larger audience.

[1] https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapp...

[2] https://stackoverflow.com/questions/1279613/what-is-an-orm-h...

I use TypeORM for Node.js, and it's extremely frustrating and littered with bugs, but even then it still saves me time vs. using SQL for everything. Automatic table synchronizations are a huge time saver. Even if one doesn't use an ORM, I think one will eventually end up rolling their own, so why waste that time? Anyways ORMs have query builders, so one can always write SQL queries when necessary. I just wish TypeORM's query builder was just knex because knex is far superior to the TypeORM's bug-ridden querybuilder.

Anyone who eschews an ORM just end up writing their own half-baked badly documented ad-hoc ORM. “Jusr use SQL” is not an alternative since you still need to map the result into a data structure.

> But after some meditation on the matter, we’ve reasoned that ORMs do not suck. They are simply an abstraction with a quintessential pro and con—they abstract away some visibility and occasionally incur some performance hits. That’s about it.

This isn't an insight. Yes, everything is a tradeoff, but that doesn't mean all tradeoffs are equal.

The higher the ratio of abstracted away complexity to the simplicity of an interface, the better the abstraction. For ORMs, this ratio is low. They abstract away very little and present a huge interface.

ORM isn't the anti-pattern, but rather the current way we do OOP. Object orientation is a pervasive, powerful paradigm, but our current approach only works for in-process, highly interlinked, synchronous objects.

We need to move to OOP more similar to Erlang. Message based, where the messages are values, not references to references of references, which is the case with Java-style objects (you work with an object via a handle, which is in fact a reference to the object, allowing mutable shared state).

I purely use EFCore for managing migrations easily, almost all queries end up complicated enough or need atomicity so I write them as raw, the automatic object mapping is just a nice bonus.

It's amazing how many SQL Injection vulnerabilities I see in brand new code. At least with an ORM, this is abstracted away unless you try extremely hard to create such a vulnerability.

Part of the madness is domain modeling. One imagines their software’s domain model includes things like accounts and persons and try to build those as objects.

This is a lie. The domain has been modeled in the database already with constraints. The domain of the backend is not to manipulate persons or accounts but rather to do things requiring secrets, like call the database.

As such the actual meaningful structures are things like SQL clients, transactions, JSON decoders, situation specific POCOs and secrets.

What really needs to exist and doesn’t is languages with first class support for direct relational queries in the language. We have functional, procedural, and OO programming languages but no relational programming languages.

The fact that a database is this weird thing bolted onto your code with a bunch of glue is asinine. The data is the whole point. It should be in the center with code around it not off to the side. The database should be the runtime with the data as the center of the universe.

Hot take: ORM is dynamic runtime code gen.

Sometimes, you need dynamic code gen and you dont want to manually do it.

…but mostly, it’s a bad idea. You can’t easily debug the generated code, you can’t easily tweak the behaviour, and writing the templates for it is hard.

Mostly you can replace it with a hand written or pre generated (“compiles to sql”) solution… but I’d like to point out you can’t always and if you do need dynamic SQL please, use an ORM, don’t template your own half baked solution.

The anti pattern is not the tools but how the tools are used. ORM is abused a lot by people that don't necessarily understand databases very well. This abuse leads to overly complicated table structures based on an illusion that the ORM tries to keep up that internal domain models map 1 to 1 to the database. This is basically self inflicted pain. If you've never heard about the object impedance mismatch, that's basically the problem with modern object oriented application architectures and trying to shoehorn that into a database.

Complex table structures have all sorts of issues related to complex database migrations and operational overhead related to that, additional complexity in the form of lots of joins, a need for indices to support those, all sorts of cascading behavior when things are updated or deleted, transactionality issues, and so on. That complexity tends to leak back into applications.

If you know what you are doing, you can avoid all of that of course. But there are a lot of examples of people using ORMs where things get a bit messy.

I've fixed a fair few projects where hibernate usage got out of hand a bit. These are not fun projects to join usually. Performance issues, flaky behavior, wonky test failures (and slow tests), etc.

Usually a good way out is to cut down on the number of tables. My mantra is that if you aren't querying on it, maybe it can just be a json blob instead of a lot of silly tables and columns that need to be joined together. I like document stores. And you can easily adapt sql databases to build one that has nice transactional behavior.

A few years ago I consulted a company to help them build them a search engine. They had completely over-engineered their data model, spent months obsessing on their domain model, and there were 20 or so tables and all the usual issues related to that (hard to extend, issues with complex transactions, stupidly complicated joins, etc.). Basically, I sat their tech lead down and went over the requirements:

- get a thing by id

- do some CRUD on things

- put all the things in the search index (for each thing do X)

- use the search index to find things

That's it. The whole point of the system was to act as a single source of truth for the search index.

So, I told them: that sounds like you need 1 table with a json blob to represent your thing. We had a few other columns for things like timestamps, userIds, etc. That vastly simplified the application architecture. We could add features without requiring a lot of database migrations. Etc. All good things.

Isn't the root issue, that relational model is superior for the job. And everyone was using it since codd wrote this famous paper in 1970? (I'm not that old btw^^)

And if you would read only the first two pages, you would see that ORM (and also NO-SQL) has some of the problems he describes in systems used prior!! to 1970!!

I think 90% of users must not understand the relational model, because it's impossible, that the other models are the right tool for the job so often.

ORMs will always be bad. They take control away and lock you into a system where fewer people are using than the underlying tech being hidden.

SQL is not hard, and I today's world most people should be using indexed documents, vs trying to do inner and outer joins to save some bytes. And in the cases where all the advanced SQL stuff matters you probably are not going to want to use a ORM anyway.

They’re still valuable in larger enterprises where applications are maintained for decades without rewriting. I’ve seen db vendors get switched more than once in contexts like this. Code gen tools are nice, saves a ton of boilerplate. Everyman type programmers still get a lot of mileage out of them. Majority of queries HQL handles fine and you can always drop into SQL to handle the ugly ones.

I'm always confused that ORMS don't standardize on a syntax where N+1 queries are literally illegal in all cases. With `forEach` and callbacks and everything you could easily have a syntax where you can't await until an `run` and curry everything into a single round trip. It lets you write "standard" javascript but disallows N+1 loops always.

Then people have to write "bad" code to N+1.

Ruby sequel (http://sequel.jeremyevans.net/) is the only library where you can combine classic ORM Model bases usage, with a more raw query builder "just get me all the data into plain objects". You'll never need anything again in your career life.

Are you all discussing web development? Then, I assumed that most web developers use ORM. So, I'm purely surprised by the number of people here who are against it.

I agree that abstraction can be leaky, and it's true that you often end up writing some SQL-like code.

However, isn't writing raw SQL potentially dangerous, considering aspects like security, access control etc?

I want to learn your thoughts!

Im imagining a build tool to take raw SQL statements and validate them and produce typescript code from them. So ie const data = query(select * from table_a where pk = x) is a promise of the appropriate type etc. I currently use prisma and it is kind of good but can definitely be leaky and is rough around the edges. I like doing raw SQL statements but making it type safe is annoying.

I only have experience with EF from C# + LINQ and I was very, very happy using it. The query language is insanely good. Just to have statically typed queries was a total godsend. Ad-hoc projections which lead to optimized SQL. It literally has saved me days, maybe even weeks of work. Sure sometimes you need to fallback on raw SQL. But that was less then 1% of cases for me.

I love the DynamoDB mapper model of the ORM. It only handles object serialization and deserialization but everything else you write code as if you are working with the native DynamoDB API. I want an RDBMS ORM that does only that - translate a ResultSet to a Java POJO and convert a Java POJO to an SQL insert/update statement. I would gladly handle everything myself.

I don't agree to ORM being anti-pattern. I'm using Java for many years and I'm using Hibernate for many years. It's awesome and incredible productivity booster.

Though ORM requires extremely high qualification, that I would admit. It's definitely not a tool for unexperienced developers. SQL is much easier to use and brings less surprises.

when you write code to build a query in an ORM in an interpreted language like Ruby or Python, does it typically go out of its way to actually build the query once? or does the query get "re-built" (or cached?) every time you call the function that contains your query-building code?

I've been wondering about this and other things while working on my own mini-ORM thing for SQLite—I have it so queries are built once, at compile-time, type-checked, and stored in read-only memory in the executable. but I'm going down the route of having the user manually write SQL for everything that isn't super basic, instead of making a sort of mini-DSL for query-building. so far it's been going great but there's a lot of prior art in this space, and while I've used both Rails and Django before, forever ago, I've been kind of going into this all blindly (and so far it's been going great!).

Love how the tech community keeps veering from “ORMs are awesome!” (rails era) and “blockchains are the future” (2016) to “they suck!”

No, ORMs don’t suck. We used them for over a decade in our projects. There is no downside because you need middleware that writes your SQL queries for you anyway, so the app layer can reason about sharding and other data, and to avoid SQL injection. So you may as well make it object-oriented. Just make sure your ORM can handle primary keys with more than one column.

What sucks is using relational databases when you could be using graph databases. Because graph databases don’t waste memory duplicating primary and foreign keys across tables, and don’t waste time doing O(log n) lookups in an index for each row, 99% of the time you can directly get the ENTIRE array of Foos that are related to a given Moo in essentially O(1). https://neo4j.com/blog/data-modeling-basics/

I would have used them but MySQL was ubiquitous so we went with RDBMS. Many people chose RDBMS because they were mature and very widespread. But graph databases have been catching up.

Better visualization/viewability of what's happening again seems like the best thing. If we could have a uml diagram of the orm's model, then see the retrieval access patterns flowing across that model, the risks/badsides of ORMs would be much reduced. Making clear what's happening is as usual a win.

Setup cost is non trivial.

Portability is typically poor.

Custom SQL or optimizing bad SQL can sometimes be very difficult to do without completely breaking open the ORM.

The promises of ORMs are typically not kept.

I have always found a thin wrapper around JDBC or some other SQL driver is always sufficient in avoiding the code explosion of reading from/writing to SQL databases.

EF Core is my favorite ORM. Some Java ones are alright, and Javascript ORMs all suck because of the lack of RTTI. I try to keep my webapps as cruddy as possible and do complicated things in application logic with transactions for atomicity. I don't like relying on SQL

Laravel Eloquent is nice to use, I've heard criticism before, but not that it is an anti-pattern.

Regarding the topic of ORM being an antipattern, I can also highly recommend this methology by Hettie Dombrovskaya and Boris Novikov: https://github.com/hettie-d/NORM/

Been using ORM for a decade. It’s been amazing for my use case. Absolutely amazing. Tools are tools. Blanket statements about tools say a lot more about who’s saying it than about the tool.

Don’t be afraid to conclude a given popular tool is wrong for your needs, or an unpopular one is right.

Where they ever an anti-patter?

I would say no.

But most implementations have issues of varying degrees of servility. And the important part is understanding this issues and deciding weather or not this issues are worth it compared to the benefit a _specific_ ORM might provide.

Yes. What the author is describing with the fancy TypeScript query builder is strictly worse than plain SQL. You still have to understand how SQL works, but on top, you all so have to understand this weird DSL on top of it.

The obvious solution is to use nor ORMs or raw SQL generated with various helpers in your language, but to open up DB engine to execute low level bytecode. Think: DB=GPU, SQL=GLSL, what's actually executed -> compiled code.

all the absolutism I read in the comments speaks miles about the problem not being in ORMs (which are a tool that is very useful given the right scenario) but existing between chair and keyboard.

I'd rather have an ORM than having to deal with some of the garbage code I've seen over the years (specially around many-to-many tables or relations in general) to handle the deserialization from db "manually, hand crafted, artisanal, more maintainable", specially for very basic CRUD or almost CRUD scenario where a decent ORM pulls most of the project with little hassle.

Hand crafted queries have their places just like ORMs.

We have LLMs which are pretty good at SQL now, this seems like a good usecase.

Rather than using ORMs, have an AI tool to generate an SQL query to join xyz objects and generate a test. You get to fine tune your SQL and speedily make queries.

ORM is the wrong name for what we see for now. Its name should be OSM - Object SQL Mapper translator.

So, the main reason to use an ORM (and also the correct way), is to help translate your business logic into SQL without writing sql string.

In Symfony the ORM (Doctrine) layer has a normal relational database in the back. Performance can take a hit, but you can always build custom SQL for faster querys of larger sets. Or am I missing the point?

If we really wish to dig deeper, you only need an general purpose languages to host HTTP Server and perhaps HTTP client to connect to other external services.

Everything else including business logic can be done in RDBMS.

“At lago we…” Hmmm… as a forced user of Lago’s product at work, I’m sorry but I stopped reading at that point. Terrible software, terrible support, enjoy your ORM hellscape I’ve flipped the bozo bit.

Also mutability; object-level edits/writes are half the problem with ORM

One of the only places I've found ORMs to function well is on mobile where, usually, neither the schemas nor queries are that complex.

In any other setting they have just been a limiting abstraction layer.

I use an ORM for two things, in prioritized order:

1. Composability — making it easy to compose smaller queries together into larger queries

2. Type safety — so I can use the compiler to tell me when I've made a mistake

I’m about to launch a mobile app that requires not-very-complicated back-end support. I was planning to use Laravel & PHP. Should I shun its ORM?

I’ve asked about ORMs before https://news.ycombinator.com/item?id=35215355

For simple projects, I don’t see a need for it.

This is a classic silver bullet syndrome topic... https://www.youtube.com/watch?v=qamzvLfX-Zo

I think ORMs are fine for small queries. For example: User.find_by(email: "xyz"). But as the queries get more complex ORMs fall apart and simply using raw SQL is better imo.

There are ORMs (like Doctrine) that don't break SRP. I think ORMs have a bad reputation because of the ActiveRecord pattern. Not every ORM has to implement that pattern.

ORM doesn't seem like an anti-pattern in my experience; sometimes they are very useful for certain applications, other times they aren't. This applies to all tools.

I use ORMs to create custom line of business apps, mostly CRUD-ish and it works fine. The ORMs I've worked with have a way to write your own queries if the need arises.

ORMs major selling point is solving the n+1 queries problem. They can extract the tree of objects very well. But for fast batch processing they have many drawbacks.

It's interesting that in general I hear a lot of hate for ORMs, but the Rails devs I know all seem to like ActiveRecord and can't imagine working without it.

I'm working with the Django ORM and I absolutely love it. Maybe it'll bite me in the ass five years from now, but I'll have made millions by then.

ORM is a crutch. One of the things that has always separated good developers from great developers, is that the great developers are masters of database querying.

ORM, Python GIL, need for k8s, mongo is webscale - pick your summertime bikeshedding topic instead of going outside and re-evaluating your life friends!

To me using ORM instead of SQL seems like a healthy person using Stephen Hawking's speech generation device instead of just speaking.

There are fantastic ORMs out there. ActiveRecord is stellar. GRDB for Swift is fantastic.

As usual it depends on your task and the ORM you choose.

God save us from JPA Criteria and its hellspawn

If you’re not migrating from one database schema to the next database hotness at least once a month, are you even alive?

No. It's needed when you're doing something high-performance and are listening to a database change stream.

For those who don't use ORM, how do you map DB result sets to strongly typed model and subsequently JSON?

Using an ORM is not an anti-pattern. It is a tool.

For 95% of my use cases, ORM have sufficed. When it hasn't, just use SQL...

Well, theyre the old anti-pattern.

You should be using the new anti pattern - GraphQL

It'll keep your other anti-pattern happy. Consultants.

> ORMs are an anti-pattern. They are a toy for startups, but eventually hurt more than help.

Does anyone actually say this?

I really like how Ecto does things in Elixir by separating schema and functions that modify the data.

This is saying that if you use your ORM as a typed query builder, its fine!

But then just use a typed query builder?

Was the article removed? Link just takes me to the project README, which says nothing about ORMs.

I remember the world before ORM (all queries inside data access objects) and it was not a great time to programming.

While I understand that ORMs have problems, I don't want to craft all SQL by hand and make myself a SQL builder because I need some optional filters.

Very interesting how the page defines every acronym _except_ ORM

ORMs work great early on, but you’ll always end up fighting them.

Though not considered ORM, I enjoy using Ecto (elixir).

I use sql alchemy in all my python microservice. It’s great.

If your ORM is sqlalchemy its not an anti-pattern.

I hope to see a similar thread on OOP one day.

saying the ORMs violate SRP is like saying REST services violate SRP because you have different endpoints.

This word brings me nightmares: EAGER

Anti-pattern is an anti-pattern.

I recently discovered https://github.com/porsager/postgres

I think this is how an ORM for SQL databases should be. Unfortunately I don't use Node on the backend (Rails, Django, Phoenix) but I wish there would be ORMs like that for those frameworks.

TLDR: write SQL with interpolated parameters, get the results parsed into objects.

django + postgres seems to be an outlier in terms of being high quality.

postgres -> pg_graphl -> graphql_codegen

^ my preferred alternative to ORMs

I don't see any reason why ORM would cease to be an anti-pattern.

It never was

> ORMs produce objects. (Duh! That’s what the O stands for.) Objects are like directed graphs—nodes that point to other nodes but not necessarily to each other. Conversely, database relational tables contain data that are always linked bidirectionally via shared keys, aka an undirected graph.

> The second issue is that ORMs sometimes make multiple roundtrips to a database by looping through a one-to-many or many-to-many relationship. This is known as the N+1 problem (1 original query + N subqueries). For instance, the following Prisma query will make a new database request for every single comment!

I'll throw in another: combinatorial blowout between cross-joins is another problem. If you inner-join collections directly, you will end up with every possible permutation of each of the collection values. This is M x N or even M x N x O x P ...

Another similar problem is the idea of having "dictionary" tables which have surrogate keys/enums/etc and you want to put the keys in other tables but have a frontend show the display value. And really the display text is not part of the object, it's part of the dictionary object. And while you can manually write a query which pulls these back/hydrates them, it's not automatic and it's not performant unless you write something manually, you are individually loading N different rows with N queries!

This problem is real, but it's actually not a problem with ORMs but rather a problem with JDBC-style/row-oriented transfer paradigms. It's an impedance mismatch with the way we query the database, not with using ORMs.

What programmers intuitively want is to pull over some pool of objects from a given query. And that doesn't necessarily map cleanly onto a single SQL query - in fact they may be different types of objects in different tables etc! And you can't just inner join and pull them back in a single row - because then you get combinatorial blowout if you have more than one collection.

Obviously you need to define what pool of objects you want, because like the dictionary example, you could potentially keep hydrating deeper and deeper into the structure. You have the query string, but you also need to have some "hydration" string. And it turns out JSON:API has already defined a nice little standard for how to define what parts of the objects you want hydrated.

https://jsonapi.org/format/#fetching-includes

So what shakes out of this is "JOPT" - Java Object Pool Transfer. You can probably build it on top of JDBC, just be aware that it probably involves multiple queries, one for each type of object. The ORM loads all objects of all types, puts them into a Map<keytype,MyObj> for each type of object in the pool, and then assembles the object graph using the foreign keys.

You can do the data pulls relatively efficiently with temporary tables containing the pkeys that you want for that object type, that you populate for each query and then inner join against the tables. "SELECT * FROM table INNER JOIN tempTbl.pkey" will run much more efficiently than a "SELECT * FROM table WHERE pkey IN (:1, :2, :3)" because every time you want to pull a different number of objects you get a different literal SQL string which blows up the query planner.

It might also be desirable to have some better SQL support for maintaining these pools as a cached result rather than having to run a separate query for each type. You could pull back the primary result and have the pools stored as an aggregation column, then turn it around and pull the other object types, but it's just clunky in general, SQL is not expressive in the ways that is ideal for object pools. It would be nice to have a "cursor" that can traverse across multiple object types, or cache the included results between multiple queries in a transaction, etc.

--

The other thing is that the "collections" model is fundamentally bad in SQL too. Really this is the fundamental problem that originally drove practical adoption of NoSQL, I think. At the end of the day everyone's data is relational, the examples of "non-relational" applications ("netflix movie reviews"/IMDB isn't relational, really?) are always extremely contrived and obviously one feature-request away from being relational, that's been bullshit since the start. But it's obviously desirable to just put the IDs of the collection members into a text field/etc and that's why people like NoSQL - collections are literally so painful in SQL that people just choose to not use SQL.

With JSONB being very advanced on Postgres you can probably do this quite easily, or you could use arrays, etc (I think arrays might be built on the text?). JSONB can have foreign keys, indexes, etc, and TOAST can store up to 5GB of text (or something like that) per item if you need it to. Practically speaking, I think you could build a very successful application without using a "join table" or a one-to-many schema, and the object transfer protocol will just pull those uuids for you. Postgres can dynamically un-aggregate text fields back into rows for inner join/etc too, if you want.

Alternatively, you can map the one-to-many or join-table into an aggregation query that turns the actual collection table into a string_agg() function or similar.

A fully general model might require these to be implemented via recursive CTEs that aggregate all of the collection object uuids into a pool for the ORM to pull back, because the relations you're filtering might be many layers deep.

But these technical details are the type of dumb technical query building that ORMs are great at. Yes, you have to come up with the code that unrolls a collection text column into a couple rows, does the WHERE query, and aggregates back out, but that's just annoying to write, not technically difficult, and ORMs don't care about annoying code.

--

Anyway that's what I think people want from ORMs. They want to write a query in HQL or an object-based/programmatic query builder, and just pull back a pool of objects that's hydrated according to the include-string. People don't actually care about the relations and it's actively tedious to manage that aspect of ORMs because there's so many ways for it to bite you without you doing anything that's obviously wrong. It works, especially on toy problems, it just runs like shit, or pulls back M x N permutations of collection members, etc. Having to janitor that is the biggest headache of ORMs and honestly SQL in general.

It's functionally insane that we still interact with ORMs in SQL-like and row-style syntax. It's an object persistence layer, the tables are an implementation detail, the entire point is decoupling that but instead we just end up with even more complex logic to make the ORM behave. That is the incongruity that I think people are searching for here. Stop making people manually build the parts of the query that handle the administrivia to hydrate the object, and just make the querying and object mapping work properly, fix the combinatorial blowouts, etc. And do it without falling back to hammering out object requests one at a time like lazy loading tends to be.

If you're pulling back records one by one, or using the dynamic/lazy-loading function for relations between objects - yeah that's gonna be super slow and you're gonna hate it. In my experience that's the #1 reason people don't like ORMs, they're not running one query, they're running 1000 queries and hammering the database hard. The lazy-loading functionality might as well not exist and tbh the default should probably be to have that getter throw a "you fucked up the query, this object hasn't been joined!" exception unless it's specifically configured to allow lazy-loading.

I am going to play the devil's advocate in this sea of ORM-haters.

First of I am an avid EF-Core developer. But I feel like I have a pretty good grasp on when I should let the datbase do the stuff the database is good at (cascading deletes). And I have experienced times where I had to use an SP because EF-Core was slow and would lock up a table or two.

This article shows an example of an ORM system that makes use of raw sql to construct its final queries. I can't be the only one that thinks that that is weird. Why use an ORM that doesn't abstract from the database - that's half the point of an ORM. And I think that is the key point of ORM... Abstraction.

Some of the advantages I can think of when it comes to ORM.

* ORM speeds up the development time. Now you don't have to write long queries. In EF-Core for example we just use LINQ either with method or query syntax. Query syntax already looks a lot like sql.*

* More tools. EF-Core Migration is great. Now I can spin up an mssql container. Quickly create a bunch of tables. Then go ahead and run integration tests.*

* Because the database is abstracted. I can now run unit test on an in-memory database where I can have whatever data I want and I am not depended on queries being only compatible with MsSql.*

* If anyone has ever worked with ASP. Not ASP.NET. Just ASP. Legacy technologies appears irregardless of age. When writing queries you might just try to convert a bool to a text string. Works fine on your computer. Shit's the bed on the server because the server's language is something other than English. Argueably this could have been prevented if ASP weren't just so ASP. But also an ORM could have prevented it.*

* I don't have to worry about the dialect of the database I communicate with. Because the ORM takes care of that. This also incldues the formatting of my DateTime variables.*

* Error handling next. I can specify in EF-Core how big many characters can be in my column. And do error handling accordingly if the amount of characters are exceeded. This is the power of code-first approach - something I believe is a strong approach to designing your database scheme.*

* Last small advantage is that I can combine it with GraphQL and then I wont have to make a new endpoint for every new "edge-case" request.*

Disadvantages I can think of.

* Slow. Naturally when you add an abstraction layer it becomes a bit slower than if you were to use raw sql. So if you work with a a lot of data at once you have to rethink your approach. And adding GraphQL into the mix is another abstraction layer that is just going to make it slower.*

* Implementing the database scheme is definitely faster using db management system rather than a code first approach.*

* Merging in EF-Core is for me still a lost cause. You first have to grab all the rows to see which matches and then update this whilst inserting the rest. You can probably pay your way out of this one using BulkExtensions at the fantastic price of $1000/year. Currently I solve this using a temp table and a stored procedure.*

* Duplicate and unnecessary operations has been apparent until EF-Core 7.0. Before when I had to delete or update an entity I would have to first fetch it up the database in order to track it before I can do either. This is two db-transactions that can be reduced to one in EF-Core 7.0 now. But before that was an absolutely pain.*

* Similarly to previous point. Adding an entity to a database is slow and can result in deadlocks if you have a lot of inserts happening rapidly. I've cirumvented this using EFCore.BulkExtensions which does an optimized insert for each dataprovider. But using a third party library even when is free is a pain... Especially since the repo owner now have gone the way of paid licensing of the library.*

Complex queries such as partion by and pivots is not possible in EF. Probably never will be. But in regards to partion by I think it should be implemented at some point mostly because I have experienced I can make a query many times faster by using it.

Summary

ORMs are great. Especially in REST services when your queries are small. But don't use ORMs when you have to handle a lot of big data.

never was