Alembic migration No schema selected to create in

Question

I am trying to run my migration in which there are also enums in few tables. Something like below

op.create_table('test_table',
sa.Column('name', sa.String(length=250), nullable=False),
sa.Column('status', sa.Enum('CREATED', 'COMPLETED', name='status'), nullable=False),
sa.Column('id', sa.BigInteger(), autoincrement=True, nullable=False),
sa.Column('is_deleted', sa.Boolean(), server_default=sa.text('false'), nullable=False),
sa.PrimaryKeyConstraint('id', name=op.f('pk_test_table')),
schema='my_personal_schema'
)

Now I recently came to know the postgresql creates all the enums by default in the public cloud. So this works fine everywhere(local, public cloud where my user has access to public schema) but on an environment where this access is restricted it gives error

InvalidSchemaError No schema selected to create in

I was able to find the cause because on my local machine when I deleted my schema and tried running the migration again it gave me error as

status enum already exist.

This was because I deleted my schema and the enum was still present in public. So I updated my migration to look like this

op.create_table('test_table',
sa.Column('name', sa.String(length=250), nullable=False),
sa.Column('id', sa.BigInteger(), autoincrement=True, nullable=False),
sa.Column('is_deleted', sa.Boolean(), server_default=sa.text('false'), nullable=False),
sa.PrimaryKeyConstraint('id', name=op.f('pk_test_table')),
schema='my_personal_schema'
)
with op.batch_alter_table('test_table', schema='my_personal_schema') as batch_op:
    status = postgresql.ENUM('CREATED', 'COMPLETED', name='status', create_type=False)
    status.create(batch_op.get_bind(), checkfirst=True)
    batch_op.add_column(sa.Column('status', status, nullable=True))

This worked fine on my local machine as the issue I was facing previously is gone(tried removing the schema and applying the migration again, it worked). This means the enums are getting created inside the schema now. But On the private environment where I didn't have access to public schema, it is still giving me the same error. Not sure why it is not able to find the schema.

NOTE: The migration runs fine if I comment all the enums.

score 0 · Answer 1 · answered Aug 10 '23 at 06:13

For anyone who is looking for solution of this, I was able to resolve this.

So the issue was that postgresql by default creates all enums in public schema(so that any tables from any schema inside the database can use it). In my case my application user did not have access to public schema. So I had to create my enums inside the schema. The code for the same is something like this

with op.batch_alter_table('test_table', schema='my_personal_schema') as batch_op:
    status = ('CREATED', 'COMPLETED')
    op.execute(f'CREATE TYPE my_personal_schema.task_status AS ENUM {status}')
    batch_op.add_column(sa.Column('status', postgresql.ENUM(name='task_status', schema='my_personal_schema'), nullable=False))

Hope it helps someone!!

Alembic migration No schema selected to create in

1 Answers1