Multiple Database Switching

Hi Everyone,

I’m trying to implement a way to dynamically switch databases using URL parameters. The purpose of this is to allow users to switch between a live database and test database by clicking a button which would submit a GET or POST request to the server and allow them to switch between both databases, I also tried setting this during login.

I’ve tried using django’s database routing that’s mentioned in the documentation as well as middleware which is mentioned in several stackoverflow threads.

login view:

User = get_user_model()

THREAD_LOCAL = threading.local()

def login_view(request):
    form = LoginForm(request.POST or None)
    btn = 'Login'
    heading = 'Sign in'

    if form.is_valid():
        username = form.cleaned_data.get('username')
        password = form.cleaned_data.get('password')
        user = authenticate(username=username, password=password, backend='django.contrib.auth.backends.ModelBackend')
        login(request, user)
        THREAD_LOCAL.TEST_DB = 0
        messages.success(request, 'Logged in')
        return HttpResponseRedirect('/')

Database Router:

THREAD_LOCAL = threading.local()

class DifferentDBRouter:

    def db_for_read(self, model, **hints):
        if THREAD_LOCAL.TEST_DB == 0:
            return "default"
        else:
            return "test"

    def db_for_write(self, model, **hints):
        if THREAD_LOCAL.TEST_DB == 0:
            return "default"
        else:
            return "test"

Settings:

DATABASE_ROUTERS = ['django_rest_api_tasks.db_router.DifferentDBRouter']

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
    },
    'test': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'test.sqlite3',
    }
}

Middleware:

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

THREAD_LOCAL = threading.local()

class DBRouterMiddleware(MiddlewareMixin):
    def process_view(self, request, view_func, view_args, view_kwargs):
        if request.user:
            THREAD_LOCAL.TEST_DB = request.session['TestDB']

I’ve also tried using threading.local() and the django-threadlocals library to pass the request to the database router since the database router doesn’t have access to requests, is it possible to pass requests to the database router and change the database connection from there?

It’s not clear to me from all the information you’ve posted here.

Is there a specific question you’re asking or issue with which you need assistance?

If the only question you’re asking is:

Then no, you can’t guarantee that you can provide a request to the router, because not all database queries are necessarily associated with a request.

We have an application that allows a person to select from a variety of databases - selecting the database is part of the view as the queries are being generated. The current-selected database is defined in a using function.

Hi,

I’d like to be able to switch between multiple databases dynamically through session variables, preferably without having to use a .using() function to specify it, as this would require having to use the .using() function with every single query.

Which is why I looked into maybe using database routing to avoid this. There’s a package called django-dynamic-db-router but it specifies using

with in_database(‘name’):

To select the database for all queries within the view, instead of having to rely on the .using() function. But this would also mean having to modify all my views.

If database routing doesn’t have access to requests, could something else be used instead to select the database based on session variables? Like middleware?

In a production environment when you have potentially multiple processes serving requests, this is exactly how you want to do this.

Which is effectively the same thing - it’s altering the queries being generated within the views.

You might be able to find a different way of doing this that works for your purposes. I’m not going to say that one doesn’t exist. I’ve just never found such a way that makes me comfortable that it’s going to work under all circumstances.

Just never lose sight of the fact that your production environment works in a fundamentally different way than your development environment using runserver (or Werkzeug). So if you do come up with something, you’re going to want to test it thoroughly.