alembic upgrade fails with spatial index + custom schema

[hochstibe]

• sqlalchemy 1.4.40
• alembic 1.8.1
• geoalchemy2 0.12.4
• PostGIS 3.2.0

from sqlalchemy import Column, Integer, MetaData
from sqlalchemy.orm import declarative_base
from geoalchemy2 import Geometry

metadata = MetaData(schema='test')
# Base = declarative_base() -> correct
Base = declarative_base(metadata=metadata)


class User(Base):
    __tablename__ = 'user_account'
    id = Column(Integer, primary_key=True)
    geom = Column(Geometry(geometry_type='POINT', srid=2056, spatial_index=True))

When running the migration, it fails on creating the spatial index:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidSchemaName) FEHLER:  Schema »test« does not exist

[SQL: CREATE INDEX idx_user_account_geom ON test.user_account USING gist (geom)]

Solution: Fix in alembic_helpers.py Line 418: add the schema

    operations.create_table(table_name, *operation.columns, schema=operation.schema, **operation.kw)

alembic revision --autogenerate -m "init" produced (thanks to 0.12.4 👍 ) a correct script including the schemas for the table and the index:

"""init geom

Revision ID: 8eb815ac8210
Revises: 
Create Date: 2022-09-01 07:50:30.679892

"""
from alembic import op
import sqlalchemy as sa
from geoalchemy2 import Geometry

# revision identifiers, used by Alembic.
revision = '8eb815ac8210'
down_revision = None
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_geospatial_table('user_account',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('geom', Geometry(geometry_type='POINT', srid=2056, spatial_index=False, from_text='ST_GeomFromEWKT', name='geometry'), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='test'
    )
    op.create_geospatial_index('idx_user_account_geom', 'user_account', ['geom'], unique=False, schema='test', postgresql_using='gist', postgresql_ops={})
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_geospatial_index('idx_user_account_geom', table_name='user_account', schema='test', postgresql_using='gist', column_name='geom')
    op.drop_geospatial_table('user_account', schema='test')
    # ### end Alembic commands ###

[adrien-berchet]

Hi @hochstibe
Thank you very much for this report and the included fix! And sorry for this miss 😅
#404 includes your patch and a new release should follow today.

[adrien-berchet]

https://pypi.org/project/GeoAlchemy2/0.12.5/