How to use a different database per "application instance" in Django?

Question

The scenario

We have two applications.

TheApp

TheApp is an incredible app which customers love. Each customer gets his own instance of the application, which means each customer will use a different database (name,user,password). The database connection should be decided on the domain from which the request comes in.

req: customerA.foo.tld -> db:(app_cust1, cust1, hunter2)
req: customerB.foo.tld -> db:(app_cust2, cust2, hunter3)

Administration application

Should be able to create/delete TheApp instances for the customers. Therefore it has to setup the new database and write the config to somewhere. The way which decides which db is used for the incoming request should perform well and be easy manageable.

The Question

Which is the best way to decide which database connection should be used for an instance? What performs the best? What scales best?

Answers I came up with™

I read stuff and those are the ways I came up with:

(wsgi daemon + settings.py) per instance

Each customer will get his own settings.py with the database credentials. The settings may inherit some common stuff from a shared settings file.

For each new setting file a new wsgi instance of the application has to be started. This may scale badly if we have many customers? Also creating the apache vhost files is ugly.

Using 'using' & one settings.py

I could do it like

MyModel.objects.using(THE_CURRENT_DB).all()

and set THE_CURRENT_DB somewhere (middleware thingy?) per request. But it seems ugly to have to do this everywhere. Also the settings.py/app has to be rewritten everytime a customer gets his instance.

One settings.py + Application Router

I didn't yet have a look if I can access any information about the request in the router, but if so, I maybe could decide which of the dbs in settings.py should be used. Kind of like https://docs.djangoproject.com/en/1.3/topics/db/multi-db/#an-example but not per model but per request.

Modify settings in a middleware

Just had the idea that maybe the db setting could be altered in a middleware. Didn't yet have a look how middleware works in Django and what's possible there.

Some obscure other way

As I'm pretty new with Django I may have missed some points or some of them are just totally silly and bad. What would jes^wyou do?

Why not everything in one db?

Well. Because I think separation of stuff is good. And if bad things happen not everybody is suddenly affected.

Answer 1

This is easily done with middleware and Postgres namespaces. Here is a quick and dirty example with absolutely no error handling:

class NamespaceMiddleware:
    def process_request(self, request):

        # Get the subdomain. You could also use the domain name, but you'll have to remove special characters.
        host = request.get_host()
        parts = host.split('.')
        if len(parts) >= 3:
            subdomain = parts[0]

        # Set the namespace (aka "schema"). This will throw a DatabaseError if the namespace does not exist.
        from django.db import connection
        cursor = connection.cursor()
        cursor.execute("SET search_path TO ", subdomain)

With this middleware enabled, each customer can have completely separate data, and no mopery is required to make it work. There are a few things to know, though:

1. One problem is dealing with this in development. I usually add an if statement to ignore the above procedure if settings.DEBUG is True, but you could also set up virtual hosts and edit your hosts file to test this in development.
2. Another consideration is that you must run a separate virtual host for each instance. Otherwise you may run into problems where instance data can cross over. I assume this is some sort of threading issue, but someone smarter than I can probably explain that in more detail.
3. Finally, you need to think about how to deal with new installs and schema updates. Django will put everything in the public schema. You'll need to learn how to copy this schema to create a new one, and also get used to scripting database updates.

Answer 2

That is one of those scenarios that show the weakness of django configuration module (settings). There is no "django supported" way to do that.

I think you could choose to go with an option that has minimal impact to code maintenance and portability. So I suggest to:

• use an middleware to keep the users configuration in some data structure (django supported)
• make settings.DATABASE a callable to fetch users configuration from above (django hack)
• use django multiple database feature to access models (django supported)