Django

Django ORM & QuerySets👨‍💻

The Django ORM is not just a convenience layer over SQL. It is a full query-building toolkit that, when used properly, generates SQL as efficient as what you would write by hand. The central abstraction is the QuerySet -- a lazy, chainable object that represents a database query. Nothing hits the database until you iterate, slice, or call a terminal method like .count() or .exists().

Most Django performance problems trace back to misusing QuerySets: evaluating them too early, triggering N+1 queries by accessing related objects in loops, or pulling entire tables into Python when the database could do the work. This guide covers the patterns that separate production-grade Django code from tutorial-level code. If you are on Django 6 (Python 3.12+), you also get mature async ORM support, which we will touch on where relevant.

Key Takeaways

  • 1QuerySets are lazy -- they build up a SQL query incrementally and only execute when you consume the result. Chaining `.filter()`, `.exclude()`, and `.order_by()` does not hit the database.
  • 2Q objects enable OR, AND, and NOT logic that keyword arguments alone cannot express. They are essential for search features and permission-based filtering.
  • 3F expressions reference column values at the database level, enabling atomic updates and field-to-field comparisons without loading objects into Python.
  • 4select_related uses SQL JOINs for ForeignKey/OneToOne relationships. prefetch_related uses separate queries for ManyToMany and reverse FK relations. Mixing them up is the fastest way to tank performance.
  • 5aggregate() returns a single dictionary of summary statistics. annotate() adds a computed column to every row in the QuerySet. Know which one you need before you write the query.
  • 6Django 6 supports async QuerySet methods (async for, acount, aexists, aget, etc.) so you can use the ORM inside async views without wrapping everything in sync_to_async.

Master django orm & querysets

Take the Django ORM Mastery course with hands-on lessons and challenges.

Examples

QuerySet chaining -- build queries incrementally

python

Each .filter() and .select_related() call returns a new QuerySet without executing SQL. The query only runs when you slice with [:20] and iterate. This pattern lets you build complex queries conditionally without performance overhead.

Q objects -- OR logic and dynamic query building

python

Q objects use | for OR, & for AND, and ~ for NOT. The dynamic search pattern with reduce() is what you actually use in production -- hardcoding Q(title__icontains=q) | Q(body__icontains=q) does not scale when the field list changes.

F expressions -- database-level operations without Python round-trips

python

F expressions tell the database to use the column value directly. The counter increment is atomic because the read and write happen in a single SQL UPDATE -- two concurrent requests cannot clobber each other. This is the right way to handle counters, stock levels, and any value that changes under concurrency.

select_related and prefetch_related -- eliminating N+1 queries

python

select_related generates a JOIN and works for ForeignKey and OneToOneField only. prefetch_related fires a second query with an IN clause and works for ManyToMany and reverse FK. The Prefetch object gives you control over filtering and ordering of the prefetched set. Using the wrong method for the relationship type either raises an error or silently degrades performance.

Aggregation and annotation -- push computation to the database

python

aggregate() collapses the QuerySet into one row of numbers. annotate() keeps all rows but tacks on computed columns you can filter and order by. The Count with filter= uses SQL conditional aggregation (CASE WHEN) which is far more efficient than running separate queries per status.

Common Mistakes

Mistake:

Accessing related objects in a loop without select_related or prefetch_related, causing N+1 queries. A page listing 50 articles with authors fires 51 queries instead of 1.

Fix:

Always use select_related for ForeignKey/OneToOne and prefetch_related for ManyToMany/reverse FK before iterating. Install django-debug-toolbar in development -- it makes N+1 problems impossible to miss.

Mistake:

Using Python loops to update fields based on their current value, e.g. loading every product, changing the price in Python, and calling .save() in a loop. This is slow and has race conditions.

Fix:

Use F expressions for database-level updates: Product.objects.filter(...).update(price=F('price') * 0.9). One SQL statement, atomic, no round-trips.

Mistake:

Calling .count() to check if any rows exist. count() scans all matching rows and returns an integer. If you only need a boolean answer, you are doing unnecessary work.

Fix:

Use .exists() instead. It adds LIMIT 1 to the SQL and returns True/False after finding the first match. The performance difference is dramatic on large tables.

Mistake:

Filtering on a prefetched relation (e.g. article.tags.filter(active=True) after prefetch_related('tags')), which breaks the prefetch cache and fires a new query per object.

Fix:

Use a Prefetch object with a filtered queryset and to_attr: Prefetch('tags', queryset=Tag.objects.filter(active=True), to_attr='active_tags'). Access via article.active_tags without extra queries.

Best Practices

  • Use .only() or .defer() when you need a subset of columns, and .values_list(flat=True) when you just need a list of IDs or names. Full model instantiation is expensive when you do not need it.
  • Define custom managers with default select_related/prefetch_related calls for your most common access patterns. This way every query in the codebase benefits without developers having to remember.
  • Always call .explain() on slow queries during development. Look for sequential scans on large tables and add indexes through Meta.indexes with explicit names.
  • Use .exists() for boolean checks, .count() only when you need the actual number, and .iterator() when processing large result sets to avoid loading everything into memory.
  • In Django 6, prefer async ORM methods (async for qs, await qs.acount(), await qs.aget()) inside async views instead of wrapping sync ORM calls with sync_to_async. The async ORM path avoids thread pool overhead.

Summary

Django QuerySets are lazy query builders. Chain .filter(), .exclude(), .annotate(), and .order_by() freely -- the SQL only executes when you consume the result. Use Q objects for OR/NOT logic and dynamic search. Use F expressions for atomic updates and field-to-field comparisons. Eliminate N+1 queries with select_related (JOIN for FK/O2O) and prefetch_related (separate query for M2M/reverse FK). Push aggregation to the database with aggregate() and annotate() instead of computing in Python. On Django 6, use the async ORM methods in async views for better concurrency.

Practice Django with hands-on challenges

Learn django orm & querysets hands-on in your IDE

Interactive lessons and challenges on Stanza, practice in VS Code, Cursor, or the web.

Related Concepts

Django Class Based ViewsDjango SignalsDjango Testing

Related Cheatsheets

Django Orm Cheatsheet

Master Django with Stanza

Interactive lessons and challenges, right in your code editor.

Check the free courses. No credit card.