How to have multiple PostgreSQL Schema in Django?

Hello Django lovers,

I need to separate my application data into multiple Postgresql schemas. I’m trying to have a simple solution for that. So we have a new database backend which was inherited from the Django PostgreSQL backend _cursor is replaced to be able to add SET search_path=[schema_name] to queries. here is a sample code:

from django.db.backends.postgresql import base


class DatabaseWrapper(base.DatabaseWrapper):
    def _cursor(self, name=None):
        cursor = super()._cursor(name=name)
        cursor.execute(f'SET search_path={self._get_schema()}')
        return cursor
  1. for some operation like add or read in Django admin i see this error:
  File "/b/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.ProgrammingError: syntax error at or near "SET"
LINE 1: ...5572462592_sync_1" NO SCROLL CURSOR WITH HOLD FOR SET search...

I have tried with DISABLE_SERVER_SIDE_CURSORS but the Django admin terminates my session and returns to the login page

  1. i think modifying _cursor method is not a professional way because I have found some N+1 Query warnings in Sentry

  2. this method is working with DRF very well, and I can distribute my data using API but seems modifying _cursor method, is not an excellent way for Django admin. for example, most of the time Django admin redirects me to the login page without any error and message

if you have any idea, i really like to hear that.

For those situations where we need to access multiple schemas in the same database, we create separate entries in the DATABASES configuration. Each one specifies a different search_path in the OPTIONS section of that connection.

We have separate apps for the models in each schema, and a router that routes a query for that app to the appropriate database connection. (Most of these “apps” consist only of a models.py file, as the models themselves are processed by views in the main app.)

2 Likes