Different Postgres schemes with different models in Python alembic

[maguirreluco-btf]

I am new to using SQLAlchemy and Alembic and I’m trying to work with different models and schemas in a Postgres database. These schemas represent different system domains and the idea is to unify database models and migrations into a single project. My question is whether it is possible to keep migrations from the database with a single instance of alembic? I would appreciate some approach or suggestion on how to implement this.

[zzzeek]

yes, when SQLAlchemy models are defined they can be given explicit schema names on a per-table basis as described at https://docs.sqlalchemy.org/en/20/orm/declarative_tables.html#explicit-schema-name-with-declarative-table . The alembic migrations themselves would also include explicit schema name arguments within each op directive, such as op.add_column("organization", Column("name", String()), schema="some_schema"), so that DDL is emitted with schema-qualified names.

so if the situation you have is no more complicated than that, it's just a matter of schema-qualifiying your models and migrations.

[maguirreluco-btf]

thanks, I did it and it worked! now my question is it possible to handle the models in separate files? Apparently if I have my separate "Base" I must import all classes and if I have an "intermediate" class it must implement the attribute table or tablename

[zzzeek]

you can put models in however many files you want. if you want to run an "Autogenerate" process that is aware of all the tables in your model, then yes, you have import them all, otherwise the python program wouldn't know about model/table definitions that are inside of modules that aren't imported.