django one app one model multiple databases

Question

I am new to django webapp development and i am stuck with a problem. I have created 1 app with 1 model to insert data to the database using 1 form. I am going to use multiple databases for this. Each database will have 1 table(for now) with same structure. Now my problem is:

How can I use just 1 model, 1 view and 1 form for multiple databases and their respective tables. Databases and tables should be switched on calling their respective urls.

e.g. http://www.example.com/x/abc/ will access first database and it's tables for all the operations.
http://www.example.com/y/abc/ will access second database

I have already tried the sample db routing provided in django documentation, but it doesn't help much. also I couldn't find a relative post/question which addresses this particular problem

I want to do this because later i will add more models and forms for accessing the data from the database tables and this seems the cleanest way to me

PS: I am using django 1.9.6

Rumania

and you have to have separate databases because...? Have you considered the alternative of using a url path segment as an identifier, and leveraging that identifier as a foreign key to filter data on? – Beebread

because the tables inside them will have the same structure but different data, the url right now is not a big problem. the problem is "1 model, 1 view and 1 form for multiple databases" to avoid a lot of duplicacy. – Rumania

What difference does it make if the data is different? It's all segregated by foreign key. The foreign key approach reduces your complexity by an order of magnitude. – Beebread

While I generally agree that your goal doesn't require multiple databases, I appreciate this question. In my case, I have 2 databases: default & validation. The purpose of the validation database is for users to validate their data submissions without their changes going into the default db. We could use DryRun exceptions for this, but the complex loading code (not written by me) apparently has side-effects. And methods used in the model classes make new queries. So we want every load run in validate mode to use the validation database, hence we need 1 model, 1 app, multiple databases. – Causalgia

Answer 1

Regardless of whether or not this is a good way to architect your application, you can tell Django which database to read and write with using:

Person.objects.using('db1').create(...)
Person.objects.using('db2').create(...)

So you don't need to use a router, simply define the two database in your settings and run migrate on both. Your model's table will be created in each database, and in your code you can read and write from the two database based on any logic you choose (e.g. based on the request path).

See https://docs.djangoproject.com/en/1.9/topics/db/multi-db/#manually-selecting-a-database

Iceskate

You'll also need to define all the databases in settings.py - at the moment you've probably only defined a default database – Ebeneser

thanks. I also read about this approach and will use it. But I was wondering whether or not there is another solution. Thank you for your help – Rumania

Answer 2

I am responding because of your "I was wondering whether or not there is another solution" comment. There is. I have it up and running on a website... multiple SQLite databases with one app. You also mentioned db router problems, which I struggled with too.

First, put anywhere a router.py file that contains the following:

class Router(object):

    appname = ''

    def db_for_read(self, model, **hints):
        """
        Attempts to read self.appname models go to model.db.
        """
        if model._meta.app_label == self.appname:
            return model.db
        return None

    def db_for_write(self, model, **hints):
        """
        Attempts to write self.appname models go to model.db.
        """
        if model._meta.app_label == self.appname:
            return model.db
        return None

    def allow_relation(self, obj1, obj2, **hints):
        """
        Allow relations if a model in the self.appname app is involved.
        """
        if obj1._meta.app_label == self.appname or \
           obj2._meta.app_label == self.appname:
           return True
        return None

    # This is possibly the new way, for beyond 1.8.
    ''' 
    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """
        Make sure the self.appname app only appears in the self.appname
        database.
        """
        if app_label == self.appname:
            return db == self.appname
        return None
    '''

    # Contrary to Djano docs this one works with 1.8, not the one above.
    def allow_migrate(self, db, model):
            """
            Make sure the self.appname app only appears in the self.appname
            database.
            """
            if db == self.appname:
                return model._meta.app_label == self.appname
            elif model._meta.app_label == self.appname:
                return False
            return None

I have tested this only with Django 1.8; as you are using 1.9 you will, according to the docs at least, have to use the other allow_migrate.

Note especially that: (1) there are no squirrely base classes to Router() with attributes, which means that the Python type function can be easily used to clone it; and, (2) evidently the current model instance is accessible inside Router() via an outer namespace.

So now, in your models.py, do this:

from django.db import models

import os
appname = os.path.dirname(__file__).split('/')[-1]
from dirwhereyouputtherouter.router import Router
router = type( appname, (Router,), dict(appname=appname) )

class Book(models.Model):

    # This is the default, for use when there is only one db per app.
    db = appname 

    # the various fields here, etc.

Do that for each model. And then the Router() when hit with any query will return model.db. I'm choosing here, in my scheme, to keep the default Django scheme... of the app getting it's name from the name of its directory.

Now, in settings.py, you need DATABASE_ROUTERS = [ 'appdir.models.router', ]. That directs the query through the router that we have initialized in models.py using the type() function. Note especially that I do not list the default database here in DATABASE_ROUTERS. I do have one, which is listed in the DATABASES setting with default as the key. When you do your initial migrations the various Django apps tables will end up in the default database, by default of course, as the Router() will step aside.

So inside your view you would start with Book.db = x, where the view signature would be myview(request, x, someothername):. There is a chance that you would want to drape the entire view body with a try: finally: where in the finally block you would return the choice of the database to some default.

I must confess that there may be a fly in the ointment when it comes to migrations. I hate those, and in my case I write over the entire contents of my little SQLite databases whenever I update them, which is frequently. So, not being concerned about saving data, I throw them and the Migrations folders away whenever I need to make changes. But if your circumstances are different you may have to struggle with the migrate and makemigrations commands. Each of my databases has the same schema, as defined by the model. So I actually created a blank database first by temporarily putting an entry in the DATABASES setting that had as key the name of the app. I used that to create one copy of the SQLite db file and then simply copied it and renamed it when I wanted to add a new database (adding the details for the new database to DATABASES and removing the temporary entry that had the appname as the key). Alternatively you may want to retain and make good use of the database whose key in DATABASES is the name of the app.

But sadly, we are not done. I had to fix admin.py. After creating in admin.py class BookAdmin(admin.ModelAdmin):, in the usual way, you won't find your two databases accessible in admin. So my admin.py looks like:

from django.contrib import admin
from django.conf import settings
import os

class BookAdmin(admin.ModelAdmin):

    list_display = ...
    list_filter = ...
    etc.

from models import Book


appname = os.path.dirname(__file__).split('/')[-1]

dbkeys = settings.DATABASES.keys()
while 'default' in dbkeys: dbkeys.remove('default')
dbkeys = [ k for k in dbkeys if  os.path.dirname(settings.DATABASES[k]['NAME']).split(os.sep)[-1] == appname ]

for dbname in dbkeys:

    if dbname == dbkeys[0]:

        class Book_(Book):
            class Meta:
                proxy = True
                verbose_name = Book.__name__ + '_' + ''.join('_' + x.capitalize() or '_' for x in dbname.split('_'))
                verbose_name_plural = Book.__name__ + 's_'+ ''.join('_' + x.capitalize() or '_' for x in dbname.split('_'))
                db_table = appname + '_book'
        Book_.db = dbname
        Book_.__name__ = Book.__name__ + '_' + appname.capitalize() + '_' + ''.join('_' + x.capitalize() or '_' for x in dbname.split('_'))
        admin.site.register(Book_, BookAdmin)


    elif dbname == dbkeys[1]:

        class Book__(Book):
            class Meta:
                proxy = True
                verbose_name = Book.__name__ + '_' + ''.join('_' + x.capitalize() or '_' for x in dbname.split('_'))
                verbose_name_plural = Book.__name__ + 's_'+ ''.join('_' + x.capitalize() or '_' for x in dbname.split('_'))
                db_table = appname + '_book'
        Book__.db = dbname
        Book__.__name__ = Book.__name__ + '_' + appname.capitalize() + '_' + ''.join('_' + x.capitalize() or '_' for x in dbname.split('_'))
        admin.site.register(Book__, BookAdmin)

This works because I put the db files for each app in that app's folder. Sorry that it's all a bit tricky. I had good reasons for wanting the capability. See also my unanswered question about sub-classing models to register them using admin.site.register, here.

Boondocks