Database Routing by URL parameter

Question

I'm developing on a Django Project with several Databases. In an App I need to switch the database connectivity from a Development-Database to Test-DB or Production-DB, based on a request from the user. (DB Architecture is set and not changeable!)

I Tried my luck also with this old Guide here Does not work. Within the DB Router I have no access to the threading.locals.

I tried it also to set up a custom db router. Over a session variable, I tried to set the Connection string. To read the users Session in the dbRouter, I need the exact Session key, or I have to loop throw all Sessions.

The way over object.using('DB_CONNECTION) is a not acceptable solution… To many dependencies. I want to set a connection globally for a logged in User without giving the DB connection to each models function….

Please give me some imput how to solve this issue.

I should be able to return the dbConnection in a db Router based on a session value...

def db_for_read|write|*():
   from django.contrib.sessions.backends.db import SessionStore
   session = SessionStore(session_key='WhyINeedHereAKey_SessionKeyCouldBeUserId')
   return session['app.dbConnection']

Update1: Thank you @victorT for your imputs. I just tried it with the given examples. Still not reached the target...

Here what i tried. Mabe you'll see a config error.

Django Version:     2.1.4
Python Version:     3.6.3
Exception Value:    (1146, "Table 'app.myModel' doesn't exist")

.app/views/myView.py

from ..models import myModel
from ..thread_local import thread_local

class myView:
    @thread_local(DB_FOR_READ_OVERRIDE='MY_DATABASE')
    def get_queryset(self, *args, **kwargs):
        return myModel.objects.get_queryset()

.app/myRouter.py

from .thread_local import get_thread_local

class myRouter:
    def db_for_read(self, model, **hints):
        myDbCon = get_thread_local('DB_FOR_READ_OVERRIDE', 'default')
        print('Returning myDbCon:', myDbCon)
        return myDbCon

.app/thread_local.py

import threading
from functools import wraps


threadlocal = threading.local()


class thread_local(object):
    def __init__(self, **kwargs):
        self.options = kwargs

    def __enter__(self):
        for attr, value in self.options.items():
            print(attr, value)
            setattr(threadlocal, attr, value)

    def __exit__(self, exc_type, exc_value, traceback):
        for attr in self.options.keys():
            setattr(threadlocal, attr, None)

    def __call__(self, test_func):

        @wraps(test_func)
        def inner(*args, **kwargs):
            # the thread_local class is also a context manager
            # which means it will call __enter__ and __exit__
            with self:
                return test_func(*args, **kwargs)

        return inner

def get_thread_local(attr, default=None):
    """ use this method from lower in the stack to get the value """
    return getattr(threadlocal, attr, default)

This is the output:

Returning myDbCon: default              
DEBUG (0.000) None; args=None
DEBUG (0.000) None; args=None
DEBUG (0.000) None; args=('2019-05-14 06:13:39.477467', '4agimu6ctbwgykvu31tmdvuzr5u94tgk')
DEBUG (0.001) None; args=(1,)
DB_FOR_READ_OVERRIDE MY_DATABASE        # The local_router seems to get the given db Name,
Returning myDbCon: None                 # But disapears in the Router
DEBUG (0.000) None; args=()
Returning myDbCon: None
DEBUG (0.001) None; args=()
Returning myDbCon: None
DEBUG (0.001) None; args=()
Returning myDbCon: None
DEBUG (0.001) None; args=()
Returning myDbCon: None
DEBUG (0.001) None; args=()
Returning myDbCon: None
DEBUG (0.002) None; args=()
ERROR Internal Server Error: /app/env/list/    # It switches back to the default
Traceback (most recent call last):
  File "/.../lib64/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/.../lib64/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/.../lib64/python3.6/site-packages/MySQLdb/cursors.py", line 255, in execute
    self.errorhandler(self, exc, value)
  File "/.../lib64/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/.../lib64/python3.6/site-packages/MySQLdb/cursors.py", line 252, in execute
    res = self._query(query)
  File "/.../lib64/python3.6/site-packages/MySQLdb/cursors.py", line 378, in _query
    db.query(q)
  File "/.../lib64/python3.6/site-packages/MySQLdb/connections.py", line 280, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.ProgrammingError: (1146, "Table 'app.myModel' doesn't exist")

The above exception was the direct cause of the following exception:

Update2: This is the try with the usage of sessions.

I store the db connection trough a middleware in the session. In the Router I want to access then the session that is requesting. My expectation would be, that Django handels this and knows the requester. But I have to give in the Session key as

 s = SessionStore(session_key='???')

that i dont get to the router...

.middleware.py

from django.contrib.sessions.backends.file import SessionStore

class myMiddleware:

    def  process_view(self, request, view_func, view_args, view_kwargs):
        s = SessionStore()
        s['app.dbConnection'] = view_kwargs['MY_DATABASE']
        s.create()

.myRouter.py

class myRouter:
    def db_for_read(self, model, **hints):
        from django.contrib.sessions.backends.file import SessionStore
        s = SessionStore(session_key='???')
        return s['app.dbConnection']

This results in the same as the threading.local... an emtpy value :-(

Here is half of the answer https://chase-seibert.github.io/blog/2012/06/15/django-ditch-objectsusing-in-favor-of-a-per-view-decorator-to-switch-databases.html# — Victor T, May 13 '19 at 13:11
Could you elaborate on the kind of errors you get? What if you ignore the package and use directly the [relevant file](https://github.com/ambitioninc/django-dynamic-db-router/blob/master/dynamic_db_router/router.py) in your code ? — Victor T, May 13 '19 at 14:39
Does `myModel.objects.using('MY_DATABASE').all()` work at least in the django shell (`python manage.py shell` then `from app.models import myModel`) ? With and without myRouter.py. Sounds like you have a database settings issue. Or maybe you are missing some migrations if django is managing the db, if not you should have ` class Meta: managed = False` (https://docs.djangoproject.com/en/2.2/ref/models/options/#managed) — Victor T, May 14 '19 at 08:22
Here is something close to what you are trying to do https://stackoverflow.com/questions/16215122/url-and-database-routing-based-on-language , as long as you add the database name to the request within the middleware. Plus what you are trying to do sounds like it's more of a middleware task than a view task. — Victor T, May 14 '19 at 08:40
Yes the .using() works, but the using will not reach the models if I have logic in them (Post save processing etc.). -- I Agree with you that this is a middleware task. I tried also this approach... it has the same effect in the middleware as I do it in the View, the values set to the threading.local are not accessible in the custom router :-( — Alessandro Nughes, May 14 '19 at 09:50
I just found [https://pypi.org/project/django-threadlocals/](this) package... seems promising... could works with Django2... Im going to try this out later and post the results :-) — Alessandro Nughes, May 14 '19 at 10:47

score 0 · Answer 1 · answered May 13 '19 at 13:33

Use threading.local to set variables while responding to a request (doc). The custom router is the way to go.

Sources:

Taken from django-dynamic-db-router:

from dynamic_db_router import in_database

with in_database('non-default-db'):
    result = run_complex_query()

Note that the project is for Django 1.11 max, and may have compatibility issues. Nonetheless, the router class and decorator described in both sources are quite simple.

In your case, set the variable per-user. As a reminder, you'll find the user with request.user.

We are on Django 2.1 :-( tried also the dynamic-db-router, without any luck ... But, mabe i'll give it another try. — Alessandro Nughes, May 13 '19 at 14:26

score 0 · Accepted Answer · answered May 16 '19 at 13:59

At least I had time to test the threadlocals package and it works with Django 2.1 and Python 3.6.3.

.app/middleware.py

from threadlocals.threadlocals import set_request_variable

try:
    from django.utils.deprecation import MiddlewareMixin
except ImportError:
    MiddlewareMixin = object

class MyMiddleware(MiddlewareMixin):
    def  process_view(self, request, view_func, view_args, view_kwargs):
        set_request_variable('dbConnection', view_kwargs['environment'])

...

.app/router.py

from threadlocals.threadlocals import get_request_variable

class MyRouter:
    def db_for_read(self, model, **hints):
        if model._meta.app_label == 'app':
            return get_request_variable("dbConnection")
        return None
...

Database Routing by URL parameter

2 Answers2