Execute some code when an SQLAlchemy object's deletion is actually committed

Question

I have a SQLAlchemy model that represents a file and thus contains the path to an actual file. Since deletion of the database row and file should go along (so no orphaned files are left and no rows point to deleted files) I added a delete() method to my model class:

def delete(self):
    if os.path.exists(self.path):
        os.remove(self.path)
    db.session.delete(self)

This works fine but has one huge disadvantage: The file is deleted immediately before the transaction containing the database deletion is committed.

One option would be committing in the delete() method - but I don't want to do this since I might not be finished with the current transaction. So I'm looking for a way to delay the deletion of the physical file until the transaction deleting the row is actually committed.

SQLAlchemy has an after_delete event but according to the docs this is triggered when the SQL is emitted (i.e. on flush) which is too early. It also has an after_commit event but at this point everything deleted in the transaction has probably been deleted from SA.

Answer 1

When using SQLAlchemy in a Flask app with Flask-SQLAlchemy it provides a models_committed signal which receives a list of (model, operation) tuples. Using this signal doing what I'm looking for is extremely easy:

@models_committed.connect_via(app)
def on_models_committed(sender, changes):
    for obj, change in changes:
        if change == 'delete' and hasattr(obj, '__commit_delete__'):
            obj.__commit_delete__()

With this generic function every model that needs on-delete-commit code now simply needs to have a method __commit_delete__(self) and do whatever it needs to do in that method.

---

It can also be done without Flask-SQLAlchemy, however, in this case it needs some more code:

• A deletion needs to be recorded when it's performed. This is be done using the after_delete event.
• Any recorded deletions need to be handled when a COMMIT is successful. This is done using the after_commit event.
• In case the transaction fails or is manually rolled back the recorded changes also need to be cleared. This is done using the after_rollback() event.