Future plans for storage using object.ID?

[RickMeasham]

Hi folks,

I was really excited yesterday to discover that I can now create a foreign key to the objects table.

The beauty of using a foreign key to the objects table is that I can cascade a delete and remove files when when the parent record is deleted. (Eg. delete a user and their avatar is removed)

However I note that all the storage methods insist on bucket and filename still. Using this for upload makes sense as it allows for URLs that have a sensical filename for downloading. But once a file is uploaded, it would be far nicer if I could just use the object.id

Are there any plans to

1. Return the UUID from the upload call?
2. Allow this UUID to be used for download createSignedURL etc?

Thanks

[GaryAustin1]

Are you sure deleting the file object from the object table deletes the file in storage? I've not seen any change on this and in the past if you delete the row in the object table directly, you "orphan" the file in storage.

I use the http extension to delete files on a trigger if the object (a note in my case) is deleted and has a file attached to it.

I have no confidence this would work for a mass delete, of say a user and all his notes, so still have to solve that with a function running external to the db.

[RickMeasham]

I hadn't tested that it deleted the file in storage. I guess I assumed that the storage tables were connected to storage!

[GaryAustin1]

This is where we discuss it. Note the first answer ends up being wrong and it does not delete so read all responses.
#3124

I looked at the Supabase UI code and it loops thru making storage http delete calls when you delete a folder. If just deleting objects work you think they would do that.

[GaryAustin1]

I also just double checked and there are no triggers on the object table (which would be required as something would have to call the actual s3 storage to delete the file). So nothing new added.

[RickMeasham]

Thank you so much for looking into this. I'm leaving it open because I'd like to see some sort of action taken on this in the future and would love some thoughts from the core team

[mousindev]

Hi all.

I wonder the same. After uploading a file I'd like to get the Object Id to update the related record's foreign key with it. When it comes to Db, it is possible to chain a select() to the insert() method and that will do. Anything similar that can be done when uploading files?

Thanks!

[abdirahmn1]

To anyone that might stumble on this in the future, this problem is already solved, here's the PR that was merged, a changelog was also published on july 7 2023.

[mousindev]

That's great news! It covers the first point of the original message from @RickMeasham. What about the second one? Any news on that? Getting the Id of the file uploaded is cool, but it would be even cooler to be able to use it to download or get the public url of that file later :)
Then it would be possible to really use it as a foreign key in custom db tables.

[mousindev]

Not sure I am following the last comment. Let's imagine this scenario where we store the user avatar (not our scenario, but the approach we are following):

• We upload the file in a given path and with a given name, and keep the response from that call to the storage
• Using the returned path we get the public url for the file to avoid calling it every time we need to display the avatar
• Because we cannot use the id of the stored object for further actions (delete, getPublicUrl, etc) we then store in the Db table (for this sample scenario, would be in uses.avatar_path for example) the path for further actions, but also the public URL to display it faster.

Is there a better way to do it? From my perspective, using the Id would allow to have a foreign key to storage.objects.id (which would allow cascade deleting for example) in a similar manner we do when replicating and linking auth.users and public.users

Also, from what I understand from your comment @GaryAustin1 , in this example above, storing the publicURL would not be needed as invoking getPublicUrl() will not make a request to the server?

Thanks!

[abdirahmn1]

using the Id would allow to have a foreign key to storage.objects.id (which would allow cascade deleting for example) in a similar manner we do when replicating and linking auth.users and public.users

Having a ON DELETE CASCADE wouldn't actually remove the file from the storage backend that supabase uses which in this case is S3, this would only delete the row on storage.objects but not the actual file on S3, there is no trigger on supabase that does that automatically when a row that references a file on S3 is removed.

Instead, you need to separately call the supabase api to delete that file on S3, here's the example code from the docs

const { data, error } = await supabase
  .storage
  .from('avatars')
  .remove(['folder/avatar1.png'])

here's the source code that deletes a file:

  async deleteObject(objectName: string) {
    await this.db.withTransaction(async (db) => {
      const obj = await db.asSuperUser().findObject(this.bucketId, objectName, 'id,version', {
        forUpdate: true,
      })

      const deleted = await db.deleteObject(this.bucketId, objectName)

      if (!deleted) {
        throw new StorageBackendError('not_found', 404, 'Object Not Found')
      }

      await ObjectAdminDelete.send({
        tenant: this.db.tenant(),
        name: objectName,
        bucketId: this.bucketId,
        version: obj.version,
      })
    })

    await ObjectRemoved.sendWebhook({
      tenant: this.db.tenant(),
      name: objectName,
      bucketId: this.bucketId,
    })
  }

see how they send a different API req to the storage backend S3 (they do support other blob stores too i think) to delete the file... so yeah, that's why ON DELETE CASCADE won't work, you will have an "orphan" file that has no reference to your project.

@GaryAustin1 commented a potential solution for your provblem:

Are you sure deleting the file object from the object table deletes the file in storage? I've not seen any change on this and in the past if you delete the row in the object table directly, you "orphan" the file in storage.

I use the http extension to delete files on a trigger if the object (a note in my case) is deleted and has a file attached to it.

I have no confidence this would work for a mass delete, of say a user and all his notes, so still have to solve that with a function running external to the db.

You are welcome :)

[mousindev]

Hey @abdirahmn1, thanks for your comments. This is what I meant. That so far we cannot use it the same way as with the users example because of what you say, the orphan file, but that it would be nice to be able to use it the same way. I did not think of the trigger + http extension, but I think that it would be a workaround for us since we are not planning on bulk actions.

The other point is the one that remains unclear to me. Now that I understood from Gary that getPublicURL() does not call the backend would mean I don't need to store the public url but I can get it anytime using the path, but back to our discussion, it would be nice to be able to do it with the ID instead so I neither need to store the path nor would I need to make a separate call to get the path (if i'd only stored the Id) to be able to use the getPublicURL() function.

Thanks

[abdirahmn1]

The other point is the one that remains unclear to me. Now that I understood from Gary that getPublicURL() does not call the backend would mean I don't need to store the public url but I can get it anytime using the path

Just because you can get the public url anytime by calling the getPublicUrl() method doesn't mean that its not a good idea to store the url, storing the url is better in my opinion because you don't have to call getPublicUrl each time you want the url, the API is idempotent, so its ok to store the results and skip calling the API next time you want the public url, it will always be the same.

but back to our discussion, it would be nice to be able to do it with the ID instead so I neither need to store the path nor would I need to make a separate call to get the path (if i'd only stored the Id) to be able to use the getPublicURL() function.

Personally i would store path and publicUrl then its just a matter of SELECT path, public_url FROM foo;, all the file manipulation methods in supabase-js need a path, Just store the path and publicUrl and no need for the id. The reason getPublicUrl doesn't want id is because its literally doing string concatenation in order to build the publicUrl, if it has used an id, it will have to make an API req to retrieve storage.objects.bucked_id and storage.objects.name, so you don't have to do this req because there is a better way - string concatenation!

you are welcome 😄

[GaryAustin1]

I personally store the path (my bucket is fixed for the notes table my images are attached to). I don't store a full URL because if my instance id changed I would have to redo all storage links in my database tables.

You don't even have to use getPublicUrl if you don't want to build a public path, it is your URL, a fixed string the bucket and pathname.

[Ivaylo-Korakov]

It is very nice that it is now possible via the API but unfortunately, the client libraries haven't been updated to match this.
Are there plans to do so? Looking at supabase/storage-js in particular here.

@GaryAustin1 how were you able to retrieve the storage.objects.id on upload?

Also if there is no way at the moment with the standard supabase.js client lib how can I query the objects table from the storage schema with supabase.js?

[GaryAustin1]

I've never tried to get the id on upload as I have no use for it. I'm not sure if it is in the current version of storage-api or in v3 by request. You should be able to see in the network response independent of the client code.

You can also get to storage by using ...supabase.schema('storage').from('objects')...

[JanRuettinger]

I am not sure if I am doing something conceptually wrong but I have a use case which is fairly basic and requires the object_id.

My use case:

1. User uploads a file
2. Backend preprocesses the file and uploads it to supabase storage using the service role (missing association between user and file)
3. Get object_id from uploaded file and manually assign owner to that object

Ideally, I'd just pass the user_id as a parameter when I upload the file in the first place but that doesn't seem to be possible.

[StephenTangCook]

Hey @fenos! Regarding your great work here to return the storage object id, do you know if/when these changes will make their way into supbase-js?

[aeynaud]

Hey there! I'd like to follow-up. Is it possible now to get the user id through the Javascript Library?
It would also be awesome if we could choose our own object table parameters when using:

const avatarFile = event.target.files[0]
const { data, error } = await supabase
  .storage
  .from('avatars')
  .upload('public/avatar1.png', avatarFile, {
    cacheControl: '3600',
    upsert: false
  })

Something like this below, which would be attached to the file uploaded to S3.

.from('objects')
.insert({ uuid: YOUR_UUID })

[TsarPrince]

Yes, would be a lot more convenient ensuring integrity. Would be glad, if we can have an update on this.

[Andriy-Kulak]

Long story short: when I use the sdk for storage, we assign the id to the file that's uploaded but basically no easy way to return it as part of creation process.

any update on this? This makes it difficult for me to reference things on the backend or front-end of my next.js application

[mousindev]

Hi all,

I don't know since when this is available, but today when I tried storing a new file, it returned some of the attributes of the newly stored filed in the objects table, being id one of them, which is great because now I have a foreign key from my table to the storage.objects table. Thanks for that!!
Now that we have the id directly, the other part that was being discussed, is being able to use it in some of the storage functions, like getPublicUrl(). Any plans on this?

Thanks!

[GaryAustin1]

Just note that getPublicUrl would be a bad idea with the id. Right now it just string concatenation as helper function so does not call the server and is very fast. I guess you could create another version of it that takes id, but then it will be slower as it first has to go to the database to get the path, then do the string concatenation. Also, just make sure you know... do not delete the storage.objects row with just SQL! That would leave the file forever in s3 storage.

[mousindev]

Thanks Gary. Got it!
Yes, no plans on deleting records in the storage table directly :D

Thanks!

[samuelbelo]

It would be nice to retrieve objects by ID in some function like:

const { data } = supabase.storage.from('movie-posters').getObjectById(this.myUuid)

Any updates regards this subject?

[desinox]

@samuelbelo
Just wanted to leave my workaround here:
Create a (security invoker) function in public that returns path or the record:

DROP FUNCTION IF EXISTS storage_object_get_path(object_id uuid);
CREATE OR REPLACE FUNCTION public.storage_object_get_path(object_id uuid)
 RETURNS text
 LANGUAGE plpgsql
AS $function$DECLARE
    object_name text;
BEGIN
    SELECT name into object_name FROM storage.objects WHERE id = object_id;

    IF object_name IS NOT NULL THEN
        RETURN object_name;
    END IF;

    RAISE EXCEPTION 'Object not found';
END;$function$;

and then use an rpc to get path

const imageDataResponse = await supabase.rpc("storage_object_get_path", { object_id: xxx });

Hope that helps