Django Queries

Django ORM is one of the most important skills for any backend developer to learn. As your project grows bigger, how well you write your queries will decide how fast your app runs, how correct your data stays, and how easy your code is to maintain later.

Note

Being good at Django ORM is not just about writing short queries. It is about understanding how a query behaves, what SQL it creates, when it actually runs, and how safe it is for your data.

Introduction to Django ORM

What Django ORM actually is

Django ORM is:

• a tool that connects your Python objects to database tables (this is called an Object Relational Mapper)
• a tool that helps you build queries
• a system that waits before running a query (this is called lazy execution)
• a layer that understands transactions, so it can keep multi-step database changes safe

Django ORM is not:

• something magical
• a way to skip learning SQL completely

In simple words, ORM lets you write normal Python code, but behind the scenes, the database still runs SQL to actually get the work done. Good Django developers understand both how ORM works and how SQL thinks.

Did you know?

Interviewers often ask questions to check if you understand lazy QuerySet execution, the difference between select_related and prefetch_related, and how to keep transactions safe.

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 simple picture helps you understand:

• where a query gets created
• when it actually runs
• why performance problems show up

Core ORM concepts

Key components

Component	Purpose
Model	Connects to a database table
Manager	The starting point for queries
QuerySet	A query that waits before running
Field	Defines a column
Lookup	Used to build WHERE conditions
Expression	Used to build SQL expressions

ORM is lazy

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

The query only runs when you actually use the result. For example, this happens when you:

• loop through qs
• turn it into a list using list(qs)
• print it in most cases
• slice it in a way that forces it to run
• check if it is true or false using if qs:

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

Caution

Lazy behavior is very useful, but if a query accidentally runs again and again inside a loop, it can quietly slow down your whole app.

Resetting the database

Resetting your database is useful while you are still developing your app, especially when you are changing your database structure a lot.

Safe reset in development

python manage.py flush

This command removes your data but keeps your tables and migration history safe.

Full reset (high risk)

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

Danger

Never do a full reset like this on a live (production) website. It can delete real data and break your migration history.

Managers and QuerySets

Manager

A Manager is the starting point for talking to your database.

Product.objects

objects is the default Manager.

QuerySet

A QuerySet is basically a database query, and you can chain many of them together.

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

Manager vs QuerySet

Manager	QuerySet
The starting point	Builds the query
Usually one default per model	Can be chained, and stays unchanged once created (immutable)
Example: objects	Example: filter(), exclude()

Retrieving objects

all

Product.objects.all()

This gives you a QuerySet with all the rows for that model.

get

Product.objects.get(id=1)

get() expects to find exactly one row. If it finds none, or finds more than one, it gives an error:

• 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 if nothing is found. This makes them safer to use when the data might not always exist.

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

When you chain filters one after another, Django uses AND logic between them by default. Q objects let you also use OR, AND, and NOT whenever you need them.

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 your filters need to change dynamically or need OR logic. It keeps your code clean and saves you from writing many repeated if/else blocks.

Referencing fields with F objects

F expressions let the database do the calculation itself, instead of doing it in Python. This helps avoid a problem called a race condition, which can happen when you read a value in Python, change it, and then save it back.

from django.db.models import F

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

Why this matters:

• the update happens safely in one single database step (atomic)
• it is safer when many users update the same data at the same time
• it avoids using old, outdated values that were sitting in memory

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 slow and costly on large tables. Avoid using it in production APIs.

Limiting results

Slicing

Product.objects.all()[:10]

This usually turns into SQL with LIMIT 10.

Pagination internals

Paginator uses slicing internally, and it works best when your data has a fixed, stable order.

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')

This returns dictionaries.

values_list

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

This returns tuples. If you select only one field and use flat=True, it returns a simple flat list instead.

distinct

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

When to use this:

• report-type API endpoints
• lightweight API responses
• improving performance when you do not need the full model object

Deferring fields

defer

Product.objects.defer('description')

This skips loading large columns right away, so the query runs faster.

only

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

This loads only the fields you choose, first.

Caution

If you try to access a field that was deferred (skipped), Django will run an extra query to get it. So only use defer() and only() when you clearly understand how your data will be accessed.

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 an SQL JOIN to load related single-value objects in the same 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 one by one, and then joins the results together 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 this N+1 problem.

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() gives you back a dictionary, not a QuerySet.

Note

Since aggregate() returns a plain dictionary, you cannot chain any more queryset methods after it.

Annotating objects

annotate

annotate() adds a calculated field to each 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
Gives one summary for everything	Gives a calculated value for each row
Returns a single dictionary	Returns a 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(' ') when you want a fixed constant value. If you use F(' ') instead, Django will wrongly try to look for a field named space.

Common functions to know

• Length
• Upper
• Lower
• Concat
• ExtractYear

When to use Func directly

Use Func when you need a database-specific function that Django does not already provide as a built-in helper.

Grouping data

In Django ORM, grouping data is usually done using values(...).annotate(...) together.

from django.db.models import Count

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

This turns into SQL with GROUP BY category.

Working with ExpressionWrapper

Use ExpressionWrapper when Django cannot automatically figure out what type the result of an expression should be.

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 you use GenericForeignKey, you query using ContentType along with the target object’s 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:

• there is no normal foreign key constraint in the database
• joins become harder to do
• it can be slower than standard relations

Custom managers

Custom managers let you keep reusable query logic in one place, so you do not repeat the same code again and again.

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

Once a QuerySet runs, its results get saved (cached) for reuse.

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

This cache is skipped (not used) when:

• a new queryset is created
• the queryset chain changes (filter, exclude, etc.)
• the data changes outside Django, directly in the 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:

• it does not call each object’s save() method
• it does not fire signals the normal way for each row

Updating objects

update

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

update() writes directly to the database using SQL, and it does not call the model’s save() method.

Fetch then save

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

Use the fetch-then-save method when you need validation, custom logic inside save(), or you want signals to fire.

Caution

Avoid creating something like Product(pk=1) and calling save just to update one or two fields, unless you are sure about every field’s value. This can accidentally overwrite your existing data.

Deleting objects

delete

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

Deleting follows the on_delete rules you set on relations (like CASCADE, PROTECT, etc.).

Always check what else will get deleted (the cascade effect) before deleting data in production.

Transactions

Transactions help keep your data consistent when you are doing multiple related writes together.

from django.db import transaction

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

If an error happens, all the changes made inside the atomic() block get undone (rolled back).

If you nest transactions inside each other, Django uses savepoints internally to manage them.

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

You should normally use ORM by default, but raw SQL is okay to use for complex or database-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 use parameters for raw SQL inputs. If you directly join user input into your SQL string, it can lead to SQL injection, which is a serious security risk.

Performance and safety quick comparison

Read Performance

Use these in this order:

• values() or values_list() when you do not need full model instances.
• select_related() for FK and OneToOne relations.
• prefetch_related() for ManyToMany and reverse relations.
• Check Django Debug Toolbar or your query logs to avoid N+1 queries.

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. The SQL only runs when you actually use the result.

Immutability

Every time you chain a QuerySet, Django creates a brand new QuerySet.

Related Loading

Use select_related for joins, and prefetch_related for results that come from separate queries and get merged together.

Q and F

Q is used for OR/NOT logic, and F is used for safe, database-side calculations.

annotate vs aggregate

annotate adds a calculated value to each row, while aggregate gives one summary for the whole query.

Transactions

Use atomic blocks to keep your data consistent and to safely roll back if something goes wrong.

Custom Managers

Keep reusable query logic inside managers and querysets to keep your code clean and organized.

Raw SQL

Use raw SQL only when really needed, and always parameterize your inputs.

Tip

Being really good at ORM means knowing exactly what query will run, when it will run, and how safe it is once real users are using your app.