How to use Django with multiple databases

There may come a time in the life of your Django project where you need to use multiple databases. Django provides two ways of doing this and in this article, I will show you how.

Let Django know about your Database

For Django to know about your database(s), you must add them to the database dictionary in your project’s settings.py file. This looks like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
    'customers_db': {
        'NAME': 'customers',
        'ENGINE': 'django.db.backends.postgresql',
        'USER': 'postgres_user',
        'PASSWORD': 'root'
    }
}

Adding the database to settings.py is the first step. Django will by default associate any models and queries you create with the default database. In the above configuration, the default database is the sqlite database labelled as such. To associate queries or models with a different database, you can either:

Explicitly state which database to use when making a query or saving to the database or
Set up a database routing scheme that will automatically select the right database for you.

Specifying the database to use when making queries.

For the purposes of this post, let’s assume these are your models:

# models.py

class Customer(models.Model):
    name = models.CharField(max_length=20)
    customer_id = models.CharField(max_length=120)


class User(models.Model):
    name = models.CharField(max_length=20)
    user_id = models.CharField(max_length=120)
    age = models.IntegerField()

To manually select the database you want in your query use the using() method. using() takes a database alias as its argument. Here is an example:

 
>>> default_user = User(name='Sipho', user_id='12:3456', age=57)
>>> default_user.save()  # user object saved to default database
>>> customer = Customer(name='Thandiwe Ndiweni', customer_id='86:19216857') 

# Saving to different database

>>> customer.save(using='customers_db')
>>> Customer.objects.all().using('customers_db')

Here, you see that customer is created and retrieved from the customers database by specifying the database name as an argument to using.

If you to write a lot of similar queries, it is a good idea to write a database router to automatically handle database selections. A database router is like a urlconfig but for databases. Here is an example of a router you could write to handle database operations for the customers database:

class YourRouter:
    """
    A router to control all database operations on models in the
    customer application.
    """
    def db_for_read(self, model, **hints):
        """
        Attempts to read user models go to customers_db.
        """
        if model._meta.app_label == 'user_data':
            return 'customers_db'
        return None

    def db_for_write(self, model, **hints):
        """
        Attempts to write user models go to users_db.
        """
        if model._meta.app_label == 'customer_data':
            return 'customers_db'
        return None

    def allow_relation(self, obj1, obj2, **hints):
        """
        Allow relations if a model in the user app is involved.
        """
        if obj1._meta.app_label == 'customer_data' or \
           obj2._meta.app_label == 'customer_data':
           return True
        return None

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """
        Make sure the auth app only appears in the 'customers_db'
        database.
        """
        if app_label == 'customer_data':
            return db == 'customers_db'
        return None

Place the code above in a module in an appropriate location in your app and then point Django to it by adding this line to settings.py :

DATABASE_ROUTERS = ['path.to.YourRouter']

substituting path.to with the actual Python module where you defined the router. Next, modify the models and give them app_labels:

# models.py

class Customer(models.Model):
    name = models.CharField(max_length=20)
    customer_id = models.CharField(max_length=120)

    class Meta:
        app_label = 'customer_data'


class User(models.Model):
    name = models.CharField(max_length=20)
    user_id = models.CharField(max_length=120)
    age = models.IntegerField()

    class Meta:
        app_label = 'user_data'

Synchronising your databases.

migrate works on the default database by default, so to synchronize changes to other databases, pass them as options to the command:

./manage.py migrate --database=customers_db

Conclusion

This post gave you an overview of working with multiple databases in Django. You saw how to add more than one database to the configuration, how to manually select a database to be targeted by a query or save operation and how to create custom database routers. The official docs cover more detail about how to perform additional operations such as deleting records, or moving records between databases. Thanks for reading.

References:

Let Django know about your Database

Specifying the database to use when making queries.

Synchronising your databases.

Conclusion

Related