Is there a way to ignore column comments during revision --autogenerate

[zlisde]

We are currently using alembic for schema change management, and also another tool to apply comments on columns for data documentation purpose. However when we run revision --autogenerate, alembic generate scripts removed all the comments not defined in the table models, I am wondering if there is a way to ignore column comments during alembic change capture.

[zzzeek]

several, the most direct is to apply column_reflect to the Table class in env.py and set "comment" to blank:

from sqlalchemy import event
from sqlalchemy import Table

@event.listens_for(Table, 'column_reflect')
def receive_column_reflect(inspector, table, column_info):
    column_info['comment'] = None

the compare logic should skip entirely if the "comment" is reflected as None so the above should disable all comparison.

[CaselIT]

well include_object is usually defined inside the env.py file and it has access to the metadata

alembic/alembic/templates/generic/env.py

Line 20 in 2c96569

target_metadata = None

[zzzeek]

include_object is probably the only hook where you get the current and the compared object at the same time. that said if you set all comments to None everywhere it should not come up with any comments being changed.

[will-misslin]

When I output the target_metadata.tables comment attribute for each table in the schema, all the results come back as None. To be extra sure, I set the object's comment to match the target_metadata value. This is the version of include_object I am using to debug:

    def include_object(object, name, type_, reflected, compare_to):
        if type_ == "table":
			# print for debugging
            target_comment = target_metadata.tables[f"{SF_SCHEMA}.{object.name}"].comment
            print(f"object: {object.comment}")
            print(f'target: {target_comment}')

			# equate both versions to avoid triggering any comment ddl
            object.comment = target_comment
        # but continue to include the object in the autogenerate comparison
        return True

The output:

(.venv) > $ poetry run alembic --name data_analytics revision --autogenerate                       1s 

INFO  [alembic.runtime.migration] Context impl SnowflakeImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
object: None
target: None
...

However, the autogenerated revision still contains the table comment DDL. I also noticed that the target_metadata.tables object is an immutable dict, which sounds a bit ominous. Is there another attribute I should be using to inspect and set comments?

Thanks!

[zzzeek]

can you be specific? it is illustrating "op.alter_table()" operations that change only the comment ? can you look at the "reflected" boolean and "compare_to" to more accurately inspect/set the comments to be equivalent? you probably want to set "comment" on both "object" and "compare_to" at the same time.

the "target_metadata" collection is the MetaData() object that you are passing to the environment, and the "tables" collection that's exposed is immutable because MetaData does not support table removal and table addition must go through the documented APIs.

[will-misslin]

Oh wow. I don't know how I missed it, but compare_to was absolutely what I was looking for!. Thanks for talking me through this! Here is the final version of include_object that we will use to remove both column and table level comments. With this, we can avoid also needing to use the column_reflect from the original answer.

    def include_object(object, name, type_, reflected, compare_to):
        # capture tables and objects, excluding new model additions (nothing to
        # compare to)
        if type_ in ("table", "column") and compare_to is not None:
            # equate both versions to avoid triggering any comment ddl
            object.comment = compare_to.comment = None
        # but continue to include the object in the autogenerate comparison
        return True