Set up multiple databases on django

Step

1. Create a virtual environment
2. Create a Django project
3. Create a database server

Define your database

The first step in using multiple databases in Django is to tell Django which database server you will use. This is done using the DATABASES setting. The database can have any alias you choose. However, the alias default value has a special meaning. When no other database is selected, Django uses the database aliased as default

The following is a sample fragment of settings.py defining two databases-a default PostgreSQL database and a MySQL database named 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 a default database entry to be defined, but if not used, the parameter dictionary can be left blank. To do this, you must set DATABASE_ROUTERS for all application models so that queries are not routed to the default database

The following is a sample settings.py snippet defining two non-default databases, the default entries are intentionally left blank

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 try to access a database that is not defined in the DATABASES setting, Django will raise a django.utils.connection.ConnectionDoesNotExist exception

Synchronize the database

The migration management command is run on a database at the same time. By default, it runs on the default database, but by providing the --database option, you can tell it to synchronize different databases. Therefore, to synchronize all models to all databases in the first example above, you need to call

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

If you do not want each application to be synchronized to a specific database, you can define a database router to implement a strategy to limit the availability of a specific model. If you leave the default database blank as in the example above, you must provide a database name every time you run migrate

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

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

An exception to this rule is the makemigrations command. It verifies the migration history in the database to find problems with existing migration files before creating a new migration. By default, it only checks the default database, but if a router is installed, it will query the router's allow_migrate() method

Automatic database routing

Once you have defined the database in the DATABASES settings, now we need to deal with automatic routing.

1. Create a folder named routers
2. Create a file named db_routers.py in the folder
3. Create a database router named AuthRouter to control all database operations on models in auth and contenttypes-add the following code to 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

If the application name of the model matches any of the values, it will return and use the selected database. In this case, only users and account applications can access the users_db database because it is in route_app_labelsroute_app_labels

The database router is a class that provides at most four methods:

1.db_for_read(model, **hints)
2.db_for_write(model, hints)
3.allow_relation(obj1, obj2, **hints)
4.allow_migrate(db, app_label, model_name=None, **hints)

Connect to the router

Use the DATABASE_ROUTERS setting to install the database router. This setting defines a list of class names, each of which specifies a router that should be used by the main router.

Django's database operations use the main router to allocate database usage. Whenever a query needs to know which database to use, it will call the main router and provide the model and hints (if available). Then Django tries each router in turn until it finds a database suggestion.

Finally, in the settings file, we add the following code (replace path.to. with the actual Python path of the module that defines the router)

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

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

In this example, the AuthRouter is to process the UsersRouter before, as a result, before any other decisions are made, the authentication model is decided to process

Manually select the database

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

You can select the database for the QuerySet at any point in the QuerySetchain. Call using() on the QuerySet to obtain another QuerySet that uses the specified database.

using() accepts one parameter: the alias of the database on which to run the query

E.g

>>> # 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()