Migrate Django model to Postgresql schema

Question

I would like to create a new table in a specific Postgresql schema (i.e. "schema1) from a Django migration.

Despite following approach 1 from this blog or this post, the migration sends the table to the default schema "public" instead of "schema1".

In settings.py, I have:

DATABASES = {

    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'OPTIONS': {
                        'options': '-c search_path=django,public'
                    },
        'NAME': 'myDB',
        'USER': 'username',
        'PASSWORD': '***',
        'HOST': 'my.host.address',
        'PORT': '1234',
    },

    'schema1': {
    'ENGINE': 'django.db.backends.postgresql_psycopg2',
    'OPTIONS': {
                    'options': '-c search_path=schema1,public'
                },
    'NAME': 'myDB',
    'USER': 'username',
    'PASSWORD': '***',
    'HOST': 'my.host.address',
    'PORT': '1234',
    } 
}  


#Path to DBrouter to handle PG schemas https://stackoverflow.com/a/51007441/3976696
DATABASE_ROUTERS = ('djangogirls.dbrouters.MyDBRouter',)

In djangogirls/dbrouters.py, I have:

from legacydbapp.models import MyUser

# Include here any class (i.e. table) that belongs to the schema "schema1"
ROUTED_MODELS_SCHEMA1 = [MyUser]


class MyDBRouter(object):
    """
    A router to place DB queries into correct schema depending on considered tables.
    """
    def db_for_read(self, model, **hints):
        if model in ROUTED_MODELS_SCHEMA1 :
            return 'schema1'
        return None

    def db_for_write(self, model, **hints):
        if model in ROUTED_MODELS_SCHEMA1 :
            return 'schema1'
        return None

And the model class I'm trying to migrate, in models.py:

class MyUser(models.Model):
    first_name = models.CharField(max_length=30, default='',null=True, blank=True) 
    last_name = models.CharField(max_length=30, default='', null=True, blank=True)
    profession = models.CharField(max_length=32,default='', null=True, blank=True)
    def __str__(self):
        return self.first_name + " " + self.last_name
    class Meta:
        managed = True
        db_table = 'myuser'

I ran the following commands:

$ python manage.py makemigrations legacydbapp
$ python manage.py sqlmigrate legacydbapp 0001_initial
$ python manage.py migrate legacydbapp

And the sqlmigrate returns the following SQL:

BEGIN;
--
-- Create model MyUser
-- 
CREATE TABLE "myuser" (
    "id" serial NOT NULL PRIMARY KEY, 
    "first_name" varchar(30) NULL, 
    "last_name" varchar(30) NULL, 
    "profession" varchar(32) NULL); 
COMMIT;

If the DB router were working, I would expect the SQL to read instead CREATE TABLE "schema1.myuser", but this isn't the case. Did I mess up somewhere, or is this simply not achievable in Django 2.1.5?

Daniel Hepper · Accepted Answer · 2019-02-18 17:14:21Z

3

You have to explicitly provide the name of the database definition when running migrate:

$ python manage.py migrate legacydbapp --database schema1

To ensure that the model MyUser is only created in a specific database, your router has to implement .allow_migrate()

answered Feb 18, 2019 at 17:14

Daniel Hepper

30.1k10 gold badges78 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

sc28 Over a year ago

Thanks, this worked once I specified --database schema1. However, I didn't need to implement allow_migrate(). If I understand the docs correctly, this would be a safeguard to silently prevent migrations to apply in unauthorized schemas (e.g. in case one forgets to specify the DB with --database schema1). I would appreciate if you could confirm this, or indicate if there are any other risks not implementing any allow_migrate() function.

Daniel Hepper Over a year ago

That's my understanding, too. If you always call migrate with an appname and the database parameter, you probably don't need to implement allow_migrate. However, if you call migrate without any parameters, it will apply migrations from all apps, including your legacydbapp.

Stefan_EOX Over a year ago

This does not work for me. I get django.db.utils.ConnectionDoesNotExist: The connection schema1 doesn't exist

Daniel Hepper Over a year ago

@Stefan_Fairphone you have to replace schema1 with the appropriate connection name from your DATABASES setting

Stefan_EOX Over a year ago

Yeah, I know, but the database is called postgres and has multiple schemas. I used schema1 for the purpose of being in line with your answer.

|

Youssri Abo Elseod · Accepted Answer · 2023-08-03 13:01:06Z

0

hi to migrate one model to specific scema you can use:

python manage.py migrate -s schema1

edited Aug 3, 2023 at 13:01

answered Mar 23, 2022 at 9:48

Youssri Abo Elseod

8111 gold badge12 silver badges24 bronze badges

4 Comments

spideyclick Over a year ago

I don't see this anywhere in the Django docs. Where did you get the -s parameter from?

Youssri Abo Elseod Over a year ago

in django 4 "python manage.py migrate -s schema1"

Pushpan Over a year ago

I don't understand why this answer was marked as not useful. You just saved my day bro. I just wanted to create dummy schema for postgress fallback search. This command just worked as a charm.

Uriel Alves Over a year ago

it's worked for me too. Thanks

Collectives™ on Stack Overflow

Migrate Django model to Postgresql schema

2 Answers 2

7 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related