Alembic Migration Failure: Default value of column is not constant

[larry0161]

Hello! I have a python project that used sqlalchemy 1.2.18, alembic 1.0.7 and pysqlcipher3 1.0.3 to manage a local SQLite DB. I recently upgraded the project to python 3.10 and in doing so, updated various packages including sqlalchemy -> 1.4.39, alembic -> 1.13.1 and pysqlcipher3 -> 1.2.0. My application was running fine until I tried to run an autogenerated migration. The migration fails when attempting to create the temporary table. The source of error is an existing column that is not altered in the new migration. The error states the following:

pysqlcipher3.dbapi2.OperationalError: default value of column [existing_col] is not constant.

The existing code for the column in question is the following:

existing_col = sql.Column(
sql.String(16),
default="DEF",
server_default=sql.sql.expression.text("DEF")
)
The default values are constant so I do not know why the error states that they are not constant.

I've found that if I rollback the updates to use alembic==1.0.10, there is no longer an issue with migrations. As soon as I update to alembic==1.0.11, the error returns. When reading the release history, I found the following snippet for 1.0.11 which could be pertinent: """ SQLite server default reflection will ensure parenthesis are surrounding a column default expression that is detected as being a non-constant expression, such as a datetime() default, to accommodate for the requirement that SQL expressions have to be parenthesized when being sent as DDL. Parenthesis are not added to constant expressions to allow for maximum cross-compatibility with other dialects and existing test suites (such as Alembic’s), which necessarily entails scanning the expression to eliminate for constant numeric and string values. The logic is added to the two “reflection->DDL round trip” paths which are currently autogenerate and batch migration. Within autogenerate, the logic is on the rendering side, whereas in batch the logic is installed as a column reflection hook. """

I've tried some suggested solutions including adding single quotes to the server_default ("'DEF'"),escaping the single quotes ("'(DEF)'"), adding literal bindings, and deleting the default argument. None of them worked.

The migrations work if I create a new database but this would be inconvenient for app users since they will essentially have their history reset. Any insight as to why the migrations are complaining about non-constant values despite them being constant would be greatly appreciated. Thanks!

[larry0161]

Hey everyone,

I was able to fix the issue but was hoping to get some insight as to why it works.

There was a bit of misdirection with the warning. After decrypting the database and removing the need for pysqlcipher3, the new errors showed that the problem stemmed from a new column I was adding to the database. I was adding the column as shown below:

new_column = sql.Column(sql.String(64),server_default=sql.sql.expression.text("CONSTANT"))

Although I had made all of my existing columns using the same format, this new column was throwing a "Default value of column is not constant" error. I was able to fix it by replacing the server_default to "CONSTANT" as shown below:

new_column = sql.Column(sql.String(64),server_default="CONSTANT")

After making the change, everything worked. I looked at the migration file and it seems that text() adds parenthesis to the default value so it displays as

server_default=sa.text('(CONSTANT)')

whereas the fixed migration file shows (As well as the migration file generated using alembic 0.10.0)

server_default='CONSTANT'

Any insight as to why this fixed the issue would be greatly appreciated. thanks!

[zzzeek]

autogenerated migrations are not guaranteed to work so overall the solution to the original issue is to manually adjust the auto-generated migration to make it work. unless by "reflection" you're referring to the batch migration feature. it's not really clear which feature you are using without code shown in full context

[larry0161]

Hey zzzeek! So sorry about the delay. I appreciate your response. Apologies for my lack of terminology and know-how of alembic, I am relatively new to the tool. I am using batch migrations for my project. Are the autogenerated migration files constructed differently if they are going to be used for reflection?

[zzzeek]

can you show me the batch migration code?