2

I have a migration that works on a generic schema named tenant_schema. In the run_migrations_online function in env.py i set up a schema_translate_map for tenant_schema.

I expected sqlalchemy to translate this migration operation to run on the desired schema, however it seems like it tries to run sql queries using the schema tenant_schema.

any ideas how to fix it ?

example:

the upgrade function in the migration file:

2018-09-05_17-28_247f3546088f_add_foo_column.py

def upgrade():
    op.add_column('derived_table', sa.Column('foo', sa.BigInteger(), nullable=True), 
                  schema='tenant_schema')

the run_migrations_online function:

env.py

schema = 'other_name'  # normally i get the name as an argument from alembic
def run_migrations_online():
    connectable = create_engine(get_url(), echo=True)

    with connectable.connect() as connection:
        # setting up the translation map
        conn = connection.execution_options(schema_translate_map={'tenant_schema': schema})
        context.configure(
            connection=conn,
            target_metadata=target_metadata,
            include_schemas=True,
            version_table_schema=schema,
            include_object=include_object,
        )
        with context.begin_transaction():
            context.run_migrations()

the exception (full traceback is too long and not that informative):

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) schema "tenant_schema" does not exist
 [SQL: 'ALTER TABLE tenant_schema.derived_table ADD COLUMN foo BIGINT'] 
(Background on this error at: http://sqlalche.me/e/f405)

as you can see it tries to do ALTER TABLE tenant_schema.derived_table instead of the desired ALTER TABLE other_name.derived_table

moshevi
  • 4,999
  • 5
  • 33
  • 50

1 Answers1

1

Problem

From the SQLAlchemy docs regarding schema_translate_map (emphasis mine):

The feature takes effect only in those cases where the name of the schema is derived directly from that of a Table or Sequence; it does not impact methods where a string schema name is passed directly

As all the schemas are passed directly in alembic migration operations, the schema_translate_map is not taken into account.

Solution

What you probably need is:

  1. Use alembic hooks to tweak the way schema is added to your migration, so that it is not a literal string but a lookup in some global context (e.g. render os.environ['TENANT_SCHEMA'] instead of literal string 'tenant_schema').

    Probably the right place to hook would be to override the rendering functions, see example in the docs. Unfortunately I cannot show any code for this because I haven't tried this myself.

    Alternatively, you can try to register your custom comparators which would run after alembic's and won't actually compare anything but will replace the schema attribute in alembic-generated operations with a custom string subclass:

    from alembic.autogenerate import comparators
    
    class FakeSchema(str):
        def __repr__(self):
            return "os.environ['TENANT_SCHEMA']"
    
    @comparators.dispatch_for('schema')
    def tweak_schema(autogen_context, upgrade_ops, schemas):
        for op in upgrade_ops.ops:
            if getattr(op, 'schema', None) == 'tenant_schema':
                op.schema = FakeSchema(op.schema)
                autogen_context.imports.add('import os')  # for os.environ
    

    You can read about comparator functions in alembic docs.

  2. Set the schema name in that global context to the value you need when migrations are run (in this example, pass TENANT_SCHEMA environment variable to alembic or add it into os.environ in your env.py).

Mikhail Burshteyn
  • 4,762
  • 14
  • 27