The Django CompositeKey field. Get access to a legacy database without altering DB tables

I have discovered a truly remarkable solution for composite foreign keys support in Django ORM that works well when you can’t modify an existing database.

A composite foreign key is a foreign key that consists of two or more columns. Request to support for multiple-column primary keys has been open for over 15 years in the Django Tracker. There have been several attempts to implement this functionality. Nevertheless, the best thing you can do is modify an existing database table. You have to drop the old composite primary key and replace it with a new auto-increment primary key.

Database modification is not always possible. You could have a legacy system with countless amount of old code written around it. TimeScale DB, the PostgreSQL database extension, adds time-series data support but doesn’t allow having a single unique auto-incremented primary key column.

Fortunately, Django ORM is powerful enough to support virtual fields that are directly not mapped to any existing database table column. This opens the way for implementing a CompositeForeignKey ORM field, as a subclass of AutoField, without Django ORM core modifications.

The implementation is available in the next release of the Viewflow package and can be installed as:

pip install django-viewflow --pre

The value of the composite field is represented as JSON, the format that serializes back and forth smoothly, that auto-magically makes it compatible with Django Admin.

class Seat(models.Model):
    id = CompositeKey(columns=['aircraft_code', 'seat_no'])
    aircraft_code = models.ForeignKey(
        Aircraft, models.DO_NOTHING,
        db_column='aircraft_code'
    )
    seat_no = models.CharField(max_length=4)

In practice, tables with composite keys are references of other tables over ForeignKey with standard Django support, and formsets work out of the box

Standard Django Generic Views and packages like Django-filter also work also without any other modifications.

The opportunity to add support for Django Migrations is yet undiscovered. If possible, it would be added in future releases. But having no migration support is not a limitation in the case of a legacy DB.

Quick brief to get access to a legacy database from Django

1. Set up a new database connection. In the settings.py add the new section to the DATABASES dictionary. Substitute your DB engine, name, host, and credentials with the correct values

DATABASES = {
    'default': {
        ...  # default django database configuration
    },
   'legacy_db': {
       'ENGINE': 'django.db.backends.postgresql'
       'NAME': '__legacy_db_name__',
       'HOST': 'localhost',
       'USER': '__username__',
       'PASSWORD': '__password__',
   }
}

2. Setup database routing to ensure that appropriate database connection is used to perform queries.

settings.py:
DATABASE_ROUTERS = ['my_app.routers.Router', ]
my_app/routers.py
class Router:
    def db_for_read(self, model, **hints):
       if model._meta.app_label == 'my_app':
           return 'legacy_db'
    def db_for_write(self, model, **hints):
       if model._meta.app_label == 'my_app':
           return 'legacy_db'
    def db_for_write(self, model, **hints):
        if model._meta.app_label == 'device_ops':
            return 'devices'
    def allow_relation(self, obj1, obj2, **hints):
        if (obj1._meta.app_label == 'my_app' and       
            obj2._meta.app_label == 'my_app'):
                 return 'devices'
    def allow_migrate(self, db, app, model_name=None, **hints):  
        return False if app == 'my_app' else None

3. Create models.pyfile with database table definition.

python ./manage.py inspectdb --database=legacy_db

4. Modify generated code, add the field to models with Composite Foreign Key.

from viewflow.fields import CompositeKey
class MyModel(models.Model):
    id = CompositeKey(columns=['field1', 'field2'])
     ...

For the reference implementation, you could check legacy_db and timeseries_db samples form the Viewflow Cookbook repository:

viewflow/cookbook

Known limitations

As mentioned above, no migration support is available. No mass delete queries are supported. You can’t run Seat.objects.filter(...).delete() Django seems to have no way to override this functionality from the Field class alone.

The field itself supports only the lookup by exact value, but this could be extended with the custom lookup implementation. But since most of the analytical queries involve ForeignKey relationships only, the lightweight CompositeForeignKey field does not prevent you from successfully using a legacy database from Django ORM.