# Use DATABASE\_ROUTERS to pick a database connection for Transaction API by default

**URL:** <https://forum.djangoproject.com/t/use-database-routers-to-pick-a-database-connection-for-transaction-api-by-default/29744>\
**Category:** ORM\
**Created:** [April 2, 2024, 1:58pm UTC](https://forum.djangoproject.com/t/use-database-routers-to-pick-a-database-connection-for-transaction-api-by-default/29744 "2024-04-02T13:58:02Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![diachkow](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/diachkow/32/20329_2.png) [@diachkow](https://forum.djangoproject.com/u/diachkow)\
**Post date:** [April 2, 2024, 1:58pm UTC](https://forum.djangoproject.com/t/use-database-routers-to-pick-a-database-connection-for-transaction-api-by-default/29744/1 "2024-04-02T13:58:02Z")

</div>

In general, I am bumping the older discussion from [Google Groups](https://groups.google.com/g/django-developers/c/clzg6MiixFc) that took place a couple of years ago and [Django Issue Tracker](https://code.djangoproject.com/ticket/35349#comment:2) ticket that I opened recently, but it turned out that the behavior described is expected and documented in Django’s Transaction API docs.

The goal of this thread is to start a discussion about changing the default behaviour of the Transaction API. In my opinion, all transaction commands should be resolved via DATABASE\_ROUTERS configuration and if none is available, then just use the DEFAULT\_DB\_ALIAS value to execute. It could either use a value returned by `db_for_write()` method of database routers or rely on new, introduced by this feature, `db_for_transaction()` method.

The reason why I find the current behavior unexpected is that the other Django ORM APIs, such as the QuerySet API, always resolve to the database alias provided via DATABASE\_ROUTERS if it is not explicitly specified via `.using()` queryset method or `using=` argument of one of the API methods.

---

<div class="post-metadata">

**Author:** ![diachkow](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/diachkow/32/20329_2.png) [@diachkow](https://forum.djangoproject.com/u/diachkow)\
**Post date:** [April 2, 2024, 2:05pm UTC](https://forum.djangoproject.com/t/use-database-routers-to-pick-a-database-connection-for-transaction-api-by-default/29744/2 "2024-04-02T14:05:48Z")

</div>

There’s actually [another topic](https://forum.djangoproject.com/t/django-transactions-and-multiple-database-issue/3525) that describes the similar problem that I have faced. But, there’s no replies since July 2020, so I guess its time to bring up this discussion again.

---

<div class="post-metadata">

**Author:** ![adamchainz](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/adamchainz/32/26_2.png) [@adamchainz](https://forum.djangoproject.com/u/adamchainz)\
**Post date:** [April 2, 2024, 10:07pm UTC](https://forum.djangoproject.com/t/use-database-routers-to-pick-a-database-connection-for-transaction-api-by-default/29744/3 "2024-04-02T22:07:45Z")

</div>

Hi @diachkow

The low traffic is probably due to Django’s behaviour working for nearly everyone. The main use case for database routers is shifting some read queries to a replica, so transactions being on the default/primary database probably makes little difference.

I saw on your ticket:

> I am developing an application that switches the database connection on per-tenant bases. The database configurations are added to settings.DATABASES at runtime in a middleware

That is really pushing Django outside of its design goals. Django doesn’t support runtime mutation of settings except for testing. But particularly so with `settings.DATABASES` - it has a special warning when you change it: [django/django/test/signals.py at 888b9042b3598bab6557c62de82505eec9ea62ed · django/django · GitHub](https://github.com/django/django/blob/888b9042b3598bab6557c62de82505eec9ea62ed/django/test/signals.py#L163-L171) .

(Have you considered PostgreSQL schemas for tenant isolation? There are a number of high quality DjangoCon talks on the topic and various packages: [https://www.youtube.com/results?search\_query=tenant+postgresql+django](https://www.youtube.com/results?search_query=tenant+postgresql+django) .)

On the Google Groups discussion, Aymeric wrote:

> You can meaningfully route individual ORM queries based on the model being queried and it’s often useful to do so. So Django supports it.
> 
> It would be convenient to do the same for transaction blocks, but it isn’t possible in a gener  
> ally useful way because you don’t know yet which models will be queried. So Django doesn’t support it.

I think that summarizes the main problem I see - the router can’t meaningfully know what the transaction is for. Transactions can be for reads or writes, and what the transaction will do depends on the code within.

I think you’re probably better off using your own `atomic()` wrapper that calls the router, if that is what you want. Maybe call it `write_atomic()` and do `using=router.db_for_write(...))` ?

---

<div class="post-metadata">

**Author:** ![diachkow](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/diachkow/32/20329_2.png) [@diachkow](https://forum.djangoproject.com/u/diachkow)\
**Post date:** [April 4, 2024, 9:33am UTC](https://forum.djangoproject.com/t/use-database-routers-to-pick-a-database-connection-for-transaction-api-by-default/29744/4 "2024-04-04T09:33:17Z")

</div>

> Have you considered PostgreSQL schemas for tenant isolation?

Yes, I have considered using schemas for multi-tenant support. In fact, in our application we are like having two tiered multi-tenancy. To keep it simple, the organizations that are the main customers of our applications can create different, let us call them “projects” in our system. Each project should have different participants, and their data and the project data itself should be stored separately from the data of other projects. At first, to have this in our system, we used the [django-tenants](https://github.com/django-tenants/django-tenants) library to organize the data on projects. Now that we need to keep the data of each organization separately in a dedicated database instance, as this is the part of the business requirements we have, we need to organize this dynamic database connection feature in our Django application.

We managed to implement our custom middleware + database router pair, which dynamically establishes new connections at runtime and adds them to DATABASES settings and routes them to a necessary database alias, using a shared context variable (`contextlib.ContextVar` instance).

> I think you’re probably better off using your own `atomic()` wrapper that calls the router

This was the first thing we thought of when we were trying to figure out how to use the desired database alias for our transaction operations. But since other third-party libraries, like the django-tenants themselves, can also call the Transaction API with the default `@atomic’ decorator, this didn’t work.

The workaround we found to fulfill our needs is to patch `django.db.transaction.DEFAULT_DB_ALIAS` and set it to a string-like object that dynamically resolves to the desired database alias. This way, Django’s Transaction API now has a default fallback to our dynamic database alias.

After your explanation of why database routers can’t be used for the Transaction API because we don’t know the models that the operations are calling, I think it would be nice to have an option to somehow override the default behavior of the `django.db.transaction.get_connection(...)` function. If only we had an option to specify via Django settings the path to a callable object that takes an optional database alias as its only argument and should return the DatabaseWrapper object, i.e. the connection object, and this callable would be used to resolve the database connection for the Transaction API - that would be awesome. I am thinking of something like

```python
TRANSACTION_CONNECTION_FACTORY = 'django.db.transaction.get_connection'

```

Or we can copy-paste the current get\_connection implementation to a new function and then make a new implementation of `get_connection` that rely on `TRANSACTION_CONNECTION_FACTORY` settings:

```python
# ... where ever django settings defaults are stored ...

TRANSACTION_CONNECTION_FACTORY = 'django.db.transaction.default_connection_factory'

# ... in django.db.transaction module ...

from django.conf import settings
from django.utils.module_loading import import_string

def get_connection(using=None):
   factory = import_string(settings.TRANSACTION_CONNECTION_FACTORY)
   return factory(using)

```

This way, we can make the Transaction API to use a dynamically-resolved db alias for executing transactions.

---

<div class="post-metadata">

**Author:** ![adamchainz](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/adamchainz/32/26_2.png) [@adamchainz](https://forum.djangoproject.com/u/adamchainz)\
**Post date:** [April 4, 2024, 1:51pm UTC](https://forum.djangoproject.com/t/use-database-routers-to-pick-a-database-connection-for-transaction-api-by-default/29744/5 "2024-04-04T13:51:40Z")

</div>

> [@diachkow](#):
>
> In fact, in our application we are like having two tiered multi-tenancy.

Gosh, that sounds complicated. All the best with it.

> [@diachkow](#):
>
> I think it would be nice to have an option to somehow override the default behavior of the `django.db.transaction.get_connection(...)` function.

I think you would instead be best doing this within a [custom database backend](https://docs.djangoproject.com/en/5.0/ref/databases/#subclassing-the-built-in-database-backends). You can then swap your configuration on a per-instance basis. That _should_ allow you to avoid changing `settings.DATABASES`, and will avoid the need for any “transaction connection factory”, since it’s more specific.

---

<div class="post-metadata">

**Author:** ![diachkow](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/diachkow/32/20329_2.png) [@diachkow](https://forum.djangoproject.com/u/diachkow)\
**Post date:** [April 4, 2024, 2:22pm UTC](https://forum.djangoproject.com/t/use-database-routers-to-pick-a-database-connection-for-transaction-api-by-default/29744/6 "2024-04-04T14:22:47Z")

</div>

Well, maybe it’s actually a good idea to use a custom database backend for our use case. We did not consider this as it felt like a little overhead for my task, but with all the issues we faced with patching the `settings.DATABASES` object and the transactions API underneath, maybe the DB backend is really what we were looking for. I’ll let you know later here if that worked out. However, I still think it would be great to have an option to somehow change the default fall-back option for the DB alias in the Transactions API and have an option to resolve this dynamically, i.e. be a callable object.

---

<div class="post-metadata">

**Author:** ![febin-micheal](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/febin-micheal/32/20376_2.png) [@febin-micheal](https://forum.djangoproject.com/u/febin-micheal)\
**Post date:** [April 5, 2024, 7:28am UTC](https://forum.djangoproject.com/t/use-database-routers-to-pick-a-database-connection-for-transaction-api-by-default/29744/7 "2024-04-05T07:28:13Z")

</div>

Hi Adam

I came across this comment from Florian [https://groups.google.com/g/django-developers/c/clzg6MiixFc/m/azrqPlCVCQAJ](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](https://github.com/ansible-community/ara/blob/master/ara/server/db/backends/distributed_sqlite/base.py).  
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:

```auto
#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:

```auto
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:

```auto
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

```

```auto
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

```auto
#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]

```

```auto
#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](https://code.djangoproject.com/ticket/35349) @diachkow

Cheers,  
Febin

---

<div class="post-metadata">

**Author:** ![febin-micheal](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/febin-micheal/32/20376_2.png) [@febin-micheal](https://forum.djangoproject.com/u/febin-micheal)\
**Post date:** [April 9, 2024, 12:51pm UTC](https://forum.djangoproject.com/t/use-database-routers-to-pick-a-database-connection-for-transaction-api-by-default/29744/8 "2024-04-09T12:51:14Z")

</div>

@adamchainz @diachkow  
I found the issue in my code  
I forgot to include my custom db engine `mydbengine` inside my `default DATABASES`  
For reference [ara/ara/server/settings.py at master · ansible-community/ara · GitHub](https://github.com/ansible-community/ara/blob/master/ara/server/settings.py)

````auto
DATABASES = {
    'default': {
        'ENGINE': 'mydbengine',
    },
    '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',
    }
}```
````

---

<div class="post-metadata">

**Author:** ![adamchainz](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/adamchainz/32/26_2.png) [@adamchainz](https://forum.djangoproject.com/u/adamchainz)\
**Post date:** [April 9, 2024, 1:22pm UTC](https://forum.djangoproject.com/t/use-database-routers-to-pick-a-database-connection-for-transaction-api-by-default/29744/9 "2024-04-09T13:22:20Z")

</div>

> [@febin-micheal](#):
>
> 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.

Why doesn’t it work? I think more debugging/info is required. The `atomic()` should be routed to the `default` database, which then picks the correct connection parameters? You shouldn’t need a database router with that custom backend, as it acts like a router. Perhaps still using a router is causing double connections to your tenant DB.

As a last resort, you could also use [`patchy.patch()`](https://pypi.org/project/patchy/) to inject custom database-picking code you want into `transaction.atomic` (more accurately than with monkey-patching).

(Btw if you want more in-depth help, I’m available for consulting work.)

---

<div class="post-metadata">

**Author:** ![febin-micheal](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/febin-micheal/32/20376_2.png) [@febin-micheal](https://forum.djangoproject.com/u/febin-micheal)\
**Post date:** [April 9, 2024, 6:28pm UTC](https://forum.djangoproject.com/t/use-database-routers-to-pick-a-database-connection-for-transaction-api-by-default/29744/10 "2024-04-09T18:28:32Z")

</div>

Thanks for your reply

> [@adamchainz](#):
>
> Why doesn’t it work? I think more debugging/info is required. The `atomic()` should be routed to the `default` database, which then picks the correct connection parameters?

It worked. The root cause of this issue is mentioned below:

> [@febin-micheal](#):
>
> I forgot to include my custom db engine `mydbengine` inside my `default DATABASES`  
> For reference [ara/ara/server/settings.py at master · ansible-community/ara · GitHub](https://github.com/ansible-community/ara/blob/master/ara/server/settings.py)
> 
> ````auto
> DATABASES = {
> 'default': {
> 'ENGINE': 'mydbengine',
> },
> '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',
> }
> }```
> 
> ````

If the custom database backend is not specified it will default to the default database backend

> [@adamchainz](#):
>
> You shouldn’t need a database router with that custom backend, as it acts like a router. Perhaps still using a router is causing double connections to your tenant DB.

Thank you for this insight. So if we are using a custom database backend a database router is redundant got it. I removed the database router and it is working as expected.

> [@adamchainz](#):
>
> As a last resort, you could also use [`patchy.patch()`](https://pypi.org/project/patchy/) to inject custom database-picking code you want into `transaction.atomic` (more accurately than with monkey-patching).
> 
> (Btw if you want more in-depth help, I’m available for consulting work.)

Thank you for this valuable insight. This might be useful for us somewhere else.  
Once again Thank you for your valuable time. Hope this discussion thread will be beneficial for someone else.  
Have a great day  
Cheers,  
Febin