Django ManyToMany through with multiple databases

Django does have the ability to work with multiple databases. See https://docs.djangoproject.com/en/1.11/topics/db/multi-db/.

You can also use raw SQL queries in Django. See https://docs.djangoproject.com/en/1.11/topics/db/sql/.

I have a similar setup with PostgreSQL. Utilizing search_path to make cross-schema references possible in Django (schema in postgres = database in mysql). Unfortunately, seems like MySQL doesn't have such a mechanism.

However, you might try your luck creating views for it. Make views in one databases that references other databases, use it to select data. I think it's the best option since you want your data read-only anyway.

It's however not a perfect solution, executing raw queries might be more useful in some cases.

---

UPD: Providing mode details about my setup with PostgreSQL (as requested by bounty later). I couldn't find anything like search_path in MySQL documentation.

Quick intro

PostgreSQL has Schemas. They are synonymous to MySQL databases. So if you are MySQL user, imaginatively replace word "schema" with word "database". Requests can join tables between schemas, create foreign keys, etc... Each user (role) has a search_path:

This variable [search_path] specifies the order in which schemas are searched when an object (table, data type, function, etc.) is referenced by a simple name with no schema specified.

Special attention on "no schema specified", because that's exactly what Django does.

Example: Legacy databases

Let's say we got coupe legacy schemas, and since we are not allowed to modify them, we also want one new schema to store the NM relation in it.

• old1 is the first legacy schema, it has old1_table (which is also the model name, for convenience sake)
• old2 is the second legacy schema, it has old2_table
• django_schema is a new one, it will store the required NM relation

All we need to do is:

alter role django_user set search_path = django_schema, old1, old2;

This is it. Yes, that simple. Django has no names of the schemas ("databases") specified anywhere. Django actually has no idea what is going on, everything is managed by PostgreSQL behind the scenes. Since django_schema is first in the list, new tables will be created there. So the following code ->

class Throughtable(models.Model):
    a_id = models.ForeignKey('old1_table', ...)
    b_id = models.ForeignKey('old2_table', ...)

-> will result in a migration that creates table throughtable that references old1_table and old2_table.

Problems: if you happened to have several tables with same names, you will either need to rename them or still trick Django into using a dot inside of table names.

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.