Schema name needed for autogeneration? #1624
-
|
When trying to autogenerate my second migration (just adding some fields to another table), I'm getting for every foreign key constraint. I'm using naming_convention like this: class Base(DeclarativeBase):
metadata = MetaData(
naming_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_%(referred_table_name)s_%(table_name)s_%(column_0_name)s",
"pk": "pk_%(table_name)s",
},
)The Abbreviations Model looks like this: class Abbrev(Base):
__tablename__ = "abbreviations"
id: Mapped[str] = mapped_column(primary_key=True)
expansion: Mapped[str] = mapped_column(nullable=False)
project_id: Mapped[int] = mapped_column(ForeignKey("project.id"), primary_key=True)
project: Mapped["Project"] = relationship(back_populates="abbrevs")The first and working migration for the table looked like this: op.create_table(
"abbreviations",
sa.Column("id", sa.String(), nullable=False),
sa.Column("expansion", sa.String(), nullable=False),
sa.Column("project_id", sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
["project_id"],
["project.id"],
name=op.f("fk_project_abbreviations_project_id"),
ondelete="CASCADE",
onupdate="CASCADE",
),
sa.PrimaryKeyConstraint("id", "project_id", name=op.f("pk_abbreviations")),
)Error Generated migration: def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.drop_constraint('fk_project_abbreviations_project_id', 'abbreviations', type_='foreignkey')
op.create_foreign_key(op.f('fk_project_abbreviations_project_id'), 'abbreviations', 'project', ['project_id'], ['id'])
op.drop_constraint('fk_chapter_chapter_parent_id', 'chapter', type_='foreignkey')
op.drop_constraint('fk_project_chapter_project_id', 'chapter', type_='foreignkey')
op.create_foreign_key(op.f('fk_chapter_chapter_parent_id'), 'chapter', 'chapter', ['parent_id'], ['id'])
op.create_foreign_key(op.f('fk_project_chapter_project_id'), 'chapter', 'project', ['project_id'], ['id'])
op.drop_constraint('fk_project_files_project_id', 'files', type_='foreignkey')
op.create_foreign_key(op.f('fk_project_files_project_id'), 'files', 'project', ['project_id'], ['id'])
op.drop_constraint('fk_settings_prompts_settings_id', 'prompts', type_='foreignkey')
op.create_foreign_key(op.f('fk_settings_prompts_settings_id'), 'prompts', 'settings', ['settings_id'], ['id'])
op.drop_constraint('fk_project_replacement_project_id', 'replacement', type_='foreignkey')
op.create_foreign_key(op.f('fk_project_replacement_project_id'), 'replacement', 'project', ['project_id'], ['id'])
op.add_column('settings', sa.Column('client_name', sa.String(), nullable=False))
op.add_column('settings', sa.Column('assistant_name', sa.String(), nullable=False))
op.add_column('settings', sa.Column('diagramming_tool', sa.String(), nullable=False))
op.add_column('settings', sa.Column('project_description', sa.String(), nullable=False))
op.add_column('settings', sa.Column('language_list', sa.ARRAY(sa.String()), nullable=False))
op.drop_constraint('fk_project_settings_project_id', 'settings', type_='foreignkey')
op.create_foreign_key(op.f('fk_project_settings_project_id'), 'settings', 'project', ['project_id'], ['id'])
op.drop_constraint('fk_project_srs_project_id', 'srs', type_='foreignkey')
op.drop_constraint('fk_chapter_srs_chapter_id', 'srs', type_='foreignkey')
op.create_foreign_key(op.f('fk_chapter_srs_chapter_id'), 'srs', 'chapter', ['chapter_id'], ['id'])
op.create_foreign_key(op.f('fk_project_srs_project_id'), 'srs', 'project', ['project_id'], ['id'])
# ### end Alembic commands ###Versions.
When adding a schema name and removing my old migrations and doing them cleanly with the schema name, it works as expected. Is this desired behavior? |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 4 replies
-
|
like I mentioned on the other issue, go over https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path and then also in your env.py, if you are using schemas that are not the default schema, you need to use https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.include_schemas and very often https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.include_name as well if you have a lot of schemas |
Beta Was this translation helpful? Give feedback.
-
|
Yes I tried using the default schema and a custom one. However the problem seems to be the onupdate/ondelete parts in my migration. When I remove them in the constraint creation and let it run on a clean db it works. As soon as I put it back in one constraint, I get the output for exactly that constraint. |
Beta Was this translation helpful? Give feedback.
-
|
I notice that your model given above does not include onupdate or ondelete cascades in the ForeignKey defs. But then your "first migration" does include them. Did you add these manually? Overall, Alembic if it sees a foreign key constraint that gains or loses an onupdate/ondelete cascade will seek to drop that constraint and re-create it the new way, since there is no ALTER CONSTRAINT. |
Beta Was this translation helpful? Give feedback.
-
|
Here is my parent model: class Project(Base):
__tablename__ = "project"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(unique=True)
settings: Mapped["Settings"] = relationship(
back_populates="project", cascade="all, delete-orphan", passive_deletes=True
)
srss: Mapped[list["SRS"]] = relationship(
back_populates="project", cascade="all, delete-orphan", passive_deletes=True
)
chapters: Mapped[list["Chapter"]] = relationship(
back_populates="project", cascade="all, delete-orphan", passive_deletes=True
)
replacements: Mapped[list["Replacements"]] = relationship(
back_populates="project", cascade="all, delete-orphan", passive_deletes=True
)
abbrevs: Mapped[list["Abbrev"]] = relationship(
back_populates="project", cascade="all, delete-orphan", passive_deletes=True
)
file_chunks: Mapped[list["FileChunk"]] = relationship(
back_populates="project", cascade="all, delete-orphan", passive_deletes=True
)The child looks like this: class Abbrev(Base):
__tablename__ = "abbreviations"
id: Mapped[str] = mapped_column(primary_key=True)
expansion: Mapped[str] = mapped_column(nullable=False)
project_id: Mapped[int] = mapped_column(ForeignKey("project.id"), primary_key=True)
project: Mapped["Project"] = relationship(back_populates="abbrevs")And the migration like this: op.create_table(
"project",
sa.Column("id", sa.Integer(), nullable=False),
sa.Column("name", sa.String(), nullable=False),
sa.PrimaryKeyConstraint("id", name=op.f("pk_project")),
sa.UniqueConstraint("name", name=op.f("uq_project_name")),
)
op.create_table(
"abbreviations",
sa.Column("id", sa.String(), nullable=False),
sa.Column("expansion", sa.String(), nullable=False),
sa.Column("project_id", sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
["project_id"],
["project.id"],
name=op.f("fk_project_abbreviations_project_id"),
ondelete="CASCADE",
onupdate="CASCADE",
),
sa.PrimaryKeyConstraint("id", "project_id", name=op.f("pk_abbreviations")),
)But I think I found the issue then. I need to put the onupdate in the model too right? |
Beta Was this translation helpful? Give feedback.
-
|
yes |
Beta Was this translation helpful? Give feedback.
Here is my parent model: