SQLAlchemy Relationship, Cascade, and Passive Deletes Test

README.md

Objective

The purpose of this gist is to test the exact behavior of SQLAlchemy relationship when coded in a certain way. What are tested:

• The existence of relationship() in the class
• The value of cascade = XXX in the relationship()
• The value of passive_deletes = XXX in the relationship()

Code

Code that will be used for experiment (using SQLAlchemy 2.0):

import sqlalchemy as sa
from sqlalchemy.orm import MappedAsDataclass, DeclarativeBase
from sqlalchemy.orm import Mapped, mapped_column, relationship, Session


class BaseTable(MappedAsDataclass, DeclarativeBase):
    pass


class Departments(BaseTable):
    __tablename__ = 'departments'

    dep_id: Mapped[int] = mapped_column(primary_key = True)

    # Relationship that will be experimented through each scenario
    employees: Mapped[list['Employees']] = relationship(init = False, cascade = 'XXX', passive_deletes = XXX)


class Employees(BaseTable):
    __tablename__ = 'employees'

    emp_id: Mapped[int] = mapped_column(primary_key = True)
    dep_id: Mapped[int | None] = mapped_column(sa.ForeignKey('departments.dep_id', ondelete = 'cascade'))

    # department: Mapped[list[Departments]] = relationship(init = False, back_populates = 'employees')


engine = sa.create_engine(DB_CONN_URI, echo = 'debug')
BaseTable.metadata.drop_all(engine)
BaseTable.metadata.create_all(engine)

with Session(engine) as session:
    session.add(Departments(1))
    session.add(Employees(1, 1))
    session.add(Employees(2, 1))
    session.add(Employees(3, 1))
    session.commit()

print('\n' * 3)

with Session(engine) as session:
    if dep := session.get(Departments, 1):
        emp = dep.employees[-1] # session.get(Employees, 3)
        dep.employees.pop() # session.delete(emp)

        session.commit()
        print(emp.emp_id, emp.dep_id) # print(emp.emp_id, emp.dep_id, emp.department is not None)
        print(emp in session)

print('\n' * 3)

with Session(engine) as session:
    session.delete(session.get(Departments, 1))
    session.commit()

Scenario and Result

Scenario A - Foreign key ondelete = "cascade", no relationship

Code	Result	Result (with passive deletes)
dep.employees.pop()	Not possible without relationship	Not possible without relationship
session.delete(dep)	SQLAlchemy will delete department directly	Not possible without relationship

Scenario B - Foreign key ondelete = "cascade", relationship on department class (no parameter)

Code	Result	Result (with passive deletes)
dep.employees.pop()	SQLAlchemy will set the employee dep_id to null	Same
session.delete(dep)	SQLAlchemy will set each employee dep_id to null first, before deleting the department	SQLAlchemy will delete department directly

Scenario C - Foreign key ondelete = "cascade", relationship on department class (cascade = "all")

Code	Result	Result (with passive deletes)
dep.employees.pop()	SQLAlchemy will set the employee dep_id to null	Same
session.delete(dep)	SQLAlchemy will delete each employee first, before deleting the department	SQLAlchemy will delete department directly

Scenario D - Foreign key ondelete = "cascade", relationship on department class (cascade = "all, delete")

Code	Result	Result (with passive deletes)
dep.employees.pop()	SQLAlchemy will set the employee dep_id to null	Same
session.delete(dep)	SQLAlchemy will delete each employee first, before deleting the department	SQLAlchemy will delete department directly

Scenario E - Foreign key ondelete = "cascade", relationship on department class (cascade = "all, delete-orphan")

Code	Result	Result (with passive deletes)
dep.employees.pop()	SQLAlchemy will delete employee directly	Same
session.delete(dep)	SQLAlchemy will delete each employee first, before deleting the department	SQLAlchemy will delete department directly

Other Behavior

What will happen to the saved/loaded Employee object after deletion?

Saved via	Delete orphan	Deleted via dep.employees.pop()	Deleted via session.delete(emp)
emp = dep.employees[-1]	Yes	emp.department will be None after deletion	All retained
	No	emp.dep_id and emp.department will be None after deletion	All retained
emp = session.get(Employee, 3)	Yes	emp.department will be None after deletion	All retained
	No	emp.dep_id and emp.department will be None after deletion	All retained

Note that when dep.employees.pop() is called, emp will be removed from demp.employees and the emp.department will be None, but the actual changes to the database will only take effect after session.commit() is called (check using print(emp in session)). If no commit is called, there will be desync between the ORM and the database.

passive_deletes doesn't seem to affect the ORM side, only the database side, but may also cause desync if the database in fact doesn't support passive_deletes. Always check the database support before using it!

Verdict

• No cascade, cascade = "all", and cascade = "all, delete" will result in error if the foreign key (employee.dep_id) can't be null
• cascade = all is the same as cascade = "all, delete" (see the docs, at the end of the first section)
• cascade = "all, delete-orphan" is useful when foreign key can't be null, and when only used by a single parent (department)
• cascade = "all, delete-orphan" may be dangerous when employee is needed by multiple parents (departments), since it will delete the employee directly even if it's associated with other departments too
• You can getaway using no relationship (by relying only on ondelete = "cascade", if the database support it), but then you can't call dep.employees and must use select(Employees).where(Employees.dep_id == XXX)

References

• https://docs.sqlalchemy.org/en/20/orm/cascades.html
• https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html
• https://docs.sqlalchemy.org/en/20/tutorial/orm_related_objects.html
• https://stackoverflow.com/questions/78575002/when-to-use-passive-deletes-with-delete-cascade (similar test)