Alembic generate unnecessary alter commands to column for Identity

[indiVar0508]

Hi

Recently we added dialect_compat for our application DB referring this for postgresDB, so when application is setup automatically all existing ORM models would be leveraging Identity for autoincrement feature.

from sqlalchemy.schema import CreateColumn
from sqlalchemy.ext.compiler import compiles


@compiles(CreateColumn, 'postgresql')
def use_identity(element, compiler, **kw):
    text = compiler.visit_create_column(element, **kw)
    text = text.replace(
        "BIGSERIAL NOT NULL", " BIGINT GENERATED BY DEFAULT AS IDENTITY"
     )
    return text

after adding this forsome reason alembic is creating a revision file for me where it is trying to remove Identity in upgrade method i assume it is because my ORM model column does not have Identity defined in Column()?

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('article', 'id',
               existing_type=sa.BIGINT(),
               server_default=None,
               existing_nullable=False,
               autoincrement=True)
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('article', 'id',
               existing_type=sa.BIGINT(),
               server_default=sa.Identity(always=False, start=1, increment=1, minvalue=1, maxvalue=9223372036854775807, cycle=False, cache=1),
               existing_nullable=False,
               autoincrement=True)
    # ### end Alembic commands ###

Please refer to MRC for same,

dummy_file.py

from sqlalchemy.schema import CreateColumn
from sqlalchemy.ext.compiler import compiles


@compiles(CreateColumn, 'postgresql')
def use_identity(element, compiler, **kw):
    text = compiler.visit_create_column(element, **kw)
    text = text.replace(
        "BIGSERIAL NOT NULL", " BIGINT GENERATED BY DEFAULT AS IDENTITY"
     )
    return text

from sqlalchemy import Column, BigInteger, Unicode, UnicodeText, create_engine, Identity
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import create_session

Base = declarative_base()


class Article(Base):

    __tablename__ = 'article'
    id = Column(BigInteger,  primary_key=True, autoincrement=True)
    name = Column(Unicode(255))
    content = Column(UnicodeText)


engine = create_engine('postgresql://postgres:postgres@localhost:5432/test', echo=True)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)  # Just to reproduce error assume my older migration files are generating these ORM Models

if i do alembic revision --autogenerate -m "First commit"
My expectation was nothing should be generated a simple empty upgrade / downgrade method shall be generated but this generates above mentioned methods in revision file.

But if i Do Column(BigInteger, Identity(), primary_key=True, autoincrement=True) then this does not happen,

I assumed alembic might be looking in Column attributes somewhere in package and saw that Identity is not specified but DB has Identity based table specified due to complies thing, so it just does not care for these complie method.

Is there a way where i don't have to update all Column method with Identity for my ORM models for Alembic to not upgrade Models?

[CaselIT]

Hi,

i assume it is because my ORM model column does not have Identity defined in Column()?

that's correct. use Identity in the orm model columns or setup an appropriate filter to atogenerate https://alembic.sqlalchemy.org/en/latest/autogenerate.html#controlling-what-to-be-autogenerated

My expectation was nothing should be generated a simple empty upgrade / downgrade method shall be generated but this generates above mentioned methods in revision file.

alembic has no way of knowing that you are modifying the create table sql to add identity. All it sees is that the table in the metadata has no identity, while the db has it, so it renders the removal of it.

[indiVar0508]

Hi @CaselIT ,

Thanks for documentation, I was following the doc and created a method but for some reason i only get table ,
i am not sure what i am doing wrong, i tried with my dummy example shown above, there also i got same.

def include_object(object, name, type_, reflected, compare_to):
    print(type_ == "column")  # False
    print(type_ == "table")  # True
    if (type_ == "column" and ...):  # check to identify if revision happening to this col is related to Identity.
        return False
    else:
        return True

Also, to check if the comparison/migration is happening for Indentity how can i control it?

Thanks

[indiVar0508]

Hi @CaselIT ,

Any thoughts / feedback on this one?

[CaselIT]

That's strange, either include_names or include_object is called for each column.

Note that that function is called many times, once for each object that's been processed.

Also, to check if the comparison/migration is happening for Indentity how can i control it?

the comparison here is called for each table, so for the table in question you would get in object the table from your metadata and in compare_to the same column as reflected from the database. You can then check if the column in object is one where you have manually added an identity

[indiVar0508]

thanks @CaselIT ,

tried again today it worked magically using this condition it works now! :D

def include_object(object, name, type_, reflected, compare_to):
    if (type_ == "column" and object.primary_key is True):
        if object.identity is None and compare_to.identity:
            return False
    return True

[sl-tarvin]

I was having this exact issue with my ORM models using Snowflake. The include_object logic shared by @indiVar0508 successfully filtered out the false-positive change detection for my primary keys that didn't have sa.Identity() defined on the local side.
Thank you!