Django multiple dynamic databases after server startup

Question

I'm new to django and try to figure out what would be the best solution for me to use dynamically multiple databases in django. I know django is able to work with multiple databases registered in the settings.py file but In my case I have one main database (sqlite) which acts as my repository, I created all the models, the rest api viewsets for this one.

The user can choose to connect to an Oracle database by entering the connection information and then I will need to gather data from this database and insert it to my repository. The user can register multiple Oracle databases through the app. I'm wondering if I should use a pure cx_Oracle class to handle those connection from django or if I should register them in the settings.py?

Each view in the frontend maps to a specific database and I will need to switch context between them, If I use a cx_Oracle class how can I route the requests to the right class instance in the backend?

Any help or insight would be appreciated, I didn't find anything matching my use case on the internet.

Answer 1

As found out in the comments - here is an article describing how to set up a server instance with selecting databases on the fly, so its author is the one that should get all credits. Restating the basic approach:

1. Create a model class representing the db connection:

   class Database(models.Model):
       name = models.CharField(max_length=256, unique=True)
       config = JSONField()
   

2. Add a label property to distinct the db connection entities. Here, it requires to set strings DYNAMIC_DATABASES_PREFIX and DYNAMIC_DATABASES_SEPARATOR in the django settings, but could also be hardcoded as some constants:

   class Database(models.Model):
       ...
       @property
       def label(self):
           # We want to be able to identify the dynamic databases and apps
           # So we prepend their names with a common string
           prefix = getattr(settings, 'DYNAMIC_DATABASES_PREFIX', 'DYNAMIC_DATABASE')
           separator = getattr(settings, 'DYNAMIC_DATABASES_SEPARATOR', '_')
           return '{}{}{}'.format(prefix, separator, self.pk)
   

3. Add a method for adding the db connection to/removing the db connection from django's db connections (the nifty part is putting a dummy app for each db connection - this way we can have different databases with duplicate table names):

   class Database(models.Model):
       ...
       def register(self):
           # label for the database connection and dummy app
           label = self.label
           # Do we have this database registered yet
           if label not in connections._databases:
               # Register the database
               connections._databases[label] = self.config
               # Break the cached version of the database dict so it'll find our new database
               del connections.databases
           # Have we registered our fake app that'll hold the models for this database
           if label not in apps.app_configs:
               # We create our own AppConfig class, because the Django one needs a path to the module that is the app.
               # Our dummy app obviously doesn't have a path
               AppConfig2 = type('AppConfig'.encode('utf8'),(AppConfig,),
                                 {'path': '/tmp/{}'.format(label)})
               app_config = AppConfig2(label, label)
               # Manually register the app with the running Django instance
               apps.app_configs[label] = app_config
               apps.app_configs[label].models = {}
   
       def unregister(self):
           label = self.label
           if label in apps.app_configs:
               del apps.app_configs[label]
           if label in apps.all_models:
               del apps.all_models[label]
           if label in connections._databases:
               del connections._databases[label]
               del connections.databases
   

4. Add a connection lookup by connection name that also registers the connection to running django instance, making it operational:

   class Database(models.Model):
       ...
       def get_model(self, table_name):
           # Ensure the database connect and it's dummy app are registered
           self.register()
           label = self.label
           model_name = table_name.lower().replace('_', '')
   
           # Is the model already registered with the dummy app?
           if model_name not in apps.all_models[label]:
               # Use the "inspectdb" management command to get the structure of the table for us.
               file_obj = StringIO()
               Command(stdout=file_obj).handle(database=label, table_name_filter=lambda t: t == table_name)
               model_definition = file_obj.getvalue()
               file_obj.close()
   
               # Make sure that we found the table and have a model definition
               loc = model_definition.find('(models.Model):')
               if loc != -1:
                   # Ensure that the Model has a primary key.
                   # Django doesn't support multiple column primary keys,
                   # So we have to add a primary key if the inspect command didn't
                   if model_definition.find('primary_key', loc) == -1:
                       loc = model_definition.find('(', loc + 14)
                       model_definition = '{}primary_key=True, {}'.format(model_definition[:loc + 1], model_definition[loc + 1:])
                   # Ensure that the model specifies what app_label it belongs to
                   loc = model_definition.find('db_table = \'{}\''.format(table_name))
                   if loc != -1:
                       model_definition = '{}app_label = \'{}\'\n        {}'.format(model_definition[:loc], label, model_definition[loc:])
   
                   # Register the model with Django. Sad day when we use 'exec'
                   exec(model_definition, globals(), locals())
                   # Update the list of models that the app
                   # has to match what Django now has for this app
                   apps.app_configs[label].models = apps.all_models[label]
               else:
                   logger.info('Could not find table: %s %s', label, table_name)
           else:
               logger.info('Already added dynamic model: %s %s', label, table_name)
   
           # If we have the connection, app and model. Return the model class
           if (label in connections._databases and label in apps.all_models and model_name in apps.all_models[label]):
               return apps.get_model(label, model_name)
   

5. Create custom db routing, using the mentioned config strings for db selection:

   class DynamicDatabasesRouter(object):
       label_prefix = '{}{}'.format(
           getattr(settings, 'DYNAMIC_DATABASES_PREFIX', 'DYNAMIC_DATABASE'),
           getattr(settings, 'DYNAMIC_DATABASES_SEPARATOR', '_')
       )
   
       def db_for_read(self, model, **hints):
           if model._meta.app_label.startswith(self.label_prefix):
               # We know that our app_label matches the database connection's name
               return model._meta.app_label
           return None
   
       def db_for_write(self, model, **hints):
           if model._meta.app_label.startswith(self.label_prefix):
               # We know that our app_label matches the database connection's name
               return model._meta.app_label
           return None
   
       def allow_relation(self, obj1, obj2, **hints):
           return None
   
       def allow_migrate(self, db, app_label, model_name=None, **hints):
           return None
   

6. Register the router in settings:

   DATABASE_ROUTERS = ['myapp.routing.DynamicDatabasesRouter']
   

7. (Optional) make the model modifiable in the admin site if you use it:

   def config(conn):
       return json.dumps(conn.config)
   config.short_description = 'Config'
   
   class DatabaseAdmin(admin.ModelAdmin):
       list_display = ('name', config)
   
   admin.site.register(Database, DatabaseAdmin)
   

An example usage in a view:

class HomeView(TemplateView):
    template_name = 'home.html'

    def get_context_data(self):
        context = super(HomeView, self).get_context_data()

        # We can pick which dynamic database connection we want based on a GET parameter
        db = Database.objects.get(pk=self.request.GET.get('env', 1))
        # Pass the database instance to the template so we can display it.
        context['db'] = db

        # Get a model class for a table in our dynamic database.
        # Lets pretend there's a table called 'author'
        Author = db.get_model('author')
        authors = Author.objects.all().order_by('name')
        # Send the author instances to the template for iterating over.
        context['authors'] = authors

        return context