CODEWITHSUNDEEP
djangopostgresqldatabase-schemapostgisgeodjango
JJD
JJD

Reputation: 51844

How to connect to multiple PostgreSQL schemas from Django?

In my GeoDjango project I want to connect to a legacy PostgreSQL/PostGIS database. It contains the following schemas:

I want the Django tables shown in the screenshot to go into the django schema. I do not want to pollute the public schema.

Django tables

I want the "data" models to connect to the data schema. I already tried to generate models from the legacy tables but python manage.py inspectdb connects to the public schema.

In order to provide access to the different schemas I adapted the approach 2 of this article which preassigns individual search_path values to specific database users:

-- user accessing django schema...
CREATE ROLE django_user LOGIN PASSWORD 'secret';
ALTER ROLE django_user SET search_path TO django, public;

-- user accessing data schema...
CREATE ROLE data_user LOGIN PASSWORD 'secret';
ALTER ROLE data_user SET search_path TO data, public;

Then I configured the database connections as follows:

DATABASES = {

    'default': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'NAME': 'multi_schema_db',
            'USER': 'django_user',
            'PASSWORD': 'secret',
    },

    'data': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'NAME': 'multi_schema_db',
            'USER': 'data_user',
            'PASSWORD': 'secret',
    },
}

How can I actually configure that Django uses the django schema while "data" models connect to the data schema?

Readings

Upvotes: 14

Views: 23388

Answers (4)

agas0077
agas0077

Reputation: 59

For me this article helped a lot: https://erthalion.info/2014/03/08/django-with-schemas/

Basically it suggest setting search_path not via DATABASES...OPTIONS, but using connection_created signal.

In my case, I created signal.py in my core app an put this code inside. This work both for migrations and basic usage.

from django.conf import settings
from django.db.backends.signals import connection_created
from django.dispatch import receiver

@receiver(connection_created)
def setup_connection(sender, connection, **kwargs):
    # Чтобы грузить данные приложения в конкретную схему.
    if connection.alias == "default":
        cursor = connection.cursor()
        cursor.execute(f'SET search_path="{settings.SEARCH_PATH}"')

Upvotes: 0

wbloos
wbloos

Reputation: 251

If you don't need to manage the tables through migrations, you could use escaped quotes for the db_table attribute of your model:

class SomeModel(models.Model):
    field1 = models.AutoField(primary_key=True)  
    class Meta():
        managed=False
        db_table=u'"schema\".\"table"'

Upvotes: 7

JJD
JJD

Reputation: 51844

You have to leverage the search_path:

DATABASES = {

    'default': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'OPTIONS' : {
                'options': '-c search_path=django,public'
            },
            'NAME': 'multi_schema_db',
            'USER': 'django_user',
            'PASSWORD': 'secret',
    },

    'data': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'OPTIONS' : {
                'options': '-c search_path=data,public'
            },
            'NAME': 'multi_schema_db',
            'USER': 'data_user',
            'PASSWORD': 'secret',
    },
}

Upvotes: 10

Sina Khelil
Sina Khelil

Reputation: 1991

We use Django Tenant Schemas with great success. It allows you to access different schemas by delineating different tenants as the owners of the schemas.

This will allow you to set the schema on a per call basis. If the schema needs to be set on a per url basis, you can do that in middleware.

Upvotes: 2

Related Questions