Removing orphanated entries from the database after the parent is deleted

[NaviteLogger]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

class Product(SQLModel, table=True):
    """Products model."""

    __allow_unmapped__ = True

    id: UUID = Field(default=uuid.uuid4(), primary_key=True, nullable=False)
    name: str = Field(nullable=False)
    date: datetime = Field(default=datetime.now(timezone.utc), nullable=False)

    prices: list[Prices] = Relationship(
        back_populates="product",
        sa_relationship_kwargs={"cascade": "all, delete, delete-orphan"},
    )

    def __init__(self, name: str) -> None:
        """Initialize the product model."""
        self.id = uuid.uuid4()
        self.name = name
        self.date = datetime.now(timezone.utc)

    def delete_product(self) -> Response:
        """Delete the product from the database."""
        with Session(engine) as session:
            session.delete(self)
            session.commit()
            return self


class Prices(SQLModel, table=True):
    """Products prices model."""

    __allow_unmapped__ = True

    id: UUID = Field(default=uuid.uuid4(), primary_key=True)
    product_id: UUID = Field(foreign_key="product.id")
    price_list: list[float] = Field(sa_column=Column(ARRAY(Float)))
    date: datetime = Field(default=datetime.now(timezone.utc), nullable=False)

    product: Product | None = Relationship(back_populates="prices")

    def __init__(self, product_id: UUID, price_list: list[float]) -> None:
        """Initialize the price model."""
        self.id = uuid.uuid4()
        self.product_id = product_id
        self.price_list = price_list
        self.date = datetime.now(timezone.utc)

Description

What I am trying to achieve is to have the prices deleted the second I remove a product, as there is no point in having them without a parent. I have tried different solutions yet to no avail.

Operating System

Linux

Operating System Details

Docker

SQLModel Version

0.0.19

Python Version

3.12.3

Additional Context

No response

[YuriiMotov]

Docs about Cascade Delete Relationships: https://sqlmodel.tiangolo.com/tutorial/relationship-attributes/cascade-delete-relationships/

This should help