Django ManyToMany through with multiple databases

Question

TLTR: Django does not include database names in SQL queries, can I somehow force it to do this or is there a workaround?

The long version:

I have two legacy MySQL databases (Note: I have no influence on the DB layout) for which I'm creating a readonly API using DRF on Django 1.11 and python 3.6

I'm working around the referential integrity limitation of MyISAM DBs by using the SpanningForeignKey field suggested here: https://stackoverflow.com/a/32078727/7933618

I'm trying to connect a table from DB1 to a table from DB2 via a ManyToMany through table on DB1. That's the query Django is creating:

SELECT "table_b"."id" FROM "table_b" INNER JOIN "throughtable" ON ("table_b"."id" = "throughtable"."b_id") WHERE "throughtable"."b_id" = 12345

Which of course gives me an Error "Table 'DB2.throughtable' doesn't exist" because throughtable is on DB1 and I have no idea how to force Django to prefix the tables with the DB name. The query should be:

SELECT table_b.id FROM DB2.table_b INNER JOIN DB1.throughtable ON (table_b.id = throughtable.b_id) WHERE throughtable.b_id = 12345

Models for app1 db1_app/models.py: (DB1)

class TableA(models.Model):
    id = models.AutoField(primary_key=True)
    # some other fields
    relations = models.ManyToManyField(TableB, through='Throughtable')

class Throughtable(models.Model):
    id = models.AutoField(primary_key=True)
    a_id = models.ForeignKey(TableA, to_field='id')
    b_id = SpanningForeignKey(TableB, db_constraint=False, to_field='id')

Models for app2 db2_app/models.py: (DB2)

class TableB(models.Model):
    id = models.AutoField(primary_key=True)
    # some other fields

Database router:

def db_for_read(self, model, **hints):
    if model._meta.app_label == 'db1_app':
        return 'DB1'

    if model._meta.app_label == 'db2_app':
        return 'DB2'

    return None

Can I force Django to include the database name in the query? Or is there any workaround for this?

Django ManyToMany through with multiple databases

3 Answers