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

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 - Thread on the django-users list which has a lot of information: http://groups.google.com/group/django-users/browse_thread/thread/74bcd1afdeb2f0/0fdfce061124b915 - Revision history for the multi-db documentation: http://code.djangoproject.com/log/django/trunk/docs/topics/db/multi-db.txt?verbose=on 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.