How to create Django models with ManyToMany relation with tables in different PostgreSQL schemas, one model using just part of unmanaged table

Question

I have a Django application which works just fine. Problem is that it needs to be migrated and that brings some changes.

Whole application used to be in PostgreSQL schema intake.
Problem is with following models.

class Authority(models.Model):
    id = models.IntegerField(primary_key=True)
    code = models.IntegerField()
    name = models.CharField(max_length=255)

class Doc(behaviors.Timestampable):
    id = models.IntegerField(primary_key=True)
    authorities = models.ManyToManyField(Authority, related_name="docs", )

I need Authority model to use existing table named authority_list. Table is in different schema named lists and it contains many columns. I need only three of them. Table is used by other apps too and is used only for reading.

I tried creating router like this:

ROUTED_MODELS = [models.Authority]

class DefaultRouter:
    def db_for_read(self, model, **hints):
        if model in ROUTED_MODELS:
            return 'lists'
        return None

    def db_for_write(self, model, **hints):
        if model == models.Authority:
            raise Exception("This model is read only!")
        return None

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'OPTIONS': {
            'options': '-c search_path=intake,lists,public'
        },
        'NAME': 'dmx',
        'USER': 'postgres',
        'HOST': 'localhost',
    },
    'lists': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'OPTIONS': {
            'options': '-c search_path=lists,intake,public'
        },
        'NAME': 'dmx',
        'USER': 'postgres',
        'HOST': 'localhost',
    },
DATABASE_ROUTERS = ('dmx.db_routers.DefaultRouter',)

Changed Authority model

class Authority(models.Model):
    class Meta:
        managed = False
        db_table = "authority_list"

It didn't work like I wanted so I found somewhere this syntax 'lists\".\"authority_list' that didn't work either.

Whatever I try Django always creates table authority_list in intake schema.
And because relation is many to many, Django creates joining table that links intake.authority_list too instead of lists.authority_list.

Only solution I found was to create view in schema intake, select those 3 columns I need from lists.authority_list. But then I was told that view couldn't be used. Table lists.authority_list is periodically (once a week) updated in a way that table is delete and created again. It doesn't seem to me as a good practice but I can't change it.

Is there some way how to do it?

score 0 · Accepted Answer · answered Dec 10 '19 at 06:30

Answering my own question: actually it is very simple.

Problem was with generated migrations not with the model syntax. Since my application is not in production yet I could delete all migrations and database and recreate them. And that helped and now it works as I would expect.

Only thing you need is:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'OPTIONS': {
            'options': '-c search_path=intake,lists,public'
        },
        'NAME': 'dmx',
        'USER': 'postgres',
        'HOST': 'localhost',
    },

And model

class Authority(models.Model):
    id = models.IntegerField(primary_key=True)
    code = models.IntegerField()
    name = models.CharField(max_length=255)

    class Meta:
        managed = False
        db_table = "authority_list"

You don't need to write your router. Just add schema (lists in my case) to your DATABASES OPTIONS. You don't need to use (maybe deprecated?) syntax like db_table = 'lists\".\"authority_list' And it is ok not to use all fields from table columns.

How to create Django models with ManyToMany relation with tables in different PostgreSQL schemas, one model using just part of unmanaged table

1 Answers1