How to alter foreignkey with Alembic

Question

I handle my PostgreSQL migrations with Alembic. This is how I create a table items:

from alembic import op
import sqlalchemy as sa

def upgrade():
    items_table = op.create_table(
        "items",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column("user_id", UUID(as_uuid=True), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(
            ["user_id"],
            ["users.id"],
        ),
    )

I'd like to make a new migration file to add ondelete="CASCADE" after the sa.ForeignKeyConstraint(...). How can I do this using sqlalchemy? How do I drop the ForeignKeyConstraint and create a new one? Or do I need to drop the whole table and create it again?

Answer 1

It's something that alembic can actually autogenerate, it would drop current constraint and create a new one. I have a pretty simple model in this repository

We can add a self referential relationship as an example:

parent_id: Mapped[int] = Column(ForeignKey("book.id"))

def upgrade() -> None:
    op.add_column("book", sa.Column("parent_id", sa.Integer(), nullable=True))
    op.create_foreign_key(
        op.f("fk_book_parent_id_book"), "book", "book", ["parent_id"], ["id"]
    )


def downgrade() -> None:
    op.drop_constraint(op.f("fk_book_parent_id_book"), "book", type_="foreignkey")
    op.drop_column("book", "parent_id")

If we add ondelete="CASCADE" to it alembic would create new constraint and drop the old one:

parent_id: Mapped[int] = Column(ForeignKey("book.id", ondelete="CASCADE"))

def upgrade() -> None:
    op.drop_constraint("fk_book_parent_id_book", "book", type_="foreignkey")
    op.create_foreign_key(
        op.f("fk_book_parent_id_book"),
        "book",
        "book",
        ["parent_id"],
        ["id"],
        ondelete="CASCADE",
    )


def downgrade() -> None:
    op.drop_constraint(op.f("fk_book_parent_id_book"), "book", type_="foreignkey")
    op.create_foreign_key(
        "fk_book_parent_id_book", "book", "book", ["parent_id"], ["id"]
    )

You may need to add

compare_type=True,
compare_server_default=True,

to your context.configure call in alembic/env.py, it's covered in alembic documentation