Django’s ORM can abstract away database operations, but when you’re dealing with multiple databases, it’s not always obvious how to direct your queries. The magic happens with the using() method, which lets you explicitly choose which database a query should run against.
Let’s see this in action. Imagine you have two databases configured in your settings.py: default (your primary) and replica (a read-only replica).
# settings.py
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'NAME': 'mydatabase_primary',
'USER': 'user_primary',
'PASSWORD': 'password_primary',
'HOST': 'db_primary.example.com',
'PORT': '5432',
},
'replica': {
'ENGINE': 'django.db.backends.postgresql',
'NAME': 'mydatabase_replica',
'USER': 'user_replica',
'PASSWORD': 'password_replica',
'HOST': 'db_replica.example.com',
'PORT': '5432',
}
}
Now, consider a simple Book model:
# models.py
from django.db import models
class Book(models.Model):
title = models.CharField(max_length=200)
author = models.CharField(max_length=100)
published_date = models.DateField()
def __str__(self):
return self.title
By default, queries on Book will hit your default database. To query the replica database, you append .using('replica') to your queryset.
from .models import Book
# Fetch all books from the primary database (default)
all_books_primary = Book.objects.all()
print(f"Books from primary: {[book.title for book in all_books_primary]}")
# Fetch all books from the replica database
all_books_replica = Book.objects.using('replica').all()
print(f"Books from replica: {[book.title for book in all_books_replica]}")
# Filter books from the replica database
recent_books_replica = Book.objects.using('replica').filter(published_date__year=2023)
print(f"Recent books from replica: {[book.title for book in recent_books_replica]}")
# Create a new book - this will go to the default database
new_book = Book.objects.create(title="The New Novel", author="A. Writer", published_date="2024-01-01")
print(f"Created book: {new_book.title} on database: {new_book._state.db}") # _state.db shows which DB it's associated with
# Update a book - this also targets the default database unless specified
book_to_update = Book.objects.get(title="The New Novel")
book_to_update.title = "The Even Newer Novel"
book_to_update.save() # This save will go to the default database
# Explicitly save an object to a different database
another_book = Book(title="Old Tales", author="Past Author", published_date="1999-05-10")
another_book.save(using='replica') # This save targets the replica database
print(f"Saved 'Old Tales' to database: {another_book._state.db}")
This using() method applies not just to retrieval but also to creation, updates, and deletions. When you save an object (.save()) or perform an action like .create(), it defaults to the default database. If you need to perform these operations on a different database, you must explicitly pass the using argument to the save method or use a manager bound to that database.
# Example of using a manager bound to a specific database
from django.db import connections
# Get the connection for the replica database
replica_db_connection = connections['replica']
# Create a queryset bound to the replica database
replica_books_manager = Book.objects.using('replica')
# Now, any operation on replica_books_manager will target the replica DB
new_book_on_replica = replica_books_manager.create(title="Replica Edition", author="DB Master", published_date="2024-02-15")
print(f"Created 'Replica Edition' on database: {new_book_on_replica._state.db}")
The _state.db attribute on a model instance is a useful internal detail that tells you which database that specific instance is associated with. This is set when the object is retrieved or when you explicitly save it to a database.
A crucial point often overlooked is how raw SQL queries interact with multiple databases. If you execute raw SQL using connection.cursor() or Manager.raw(), you must also specify the database connection.
from django.db import connection
# Raw query on the default database
with connection.cursor() as cursor:
cursor.execute("SELECT COUNT(*) FROM myapp_book WHERE author = %s", ["A. Writer"])
count = cursor.fetchone()[0]
print(f"Count of books by A. Writer (default DB): {count}")
# Raw query on the replica database
with connection.cursor() as cursor:
cursor.execute("SELECT COUNT(*) FROM myapp_book WHERE author = %s", ["A. Writer"])
count = cursor.fetchone()[0]
print(f"Count of books by A. Writer (replica DB): {count}")
# Using Manager.raw() on the replica database
replica_books_raw = Book.objects.using('replica').raw("SELECT * FROM myapp_book WHERE title LIKE %s", ["%Novel%"])
print(f"Raw query results from replica: {[book.title for book in replica_books_raw]}")
The using() method is the primary mechanism for routing queries, but Django also offers more sophisticated routing capabilities via the DATABASE_ROUTERS setting in settings.py. This allows you to define custom logic for which database should be used for a given app, model, or operation, based on conditions you define. For instance, you could set up a router that automatically directs all read operations for a specific app to a replica database and all write operations to the default.
When you use using(), you’re essentially telling Django, "For this specific query, use this database." It bypasses any routing rules you might have defined in DATABASE_ROUTERS for that particular query. This explicit control is powerful for scenarios where you need to deviate from your default routing, such as performing a bulk update on a replica for performance reasons, even if your router typically sends writes to the primary.
The _state.db attribute is derived from the alias argument passed to the model’s __init__ or, more commonly, from the database alias associated with the manager used to fetch or create the object. If an object is fetched using Book.objects.using('replica').get(...), its _state.db will be 'replica'. When you call .save() on such an object, it will attempt to save to that same database unless you override it with save(using='another_db'). This behavior ensures that an object remains associated with its original database unless explicitly told otherwise.
When dealing with transactions across multiple databases, Django’s transaction.atomic() context manager supports a using argument. This allows you to create atomic blocks for specific databases. However, performing a single atomic transaction that spans multiple databases simultaneously is not directly supported by Django’s ORM and would typically require a distributed transaction coordinator, which is outside the scope of standard Django database handling.
The most surprising thing about routing queries across multiple databases is that Django’s default behavior for fetching related objects (e.g., book.author.name) will follow the using() directive of the initial query. If you fetch book = Book.objects.using('replica').get(pk=1), then book.author will also be fetched from the replica database, assuming your Author model is set up to use the same database or you have a router that directs it there. This "follow-through" behavior can be a performance booster or a subtle trap if your related data isn’t consistent across databases.
The next step you’ll likely encounter is managing migrations when you have multiple databases, as Django needs to know which database each migration should be applied to.