NB: When using SQLModel + Alembic, make sure ForeignKeys are named!

[thomasborgen]

When using alembic to create migrations from sqlmodel Alembic does not automatically add names to ForeignKey constraints and other constraints.

TLDR

Setup sqlalchemy Metadata with naming conventions so that foreign keys and other constraints can be referred to in later migrations. Otherwise many FKs can't be changed or removed.

from pydantic import ConfigDict
from sqlmodel import Field, SQLModel

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",
}


class SimpleModel(SQLModel):
    """Base model for everything."""

    model_config = ConfigDict(
        protected_namespaces=(),
    )  # type: ignore


SimpleModel.metadata.naming_convention = convention

Longer version

Why is this important?

Well if you have a model with more than 1 FK like

class ProductBase(SimpleModel):
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)
    variant_id: uuid.UUID = Field(foreign_key="variant.id")
    parent_id: uuid.UUID | None = Field(foreign_key="product.id")

class Product(ProductBase, table=True):
     # Foreign key relationships
    variant: Variant = Relationship(back_populates="products")
    parent: Optional["Product"] = Relationship(
        back_populates="children",
        sa_relationship_kwargs={
            "remote_side": "Product.id"  # Refers to the table Class
        },
    )
    # Reverse populations
    children: list["Product"] = Relationship(back_populates="parent")

This creates an alembic migration that looks something like:

    op.create_table(
        "product",
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("variant_id", sa.Uuid(), nullable=False),
        sa.Column("parent_id", sa.Uuid(), nullable=True),
        sa.ForeignKeyConstraint(
            ["parent_id"],
            ["product.id"],
        ),
        sa.ForeignKeyConstraint(
            ["variant_id"],
            ["variant.id"],
        ),
        sa.PrimaryKeyConstraint("id"),
    )

ForeignKeyConstraint has a keyword argument name: _ConstraintNameArgument = None, This is not set by default in SQLModel models.

So if I want to change a FK or even if i want to remove a FK i can't!! Because there is no name sql can refer to when deleting it.

This can cause so many issues down the line. Fix is in the TLDR, add sqlalchemy naming conventions.

Given how userfriendly SQLModel is and how easy it is to work with because it hides a lot of complexity I think that SQLModel should define these names by default but also make it easy to set up custom ones with ConfigDict(). Maybe having an argument in Relationship called name could also make sense.