I recently found myself working on an internal site to aid developers when working across deployments of the same databases but in different environments. For example the same set of databases in a production, testing and development environment.

I wanted to give a user the ability to select which environment they wanted to work against and then be able to automatically allow for subtle differences in their table structures where new features may be present in one environment but not in another.

I decided that in order to achieve this, I had to firstly store configurations for database connections but in a dynamic manner where the configurations could be added and removed as needed.

So I went with a basic model to represent a database connection.

from __future__ import unicode_literals

from django.db import models
from jsonfield import JSONField

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

    def __unicode__(self):
        return self.name

Simple! We give each a unique name that would usually represent the key in the settings.DATABASES dictionary. The json is what would usually be the value. A dictionary to provide details of the database connection. Host, database name etc.

Once I had that, I then registered the Database model with the Admin.

from json import dumps

from django.contrib import admin
from dynamic_databases.models import Database

def config(obj):
    return dumps(obj.config)
config.short_description = 'Config'

class DatabaseAdmin(admin.ModelAdmin):
    list_display = ('name', config)

admin.site.register(Database, DatabaseAdmin)

Again, so far simple enough. We have a model and it's accessible through the admin to add, edit or remove dynamic database references.

Next up is the ability to register the databases with the running Django instance and to then access the tables of that database as models in Django.

Firstly I added a method to the Database class that I could call and pass a table name and expect to get back a fully functioning Model class.

So I added a method called get_model to the Database class and it took one parameter. The name of a table.

    def get_model(self, table_name):

The first thing that it did was call another new method that ensured that the database connection was registered with Django. There's no point in trying to get a model class if we can't connect to the database it resides on. We'll also require a dummy container app for each database connection. Model classes need to be registered with an app. So for us we'll have an app per dynamic database. So more than one database can have tables by the same name.

    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 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)

Now things are starting to take shape. We have the ability to register our database connection and also the app it needs to store the models in. Next we need to get the table structure and create a model that represents it. It'll then be placed in our dummy app. Lets got back and flesh out our get_model method

    def get_model(self, table_name):
        # Ensure the database connect and it's dummy app are registered
        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]:
            logger.info('Adding dynamic model: %s %s', label, table_name)

            # Use the "inspectdb" management command to get the structure of the table for us.
            file_obj = StringIO()
                database=label, table_name_filter=lambda t: t == table_name
            model_definition = file_obj.getvalue()

            # 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]
                logger.info('Could not find table: %s %s', label, table_name)
            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)

Just to keep everything above board, here are the imports you'll also need in your models.py

from __future__ import unicode_literals

from logging import getLogger
    from cStringIO import StringIO
except ImportError:
    from StringIO import StringIO

from django.core.management.commands.inspectdb import Command
from django.conf import settings
from django.db import models, connections
from django.apps import AppConfig
from django.apps.registry import apps

from jsonfield import JSONField

logger = getLogger('dynamic_databases.models')

Next we need a router in order to tell django what database connection to use for what model classes. I created a router.py file in my dynamic_databases app.

from django.conf import settings

class DynamicDatabasesRouter(object):
    # We need to identify our dynamic models
    # and point them in the right direction
    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

You can register your new router in your settings file with something like the following:

DATABASE_ROUTERS = ['dynamic_databases.router.DynamicDatabasesRouter']

At this stage you'd be good to go. Create a database instance and call get_model on it to get a model class that represents a table in your dynamic database connection.

There is one more thing we can allow for though. What about if a database instance is altered or deleted? Shouldn't we remove the connection, app and models? I think so.

For this we'll need to listen for some signals and act when we are invoked. I created a file called receivers.py in my app. It listened for any save or delete on a Database model instance. It looked like this:

from django.db.models.signals import pre_save, pre_delete
from django.dispatch import receiver

from dynamic_databases.models import Database

@receiver([pre_save, pre_delete], sender=Database)
def unregister_database(sender, **kwargs):
    if 'instance' in kwargs:

You'll notice that it's calling a method called unregister that we haven't written yet. So lets. You could probably look at the get_model and register methods and figure out what needs to be undone. Once you have it, you add the method to the Database model class.

    def unregister(self):
        label = self.label
        logger.info('Unregistering Database, app and all related models: "%s"', 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

No we need to make sure that our receiver is registered so it is invoked when the correct changes occur. We can put the import of the receiver in our dynamic_databases apps.py file.

from __future__ import unicode_literals

from django.apps import AppConfig

class DynamicDatabasesConfig(AppConfig):
    name = 'dynamic_databases'

    def ready(self):
        import dynamic_databases.receivers

We also need to register this class so that Django knows that it represents our dynamic_databses app. In the app's __init__.py file we add the following:

default_app_config = 'dynamic_databases.apps.DynamicDatabasesConfig'

So there you have it.

We can

  • Store dynamic database connections.
  • Add, update and delete through the admin interface.
  • Get model classes that represent the tables in the dynamic databases
  • We can update the dynamically registered classes as changes are made to our dynamic database model instances.
  • We can now try it out...

I created a test project, added the app and ran the migrations.

I then created a view to do something very very basic.

from django.views.generic import TemplateView

from dynamic_databases.models import Database

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

A simple home.html can go something like this:

        <table border="1">
                <th>Book Count</th>
{% for author in authors %}
                <td>{{ author.name }}</td>
                <td>{{ author.book_count }}</td>
{% endfor %}

It's important to recognize the limitations here. We haven't touched on the fact that there is no ContentType instance that corresponds to our model classes. So a lot of machinery won't work there. We could also create an instance of ContentType whenever we add a new model but I haven't gone that far yet. Let me know how you get on, if you give it a whirl.

Please feel free to ask questions or to tell me that this is flaky and will blow up in my face. It was fun digging into the code and getting it working all the same. Even if it's not recommended for production use because I've missed something fundamental.

So ultimately, use at your own risk.


comments powered by Disqus