tenant-schemas and multiple databases

[sigilioso]

Hi, I am setting up tenat-schemas with a postgresql cluster, I am using a db router to handle connections to master and slave nodes. As a result, I need to set several connections using the 'tenant_schemas.postgresql_backend' engine. Something like this:

DATABASES = {
    'default': {  # connection to a slave
        'ENGINE': 'tenant_schemas.postgresql_backend',
        # ...
    },
    'master': {  # connection to master
        'ENGINE': 'tenant_schemas.postgresql_backend',
        # ...
    }    

When a schema is set, the default connection is used by calling connection.set_schema or connection.set_tenant because django.db.connection is a proxy object for the default connection. Therefore, when using a different connections no tenant is set.

I managed to make this work by extending the tenant_schemas.postgresql_backend so it set the schema in all connections:

from django.db import connections

from tenant_schemas.postgresql_backend.base import DatabaseWrapper as TenantSchemasDatabaseWrapper

def _iter_tenant_connections(exclude=None):
    if exclude is None:
        exclude = ()
    for conn in connections.all():
        if hasattr(conn, 'set_tenant') and conn not in exclude:
            yield conn

class DatabaseWrapper(TenantSchemasDatabaseWrapper):

    def __init__(self, *args, **kwargs):
        TenantSchemasDatabaseWrapper.__base__.__init__(self, *args, **kwargs)
        self.set_schema_to_public(spread=False)

    def set_tenant(self, tenant, include_public=True, spread=True):
        super(DatabaseWrapper, self).set_tenant(tenant, include_public)
        if spread:
            for conn in _iter_tenant_connections(exclude=(self,)):
                conn.set_tenant(tenant, include_public, spread=False)

    def set_schema(self, schema_name, include_public=True, spread=True):
        super(DatabaseWrapper, self).set_schema(schema_name, include_public)
        if spread:
            for conn in _iter_tenant_connections(exclude=(self,)):
                conn.set_schema(schema_name, schema_name, spread=False)

    def set_schema_to_public(self, spread=True):
        super(DatabaseWrapper, self).set_schema_to_public()
        if spread:
            for conn in _iter_tenant_connections(exclude=(self,)):
                conn.set_schema_to_public(spread=False)

Now I wonder if supporting several connection or using tenant-schemas in a database connection which is not the default one could be an interesting feature. In that case, I think a different approach would be more convenient. Maybe defining a new function to set a tenant/schema in all tenant connections and use it instead of connection.set_schema / connection.set_tenant in the middleware, commands, etc.

What do you guys think?

[thelinuxer]

Hi, I tried using this backend with django-tenant-schemas=1.7.0 and Django==1.10.6

The migration keeps saying "no migrations to apply" even when creating a new tenant. It seems to be detecting whether or not migrations should apply from django_migrations table in public schema. How does tenant-schemas know when a schema needs to be migrated ?

I tried also the approach you mentioned. I created "set_tenant" and "set_schema" functions that should set all connections properly. Unfortunately this also failed to work as expected. It works properly while creating tables but fails once a data migration that requires reading (which reads from the replica) appear.

My router is pretty simple:

class PrimaryReplicaRouter(object):
    def db_for_read(self, model, **hints):
        """
        Reads go to a replica.
        """
        return 'replica'

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

and In the settings.py file I have:

DATABASES = {
    'default': {
        'ENGINE': 'tenant_backend',
        'NAME': 'userdb',
        'USER': 'master',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': 12010,
        'ATOMIC_REQUESTS': True
    },
    'replica': {
        'ENGINE': 'tenant_backend',
        'NAME': 'userdb',
        'USER': 'master',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': 12011,
        'ATOMIC_REQUESTS': True
    },
}

DATABASE_ROUTERS = (
    'tenant_schemas.routers.TenantSyncRouter',
    'replicationrouter.PrimaryReplicaRouter',
)

[bernardopires]

Closing this in favor of #353