Unexpected behavior postgis migration on fastAPI

[Pierre-Alexandre35]

Describe the bug

My database is postgis/postgis:16-3.4 on linux/amd64. I am trying to generate a table called Village and the autogenerate of the alembic version from my model is not acting as expected. This is my model:

class Village(Base):
    __tablename__ = "village"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)
    coordinates = Column(
        Geometry(geometry_type="POINT", srid=4326), nullable=False
    )
    owner_id = Column(
        Integer, ForeignKey(f"{SCHEMA_NAME}.user.id"), nullable=True
    )
    population = Column(Integer, nullable=False)

    owner = relationship("User", back_populates="villages")

when i am doing this : docker-compose run --rm backend alembic revision --autogenerate -m "create village table", I have a new version that is created:

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '6f134c73e76c'
down_revision = '91979b40eb38'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('spatial_ref_sys')
    op.alter_column('user', 'is_active',
               existing_type=sa.BOOLEAN(),
               nullable=True)
    op.alter_column('user', 'is_superuser',
               existing_type=sa.BOOLEAN(),
               nullable=True)
    op.create_index(op.f('ix_user_email'), 'user', ['email'], unique=True)
    op.create_index(op.f('ix_user_id'), 'user', ['id'], unique=False)
    op.drop_column('user', 'address')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user', sa.Column('address', sa.VARCHAR(length=100), autoincrement=False, nullable=True))
    op.drop_index(op.f('ix_user_id'), table_name='user')
    op.drop_index(op.f('ix_user_email'), table_name='user')
    op.alter_column('user', 'is_superuser',
               existing_type=sa.BOOLEAN(),
               nullable=False)
    op.alter_column('user', 'is_active',
               existing_type=sa.BOOLEAN(),
               nullable=False)
    op.create_table('spatial_ref_sys',
    sa.Column('srid', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('auth_name', sa.VARCHAR(length=256), autoincrement=False, nullable=True),
    sa.Column('auth_srid', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('srtext', sa.VARCHAR(length=2048), autoincrement=False, nullable=True),
    sa.Column('proj4text', sa.VARCHAR(length=2048), autoincrement=False, nullable=True),
    sa.CheckConstraint('(srid > 0) AND (srid <= 998999)', name='spatial_ref_sys_srid_check'),
    sa.PrimaryKeyConstraint('srid', name='spatial_ref_sys_pkey')
    )
    # ### end Alembic commands ###

and when doing my docker-compose run --rm backend alembic upgrade head , I am getting this error:

psycopg2.errors.DuplicateTable: relation "idx_village_coordinates" already exists


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/usr/local/lib/python3.8/site-packages/alembic/config.py", line 581, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/usr/local/lib/python3.8/site-packages/alembic/config.py", line 575, in main
    self.run_cmd(cfg, options)
  File "/usr/local/lib/python3.8/site-packages/alembic/config.py", line 552, in run_cmd
    fn(
  File "/usr/local/lib/python3.8/site-packages/alembic/command.py", line 298, in upgrade
    script.run_env()
  File "/usr/local/lib/python3.8/site-packages/alembic/script/base.py", line 489, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/local/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.8/site-packages/alembic/util/compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 843, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "app/alembic/env.py", line 81, in <module>
    run_migrations_online()
  File "app/alembic/env.py", line 75, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python3.8/site-packages/alembic/runtime/environment.py", line 846, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python3.8/site-packages/alembic/runtime/migration.py", line 522, in run_migrations
    step.migration_fn(**kw)
  File "/app/app/alembic/versions/2cdd3b4db1ff_create_village_table.py", line 44, in upgrade
    op.create_index(
  File "<string>", line 8, in create_index
  File "<string>", line 3, in create_index
  File "/usr/local/lib/python3.8/site-packages/alembic/operations/ops.py", line 979, in create_index
    return operations.invoke(op)
  File "/usr/local/lib/python3.8/site-packages/alembic/operations/base.py", line 373, in invoke
    return fn(self, operation)
  File "/usr/local/lib/python3.8/site-packages/alembic/operations/toimpl.py", line 88, in create_index
    operations.impl.create_index(idx)
  File "/usr/local/lib/python3.8/site-packages/alembic/ddl/impl.py", line 283, in create_index
    self._exec(schema.CreateIndex(index))
  File "/usr/local/lib/python3.8/site-packages/alembic/ddl/impl.py", line 141, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1068, in _execute_ddl
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateTable) relation "idx_village_coordinates" already exists

[SQL: CREATE INDEX idx_village_coordinates ON village USING gist (coordinates)]

Expected behavior
I found that If I modify the create_village_table.py file with this code, manually its working:

revision = "6f134c73e76c"
down_revision = "91979b40eb38"
branch_labels = None
depends_on = None


def upgrade():
    # Create village table
    op.create_table(
        "village",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("name", sa.String(255), nullable=False),
        sa.Column(
            "coordinates",
            Geometry(geometry_type="POINT", srid=4326),
            nullable=False,
        ),
        sa.Column(
            "owner_id", sa.Integer, sa.ForeignKey("user.id"), nullable=True
        ),
        sa.Column("population", sa.Integer, nullable=False),
        sa.Column(
            "created_at",
            sa.DateTime,
            server_default=sa.func.now(),
            nullable=False,
        ),
    )


def downgrade():
    # Drop village table
    op.drop_table("village")

Versions.

• OS:
• Python: 3.7
• Alembic: 1.4.3
• SQLAlchemy: 1.3.19 + geoalchemy2 (latest)

[zzzeek]

what alembic revision --autogenerate does is dependent on what's already in your database when you run it, as well as what your environment sees.

the error message and the missing create table do not seem to be related. I can't see what would cause the error message since your non-working migration has nothing about a coordinates column or what I would assume is geoalchemy trying to create an index called idx_village_coordinates.

for the missing "create table", you'd want to make sure your models are correctly integrated with your env.py by using the MetaData collection from your models as well as ensuring all of your model files are imported when env.py runs.

Overall it's difficult to say what's happening here without a clear view of the database beforehand and full logging output.

[Denilson-Semedo]

Ensure that your Models Are Imported in Alembic's env.py. Alembic needs to "see" your models to detect changes. Modify your env.py to import all models and set target_metadata properly.