Fixing a cascading delete problem in SQLAlchemy

README.md

Fixing a cascading delete problem in SQLAlchemy

Recently I spent two days figuring out why a seemingly trivial use of a cascading delete relationship in SQLAlchemy did not work properly, eventually I found the answer in this StackOverflow post. Because it was a real puzzler I've created this short writing, perhaps it can be of help to someone facing the same problem someday.

The context of the problem is very simple: there are two tables (Parent and Child), each Parent can only have one Child and when a Parent row is deleted the associated Child row should also be deleted.

This is how that looks in SQLAlchemy Python code:

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    name = Column(String)

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    childID = Column(Integer, ForeignKey(Child.id))
    child = relationship(Child, single_parent=True, cascade="all, delete, delete-orphan")

The following snippet shows how a parent and child row can be created in the database:

child = Child(id=456, name='a-child')
parent = Parent(id=123, name='a-parent', child=child)
session.add(parent)
session.commit()

So far, so good.

The problem with cascasing deletes not working as you would expect is when you use a bulk delete like this:

session.query(Parent).filter(Parent.id == 123).delete()

Even though the filtered query only returns at most one object, the delete is still considered a bulk operation and those operations do not cascade through ORM relationships.

In this case the proper way to perform a cascading delete is to get the Parent object and perform a delete on that:

parent = session.query(Parent).filter(Parent.id == 123).first()
session.delete(parent)

See the attached sqlalchemy-cascade-delete.py file for a reproducable example of this problem.

sqlalchemy-cascade-delete.py

#!/usr/bin/env python

from sqlalchemy import create_engine, String, ForeignKey, Column, Integer
from sqlalchemy.orm import declarative_base, sessionmaker, relationship

Base = declarative_base()


class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    name = Column(String)


class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    childID = Column(Integer, ForeignKey(Child.id))
    child = relationship(Child, single_parent=True, cascade="all, delete, delete-orphan")


engine = create_engine('postgresql://usr:pass@localhost/selfservice')
Base.metadata.create_all(engine)
SessionMaker = sessionmaker(bind=engine)
session = SessionMaker()
child = Child(id=456, name='a-child')
parent = Parent(id=123, name='a-parent', child=child)
session.add(parent)
session.commit()

# The delete below does not cascade!
# session.query(Parent).filter(Parent.id == 123).delete()

# Cascade delete entities:
parent = session.query(Parent).filter(Parent.id == 123).first()
session.delete(parent)

session.commit()