Notes on SQLAlchemy relationship and cascades

I have recently faced a bug where SQLAlchemy failed to delete some rows. The error message read something like:

sqlalchemy.orm.exc.ObjectDeletedError: Instance '<Parent at 0x7535d64fe510>' has been deleted, or its row is otherwise not present.

This happened when trying to delete the children related to certain parent rows. Here is an oversimplified definition of the parent relation as seen in the python implementation:

class Parent(BaseModel):
    __tablename__ = "parents"
    query: orm.Query
    ...
    children = db.relationship(
        "Child",
        back_populates="parent",
        cascade="all, delete, delete-orphan",
    )

This parents relation has no child_id foreign key at all.

Also important to note, that the child relation has a foreign key parent_id with an on delete=cascade constraint.

class Child(BaseModel):
    ...
    parent_id = db.Column(
        db.Integer,
        db.ForeignKey("parent.id", ondelete="CASCADE"),
        nullable=False,
    )

After going through the documentation and the code base, the error was solved with a simple change on the relationship defintion to:

    children = db.relationship(
        "Child",
        back_populates="parent",
        passive_deletes="all",
    )

Here I record some of the notes I took while reading the docs.

SQLAlchemy passive deletes

I have drawn my conclusion from the following two links:

• Cascade documetation
• Relationship documentation

SQLAlchemy handles cascade deletes by loading each related record and modifying them.

Passive deletes indicates if the ORM should try to delete/update the children records or if the database engine should handle it. Passive delete has three possible values:

• False: default value.
• True: unloaded children should not be loaded during a delete operation on the parent.
• “all”: disables Sqlachemy automatic setting of foreign key values to NULL. A delete for the parent record will be emited and the database engine will be in charge of handling the children records.

The ORM defines the cascade constraints on the parent relation. This opposed to how their are defined on the sql schema, where this is deined in the foreign key of the children relation.

Database level ON DELETE cascade is generally much more efficient than relying upon the “cascade” delete feature of SQLAlchemy.

Cascade

Link to documentation.

Cascade defines how operations on the parent record on a session are propagated to its children.

The default value is: save-update, merge. Here is a list of possible values:

• save-update: when a recorded is added to the session (Session.add()), all its related children are also added.
• delete: when the parent is deleted, also are its children. If the parent has no delete cascade, SQLAlchemy default behaviour is to set the children foreign keys to NULL.
• delete-orphan: children are deleted when deassociated from their parents. This option also implies that children only have one parent at a time.
• merge: session.merge is propagated from parents down to children.
• refresh-expire: session.expire is propagated from parents down to children.
• expunge: session.expunge is propagated from parents down to children.
• all: short hand for save-update, merge, refresh-expire, expunge, delete.

Common values:

• all
• all, delete-orphan