Multiple Database Switching

Using Django Using the ORM
1

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?

2

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.

3

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?

4

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.

5

I second Ken’s response, but I have some experience with having 2 databases in a production environment and I can tell you some potential pitfalls not already mentioned. We were using the second database to service data submission validation jobs. In order for the validation to proceed in stages, some data had to be in the database and since we didn’t want that data to get into production, we created a “validation database”. It was coded that way as a work-around to poor implementation of the loading code (the load scripts, in essentially dry run mode, are what we were using for validation). Dry run apparently had side-effects and thus was not reliable to not change the database. Ultimately, even though we got things to work, we refactored the loading code to use atomic transactions and eliminated the second database.

We had some property methods in a number of models that made fresh database queries, so those queries had to have .using() calls. And that meant that the property had to know the database currently in use elsewhere. For that, we had to use self._state.db. There were also some unexpected things deep in the django code that default to the default_db even though .using() was included. (I wish I could remember the specifics on that and what our solution was.) Also, we couldn’t call .full_clean() on non-default databases, so we had to skip full_clean when our validation database was selected. You also have to provide the using argument on calls to .save(). And we had lots and lots of tests to ensure things were operating on the correct database. The errors you get when it uses the wrong database can be very cryptic.