Django - multiple databases - how to define the router?

Question

I would like to connect my application to 3 different databases.

To do this, I've changed the following code in the settings file:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql', 
        'NAME': 'local_db',
        'USER': 'root',
        'PASSWORD': 'password1',
        'HOST': 'localhost',
        'PORT': '3306',
        'OPTIONS': { 'sql_mode': 'traditional', }
    },
    'firstExternalDb': {
        'ENGINE': 'django.db.backends.mysql', 
        'NAME': 'firstExternalDb',
        'USER': 'userName',
        'PASSWORD': 'password1',
        'HOST': 'firstExternalDb.node.com',
        'PORT': '3306',
        'OPTIONS': { 'sql_mode': 'traditional', }
    },
    'secondExternalDb': {
        'ENGINE': 'django.db.backends.mysql', 
        'NAME': 'secondExternalDb',
        'USER': 'userName',
        'PASSWORD': 'password1',
        'HOST': 'secondExternalDb.node.com',
        'PORT': '3306',
        'OPTIONS': { 'sql_mode': 'traditional', }
    },
}

And I want to have a possibility to specify for which database I will create a datatable. For example all django tables like 'auth_group' , 'django_admin_log' I want to save in localhost.

Was trying to create a router going through this tutorial https://docs.djangoproject.com/pl/1.11/topics/db/multi-db/#topics-db-multi-db-routing

But I do not understand this. Could you answer my questions:

Should I create new router for each application ?
how to define that all django tables should be used by default database ?

I've created two routers inside two models file: The first one only for default DB:

class defaultRouter:
    def db_for_read(self, model, **hints): 
        return 'default'

    def db_for_write(self, model, **hints): 
        return 'default'

    def allow_relation(self, obj1, obj2, **hints):
        db_list = ('default')
        if obj1._state.db in db_list and obj2._state.db in db_list: 
            return True
        return None

    def allow_migrate(self, db, app_label, model_name=None, **hints): 
        return True

And the second one inside the next app:

class secondExternalDbRouter:
    def db_for_read(self, model, **hints): 
        return 'secondExternalDb'

    def db_for_write(self, model, **hints): 
        return 'secondExternalDb'

    def allow_relation(self, obj1, obj2, **hints):
        db_list = ('secondExternalDb')
        if obj1._state.db in db_list and obj2._state.db in db_list: 
            return True
        return None

    def allow_migrate(self, db, app_label, model_name=None, **hints): 
        return True

Both routers are right under the model class, in the same file.

Settings file:

DATABASE_ROUTERS = ['mainApp.models.defaultRouter','secondApp.models.secondExternalDbRouter',]

@MarkChackerian has a good answer to clear up some misconceptions you are having. On top of that, your router order is backwards. Even so, you shouldn't need a default router since that is already implied when the `secondExternalDbRouter` returns `None`. — Bobort, Mar 28 '23 at 19:48

score 2 · Answer 1 · answered Jan 24 '18 at 16:35

You don't have to have to create a new router for each application. You might want to do it if makes your code more modular, i.e. you can keep the router together with some other code, but personally I wouldn't do that initially.

If you don't specify a read or write mapping for a table, the default database will be used. In other words, you only need to create the routing logic for any case that is NOT using the default database. For example, this would force a table to be read only but not affect anything else:

class MyCustomRouter(object):
    def db_for_write(self, model, **hints):
        if model == MyReadOnlyModel:
            raise Exception("This model is read only. Shame!")
        return None

See Django - how to specify a database for a model? for more examples.

Ranjith Singhu Ganapathy · Answer 2 · 2018-01-24T17:04:17.763

Default Router

class DefaultDBRouter(object):
    """
    A router to control all database operations on models in the
    auth application.
    """

    def db_for_read(self, model, **hints):
        """
        Reads go to a default.
        """
        return "default"

    def db_for_write(self, model, **hints):
        """
        Writes always go to default.
        """
        return 'default'

    def allow_relation(self, obj1, obj2, **hints):
        """
        Relations between objects are allowed if both objects are
        in the default.
        """
        db_list = ('default')
        if obj1._state.db in db_list and obj2._state.db in db_list:
            return True
        return None

    def allow_migrate(self, db, app_label, model=None, **hints):
        """
        All non-micro-management models end up in this pool.
        """
        return True

FirstExternalDBRouter

FIRST_EXTERNAL_APPS = [
    # DEFINE APPS TO USE THIS DB
]


class FirstExternalDBRouter(object):
    def db_for_read(self, model, **hints):
        if model._meta.app_label in FIRST_EXTERNAL_APPS:
            return 'firstExternalDb'
        return None

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

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

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if app_label in FIRST_EXTERNAL_APPS:
            return db == 'firstExternalDb'
        return None

SecondExternalDBRouter

SECOND_EXTERNAL_APPS = [
    # DEFINE APPS TO USE THIS DB
]


class SecondExternalDBRouter(object):
    def db_for_read(self, model, **hints):
        if model._meta.app_label in SECOND_EXTERNAL_APPS:
            return 'secondExternalDb'
        return None

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

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

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if app_label in SECOND_EXTERNAL_APPS:
            return db == 'secondExternalDb'
        return None

Define all three router in settings in the parameter DATABASE_ROUTERS

All three database will have same schema migration(tables), only you are going to control which database to read and write on accessing a model.

thanks for the code. I changed everything but still if I run the command: "python2.7 manage.py migrate --database=secondExternalDb" — py nji22, Jan 24 '18 at 17:09
the program creates all django datatables inside the secondExternalDb database — py nji22, Jan 24 '18 at 17:10
When maintaining more than one DB you should make sure all DB to have same schema(tables). Tables will be same, but data persist in the DB that you specified. — Ranjith Singhu Ganapathy, Jan 25 '18 at 12:24

Django - multiple databases - how to define the router?

2 Answers2