SQLAlchemy not cascade deleting multiple levels down

Question

I'm a little new to SQLAlchemy. I've searched around for an answer to my question but I have found nothing that works for my situation.

In short, deleting a record in the Release model will delete all the records in the other models as long as there is no related records in TestResults. However, if there are related records in TestResult, then deleting a Release will not work. It almost seems as if deleting a parent will delete a child and the child's child but not the child's child's child. Here is some code to help highlight this:

class Release(db.Model):
    __tablename__ = 'releases'
    id = db.Column(db.Integer, primary_key=True)
    platform_id=db.Column(db.Integer, db.ForeignKey('platforms.id'))
    name = db.Column(db.String(20), unique=True)
    builds = db.relationship('ReleaseBuilds', cascade='all,delete', lazy='dynamic', order_by="desc(ReleaseBuilds.date_created)")

class ReleaseBuilds(db.Model):
    __tablename__='release_builds'
    id = db.Column(db.Integer, primary_key=True)
    release_id = db.Column(db.Integer, db.ForeignKey('releases.id'))
    name = db.Column(db.String(150), nullable=False)
    artifacts = db.relationship('ReleaseBuildArtifacts', cascade='all,delete', backref='builds', lazy='dynamic')
    deployments = db.relationship('Deployments', cascade='all,delete', lazy='dynamic')
    tests = db.relationship('Test', cascade='delete', lazy='dynamic')

class ReleaseBuildArtifacts(db.Model):
    __tablename__='release_build_artifacts'
    id = db.Column(db.Integer, primary_key=True)
    release_build_id = db.Column(db.Integer, db.ForeignKey('release_builds.id'))
    application_id = db.Column(db.Integer, db.ForeignKey('applications.id'))
    rpm = db.Column(db.String(300))
    build = db.relationship('ReleaseBuilds')
    application = db.relationship('Application')

class Deployments(db.Model):
    __tablename__ = 'deployments'
    release_build_id = db.Column(db.Integer, db.ForeignKey('release_builds.id'), primary_key=True)
    environment_id = db.Column(db.Integer, db.ForeignKey('environments.id'), primary_key=True)
    date_deployed = db.Column(db.DateTime(timezone=False), default=datetime.datetime.utcnow)
    environment = db.relationship('Environment', foreign_keys=[environment_id])

class TestType(db.Model):
    __tablename__ = 'test_types'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True)

class Test(db.Model):
    __tablename__ = 'tests'
    id = db.Column(db.Integer, primary_key=True)
    release_build_id = db.Column(db.Integer, db.ForeignKey('release_builds.id'), nullable=False)
    environment_id = db.Column(db.Integer, db.ForeignKey('environments.id'), nullable=False)
    test_type_id = db.Column(db.Integer, db.ForeignKey('test_types.id'))
    name = db.Column(db.String(300))
    environments = db.relationship('Environment', foreign_keys=[environment_id])
    results = db.relationship('TestResult', cascade='all,delete', lazy='dynamic')

    __table_args__ = (
        ForeignKeyConstraint(['release_build_id', 'environment_id'],['deployments.release_build_id', 'deployments.environment_id']),
        )

class TestResult(db.Model):
    __tablename__ = 'test_results'
    id = db.Column(db.Integer, primary_key=True)
    test_id = db.Column(db.Integer, db.ForeignKey('tests.id'), nullable=False)
    name = db.Column(db.String(500))
    passed = db.Column(db.Boolean)

Any suggestions as to why this cascade delete is not working?

Answer 1

I came across a similar issue in our project, where we define cascades on the ORM level and also use lazy=dynamic relationships. This caused the cascade not to run on the bottom-most children.

Dynamic loading causes the relationship to return a Query object when accessed.

Delete on queries is quite limited, in order to increase performance, as documented here: https://docs.sqlalchemy.org/en/13/orm/query.html

The method does not offer in-Python cascading of relationships - it is assumed that ON DELETE CASCADE/SET NULL/etc. is configured for any foreign key references which require it, otherwise the database may emit an integrity violation if foreign key references are being enforced.

After the DELETE, dependent objects in the Session which were impacted by an ON DELETE may not contain the current state, or may have been deleted. This issue is resolved once the Session is expired, which normally occurs upon Session.commit() or can be forced by using Session.expire_all(). Accessing an expired object whose row has been deleted will invoke a SELECT to locate the row; when the row is not found, an ObjectDeletedError is raised.

Therefore a solution for your problem could be either defining cascades on the database level, or using other types of relationships.

Related question was raised here: SQLAlchemy delete doesn't cascade

EDIT: (Solution I applied is changing the loading type on query level - in options)