Issue with Django DatabaseRouter and Related Models multi region database

Using Django Using the ORM
1

Issue with Django DatabaseRouter and Related Models

I’m working on a Django project with multiple regional databases. I’m facing an issue where fetching an employee object with a related model from a different database results in the related model being retrieved from the default database instead of the intended regional database.

Setup

  • Two regional databases (us and potentially others)
  • DatabaseRouter to route models to their respective databases
  • The issue occurs when trying to prefetch or access related models using foreign key relationshipsPreformatted text
 employee = Employees.objects.using('us').get(employee_id='ORG-EMP-1001')
SELECT "general_employees"."org_id_id",
      "general_employees"."first_name",
 FROM "general_employees"
 WHERE "general_employees"."employee_id" = 'ORG-EMP-1001'
 LIMIT 21
Execution time: 0.306803s [Database: us]

organisation = Organisation.objects.using('us').get(pk=employee.org_id.pk)
SELECT "general_organisation"."onboard_id",
      "general_organisation"."org_id",
      "general_organisation"."zip_code",
 FROM "general_organisation"
 WHERE "general_organisation"."org_id" = 'ORG-FFZN5OFF'
 LIMIT 21
Execution time: 0.021803s [Database: default]

Traceback (most recent call last):
 File "/Users/sshva/Documents/newenv/lib/python3.10/site-packages/django/db/models/fields/related_descriptors.py", line 187, in __get__
   rel_obj = self.field.get_cached_value(instance)
 File "/Users/sshva/Documents/newenv/lib/python3.10/site-packages/django/db/models/fields/mixins.py", line 15, in get_cached_value
   return instance._state.fields_cache[cache_name]
KeyError: 'org_id'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
 File "<console>", line 1, in <module>
 File "/Users/sshva/Documents/newenv/lib/python3.10/site-packages/django/db/models/fields/related_descriptors.py", line 205, in __get__
   rel_obj = self.get_object(instance)
 File "/Users/sshva/Documents/newenv/lib/python3.10/site-packages/django/db/models/fields/related_descriptors.py", line 168, in get_object
   return qs.get(self.field.get_reverse_related_filter(instance))
 File "/Users/sshva/Documents/newenv/lib/python3.10/site-packages/django/db/models/query.py", line 496, in get
general.models.Organisation.DoesNotExist: Organisation matching query does not exist.

class DatabaseRouter:
   def db_for_read(self, model, **hints):
       if 'database' in hints:
           return hints['database']
       if model._meta.app_label == 'common':
           return 'common'
       return 'default'

   def db_for_write(self, model, **hints):
       if 'database' in hints:
           return hints['database']
       if model._meta.app_label == 'common':
           return 'common'
       return 'default'

   def allow_relation(self, obj1, obj2, **hints):
       if obj1._state.db == obj2._state.db:
           return True
       return False

   def allow_migrate(self, db, app_label, model_name=None, **hints):
       if db == 'common' and app_label != 'common':
           return False
       if db != 'common' and app_label == 'common':
           return False
       return True

   def allow_relation(self, obj1, obj2, **hints):
       if 'prefetch_related' in hints:
           return True
       return obj1._state.db == obj2._state.db

Issue

When I try to fetch an Employees object from the us database and then access its related Organisation model, the related model is retrieved from the default database instead of the us database. This results in a DoesNotExist error.

How can I ensure that related models are fetched from the same database as the main model?

2

Side Note: When posting code, templates, errors, tracebacks, etc here, enclose the code between lines of three backtick - ` characters. This means you’ll have a line of ```, then your code, then another line of ```. This forces the forum software to keep your code properly formatted. (I’ve taken the liberty of fixing your original post for you.)

3

thanks a lot ken any help for the post would be more helpful

4

Agreed - but in the situation where I am unable to do that at the moment, this is done to make it easier for someone else seeing this thread to read your post - and hopefully provide that assistance.