evaulate support of schema_translate_map feature of SQLAlchemy with alembic migrations

[anetbnd]

Hello

I'm using the following version script for upgrade:

    op.create_table('feature_class',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(length=1024), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('name')
    )
    op.add_column(
        'features',
        sa.Column('feature_class_id', sa.Integer(), nullable=True),
    )

The table features still exists from a version before. When running the upgrade it fails with the error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "features" does not exist
 [SQL: 'ALTER TABLE features ADD COLUMN feature_class_id INTEGER'] (Background on this error at: http://sqlalche.me/e/f405)

I enabled SQLAlchemy SQL statement output and was able to see the following SQL statements for the operation:

CREATE TABLE develop_test.feature_class (
        id SERIAL NOT NULL,
        name VARCHAR(1024) NOT NULL,
        PRIMARY KEY (id),
        UNIQUE (name)
)

ALTER TABLE features ADD COLUMN feature_class_id INTEGER

As you can see the CREATE TABLE statement is using the schema name develop_test, but not the ALTER TABLE statement.

I apply the schema on this database by using the following SQLAlchemy command:

engine.update_execution_options(schema_translate_map={None: 'develop_test'})

Where engine is the engine object, which have been bound on the declarative class which I use for all table classes.

In my env.py script I use exactly this engine object to create the connection:

    # database is an "container"-like object, which collects several information about 
    # my database, like the engine, metadata and schema objects
    connectable = database.engine 

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            process_revision_directives=process_revision_directives,
            version_table_schema=database.schema 
        )

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

Maybe I'm using the schema in a wrong way?

[anetbnd]

BTW: my alembic version is: 1.0.2

[zzzeek]

oh, the translate map. That's not supported with Alembic, you would need to pull the desired schema name out of the map yourself and apply it explicitly to op.add_column().

[zzzeek]

sorry, this use case hasn't been considered and would need development time to be figured out, so I can't provide a timeline for this. you would need to work around manually for now.

[anetbnd]

Hi Mike

Thanks for the answer. I wonder why the other operations (create_table and create_index for example) are using the schema map, but this operation not. Maybe the required changes are quite small and I can prepare a pull-request?

I cannot pass the schema name to the operation itself inside the version file, because I cannot see any way to pass data from env.py to it. And hardcoding the schema name does not work, because it changes depending on the machine where I apply this version script.

Is there any other way in using schema with alembic? For example not using the translation map, but any option inside the env.py for context.configure?

Best regards, André

[anetbnd]

Hi again

I did some debugging in order to find out, what the difference between CREATE TABLE and ALTER TABLE operations is. What I found out is, that CREATE TABLE operations are done by SQLAlchemy, which works out of the box with schema-maps, while ALTER TABLE operations are provided by Alembic and do not use the schema-map during statement compilation.

In the file alembic/ddl/base.py is the method format_table_name which prepares the table name for the ALTER TABLE statement. Here only the schema argument is taken into account, but not schema-map:

def format_table_name(compiler, name, schema):
    quote = functools.partial(compiler.preparer.quote, force=None)

    if schema:
        return quote_dotted(schema, quote) + "." + quote(name)
    else:
        return quote(name)

Just for testing I changed this function a little bit in order to use the schema-map:

def format_table_name(compiler, name, schema):
    class SchemaObject():
        def __init__(self, name, schema):
            self.name   = name
            self.schema = schema

        def _translate_schema(self, effective_schema, map_):
            return map_.get(effective_schema, effective_schema)


    table_object = SchemaObject(name, schema)
    result       = compiler.preparer.format_table(table_object)
    return result

The compiler.preparer object has all information available to handle schema-maps here. However the method format_table expects a Table object but not a table name as string, which is not present here. Just as a dirty hack, I created a fake object, which looks like a Table object for this particular case. This is of course not a clean solution.

However maybe this helps anyone?

@zzzeek Do you have any idea how to get a Table object here instead of the table-name as string? In this case, it would be very easy to apply schema-maps within this function.

[anetbnd]

And here is also a small hint, how to apply those changes without adapting the alembic module (you can still use the alembic from pip):

In your env.py file, just add those lines to the beginning of your script:

import alembic.ddl.base as alembic_base

def format_table_name_with_schema(compiler, name, schema):
    class SchemaObject():
        def __init__(self, name, schema):
            self.name   = name
            self.schema = schema

        def _translate_schema(self, effective_schema, map_):
            return map_.get(effective_schema, effective_schema)


    table_object = SchemaObject(name, schema)
    result       = compiler.preparer.format_table(table_object)
    return result

alembic_base.format_table_name = format_table_name_with_schema

[zzzeek]

hi @anetbnd -

great work running through the details on this one. I agree fully that schema_translate_map is there and if it is, alembic ops should be using it fully. The means by which the compiler works with a table object that has ".schema" can be maintained here, Alembic has an existing system of creating ad-hoc "table" objects for this purpose, so replace SchemaObject(name, schema) with this:

from alembic.operations import schemaobj
schema_obj = schemaobj.SchemaObjects()
schema_obj.table(name, schema=schema)

there might be a way we can get at an existing SchemaObjects handle as well, but for now we can start with that.

[anetbnd]

Hi @zzzeek

Thanks for that hint, it makes the workaround much nicer! My full workaround patch for this issue is now:

import alembic.ddl.base as alembic_base

def format_table_name_with_schema(compiler, name, schema):
    from alembic.operations import schemaobj

    table = schemaobj.SchemaObjects().table(name, schema=schema)
    return compiler.preparer.format_table(table)

alembic_base.format_table_name = format_table_name_with_schema

One can simply put this on the top of the env.py file to fix this issue here.

@zzzeek Do you think, that using the function from above as format_table_name inside alembic/ddl/base.py would fix the issue also officially? Or would this introduce unwanted side-effects?

[zzzeek]

yes the code we have above should be added to alembic. Since the schema_translate_map already takes effect for a subset of alembic operations, it is worse that it is inconsistently applied vs. the possibility of someone using schema_translate_map and relying upon op.alter_column() not using it, so I would propose we include this as a bug fix.

[zzzeek]

note schema_translate_map is changing in SQLAlchemy see https://github.com/sqlalchemy/sqlalchemy/issues/5004 .

this may alter how the approach is applied here.

[rohitjain-rj]

Hi @zzzeek. Is there update on this. Any plan for this feature to get released with Alembic?

[zzzeek]

not in any way. anyone is free to work on this feature. a workaround above is given.

[zzzeek]

this issue seems to conflict with what's stated at #409 which seems to claim schema_translate_map works.

[bpneumann]

Expanding on @anetbnd 's solution, I found a way to perform the schema translation without affecting the base implementation of alembic.ddl.base.format_table_name:

import alembic.ddl.base as alembic_base
from functools import wraps

schema_translate_map = {
    '_placeholder_schema_': 'real_schema'
}

def schema_translate_wrapper(f):
    @wraps(f)
    def format_table_name_with_schema(compiler, name, schema):
        translated_schema = schema_translate_map.get(schema, schema)
        return f(compiler, name, translated_schema)

    return format_table_name_with_schema

alembic_base.format_table_name = schema_translate_wrapper(alembic_base.format_table_name)

[joshua-alday]

Heya folks, I've been running into this issue as well so I have found this really helpful. But! I have ran into an issue. I have foreign keys that are not being mapped properly, or perhaps I'm not using this solution properly.

I'm implementing a multitenancy solution that uses schema_translate_map. So the solution provided above needs to be reapplied in a loop. I assume I can just reassign alembic_base.format_table_name in a loop so I can resupply the schema_translate_map.

Problem comes from foreign keys however. It does not seem this solution works for them as the foreign keys will be created on the original protoschema.