Autogenerate doesn't issue a "CREATE SCHEMA" DDL

[ariciputi]

Hi all,
I'm using SQLAlchemy + Alembic in all my projects, mostly with PostgreSQL. When I use PG as a backend I tend to use schemas a lot. As in:

tbl = Table('table_a', metadata, Column('id', Integer, primary_key=True), schema='a_schema')

With a definition like this I would expect Alembic to generate an upgrade script like this:

def upgrade():                                                                                      
      # ### commands auto generated by Alembic - please adjust! ###     
      op.execute("CREATE SCHEMA IF NOT EXISTS a_schema")  # <-- I have to add this statement manually!!                            
      op.create_table(                                                                                
          "table_a",                                                                        
          sa.Column("id", sa.Integer(), nullable=False),                                              
          sa.PrimaryKeyConstraint("id"),                                                              
          schema="a_schema",                                                                 
      )

However the "CREATE SCHEMA" statement is not automatically generated, I have to add it manually. No matter what I try to do, I cannot manage to get it there. The revision script is generated using --autogenerate at the command line and the db given to Alembic as a comparison is created from scratch (so obviously the schema is not already there).

Any suggestion on how to get SCHEMAs managed by Alembic automatically?

Cheers.

[zzzeek]

hey there -

There's not too much way to do this other than to implement the logic yourself. this is something that can be done but I'd have to work out an example for you. the notion of a "schema" is not very portable between different kinds of databases so it's not generally supportable.

what sort of application is this where schemas are being added and dropped on a regular basis? if this is for multitenancy, usually that kind of thing is automated separately.

[ariciputi]

Hi @zzzeek,
my applications are usually single-tenant but they tend to have many tables that you can logically group in few subsets. So it is more a matter of a mental habit to keep them grouped using schemas (especially because I use PG most of the times). I know all the information that I need is there, so it is just a matter of understanding where to retrieve it. If you can give me some advice it would be great.

Cheers

[AsifArmanRahman]

I was trying to achieve the same, and this answer from StackOverflow seems to do the job.