What are the limitations of Django's ORM?

creator of SQLAlchemy's response to the question is django considered now pythonic.. This shows a lots of difference and deep understanding of the system.

sqlalchemy_vs_django_db discussion in reddit

Note: Both the links are pretty long, will take time to read. I am not writing gist of them which may lead to misunderstanding.

Let me start by saying that I fully advocate the use of the ORM for most simple cases. It offers a lot of convenience when working with a very straightforward (relational) data model.

But, since you asked for shortcomings...

From a conceptual point of view, an ORM can never be an effective representation of the underlying data model. It will, at best, be an approximation of your data - and most of the time, this is enough.

The problem is that an ORM will map on a "one class -> one table" basis, which doesn't always work.

If you have a very complex data model - one which, ideally, cannot be properly represented by a single DB table - then you may find that you spend a lot of time fighting against the ORM, rather than having it work for you.

On a practical level, you'll find that there is always a workaround; some developers will be partisan in their support for/against an ORM, but I favour a hybrid approach. The Django works well for this, as you can easily drop into raw SQL as needed. Something like:

Model.objects.raw("SELECT ...")

ORMs take a lot of the work out of the 99.99% of other cases, when you're performing simple CRUD operations against your data.

In my experience, the two best reasons to avoid an ORM altogether are:

• When you have complex data that is frequently retrieved via multiple joins and aggregations. Often, writing the SQL by hand will be clearer.
• Performance. ORMs are pretty good at constructing optimised queries, but nothing can compete with writing a nice, efficient piece of SQL.

But, when all's said and done, after working extensively with Django, I can count on one hand the number of occasions that the ORM hasn't allowed me to do what I want.

Another answer from a Django fan, but:

• If you use inheritance and query for parent classes, you can't get children (while you can with SQLAlchemy).
• Group By and Having clauses are really hard to translate using the aggregate/annotate.
• Some queries the ORM make are just ridiculously long, and sometimes you and up with stuff like model.id IN [1, 2, 3... ludicrous long list]
• There is a way ask for raw where "stuff is in field" using __contains, but not "field is in stuff". Since there is no portable way to do this accross DBMS, writting raw SQL for it is really annoying. A lot of small edge cases like this one appear if your application starts to be complex, because as @Gary Chambers said, data in the DBMS doesn't always match the OO model.
• It's an abstraction, and sometimes, the abstraction leaks.

But more than often, the people I meet that don't want to use an ORM do it for the wrong reason: intellectual laziness. Some people won't make the effort to give a fair try to something because they know something and want to stick to it. And it's scary how many of them you can find in computer science, where a good part of the job is about keeping up with the new stuff.

Of course, in some area it just make sense. But usually someone with good reason not to use it, will use it in other cases. I never met any serious computer scientist saying to it all, just people not using it in some cases, and being able to explain why.

And to be fair, a lot of programmers are not computer scientists, there are biologists, mathematician, teachers or Bob, the guy next door that just wanna help. From their point of view, it's prefectly logical to not spend hours to learn new stuff when you can do what you want with your toolbox.