create multiple table with different schema using flask-migrate on a single database

Question

I am using flask-migrate to handle the creation and maintenance of the table. I have multiples tables with different schema.

 class A(db.model):
      __tablename__ = 'A'
      __table_args__ = {'schema': 'public'}
      # rest of data

 class B(db.model):
      __tablename__ = 'B'
      __table_args__ = {'schema': 'schema_b'}
      # rest of data

so when I am running flask db init and flask db migrate, a migration script is created in the migration folder. But when I am running flask db upgrade to add tables in database it is showing me error

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidSchemaName) schema "schema_b.B" does not exist

As I search about the issue, I found this include_schemas and migrate using different schema, in both the part it is mentioned that to use include_schemas=True in configure in migration/env.py. Also, the link mentioned in the solution in the answer has invalid link, so this becoming little programmatical to me.

I have made changes accordingly. then I am running flask db migrate which is detecting all the tables with all schema. but as I am running flask db upgrate

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidSchemaName) schema "schema_b.B" does not exist

error again appears.

Help me, how to solve this problem using flask migrate. to create a table I have a SQL command which is working fine.

Does the schema exit in your database? It's been a while since I've done this, but I believe the schema needs to be created manually. You just need to add a SQL operation that creates the schema in your migration. — Miguel Grinberg, Jun 15 '20 at 22:43
@Miguel, No schema doesn't exist. I thought that it would handle automatically, let me test it out. Thanks for the information — sahasrara62, Jun 16 '20 at 09:04
@Miguel Thanks this works. Please add your suggestion in answer, so it will help in future — sahasrara62, Jun 16 '20 at 10:02

score 1 · Accepted Answer · answered Jun 16 '20 at 10:23

The include_schemas=True option makes Alembic look for tables in your non-default schemas, but it cannot generate new schemas when you add them to a model definition.

In such a case, what you have to do is run flask db migrate to generate the migration, and then open the generated migration script and add the schema creation right before the new table is created. For example, in the example in your question, your migration for your B model would have been generated more or less like this:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('schema_b.B',
        # ...
    )
    # ### end Alembic commands ###

So you need to add the schema creation statement above the table creation, so that the schema exists already when the table is created:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.execute('create schema schema_b')  # <--- add this
    op.create_table('schema_b.B',
        # ...
    )
    # ### end Alembic commands ###

And then for consistency also drop the schema on the downgrade path:

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('schema_b.B')
    op.execute('drop schema schema_b')  # <--- add this
    # ### end Alembic commands ###

Thanks, the more clear picture now, rather than adding manually add via code. — sahasrara62, Jun 16 '20 at 10:28

create multiple table with different schema using flask-migrate on a single database

1 Answers1