python - How to use schemas in Django?

Question

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

Answer 1

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/

Answer 2

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 3

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 4

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 5

I just developed a package for this problem: https://github.com/ryannjohnson/django-schemas.

After some configuration, you can simply call set_db() on your models:

model_cls = UserModel.set_db(db='db_alias', schema='schema_name')
user_on_schema = model_cls.objects.get(pk=1)

The package uses techniques described in https://stackoverflow.com/a/1628855/5307109 and https://stackoverflow.com/a/18391525/5307109, then wraps them for use with Django models.

Answer 6

There is no explicit Django support for postgreSQL schemas.

When using Django (0.95), we had to add a search_path to the Django database connector for PostgreSQL, because Django didn't support specifying the schema that the tables managed by the ORM used.

Taken from:

http://nxsy.org/using-postgresql-schemas-with-sqlalchemy-and-elixir

The general response is to use SQLAlchemy to construct the SQL properly.

Oh, and here's another link with some suggestions about what you can do with the Django base, extending it to try to support your scheme:

http://news.ycombinator.com/item?id=662901

Answer 7

I've had some success just saying

db_table = 'schema\".\"tablename'

in the Meta class, but that's really ugly. And I've only used it in limited scenarios - it may well break if you try something complicated. And as said earlier, it's not really supported...

Answer 8

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 9

For SQL server database:

db_table = "[your_schema].[your_table]"