Django Multiple Databases

[OanaRatiu]

Hello!

I want to use Django's multiple databases functionality, using Postgresql. I also need them to be tenanted. Django-tenant-schemas does not support this, it uses the connection object that maps to the default database. In Django docs, they say we should use the connections['my-db-alias'] to connect to the correct database if we want multiple databases ( https://docs.djangoproject.com/ja/1.9/topics/db/multi-db/#using-raw-cursors-with-multiple-databases ). Will this functionality be supported any soon in this library?

Thanks, Oana

[bernardopires]

No, this app uses postgres' schemas to accomplish tenants and does not support multiple databases.

[dhruvgargTA]

I think this is more related to allowing for read or write replicas in Postgres. We are looking to use django-tenant-schemas with a single master and a few read replicas and the current implementation only patches the default DB's connection.

#365 Is probably closer to what @OanaRatiu was asking for.

[ishehata]

Yes we also need this for replicate state-full sets.

[thelinuxer]

Can we reopen this issue please ? Replication is totally not functioning with the current database wrapper since it sets the schema only on the default connection as already mention in issue #365.

[tgroshon]

A separate but related use case for multiple databases would be storing tenant schemes on separate database clusters. For instance, store clients 1-5 on one database instance/cluster and clients 6-10 on a second database instance/cluster. This is very beneficial as apps grow because old tenants with lots of data can be given their own cluster while you pack a bunch of newer tenants (which naturally have less data) on a different cluster. Could be solved by storing the database address and port to connect to along with the name of the schema.

For example, Switchman is a Ruby on Rails tool that supports this use case: https://github.com/instructure/switchman/

[ishehata]

@bernardopires man, can you please read the comments and re-open the issue ?

[bernardopires]

I agree with this. I don't have a suggestion on how to approach this as I don't have the experience of handling multiple databases. I think something simple using the database router should be possible (master writes, all others read)

[kc-diabeat]

This is a working version of django-tenant-schemas with multi-db support: https://github.com/kc-diabeat/django-tenant-schemas @bernardopires - It will require some more touch-ups and tests. If you think this can be incorporated into this project, please create separate branch for this issue that I can add a pull request to.

Usage:

1. Create 3 databases tenant_tutorial, tenant_tutorial1 and tenant_tutorial2 in postges as defined in tenant_tutorial_multidb.settings

2. Migrate Schemas for all dbs:

   • ./manage.py migrate_schemas --settings='tenant_tutorial_multidb.settings'
   • ./manage.py migrate_schemas --settings='tenant_tutorial_multidb.settings' --database='db1'
   • ./manage.py migrate_schemas --settings='tenant_tutorial_multidb.settings' --database='db2'

3. Create public Client for the db1 and db2 in shell:

   • ./manage.py shell --settings='tenant_tutorial_multidb.settings'
   • Client(name='publicdb1', schema_name='public', domain_url='public.trendy-sass.com').save(using='db1')
   • Client(name='publicdb2', schema_name='public', domain_url='public.trendy-sass.com').save(using='db2')

4. Create tenant1 client from shell for db1 and db2 as step 3.

   • Client(name='t1db1', schema_name='t1db1', domain_url='t1.trendy-sass.com').save(using='db1')
   • Client(name='t1db2', schema_name='t1db2', domain_url='t1.trendy-sass.com').save(using='db2')

5. Runserver: ./manage.py runserver --settings='tenant_tutorial_multidb.settings' Below urls may be accessed to access the schemas on specified db http://public.trendy-sass.com:8000/db1/ http://public.trendy-sass.com:8000/db2/ http://t1.trendy-sass.com:8000/db1/ http://t1.trendy-sass.com:8000/db2/

[ranjur]

I have added some tweaks in the middleware and the schema_context and tenant_context seems working for me: Here is the code snippet I have created middleware.py

class BaseTenantMiddleware(django.utils.deprecation.MiddlewareMixin):
    TENANT_NOT_FOUND_EXCEPTION = Http404

    """
    Subclass and override  this to achieve desired behaviour. Given a
    request, return the tenant to use. Tenant should be an instance
    of TENANT_MODEL. We have three parameters for backwards compatibility
    (the request would be enough).
    """

    def get_tenant(self, model, hostname, request):
        raise NotImplementedError

    def hostname_from_request(self, request):
        """ Extracts hostname from request. Used for custom requests filtering.
            By default removes the request's port and common prefixes.
        """
        return remove_www(request.get_host().split(":")[0]).lower()

    def set_connection_to_public(self):
        connection.set_schema_to_public()

    def set_connection_to_tenant(self, tenant):
        connection.set_tenant(tenant)

    def process_request(self, request):
        # Connection needs first to be at the public schema, as this is where
        # the tenant metadata is stored.
        self.set_connection_to_public()

        hostname = self.hostname_from_request(request)
        TenantModel = get_tenant_model()

        try:
            # get_tenant must be implemented by extending this class.
            tenant = self.get_tenant(TenantModel, hostname, request)
            assert isinstance(tenant, TenantModel)
        except TenantModel.DoesNotExist:
            raise self.TENANT_NOT_FOUND_EXCEPTION(
                "No tenant for {!r}".format(request.get_host())
            )
        except AssertionError:
            raise self.TENANT_NOT_FOUND_EXCEPTION(
                "Invalid tenant {!r}".format(request.tenant)
            )

        request.tenant = tenant

        self.set_connection_to_tenant(request.tenant)

        # Do we have a public-specific urlconf?
        if (
            hasattr(settings, "PUBLIC_SCHEMA_URLCONF")
            and request.tenant.schema_name == get_public_schema_name()
        ):
            request.urlconf = settings.PUBLIC_SCHEMA_URLCONF
            set_urlconf(request.urlconf)

class MultiDBTenantMiddleware(SuspiciousTenantMiddleware):
    def set_connection_to_public(self):
        for db in get_db_alias():
            connections[db].set_schema_to_public()

    def set_connection_to_tenant(self, tenant):
        for db in get_db_alias():
            connections[db].set_tenant(tenant)

use MultiDBTenantMiddleware as middleware

in utils.py

MULTI_DB_ENABLED = True if len(settings.DATABASES.keys()) > 1 else False

def get_db_alias():
    return settings.DATABASES.keys()

# Changes to schema_context and tenant_context when multi db enabled
if MULTI_DB_ENABLED:
    def get_previous_tenant_dict():
        previous_tenant_dict = dict()
        for db in get_db_alias():
            previous_tenant_dict[db] = connections[db].tenant
        return previous_tenant_dict

    def apply_previous_tenant_dict(previous_tenant_dict):
        if not previous_tenant_dict:
            for db in get_db_alias():
                connections[db].set_schema_to_public()
        else:
            for db in get_db_alias():
                connections[db].set_tenant(previous_tenant_dict[db])

    @contextmanager
    def schema_context(schema_name):
        previous_tenant_dict = get_previous_tenant_dict()
        try:
            for db in get_db_alias():
                connections[db].set_schema(schema_name)
            yield
        finally:
            apply_previous_tenant_dict(previous_tenant_dict)

    @contextmanager
    def tenant_context(tenant):
        previous_tenant_dict = get_previous_tenant_dict()
        try:
            for db in get_db_alias():
                connections[db].set_tenant(tenant)
            yield
        finally:
            apply_previous_tenant_dict(previous_tenant_dict)

Need to use the normal replica router after the above changes

• There might be some alteration we needs to do in migration script, cache.py and logs.py