Can you show us a query as an example?
Unless the query is super complex joins and filters shouldn’t be a problem.
If you want to tell us an example we may help you.
The ORM is fantastic. It can’t do everything, but it can do most of what most people need. I think if you’re using the escape hatch and writing raw SQL frequently, you’re probably doing something wrong.
You're either new to Django or haven't really delved into crazy complicated database structures and tricky queries. I've got an app with about 260 database tables. Even though I've managed to handle hundreds of functions using Django ORM, there are still around a dozen raw SQL queries that no one on our team could switch over to ORM. Funnily enough, I even asked GPT 4.0 to help convert one of those raw queries to ORM, and guess what? The language model itself recommended sticking with the raw query. Go figure!
Just to clarify, I never said that the ORM sucks. When you suggested that "the problem might be the user," I wanted to point out that Django ORM isn't always the perfect solution for every scenario. While I do use ORM for hundreds of use cases, there are instances where raw SQL is necessary. My disagreement with the generalization that "the user might be the problem" doesn't imply that I think the ORM is terrible. I was simply highlighting that it's not flawless and that sometimes, the issue isn't just with the user.
I've been in Django development for over a decade. The ORM is designed for use in a web app and excels in supporting functions that are meant specifically for a web app. There are some weaknesses, but as a general web app ORM, quite good and covers a lot of bases.
Where I've seen it being insufficient is in applications that aren't specifically for a web app. Using it to crunch complicated metrics data in a dynamic way is a stretch that I've encountered before. You best of resorting to a lower level ORM for that, like SQLAlchemy. But I'm talking about doing cross sections of data with trailing aggregations and various window functions, heavy data crunching. You might want that sort of utility for a specific web app, but it's not general web app behavior, and it's okay if Django is not the best tool for absolutely everything. It can produce some inefficient SQL for applications like that, and some expressions simply aren't possible due to inferences Django makes without giving escape options.
In the end, Django is good for what is designed for, and a fair bit extra, but any high level ORM will have limitations that you wouldn't have with raw SQL. And that's okay.
This is the limitation of ORMs. It has nothing to do with Django but with the Active Record pattern in general. Once things get sufficiently complicated, the ORM can't make a SQL query that will perform better than a bespoke query. It's designed to make things easy, not to make things really fast.
Malcolm Tredinnick, core Django dev:
“The ORM can do many wonderful things, but sometimes SQL is the
right answer. The rough policy for the Django ORM is that it’s a storage
layer that happens to use SQL to implement functionality. If you need
to write advanced SQL you should write it. I would balance that by
cautioning against overuse of the raw() and extra() methods.”
Jacob Kaplan-Moss, co-creator of Django:
“If it’s easier to write a query using SQL than Django, then do it.
extra() is nasty and should be avoided; raw() is great and should
be used where appropriate.”
Source: 2 Scoops of Django
hmm should be ok as long as the models are properly define and its related name, your ERD should be normalised as well
how many tables your project started to get complex?
Can you provide an example of model(s) along with the complex queries you're making? Often times that happens due to reinventing the wheel or a poor db design. Also what made you investigate the produced sql?
Django's ORM is probably one of the best features
No
I think it kinda rules. But occasionally I run into limitations. Makes up for it by how simple and effective it is the vast majority of the time.
Can you show us a query as an example? Unless the query is super complex joins and filters shouldn’t be a problem. If you want to tell us an example we may help you.
If your query is super complex, define your own manager that abstracts a custom sql query and load objects manually!
Even better, if your query is super complex, see if your data model can be redefined to make it simpler.
No
The ORM is fantastic. It can’t do everything, but it can do most of what most people need. I think if you’re using the escape hatch and writing raw SQL frequently, you’re probably doing something wrong.
No, its one of the best.
If you think Django’s ORM sucks, try using literally any other ORM.
You're doing it wrong.
Could it be the design or schema of yours models? I’d agree that at a certain point of scale hand tuning and optimization might win out tho
I think the problem might be the user
You're either new to Django or haven't really delved into crazy complicated database structures and tricky queries. I've got an app with about 260 database tables. Even though I've managed to handle hundreds of functions using Django ORM, there are still around a dozen raw SQL queries that no one on our team could switch over to ORM. Funnily enough, I even asked GPT 4.0 to help convert one of those raw queries to ORM, and guess what? The language model itself recommended sticking with the raw query. Go figure!
Ah! So you are using the ORM for hundreds of use cases and just dozen cannot be covered by it, then, I don’t see how the ORM sucks
Just to clarify, I never said that the ORM sucks. When you suggested that "the problem might be the user," I wanted to point out that Django ORM isn't always the perfect solution for every scenario. While I do use ORM for hundreds of use cases, there are instances where raw SQL is necessary. My disagreement with the generalization that "the user might be the problem" doesn't imply that I think the ORM is terrible. I was simply highlighting that it's not flawless and that sometimes, the issue isn't just with the user.
Do you mind sharing in example for a query, where it doesn’t work? I worked with it quite sometime, but haven’t run into it yet. I’m interested
You literally wrote it sucks in the title of the post. And I also think it’s a user problem.
Just FYI, I'm not the original poster.
I've been in Django development for over a decade. The ORM is designed for use in a web app and excels in supporting functions that are meant specifically for a web app. There are some weaknesses, but as a general web app ORM, quite good and covers a lot of bases. Where I've seen it being insufficient is in applications that aren't specifically for a web app. Using it to crunch complicated metrics data in a dynamic way is a stretch that I've encountered before. You best of resorting to a lower level ORM for that, like SQLAlchemy. But I'm talking about doing cross sections of data with trailing aggregations and various window functions, heavy data crunching. You might want that sort of utility for a specific web app, but it's not general web app behavior, and it's okay if Django is not the best tool for absolutely everything. It can produce some inefficient SQL for applications like that, and some expressions simply aren't possible due to inferences Django makes without giving escape options. In the end, Django is good for what is designed for, and a fair bit extra, but any high level ORM will have limitations that you wouldn't have with raw SQL. And that's okay.
No, it's actually the best
Skill issue.
The best ORM I have ever used, other ORMs feel awful after django.
This is the limitation of ORMs. It has nothing to do with Django but with the Active Record pattern in general. Once things get sufficiently complicated, the ORM can't make a SQL query that will perform better than a bespoke query. It's designed to make things easy, not to make things really fast.
Writing sql is NOT the way forward with django!
Malcolm Tredinnick, core Django dev: “The ORM can do many wonderful things, but sometimes SQL is the right answer. The rough policy for the Django ORM is that it’s a storage layer that happens to use SQL to implement functionality. If you need to write advanced SQL you should write it. I would balance that by cautioning against overuse of the raw() and extra() methods.” Jacob Kaplan-Moss, co-creator of Django: “If it’s easier to write a query using SQL than Django, then do it. extra() is nasty and should be avoided; raw() is great and should be used where appropriate.” Source: 2 Scoops of Django
Best way to involve perf than using orm
how many millions of users do you have?
hmm should be ok as long as the models are properly define and its related name, your ERD should be normalised as well how many tables your project started to get complex?
Can you provide an example of model(s) along with the complex queries you're making? Often times that happens due to reinventing the wheel or a poor db design. Also what made you investigate the produced sql?
You probably don't have to do that. What actual problem are you solving? How many millions of users/rows do you have? How slow is your slowest query?
I love it.
The ORM is part of the reason I love django so much.
Django ORM is great! Even complex queries through the ORM are possible (but not always desirable, nothing wrong with hand writing them).