Foreign key fields different databases. Django

Question

Is that possible to have model with foreign key fields on different databases? example:

class MultiBDModel(models.Model):
    db1_user = models.ForeignKey(User) # here suppose to be foreign key on `db1`
    db2_user = models.ForeignKey(User) # and here on `db2`

maybe copy somehow User. Apply for it custom manager. Which returns query set with using='db1'

in settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3', # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
        'NAME': 'db1',                      # Or path to database file if using sqlite3.
        'USER': '',                      # Not used with sqlite3.
        'PASSWORD': '',                  # Not used with sqlite3.
        'HOST': '',                      # Set to empty string for localhost. Not used with sqlite3.
        'PORT': '',                      # Set to empty string for default. Not used with sqlite3.
    },

    'website': {
        'ENGINE': 'django.db.backends.sqlite3', # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
        'NAME': 'db2',                      # Or path to database file if using sqlite3.
        'USER': '',                      # Not used with sqlite3.
        'PASSWORD': '',                  # Not used with sqlite3.
        'HOST': '',                      # Set to empty string for localhost. Not used with sqlite3.
        'PORT': '',                      # Set to empty string for default. Not used with sqlite3.              # Set to empty string for default. Not used with sqlite3.
    }
}

score 2 · Answer 1 · answered Jun 20 '17 at 16:02

No. As written by @ignacio-vazquez-abrams, one model must have all fields in the same database.

BUT

As an alternative, you could use a proxy model to link between models from two different databases.

Aim

One model should provide the fields from db1 and db2 at the same time

General trick

You have the model ContactMessage from db1, that we will name legacy_db. We suppose you don't want to touch to this model since it comes from another project.
Create a proxy model ProxyContactMessage, it has the same attributes than ContactMessage.
Use a database router to tell Django where to look in legacy_db for ProxyContactMessage objects.
Add a new model ExtendedContactMessage with the fields you would like to add. Declare a OneToOneField with ProxyContactMessage. This data will be saved to your db2 django_db.
Your proxy model cannot hold the new fields since it's abstract, but it can have methods that ask the related ExtendedContactMessage object (if any). Add the callables you want.

Example

In your legacy_app/models.py, the model on db1 legacy_db is:

class ContactMessage(models.Model):
    subject = models.CharField(max_length=255)
    message = models.TextField()
    created_at = models.DateTimeField()
    created_by = models.CharField(max_length=255)

    class Meta:
        managed = False
        db_table = 'contact_message'

    def __unicode__(self):
        return self.subject

Therefore you create in myapp/models.py:

class ProxyContactMessage(ContactMessage):
    class Meta:
        proxy = True
        verbose_name = 'Contact message'
        verbose_name_plural = 'Contact messages'

    def add_extension(self):
        e = ExtendedContactMessage(contact_message=self)
        e.save()
        return e

    def mark_as_processed(self):
        try:
            e = self.extendedcontactmessage
        except ExtendedContactMessage.DoesNotExist:
            e = self.add_extension()
        e.mark_as_processed()

    def processed(self):
        return self.extendedcontactmessage.processed

    def processed_at(self):
        return self.extendedcontactmessage.processed_at

class ExtendedContactMessage(models.Model):
    contact_message = models.OneToOneField(ProxyContactMessage)
    processed = models.BooleanField(default=False, editable=False)
    processed_at = models.DateTimeField(null=True, default=None, editable=False)

    def mark_as_processed(self):
        self.processed = True
        self.processed_at = timezone.now()
        self.save()

Note that only the non abstract model ExtendedContactMessage will be saved in db2, since ProxyContactMessage is abstract.

In settings.py, set DATABASE_ROUTERS with the class

class LegacyRouter(object):
    """
    A router to control all database operations on models in the
    legacy database.
    """
    def db_for_read(self, model, **hints):
        if model.__name__ == 'ProxyContactMessage':
            return 'legacy_db'
        return None
    def db_for_write(self, model, **hints):
        """
        Attempts to write in legacy DB for ContactMessage.
        """
        if model.__name__ == 'ProxyContactMessage':
            return 'legacy_db'
        return None

Your default router sends everything to db2.

Finally you may have an admin class like:

def mark_as_processed(modeladmin, request, queryset):
    for obj in queryset:
        obj.mark_as_processed()
mark_as_processed.short_description = "Mark as processed"

class ProxyContactMessageAdmin(admin.ModelAdmin):
    list_display = (
        'subject',
        'message',
        'created_at',
        'created_by',
        'processed',
        'processed_at',
    )
    actions = (mark_as_processed,)
admin.site.register(ProxyContactMessage, ProxyContactMessageAdmin)

"Hack" the app_name in Meta

Catch the queryset

score 1 · Accepted Answer · answered May 22 '12 at 20:51

1

No. The ORM cannot do anything the database engine isn't capable of.

answered May 22 '12 at 20:51

Ignacio Vazquez-Abrams

776,304
153
1,341
1,358

Ok i understood. I forget that django is doing joins in database not in python. – Pol May 22 '12 at 20:54

Foreign key fields different databases. Django

2 Answers2

BUT

Aim

General trick

Example