Alembic generates arbitrary type changes for Geometry columns

Question

I'm working on a project that uses SQLite as a database and Alembic as a database migration tool. It includes spatial data and therefore, spatial extensions and geoalchemy2 are included in the project. I'm using autogenerate command and it detects some changes that don't exist in the geometry columns.

Here is the simplified structure of the project:

    # Model
    sqlite_naming_convention = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(column_0_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
    }
    Metadata = MetaData(naming_convention=sqlite_naming_convention)
    BaseSpatiaLite = declarative_base(metadata=Metadata)


    class Geometries(BaseSpatiaLite):
        __tablename__ = "Geometries"

        geometry_id = Column(Integer, primary_key=True)
        geometry = Column(
            geoalchemy2.types.Geometry(geometry_type="GEOMETRY", srid=4326, management=True),
            nullable=False,
        )
        name = Column(String(length=150), nullable=False)

Alembic's env.py is as follows:

    # env.py
    ...
    def run_migrations_online():
        connectable = engine_from_config(
            config.get_section(config.config_ini_section),
            prefix="sqlalchemy.",
            poolclass=pool.NullPool,
        )
        # Enables Spatialite extension
        listen(connectable, "connect", load_spatialite)
        # Creates Spatial tables if they don't exist
        create_spatial_tables_for_sqlite(connectable)
        with connectable.connect() as connection:
            context.configure(
                connection=connection,
                target_metadata=target_metadata,
                render_as_batch=True,
                compare_type=True,
            )
    
            with context.begin_transaction():
                context.run_migrations()

First migration script which creates the Geometry table:

    ...
    def upgrade():
        op.create_table(
            "Geometries",
            sa.Column("geometry_id", sa.Integer(), nullable=False),
            sa.Column("geometry", geoalchemy2.types.Geometry(management=True), nullable=False),
            sa.Column("name", sa.String(length=150), nullable=False),
            sa.PrimaryKeyConstraint("geometry_id"),
        )
    
    
    def downgrade():
        op.drop_table(
            "Geometries",
        )

After this migration script is run, the table is created correctly:

When I run autogenerate command again, it should have found no changes. However, it generates a migration script which has an arbitrary type change:

    def upgrade():
        with op.batch_alter_table("Geometries", schema=None) as batch_op:
            batch_op.alter_column(
                "geometry",
                existing_type=sa.NUMERIC(),
                type_=geoalchemy2.types.Geometry(srid=4326, management=True),
                nullable=False,
            )
    
    
    def downgrade():
        with op.batch_alter_table("Geometries", schema=None) as batch_op:
            batch_op.alter_column(
                "geometry",
                existing_type=geoalchemy2.types.Geometry(srid=4326, management=True),
                type_=sa.NUMERIC(),
                nullable=True,
            )

I know that I might set compare_type argument to False but I'd like to auto-detect the type changes. Is there any way to tell Alembic that the type of geometry column is Geometry and there is no change at all?

Baris · Accepted Answer · 2020-07-12T20:30:01.660

I found a solution. I'm sharing it here in case some other people might face this error: (https://alembic.sqlalchemy.org/en/latest/autogenerate.html#comparing-types)

It's possible to implement a custom compare_type function and use it in env.py. In my case, geometry columns were interpreted as sqlalchemy.Integer or sqalchemy.NUMERIC types. That's why I added an if clause which returns False if inspected_type is NUMERIC or Integer and metadata_type is geoalchemy2.types.Geometry.

# add it to env.py
def custom_compare_type(
    context, 
    inspected_column, 
    metadata_column, 
    inspected_type, 
    metadata_type
):
# return False if the metadata_type is the same as the inspected_type
# or None to allow the default implementation to compare these
# types. a return value of True means the two types do not
# match and should result in a type change operation.
if (isinstance(inspected_type, NUMERIC) or isinstance(inspected_type, Integer)) and isinstance(
    metadata_type, Geometry
):
    return False

return None

When you change compare_type=True to compare_type=custom_compare_type, Alembic should drop detecting arbitrary type changes for geometry columns!

Note: Alembic still detects a nullability change but it's not related to compare_type issue.

Alembic generates arbitrary type changes for Geometry columns

1 Answers1