How to work around lack of support for foreign keys across databases in Django

django django-models django-orm
saltycrane picture saltycrane · Mar 31, 2011 · Viewed 7.3k times · Source

I know Django does not support foreign keys across multiple databases (originally Django 1.3 docs)

But I'm looking for a workaround.

What doesn't work

I have two models each on a separate database.

routers.py:

class NewsRouter(object):
    def db_for_read(self, model, **hints):
        if model._meta.app_label == 'news_app':
            return 'news_db'
        return None

    def db_for_write(self, model, **hints):
        if model._meta.app_label == 'news_app':
            return 'news_db'
        return None

    def allow_relation(self, obj1, obj2, **hints):
        if obj1._meta.app_label == 'news_app' or obj2._meta.app_label == 'news_app':
            return True
        return None

    def allow_syncdb(self, db, model):
        if db == 'news_db':
            return model._meta.app_label == 'news_app'
        elif model._meta.app_label == 'news_app':
            return False
        return None

Model 1 in fruit_app/models.py:

from django.db import models

class Fruit(models.Model):
    name = models.CharField(max_length=20)

Model 2 in news_app/models.py:

from django.db import models

class Article(models.Model):
    fruit = models.ForeignKey('fruit_app.Fruit')
    intro = models.TextField()

Trying to add a "Article" in the admin gives the following error because it is looking for the Fruit model on the wrong database ('news_db'):

DatabaseError at /admin/news_app/article/add/

(1146, "Table 'fkad_news.fruit_app_fruit' doesn't exist")

Method 1: subclass IntegerField

I created a custom field, ForeignKeyAcrossDb, which is a subclass of IntegerField. Code is on github at: https://github.com/saltycrane/django-foreign-key-across-db-testproject/tree/integerfield_subclass

fields.py:

from django.db import models


class ForeignKeyAcrossDb(models.IntegerField):
    '''
    Exists because foreign keys do not work across databases
    '''
    def __init__(self, model_on_other_db, **kwargs):
        self.model_on_other_db = model_on_other_db
        super(ForeignKeyAcrossDb, self).__init__(**kwargs)

    def to_python(self, value):
        # TODO: this db lookup is duplicated in get_prep_lookup()
        if isinstance(value, self.model_on_other_db):
            return value
        else:
            return self.model_on_other_db._default_manager.get(pk=value)

    def get_prep_value(self, value):
        if isinstance(value, self.model_on_other_db):
            value = value.pk
        return super(ForeignKeyAcrossDb, self).get_prep_value(value)

    def get_prep_lookup(self, lookup_type, value):
        # TODO: this db lookup is duplicated in to_python()
        if not isinstance(value, self.model_on_other_db):
            value = self.model_on_other_db._default_manager.get(pk=value)

        return super(ForeignKeyAcrossDb, self).get_prep_lookup(lookup_type, value)

And I changed my Article model to be:

class Article(models.Model):
    fruit = ForeignKeyAcrossDb(Fruit)
    intro = models.TextField()

The problem is, sometimes when I access Article.fruit, it is an integer, and sometimes it is the Fruit object. I want it to always be a Fruit object. What do I need to do to make accessing Article.fruit always return a Fruit object?

As a workaround for my workaround, I added a fruit_obj property, but I would like to eliminate this if possible:

class Article(models.Model):
    fruit = ForeignKeyAcrossDb(Fruit)
    intro = models.TextField()

    # TODO: shouldn't need fruit_obj if ForeignKeyAcrossDb field worked properly
    @property
    def fruit_obj(self):
        if not hasattr(self, '_fruit_obj'):
            # TODO: why is it sometimes an int and sometimes a Fruit object?
            if isinstance(self.fruit, int) or isinstance(self.fruit, long):
                print 'self.fruit IS a number'
                self._fruit_obj = Fruit.objects.get(pk=self.fruit)
            else:
                print 'self.fruit IS NOT a number'
                self._fruit_obj = self.fruit
        return self._fruit_obj

    def fruit_name(self):
        return self.fruit_obj.name

Method 2: subclass ForeignKey field

As a second attempt, I tried subclassing the ForeignKey field. I modified ReverseSingleRelatedObjectDescriptor to use the database specified by forced_using on the model manager of Fruit. I also removed the validate() method on the ForeignKey subclass. This method did not have the same problem as method 1. Code on github at: https://github.com/saltycrane/django-foreign-key-across-db-testproject/tree/foreignkey_subclass

fields.py:

from django.db import models
from django.db import router
from django.db.models.query import QuerySet


class ReverseSingleRelatedObjectDescriptor(object):
    # This class provides the functionality that makes the related-object
    # managers available as attributes on a model class, for fields that have
    # a single "remote" value, on the class that defines the related field.
    # In the example "choice.poll", the poll attribute is a
    # ReverseSingleRelatedObjectDescriptor instance.
    def __init__(self, field_with_rel):
        self.field = field_with_rel

    def __get__(self, instance, instance_type=None):
        if instance is None:
            return self

        cache_name = self.field.get_cache_name()
        try:
            return getattr(instance, cache_name)
        except AttributeError:
            val = getattr(instance, self.field.attname)
            if val is None:
                # If NULL is an allowed value, return it.
                if self.field.null:
                    return None
                raise self.field.rel.to.DoesNotExist
            other_field = self.field.rel.get_related_field()
            if other_field.rel:
                params = {'%s__pk' % self.field.rel.field_name: val}
            else:
                params = {'%s__exact' % self.field.rel.field_name: val}

            # If the related manager indicates that it should be used for
            # related fields, respect that.
            rel_mgr = self.field.rel.to._default_manager
            db = router.db_for_read(self.field.rel.to, instance=instance)
            if getattr(rel_mgr, 'forced_using', False):
                db = rel_mgr.forced_using
                rel_obj = rel_mgr.using(db).get(**params)
            elif getattr(rel_mgr, 'use_for_related_fields', False):
                rel_obj = rel_mgr.using(db).get(**params)
            else:
                rel_obj = QuerySet(self.field.rel.to).using(db).get(**params)
            setattr(instance, cache_name, rel_obj)
            return rel_obj

    def __set__(self, instance, value):
        raise NotImplementedError()

class ForeignKeyAcrossDb(models.ForeignKey):

    def contribute_to_class(self, cls, name):
        models.ForeignKey.contribute_to_class(self, cls, name)
        setattr(cls, self.name, ReverseSingleRelatedObjectDescriptor(self))
        if isinstance(self.rel.to, basestring):
            target = self.rel.to
        else:
            target = self.rel.to._meta.db_table
        cls._meta.duplicate_targets[self.column] = (target, "o2m")

    def validate(self, value, model_instance):
        pass

fruit_app/models.py:

from django.db import models


class FruitManager(models.Manager):
    forced_using = 'default'


class Fruit(models.Model):
    name = models.CharField(max_length=20)

    objects = FruitManager()

news_app/models.py:

from django.db import models

from foreign_key_across_db_testproject.fields import ForeignKeyAcrossDb
from foreign_key_across_db_testproject.fruit_app.models import Fruit


class Article(models.Model):
    fruit = ForeignKeyAcrossDb(Fruit)
    intro = models.TextField()

    def fruit_name(self):
        return self.fruit.name

Method 2a: Add a router for fruit_app

This solution uses an additional router for fruit_app. This solution does not require the modifications to ForeignKey that were required in Method 2. After looking at Django's default routing behavior in django.db.utils.ConnectionRouter, we found that even though we expected fruit_app to be on the 'default' database by default, the instance hint passed to db_for_read for foreign key lookups put it on the 'news_db' database. We added a second router to ensure fruit_app models were always read from the 'default' database. A ForeignKey subclass is only used to "fix" the ForeignKey.validate() method. (If Django wanted to support foreign keys across databases, I would say this is a Django bug.) Code is on github at: https://github.com/saltycrane/django-foreign-key-across-db-testproject

routers.py:

class NewsRouter(object):
    def db_for_read(self, model, **hints):
        if model._meta.app_label == 'news_app':
            return 'news_db'
        return None

    def db_for_write(self, model, **hints):
        if model._meta.app_label == 'news_app':
            return 'news_db'
        return None

    def allow_relation(self, obj1, obj2, **hints):
        if obj1._meta.app_label == 'news_app' or obj2._meta.app_label == 'news_app':
            return True
        return None

    def allow_syncdb(self, db, model):
        if db == 'news_db':
            return model._meta.app_label == 'news_app'
        elif model._meta.app_label == 'news_app':
            return False
        return None


class FruitRouter(object):
    def db_for_read(self, model, **hints):
        if model._meta.app_label == 'fruit_app':
            return 'default'
        return None

    def db_for_write(self, model, **hints):
        if model._meta.app_label == 'fruit_app':
            return 'default'
        return None

    def allow_relation(self, obj1, obj2, **hints):
        if obj1._meta.app_label == 'fruit_app' or obj2._meta.app_label == 'fruit_app':
            return True
        return None

    def allow_syncdb(self, db, model):
        if db == 'default':
            return model._meta.app_label == 'fruit_app'
        elif model._meta.app_label == 'fruit_app':
            return False
        return None

fruit_app/models.py:

from django.db import models


class Fruit(models.Model):
    name = models.CharField(max_length=20)

news_app/models.py:

from django.db import models

from foreign_key_across_db_testproject.fields import ForeignKeyAcrossDb
from foreign_key_across_db_testproject.fruit_app.models import Fruit


class Article(models.Model):
    fruit = ForeignKeyAcrossDb(Fruit)
    intro = models.TextField()

    def fruit_name(self):
        return self.fruit.name

fields.py:

from django.core import exceptions
from django.db import models
from django.db import router


class ForeignKeyAcrossDb(models.ForeignKey):

    def validate(self, value, model_instance):
        if self.rel.parent_link:
            return
        models.Field.validate(self, value, model_instance)
        if value is None:
            return

        using = router.db_for_read(self.rel.to, instance=model_instance)  # is this more correct than Django's 1.2.5 version?
        qs = self.rel.to._default_manager.using(using).filter(
                **{self.rel.field_name: value}
             )
        qs = qs.complex_filter(self.rel.limit_choices_to)
        if not qs.exists():
            raise exceptions.ValidationError(self.error_messages['invalid'] % {
                'model': self.rel.to._meta.verbose_name, 'pk': value})

Additional information

Update

We implemented the last method after tweaking our routers some more. The whole implementation has been pretty painful which makes us think that we must be doing it wrong. On the TODO list is writing unit tests for this.

Answer

adorablepuppy picture adorablepuppy · Apr 11, 2011

You could make a view in the database that has the cross database query in it, then define the model for the view in a separate file to keep syncdb working.

Happy programming. :)

Related questions

Django values_list vs values

In Django, what's the difference between the following two: Article.objects.values_list('comment_id', flat=True).distinct() vs Article.objects.values('comment_id').distinct() My goal is to get a list of unique comment ids under each Article. I've …

Read More
What's the difference between select_related and prefetch_related in Django ORM?

In Django doc, select_related() "follows" foreign-key relationships, selecting additional related-object data when it executes its query. prefetch_related() does a separate lookup for each relationship, and does the "joining" in Python. What does it mean by "doing the joining …

Read More
Django database query: How to get object by id?

Django automatically creates an id field as primary key. Now I need to get the object by this id. object = Class.objects.filter() How to write this filter?

Read More