Run SQL through Supabase Client

[masnwilliams]

Hello! I have a seed script I use on local development that populates my database with some fake data. I have most of everything automated however I still have to manually input SQL scripts to create a trigger and function to handle new users.

Is there any way to use the supabase client to run raw SQL just like the queries you can input into the SQL Editor?

Here is the SQL I have to run:

-- inserts a row into public.users
create or replace function public.handle_new_user() 
returns trigger 
language plpgsql 
security definer set search_path = public
as $$
begin
  insert into public.user(id,email)
  values (new.id, new.email);

  insert into public.user_social(id,"userId")
  values (gen_random_uuid(), new.id);
  
  return new;
end;
$$;

-- trigger the function every time a user is created
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

Any help is appreciated!

[steve-chavez]

Hello,

I have a seed script I use on local development that populates my database with some fake data.

Hm, that sounds like a migration that you could run through https://github.com/supabase/cli

[masnwilliams]

Thank you!

I actually use Prisma on top of Supabase so I just ran this command:
npx prisma migrate dev --name create_new_user_trigger_function --create-only

Then I was able to paste the above SQL into the generated migration.sql file. Now it is all automated!

Thank you so much!

[marcospgp]

the problem with this is that your triggers and functions will get buried in a sea of migration functions

it's much better to separate the schema source of truth from migration files