Alembic migration server default value is recognized as null when trying to update a column

Question

I am trying to do a migration to update the value of the column has_bubble_in_countries based on the has_bubble_v1 s column value.

I created before the upgrade() the table:

subscription_old_table = sa.Table(
        'Subscription',
        sa.MetaData(),
        sa.Column('id', sa.Unicode(255), primary_key=True, unique=True, nullable=False),
        sa.Column('has_bubble_v1', sa.Boolean, nullable=False, default=False),
        sa.Column('has_bubble_in_countries', MutableList.as_mutable(ARRAY(sa.Enum(Country))), nullable=False, default=[], server_default='{}')
    )

And then the upgrade() method looks like:

def upgrade():

    connection = op.get_bind()

    for subscription in connection.execute(subscription_old_table.select()):
        if subscription.has_bubble_v1:
            connection.execute(
                subscription_old_table.update().where(
                    subscription_old_table.c.id == subscription.id
                ).values(
                    has_bubble_in_countries=subscription.has_bubble_in_countries.append(Country.NL),
                )
            )
    # Then drop the column after the data has been migrated
    op.drop_column('Subscription', 'has_bubble_v1')

All the rows in the database of has_bubble_in_countries column have this value {} when I check the database using pgadmin's interface.

When the upgrade() function gets to the update method it throws this error:

sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "has_bubble_in_countries" of relation "Subscription" violates not-null constraint
DETAIL:  Failing row contains (keydsakwlkad, null, 2027-08-14 00:00:00+00,groot abonnement, big, {nl}, null, null, 2022-08-08 08:45:52.875931+00, 3482992, {}, f, null, null, null, t, 2011-05-23 08:55:20.538451+00, 2022-08-08 09:10:15.577283+00, ***null***).

[SQL: UPDATE "Subscription" SET has_bubble_in_countries=%(has_bubble_in_countries)s::country[] WHERE "Subscription".id = %(id_1)s]
[parameters: {'has_bubble_in_countries': None, 'id_1': '1pwohLmjftAZdIaJ'}]

The bolded value from the error is the value that is retrieved for the has_bubble_in_countries column even if it has a server_default='{}' and nullable=False.

Is there any possibility to add a configuration to alembic to recognize the server default s value when it is retrieved from the database? Or how can this be fixed?

Ian Wilson · Accepted Answer · 2022-12-28T22:48:55.630

I think the problem is actually that are you passing in the result of .append() which is None. Unlike other languages where it is common to return the altered list, append changes the list in place. I'm not sure that is a great idea for a core query result here but it seems to work. Also as far as I know, if you pass in NULL it doesn't trigger the default. The default is used when you pass in no value at all either when inserting or updating.

with Session(engine) as session, session.begin():

    for subscription in session.execute(subscription_old_table.select()):
        if subscription.has_bubble_v1:
            # Append here.
            subscription.has_bubble_in_countries.append(Country.NL)
            # Then set values:
            session.execute(
                subscription_old_table.update().where(
                    subscription_old_table.c.id == subscription.id
                ).values(has_bubble_in_countries=subscription.has_bubble_in_countries,
                )
            )

Maybe cloning the list and then adding the element like this would be safer and clearer:

has_bubble_in_countries=subscription.has_bubble_in_countries[:] + [Country.NL]

Alembic migration server default value is recognized as null when trying to update a column

1 Answers1