How to use schemas in Django?

Question

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

I am just starting with Django and it strikes me that such basic feature is not yet natively supported. Especially when there is [a 7 year old feature request](https://code.djangoproject.com/ticket/6148) open. — Luís de Sousa, Jan 08 '15 at 16:10
Could you please unmark the [answer by kmpm](http://stackoverflow.com/a/1912906/2066215) as accepted? It is no longer functional. — Luís de Sousa, May 18 '15 at 07:23
I am no longer using Django frequently, so If there is a good answer for this question please let me now so I could mark as answered the best one. — Vinicius, Apr 07 '16 at 01:44
I stopped using Django myself. IMHO you should not accept any of the answers, since none solves this issue. The best you can do is changing the SEARCH_PATH, but it only works if you are using a single schema. — Luís de Sousa, Apr 07 '16 at 07:13

score 38 · Answer 1 · answered Feb 11 '15 at 10:35

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/

score 35 · Answer 2 · answered Oct 27 '09 at 05:02

35

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.

answered Oct 27 '09 at 05:02

k4ml

1,196
12
17

2

I don't use PostgreSQL, but this is how I make it work in SQL Server too. – cethegeek Dec 16 '09 at 20:13
2

Any trick for creating the schema automatically in syncdb before creating tables? Necessary for running test suites. – akaihola Dec 17 '09 at 09:35
1

Warning, using the naming works but will fail for creating indexes if you use syncdb or migrate – Ghislain Leveque Oct 10 '14 at 07:42
Where exactly do you introduce that instruction? – Luís de Sousa Jan 08 '15 at 15:55
Need to also remember that table and schema names are case sensitive in postgres, got me with all caps. – serg Jul 28 '16 at 22:10
@cethegeek, how did you get this working for SQL Server? I used `db_table = 'myschema\".\"mytable'`, and django still created the model as `dbo.myschema.mytable`. – OverflowingTheGlass Aug 18 '17 at 13:40
@CameronTaylor I don't think I ever used Django to create the tables (syncdb). I only used this to access the tables after they were manually created in the database. – cethegeek Aug 20 '17 at 14:12

score 11 · Answer 3 · edited Jul 30 '15 at 11:31

11

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

Create the following directory:

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

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

In settings.py, add the following database configuration:

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

edited Jul 30 '15 at 11:31

JJD

50,076
60
203
339

answered Aug 22 '13 at 21:58

Mingyu

31,751
14
55
60

This looks like the only valid solution in this thread, however laborious. But how would it work with several "apps"? – Luís de Sousa Jan 08 '15 at 16:02
@Luís de Sousa I don't see any problems with several "apps". We have been using this method to deal with the lack of schema support in production for more than 1 years without any problems. – Mingyu Jan 08 '15 at 16:11
Minguy, I do not see problems myself, I am just asking how to do it. I am presently on chapter 5 of the Django book. – Luís de Sousa Jan 08 '15 at 16:13
There is [a question on this later issue](http://stackoverflow.com/questions/4497235/django-schemas-postgresql#comment48690281_4497235), presently closed for unknown reasons. It is very common to find multiple schemas in systems developed on Oracle or Postgres that in Django would correspond to different applications. – Luís de Sousa May 18 '15 at 07:20

score 11 · Answer 4 · edited Dec 22 '15 at 19:20

11

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.

edited Dec 22 '15 at 19:20

vastlysuperiorman

1,694
19
27

answered Dec 16 '09 at 07:18

kmpm

505
5
14

1

Thats because the patch/fix is not implemented yet. – kmpm Mar 06 '12 at 13:55
This is still not functional in Django 1.6.1. Why is this answer marked as accepted? It is even doubtful it classifies as one... – Luís de Sousa Jan 08 '15 at 15:54
1

the ticket #6148 had a patch that never was included and probably never will be. It have been open for 7 years as of writing. – kmpm Jan 08 '15 at 21:22
I probably accepted this answer because back in 2009 it worked. Should I remove the accepted mark? – Vinicius Jan 21 '15 at 05:21
The correct answer is in there though, after my update at least. You don't use schemas in django. – kmpm Mar 31 '15 at 12:07
I think this is out of date again: https://www.amvtek.com/blog/posts/2014/Jun/13/accessing-multiple-postgres-schemas-from-django/ seems to work just fine. – Scott Stafford Jan 17 '17 at 18:58
This is needed when using Oracle, and db_schema isnt allowed. Is there another way rather than escaping? – Jcc.Sanabria Jun 05 '19 at 21:01

score 7 · Answer 5 · edited May 23 '17 at 12:26

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.

score 3 · Answer 6 · answered Jul 22 '09 at 07:21

3

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...

answered Jul 22 '09 at 07:21

Magnus Hagander

23,890
5
56
43

It is indeed ugly, as this way you have to manage table names yourself. – Luís de Sousa Jan 08 '15 at 15:58

score 2 · Answer 7 · answered Jul 21 '09 at 17:54

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

score 2 · Answer 8 · edited Jun 20 '20 at 09:12

2

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.

schema1.table_name_a
schema2.table_name_b
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

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 10 '13 at 21:10

george.erickson

96
2

2

This only works if you manage the database manually. If you use `syncdb` with this set up you will end up with all your tables in the `public` schema. – Luís de Sousa Jan 08 '15 at 16:08

score 2 · Answer 9 · answered Nov 28 '17 at 15:34

2

For SQL server database:

db_table = "[your_schema].[your_table]"

answered Nov 28 '17 at 15:34

DrunkZemin

51
3

score -1 · Answer 10 · answered Nov 13 '21 at 07:45

https://docs.djangoproject.com/en/dev/topics/db/multi-db/#using-routers

urls.py

from django.urls import path, include
from rest_framework.routers import DefaultRouter
from my_app.my_views import ClientViewSet

router = DefaultRouter(trailing_slash=False)
router.register(r'', ClientViewSet, base_name='clients')

urlpatterns = [
    path('', include(router.urls)),
]

How to use schemas in Django?

10 Answers10

Example

Linked