How to use schemas in Django?

Question

I whould like to use postgreSQL schemas with django, how can I do this?

Answer 1

For SQL server database:

db_table = "[your_schema].[your_table]"

Answer 2

As mentioned in the following ticket: https://code.djangoproject.com/ticket/6148, we could set search_path for the django user.

One way to achieve this is to set search_path via psql client, like

ALTER USER my_user SET SEARCH_PATH TO path;

The other way is to modify the django app, so that if we rebuild the database, django won't spit all the tables in public schema.

To achieve this, you could override the DatabaseWrapper defined in django.db.backends.postgresql_psycopg2.base

1. Create the following directory:

   app/pg/
   ├── __init__.py
   └── base.py
   

2. Here's the content of base.py

   from django.db.backends.postgresql_psycopg2.base import DatabaseWrapper
   
   class DatabaseWrapper(DatabaseWrapper):
       def __init__(self, *args, **kwargs):
           super(DatabaseWrapper, self).__init__(*args, **kwargs)
   
       def _cursor(self):
           cursor = super(DatabaseWrapper, self)._cursor()
           cursor.execute('SET search_path = path')
           return cursor
   

3. In settings.py, add the following database configuration:

   DATABASES = {
       'default': {
           'ENGINE': 'app.pg',
           'NAME': 'db',
           'USER': 'user',
           'PASSWORD': '',
           'HOST': '',
           'PORT': '',
       }
   }
   

Answer 3

I know that this is a rather old question, but a different solution is to alter the SEARCH_PATH.

Example

Lets say you have three tables.

1. schema1.table_name_a
2. schema2.table_name_b
3. table_name_c

You could run the command:

SET SEARCH_PATH to public,schema1,schema2;

And refer to the tables by their table names only in django.

See 5.7.3. The Schema Search Path

Answer 4

It's a bit more complicated than tricky escaping. Have a look at Ticket #6148 in Django for perhaps a solution or at least a patch. It makes some minor changes deep in the django.db core but it will hopefully be officially included in django. After that it's just a matter of saying

db_schema = 'whatever_schema'

in the Meta class or for a global change set

DATABASE_SCHEMA = 'default_schema_name'

in settings.py

UPDATE: 2015-01-08

The corresponding issue in django has been open for 7 years and the patch there will not work any more. The correct answer to this should be...

At the moment you can't use postgreSQL schemas in django out of the box.

Answer 5

I've been using:

db_table = '"schema"."tablename"'

in the past without realising that only work for read-only operation. When you try to add new record it would fail because the sequence would be something like "schema.tablename"_column_id_seq.

db_table = 'schema\".\"tablename'

does work so far. Thanks.

Answer 6

Maybe this will help.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'OPTIONS': {
            'options': '-c search_path=your_schema'
        },
        'NAME': 'your_name',
        'USER': 'your_user',
        'PASSWORD': 'your_password',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
}

I get the answer from the following link: http://blog.amvtek.com/posts/2014/Jun/13/accessing-multiple-postgres-schemas-from-django/