Using alembic to manage multiple databases and schema_translate_map.

[skip-life1]

I want to use Alembic to manage MySQL database migrations. The database contains multiple schemas, each used by different services.
Additionally, each schema is used by multiple tenants. To distinguish between tenants, I will add prefixes to the multiple schemas of different tenants, similar to SQLAlchemy's schema_translate_map.

I tried to look up related information and found the following approach, in run_migrations_online:

        conn = connection.execution_options(schema_translate_map={"schema1": "test_alembic_multi_schema_schema1"})
        context.configure(
            connection=conn,
            target_metadata=target_metadata,
            include_schemas=True,
            include_name=include_name
        )

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

This approach did not yield the desired result. That is, dynamically replacing the schema of each table according to the mapping relationship in schema_translate_map.
Is this feasible?
If feasible, what could be the possible issues preventing it from working?
If not feasible, how can I achieve the desired functionality?

I'm not sure if I explained the issue well. I can more details as required

Have a nice day!

[zzzeek]

multi-tenant systems require customization of env.py to suit your specific needs, which can vary greatly.

the schema_translate_map feature should only be used with real schema names. it would not be feasible to refer to names that are not actual schemas, when you say "I will add prefixes to the multiple schemas of different tenants" that is ambiguous if you mean that you are making new schemas for each tenant, which means you are not sharing a single schema among tenants, or this is just some kind of internal name, which means you would not place such a name into schema translate map.

overall a mutli-tenant scheme might be breaking tenant data among: 1. schema-per-tenant 2. table-per-tenant or 3. multiple tenants represented in a single table. your question is not clear as to which of these schemes you are using.

I would refer to the various recipes we have for multi-tenancy such as https://alembic.sqlalchemy.org/en/latest/cookbook.html#rudimental-schema-level-multi-tenancy-for-postgresql-databases and https://alembic.sqlalchemy.org/en/latest/cookbook.html#run-multiple-alembic-environments-from-one-ini-file

[zzzeek]

I will update the recipe at https://alembic.sqlalchemy.org/en/latest/cookbook.html#rudimental-schema-level-multi-tenancy-for-postgresql-databases to include MySQL as well. substitute

connection.execute(text('set search_path to "%s"' % current_tenant))

with

connection.execute(text('USE %s' % current_tenant))