How to add an image to storage and get publicUrl by using the "seed.sql" file?

[Donnerstagnacht]

Could someone explain how I can add an image file to a storage bucket and reference the publicUrl in a database table by using the "seed.sql" file during the starting process?

-- Set up Storage: insert into storage.buckets (id, name, public) values ('avatars', 'avatars', true);

-- Set up Table: insert into table public.images publicUrl text

-- Set up insert img?

-- Insert public Url to table?

[fenos]

the public URL is a deterministic URL composed as follows:

https://[project_ref].supabase.co/storage/v1/object/public/[id]

I hope it helps

[kurtguenther]

the public URL is a deterministic URL composed as follows:

https://[project_ref].supabase.co/storage/v1/object/public/[id]

I hope it helps

I don't think this answers the question about how we can seed the actual images in buckets in local dev through seed.sql

To answer the question:

1. Create buckets -> INSERT into storage.buckets table
2. Create avatar record -> INSERT into storage.objects table
3. In the public.profile (or whatever table hosts the profiles) you just add the reference to that url as specified above.
4. Add the actual binary image to storage container -->> ??? This is what we need help on.

123 could be shortcut'd by doing the work locally in Supabase Studio and doing a pg_dump locally to get the states.
Step 4 though is a mystery. Is there a way to populate this (or run a local command via CLI that could do it?)

[meenie]

I would love an answer to this question. I don't think you are going to find one, though. I think the only way to get this to work is to create your own bash script that does supabase db reset and then a couple of API calls to upload those files using the Supabase API or just shove them into the mounted volume within docker.

[leikoilja]

Having out of the box support for that using seed.sql file would be superb, but since it's not yet supported, here is my solution that works quite well for me so far:

1. My seed.sql creates a table for my main items, those have rows with a field image that points to the image URL from unsplash
2. seed.sql also creates a new bucket for images
3. yarn add -D tsx to be able to run node TS scripts
4. adding "seed:images": "tsx supabase/seed_images.ts" to my package.json script's section
5. adding TS script to supabase directory as (note, i have it in multiple files, so this is redactor version):

import { createClient } from "@supabase/supabase-js";

default async function getImageRefFromImage(
  supabaseClient: any,
  inputImage: string | File | Blob,
  imagePrefix: string,
  debounceMs: number = 0,
  collectionName: string = '<db_bucket_name>',
  upsert: boolean = false, // override if file exists
) {
  // inputImage can be either a string (url) or a File
  let image = inputImage;
  if (typeof inputImage === "string" && inputImage.startsWith("https://")) {
    image = await fetch(inputImage).then((r) => r.blob());
    await new Promise((resolve) => setTimeout(resolve, debounceMs));
  }
  const { data: imageData, error } = await supabaseClient.storage
    .from(collectionName)
    .upload(`${imagePrefix}_image` + ".jpg", image, {
      upsert: upsert,
    });
  if (error) logErrorMessage(error);
  return imageData.path;
}

async function placeImagesToBucket() {
  const client = createClient(
    "http://localhost:54321",
    "<anon-key>"
    { auth: { persistSession: false } },
  );
  let { data } = await client.from("<db-table-with-column-image").select();
  if (data) {
    for (const item of data) {
      if (item.image?.startsWith("https")) {
        const imagePath = await getImageRefFromImage(
          client,
          item.image,
          item.id,
          100,
        );
        console.log(`For item(id=${item.id}), imagePath is ${imagePath}`);
        await client
          .from("meals")
          .update({
            image: imagePath,
          })
          .eq("id", item.id);
      }
    }
  }
}

placeImagesToBucket();

6. when i need to seed images i do yarn seed:images