Closed quentin-roche closed 5 months ago
you dont need op.f() if your constraints have names already or if you arent using naming conventions, can I see an example of a "bad" migration as well as your naming convention setup? thanks
The goal is to make sure that constraint names get truncated the same way as SQLAlchemy does (truncate_and_render_constraint_name). SQLAlchemy truncate names because MySQL does not accept contraint names that are more than 64 character long.
that's not the purpose of op.f(). op.f() is strictly to override constraint naming conventions from taking place. if your names are hardcoded more than 64 chars that's a bug on your end and you need to fix that
I'm using naming convention. I did not create the names. This is my SQLAlchemy setup:
from sqlalchemy.orm.decl_api import declarative_base
from sqlalchemy.schema import MetaData
convention = {
"ix": "ix_%(column_0_label)s",
"uq": "uq_%(table_name)s_%(column_0_name)s",
"ck": "ck_%(table_name)s_%(constraint_name)s",
"fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
"pk": "pk_%(table_name)s",
}
Base = declarative_base(metadata=MetaData(naming_convention=convention))
metadata: MetaData = Base.metadata
When using metadata.create_all
the names in the database are correctly truncated. But this is part of one of the downgrade generated by Alembic:
def upgrade() -> None:
[...]
with op.batch_alter_table("single_source_of_truth", schema=None) as batch_op:
batch_op.add_column(
sa.Column("asset_models_version_id", sa.Integer(), nullable=True)
)
batch_op.add_column(
sa.Column("asset_financials_version_id", sa.Integer(), nullable=True)
)
batch_op.add_column(
sa.Column("financial_context_version_id", sa.Integer(), nullable=True)
)
batch_op.drop_constraint(
"fk_single_source_of_truth_solar_pv_version_id_file_version",
type_="foreignkey",
)
batch_op.drop_constraint(
"fk_single_source_of_truth_biogas_plant_version_id_file_version",
type_="foreignkey",
)
batch_op.drop_constraint(
"fk_single_source_of_truth_air_separation_unit_version_id_file_version",
type_="foreignkey",
)
batch_op.drop_constraint(
"fk_single_source_of_truth_saf_fischer_tropsch_version_id_file_version",
type_="foreignkey",
)
batch_op.drop_constraint(
"fk_single_source_of_truth_desalination_plant_version_id_file_version",
type_="foreignkey",
)
batch_op.drop_constraint(
"fk_single_source_of_truth_hydrogen_longterm_storage_version_id_file_version",
print(len('fk_single_source_of_truth_hydrogen_longterm_storage_version_id_file_version'))
75
It might have something to do with the fact that I may have use SQLite during the migration generation.
Describe the bug
When alembic generates the code for migrations, it uses
op.f("constraint_name")
to encapsulate contraint names. The goal is to make sure that constraint names get truncated the same way as SQLAlchemy does (truncate_and_render_constraint_name
). SQLAlchemy truncate names because MySQL does not accept contraint names that are more than 64 character long.I don't know why (did not have the time to search for too long) but sometimes Alembic will not add batch_op.f() in batch operation. It seems to me that it does not add during:
This behaviour of not adding the batch_op.f() seem to affect:
Expected behavior
Adding
op.f("constraint_name")
to truncate all contraint names.To Reproduce
I can't share the full code but this is my env file:
Error
I don't remember if it was an error or the contraint being recreating for each migration. (It was few weeks ago)
Versions
Dirty fix
I had many old migration that were affected and did not want to modify manually thousands of lines of codes. The fix is more for people that already have migrations with missing
batch_op.f()
and don't want to modify them.If you need some more info, feel free. This is not really an issue for me anymore since I got a fix but it might help someone understand what is going wrong with its own setup
Have a nice day!