Django multiple and dynamic databases

Question

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?

Fer

Why do you HAVE to use multiple databases? Is there a reason why you can not adjust your app model(s) schema to hold the information for each customer? Having multiple databases for each customer is highly irregular. – Epigraphy

I am trying to determine the appropriate architecture at the moment. I don't HAVE to use multiple databases but it is one option to provide portable, online and offline provision of services. And to this end i'd like to find out if it's possible, it's fairly simple conceptually but i know in practice supporting this in a general framework could be tricky so i thought i'd see if anyone had done anything like this before. – Fer

possible duplicate of Django: dynamic database file (Note: the linked question seems to offer a proper solution, that does not require the workaround of restarting the server whenever a new database is added/changed/deleted) – Affranchise

@LylePratt you need new database per customer if you have multi tenancy – Acalia

Answer 1

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.

Bullnose

I've came with a different solution, although I've implemented your method (via files) and mine, using the database itself as the store for these files. The last line of settings.py loads all the models that contains database definitions and add them up to django's DATABASE setting. – Mendicant

You can make it more elegant than a text file, if you write the db connections in your main database (the one you manage users with). – Adnate

is this same procedure works for 'Throttle Limit' as well? – Cloth

Can you share how you achieved this? Can you share the script as an example in the answer GeorgeSilva and @Adnate I am trying this. – Humdrum

@Humdrum I meant that in the "main" database that stores your users (or projects, or whatever), you can add a column with the name of the "personal" database that should be used for that user. Then when the app executes for a user, it reads the db name from the "main" database and uses it to open a connection to the "personal" database. That makes it possible to switch databases at execution time by accessing your own (secure) API. – Adnate

Ok cool. What I am trying is somewhat similar based on above comments. What I am not understanding is here. The dynamic db is a separate db from the main database and accessed by a module. The main db is connected to a module called as mod_customerdb_records for db creation and crud operation in main db for customers db records. Now from mod_ecomm I access the dynamic db and the main db changing the db connection based on requirement for access of customers data. Is this a suggested architecture or is there a better decoupled architecture that is available? – Humdrum

Answer 2

To augment @thedawnrider's answer, in some cases editing settings.DATABASES may not be enough. It might be more reliable to edit django.db.connections.databases, which serves as a cache and wrapper around settings.DATABASES.

e.g.

from django.db import connections
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'] = ''
connections.databases[database_id] = newDatabase

Sherburn

Answer 3

This question is pretty outdated and should be updated.

Django support multiple databases out of the box

Here is many good tutorials [ 1, 2 ] and answers how to manage connections dynamically

Also you may simply use django-dynamic-db-router

Database connection settings may be configured on the fly by using configure method of settings object:

from django.conf import settings

dbs = settings.DATABASES.copy()
dbs['some_new_db'] = {'ENGINE': 'dummy'}

settings.configure(DATABASES=dbs)

Tentative

the question is about dynamic databases which can be added or removed at runtime not static defined databases – Searle

@MuSa No problem, just add DB creation logic inside db_for_read method of DB router and Django will create new database at every time when you read from DB. Same with db_for_write. I just try to say that all features exists out of the box. Other stuff depends on your imagination. – Tentative

db_for_read and db_for_write are methods for DB routing not for creating databases or databases connections, database connections are defined in the settings file (hard coded), and settings file will be loaded once, in your all links, the databases were defined in settings file, no link mentioned how to create new database connection at the runtime, and the new database connection should be somehow stored and reloaded each time the server restart. – Searle

@MuSa Yes, you right. After my comment I figured out what you mean and added info about configure. This method allow to add/edit new connections on the flight (for example if settings.py is updated). and the new database connection should be somehow stored → in my opinion best solution will be to create hard coded connection DB that store other connections. And proper logic inside db_for_read will allow to do that (store in DB if connection still not exists). It is more flexible and solid than create python-like file and manually escape all quote symbols, etc. – Tentative

Answer 4

I am checking and adding connection in Django middleware

if connections.databases.get(database) is None:
    logger.debug(f'Connection added! {host} {database} {username} {password} {port}')
    connections.databases[database] = {
      "ENGINE": "django.db.backends.postgresql_psycopg2",
      "HOST": host,
      "NAME": database,
      "USER": username,
      "PASSWORD": password,
      "PORT": port,
      "CONN_MAX_AGE" : 500
    }

Acalia