How to Set Relationship Cascade Options in SQLModel

If you’ve used SQLAlchemy for any length of time, you will have come across the need to set up relationship cascade rules. These are rules that define how changes to instances of parent models should affect related models. For instance, when I delete a “Class” from my database, should I delete all “Enrollment” records related to that class as well? Cascade rules let the programmer specify this behavior.

SQLModel is an exciting new project that seeks to combine the data validation of Pydantic with the database schema declaration provided by SQLAlchemy to reduce boilerplate code. However, SQLModel does not provide a straightforward wrapper for all of the functionality of SQLAlchemy. So, without an obvious way to set cascade rules for relationships, how can we burrow beneath SQLModel into the interior of SQLAlchemy to get access to cascade rules?

A SQLModel Relationship

Let’s begin by looking at an ordinary SQLModel relationship. Let’s say, one between classes and enrollments. We will declare two SQLModel models, Class and Enrollment. A class can have many enrollments, and an enrollment is associated with exactly one class.

class Class(SQLModel, table=True):
    """A model representing a class."""

    id: Optional[int] = Field(default=None, primary_key =True)
    # other attributes here...
    enrollments: List["Enrollment"] = Relationship(
        back_populates="class"
    )

class Enrollment(SQLModel, table=True):
    """A model representing an enrollment in a class."""
    id: Optional[int] = Field(default=None, primary_key =True)
    # other attributes here...
    class_id: int = Field(
        default=None, foreign_key="class.id"
    )
    class: Optional["Class"] = Relationship(
        back_populates="enrollments"
    )

This is an implementation of a basic one-to-many relationship between Class and Enrollment. All Enrollments with a class_id equal to the id of a Class are related to the Class with that id. Thus, these Enrollments occupy the enrollments collection of the Class, and the Class fills the class variable of each of those Enrollments.

Implementing Cascades

Now, let’s implement cascading deletes to delete all enrollees in a class when that class is deleted. Luckily, this is pretty simple. Just modify the Relationship call in Class like so:

enrollments: List["Enrollment"] = Relationship(
    back_populates="class", sa_relationship_kwargs={"cascade": "delete"}
)

Pretty simple, right? SQLModel lets us pass arguments to the underlying SQLAlchemy implementation using the sa_relationship_kwargs keyword argument. So, all we have to do is pass our desired keyword arguments to the function, and they will be applied to the SQLAlchemy implementation. And, “delete” is not the only cascade option that can be passed. Other options include “save-update, merge, expunge, delete-orphan, and more. Click here to learn more about each of these options.

Conclusion

So, now we know how to set up cascade behaviors on our SQLModel models. And along the way, we learned how to pass all kinds of other arguments into the SQLModel Relationship function, allowing us to access the full power of SQLAlchemy while retaining all the benefits of SQLModel (in fact, almost every SQLModel object has a keyword argument similar to Relationship‘s sa_relationship_kwargs. This article contains another example.). I hope this guide has proven useful to you. Happy Coding!