Django Foreign key in another database

Question

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'")

Answer 1

If you are working with microservices and using django as a main language program, you might find it interesting:

Scenario

• I have an authentication DB which manage all application users (one Microservice).
• I have another DB for human resources (another microservice).

Code

• I have a module called catalogo, which will manage all models of the authentication MSA (microservice):
  • When you are creating a managed = False table, is not necessary to specify indexes (this works almost similar to Proxy on django).

 class User(AbstractBaseUser, PermissionsMixin, CommonStructure):

    idusuario = models.CharField(
        max_length=50,
        blank=False,
        null=False,
        unique=True,
    )
    ...
    class Meta:
      db_name = "catalogo_usuario"
      managed = False

• Now, we are gonna create a model (in another module called sip) that will have a Foreign Key to the catalogo.User:

class Store(models.Model):
    code = models.CharField(
        max_length=10,
        null=False,
        blank=False,
        verbose_name=u'Code',
    )
    description = models.CharField(
        max_length=200,
        null=False,
        blank=False,
        verbose_name=u'Description',
    )
    created_by = models.ForeignKey(
      'catalogo.User',
      on_delete=models.SET_NULL,
      db_constraint=False,
      null=True,
      related_name="stores",
      default=None,
    )
    class Meta:
      managed = True

• Finally, in the DB Router file, the following code worked pretty good for me:

    def allow_relation(self, obj1, obj2, **hints):
        if (
            obj1._meta.app_label in self.route_app_labels
            or obj2._meta.app_label in self.route_app_labels
        ):
            return True
        return None

The migration file will contain something like the following:

('created_by', models.ForeignKey(db_constraint=False, on_delete=django.db.models.deletion.SET_NULL, related_name='stores', to='catalogo.User',)),

In the database side, it will not create a relationship with user Table that is in another database, but it will create an index for the field:

select * from information_schema.STATISTICS s 
where table_name = '<table_name>';

Debugging

• Go to django shell with ./manage.py shell.
• Access to the model:

from sip.models import Store
store = Store.objects.all().first()
store.created_by

Answer 2

What worked for me was setting the ForeignKey db_constraint to False. This way, no constraint is created during the migration, but the correct field type is still created (from the target model's primary key). The database integrity is not enforced, so you have to know what you are doing. The ForeignKey field is indexed by Django regardless of this flag.

Example:

class Employee(models.Model):
    car = models.ForeignKey(Car, db_constraint=False)
    ...

Docs: https://docs.djangoproject.com/en/4.1/ref/models/fields/#django.db.models.ForeignKey.db_constraint

This flag has existed at least since version 1.x, so I don't know how it didn't come up before.

Needless to say, you are sacrificing your database constraint, so if a record is deleted on the other database, you will have an invalid reference and this may cause exceptions in Django when accessing the property.

DB Router

You will also need to tell Django that you allow relations between databases. You can do that by adding the following method to your router class:

class MyRouter:

    def allow_relation(self, obj1, obj2, **hints):
        if obj1._meta.app_label == 'default' and obj2._meta.app_label == 'database2':
            return True

This function should return True if the router should allow the relation, False if you want to actively deny it, and None for the default behaviour - which is allowing only relations in the same database (which is not what we want). A looser implementation would be to just allow everything (return True).

Docs: https://docs.djangoproject.com/en/4.1/topics/db/multi-db/#allow_relation

Answer 3

I thing this problem has nothing to do with django. By design DBMS doesn't allow FK between databases. Maybe you can found some workaround on internet but probable they don't adhere to best practices.

Check Cross Database Relations section at: https://docs.djangoproject.com/en/3.2/topics/db/multi-db/

Answer 4

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.