Using Alembic for Single Database with Multiple Schemas

OK so you have entirely separate alembic environments each with its own env.py correct?

OK last question do your metadata models have a “schema” in them? that is, they look like:

class MyModel(Base):
   # ...

   __table_args__ = {"schema": "user_schema"}

the include_object we are looking for here looks like this:

def include_object(object, name, type_, reflected, compare_to):
    if type_ == "table" and object.schema != "user_schema":
        return False
   else:
        return True

context.configure(
    # ...
    include_schemas=True,
    include_object = include_object
)

within the autogenerate process (I’m assuming that’s what we’re talking about when you say “recreate the tables for every migration script.”), you have complete control over what objects are considered by using that hook.

Hi @zzzeek I have the same requirement

or, is each schema a copy of one “master” schema, and your application has just one model that is copied to all of them? this would be multitenancy, and youd’ need to alter your env.py to loop through all the schemas individually.

Can you explain to me how can I achieve this. Do you have some snippets on this.?

it’s linked in the comment above, that link is:

https://alembic.sqlalchemy.org/en/latest/cookbook.html#rudimental-schema-level-multi-tenancy-for-postgresql-databases