Recent migration generated by db migrate wants to change all IDENTITY columns to INTEGER

[rosensama]

I have a pretty old app where I've used flask-migrate since the start. Love it. The app is pretty stable so I haven't had to touch my schema for a while. I'm guessing that something changed as I've updated sqlalchemy and other packages since the last time I needed db migrate that is triggering this. I haven't had any other issues with my database access layer and the app is actively used.

I'm hoping for some advice / pointers on what's going on. Right now I'm commenting out all the wayward commands in the migration I need to apply this month. Since the app is so old maybe it was created with legacy DDL? Maybe there's a migration step I need to apply to my models? I haven't found any good search results in this repo or in Google. Thanks in advance for your time here.

Relevant versions:

• flask-migrate: 4.1.0
• flask-sqlalchemy: 3.1.1
• sqlalchemy: 2.0.37
• flask: 3.1.0
• psycopg2: 2.9.10

The database server is postgresql 14.15 (Ubuntu 14.15-1.pgdg24.04+1)

Many of my models use a "standard" primary key column definition

    id = Column(Integer, primary_key=True)

The output from \d in psql

      Column       |            Type             | Collation | Nullable |             Default              
-------------------+-----------------------------+-----------+----------+----------------------------------
 id                | integer                     |           | not null | generated by default as identity

When generating my most recent migration for every one of those columns, the following command was generated.

        batch_op.alter_column(
            "id",
            existing_type=sa.INTEGER(),
            server_default=None,
            existing_nullable=False,
            autoincrement=True,
        )

The matching downgrade command for each is

        batch_op.alter_column(
            "id",
            existing_type=sa.INTEGER(),
            server_default=sa.Identity(
                always=False,
                start=1,
                increment=1,
                minvalue=1,
                maxvalue=2147483647,
                cycle=False,
                cache=1,
            ),
            existing_nullable=False,
            autoincrement=True,
        )

If I try to execute the generated update commands (db upgrade), pycopg2 returns the following error:

psycopg2.errors.SyntaxError: column "id" of relation "user" is an identity column
HINT:  Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead.

[miguelgrinberg]

What does the migration that defined this column originally say?

[rosensama]

    op.create_table('user',
    sa.Column('id', sa.Integer(), nullable=False),
    . . . 
    sa.PrimaryKeyConstraint('id', name=op.f('pk_public_user')),
    )

[rosensama]

If it helps, I believe I was using SQLAlchemy 1.3 and Flask-Migrate 3.1 the last time I created an id column. Then I migrated to 2.0 and 4.1 and could have missed a step. Some of the affected tables would have been created with older versions.

[miguelgrinberg]

If I have to make a guess, I'd say that the database you are generating these migrations against does not match the definitions in your models and your previous migrations, and for that reason it is trying to make a correction to make everything match. I guess you can inspect the schema of the affected table(s) to see how the id is defined. The original migration that you copied above appears to agree with the definition that Flask-Migrate is trying to migrate to, so you either have some other migration that changed these ids to the other format, or else the database was changed outside of this application.

One thing you can try is to generate a new database by running all your migrations, and then see if a correct migration is generated when using the clean db as reference.

[rosensama]

Thanks. I'll look into that. Although I'm pretty sure that the most recent affected tables have only been maintained through editing models and invoking Flask-Migrate. I can't remember the last time I executed DDL outside of that stack.

[rosensama]

I still have the same issue. I created an empty database, changed my app config to point to it, and ran flask db upgrade. Then I ran flask db migrate -m "DELETE ME - should be no-op". It creates a new migration full of the same alter_column commands in upgrade() and downgrade().

I found the culprit. It's from SQLA 1.3 docs, but we didn't comment WHY we added it way back when. Might have needed it to work with our audit trail generation?
https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#postgresql-10-identity-columns

@compiles(CreateColumn, "postgresql")
def use_identity(element, compiler, **kw):
    text = compiler.visit_create_column(element, **kw)
    text = text.replace("SERIAL", "INT GENERATED BY DEFAULT AS IDENTITY")
    return text

If I comment that out and repeat the experiment of applying all migrations to an empty database, flask db migrate produce a new migration. It does log this for the tables it used to try to change.

The ID column is no longer identity, but

     Column      |       Type        | Collation | Nullable |                     Default                      
-----------------+-------------------+-----------+----------+--------------------------------------------------
 id              | integer           |           | not null | nextval('user'::regclass)

INFO  [alembic.ddl.postgresql] Detected sequence named 'user_id_seq' as owned by integer column 'user(id)', assuming SERIAL and omitting

I am curious what changed that the migration stack just started picking it up as needing a change. Maybe SQLA 2.x doesn't like it any more? Or a postgres version update obsoleted this? And something funky about this causes alembic to recognize a move from identity, but not generate code that does this correctly?

[miguelgrinberg]

Flask-Migrate doesn't do this work, this is Alembic. What changed is that Alembic has improved and is able to pick up more differences when you ask for an autogenerated migration.

[rosensama]

Thanks so much for the help. I see a path forward so that alembic will see my existing schema as matching my model definitions. Basically, update to the currently documented way of declaring IDENTITY columns (added in 1.4) https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#postgresql-10-and-above-identity-columns.

1. Remove the decorated use_identity function from 1.3 docs
2. Update the id column defs to be declared with IDENTITY

id = Column(Integer, Identity(), primary_key=True)

With the above in place flask db migrate no longer detects changes to the id columns.

Then similarly update existing migrations adding Identity() to the id columns to be consistent.

sa.Column('id', sa.Integer(), sa.Identity(), nullable=False),