Managing database queries in Django applications and where save the queries

As a Django app grows, managing database queries effectively is crucial for maintainability, performance, and testability. Storing them haphazardly is a recipe for disaster. The best place to store your queries is on custom Model Managers and QuerySets. This approach keeps your logic organized, reusable, and closely tied to the data it operates on. Here are the main variants for storing your queries, from the least recommended to the best practice.

1. Inline in Views (The Anti-Pattern 👎)

This is where many developers start, but it should be avoided in any serious application. • What it is: Placing complex filter(), exclude(), annotate(), and aggregate() calls directly inside your view functions or classes. • Why it's bad: ◦ Violates DRY (Don't Repeat Yourself): The same query logic is often copied across multiple views. ◦ Poor Testability: You have to test the query through the entire request-response cycle, making unit testing difficult. ◦ Fat Views, Thin Models: It bloats your views with business logic that doesn't belong there, making them hard to read and maintain.

# views.py (👎 DON'T DO THIS)

def low_stock_products_view(request):
    # Logic is stuck here, can't be reused
    products = Product.objects.filter(
        is_active=True,
        stock__lt=10,
        category__name__in=['Electronics', 'Appliances']
    ).select_related('category').order_by('-last_updated')
    return render(request, 'products.html', {'products': products})

2. On Model Methods

This is a step up, but it's best suited for queries related to a single instance of a model. • What it is: A method on your model class that performs a query related to that specific object (self). • Use case: Getting related objects for one specific instance. • Example:

# models.py

class Category(models.Model):
    name = models.CharField(max_length=100)

    def get_active_products(self):
        """Returns active products for this specific category."""
        return self.product_set.filter(is_active=True)

# Usage in a view

category = Category.objects.get(name='Electronics')
active_products = category.get_active_products() # Operates on one instance

3. Custom Managers and QuerySets (The Django Way ✅)

This is the most powerful and recommended approach for organizing reusable, table-level queries. The manager provides the entry point, and the QuerySet provides the chainable methods.

Custom QuerySet

A custom QuerySet lets you create chainable filter methods.

Custom Manager

A custom Manager is the primary interface for querying the model's table (Product.objects...). You can attach your custom QuerySet to it. • How it works: 1 Create a ProductQuerySet with your custom filtering methods. 2 Create a ProductManager that uses ProductQuerySet. 3 Tell your Product model to use ProductManager as its objects manager. • Example: Let's refactor the anti-pattern from the first point.

# models.py

from django.db import models

# 1. Define the custom QuerySet

class ProductQuerySet(models.QuerySet):
    def active(self):
        """Returns only active products."""
        return self.filter(is_active=True)

    def low_stock(self, threshold=10):
        """Returns products with stock below a certain threshold."""
        return self.filter(stock__lt=threshold)

    def in_categories(self, category_names):
        """Returns products within a list of category names."""
        return self.filter(category__name__in=category_names)

# 2. Define the Manager (no custom methods needed here, just wiring)

# The manager will automatically get the QuerySet methods

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

# 3. Attach it to the model

class Product(models.Model):
    name = models.CharField(max_length=200)
    stock = models.IntegerField()
    is_active = models.BooleanField(default=True)
    category = models.ForeignKey('Category', on_delete=models.CASCADE)
    last_updated = models.DateTimeField(auto_now=True)
    # ... other fields

    # Attach the manager
    objects = ProductManager()

# views.py (✅ CLEAN AND REUSABLE)

def low_stock_products_view(request):
    # Logic is now reusable, testable, and chainable!
    products = Product.objects.active().low_stock().in_categories(
        ['Electronics', 'Appliances']
    ).select_related('category').order_by('-last_updated')
    return render(request, 'products.html', {'products': products})

• Benefits: ◦ Extremely Reusable (DRY): Product.objects.active() can be used everywhere. ◦ Chainable: The logic is composable (.active().low_stock()). ◦ Testable: You can write unit tests directly against the QuerySet methods. ◦ Keeps Logic Organized: Business logic about your data lives with your data model.

4. Service Layer / Utility Functions

For extremely complex business operations that might involve multiple models, external API calls, and more, a service layer can be a good pattern. • What it is: A set of functions or classes in a separate module (e.g., yourapp/services.py) that orchestrate business logic. These services use your model managers. • Use case: A user signup process that creates a User, a Profile, sends a welcome email, and reports to a metrics service. • Example:

# products/services.py

from .models import Product, Order, OrderItem
from django.db import transaction

@transaction.atomic
def fulfill_order(order: Order):
    """
    A complex operation that uses multiple model managers.
    1. Lock products to prevent race conditions.
    2. Decrease stock.
    3. Mark order as fulfilled.
    """
    items = order.items.select_related('product')
    product_ids = [item.product.id for item in items]

    # Use a query to lock the products for update
    products_to_update = Product.objects.filter(id__in=product_ids).select_for_update()

    for item in items:
        product = next(p for p in products_to_update if p.id == item.product.id)
        if product.stock < item.quantity:
            raise ValueError(f"Not enough stock for {product.name}")
        product.stock -= item.quantity
    
    Product.objects.bulk_update(products_to_update, ['stock'])
    order.status = 'FULFILLED'
    order.save(update_fields=['status'])
    # ... maybe send an email here
    return order

This keeps complex, multi-step workflows out of your views and models, providing a clear, high-level API for your business processes.

Summary & Final Recommendation

Location	Best for	Maintainability
Inline in Views	Never. Quick prototypes only.	very poor
Model Methods	Logic for a single model instance.	good
Managers & QuerySets	Reusable, chainable queries on a model's table.	excellent
Service Layer	Complex business workflows involving multiple models or services.	excellent

My advice

1 Default to Custom Managers and QuerySets. This is the idiomatic Django way and will serve you well for 90% of cases. 2 Use Model Methods for convenience helpers on single instances. 3 Introduce a Service Layer only when you feel your business logic is outgrowing your models and views and needs its own dedicated home. 4 Stay away from complex queries in views. Your future self will thank you.