Django ManyToMany through with multiple databases

django, django-database, mysql, python

Solution

A solution exists for Django 1.6+ (including 1.11) for MySQL and sqlite backends, by option ForeignKey.db_constraint=False and explicit `Meta.db_table`. If the database name and table name are quoted by ' ` ' (for MySQL) or by ' " ' (for other db), e.g. `db_table = '"db2"."table2"'`). Then it is not quoted more and the dot is out of quoted. Valid queries are compiled by Django ORM. A better similar solution is `db_table = 'db2"."table2'` (that allows not only joins but it is also by one issue nearer to cross db constraint migration)

db2_name = settings.DATABASES['db2']['NAME']

class Table1(models.Model):
    fk = models.ForeignKey('Table2', on_delete=models.DO_NOTHING, db_constraint=False)

class Table2(models.Model):
    name = models.CharField(max_length=10)
    ....
    class Meta:    
        db_table = '`%s`.`table2`' % db2_name  # for MySQL
        # db_table = '"db2"."table2"'          # for all other backends
        managed = False

Query set:

>>> qs = Table2.objects.all()
>>> str(qs.query)
'SELECT "DB2"."table2"."id" FROM DB2"."table2"'
>>> qs = Table1.objects.filter(fk__name='B')
>>> str(qs.query)
SELECT "app_table1"."id"
    FROM "app_table1"
    INNER JOIN "db2"."app_table2" ON ( "app_table1"."fk_id" = "db2"."app_table2"."id" )
    WHERE "db2"."app_table2"."b" = 'B'

That query parsing is supported by all db backends in Django, however other necessary steps must be discussed individually by backends. I'm trying to answer more generally because I found a similar important question.

The option 'db_constraint' is necessary for migrations, because Django can not create the reference integrity constraint `ADD foreign key table1(fk_id) REFERENCES db2.table2(id)`, but it can be created manually for MySQL.

A question for particular backends is if another database can be connected to the default at run-time and if a cross database foreign key is supported. These models are also writable. The indirectly connected database should be used as a legacy database with `managed=False` (because only one table `django_migrations` for migrations tracking is created only in the directly connected database. This table should describe only tables in the same database.) Indexes for foreign keys can however be created automatically on the managed side if the database system supports such indexes.

Sqlite3: It has to be attached to another default sqlite3 database at run-time (answer SQLite - How do you join tables from different databases), at best by the signal connection_created:

from django.db.backends.signals import connection_created

def signal_handler(sender, connection, **kwargs):
    if connection.alias == 'default' and connection.vendor == 'sqlite':
        cur = connection.cursor()
        cur.execute("attach '%s' as db2" % db2_name)
        # cur.execute("PRAGMA foreign_keys = ON")  # optional

connection_created.connect(signal_handler)

Then it doesn't need a database router of course and a normal `django...ForeignKey` can be used with db_constraint=False. An advantage is that "db_table" is not necessary if the table names are unique between databases.

In MySQL foreign keys between different databases are easy. All commands like SELECT, INSERT, DELETE support any database names without attaching them previously.

This question was about legacy databases. I have however some interesting results also with migrations.

Problem

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?

Original source

Related problems