[FIXED] Django Foreign key in another database

Issue

I have two separate Django apps with two different databases. In APP1 I need all of the records from one table which is in APP2 DB. Beside that, one of my APP1 models has a ForeignKey which points to APP2 model, and according to docs this is not possible.

As Django does not support foreign key relationships spanning multiple databases i don't know what to do.

Official docs:

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.

This is because of referential integrity. In order to maintain a relationship between two objects, Django needs to know that the primary key of the related object is valid. If the primary key is stored on a separate database, it’s not possible to easily evaluate the validity of a primary key.

As a solution I have thought of merging these two databases, so then these two APPS would use one same database. But then i would have a mess with models because APP1 needs only one table from APP2, and it doesn't need remaining models and DB tables. Also, i'm pretty sure it would make problems (conflicts) while making migrations from these two apps to the one same database.

I'm using Django DB router and this is what i've tried so far:

class Car(models.Model):
    _DATABASE = "cars"
    color = models.TextField(max_length=1000)
    ...

class Employee(models.Model):
    id = models.IntegerField()
    car = models.ForeignKey(Car)
    ...

It gives me:

django.db.utils.OperationalError: (1054, "Unknow column 'car' in 'employees'")

Solution

One DB or Two?

First question to ask your self is it really really necessary to have two different databases? More than one app using the same database is quite common in the PHP world where most people are on shared hosting and the hosting plan allows for only one database.

It's very rare for a website to have sufficient traffic to split the content between two databases. And usually the over head of the router makes it not worth the while. Sharding is almost always better handled with specialized software.

Yep it has to be two!

This is not impossible but just darned hard.

The first thing to do is to change your Car model

class Car(models.Model):
    color = models.TextField(max_length=1000)
    class Meta:
        managed = False

Note that it no longer refers to the second database and it's unmanaged. But you don't have such a table in your database. So you need to create a view

 CREATE OR REPLACE VIEW myapp_cars AS SELECT * FROM cars.otherapp_cars

Please make sure to use the exact database and table names to match your existing ones. We are only half way through. You need to create a trigger to insert into the other database. That has been discussed here many times before, so I will not elaborate. Examples

1. MySQL Trigger to insert data into different DB
2. How to create trigger to insert data to a database on another server

If you want these steps to be reproducible you will need to add a migration manually.

Answered By - e4c5