Unnecessary changes produced in migration file after running flask db migrate

[maham360]

Hi all,

After running flask db migrate I am coming across an issue where the new migration file produced has changes that don't need to be there. Here is a snippet of the migration file's code:

def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_index('ix_service_inbound_api_service_id', table_name='service_inbound_api')

op.drop_index('ix_service_inbound_api_updated_by_id', table_name='service_inbound_api')

op.drop_table('service_inbound_api')

op.alter_column('communication_items', 'name',

           existing_type=sa.VARCHAR(),

           type_=sa.Text(),

           existing_nullable=False)

op.create_foreign_key(None, 'complaints', 'notification_history', ['notification_id'], ['id'])

op.alter_column('dm_datetime', 'month_name',

           existing_type=sa.VARCHAR(),

           type_=sa.Text(),

           existing_nullable=False)

op.alter_column('dm_datetime', 'week_day_name',

           existing_type=sa.VARCHAR(),

           type_=sa.Text(),

           existing_nullable=False)`....etc

def downgrade():
# ### commands auto generated by Alembic - please adjust! ###

op.drop_index(op.f('ix_users_identity_provider_user_id'), table_name='users')

op.create_unique_constraint('users_identity_provider_user_id', 'users', ['identity_provider_user_id'])

op.alter_column('user_to_service', 'service_id',

           existing_type=postgresql.UUID(),

           nullable=True)

op.alter_column('user_to_service', 'user_id',

           existing_type=postgresql.UUID(),

           nullable=True)

op.drop_constraint(None, 'templates_history', type_='foreignkey')

op.drop_constraint(None, 'templates_history', type_='foreignkey')

op.alter_column('services_history', 'smtp_user',

           existing_type=sa.String(length=255),

           type_=sa.TEXT(),

           existing_nullable=True)

op.alter_column('services_history', 'sending_domain',

           existing_type=sa.String(length=255),

           type_=sa.TEXT(),

           existing_nullable=True)

op.alter_column('services_history', 'prefix_sms',

           existing_type=sa.BOOLEAN(),

           nullable=True)`....etc

These changes don't appear to be here for any particular case, so not sure why they're occurring.

[zzzeek]

hey there -

so the Alembic autogenerate process performs the task of reading information about all the tables, columns, and constraints it can find on a target database, which it does with a live database connection, and compares what it sees to a declared model in Python code, which is delivered to Alembic in the form of a collection called MetaData.

With the fragments that are listed out here, I assume this is somewhat of a paraphrase, as typically we would see the things that are added/dropped inside the"upgrade()" method to have a corresponding drop/add inside the "downgrade". Like, the "upgrade" here tries to drop an index ix_service_inbound_api_service_id, we'd expect to see it trying to add that index again in "downgrade".

So without the fragments shown indicating any pattern, I can't give an exact reason for each entry. however, the fundamental reason for all of them can be broken down into:

• things that are being "dropped" inside of "upgrade" are structures that were found in the target database, but are not present in the model
• things that are being "created" or "added" inside of "upgrade" are structures that were found in the model, but are not present in the target database
• columns that are "altered" inside upgrade indicate a change to a column seen in the model vs. what was seen in the database, such as this inside of "upgrade":

op.alter_column('communication_items', 'name',
           existing_type=sa.VARCHAR(),
           type_=sa.Text(),
           existing_nullable=False)

that means the database had a column with the VARCHAR datatype and the model has it as Text(), which Alembic is not considering as the same (try using String() for the model datatype for that one in particular).

• For "downgrade", all the above rules get reversed, since downgrade is trying to apply all the changes in "upgrade" in reverse.

There's also some other suspicious things like the constraints with None for the name (op.drop_constraint(None, 'templates_history', type_='foreignkey')) this is something you'd fix up by making sure ForeignKey / ForeignKeyConstraints have a name of some kind, which there's some background at https://alembic.sqlalchemy.org/en/latest/naming.html .

Overall it looks like you have an existing database with structures in it that were not created directly by the model you have (or have elements that were added to migrations manually that aren't in the model), so trying to reconcile these things for the first time can produce a lot of mismatches. There are solutions for all of them but they have to be taken on a case by case basis.

[maham360]

Thanks this cleared up a lot of confusion. For this code:

op.alter_column('communication_items', 'name',
           existing_type=sa.VARCHAR(),
           type_=sa.Text(),
           existing_nullable=False)

The existing datatype for the "name" column in the model is String. See code below:

class CommunicationItem(db.Model):
    __tablename__ = "communication_items"

    id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    va_profile_item_id = db.Column(db.Integer, nullable=False)
    name = db.Column(db.String(), nullable=False)

So I'm not sure why Alembic is detecting changes from VARCHAR to Text.

[zzzeek]

what database backend is that (including server version), and is this the most recent version of Alembic (1.7.7) ? I tried a model like the above on PostgreSQL which has a TEXT type that's functionally equivalent to VARCHAR with no length, but PG reports it as "character varying", so no false positive is generated.

[maham360]

I am using SQL Alchemy version 1.3.19 and Flask version 1.1.1 and Alembic version 1.7.5 (not sure how to update it to 1.7.7)

    op.alter_column('dm_datetime', 'month_name',
               existing_type=sa.VARCHAR(),
               type_=sa.Text(),
               existing_nullable=False)
    op.alter_column('dm_datetime', 'week_day_name',
               existing_type=sa.VARCHAR(),
               type_=sa.Text(),
               existing_nullable=False)
    op.alter_column('dm_datetime', 'quartal',
               existing_type=sa.VARCHAR(),
               type_=sa.Text(),
               existing_nullable=False)
    op.alter_column('dm_datetime', 'year_quartal',
               existing_type=sa.VARCHAR(),
               type_=sa.Text(),
               existing_nullable=False)
    op.alter_column('dm_datetime', 'year_month',
               existing_type=sa.VARCHAR(),
               type_=sa.Text(),
               existing_nullable=False)
    op.alter_column('dm_datetime', 'year_calendar_week',
               existing_type=sa.VARCHAR(),
               type_=sa.Text(),
               existing_nullable=False)

Also the code above is also another snippet of the migration file. It's kind of weird how these changes were picked up because the existing data type for all the columns above in the model are all "Text" so I'm not sure what is being changed? Varchar to Text?

[maham360]

Thanks for this. I am able to get into the database and see the tables. For this code:

    op.alter_column('dm_datetime', 'month_name',
               existing_type=sa.VARCHAR(),
               type_=sa.Text(),
               existing_nullable=False)

I can see in the database:

       Column       |            Type             | Collation | Nullable | Default 
--------------------+-----------------------------+-----------+----------+---------
 month_name         | character varying           |           | not null

month_name has VARCHAR as the datatype and in the model it's Text. So I guess I'm not understanding what datatype to convert Text to that is equivalent to VARCHAR so I won't get this alteration showing up in the migration file.

[CaselIT]

month_name has VARCHAR as the datatype and in the model it's Text. So I guess I'm not understanding what datatype to convert Text to that is equivalent to VARCHAR so I won't get this alteration showing up in the migration file.

varchar is not the same as text in postgresql. See its documentation https://www.postgresql.org/docs/14/datatype-character.html

alembic seems to be working correctly: the db has varchar, the model has text, so a migration to produce such a change is produced. Either modify the model so that they have a varchar type (such as string()) or apply the migration to have the db use text

[maham360]

Thank you, I was able to correct some migration alterations by changing .text to .string in the model. There are a lot of different alterations I'm not sure how to fix such as op.drop_index('ix_service_inbound_api_service_id', table_name='service_inbound_api') and

op.alter_column('notifications', 'international',
               existing_type=sa.BOOLEAN(),
               nullable=False)

Here is the full def upgrade and def downgrade functions with all the alterations occuring. I would really appreciate it if anyone here can point me to some useful documentation on how to correct some of these.

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('ix_service_inbound_api_service_id', table_name='service_inbound_api')
    op.drop_index('ix_service_inbound_api_updated_by_id', table_name='service_inbound_api')
    op.drop_table('service_inbound_api')
    op.create_foreign_key(None, 'complaints', 'notification_history', ['notification_id'], ['id'])
    op.create_index(op.f('ix_fido2_sessions_user_id'), 'fido2_sessions', ['user_id'], unique=True)
    op.create_index(op.f('ix_inbound_numbers_service_id'), 'inbound_numbers', ['service_id'], unique=False)
    op.alter_column('notification_history', 'international',
               existing_type=sa.BOOLEAN(),
               nullable=False)
    op.alter_column('notification_history', 'rate_multiplier',
               existing_type=sa.NUMERIC(),
               type_=sa.Float(),
               existing_nullable=True)
    op.alter_column('notifications', 'notification_status',
               existing_type=sa.TEXT(),
               type_=sa.String(),
               existing_nullable=True)
    op.alter_column('notifications', 'international',
               existing_type=sa.BOOLEAN(),
               nullable=False)
    op.alter_column('notifications', 'rate_multiplier',
               existing_type=sa.NUMERIC(),
               type_=sa.Float(),
               existing_nullable=True)
    op.drop_index('ix_notifications_service_created_at', table_name='notifications')
    op.alter_column('rates', 'rate',
               existing_type=sa.NUMERIC(),
               type_=sa.Float(),
               existing_nullable=False)
    op.alter_column('service_callback', 'bearer_token',
               existing_type=sa.VARCHAR(),
               nullable=False)
    op.drop_index('ix_service_callback_api_service_id', table_name='service_callback')
    op.drop_index('ix_service_callback_api_updated_by_id', table_name='service_callback')
    op.create_index(op.f('ix_service_callback_service_id'), 'service_callback', ['service_id'], unique=False)
    op.create_index(op.f('ix_service_callback_updated_by_id'), 'service_callback', ['updated_by_id'], unique=False)
    op.create_unique_constraint('uix_service_callback_channel', 'service_callback', ['service_id', 'callback_channel'])
    op.alter_column('service_callback_history', 'bearer_token',
               existing_type=sa.VARCHAR(),
               nullable=False)
    op.drop_index('ix_service_callback_api_history_service_id', table_name='service_callback_history')
    op.drop_index('ix_service_callback_api_history_updated_by_id', table_name='service_callback_history')
    op.create_index(op.f('ix_service_callback_history_service_id'), 'service_callback_history', ['service_id'], unique=False)
    op.create_index(op.f('ix_service_callback_history_updated_by_id'), 'service_callback_history', ['updated_by_id'], unique=False)
    op.alter_column('services', 'sending_domain',
               existing_type=sa.TEXT(),
               type_=sa.String(length=255),
               existing_nullable=True)
    op.alter_column('services', 'smtp_user',
               existing_type=sa.TEXT(),
               type_=sa.String(length=255),
               existing_nullable=True)
    op.alter_column('services_history', 'prefix_sms',
               existing_type=sa.BOOLEAN(),
               nullable=False)
    op.alter_column('services_history', 'sending_domain',
               existing_type=sa.TEXT(),
               type_=sa.String(length=255),
               existing_nullable=True)
    op.alter_column('services_history', 'smtp_user',
               existing_type=sa.TEXT(),
               type_=sa.String(length=255),
               existing_nullable=True)
    op.create_foreign_key(None, 'templates_history', 'provider_details', ['provider_id'], ['id'])
    op.create_foreign_key(None, 'templates_history', 'communication_items', ['communication_item_id'], ['id'])
    op.alter_column('user_to_service', 'user_id',
               existing_type=postgresql.UUID(),
               nullable=False)
    op.alter_column('user_to_service', 'service_id',
               existing_type=postgresql.UUID(),
               nullable=False)
    op.drop_constraint('users_identity_provider_user_id', 'users', type_='unique')
    op.create_index(op.f('ix_users_identity_provider_user_id'), 'users', ['identity_provider_user_id'], unique=True)
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_users_identity_provider_user_id'), table_name='users')
    op.create_unique_constraint('users_identity_provider_user_id', 'users', ['identity_provider_user_id'])
    op.alter_column('user_to_service', 'service_id',
               existing_type=postgresql.UUID(),
               nullable=True)
    op.alter_column('user_to_service', 'user_id',
               existing_type=postgresql.UUID(),
               nullable=True)
    op.drop_constraint(None, 'templates_history', type_='foreignkey')
    op.drop_constraint(None, 'templates_history', type_='foreignkey')
    op.alter_column('services_history', 'smtp_user',
               existing_type=sa.String(length=255),
               type_=sa.TEXT(),
               existing_nullable=True)
    op.alter_column('services_history', 'sending_domain',
               existing_type=sa.String(length=255),
               type_=sa.TEXT(),
               existing_nullable=True)
    op.alter_column('services_history', 'prefix_sms',
               existing_type=sa.BOOLEAN(),
               nullable=True)
    op.alter_column('services', 'smtp_user',
               existing_type=sa.String(length=255),
               type_=sa.TEXT(),
               existing_nullable=True)
    op.alter_column('services', 'sending_domain',
               existing_type=sa.String(length=255),
               type_=sa.TEXT(),
               existing_nullable=True)
    op.drop_index(op.f('ix_service_callback_history_updated_by_id'), table_name='service_callback_history')
    op.drop_index(op.f('ix_service_callback_history_service_id'), table_name='service_callback_history')
    op.create_index('ix_service_callback_api_history_updated_by_id', 'service_callback_history', ['updated_by_id'], unique=False)
    op.create_index('ix_service_callback_api_history_service_id', 'service_callback_history', ['service_id'], unique=False)
    op.alter_column('service_callback_history', 'bearer_token',
               existing_type=sa.VARCHAR(),
               nullable=True)
    op.drop_constraint('uix_service_callback_channel', 'service_callback', type_='unique')
    op.drop_index(op.f('ix_service_callback_updated_by_id'), table_name='service_callback')
    op.drop_index(op.f('ix_service_callback_service_id'), table_name='service_callback')
    op.create_index('ix_service_callback_api_updated_by_id', 'service_callback', ['updated_by_id'], unique=False)
    op.create_index('ix_service_callback_api_service_id', 'service_callback', ['service_id'], unique=False)
    op.alter_column('service_callback', 'bearer_token',
               existing_type=sa.VARCHAR(),
               nullable=True)
    op.alter_column('rates', 'rate',
               existing_type=sa.Float(),
               type_=sa.NUMERIC(),
               existing_nullable=False)
    op.create_index('ix_notifications_service_created_at', 'notifications', ['service_id', 'created_at'], unique=False)
    op.alter_column('notifications', 'rate_multiplier',
               existing_type=sa.Float(),
               type_=sa.NUMERIC(),
               existing_nullable=True)
    op.alter_column('notifications', 'international',
               existing_type=sa.BOOLEAN(),
               nullable=True)
    op.alter_column('notifications', 'notification_status',
               existing_type=sa.String(),
               type_=sa.TEXT(),
               existing_nullable=True)
    op.alter_column('notification_history', 'rate_multiplier',
               existing_type=sa.Float(),
               type_=sa.NUMERIC(),
               existing_nullable=True)
    op.alter_column('notification_history', 'international',
               existing_type=sa.BOOLEAN(),
               nullable=True)
    op.drop_index(op.f('ix_inbound_numbers_service_id'), table_name='inbound_numbers')
    op.drop_index(op.f('ix_fido2_sessions_user_id'), table_name='fido2_sessions')
    op.drop_constraint(None, 'complaints', type_='foreignkey')
    op.create_table('service_inbound_api',
    sa.Column('id', postgresql.UUID(), autoincrement=False, nullable=False),
    sa.Column('service_id', postgresql.UUID(), autoincrement=False, nullable=False),
    sa.Column('url', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('bearer_token', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('created_at', postgresql.TIMESTAMP(), autoincrement=False, nullable=False),
    sa.Column('updated_at', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.Column('updated_by_id', postgresql.UUID(), autoincrement=False, nullable=False),
    sa.Column('version', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['service_id'], ['services.id'], name='service_inbound_api_service_id_fkey'),
    sa.ForeignKeyConstraint(['updated_by_id'], ['users.id'], name='service_inbound_api_updated_by_id_fkey'),
    sa.PrimaryKeyConstraint('id', name='service_inbound_api_pkey')
    )
    op.create_index('ix_service_inbound_api_updated_by_id', 'service_inbound_api', ['updated_by_id'], unique=False)
    op.create_index('ix_service_inbound_api_service_id', 'service_inbound_api', ['service_id'], unique=False)

# ### end Alembic commands ###

[CaselIT]

I would really appreciate it if anyone here can point me to some useful documentation on how to correct some of these.

I'm not sure what you are seeking here.

What Alembic autogenerates does it compare the metatada with what's in the db and render the difference that it can detect.
You seem to have some differences between the how the metadata is defined and what's actually in the db, so you will have to decide what's correct and what's not and act accordingly (ie update the metadata or update the db)

[maham360]

Ah ok, I just realized that lol thanks for your help