Software development topics I've changed my mind on

> ORMs are the devil in all languages and all implementations. Just write the damn SQL

What are the main issues people run into with ORMs? I've used Django ORM for years and written some relatively large applications using it without much problems. Complex queries and aggregations can result in quite hairy code though.

I can give an example for Prisma.

It joins in code, not in SQL. We realized this way too late because we assumed an ORM is optimally structuring code. It wasn't.

This, along with the fact that SQL is already a definitive language, made us realize that ORM's are stupid and utterly useless. I'm talking about the ones that try to pretend they're code, and not a query.

There are multiple ways you can design an ORM though, and one way is to let the user fully manage how they want the query to run, so you're pretty much structuring a query already. Why not go the extra mile and make an SQL query?

I get that ORM's are fine when you don't want to deal with writing queries (e.g. school projects) but for real world apps just take the extra minute...

Perhaps this information will be helpful context for you and for anyone else who happens to land on this post: - https://www.prisma.io/blog/prisma-orm-now-lets-you-choose-th...

- https://www.prisma.io/blog/database-vs-application-demystify...

I've seen and poked at a lot of the ORM-hating on here, and whenever I can get people to give specific examples instead of generic theory-level stuff, it's either not a problem in Django or Django has a fix you just need to learn about and use. The fixes have sometimes even been there for over a decade. It seems be leaps and bounds ahead of every other ORM out there.

The main problem is if you are not very careful it leaks its internals (ie. the relational model) into everything it touches. That's what the N+1 query problem is. Business logic shouldn't have to know that certain attributes will cause database queries. By carefully writing managers and having a rule to never use querysets anywhere else you can avoid it, but it requires everyone to understand this and all the Django docs and examples everywhere will just randomly drop a queryset into a view or do a `prefetch_related` because they know that some higher-level function is going to need access to that attribute (ie. coupling code together).

Ultimately Django just doesn't fully do the object-relational mapping. It maps single rows, but that's it. So it doesn't really support objects that contain lists or sets etc. Things like SQLAlchemy can actually map data from a relational database into plain old objects. Those objects can be instantiated (e.g. in tests) completely independently of the database. Notice how in Django you can't test anything without a database being present? Why do I need to store an object in a db just to test some method on an entity?

The default test runner creates the test database automatically yes, but you can create Django model objects without touching the database, just use the class like a constructor and don't save it: Person(name="Foo", age=30)

OK, now try that with my ridiculously simple TodoList example, e.g. `TodoList(items=["item", "item2"])`. You can't do it! Nor can you construct an empty list then add items to it etc.

>That's what the N+1 query problem is. Business logic shouldn't have to know that certain attributes will cause database queries.

I really don't get these arguments because in some form or another, ALL abstractions are leaky.

Example:

A novice developer might write a @OneToMany in hibernate without knowing the internals of the abstraction, causing n+1 problems. Two paths forward:

1. blame the abstraction

2. Learn (some) internals of the abstraction in order to use it correctly: dont do eager fetching, use join fetches, ... ( There's also tooling like hypersistence optimizer, digma, jpabuddy that comes to mind)

And by that same logic, would you berate somebody writing 'plain SQL' which - when expected with the query plan - turns out to be a very unperformant query?

Again, two options:

1. blame the abstraction

2. Learn (some) internals of the abstraction: analyze the query plan, perhaps write some indexes,...

This is how ArrayField works in the postgres-specific fields: https://docs.djangoproject.com/en/5.1/ref/contrib/postgres/f...

Or falling back to a more generic JsonField elsewhere: https://docs.djangoproject.com/en/5.1/ref/models/fields/#jso...

Well yeah, but you could have just used a document store at that point. If you start using JSON field you open up the problems with document databases (namely you can't really do many to many). In any case, postgres being awesome is something you get without any ORM!