[Django]-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.

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.

There are various problems that seem to arise with every Object-Relational Mapping system, about which I think the classic article is by Ted Neward, who described the subject as “The Vietnam of Computer Science”. (There’s also a followup in response to comments on that post and some comments from Stack Overflow’s own Jeff Atwood here.)

In addition, one simple practical problem with ORM systems is they make it hard to see how many queries (and which queries) are actually being run by a given bit of code, which obviously can lead to performance problems. In Django, using the assertNumQueries assertion in your unit tests really helps to avoid this, as does using django-devserver, a replacement for runserver that can output queries as they’re being performed.

One of the biggest problem that come to mind is that Building inheritance into Django ORM’s is difficult. Essentially this is due to the fact that (Django) ORM layers are trying to bridge the gap by being both relational & OO. Another thing is of course multiple field foreign keys.

One charge leveled at Django ORM is that they abstract away so much of the database engine that writing efficient, scalable applications with them is impossible. For some kinds of applications – those with millions of accesses and highly interrelated models — this assertion is often true.

The vast majority of Web applications never reach such huge audiences and don’t achieve that level of complexity. Django ORMs are designed to get projects off the ground quickly and to help developers jump into database-driven projects without requiring a deep knowledge of SQL. As your Web site gets bigger and more popular, you will certainly need to audit performance as described in the first section of this article. Eventually, you may need to start replacing ORM-driven code with raw SQL or stored procedures (read SQLAlchemy etc).

Happily, the capabilities of Django’s ORMs continue to evolve. Django V1.1’s aggregation library is a major step forward, allowing efficient query generation while still providing a familiar object-oriented syntax. For even greater flexibility, Python developers should also look at SQLAlchemy, especially for Python Web applications that don’t rely on Django.

IMHO the bigger issue with Django ORM is the lack of composite primary keys, this prevents me from using some legacy databases with django.contrib.admin.

I do prefer SqlAlchemy over Django ORM, for projects where django.contrib.admin is not important I tend to use Flask instead of Django.

Django 1.4 is adding some nice “batch” tools to the ORM.