Multiple Databases
Using multiple databases in Django can bring a number of benefits to your application, including improved performance and the ability to separate data into distinct logical parts. In this article, we will explore how you can use multiple databases in Django and show an example of how to set up a separate geospatial database and another database for users.
To get started with using multiple databases in Django, you’ll need to make a few changes to your settings.py file. First, you’ll need to define the databases you want to use in the DATABASES setting. The default database is defined as follows:
DATABASES = {
'default': {},
'geospatial': {
'ENGINE': 'django.contrib.gis.db.backends.postgis',
'NAME': 'geospatial_db',
'USER': 'geospatial_user',
'PASSWORD': 'secret_password',
'HOST': 'localhost',
'PORT': '5432',
},
'users': {
'ENGINE': 'django.db.backends.postgresql',
'NAME': 'users_db',
'USER': 'users_user',
'PASSWORD': 'secret_password',
'HOST': 'localhost',
'PORT': '5432',
}
}
Django requires that the ‘ default’ database is defined. Which is why we have defined it, but with an empty dictionary since the default (let’s say) doesn’t make sense in our case. It’s also better to not have a default database when you want to be more explicit with your migrations.
Let’s say you have a legacy database which you only want to read from, and never want to add tables to it. If you don’t have a default database, the ‘migrate’ command will always ask for the database parameter, which saves you from running the migrations on the wrong database.
Creating our Models:
class GeoSpatialModel(models.Model):
location = models.PointField()
class Meta:
managed = False
db_table = 'geospatial_model'
using = 'geospatial'
class UserProfile(models.Model):
user = models.OneToOneField(User, on_delete=models.CASCADE)
bio = models.TextField()
class Meta:
db_table = 'user_profile'
using = 'users'
The ‘using’ property in Meta class lets specify which database should this model use. This value is not necessary if you have your DATABSE_ROUTERS configured. We will do that in the next section. Before that, let’s see how to read data from our databases:
When it comes to querying data from multiple databases, Django provides a few different ways to do this. The simplest way is to use the using option on a queryset:
# Query the geospatial database
geo_spatial_data = GeoSpatialModel.objects.using('geospatial').all()
# Query the users database
user_data = UserProfile.objects.using('users').all
The approach above works, but it is not very practical. Its better to configure our database routers so we don’t have to specify a database every time we read/write data.
Database Routers:
To route database operations for different models to different databases, we need to create a custom database router. Django provides a way to create custom routers that control how the operations for different models are routed to different databases.
Here’s an example of how you can create a custom database router:
class GeoSpatialRouter(object):
"""
A router to control all database operations on GeoSpatialModel
"""
def db_for_read(self, model, **hints):
if model._meta.app_label == 'your_app_name' and model._meta.model_name == 'GeoSpatialModel':
return 'geospatial'
return None
def db_for_write(self, model, **hints):
if model._meta.app_label == 'your_app_name' and model._meta.model_name == 'GeoSpatialModel':
return 'geospatial'
return None
def allow_relation(self, obj1, obj2, **hints):
if obj1._meta.app_label == 'your_app_name' and obj1._meta.model_name == 'GeoSpatialModel' \
or obj2._meta.app_label == 'your_app_name' and obj2._meta.model_name == 'GeoSpatialModel':
return True
return None
def allow_migrate(self, db, app_label, model_name=None, **hints):
if app_label == 'your_app_name' and model_name == 'GeoSpatialModel':
return db == 'geospatial'
return None
class UserRouter(object):
"""
A router to control all database operations on UserProfile
"""
def db_for_read(self, model, **hints):
if model._meta.app_label == 'your_app_name' and model._meta.model_name == 'UserProfile':
return 'users'
return None
def db_for_write(self, model, **hints):
if model._meta.app_label == 'your_app_name' and model._meta.model_name == 'UserProfile':
return 'users'
return None
def allow_relation(self, obj1, obj2, **hints):
if obj1._meta.app_label == 'your_app_name' and obj1._meta.model_name == 'UserProfile' \
or obj2._meta.app_label == 'your_app_name' and obj2._meta.model_name == 'UserProfile':
return True
return None
def allow_migrate(self, db, app_label, model_name=None, **hints):
if app_label == 'your_app_name' and model_name == 'UserProfile':
return db == 'users'
return None
Obviously, if you have multiple models for each database, you don’t need to provide the ‘and’ condition in each case above. We will then just route using the app_name and not the model_name.
Finally, you need to specify the custom routers in the DATABASE_ROUTERS setting in your settings.py file:
DATABASE_ROUTERS = [
'path.to.GeoSpatialRouter',
'path.to.UserRouter',
]
Changes in models and Migrations:
From django docs : The migrate management command operates on one database at a time. By default, it operates on the default database, but by providing the --database option, you can tell it to synchronize a different database.
Since we don’t have a default database defined, we will always need the –database option to specify which database to use. An example of the migrate command in our case would then be:
python manage.py migrate --database=geospatial_db
python manage.py migrate --database=users_db
