Alembic: put a "version_num" for each schema

Question

In my team we have multiple projects/microservices all hosted on the same database, but each service has a specific schema.

I want to manage the migrations of each project independently of other projects, a way of achieving that could be to specify different environments to Alembic, with one environment specific to one schema.

Let's say I have two services : "service1" and "service2", each with a schema that have the name of the project. I'd like to have the following structure for my code:

team
├───service1
│   ├───requirements.txt
│   ├───main.py
│   └───alembic/
└───service2
    ├───requirements.txt
    ├───main.py
    └───alembic/

I'd like to be able to

1. navigate to team/service1
2. Create a migration and run it
   • have this migration be executed on schema "service1"
3. Go to team/service2
4. Create a migration and run it
   • have this migration be executed on schema "service2"

Is this possible? How can I achieve this?

Answer 1

I used the version_table_schema parameter of the configure method in env.py:

def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            version_table_schema="MY_SCHEMA",
        )

        with context.begin_transaction():
            context.run_migrations()

(Thanks to CaselIT)