Django Orm Patterns

Django ORM Patterns

Master Django ORM for building efficient, scalable database-driven applications with complex queries and relationships.

Model Definition

Define models with proper field types, constraints, and metadata.

from django.db import models
from django.core.validators import MinValueValidator, MaxValueValidator

class User(models.Model):
    email = models.EmailField(unique=True, db_index=True)
    name = models.CharField(max_length=100)
    is_active = models.BooleanField(default=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        ordering = ['-created_at']
        indexes = [
            models.Index(fields=['email']),
            models.Index(fields=['created_at', 'is_active']),
        ]
        verbose_name = 'User'
        verbose_name_plural = 'Users'

    def __str__(self):
        return self.email

class Post(models.Model):
    title = models.CharField(max_length=200)
    content = models.TextField()
    author = models.ForeignKey(User, on_delete=models.CASCADE, related_name='posts')
    published = models.BooleanField(default=False)
    views = models.PositiveIntegerField(default=0)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        ordering = ['-created_at']
        indexes = [
            models.Index(fields=['author', 'published']),
        ]

QuerySet API Basics

Use Django's QuerySet API for efficient database queries.

# All records
users = User.objects.all()

# Filtering
active_users = User.objects.filter(is_active=True)
inactive_users = User.objects.exclude(is_active=True)

# Get single record (raises exception if not found or multiple found)
user = User.objects.get(email='user@example.com')

# Get or create
user, created = User.objects.get_or_create(
    email='user@example.com',
    defaults={'name': 'John Doe'}
)

# Update or create
user, created = User.objects.update_or_create(
    email='user@example.com',
    defaults={'name': 'Jane Doe', 'is_active': True}
)

# Chaining filters
posts = Post.objects.filter(published=True).filter(author__is_active=True)

# Order by
users = User.objects.order_by('-created_at', 'name')

# Limit results
recent_users = User.objects.all()[:10]

# Count
user_count = User.objects.filter(is_active=True).count()

# Exists
has_active_users = User.objects.filter(is_active=True).exists()

Q Objects for Complex Queries

Build complex queries with Q objects for OR and NOT operations.

from django.db.models import Q

# OR queries
users = User.objects.filter(
    Q(name__icontains='john') | Q(email__icontains='john')
)

# AND with OR
users = User.objects.filter(
    Q(is_active=True) & (Q(name__icontains='john') | Q(email__icontains='john'))
)

# NOT queries
users = User.objects.filter(~Q(is_active=True))

# Complex combinations
posts = Post.objects.filter(
    Q(published=True) &
    (Q(author__name__icontains='john') | Q(title__icontains='important')) &
    ~Q(views__lt=100)
)

# Dynamic query building
def search_users(name=None, email=None, is_active=None):
    query = Q()
    if name:
        query &= Q(name__icontains=name)
    if email:
        query &= Q(email__icontains=email)
    if is_active is not None:
        query &= Q(is_active=is_active)
    return User.objects.filter(query)

F Objects for Field References

Use F objects to reference model fields in queries and updates.

from django.db.models import F

# Compare fields
posts = Post.objects.filter(views__gt=F('author__posts__count'))

# Update based on current value
Post.objects.filter(published=True).update(views=F('views') + 1)

# Avoid race conditions
post = Post.objects.get(id=1)
post.views = F('views') + 1
post.save()
post.refresh_from_db()  # Get updated value

# Complex expressions
from django.db.models import ExpressionWrapper, IntegerField

Post.objects.annotate(
    adjusted_views=ExpressionWrapper(
        F('views') * 2 + 10,
        output_field=IntegerField()
    )
)

Aggregation and Annotation

Perform database-level calculations and add computed fields.

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

# Simple aggregation
from django.db.models import Avg
avg_views = Post.objects.aggregate(Avg('views'))
# Returns: {'views__avg': 42.5}

# Multiple aggregations
stats = Post.objects.aggregate(
    total_posts=Count('id'),
    avg_views=Avg('views'),
    max_views=Max('views'),
    min_views=Min('views')
)

# Annotation (adds field to each object)
users = User.objects.annotate(
    post_count=Count('posts'),
    total_views=Sum('posts__views')
)

for user in users:
    print(f"{user.name}: {user.post_count} posts, {user.total_views} views")

# Filter by annotation
popular_users = User.objects.annotate(
    post_count=Count('posts')
).filter(post_count__gt=10)

# Complex annotations
from django.db.models import Case, When, Value, CharField

User.objects.annotate(
    user_type=Case(
        When(post_count__gt=10, then=Value('prolific')),
        When(post_count__gt=5, then=Value('active')),
        default=Value('casual'),
        output_field=CharField()
    )
)

Prefetch and Select Related (N+1 Prevention)

Optimize queries by reducing database hits with eager loading.

# Select related (for ForeignKey and OneToOne)
posts = Post.objects.select_related('author').all()
for post in posts:
    print(post.author.name)  # No additional query

# Prefetch related (for ManyToMany and reverse ForeignKey)
from django.db.models import Prefetch

users = User.objects.prefetch_related('posts').all()
for user in users:
    for post in user.posts.all():  # No additional query
        print(post.title)

# Custom prefetch
users = User.objects.prefetch_related(
    Prefetch(
        'posts',
        queryset=Post.objects.filter(published=True).order_by('-created_at')
    )
)

# Multiple levels
posts = Post.objects.select_related(
    'author'
).prefetch_related(
    'author__posts'  # Prefetch all posts by the same author
)

# Combining both
Post.objects.select_related('author').prefetch_related('tags')

Custom Managers and QuerySets

Create reusable query logic with custom managers and querysets.

from django.db import models

class PublishedQuerySet(models.QuerySet):
    def published(self):
        return self.filter(published=True)

    def recent(self):
        return self.order_by('-created_at')[:10]

    def by_author(self, author):
        return self.filter(author=author)

class PublishedManager(models.Manager):
    def get_queryset(self):
        return PublishedQuerySet(self.model, using=self._db)

    def published(self):
        return self.get_queryset().published()

    def recent(self):
        return self.get_queryset().recent()

class Post(models.Model):
    # fields...
    objects = models.Manager()  # Default manager
    published_posts = PublishedManager()  # Custom manager

    class Meta:
        base_manager_name = 'objects'

# Usage
Post.published_posts.published().recent()
Post.published_posts.published().by_author(user)

# Chaining custom methods
class UserQuerySet(models.QuerySet):
    def active(self):
        return self.filter(is_active=True)

    def with_posts(self):
        return self.annotate(post_count=Count('posts')).filter(post_count__gt=0)

User.objects.active().with_posts()

Transactions and Atomic Blocks

Ensure data consistency with database transactions.

from django.db import transaction

# Atomic decorator
@transaction.atomic
def create_user_with_post(email, name, post_title):
    user = User.objects.create(email=email, name=name)
    Post.objects.create(title=post_title, author=user)
    return user

# Context manager
def update_user_posts(user_id):
    try:
        with transaction.atomic():
            user = User.objects.select_for_update().get(id=user_id)
            user.posts.update(published=True)
            user.is_active = True
            user.save()
    except Exception as e:
        # Transaction is rolled back
        raise

# Savepoints
from django.db import transaction

with transaction.atomic():
    user = User.objects.create(email='user@example.com')

    sid = transaction.savepoint()
    try:
        Post.objects.create(title='Test', author=user)
    except:
        transaction.savepoint_rollback(sid)
    else:
        transaction.savepoint_commit(sid)

# Select for update (locking)
with transaction.atomic():
    user = User.objects.select_for_update().get(id=1)
    user.is_active = False
    user.save()

Advanced Select and Prefetch Patterns

Master complex query optimization with advanced eager loading techniques.

from django.db.models import Prefetch, Count, Q

# Basic select_related (ForeignKey, OneToOne)
posts = Post.objects.select_related('author', 'category')

# Multi-level select_related
comments = Comment.objects.select_related('post__author__profile')

# Prefetch with custom queryset
users = User.objects.prefetch_related(
    Prefetch(
        'posts',
        queryset=Post.objects.filter(published=True).select_related('category'),
        to_attr='published_posts'
    )
)

# Multiple prefetch with different filters
authors = User.objects.prefetch_related(
    Prefetch(
        'posts',
        queryset=Post.objects.filter(published=True),
        to_attr='published_posts'
    ),
    Prefetch(
        'posts',
        queryset=Post.objects.filter(published=False),
        to_attr='draft_posts'
    )
)

# Nested prefetch
posts = Post.objects.prefetch_related(
    Prefetch(
        'comments',
        queryset=Comment.objects.select_related('author').prefetch_related(
            Prefetch(
                'replies',
                queryset=Comment.objects.select_related('author')
            )
        )
    )
)

# Prefetch with annotations
users = User.objects.prefetch_related(
    Prefetch(
        'posts',
        queryset=Post.objects.annotate(
            comment_count=Count('comments')
        ).filter(comment_count__gt=0)
    )
)

Database Functions and Expressions

Leverage database functions for complex operations.

from django.db.models import F, Value, CharField, Case, When, Q
from django.db.models.functions import Concat, Lower, Upper, Length, Substr, Coalesce

# String operations
users = User.objects.annotate(
    full_name=Concat('first_name', Value(' '), 'last_name')
)

users = User.objects.annotate(
    email_lower=Lower('email'),
    name_upper=Upper('name')
)

# String functions
posts = Post.objects.annotate(
    title_length=Length('title')
).filter(title_length__gt=50)

# Substring
posts = Post.objects.annotate(
    title_preview=Substr('title', 1, 50)
)

# Coalesce (return first non-null value)
posts = Post.objects.annotate(
    display_name=Coalesce('custom_title', 'title', Value('Untitled'))
)

# Date functions
from django.db.models.functions import TruncDate, TruncMonth, ExtractYear, Now

posts = Post.objects.annotate(
    created_date=TruncDate('created_at'),
    created_month=TruncMonth('created_at'),
    created_year=ExtractYear('created_at')
)

# Date arithmetic
from datetime import timedelta
from django.utils import timezone

recent_posts = Post.objects.filter(
    created_at__gte=timezone.now() - timedelta(days=7)
)

# Mathematical functions
from django.db.models.functions import Abs, Ceil, Floor, Round

products = Product.objects.annotate(
    price_rounded=Round('price'),
    discount_abs=Abs('discount')
)

# Conditional expressions
User.objects.annotate(
    user_type=Case(
        When(posts__count__gt=100, then=Value('power_user')),
        When(posts__count__gt=10, then=Value('active')),
        When(posts__count__gt=0, then=Value('casual')),
        default=Value('lurker'),
        output_field=CharField()
    )
)

# Complex conditional updates
Post.objects.update(
    status=Case(
        When(Q(published=True) & Q(views__gt=1000), then=Value('viral')),
        When(Q(published=True) & Q(views__gt=100), then=Value('popular')),
        When(published=True, then=Value('published')),
        default=Value('draft'),
        output_field=CharField()
    )
)

Advanced Aggregation Patterns

Perform complex database-level calculations.

from django.db.models import (
    Count, Sum, Avg, Max, Min, StdDev, Variance,
    Q, F, Value, CharField, When, Case
)
from django.db.models.functions import Coalesce

# Multiple aggregations with filters
stats = Post.objects.aggregate(
    total_posts=Count('id'),
    published_posts=Count('id', filter=Q(published=True)),
    draft_posts=Count('id', filter=Q(published=False)),
    avg_views=Avg('views'),
    max_views=Max('views'),
    total_views=Sum('views'),
    std_dev_views=StdDev('views')
)

# Conditional aggregation
User.objects.aggregate(
    active_users=Count('id', filter=Q(is_active=True)),
    inactive_users=Count('id', filter=Q(is_active=False)),
    avg_posts_active=Avg('posts__count', filter=Q(is_active=True))
)

# Annotation with conditional aggregation
users = User.objects.annotate(
    published_post_count=Count('posts', filter=Q(posts__published=True)),
    draft_post_count=Count('posts', filter=Q(posts__published=False)),
    total_views=Sum('posts__views'),
    avg_post_views=Avg('posts__views')
).filter(published_post_count__gt=0)

# Group by with annotation
from django.db.models.functions import TruncDate

daily_stats = Post.objects.annotate(
    date=TruncDate('created_at')
).values('date').annotate(
    post_count=Count('id'),
    total_views=Sum('views'),
    avg_views=Avg('views')
).order_by('-date')

# Subquery aggregation
from django.db.models import OuterRef, Subquery

# Get latest comment for each post
latest_comment = Comment.objects.filter(
    post=OuterRef('pk')
).order_by('-created_at')

posts = Post.objects.annotate(
    latest_comment_date=Subquery(latest_comment.values('created_at')[:1]),
    latest_comment_author=Subquery(latest_comment.values('author__name')[:1])
)

# Complex nested aggregation
User.objects.annotate(
    total_post_views=Sum('posts__views'),
    total_comment_count=Count('posts__comments'),
    avg_comments_per_post=Case(
        When(posts__count=0, then=Value(0)),
        default=Count('posts__comments') / Count('posts', distinct=True)
    )
)

Database Indexes and Optimization

Optimize query performance with proper indexing.

class Post(models.Model):
    title = models.CharField(max_length=200, db_index=True)
    author = models.ForeignKey(User, on_delete=models.CASCADE)
    published = models.BooleanField(default=False)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        indexes = [
            # Single field
            models.Index(fields=['created_at']),

            # Composite index
            models.Index(fields=['author', 'published']),

            # Descending index
            models.Index(fields=['-created_at']),

            # Named index
            models.Index(fields=['title'], name='post_title_idx'),

            # Partial index (PostgreSQL)
            models.Index(
                fields=['author'],
                name='published_posts_idx',
                condition=models.Q(published=True)
            ),

            # Expression index (PostgreSQL)
            models.Index(
                Lower('title'),
                name='post_title_lower_idx'
            ),

            # Multi-column with includes (PostgreSQL)
            models.Index(
                fields=['author'],
                name='author_includes_idx',
                include=['title', 'created_at']
            ),
        ]

        # Unique together
        unique_together = [['author', 'title']]

        # Constraints (Django 2.2+)
        constraints = [
            models.UniqueConstraint(
                fields=['author', 'slug'],
                name='unique_author_slug'
            ),
            models.CheckConstraint(

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [SufficientDaikon](https://github.com/SufficientDaikon)
- **Source:** [SufficientDaikon/archon](https://github.com/SufficientDaikon/archon)
- **License:** MIT
- **Homepage:** https://sufficientdaikon.github.io/archon/

Install and usage instructions live in the source repository linked above.