Automatically add image (or file) to a table

[cspecter]

I am migrating a Firebase e-commerce app over to Supabase and am trying to seed some images into storage using the Supabase dashboard. Is there a way to have all the files I add via the dashboard automatically inserted into a table, so I can easily reference them (via foreign key) when manually adding new products to the products table? Or is this something that will always have to be done manually?

[silentworks]

You should be able to setup a stored procedure and a trigger to do this. The images are stored in a schema called storage inside of the database under a table named objects, you should be able to create your own table in the public schema and keep a reference to that of the storage.objects table whenever you upload a new file. The process would be similar to what is outlined in the docs https://supabase.io/docs/guides/auth#create-a-publicusers-table. Just remember you would replace the auth.users reference in that ith references to storage.objects instead.

[cspecter]

OK, I tried this, but it just prevents me from uploading any files. Am I doing something incorrect?

create function public.handle_add_storage() 
returns trigger as $$
begin
  insert into public.files (id, name)
  values (new.id, new.name);
  return new;
end;
$$ language plpgsql security definer;

-- trigger the function every time a user is created
create trigger on_file_created
  after insert on storage.objects
  for each row execute procedure public.handle_add_storage();

[silentworks]

@cspecter this all looks correct to me I did this and it worked fine for me. Did you create the public.files table with the two columns you are referencing?

[silentworks]

I've created a video showing it working. Do note I use a migration tool to do my table, function and trigger creation but this would work the same if you did it inside of the Supabase Dashboard or using your own SQL editor. I'm talking in this one, so you might need to unmute the video.

Kapture.2021-04-30.at.12.15.56.mp4

[cspecter]

I figured out my problem. When I created the public.files table I used the default int4 as the id type. Switching the id type to text and it works fine.

Now I need the reverse when you delete a file from storage.objects I need it removed from the public.files. (Sorry for the Q's, coming from years in NoSQL land and my SQL is rusty). This is what I have so far:

CREATE OR REPLACE FUNCTION delete_file_on_update_related_table()
  RETURNS trigger AS
$func$
BEGIN
  DELETE FROM public.files where id = new.id;
  RETURN NULL;
END
$func$  LANGUAGE plpgsql;

-- trigger the function every time a user is created
create trigger on_file_delete
  after delete on storage.objects
  for each row execute procedure public.delete_file_on_update_related_table();

[silentworks]

@cspecter I would suggest you change the type to uuid to match what the storage.objects table is using as its type for id.

Your function is almost there, just change new.id to old.id, it's old because we just deleted it. Btw I'm just returning to SQL land myself after 2 years of NoSQL.