dumping django historical data in different database using django routers

Question

I am using the django-simple-history package to track changes in my model. Now, I want to dump only the historical data into another database. I attempted to achieve this using a router, but I encountered an error:

django.db.utils.OperationalError: (1824, "Failed to open the referenced table 'common_user'")

The issue appears to be because the common_user table is in the default database and not in the history database.

Here's the codes:

# routers.py
class HistoryRouter:
    def db_for_read(self, model, **hints):
        model_name = model.__name__
        if 'historical' in model_name.lower():
            return 'history_db'
        return 'default'

    def db_for_write(self, model, **hints):
        print('3'*100)
        model_name = model.__name__
        if 'historical' in model_name.lower():
            return 'history_db'
        return 'default'

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if hints.get("db") is not None:
            return db == hints.get("db")
        if model_name != None:
            if 'historical' in model_name.lower():
                return db == 'history_db'
        return db == 'default'

#models.py
class Profile(models.Model):

    profile_type = models.ForeignKey(ProfileType, on_delete=models.PROTECT)
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    nickname = models.CharField(
        max_length=64, unique=True, blank=False, null=False)
    org = models.ForeignKey(
        Org, null=True, on_delete=models.CASCADE, blank=True, related_name="user_org"
    )
    address = models.ManyToManyField(Address, blank=True)
    is_active = models.BooleanField(default=True)
    is_section_admin = models.BooleanField(default=False)
    is_organization_admin = models.BooleanField(default=False)
    is_organization_member = models.BooleanField(default=False)
    date_of_joining = models.DateField(auto_now_add=True, null=True, blank=True)
    referral = models.CharField(
        max_length=64, blank=True, null=True)
    referred = models.CharField(
        max_length=64, blank=False, null=False, unique=True, default=uuid.uuid4().hex)
    history = HistoricalRecords(m2m_fields=(address, ), use_base_model_db=False,  cascade_delete_history=True)

#settings.py
DATABASE_ROUTERS = ['src.common.routers.HistoryRouter']

Answer 1

The reason this happens is because django-simple-history keeps track of the user that makes the modification, or at least tries to do so. But since the model of that user is in a different database, the FOREIGN KEY can not be added.

But that scenario is taken into account: you can specify the field for the user, and thus change it to a (nullable) IntegerField for example:

class Profile(models.Model):
    # …
    history = HistoricalRecords(
        m2m_fields=(address,),
        use_base_model_db=False,
        cascade_delete_history=True,
        history_user_id_field=models.IntegerField(null=True),
    )

---

Note: Please don't use default=uuid.uuid4().hex. This will generate a UUID the first time it interprets the model when you start the server, and use that UUID as default for all items it will construct. Additionally, it will also include that field in a new migration each time you migrate, since the default is always different. Use default=uuid.uuid4 instead.