Skip to content

Instantly share code, notes, and snippets.

@gcko gcko/related.py
Last active Jan 28, 2020

Embed
What would you like to do?
Django Custom Model ForeignKey Field for Spanning Databases
@gcko

This comment has been minimized.

Copy link
Owner Author

gcko commented Aug 18, 2015

The Problem

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

This Gist 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')

Other Resources

@yomguy

This comment has been minimized.

Copy link

yomguy commented Feb 1, 2016

Nice solution thanks. Is there any special routing / migration configuration with this because I cannot manage to get the migration rules to apply? I've got 2 DBs and then 2 routers (one for a Mariadb DB and another for a MySQL DB)
http://paste.debian.net/377982/

@DustinHolden

This comment has been minimized.

Copy link

DustinHolden commented Feb 8, 2016

Trying to use your gist, and I am having trouble running the migrations. I get the same error as yomguy above. "Relation does not exist."

I have a custom router file for each database.

@tisdall

This comment has been minimized.

Copy link

tisdall commented Sep 2, 2016

Is the call to ConnectionHandler necessary? The result doesn't seem to get used anywhere.

@knsathya

This comment has been minimized.

Copy link

knsathya commented Sep 2, 2016

I am getting ConnectionDoesNotExist error when I use this logic. It seems to hit if not qs.exists(): code. Any comments ?

@agusmakmun

This comment has been minimized.

Copy link

agusmakmun commented Oct 23, 2016

I got an IntegrityError if try this method, and solved by creating a new integer field that reference to the pk/id from other database (field) with EasyAutocomplete plugin. you can checkout at my article for more: https://python.web.id/blog/how-to-implement-foreignkey-for-django-multi-databases/

@SergeiMinaev

This comment has been minimized.

Copy link

SergeiMinaev commented Jul 3, 2017

Hello. I use this field. All work fine except the filter. Any idea what's wrong?

class CityManager(models.Model):
    city = SpanningForeignKey(City, null=True)
    email = models.EmailField(max_length=100)

>>> CityManager.objects.filter(city__name='Алматы')
Traceback (most recent call last):
 blablabla
sqlite3.OperationalError: no such table: mydb_core_city
 blablabla
django.db.utils.OperationalError: no such table: mydb_core_city
>>>
@Rocckk

This comment has been minimized.

Copy link

Rocckk commented Jul 12, 2019

Hello, Jared.

Please specify how this patch worked on postgres-level. Which SQL query was generated by Django on the background in your case and which database backend did you use?

I tried this on Postgresql and it did not work. Moreover, postgres does not allow to refer to tables in different databases.
For example, assuming you are in db1 database, simple query like this:

select * from db2.table1;

fails with:

ERROR: relation "db2.table1" does not exist.

Although there is such a table in the db2 database, Postgres can't find it. So there's a doubt it will work from Django's side.

@gcko

This comment has been minimized.

Copy link
Owner Author

gcko commented Jul 15, 2019

Please specify how this patch worked on postgres-level. Which SQL query was generated by Django on the background in your case and which database backend did you use?

This was implemented with a MySQL backend originally. I haven't invested any time in getting it to work with a postgres backend. you might check out the links in my original post to see if they have updated their implementations.

Good luck!

@Rocckk

This comment has been minimized.

Copy link

Rocckk commented Jul 16, 2019

This was implemented with a MySQL backend originally.

Could you specify which flavour of MySQL was used? Standard InnoDB or some other?

Thanks in advance.

@gcko

This comment has been minimized.

Copy link
Owner Author

gcko commented Jul 17, 2019

@dima-kov

This comment has been minimized.

Copy link

dima-kov commented Sep 4, 2019

Trying to use your gist, and I am having trouble running the migrations. I get the same error as yomguy above. "Relation does not exist."

I have a custom router file for each database.
@DustinHolden have you figured out this problem. Currently, I have the same issue and can not find any suitable solution.

@gcko

This comment has been minimized.

Copy link
Owner Author

gcko commented Sep 5, 2019

Trying to use your gist, and I am having trouble running the migrations. I get the same error as yomguy above. "Relation does not exist."
I have a custom router file for each database.
@DustinHolden have you figured out this problem. Currently, I have the same issue and can not find any suitable solution.

What are the Databases you are using? The code written was deployed for MySQL databases running Django 1.8 - I haven't verified if it continues to be a workable solution - there may have been some changes to Django that might necessitate updating this solution.

@dima-kov

This comment has been minimized.

Copy link

dima-kov commented Sep 5, 2019

@gcko Postgres. Yes, it is django 2.1 now, so many changes have been done. But, I am asking @DustinHolden, because we have same problem, maybe he found some solution.

@ldsad7

This comment has been minimized.

Copy link

ldsad7 commented Sep 13, 2019

Also searching for an answer as the proposed solution doesn't work for me (django 2.2).

@dima-kov

This comment has been minimized.

Copy link

dima-kov commented Sep 16, 2019

@ldsad7, as for us, we refused this feature and replaced it with regular microservices.

@sijianlin

This comment has been minimized.

Copy link

sijianlin commented Sep 23, 2019

Hello. The code has been working well but today I got unexpected error of 'SpanningForeignKey' object has no attribute 'rel'. I looked at the ForeignKey code and it seems like the "rel" attribute has been changed to "remote_field"? And res.to seems to be changed to remote_field.model? Do you need to update the code?
Thanks, Sijian

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.