Relationship cascade deletion does not work as in SQLAlchemy

[JBorrow]

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

"""
Shows how the cascade option does not work with SQLModel
"""

import sys

mode = sys.argv[1]

if mode == "model":
    from sqlmodel import SQLModel, Relationship, Field, create_engine, Session

    class Parent(SQLModel, table=True):
        id: int = Field(primary_key=True)

        children: list["Child"] = Relationship(back_populates="parent", sa_relationship_kwargs={"cascade": "all, delete-orphan"})

    class Child(SQLModel, table=True):
        id: int = Field(primary_key=True)
        parent_id: int = Field(foreign_key="parent.id")
        parent: Parent = Relationship(back_populates="children")

    engine = create_engine("sqlite:///:memory:")
    SQLModel.metadata.create_all(engine)

    with Session(engine) as session:
        parent = Parent()
        children = [Child(parent=parent) for _ in range(3)]

        session.add_all([parent] + children)
        session.commit()

    with Session(engine) as session:
        parent = session.query(Parent).one()

        session.delete(parent)
        session.commit()

    with Session(engine) as session:
        assert session.query(Child).count() == 0

elif mode == "alchemy":
    from sqlalchemy import create_engine, Column, Integer, ForeignKey, MetaData
    from sqlalchemy.orm import declarative_base, relationship, Session

    Base = declarative_base()

    class Parent(Base):
        __tablename__ = "parent"
        id: int = Column(Integer, primary_key=True)
        children = relationship("Child", cascade="all, delete-orphan")

    class Child(Base):
        __tablename__ = "child"
        id: int = Column(Integer, primary_key=True)
        parent_id: int = Column(ForeignKey("parent.id"))
        parent = relationship("Parent", back_populates="children")

    engine = create_engine("sqlite:///:memory:")
    Base.metadata.create_all(engine)
    
    with Session(engine) as session:
        parent = Parent()
        children = [Child(parent=parent) for _ in range(3)]

        session.add_all([parent] + children)
        session.commit()

    with Session(engine) as session:
        parent = session.query(Parent).one()

        session.delete(parent)
        session.commit()

    with Session(engine) as session:
        assert session.query(Child).count() == 0

else:
    pass

Description

It does not seem possible to have cascade deletions in SQLModel, and (cascading) deletions are not documented in the official docs.

In the above code I have tried to match SQLModel and SQLAlchemy up as closely as possible. I create a Parent with three Children. On deletion of the Parent, in SQLModel, the cascade does not delete the children. It does in SQLAlchemy.

Any help with resolving this issue or my misunderstanding of the cascade options in SQLModel would be much appreciated.

Operating System

macOS

Operating System Details

MacOS Sonoma 14.4.1

SQLModel Version

0.0.18

Python Version

3.12.1

Additional Context

No response

[JBorrow]

Potentially related discussions (I've tried all these already):

#613 - this does not work
#480 - there is also no documentation on providing cascade options for foreign keys on a DB level. I've tried, and I cannot seem to get it to accept ondelete or on_delete as a SQLAlchemy argument.

[JBorrow]

My mistake, this code does all work. I was mistakenly running session.query(Parent).delete() in my associated code and this was not working. You must select and then delete with the session, the delete method doesn't work. But it is a valid method, in both SQLAlchemy and SQLModel.