Django Database Routers

Introduction

Most Django applications start with a single database, but as your project grows, you might need to work with multiple databases. For example, you might want to:

Store user data in one database and product data in another
Use read replicas to distribute database load
Archive old data to a separate database
Use specific databases for particular applications within your project

Django's database routers provide a clean, Pythonic way to control database operations when working with multiple databases. They decide which database should handle a given operation based on the models involved and the type of operation (read, write, migration, etc.).

In this tutorial, we'll explore how database routers work, how to configure them, and how to implement custom routing logic for your Django applications.

Prerequisites

Before diving in, you should have:

Basic knowledge of Django models and the ORM
Understanding of database concepts
A Django project set up and running

Configuring Multiple Databases

Before we can use database routers, we need to configure multiple databases in our Django settings. Here's how:

python
# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
    },
    'users_db': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'users_database',
        'USER': 'postgres_user',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': '5432',
    },
    'products_db': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'products_database',
        'USER': 'mysql_user',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': '3306',
    }
}

In this example, we've defined three databases:

default - a SQLite database that Django will use by default
users_db - a PostgreSQL database for user-related data
products_db - a MySQL database for product-related data

Understanding Database Routers

A database router is a Python class that provides methods to control database operations. Django calls these methods when it needs to determine which database to use.

The key methods of a database router are:

db_for_read(model, **hints) - Suggest the database for read operations
db_for_write(model, **hints) - Suggest the database for write operations
allow_relation(obj1, obj2, **hints) - Allow or prevent relations between objects
allow_migrate(db, app_label, model_name=None, **hints) - Allow or prevent migrations

Let's create a simple router to direct user-related models to the users_db and product-related models to the products_db.

Creating a Custom Router

Create a new file called routers.py in your project directory:

python
# myproject/routers.py

class AppRouter:
    """
    A router to control database operations based on the app a model belongs to.
    """
    
    def db_for_read(self, model, **hints):
        """
        Point all read operations on User models to 'users_db'
        and Product models to 'products_db'.
        """
        if model._meta.app_label == 'auth' or model._meta.app_label == 'accounts':
            return 'users_db'
        elif model._meta.app_label == 'products':
            return 'products_db'
        return 'default'
    
    def db_for_write(self, model, **hints):
        """
        Point all write operations on User models to 'users_db'
        and Product models to 'products_db'.
        """
        if model._meta.app_label == 'auth' or model._meta.app_label == 'accounts':
            return 'users_db'
        elif model._meta.app_label == 'products':
            return 'products_db'
        return 'default'
    
    def allow_relation(self, obj1, obj2, **hints):
        """
        Allow relations if both objects are in the same database or
        both are in databases that we want to relate.
        """
        # Allow relations within the same database
        if obj1._meta.app_label == obj2._meta.app_label:
            return True
            
        # Allow relations between auth and accounts as they share users_db
        if (obj1._meta.app_label in ['auth', 'accounts'] and 
            obj2._meta.app_label in ['auth', 'accounts']):
            return True
            
        return None
    
    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """
        Ensure that the auth and accounts apps' models get created on the
        users_db, products app models on products_db, and everything else
        on the default database.
        """
        if app_label in ['auth', 'accounts']:
            return db == 'users_db'
        elif app_label == 'products':
            return db == 'products_db'
        return db == 'default'

Registering Your Router

Now that we've defined our router, we need to tell Django to use it. Add the following to your settings.py:

python
# settings.py
DATABASE_ROUTERS = ['myproject.routers.AppRouter']

Django will check each router in order, stopping at the first one that returns a non-None value for a given method.

Using Routers with Models

Let's define some example models:

python
# accounts/models.py
from django.db import models

class Profile(models.Model):
    user = models.OneToOneField('auth.User', on_delete=models.CASCADE)
    bio = models.TextField(blank=True)
    
    def __str__(self):
        return f"Profile for {self.user.username}"

python
# products/models.py
from django.db import models

class Category(models.Model):
    name = models.CharField(max_length=100)
    
    def __str__(self):
        return self.name

class Product(models.Model):
    name = models.CharField(max_length=100)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    category = models.ForeignKey(Category, on_delete=models.CASCADE)
    
    def __str__(self):
        return self.name

With our router configured, Django will automatically:

Store and retrieve Profile models in the users_db database
Store and retrieve Product and Category models in the products_db database
Store and retrieve all other models in the default database

Working with Multiple Databases in Code

When you're using multiple databases, sometimes you need to explicitly specify which database to use. Here are some examples:

Querying a Specific Database

python
# Get products from the products database
products = Product.objects.using('products_db').all()

# Get user profiles from the users database
profiles = Profile.objects.using('users_db').all()

Creating Objects in a Specific Database

python
# Create a product in the products database
product = Product(name="Django T-shirt", price=19.99, category_id=1)
product.save(using='products_db')

Running Migrations on Specific Databases

bash
python manage.py migrate --database=users_db
python manage.py migrate --database=products_db

Real-World Example: Analytics Database

Let's consider a real-world example where we want to keep analytics data separate from our application data:

python
# routers.py
class AnalyticsRouter:
    """
    A router to send analytics models to a separate database.
    """
    analytics_apps = {'analytics', 'statistics'}
    
    def db_for_read(self, model, **hints):
        if model._meta.app_label in self.analytics_apps:
            return 'analytics_db'
        return None
    
    def db_for_write(self, model, **hints):
        if model._meta.app_label in self.analytics_apps:
            return 'analytics_db'
        return None
    
    def allow_relation(self, obj1, obj2, **hints):
        if (obj1._meta.app_label in self.analytics_apps and 
            obj2._meta.app_label in self.analytics_apps):
            return True
        return None
    
    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if app_label in self.analytics_apps:
            return db == 'analytics_db'
        elif db == 'analytics_db':
            return False
        return None

python
# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'main_database',
        # ... other settings
    },
    'analytics_db': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'analytics_database',
        # ... other settings
    }
}

DATABASE_ROUTERS = ['myproject.routers.AnalyticsRouter']

With this setup, all analytics data will be stored in a separate database, keeping your main application database clean and focused.

Example: Read Replicas for Scalability

Another common use case is using read replicas to distribute database load:

python
# routers.py
class PrimaryReplicaRouter:
    def db_for_read(self, model, **hints):
        """
        Randomly select a read replica for read operations.
        """
        import random
        replicas = ['replica1', 'replica2', 'replica3']
        return random.choice(replicas)
    
    def db_for_write(self, model, **hints):
        """
        Always send writes to the primary database.
        """
        return 'primary'
    
    def allow_relation(self, obj1, obj2, **hints):
        """
        Allow all relations between objects.
        """
        return True
    
    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """
        Only allow migrations on the primary database.
        """
        return db == 'primary'

python
# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'default_db',  # This won't be used much with our router
    },
    'primary': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'primary_db',
        # ... other settings
    },
    'replica1': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'primary_db',
        'HOST': 'replica1.example.com',
        # ... other settings
    },
    'replica2': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'primary_db',
        'HOST': 'replica2.example.com',
        # ... other settings
    },
    'replica3': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'primary_db',
        'HOST': 'replica3.example.com',
        # ... other settings
    }
}

DATABASE_ROUTERS = ['myproject.routers.PrimaryReplicaRouter']

This setup distributes read queries across three read replicas while sending all write queries to the primary database.

Best Practices and Considerations

When working with multiple databases and routers, keep these tips in mind:

Keep Router Logic Simple: Complex router logic can be hard to debug and maintain.
Consider Transaction Integrity: Operations within a transaction should generally use the same database.
Be Aware of Cross-Database Relations: Django doesn't support foreign keys across databases, so plan your data model carefully.
Test Thoroughly: Ensure your routing logic works correctly under all scenarios.
Document Your Routing Logic: Make sure other developers understand how data is distributed across databases.
Monitor Performance: Watch for any unexpected behavior or performance issues related to database routing.

Summary

Django database routers provide a powerful mechanism for controlling database operations in multi-database environments. With routers, you can:

Direct different models to different databases
Separate reads and writes for performance optimization
Implement complex data storage strategies
Scale your application horizontally by distributing database operations

In this tutorial, we've learned how to configure multiple databases in Django, create custom database routers, and implement common routing patterns. These techniques can help you build more scalable and maintainable Django applications.

Additional Resources

Exercises

Create a router that sends all read-only models to a read-only database and writable models to a read-write database.
Implement a router that stores historical data (older than one year) in an archive database.
Create a router that uses different databases based on the current tenant in a multi-tenant application.
Modify the example analytics router to include a method that logs all database operations.
Implement a router that sends high-priority operations to a fast database and background operations to a separate database.

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Prerequisites​

Configuring Multiple Databases​

Understanding Database Routers​

Creating a Custom Router​

Registering Your Router​

Using Routers with Models​

Working with Multiple Databases in Code​

Querying a Specific Database​

Creating Objects in a Specific Database​

Running Migrations on Specific Databases​

Real-World Example: Analytics Database​

Example: Read Replicas for Scalability​

Best Practices and Considerations​

Summary​

Additional Resources​

Exercises​