How to set up multiple databases on django

This blog post will show you how to set up multiple databases on your django application.

Basic Setup

Create your virtual environment

Create a Django project

Create your database servers

Defining your databases

The first step to using more than one database with Django is to tell Django about the database servers you’ll be using. This is done using the 'DATABASES' setting. Databases can have any alias you choose. However, the alias default has special significance. Django uses the database with the alias of default when no other database has been selected.

The following is an example settings.py snippet defining two databases – a default PostgreSQL database and a MySQL database called users_db:

DATABASES = {
    'default': {
        'NAME': 'app_data',
        'ENGINE': 'django.db.backends.postgresql',
        'USER': 'postgres_user',
        'PASSWORD': 's3krit'
    },
    'users_db': {
        'NAME': 'user_data',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'mysql_user',
        'PASSWORD': 'priv4te'
    }
}

Django requires that a default database entry be defined, but the parameters dictionary can be left blank if it will not be used. To do this, you must set up DATABASE_ROUTERS for all of your apps’ models so that no queries are routed to the default database.

The following is an example settings.py snippet defining two non-default databases, with the default entry intentionally left empty:

DATABASES = {
    'default': {},
    'users': {
        'NAME': 'user_data',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'mysql_user',
        'PASSWORD': 'superS3cret'
    },
    'customers': {
        'NAME': 'customer_data',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'mysql_cust',
        'PASSWORD': 'veryPriv@ate'
    }
}

If you attempt to access a database that you haven’t defined in your DATABASES setting, Django will raise a django.utils.connection.ConnectionDoesNotExist exception.

Synchronizing your databases

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. So, to synchronize all models onto all databases in the first example above, you would need to call:

$ ./manage.py migrate 
$ ./manage.py migrate --database=users

If you don’t want every application to be synchronized onto a particular database, you can define a database router that implements a policy constraining the availability of particular models. If, as in the example above, you’ve left the default database empty, you must provide a database name each time you run migrate.

$ ./manage.py migrate --database=users
$ ./manage.py migrate --database=customers

Most other django-admin commands that interact with the database operate in the same way as migrate – they only ever operate on one database at a time, using --database to control the database used.

An exception to this rule is the makemigrations command. It validates the migration history in the databases to catch problems with the existing migration files before creating new migrations. By default, it checks only the default database, but it consults the allow_migrate() method of routers if any are installed.

Automatic database routing

Once you've got your databases defined in the DATABASES setting, now we'll need to handle automatic routing.

Create a folder called 'routers'

Inside the folder create a file called db_routers.py

Create a database router called AuthRouter to control all database operations on models in the auth and contenttypes - add the following code inside the db_routers.py file:

class AuthRouter:
    route_app_labels = {'auth', 'contenttypes'}

    def db_for_read(self, model, **hints):
        if model._meta.app_label in self.route_app_labels:
            return 'auth_db'
        return None

    def db_for_write(self, model, **hints):
        if model._meta.app_label in self.route_app_labels:
            return 'auth_db'
        return None

    def allow_relation(self, obj1, obj2, **hints):
        if (
            obj1._meta.app_label in self.route_app_labels or
            obj2._meta.app_label in self.route_app_labels
        ):
           return True
        return None

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if app_label in self.route_app_labels:
            return db == 'auth_db'
        return None

Create router for your other database, inside route_app_labels add the apps that you want to allow to have access to the database. See following code for example:

class UsersRouter:
    route_app_labels = {'users', 'accounts'}

    def db_for_read(self, model, **hints):
        if model._meta.app_label in self.route_app_labels:
            return 'users_db'
        return None

    def db_for_write(self, model, **hints):
        if model._meta.app_label in self.route_app_labels:
            return 'users_db'
        return None


    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if app_label in self.route_app_labels:
            return db == 'users_db'
        return None

If the app name of the model matches any value in route_app_labels then it returns and uses the selected database. In this case, only the users and accounts apps can access the users_db database as it is specified in route_app_labels.

A database Router is a class that provides up to four methods:

db_for_read(model, **hints)

db_for_write(model, hints)

allow_relation(obj1, obj2, **hints)

allow_migrate(db, app_label, model_name=None, **hints)

More info on Django Docs - Database Router Methods

Connecting the routers

Database routers are installed using the DATABASE_ROUTERS setting. This setting defines a list of class names, each specifying a router that should be used by the master router.

The master router is used by Django’s database operations to allocate database usage. Whenever a query needs to know which database to use, it calls the master router, providing a model and a hint (if available). Django then tries each router in turn until a database suggestion can be found.

Finally, in the settings file, we add the following code (substituting path.to. with the actual Python path to the modules where the routers are defined):

DATABASE_ROUTERS = ['path.to.AuthRouter', 'path.to.UsersRouter']

The order in which routers are processed is significant. Routers will be queried in the order they are listed in the DATABASE_ROUTERS setting.

In this example, the AuthRouter is processed before the UsersRouter, and as a result, decisions concerning the models in auth are processed before any other decision is made.

Manually selecting a database

Django also provides an API that allows you to maintain complete control over database usage in your code. A manually specified database allocation will take priority over a database allocated by a router.

You can select the database for a QuerySet at any point in the QuerySet “chain.” Call using() on the QuerySet to get another QuerySet that uses the specified database.

using() takes a single argument: the alias of the database on which you want to run the query.

For example:

>>> # This will run on the 'default' database.
>>> Publisher.objects.all()

>>> # So will this.
>>> Publisher.objects.using('default').all()

>>> # This will run on the 'users' database.
>>> Publisher.objects.using('users').all()

Thanks for reading! If you have any questions, issues, feedback or corrections, please let me know!