Django Queries

Django ORM is one of the most important backend skills. As your project grows, query quality decides performance, correctness, and maintainability.

Note

A Django developer’s real ORM skill is not only about writing short queries. It is about understanding query behavior, generated SQL, execution timing, and data safety.

Introduction to Django ORM

What Django ORM actually is

Django ORM is:

• an Object Relational Mapper
• a query builder
• a lazy execution system
• a transaction-aware abstraction layer

Django ORM is not:

• magic
• a replacement for SQL knowledge

In practical terms, ORM lets you write Python code, but the database still runs SQL under the hood. Strong Django developers know both ORM style and SQL thinking.

Did you know?

Interviewers often check whether you understand lazy QuerySet execution, select_related vs prefetch_related, and transaction safety.

Mental model of query flow

flowchart LR A[Python Object Access] --> B[QuerySet<br/>Lazy, Not Executed Yet] B --> C[SQL Compiler] C --> D[Database Engine] D --> E[Result Rows] E --> F[Model Instances or Dict/Tuple Data]

This mental model helps you debug:

• where the query is created
• when it actually runs
• why performance issues appear

Core ORM concepts

Key components

Component	Purpose
Model	Table mapping
Manager	Entry point
QuerySet	Lazy query object
Field	Column definition
Lookup	WHERE clause behavior
Expression	SQL expression building

ORM is lazy

qs = Product.objects.all()  # Query is not executed yet

The query runs only when evaluated, for example when:

• iterating over qs
• converting to list(qs)
• printing it in many contexts
• slicing with evaluation
• checking truthiness (if qs:)

qs = Product.objects.filter(is_active=True)
print(qs)         # often triggers evaluation
products = list(qs)  # definitely evaluates

Caution

Lazy behavior is powerful, but accidental evaluation inside loops can cause hidden performance problems.

Resetting the database

Reset operations are useful in development, especially during early schema redesign.

Safe reset in development

python manage.py flush

This removes data but keeps schema and migration history.

Full reset (high risk)

rm db.sqlite3
rm -rf app/migrations
python manage.py makemigrations
python manage.py migrate

Danger

Never use full-reset style in production. It can destroy data and break migration history.

Managers and QuerySets

Manager

A Manager is the entry point to database operations.

Product.objects

objects is the default Manager.

QuerySet

A QuerySet represents a database query and can be chained.

qs = Product.objects.filter(price__gt=100)

Manager vs QuerySet

Manager	QuerySet
Entry point	Query builder
Usually one default per model	Chainable and immutable
Example: objects	Example: filter(), exclude()

Retrieving objects

all

Product.objects.all()

Returns a QuerySet of all rows for that model.

get

Product.objects.get(id=1)

get() expects exactly one row. It raises:

• DoesNotExist
• MultipleObjectsReturned

from django.core.exceptions import ObjectDoesNotExist

try:
    product = Product.objects.get(id=1)
except Product.DoesNotExist:
    product = None

first and last

Product.objects.order_by('id').first()
Product.objects.order_by('id').last()

These return one object or None, making them safer for optional access.

Filtering objects

filter

Product.objects.filter(price__gt=100)

exclude

Product.objects.exclude(is_active=False)

Lookup syntax

Use this pattern:

field__lookup=value

Common lookups

Lookup	Meaning
exact	=
iexact	case-insensitive exact
contains	LIKE %x%
icontains	case-insensitive contains
in	IN (...)
gt / gte	> / >=
lt / lte	< / <=
range	BETWEEN
isnull	IS NULL
startswith	LIKE x%
istartswith	case-insensitive startswith
endswith	LIKE %x
iendswith	case-insensitive endswith

Product.objects.filter(name__icontains='phone', price__range=(100, 1000))
Product.objects.filter(category_id__in=[1, 2, 3], deleted_at__isnull=True)

Complex lookups with Q objects

Default chained filters use AND logic. Q objects let you use OR, AND, and NOT explicitly.

from django.db.models import Q

Product.objects.filter(
    Q(price__gt=500) | Q(stock__lt=10)
)

NOT operator

Product.objects.filter(~Q(is_active=True))

Dynamic query building

from django.db.models import Q

query = Q()

if min_price is not None:
    query &= Q(price__gte=min_price)
if max_price is not None:
    query &= Q(price__lte=max_price)
if keyword:
    query &= Q(name__icontains=keyword) | Q(description__icontains=keyword)

products = Product.objects.filter(query)

Tip

Use Q objects whenever filters become dynamic or require OR logic. It keeps code readable and avoids if/else query duplication.

Referencing fields with F objects

F expressions perform operations at the database level. This prevents race conditions caused by read-modify-write in Python.

from django.db.models import F

Product.objects.filter(id=1).update(stock=F('stock') - 1)

Why this matters:

• atomic DB-side update
• safer under concurrency
• avoids stale in-memory values

Sorting query results

order_by

Product.objects.order_by('price')
Product.objects.order_by('-price')

Random order

Product.objects.order_by('?')

Caution

order_by('?') is expensive on large tables and should be avoided in production APIs.

Limiting results

Slicing

Product.objects.all()[:10]

This usually compiles to SQL with LIMIT 10.

Pagination internals

Paginator is built on slicing and works best with stable ordering.

from django.core.paginator import Paginator

qs = Product.objects.order_by('-created_at')
paginator = Paginator(qs, 20)
page_1 = paginator.get_page(1)

Selecting fields to query

values

Product.objects.values('id', 'name')
Product.objects.values('id', 'name', 'category__name')

Returns dictionaries.

values_list

Product.objects.values_list('name', flat=True)
Product.objects.values_list('id', 'name')

Returns tuples (or a flat list for one field with flat=True).

distinct

Product.objects.values('category').distinct()

Use cases:

• report endpoints
• lightweight API responses
• performance optimization when full model objects are not needed

Deferring fields

defer

Product.objects.defer('description')

This avoids loading large columns immediately.

only

Product.objects.only('name', 'price')

This loads only selected fields first.

Caution

Accessing a deferred field later triggers an extra query. Use defer() and only() only when you understand access patterns.

Selecting related objects

select_related for ForeignKey and OneToOne

Order.objects.select_related('user')
Order.objects.select_related('user', 'product')
Order.objects.select_related('user__profile')
# here profile is a OneToOne field on User so we can use double underscore to select it in the same query. We can also select multiple levels of related objects in the same query using double underscores.

select_related uses SQL JOIN and loads related single-value objects in one query.

prefetch_related for ManyToMany and reverse relations

Category.objects.prefetch_related('products')
Order.objects.prefetch_related('items', 'items__product')
# here order have a reverse relation to items and items have a foreign key relation to product so we can prefetch both of them in the same query using double underscores.

prefetch_related runs separate queries and merges results in Python.

N+1 query problem

flowchart TD A[Load categories] --> B[1 query] B --> C[Loop categories] C --> D[Each category loads products separately] D --> E[N extra queries] E --> F[Total: 1 + N queries]

Use select_related or prefetch_related to avoid N+1 issues.

Aggregating objects

aggregate

from django.db.models import Avg, Count, Max, Min, Sum

Product.objects.aggregate(
    avg_price=Avg('price'),
    total_stock=Sum('stock'),
    total_items=Count('id'),
    max_price=Max('price'),
    min_price=Min('price'),
)

aggregate() returns a dictionary, not a QuerySet.

Note

Because aggregate() returns a plain dict, you cannot chain queryset methods after it.

Annotating objects

annotate

annotate() adds computed fields per row.

from django.db.models import Count

Category.objects.annotate(product_count=Count('products'))

Expression family used in annotations

Expression
|__ Value
|__ F
|__ Aggregate
|__ Func

aggregate vs annotate

aggregate	annotate
Whole result summary	Per-row computed values
Returns one dictionary	Returns QuerySet

Calling database functions

Built-in function example

from django.db.models.functions import Length

Product.objects.annotate(name_length=Length('name'))

Concat examples

from django.db.models import F, Func, Value
from django.db.models.functions import Concat

Product.objects.annotate(
    full_name=Func(F('first_name'), Value(' '), F('last_name'), function='CONCAT')
)

Product.objects.annotate(
    full_name=Concat('first_name', Value(' '), 'last_name')
)

Use Value(' ') for constants. F(' ') would incorrectly look for a field named space.

Common functions to know

• Length
• Upper
• Lower
• Concat
• ExtractYear

When to use Func directly

Use Func for database-specific functions that Django does not expose as built-in helpers.

Grouping data

Grouping in ORM is often done using values(...).annotate(...).

from django.db.models import Count

Product.objects.values('category').annotate(total=Count('id'))

This compiles to SQL with GROUP BY category.

Working with ExpressionWrapper

Use ExpressionWrapper when Django cannot infer expression output type.

from django.db.models import DecimalField, ExpressionWrapper, F

Product.objects.annotate(
    total_price=ExpressionWrapper(
        F('price') * F('quantity'),
        output_field=DecimalField(max_digits=12, decimal_places=2),
    )
)

Querying generic relationships

When using GenericForeignKey, query by ContentType and target object id.

from django.contrib.contenttypes.models import ContentType

ct = ContentType.objects.get_for_model(Product)
Comment.objects.filter(content_type=ct, object_id=product_id)

Trade-offs:

• no normal foreign key DB constraint
• joins are harder
• can be slower than standard relations

Custom managers

Custom managers encapsulate reusable query logic and reduce duplication.

from django.db import models


class ActiveManager(models.Manager):
    def active(self):
        return self.filter(is_active=True)


class Product(models.Model):
    is_active = models.BooleanField(default=True)
    objects = models.Manager()
    active_objects = ActiveManager()

Usage:

Product.active_objects.active()

Understanding QuerySet cache

QuerySet results are cached after evaluation.

qs = Product.objects.all()
list(qs)  # query runs
list(qs)  # uses cached results in same queryset object

Cache is effectively bypassed when:

• a new queryset is created
• queryset chain changes (filter, exclude, etc.)
• data changes externally in database

qs = Product.objects.all()
qs2 = qs.filter(price__gt=100)  # new queryset, new SQL

Creating objects

create

product = Product.objects.create(name='Phone', price=1000)

Instance plus save

product = Product()
product.name = 'Phone'
product.price = 1000
product.save()

bulk_create

Product.objects.bulk_create([
    Product(name='Phone', price=1000),
    Product(name='Laptop', price=2000),
])

bulk_create is fast, but by default:

• model save() logic is not called per object
• signals are not fired in the usual per-row way

Updating objects

update

Product.objects.filter(id=1).update(price=200)

update() writes directly in SQL and does not call model save().

Fetch then save

product = Product.objects.get(pk=1)
product.price = 200
product.save()

Use fetch-then-save when you need validation, custom save logic, or signals.

Caution

Avoid patterns like creating Product(pk=1) and calling save for partial updates unless you fully control all field values. It can overwrite data unexpectedly.

Deleting objects

delete

Product.objects.filter(id=1).delete()

Deletes obey relation on_delete behavior (CASCADE, PROTECT, etc.).

Always review cascade impact before production deletes.

Transactions

Transactions protect consistency across multiple related writes.

from django.db import transaction

with transaction.atomic():
    order.save()
    payment.save()

If an exception occurs, all changes inside atomic() rollback.

Nested transactions use savepoints internally.

flowchart LR A[Begin atomic block] --> B[Write order] B --> C[Write payment] C --> D{Any exception?} D -- No --> E[Commit] D -- Yes --> F[Rollback]

Executing raw SQL queries

ORM should be default, but raw SQL is valid for complex and DB-specific cases.

raw

Product.objects.raw(
    'SELECT * FROM product WHERE price > %s',
    [100],
)

cursor

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute('SELECT COUNT(*) FROM product')
    row = cursor.fetchone()

Danger

Always parameterize raw SQL inputs. String concatenation with user input can lead to SQL injection vulnerabilities.

Performance and safety quick comparison

Read Performance

Use these in this order:

• values() or values_list() when full model instances are not required.
• select_related() for FK and OneToOne relations.
• prefetch_related() for ManyToMany and reverse relations.
• Avoid N+1 queries by checking Django Debug Toolbar or query logs.

Write Safety

Use these rules:

• Use F() for atomic counter and stock updates.
• Use transaction.atomic() for multi-step writes.
• Prefer get() then save() when model hooks must run.
• Be careful with bulk operations because they skip common hooks.

Summary

Lazy Evaluation

QuerySets are lazy. SQL runs only on evaluation.

Immutability

QuerySet chaining creates new QuerySets.

Related Loading

Use select_related for joins and prefetch_related for separate query merge.

Q and F

Q handles OR/NOT logic, F handles DB-side atomic expressions.

annotate vs aggregate

annotate adds per-row computed values, aggregate returns whole-query summary.

Transactions

Use atomic blocks for consistency and rollback safety.

Custom Managers

Put reusable query logic in managers/querysets for clean architecture.

Raw SQL

Use only when needed and always parameterize inputs.

Tip

ORM mastery means knowing what query will run, when it will run, and how safe it is under real-world load.