Specify a "version_num" for different schemas?

[LoicH]

Is there a way to handle multiple Alembic environments, one for each schema in the same database?

I asked a question on StackOverflow (https://stackoverflow.com/questions/69359861/alembic-put-a-version-num-for-each-schema)

In short, in my team we have multiple projects on the same database, each project has a schema, and we want each project to be able to handle migrations/revisions independently of other projects.

[CaselIT]

Hi,

I think that's the defualt behaviour of alembic, just configure the sqlalchemy connection to point to the right schema when passed to alembic.
In your example you seem to want to use multiple alembic.ini files, so it should not require special consideration when running alembic like in this case https://alembic.sqlalchemy.org/en/latest/cookbook.html#multiple-environments
For more customization on the autogenerate, there is this section of the docs https://alembic.sqlalchemy.org/en/latest/autogenerate.html?highlight=schema#controlling-what-to-be-autogenerated

[LoicH]

Thanks for your quick answer, I'm relieved if this is the default behaviour of Alembic, yet I don't understand how I can manage my project if our team code is structured like this:

team
├───service1
│        ├───requirements.txt
│        ├───main.py
│        └───alembic/
│                 └───versions/
│                           └───schema1/
│                                     └───123abc_create_schema1_table1.py
└───service2
         ├───requirements.txt
         ├───main.py
         └───alembic/
                  └───versions/
                            └───schema1/
                                      └───234bcd_create_schema2_table1.py

[LoicH]

Thanks for your answer, indeed I don't know how to configure Alembic so that it targets a specific schema.

I see in the tutorial that Alembic can access my application's model, but my application does not have a model. Each service (service1 & service2) is an ETL pipeline, we exclusively use SqlAlchemy to run SQL queries and dump dataframes in our tables. So we don't have a model.

How can I setup Alembic to target a specific schema? Do I need to develop a model for each service?

[CaselIT]

The "target the specific schema" is needed just by autogenerate.

In you case, since you don't have a metadata and don't have the table defined in sqlalchemy in python, you can just manually write the migration with alembic, without needing a metadata object

Just specify the schema in the alembic operation and you should be good. For example in service1 migration you would have op.create_table('name', ....., schema='schema1'), on service2 you would instead specify schema='schema2'

[LoicH]

Indeed that is what I'm trying to do, but calling 'alembic upgrade' on the same database with different 'alembic.ini' won't work because they will share the same "dbo.alembic_version", no?

[CaselIT]

Whit that configuration you should have multiple version tables, one for each schema.

You can specify that using the configuration argument https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.version_table_schema in the env.py files

[LoicH]

Indeed it seems to work! Thanks a lot for your answers and your help!