Hi Adam
I came across this comment from Florian https://groups.google.com/g/django-developers/c/clzg6MiixFc/m/azrqPlCVCQAJ. Here he sites a custom database backend implementation you mentioned over here ara/ara/server/db/backends/distributed_sqlite/base.py at master · ansible-community/ara · GitHub.
I tried to replicate the same but with slight modifications.
Instead of using thread local variables I used connection.settings_dict['NAME']
Custom database backend:
#mydbengine
from django.db.backends.postgresql.base import DatabaseWrapper as BaseDatabaseWrapper
from django.db import connection
class DatabaseWrapper(BaseDatabaseWrapper):
"""
Custom postgresql database backend meant to work with multi-tenancy
in order to dynamically load different databases at runtime.
"""
def get_new_connection(self, conn_params):
conn_params["database"] = connection.settings_dict['NAME']
return super().get_new_connection(conn_params)
Middleware:
from django.db import connection
from django.utils.deprecation import MiddlewareMixin
class ChangeDatabaseMiddleware(MiddlewareMixin):
def process_request(self, request):
# Change the database connection to the database for the current tenant.
domain = request.get_host().split('.')[0]
connection.settings_dict = DATABASES[domain]
Router:
from django.db import connection
class TenantRouter:
"""
A router to route database requests based on the tenant.
"""
def get_current_tenant(self):
"""
Used to fetch the current tenant
"""
return connection.settings_dict['NAME']
def db_for_read(self, model, **hints):
"""
Attempts to read from the tenant's database.
"""
return self.get_current_tenant()
def db_for_write(self, model, **hints):
"""
Attempts to write to the tenant's database.
"""
return self.get_current_tenant()
def allow_migrate(self, db, app_label, model_name=None, **hints):
"""
Make sure migrations are only applied to the tenant's database.
"""
return True
def allow_relation(self, obj1, obj2, **hints):
"""
Allow relations if both objects are in the same tenant's database.
"""
return True
DATABASES = {
'default': {},
'tenanta': {
'ENGINE': 'mydbengine',
'NAME': 'tenanta',
'USER': 'postgres',
'PASSWORD': 'postgres',
'HOST': 'localhost',
'PORT': '5432',
},
'tenantb': {
'ENGINE': 'mydbengine',
'NAME': 'tenantb',
'USER': 'postgres',
'PASSWORD': 'postgres',
'HOST': 'localhost',
'PORT': '5432',
}
}
This approach works for most cases but when we have a transaction.atomic() decorator it doesn’t pick the correct database without providing the using parameter.
This is because in
#django.db.transaction.py
def get_connection(using=None):
"""
Get a database connection by name, or the default database connection
if no name is provided. This is a private API.
"""
if using is None:
using = DEFAULT_DB_ALIAS
return connections[using]
#django.db.utils.py
DEFAULT_DB_ALIAS = 'default'
Here we can see no matter if we set connection.settings_dict['NAME'] dynamically using a custom middleware or a custom database backend the transaction APIs fall back to DEFAULT_DB_ALIAS set to “default” without providing using param
The problem is doing transaction.atomic(using=connection.settings_dict['NAME']) works well in individual django projects search and replace would not be ideal but will work.
But this is a problem in third party packages which still use transaction.atomic() without the using param
Then we would have to search and replace in each and every third party packages.
This can be avoided by simply just replacing the DEFAULT_DB_ALIAS in django.db.utils.py but this is bad coding practice.
Is there any way to override the default transaction behavior using a custom database backend as you mentioned.
#35349 (Transaction API does not respect the DATABASE_ROUTERS configuration) – Django @diachkow
Cheers,
Febin