Django ORM Complete Guide: Models, Queries & Best Practices

Django's Object-Relational Mapper is one of the most powerful features of the framework. It lets you define your database schema as Python classes, query data using a fluent Python API instead of writing raw SQL, and handle schema changes through an automatic migration system. Whether you are building a small prototype or a production application serving millions of requests, the Django ORM provides the tools to interact with your database efficiently and safely.

This guide covers everything from defining your first model to advanced performance optimization. Every section includes working code examples that you can use directly in your Django project. We cover models and fields, migrations, QuerySet operations, field lookups, aggregation and annotation, Q objects, F expressions, related object queries, raw SQL, transactions, and the patterns that distinguish a well-optimized Django application from one that drowns in database queries.

1. What Is Django ORM and Why Use It

An Object-Relational Mapper translates between the object-oriented world of Python and the relational world of SQL databases. Instead of writing SELECT * FROM articles WHERE status = 'published' ORDER BY created_at DESC, you write Article.objects.filter(status='published').order_by('-created_at'). The ORM generates the SQL, executes it against your database, and returns the results as Python objects.

Django's ORM is tightly integrated with the rest of the framework. Here is why it is the default choice for Django projects:

• Database abstraction — write your code once and deploy to PostgreSQL, MySQL, SQLite, or Oracle without changing a single query. The ORM handles dialect differences automatically.
• Automatic migrations — when you change a model, Django generates migration files that alter the database schema. No more writing ALTER TABLE statements by hand.
• SQL injection protection — all ORM queries use parameterized SQL by default, eliminating the most common class of web application vulnerabilities.
• Lazy evaluation — QuerySets do not hit the database until you actually need the data. You can chain filters, ordering, and slicing without executing unnecessary queries.
• Admin integration — defining a model automatically gives you a full CRUD admin interface. The ORM's introspection powers the admin, forms, serializers, and REST framework.

The ORM is not perfect for every situation. Highly complex reporting queries, recursive CTEs, or database-specific features sometimes require raw SQL. But for 90% of web application queries, the ORM is faster to write, easier to maintain, and safer than hand-written SQL.

2. Models and Fields

A Django model is a Python class that maps to a database table. Each attribute on the model represents a column. Django provides field types for every common data type, plus relationships between tables.

from django.db import models
from django.utils import timezone

class Author(models.Model):
    name = models.CharField(max_length=200)
    email = models.EmailField(unique=True)
    bio = models.TextField(blank=True)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        ordering = ['name']

    def __str__(self):
        return self.name


class Category(models.Model):
    name = models.CharField(max_length=100, unique=True)
    slug = models.SlugField(max_length=100, unique=True)
    description = models.TextField(blank=True)

    class Meta:
        verbose_name_plural = 'categories'

    def __str__(self):
        return self.name


class Article(models.Model):
    STATUS_CHOICES = [
        ('draft', 'Draft'),
        ('published', 'Published'),
        ('archived', 'Archived'),
    ]

    title = models.CharField(max_length=300)
    slug = models.SlugField(max_length=300, unique=True)
    body = models.TextField()
    status = models.CharField(max_length=10, choices=STATUS_CHOICES, default='draft')
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name='articles')
    categories = models.ManyToManyField(Category, related_name='articles', blank=True)
    views = models.PositiveIntegerField(default=0)
    published_at = models.DateTimeField(null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        ordering = ['-published_at']
        indexes = [
            models.Index(fields=['status', 'published_at']),
            models.Index(fields=['slug']),
        ]

    def __str__(self):
        return self.title

Common Field Types

# Text: CharField, TextField, SlugField, EmailField, URLField
# Numeric: IntegerField, DecimalField(max_digits, decimal_places), FloatField
# Boolean/Date: BooleanField, DateField, DateTimeField(auto_now_add/auto_now)
# Files: ImageField(upload_to=), FileField(upload_to=)
# JSON: JSONField(default=dict)
# Relationships:
author = models.ForeignKey(Author, on_delete=models.CASCADE)    # many-to-one
profile = models.OneToOneField(User, on_delete=models.CASCADE)  # one-to-one
tags = models.ManyToManyField(Tag, blank=True)                  # many-to-many

# Common field options
# null=True (allow NULL), blank=True (allow empty in forms),
# default=value, unique=True, db_index=True, choices=CHOICES

# on_delete: CASCADE (delete related), PROTECT (prevent deletion),
# SET_NULL (requires null=True), SET_DEFAULT, DO_NOTHING

3. Migrations

Migrations are Django's way of propagating changes you make to your models into the database schema. When you add a field, rename a model, or change a relationship, Django generates a migration file that contains the SQL operations to make those changes.

# Create migration files after model changes
python manage.py makemigrations

# Create migrations for a specific app
python manage.py makemigrations blog

# See what SQL a migration will execute
python manage.py sqlmigrate blog 0001

# Apply all pending migrations
python manage.py migrate

# Apply migrations for a specific app
python manage.py migrate blog

# Check migration status
python manage.py showmigrations

# Reverse a migration (roll back)
python manage.py migrate blog 0003  # go back to migration 0003

# Create an empty migration (for data migrations)
python manage.py makemigrations blog --empty --name populate_slugs

Data Migrations

# Create empty migration: python manage.py makemigrations blog --empty --name populate_slugs
from django.db import migrations
from django.utils.text import slugify

def populate_slugs(apps, schema_editor):
    Article = apps.get_model('blog', 'Article')
    for article in Article.objects.filter(slug=''):
        article.slug = slugify(article.title)
        article.save(update_fields=['slug'])

class Migration(migrations.Migration):
    dependencies = [('blog', '0004_article_slug')]
    operations = [migrations.RunPython(populate_slugs, migrations.RunPython.noop)]

Always provide a reverse function so migrations can be rolled back. Never import models directly in migration files — use apps.get_model() to get the historical version of the model.

4. QuerySets: The Core Query API

A QuerySet represents a collection of objects from your database. It is lazy: no database query is executed until the QuerySet is evaluated. You can chain multiple operations to build complex queries without any performance cost until you actually need the results.

# all() - returns a QuerySet of all objects
articles = Article.objects.all()

# filter() - returns objects matching the conditions (AND)
published = Article.objects.filter(status='published')
recent = Article.objects.filter(
    status='published',
    published_at__gte=timezone.now() - timedelta(days=30)
)

# exclude() - returns objects NOT matching the conditions
non_drafts = Article.objects.exclude(status='draft')

# get() - returns a single object (raises DoesNotExist or MultipleObjectsReturned)
article = Article.objects.get(slug='django-orm-guide')

# Safer get with handling
try:
    article = Article.objects.get(pk=42)
except Article.DoesNotExist:
    article = None

# Or use first() which returns None instead of raising
article = Article.objects.filter(slug='django-orm-guide').first()

# order_by() - specify result ordering
newest = Article.objects.order_by('-published_at')        # descending
alphabetical = Article.objects.order_by('title')           # ascending
multi_sort = Article.objects.order_by('status', '-views')  # multiple fields

# Chaining - build queries step by step
queryset = Article.objects.all()
queryset = queryset.filter(status='published')
queryset = queryset.exclude(views=0)
queryset = queryset.order_by('-published_at')
# No database query has been executed yet!

# Evaluation triggers (these hit the database)
article_list = list(queryset)    # convert to list
first = queryset[0]              # indexing
for a in queryset:               # iteration
    print(a.title)
count = queryset.count()         # COUNT query
exists = queryset.exists()       # efficient existence check

Slicing, Values, and Counting

# Slicing (translates to LIMIT/OFFSET)
first_five = Article.objects.all()[:5]          # LIMIT 5
next_five = Article.objects.all()[5:10]         # OFFSET 5 LIMIT 5

# values() returns dicts, values_list() returns tuples
Article.objects.values('title', 'views')          # [{'title': '...', 'views': 150}]
titles = Article.objects.values_list('title', flat=True)  # ['Django ORM Guide', ...]
unique = Article.objects.values_list('status', flat=True).distinct()

# count() vs len() - always prefer count()
count = Article.objects.count()      # SELECT COUNT(*) - fast
count = len(Article.objects.all())   # fetches ALL rows then counts - slow

5. Field Lookups

Field lookups are how you express SQL WHERE clauses in the Django ORM. They are specified as keyword arguments to filter(), exclude(), and get() using the double-underscore syntax: field__lookup=value.

# Exact match (default when no lookup is specified)
Article.objects.filter(status__exact='published')
Article.objects.filter(status='published')  # shorthand, same result

# Case-insensitive exact match
Article.objects.filter(title__iexact='django orm guide')

# Contains (LIKE '%value%')
Article.objects.filter(title__contains='Django')
Article.objects.filter(title__icontains='django')  # case-insensitive

# Starts with / ends with
Article.objects.filter(title__startswith='Django')
Article.objects.filter(title__istartswith='django')
Article.objects.filter(slug__endswith='-guide')

# Comparison operators
Article.objects.filter(views__gt=100)       # greater than
Article.objects.filter(views__gte=100)      # greater than or equal
Article.objects.filter(views__lt=50)        # less than
Article.objects.filter(views__lte=50)       # less than or equal

# IN lookup
Article.objects.filter(status__in=['published', 'archived'])
Article.objects.filter(author_id__in=[1, 5, 12])

# Range (inclusive, translates to BETWEEN)
from datetime import date
Article.objects.filter(published_at__range=(date(2026, 1, 1), date(2026, 12, 31)))

# NULL checks
Article.objects.filter(published_at__isnull=True)   # WHERE published_at IS NULL
Article.objects.filter(published_at__isnull=False)  # WHERE published_at IS NOT NULL

# Date-specific lookups
Article.objects.filter(published_at__year=2026)
Article.objects.filter(published_at__month=2)
Article.objects.filter(published_at__day=12)
Article.objects.filter(published_at__week_day=2)  # Monday=2 in Django
Article.objects.filter(created_at__date=date.today())

# Regex lookups
Article.objects.filter(title__regex=r'^(Django|Flask)\s')
Article.objects.filter(title__iregex=r'^(django|flask)\s')

# Related field lookups (span relationships with __)
Article.objects.filter(author__name='Alice')
Article.objects.filter(author__email__contains='@example.com')
Article.objects.filter(categories__name='Python')

6. Aggregation and Annotation

Aggregation computes summary values across an entire QuerySet (like SQL aggregate functions). Annotation adds computed values to each object in a QuerySet (like SQL window functions or subqueries). Together they let you perform complex calculations entirely at the database level.

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

# aggregate() - compute a single summary value for the entire QuerySet
stats = Article.objects.aggregate(
    total_views=Sum('views'),
    avg_views=Avg('views'),
    max_views=Max('views'),
    min_views=Min('views'),
    article_count=Count('id'),
)
# {'total_views': 15430, 'avg_views': 154.3, 'max_views': 2100, ...}

# annotate() - add computed values to each object
authors = Author.objects.annotate(
    article_count=Count('articles'),
    total_views=Sum('articles__views'),
    avg_views=Avg('articles__views'),
)
for author in authors:
    print(f"{author.name}: {author.article_count} articles, {author.total_views} views")

# Annotate + filter: find prolific authors
prolific = Author.objects.annotate(
    article_count=Count('articles')
).filter(article_count__gte=5)

# Annotate + order: authors ranked by total views
ranked = Author.objects.annotate(
    total_views=Sum('articles__views')
).order_by('-total_views')

# Count with conditions (Django 2.0+)
from django.db.models import Q

Author.objects.annotate(
    published_count=Count('articles', filter=Q(articles__status='published')),
    draft_count=Count('articles', filter=Q(articles__status='draft')),
)

# Group by with values() + annotate()
# Articles per status (like SQL GROUP BY)
Article.objects.values('status').annotate(
    count=Count('id'),
    avg_views=Avg('views'),
).order_by('status')
# [{'status': 'draft', 'count': 12, 'avg_views': 0.0},
#  {'status': 'published', 'count': 85, 'avg_views': 181.5}, ...]

# Monthly article counts
Article.objects.filter(status='published').values(
    month=TruncMonth('published_at')
).annotate(count=Count('id')).order_by('month')

7. Q Objects for Complex Queries

Standard filter() keyword arguments are joined with AND. When you need OR logic, negation, or dynamic query construction, use Q objects.

from django.db.models import Q

# OR: articles that are published OR have 500+ views
Article.objects.filter(Q(status='published') | Q(views__gt=500))

# AND (explicit): Q(status='published') & Q(views__gt=100)
# NOT: ~Q(status='draft')

# Complex: published by Alice OR any article with 1000+ views
Article.objects.filter(
    (Q(status='published') & Q(author__name='Alice')) | Q(views__gte=1000)
)

# Mix Q with kwargs (Q must come first)
Article.objects.filter(Q(status='published') | Q(status='archived'), author=author)

# Dynamic query building
conditions = Q()
if status_filter:
    conditions &= Q(status=status_filter)
if author_filter:
    conditions &= Q(author__name__icontains=author_filter)
if min_views:
    conditions &= Q(views__gte=min_views)

results = Article.objects.filter(conditions)

# Search across multiple fields
search_term = 'django'
Article.objects.filter(
    Q(title__icontains=search_term) |
    Q(body__icontains=search_term) |
    Q(author__name__icontains=search_term)
)

8. F Expressions

F expressions let you reference the value of a model field in a query without loading it into Python. The operation happens entirely at the database level, which is both faster and avoids race conditions in concurrent environments.

from django.db.models import F

# Atomic update: increment views without loading the object
Article.objects.filter(pk=42).update(views=F('views') + 1)
# Generates: UPDATE article SET views = views + 1 WHERE id = 42

# Why F() matters - race condition comparison:
# BAD: article = Article.objects.get(pk=42); article.views += 1; article.save()
# GOOD: Article.objects.filter(pk=42).update(views=F('views') + 1)

# Use F() in filter - compare two fields
Article.objects.filter(updated_at__gt=F('published_at') + timedelta(days=7))

# Use F() in annotate
Article.objects.annotate(engagement=F('views') * 1.0 / (F('views') + 100))

# Use F() in order_by (with nulls handling)
Article.objects.order_by(F('published_at').desc(nulls_last=True))

# Bulk calculation: 10% discount on products over $50
Product.objects.filter(price__gt=50).update(price=F('price') * 0.9)

# Combine with database functions
from django.db.models.functions import Concat, Upper
from django.db.models import Value
Article.objects.annotate(label=Concat(Upper('status'), Value(': '), 'title'))

9. Related Object Queries

The Django ORM makes it easy to traverse relationships, but the default behavior can be dangerously inefficient. Understanding select_related and prefetch_related is the single most important performance skill for Django developers.

The N+1 Problem

# BAD: N+1 queries - 1 query for articles + N queries for authors
articles = Article.objects.filter(status='published')
for article in articles:
    print(article.author.name)  # each access triggers a separate query!

# With 100 articles, this executes 101 queries.

# GOOD: select_related - uses SQL JOIN, 1 query total
articles = Article.objects.select_related('author').filter(status='published')
for article in articles:
    print(article.author.name)  # no additional query, data already loaded

# select_related works with ForeignKey and OneToOneField
# You can chain multiple relationships:
Article.objects.select_related('author', 'editor', 'category')
Article.objects.select_related('author__profile')  # follow nested FK

prefetch_related for Many-to-Many and Reverse Relations

# prefetch_related uses separate queries, joins in Python
articles = Article.objects.prefetch_related('categories')
for article in articles:
    for cat in article.categories.all():  # no extra query
        print(cat.name)

# Custom prefetch with Prefetch objects for filtered/ordered results
from django.db.models import Prefetch
authors = Author.objects.prefetch_related(
    Prefetch('articles',
             queryset=Article.objects.filter(status='published').order_by('-views'),
             to_attr='published_articles')
)

# Combine both: select_related for FK, prefetch_related for M2M/reverse FK
Article.objects.select_related('author').prefetch_related('categories', 'comments')

10. Raw SQL and Custom Managers

The ORM handles most queries, but sometimes you need raw SQL for complex operations. Django provides multiple levels of raw SQL access, from model-aware queries to completely custom SQL.

Manager.raw()

# raw() returns model instances - your SQL must include the primary key
articles = Article.objects.raw('''
    SELECT a.*, COUNT(c.id) as comment_count
    FROM blog_article a
    LEFT JOIN blog_comment c ON c.article_id = a.id
    WHERE a.status = %s
    GROUP BY a.id
    ORDER BY comment_count DESC
''', ['published'])

for article in articles:
    print(f"{article.title}: {article.comment_count} comments")

# ALWAYS use parameterized queries to prevent SQL injection
# GOOD:
Article.objects.raw('SELECT * FROM blog_article WHERE id = %s', [user_input])
# BAD (SQL injection vulnerability!):
Article.objects.raw(f'SELECT * FROM blog_article WHERE id = {user_input}')

Direct Cursor Access

from django.db import connection
with connection.cursor() as cursor:
    cursor.execute('SELECT author_id, COUNT(*) as cnt FROM blog_article '
                   'WHERE status = %s GROUP BY author_id', ['published'])
    columns = [col[0] for col in cursor.description]
    results = [dict(zip(columns, row)) for row in cursor.fetchall()]

Custom Managers

class PublishedManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().filter(status='published')
    def popular(self, min_views=100):
        return self.get_queryset().filter(views__gte=min_views)

class Article(models.Model):
    objects = models.Manager()         # default manager
    published = PublishedManager()     # custom manager

Article.published.all()               # only published
Article.published.popular(500)        # published with 500+ views

11. Transactions and Atomic Operations

Transactions ensure that a group of database operations either all succeed or all fail together. Django provides the transaction.atomic() context manager and decorator to handle this cleanly.

from django.db import transaction

# As a context manager - all operations succeed or all fail
def transfer_credits(from_user, to_user, amount):
    with transaction.atomic():
        from_user.credits = F('credits') - amount
        from_user.save(update_fields=['credits'])
        to_user.credits = F('credits') + amount
        to_user.save(update_fields=['credits'])
        TransferLog.objects.create(from_user=from_user, to_user=to_user, amount=amount)

# As a decorator
@transaction.atomic
def create_user_with_profile(username, email, bio):
    user = User.objects.create(username=username, email=email)
    Profile.objects.create(user=user, bio=bio)
    return user

# select_for_update() - row-level locking to prevent race conditions
with transaction.atomic():
    account = Account.objects.select_for_update().get(pk=account_id)
    account.balance -= amount
    account.save()

# on_commit() - run code AFTER the transaction succeeds
with transaction.atomic():
    order = Order.objects.create(customer=customer, total=total)
    transaction.on_commit(lambda: send_order_confirmation.delay(order.id))

A critical rule: never call external services (emails, API calls, task queues) inside a transaction block. If the transaction rolls back, the external action cannot be undone. Use transaction.on_commit() to schedule actions after success.

12. Performance Tips

The difference between a fast Django application and a slow one usually comes down to how the ORM is used. Here are the optimizations that make the biggest impact.

Database Indexes

class Article(models.Model):
    title = models.CharField(max_length=300)
    slug = models.SlugField(max_length=300, unique=True)  # unique implies index
    status = models.CharField(max_length=10, db_index=True)  # single-field index
    author = models.ForeignKey(Author, on_delete=models.CASCADE)  # FK auto-indexed

    class Meta:
        indexes = [
            # Composite index for common query patterns
            models.Index(fields=['status', 'published_at']),
            # Partial index (PostgreSQL only)
            models.Index(
                fields=['published_at'],
                condition=Q(status='published'),
                name='published_articles_idx',
            ),
        ]

Query Optimization

# 1. Load only needed fields
articles = Article.objects.only('title', 'slug')  # defer everything else
articles = Article.objects.defer('body')           # load all except body

# 2. Use exists() instead of count() for boolean checks
if Article.objects.filter(status='published').exists():  # GOOD
    pass

# 3. Use bulk operations instead of loops
Article.objects.bulk_create([
    Article(title=t, author=author) for t in titles
])
Article.objects.filter(status='draft').update(status='archived')  # single UPDATE

# 4. Use iterator() for large QuerySets to save memory
for article in Article.objects.all().iterator(chunk_size=2000):
    process(article)

Debugging Queries

# See generated SQL
print(Article.objects.filter(status='published').query)

# Count queries in a code block
from django.db import connection, reset_queries
reset_queries()
# ... your code ...
print(f"Queries: {len(connection.queries)}")

# In development: pip install django-debug-toolbar

13. Common Patterns and Best Practices

Soft Delete Pattern

class SoftDeleteManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().filter(deleted_at__isnull=True)

class Article(models.Model):
    deleted_at = models.DateTimeField(null=True, blank=True)
    objects = SoftDeleteManager()   # default: excludes deleted
    all_objects = models.Manager()  # includes deleted

    def soft_delete(self):
        self.deleted_at = timezone.now()
        self.save(update_fields=['deleted_at'])

Abstract Base Classes for Reusable Fields

class TimestampMixin(models.Model):
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    class Meta:
        abstract = True  # won't create a table

class Article(TimestampMixin, models.Model):
    title = models.CharField(max_length=300)  # inherits created_at, updated_at

get_or_create and update_or_create

# Get an existing object or create a new one
author, created = Author.objects.get_or_create(
    email='alice@example.com',
    defaults={'name': 'Alice', 'bio': 'Python developer'},
)
# created is True if the object was just created, False if it existed

# Update if exists, create if not
author, created = Author.objects.update_or_create(
    email='alice@example.com',
    defaults={'name': 'Alice Updated', 'bio': 'Senior Python developer'},
)

Subqueries

from django.db.models import Subquery, OuterRef, Exists

# Find the latest article title for each author
latest = Article.objects.filter(author=OuterRef('pk')).order_by('-published_at')
Author.objects.annotate(latest_title=Subquery(latest.values('title')[:1]))

# Exists subquery: find authors who have published articles
published = Article.objects.filter(author=OuterRef('pk'), status='published')
Author.objects.annotate(has_published=Exists(published)).filter(has_published=True)

Best Practices Summary

• Always use select_related and prefetch_related when accessing related objects in loops. The N+1 problem is the most common Django performance issue.
• Add database indexes to fields you filter, order, or join on frequently. Check slow queries with EXPLAIN ANALYZE.
• Use F expressions for updates to avoid race conditions and reduce round trips between Python and the database.
• Prefer bulk operations (bulk_create, bulk_update, update) over saving individual objects in a loop.
• Use values() or only() when you do not need full model instances. Fetching fewer columns is faster and uses less memory.
• Wrap related writes in transactions with transaction.atomic() to ensure data consistency.
• Use custom managers to encapsulate common query patterns and keep your views and serializers clean.
• Profile with django-debug-toolbar in development. You cannot optimize what you cannot measure.

Frequently Asked Questions

Conclusion

The Django ORM is a comprehensive database abstraction layer that handles everything from simple CRUD operations to complex aggregations, subqueries, and transactions. The concepts covered in this guide — models, migrations, QuerySets, field lookups, aggregation, Q objects, F expressions, related object queries, raw SQL, transactions, and performance optimization — represent the complete toolkit for building data-driven Django applications.

Start with the fundamentals: define models with appropriate fields, use filter() and exclude() for querying, always use select_related() and prefetch_related() when accessing related objects, and wrap multi-step writes in transaction.atomic(). As your application grows, reach for annotate, Q objects, F expressions, custom managers, and Subquery. Profile with django-debug-toolbar, add indexes to frequently-queried fields, and use bulk operations wherever possible.

Related Resources

• SQL: The Complete Guide — understand the SQL that Django ORM generates under the hood
• SQL Joins: The Complete Guide — master the join concepts behind select_related and prefetch_related
• Python Pandas: The Complete Data Analysis Guide — analyze data exported from your Django application
• Python Virtual Environments Guide — isolate your Django project dependencies