How to use django models with foreign keys in different DBs?

Question

I have 2 models for 2 different databases:
Databases were created manually but it should change nothing.

class LinkModel(models.Model): # in 'urls' database
    id = models.AutoField(primary_key=True)
    host_id = models.IntegerField()
    path = models.CharField(max_length=255)

    class Meta:
        db_table = 'links'
        app_label = 'testapp'

    def __unicode__(self):
        return self.path

class NewsModel(models.Model):  # in default database
    id = models.AutoField(primary_key=True)
    title = models.CharField(max_length=50)
    link = models.ForeignKey(LinkModel)

    class Meta:
        db_table = 'news'
        app_label = 'test'

    def __unicode__(self):
        return self.title

After the following code an error raises

newsItem, created = NewsModel.objects.get_or_create( title="test" )
link = LinkModel.objects.using('urls').get( id=1 )
newsItem.link = link  # error!

 Cannot assign "<LinkModel: />": instance is on database "default", value is on database "urls"

Why can't I use foreign key and a model for different database?

Rabbitry

Answer 1

Cross-database limitations

Django doesn't currently provide any support for foreign key or many-to-many relationships spanning multiple databases. If you have used a router to partition models to different databases, any foreign key and many-to-many relationships defined by those models must be internal to a single database.

Django - limitations-of-multiple-databases

Trouble

Same trouble. Bug in ForeignKey() class.

In validate() method.

See ticket

Bug exists in v1.2, v1.3, v1.4rc1

Solution

Try this patch to solve it.

Lamella

good, but they will not change this part of the code as I understood – Rabbitry

@Vitaly Fadeev, Thanks for this post! The link that you've posted as the solution suggests a single line change. I was just wondering how I specify which db each model belongs to once I've implemented this change? Is it with self.rel.to._meta.db_table? – Riparian

@vitaly I created a gist around your solution that incorporates this changes as a subclass of ForeignKey: gist.github.com/gcko/de1383080e9f8fb7d208 – Enclasp

This works at v1.7. When i upgrade to v1.10, things start to break down at ./manage.py migrate. The django is trying to run sql to add foreign key to the database, which is not allowed. – Dermott

Answer 2

The Problem

*Note: this is an extension of Vitaly Fadeev's answer

Due to a desire to keep referential integrity, Django does not allow for foreign keys which span multiple databases: https://docs.djangoproject.com/en/dev//topics/db/multi-db/#limitations-of-multiple-databases. Although this is desired in 99% of all applications, in some cases it is helpful to be able to create such an association in the ORM even if you cannot ensure referential integrity.

A Solution

I have created a Gist that uses the solution proposed here by Vitaly Fadeev wrapped as a subclass of the Django ForeignKey field. This solution does not require you to modify Django Core files but instead use this field type instead in the cases you need it.

Example Usage

# app1/models.py
from django.db import models

class ClientModel(models.Model)
    name = models.CharField()
    class Meta:
        app_label = 'app1'

# app2/models.py
from django.db import models
from some_location.related import SpanningForeignKey

class WidgetModel(models.Model):
    client = SpanningForeignKey('app1.ClientModel', default=None, null=True,
                                blank=True, verbose_name='Client')

The Gist

The gist is available here: https://gist.github.com/gcko/de1383080e9f8fb7d208

Copied here for ease of access:

from django.core import exceptions
from django.db.models.fields.related import ForeignKey
from django.db.utils import ConnectionHandler, ConnectionRouter

connections = ConnectionHandler()
router = ConnectionRouter()


class SpanningForeignKey(ForeignKey):

    def validate(self, value, model_instance):
        if self.rel.parent_link:
            return
        # Call the grandparent rather than the parent to skip validation
        super(ForeignKey, self).validate(value, model_instance)
        if value is None:
            return

        using = router.db_for_read(self.rel.to, instance=model_instance)
        qs = self.rel.to._default_manager.using(using).filter(
            **{self.rel.field_name: value}
        )
        qs = qs.complex_filter(self.get_limit_choices_to())
        if not qs.exists():
            raise exceptions.ValidationError(
                self.error_messages['invalid'],
                code='invalid',
                params={
                    'model': self.rel.to._meta.verbose_name, 'pk': value,
                    'field': self.rel.field_name, 'value': value,
                },  # 'pk' is included for backwards compatibility
            )

Enclasp

Could you provide more information about your gist? – Photodisintegration

@DustinHolden what else would you like to know? As I mentioned this is based off of Vitaly Fadeev's answer, where basically skipping Django's check for referential integrity. This means that a cascade delete would not capture references made by these foreign keys and could cause exceptions if not handled. In our case the foreign keys we relate to will never be deleted (and in fact will cause problems much earlier in the call chain than here) so it isn't an issue. does that help? – Enclasp

why is this answer down here? checked and worked for me – Swan

Not sure what version of Django this was written against, but in 3.x you'll get an error of 'SpanningForeignKey' object has no attribute 'rel'. Trying to go through previous releases to see what maybe we need to adjust to get it working. – Carrico

@MichaelThompson, I'm interested by the result of your investigations. I just came accross the same issue. – Skintight

@Skintight we've found that we were able to use default Django with custom router(s) to have things work. Here's a Gist showing three DBs with two of them sharing models: gist.github.com/merit-mthompson/… – Carrico

we're still leaning towards a modified ForeignKey, though, so we can side-step "validation" issues in things like admin – Carrico

@MichaelThompson, yeah, I've implemented something similar to your gist. Just works perfectly. – Skintight

How can I expand this for OneToOne relationships too? – Trocki

Answer 3

As an alternative (a bit hackish though), you could subclass ForeignKey to check for instance existance inside the right db : 

class CrossDbForeignKey(models.ForeignKey):
    def validate(self, value, model_instance):
        if self.rel.parent_link:
            return
        super(models.ForeignKey, self).validate(value, model_instance)
        if value is None:
            return

        # Here is the trick, get db relating to fk, not to root model
        using = router.db_for_read(self.rel.to, instance=model_instance)

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

then barely :

class NewsModel(models.Model):  # in default database
    …
    link = models.CrossDbForeignKey(LinkModel)

Note that it corresponds more or less to the patch that Vitaly mentions but that way does not require to patch django source-code.

Firstborn

Would this populate reverse relationships too so that you could double-underscore from the target table back to table with the foreign key? – Hirza

Answer 4

After breaking my head some days, I managed to get my Foreign Key ON THE SAME BANK!

Can be made ​​a change over the FORM to seek a FOREIGN KEY in a different bank!

First, add a RECHARGE of FIELDS, both directly (crack) my form, in function _init_

app.form.py

# -*- coding: utf-8 -*-
from django import forms
import datetime
from app_ti_helpdesk import models as mdp

#classe para formulario de Novo HelpDesk
class FormNewHelpDesk(forms.ModelForm):
    class Meta:
        model = mdp.TblHelpDesk
        fields = (
        "problema_alegado",
        "cod_direcionacao",
        "data_prevista",
        "hora_prevista",
        "atendimento_relacionado_a",
        "status",
        "cod_usuario",
        )

    def __init__(self, *args, **kwargs):
        #-------------------------------------
        #  using remove of kwargs
        #-------------------------------------
        db = kwargs.pop("using", None)

        # CASE use Unique Key`s
        self.Meta.model.db = db

        super(FormNewHelpDesk, self).__init__(*args,**kwargs)

        #-------------------------------------
        #   recreates the fields manually
        from copy import deepcopy
        self.fields = deepcopy( forms.fields_for_model( self.Meta.model, self.Meta.fields, using=db ) )
        #
        #-------------------------------------

        #### follows the standard template customization, if necessary

        self.fields['problema_alegado'].widget.attrs['rows'] = 3
        self.fields['problema_alegado'].widget.attrs['cols'] = 22
        self.fields['problema_alegado'].required = True
        self.fields['problema_alegado'].error_messages={'required': 'Necessário informar o motivo da solicitação de ajuda!'}


        self.fields['data_prevista'].widget.attrs['class'] = 'calendario'
        self.fields['data_prevista'].initial = (datetime.timedelta(4)+datetime.datetime.now().date()).strftime("%Y-%m-%d")

        self.fields['hora_prevista'].widget.attrs['class'] = 'hora'
        self.fields['hora_prevista'].initial =datetime.datetime.now().time().strftime("%H:%M")

        self.fields['status'].initial = '0'                 #aberto
        self.fields['status'].widget.attrs['disabled'] = True

        self.fields['atendimento_relacionado_a'].initial = '07'

        self.fields['cod_direcionacao'].required = True
        self.fields['cod_direcionacao'].label = "Direcionado a"
        self.fields['cod_direcionacao'].initial = '2'
        self.fields['cod_direcionacao'].error_messages={'required': 'Necessário informar para quem é direcionado a ajuda!'}

        self.fields['cod_usuario'].widget = forms.HiddenInput()

calling the Form from the View

app.view.py

form = forms.FormNewHelpDesk(request.POST or None, using=banco)

Now, the change in the source Code DJANGO

Only fields of type ForeignKey, ManyToManyField and OneToOneField can use the 'using', so added an IF ...

django.forms.models.py

# line - 133: add using=None
def fields_for_model(model, fields=None, exclude=None, widgets=None, formfield_callback=None, using=None):

# line - 159

if formfield_callback is None:
    #----------------------------------------------------
    from django.db.models.fields.related import (ForeignKey, ManyToManyField, OneToOneField)
    if type(f) in (ForeignKey, ManyToManyField, OneToOneField):
        kwargs['using'] = using
    formfield = f.formfield(**kwargs)
    #----------------------------------------------------
elif not callable(formfield_callback):
    raise TypeError('formfield_callback must be a function or callable')
else:
    formfield = formfield_callback(f, **kwargs)

ALTER FOLLOW FILE

django.db.models.base.py

alter

# line 717
qs = model_class._default_manager.filter(**lookup_kwargs)

for

# line 717
qs = model_class._default_manager.using(getattr(self, 'db', None)).filter(**lookup_kwargs)

Ready :D

Risible