Soft delete features

[Hugo Heyman]

Hi

I could not find any documentation or info about "soft delete" features in sqlalchemy. I've found these features useful in other ORMs/languages. What I mean is having a boolean field or better yet a datetime field that can mark when entries are deleted so data is persisted.

What I suggest is some API like this:

class SomeModel(Model):
    deleted_at = Column(DateTime, soft_delete=True)
    ...

Any query on the model that does not explicitly say it wants to include soft deleted entries through some API should automatically exclude those.

What are your thoughts on this, have you considered this type of feature before?

[Mike Bayer]

SQLAlchemy would never add a high level feature like this that is easily implementable by end-users, and additionally the Column object is not part of the ORM in any case. This pattern is very popular and is achievable by simply adding a "deleted" column to your tables and populating it as needed. There is also a recipe to help with the first immediate problem of "soft delete" which is that it loads your tables with rows tha you never need to see, that is at https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PreFilteredQuery.

The simple "soft-delete" pattern is also IMO a bad decision in virtually all cases as it is much better to build out real archiving of data. The http://docs.sqlalchemy.org/en/rel_1_0/orm/examples.html#module-examples.versioned_history example illustrates one approach to this.

The openstack project now struggles daily with the extremely difficult task of removing this pattern from all applications, at Red Hat we are constantly struggling with customers whose databases are now filled with 80% garbage rows that are very difficult to purge and are never used.

[Hugo Heyman]

I see, thanks for all the info. I have just assumed that soft delete pattern was common "best practice", but after your response and reading some more on the subject I can see there are some downsides. Some kind of archiving + real deletes seems like a good approach.

[Mike Bayer]

it's a pretty common pattern, and certainly it probably works for a lot of projects. But from the SQLA perspective, there's lots of ways to approach this problem so these are best addressed as use cases / recipes / examples.