Delete filesystem data associated with database record by delete hook, or internally in service logic?

Question

Let's have many rows in the SQL database where every record has an image property that holds the path to file in the filesystem. Many database libraries have the ability to set-up hooks that are called right after the row is removed.

Which one of the following patterns is better?

1. Call hook for every removed row (even when all rows are removed in bulk action) and delete an associated file in that hook function (and fail maybe fail if the file was not removed)

2. Do not use hooks and instead delete files inside business logic inside function what calls database query (maybe do it inside a transaction to fail if the file was not removed)

Almost every documentation for database libraries warns that using a hook for bulk actions will cause performance issues but I think those performance issues will occur even when you do loop inside eg. deleteAll method.

Answer 1

Neither. Better would be not to use file system directly, but to allow database to handle the files as binary fields, or so called BLOBs.

1. Transactions

File systems are not transactional. If you use transactions in your logic and some transaction fails, you can be sure that database has rolled back any changes you have done and you have exactly the state that you had before transactions. If you want to handle files separately, via hooks or via service logic, it can be very hard to provide transaction support, e.g. to implement roll back. For instance, at one step within transaction you delete a record and delete a file in the file system, then in several steps later the transaction fails. For rollback you would need to restore the files deleted within this transaction. This can be a hard task. If you store binary data in BLOB fields, you get transaction support for free, and will always have a consistent state.

2. Backups

Database provide usually functionality to create backups and to restore the state from particular backup image. If you need backups, in case of file system you will need to implement your own tools to create and to restore backups so that database contents is consistent with files in file system. If you use BLOBs, you get it for free, database makes sure that you have consistent state at any time.

3. Permissions

You can define different user roles with different permissions, e.g. one role allows user to read and modify data, other role allows only to read data, third role allows to read data but only from several tables, not from all available tables in database. If you use file system, it will be hard to implement such permission system. It will be hard to make sure that only users that have particular role have access to the files. If you use BLOBs, you get permission system for free.

Answer 2

I strongly agree with Mentallurg's assessment – if you can do it.

If for whatever reason you need to stick with files, then I can actually think of several possibilities:

• A database hook deletes the file.
• Business logic deletes the file.
• A periodic "job" is run, e.g. by crontab, which trolls through the image directory and queries for each filename to see if it is referenced anymore. If not, it deletes the file. "Lazy deletes."

And, since it probably really doesn't matter if a disk file is still hanging around ... (you're not running out of disk space, are you?) ... maybe this third, "lazy" strategy has merit. "Yes, the file ought to be cleaned up, but maybe it really doesn't matter exactly when?"