Function declared in a migration file does not exist when seeding

[jrumszewicz]

Describe the bug
I'm developing a Supabase app locally. It has a public.profiles table that is referencing the auth.users table to be able to access users data via the API. The definition for the public.profiles table is in a migration file and it has a column which defaults to a custom nanoid() function declared in an earlier migration file. To sync the two tables together I use a trigger on the auth.users table. When I try to seed the DB by creating users I get this error:

Seeding data from supabase/seed.sql...
failed to send batch: ERROR: function nanoid_optimized(integer, text, integer, integer) does not exist (SQLSTATE 42883)

Is it a bug or is there something that I don't understand in this pipeline? The nanoid() function comes from this repo: https://github.com/viascom/nanoid-postgres

To Reproduce
Steps to reproduce the behavior:

1. Create a migration file 1_functions.sql:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- The `nanoid()` function generates a compact, URL-friendly unique identifier.
-- Based on the given size and alphabet, it creates a randomized string that's ideal for
-- use-cases requiring small, unpredictable IDs (e.g., URL shorteners, generated file names, etc.).
-- While it comes with a default configuration, the function is designed to be flexible,
-- allowing for customization to meet specific needs.
DROP FUNCTION IF EXISTS nanoid(int, text, float);
CREATE OR REPLACE FUNCTION nanoid(
    size int DEFAULT 21, -- The number of symbols in the NanoId String. Must be greater than 0.
    alphabet text DEFAULT '_-0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', -- The symbols used in the NanoId String. Must contain between 1 and 255 symbols.
    additionalBytesFactor float DEFAULT 1.6 -- The additional bytes factor used for calculating the step size. Must be equal or greater then 1.
)
    RETURNS text -- A randomly generated NanoId String
    LANGUAGE plpgsql
    VOLATILE
    PARALLEL SAFE
    -- Uncomment the following line if you have superuser privileges
    -- LEAKPROOF
AS
$$
DECLARE
    alphabetArray  text[];
    alphabetLength int := 64;
    mask           int := 63;
    step           int := 34;
BEGIN
    IF size IS NULL OR size < 1 THEN
        RAISE EXCEPTION 'The size must be defined and greater than 0!';
    END IF;

    IF alphabet IS NULL OR length(alphabet) = 0 OR length(alphabet) > 255 THEN
        RAISE EXCEPTION 'The alphabet can''t be undefined, zero or bigger than 255 symbols!';
    END IF;

    IF additionalBytesFactor IS NULL OR additionalBytesFactor < 1 THEN
        RAISE EXCEPTION 'The additional bytes factor can''t be less than 1!';
    END IF;

    alphabetArray := regexp_split_to_array(alphabet, '');
    alphabetLength := array_length(alphabetArray, 1);
    mask := (2 << cast(floor(log(alphabetLength - 1) / log(2)) as int)) - 1;
    step := cast(ceil(additionalBytesFactor * mask * size / alphabetLength) AS int);

    IF step > 1024 THEN
        step := 1024; -- The step size % can''t be bigger then 1024!
    END IF;

    RETURN nanoid_optimized(size, alphabet, mask, step);
END
$$;

-- Generates an optimized random string of a specified size using the given alphabet, mask, and step.
-- This optimized version is designed for higher performance and lower memory overhead.
-- No checks are performed! Use it only if you really know what you are doing.
DROP FUNCTION IF EXISTS nanoid_optimized(int, text, int, int);
CREATE OR REPLACE FUNCTION nanoid_optimized(
    size int, -- The desired length of the generated string.
    alphabet text, -- The set of characters to choose from for generating the string.
    mask int, -- The mask used for mapping random bytes to alphabet indices. Should be `(2^n) - 1` where `n` is a power of 2 less than or equal to the alphabet size.
    step int -- The number of random bytes to generate in each iteration. A larger value may speed up the function but increase memory usage.
)
    RETURNS text -- A randomly generated NanoId String
    LANGUAGE plpgsql
    VOLATILE
    PARALLEL SAFE
    -- Uncomment the following line if you have superuser privileges
    -- LEAKPROOF
AS
$$
DECLARE
    idBuilder      text := '';
    counter        int  := 0;
    bytes          bytea;
    alphabetIndex  int;
    alphabetArray  text[];
    alphabetLength int  := 64;
BEGIN
    alphabetArray := regexp_split_to_array(alphabet, '');
    alphabetLength := array_length(alphabetArray, 1);

    LOOP
        bytes := gen_random_bytes(step);
        FOR counter IN 0..step - 1
            LOOP
                alphabetIndex := (get_byte(bytes, counter) & mask) + 1;
                IF alphabetIndex <= alphabetLength THEN
                    idBuilder := idBuilder || alphabetArray[alphabetIndex];
                    IF length(idBuilder) = size THEN
                        RETURN idBuilder;
                    END IF;
                END IF;
            END LOOP;
    END LOOP;
END
$$;

2. Create a migration file 2_profiles.sql:

create table
  public.profiles (
    id uuid not null,
    email text not null,
    referral_code text null default nanoid (8),
    constraint profiles_pkey primary key (id),
    constraint profiles_referral_code_key unique (referral_code),
    constraint profiles_id_fkey foreign key (id) references auth.users (id) on update cascade on delete cascade
  ) tablespace pg_default;

create or replace function public.handle_new_user()
returns trigger
set search_path = ''
as $$
begin
  insert into public.profiles (id, email)
  values (new.id, new.email);
  return new;
end;
$$ language plpgsql security definer;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

3. Create a seed.sql file:

insert into auth.users ("instance_id", "id", "aud", "role", "email", "encrypted_password", "email_confirmed_at", "invited_at", "confirmation_token", "confirmation_sent_at", "recovery_token", "recovery_sent_at", "email_change_token_new", "email_change", "email_change_sent_at", "last_sign_in_at", "raw_app_meta_data", "raw_user_meta_data", "is_super_admin", "created_at", "updated_at", "phone", "phone_confirmed_at", "phone_change", "phone_change_token", "phone_change_sent_at", "email_change_token_current", "email_change_confirm_status", "banned_until", "reauthentication_token", "reauthentication_sent_at", "is_sso_user") values
('00000000-0000-0000-0000-000000000000', 'd0fc7e46-a8a5-4fd4-8ba7-af485013e6fa', 'authenticated', 'authenticated', '[email protected]', crypt('passwd', gen_salt('bf')), '2023-02-18 23:31:13.017218+00', null, '', '2023-02-18 23:31:12.757017+00', '', null, '', '', null, '2023-02-18 23:31:13.01781+00', '{"provider": "email", "providers": ["email"]}', '{}', null, '2023-02-18 23:31:12.752281+00', '2023-02-18 23:31:13.019418+00', null, null, '', '', null, '', 0, null, '', null, 'f'),
('00000000-0000-0000-0000-000000000000', '4c6ed4d5-746c-4124-9d3e-b32e5f769476', 'authenticated', 'authenticated', '[email protected]', crypt('passwd', gen_salt('bf')), '2023-02-18 23:31:13.017218+00', null, '', '2023-02-18 23:31:12.757017+00', '', null, '', '', null, '2023-02-18 23:31:13.01781+00', '{"provider": "email", "providers": ["email"]}', '{}', null, '2023-02-18 23:31:12.752281+00', '2023-02-18 23:31:13.019418+00', null, null, '', '', null, '', 0, null, '', null, 'f'),
('00000000-0000-0000-0000-000000000000', 'b4461135-6cc9-42e7-a2b1-450938500290', 'authenticated', 'authenticated', '[email protected]', crypt('passwd', gen_salt('bf')), '2023-02-18 23:31:13.017218+00', null, '', '2023-02-18 23:31:12.757017+00', '', null, '', '', null, '2023-02-18 23:31:13.01781+00', '{"provider": "email", "providers": ["email"]}', '{}', null, '2023-02-18 23:31:12.752281+00', '2023-02-18 23:31:13.019418+00', null, null, '', '', null, '', 0, null, '', null, 'f');

4. Run the supabase db reset --local command

I'm currently using the beta branch of the CLI because of this bug #2734

[jrumszewicz]

The mistake was on my part and it was due to schema confusion. The trigger is in the auth schema, makes calls to nanoid_optimized() function that is defined in the public schema which makes calls to functions from pgcrypto that are declared in the extensions schema. The solution is to set the search_path in the nanoid functions or add explicit schema names in front of all function calls.