[FIXED] Django multiple and dynamic databases

Issue

I've been evaluating django and wondered if the following is possible. I've already looked at the regular multiple database docs so please don't point me to that because this use case isn't mentioned as far as i can make out. If i'm wrong i take it back :)

I want one main database in which most of my app's models will reside, however one of the app's will need to dynamically create databases, these will be customer specific databases.

The database path (i plan to use sqlite) will be stored in primary database and so the cursor would need to be changed but the model will remain the same.

I would welcome any thoughts on ways to achieve this?

Solution

I will open with "You should not edit settings at runtime".

Having said that, I have exactly this same issue, where I want to create a unique database for each user. The reason for doing this is I am offering the ability for the user to save/access to/from a database not stored on my server, which entails having multiple databases, and thus one for each user.

This answer is NOT the recommended way to achieve the desired goal. I would love to hear from a django-guru how to best approach this problem. However, this is a solution I have been using and it has worked well so far. I am using sqlite however it can be easily modified for any of the databases.

In summary, this is the process:

1. Add the new database to settings (at runtime)
2. Create a file to store these settings for reloading when the server is restarted (at runtime)
3. Run a script which loads the saved settings files (whenever the server is restarted)

Now, how to achieve this:

1) Firstly, when a new user is created, I create a new database in the settings. This code lives in my view where new users are created.

from YOUR_PROJECT_NAME import settings
database_id = user.username #just something unique
newDatabase = {}
newDatabase["id"] = database_id
newDatabase['ENGINE'] = 'django.db.backends.sqlite3'
newDatabase['NAME'] = '/path/to/db_%s.sql' % database_id
newDatabase['USER'] = ''
newDatabase['PASSWORD'] = ''
newDatabase['HOST'] = ''
newDatabase['PORT'] = ''
settings.DATABASES[database_id] = newDatabase
save_db_settings_to_file(newDatabase) #this is for step 2)

This script loads the database settings 'at runtime' into the django project settings. However if the server is restarted, this database will no longer be in settings.

2) To facilitate reloading these settings automatically whenever the server is restarted, I create a file for each database which will be loaded whenever the server is started. Creating this file is performed by the function save_db_settings_to_file:

def save_db_settings_to_file(db_settings):
    path_to_store_settings = "/path/to/your/project/YOUR_PROJECT_NAME/database_settings/"
    newDbString = """
DATABASES['%(id)s'] = {
    'ENGINE': '%(ENGINE)s', # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
    'NAME': '%(NAME)s',                      # Or path to database file if using sqlite3.
    'USER': '',                      # Not used with sqlite3.
    'PASSWORD': '',                  # Not used with sqlite3.
    'HOST': '',                      # Set to empty string for localhost. Not used with sqlite3.
    'PORT': '',                      # Set to empty string for default. Not used with sqlite3.
}
    """ % db_settings
    file_to_store_settings = os.path.join(path_to_store_settings, db_settings['id'] + ".py")
    write_file(file_to_store_settings, newDbString) #psuedocode for compactness

3) To actually load these settings when the server is started, I add a single line to the very bottom of /path/to/your/project/YOUR_PROJECT_NAME/settings.py, which loads each file in the settings folder and runs it, having the effect of loading the database details into the settings.

import settings_manager

Then, import settings_manager will load the file at /path/to/your/project/YOUR_PROJECT_NAME/settings_manager.py, which contains the following code:

from settings import DATABASES
import os

path_to_store_settings = "/path/to/your/project/YOUR_PROJECT_NAME/database_settings/"
for fname in os.listdir(path_to_settings):
    full_path = os.path.join(path_to_settings, fname)
    f = open(full_path)
    content = f.read()
    f.close()
    exec(content) #you'd better be sure that the file doesn't contain anything malicious

Note that you could put this code directly at the bottom of settings.py instead of the import statement, but using the import statement keeps the abstraction level of settings.py consistent.

This is a convenient way to load each database setting because to remove a database from the settings all you have to do is delete the settings file, and the next time the server restarts it won't load those details into the settings, and the database will not be accessible.

As I said, this works and I have had success using it so far, but this is NOT the ideal solution. I would really appreciate if someone could post a better solution.

What's bad about it:

• It explicitly defies advice from django team not to modify settings at runtime. I do not know the reason for why this advice is given.
• It uses an exec statement to load the data into settings. This should be OK, but if you get some corrupt or malicious code in one of those files you will be a sad panda.

Note that I still use the default database for auth and sessions data, but all the data from my own apps is stored in the user-specific database.

Answered By - iancoleman