Improvements to storage API to allow for recursive deletes and database-level access

[milesingrams]

Feature request

Is your feature request related to a problem? Please describe.

I would like to be able to delete storage objects, folders, and buckets in response to the deletion of database entries. Currently solutions are cumbersome such as implementing a cloud function that finds and deletes all objects under a folder involving many back and forth API calls, especially if there are nested folders.

Describe the solution you'd like

I think the best way to do this would be both a javascript AND a SQL API that allows deletion of storage objects, folders, and buckets that would handle the recursive delete logic server-side. Also adding a SQL API would allow the use of triggers to delete storage objects. It would make sense that deleting an entry in storage.objects in SQL would cascade to the actual stored file such that deleting is as simple as operating on the storage.objects table.

Describe alternatives you've considered

It's doable using a cloud function, but it would have to make so many back and forth API calls. For a nested folder structure it could be tens of back and forths all of which cannot happen in a transaction and could leave many storage object islands that would permanently take space.

[J0]

Hey,

This looks like it might be best handled at storage-api and supabase-js and storage-js. Going to transfer to storage-api for now so it gets more visibility.

Lmk if there's anything I've missed.

Thanks!

[fenos]

Hi @milesingrams interesting idea!

There are 2 features that I can see you are mentioning here:

1. Recursive deletes
2. Automating deletes via triggers

Recurvise Deletes

This is indeed a current limitation where you'd need to list all the files and send a delete request for each, probably with a batching endpoint we could alleviate the request number.

However, something to consider as you mentioned would be to handle this scenario completely server side.
For example, when sending a delete request with a "folder" prefix and a flag recursive: true we could schedule the deletion of all the objects within a specific folder/bucket.

Automating deletion via triggers

I believe that keeping the storage API as the only interface to the SQL database for operations other than querying would be easier to maintain other than trying and keeping in sync 2 systems bi-directionally.

However, you could indeed create a trigger that sends an HTTP call (via the Postgres extension) to the storage API and schedule the deletion of particular objects upon a trigger (in this case a DELETE trigger).

In theory, triggers should be ran within the same transaction and should allow to access the auth.jwt() function within in. (I haven't tested it, but it could work)

My take on this would be to explore the recursive deletion server side and prioritise it accordingly

[fenos]

Closing in favor of #207 for recursive deletes