Different Databases With The Same Models On Django

I have the following issue: I need a different database with the same models for each user (or set of users). I have a way of finding out to which database the user is related. The

Solution 1:

Sounds like a bad design that can't scale to me. You have to duplicate the schema every time you add a user.

A better design would have a table USER with one-to-many and many-to-many relations with entities required by each user.

Solution 2:

We did it! Let me explain how.

We wrote a custom middleware and registered it as an middleware class inside our settings.py file.

MIDDLEWARE_CLASSES = (
    'django.middleware.common.CommonMiddleware',
    'django.contrib.sessions.middleware.SessionMiddleware',
    'django.middleware.csrf.CsrfViewMiddleware',
    'our.custom.middleware.Class',
    'django.contrib.auth.middleware.AuthenticationMiddleware',
    'django.contrib.messages.middleware.MessageMiddleware',
)

This middleware has a process_request method that creates a thread variable (from threading import local) to store the appropriate database name for the current user. Since every request is handled by a different thread, we know that our variable's value won't be accidently changed by another thread.

The next step was creating a Database Router and registering it as such.

DATABASE_ROUTERS = ('our.custom.database.Router',)

Attention: The default settings.py doesn't have a DATABASE_ROUTERS variable. You'll have to create it.

Our custom Router has the same implementations for db_for_read and db_for_write. The only thing these methods do is return the database name stored on our thread variable.

That's it. Now we don't have to call using every time we need to recover or save model objects.

Solution 3:

You can create a 2nd manager on your models:

classMyModelManager(models.Manager):defget_query_set(self):
        returnsuper(MyModelManager, self).get_query_set().using('appropriate_database')

classMyModel(models.Model):# "objects" is the default manager
    objects_db2 = MyModelManager()

    field1 = models.CharField(...)

classMyModel2(models.Model):
    objects_db2 = MyModelManager()
    ...

Then use queries MyModel.objects_db2.filter(...).order_by(...)...