django with multiple databases and foreignkeys for User

Question

Suppose I have a django app on my server, but I wish to do authentication using django.contrib.auth.models where the User and Group models/data are on another server in another database. In Django, my DATABASES setting would be something like this:

DATABASES = {
    'default': {},
    'auth_db': {
        'NAME'      : 'my_auth_db',
        'ENGINE'    : 'django.db.backends.mysql',
        'USER'      : 'someuser',
        'PASSWORD'  : 'somepassword',
        'HOST'      : 'some.host.com',
        'PORT'      : '3306',
    },
    'myapp': {
        'NAME': 'myapp_db',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'localuser',
        'PASSWORD': 'localpass',
    }
}

DATABASE_ROUTERS = ['pathto.dbrouters.AuthRouter', 'pathto.dbrouters.MyAppRouter']

First question: will this work, ie will it allow me to login to my Django app using users that are stored in the remote DB 'my_auth_db'?

Assuming the answer to the above is yes, what happens if in my local DB (app 'myapp') I have models that have a ForeignKey to User? In other words, my model SomeModel is defined in myapp and should exist in the myapp_db, but it have a ForeignKey to a User in my_auth_db:

class SomeModel(models.model):
    user = models.ForeignKey(User, unique=False, null=False)
    description = models.CharField(max_length=255, null=True)
    dummy = models.CharField(max_length=32, null=True)
    etc.

Second question: Is this possible or is it simply not possible for one DB table to have a ForeignKey to a table in another DB?

If I really wanted to make this work, could I replace the ForeignKey field 'user' with an IntegerField 'user_id' and then if I needed somemodel.user I would instead get somemodel.user_id and use models.User.objects.get(pk=somemodel.user_id), where the router knows to query auth_db for the User? Is this a viable approach?

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

The answer to question 1 is: Yes.

What you will need in any case is a database router (The example in the Django docs is exactly about the auth app, so there's no need to copy this code here).

The answer to question 2 is: Maybe. Not officially. It depends on how you have set up MySQL:

https://docs.djangoproject.com/en/dev/topics/db/multi-db/#limitations-of-multiple-databases

Django doesn’t currently provide any support for foreign key or many-to-many relationships spanning multiple databases.

This is because of referential integrity.

However, if you’re using SQLite or MySQL with MyISAM tables, there is no enforced referential integrity; as a result, you may be able to ‘fake’ cross database foreign keys. However, this configuration is not officially supported by Django.

I have a setup with several legacy MySQL DBs (readonly). This answer shows How to use django models with foreign keys in different DBs?

I later ran into troubles with Django ManyToMany through with multiple databases and the solution (as stated in the accepted answer there) is to set the table name with quotes:

class Meta:    
    db_table = '`%s`.`table2`' % db2_name

Related questions that might provide some additional information:

How to work around lack of support for foreign keys across databases in Django

How to use django models with foreign keys in different DBs?

It would be nice if somebody would take all this information and put in into the official Django doc :-)

This is great info, thanks! From what I see here, it looks like people created all sorts of somewhat complicated solutions, just to be able to access a model using somemodel.user syntax. Since this is just a test app, I think I will use a plain IntegerField and store user_id and have some separate functions that (for example) get the user for somemodel like this: user=User.objects.get(pk=somemodel.user_id). It looks like this will still work. It won't have referential integrity, ie if someone deletes a User then this approach will not automatically delete my SomeModel instances with user_id. — Marc, Nov 01 '17 at 01:03
Yeah, these are complicated workarounds, but all change requests get rejected by Django, so that's what we are stuck with... If performance isn't that much of an issue, the `user=User....` approach is OK, but it will fire an additional query to read the user of each object you are accessing while a spanning ForeignKey could do it in one query (provided the DBs are on the same server) — masterfloda, Nov 01 '17 at 01:17

django with multiple databases and foreignkeys for User

1 Answers1