Django error relation “auth_user” does not exist using multiple database

Question

I'm working on Django 1.9 and python 3.3 project using multiple databases (different schema in a same postgresql database). When I try to migrate the project for the first time, I'm getting this error

Running migrations:
  Rendering model states... DONE
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying MyApp.0001_initial...Traceback (most recent call last):
  File "/usr/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: ERROR:  relation "auth_user" does not exist


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 10, in 
    execute_from_command_line(sys.argv)
  File "/usr/lib/python3.4/site-packages/django/core/management/__init__.py", line 350, in execute_from_command_line
    utility.execute()
  File "/usr/lib/python3.4/site-packages/django/core/management/__init__.py", line 342, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/lib/python3.4/site-packages/django/core/management/base.py", line 348, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/lib/python3.4/site-packages/django/core/management/base.py", line 399, in execute
    output = self.handle(*args, **options)
  File "/usr/lib/python3.4/site-packages/django/core/management/commands/migrate.py", line 200, in handle
    executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial)
  File "/usr/lib/python3.4/site-packages/django/db/migrations/executor.py", line 92, in migrate
    self._migrate_all_forwards(plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/lib/python3.4/site-packages/django/db/migrations/executor.py", line 121, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/usr/lib/python3.4/site-packages/django/db/migrations/executor.py", line 198, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/lib/python3.4/site-packages/django/db/backends/base/schema.py", line 90, in __exit__
    self.execute(sql)
  File "/usr/lib/python3.4/site-packages/django/db/backends/base/schema.py", line 110, in execute
    cursor.execute(sql, params)
  File "/usr/lib/python3.4/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/usr/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/usr/lib/python3.4/site-packages/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/lib/python3.4/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/usr/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: ERROR:  relation "auth_user" does not exist

This error seems to appeard in other projects when the auth_user table isn't migrated. In my case beginning my migration with manage.py migrate auth before migrating the app who need it doesn't solve the problem.

I suspect that the problem come from the use of different database in Django. My auth_user table is stored in the default database and the content of the models.py is routed to an other database.

Is the migration process looking for the auth_user table in the same database than my app database? Is it something absolutely different?

Answer 1

Answer to the problem

It is in fact a cross database reference problem. Django can't create cross database foreign keys.

From the Django 1.8 documentation (and there isn't any solution in the next versions (current version is 1.10)) :

Cross-database relations

Django doesn’t currently provide any support for foreign key or many-to-many relationships spanning multiple databases. If you have used a router to partition models to different databases, any foreign key and many-to-many relationships defined by those models must be internal to a single database.

This is because of referential integrity. In order to maintain a relationship between two objects, Django needs to know that the primary key of the related object is valid. If the primary key is stored on a separate database, it’s not possible to easily evaluate the validity of a primary key.

If you’re using Postgres, Oracle, or MySQL with InnoDB, this is enforced at the database integrity level – database level key constraints prevent the creation of relations that can’t be validated.

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

How to workaround and keep separate databases

In my case, because router are working, there is a little hack to simplify the cross database objects.

class CrossDBUser(models.Model):
    user = models.IntegerField()

    def get_user(self):
        return User.objects.get(id=self.user)

    def set_user(self, user):
        self.user = user.id

class MyClassWithCrossDB(CrossDBUser):
    field1 = models.CharField(max_length=200, blank=False)
    field2 = models.IntegerField(default=0)

With this I can use the method set_user and get_user to work with the user stored in my object MyClassWithCrossDB.

Of course it's not perfect because it doesn't allow automated actions like on_delete=models.CASCADE and I'm obliged to work with methods instead of the instance variable. But it is a solution to workaround.