cli icon indicating copy to clipboard operation
cli copied to clipboard

Unexpected migration occurs when executing `supabase db diff --local --schema storage --use-migra -f {name}`

Open azurechen opened this issue 9 months ago • 0 comments

I always use the local dev server to develop. And I also create storage policies locally. When I prepare to deploy these policies to the remote server. I executed the following command to generate a migration file for storage policies.

supabase db diff --local --schema storage --use-migra -f {name}

And the output includes some functions that were created by Supabase not myself.

set check_function_bodies = off;

CREATE OR REPLACE FUNCTION storage.extension(name text)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
DECLARE
_parts text[];
_filename text;
BEGIN
    select string_to_array(name, '/') into _parts;
    select _parts[array_length(_parts,1)] into _filename;
    -- @todo return the last part instead of 2
    return split_part(_filename, '.', 2);
END
$function$
;

CREATE OR REPLACE FUNCTION storage.filename(name text)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
DECLARE
_parts text[];
BEGIN
    select string_to_array(name, '/') into _parts;
    return _parts[array_length(_parts,1)];
END
$function$
;

CREATE OR REPLACE FUNCTION storage.foldername(name text)
 RETURNS text[]
 LANGUAGE plpgsql
AS $function$
DECLARE
_parts text[];
BEGIN
    select string_to_array(name, '/') into _parts;
    return _parts[1:array_length(_parts,1)-1];
END
$function$
;

I guess if I run migrations and replace these functions it may cause some issues even if I don't change any logic. (Such as if Supabase official will change these functions' logic someday)

I am also curious as to why only extension, filename and foldername these three functions should be migrated. Other functions and tables under the storage schema won't be included in the migration file. Is it caused by permissions?

I know I can remove them from the migration file by myself. But next time I run the command to generate another migration file for storage policies update. These three functions will be included in the new migration file again. Therefore I think remove them by myself may not be a good solution.

To Reproduce

  1. run supabase db diff --local --schema storage --use-migra -f {name}
  2. check the migration file

Expected behavior

The generated migration file doesn't include extension, filename and foldername functions

System information

  • Version of OS: Mac OS 14.4.1
  • Version of CLI: v1.165.0
  • Version of Docker: v26.0.0
  • Versions of services: supabase/postgres │ 15.1.1.41 │ - supabase/gotrue │ v2.149.0 │ - postgrest/postgrest │ v12.0.1 │ - supabase/realtime │ v2.28.32 │ - supabase/storage-api │ v1.0.6 │ - supabase/edge-runtime │ v1.45.2 │ - supabase/studio │ 20240422-5cf8f30 │ - supabase/postgres-meta │ v0.80.0 │ - supabase/logflare │ 1.4.0 │ - bitnami/pgbouncer │ 1.20.1-debian-11-r39 │ - darthsim/imgproxy │ v3.8.0 │ -

azurechen avatar May 14 '24 00:05 azurechen