P
Prisma•6d ago
Peacock

Recommended setup for Prisma with Supabase Auth

Morning team - looking for some industry standard suggestions on hooking Prisma up to Supabase when using Supabase auth. Right now I have a user table in my public schema, and a trigger that creates a new user row whenever a signup on supabase auth occurs. I tried havint the FK relationship established, but this feels overly problematic. Would it be best to just trust the trigger assigns the same UUID on the user table? Thanks for suggestions! ❤️
3 Replies
Prisma AI Help
Prisma AI Help•6d ago
You chose to compile with the bot. One moment while I piece together your answer… Based on the knowledge sources provided, I can offer some recommendations for setting up Prisma with Supabase Auth. Here's a suggested approach: 1. Use the multiSchema preview feature in Prisma to work with both the auth and public schemas. This allows you to reference tables across schemas. Update your schema.prisma file as follows: 
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["auth", "public"]
}
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["auth", "public"]
}
2. Instead of creating a foreign key relationship between your public.users table and auth.users, it's recommended to use a trigger to copy changes from auth.users to public.users. This approach is suggested in the Github issue comment. 3. You can create a profiles table in the public schema that doesn't have a foreign key constraint to auth.users. Instead, use the UUID from Supabase Auth as the primary key for your profiles table. Here's an example schema: 
create table profiles (
  id uuid unique not null,
  updated_at timestamp with time zone,
  email text,
  username text unique,
  avatar_url text,
  website text,

  primary key (id),
  unique(username),
  constraint username_length check (char_length(username) >= 3)
);
create table profiles (
  id uuid unique not null,
  updated_at timestamp with time zone,
  email text,
  username text unique,
  avatar_url text,
  website text,

  primary key (id),
  unique(username),
  constraint username_length check (char_length(username) >= 3)
);
4. Use a trigger to automatically create a profile entry when a new user signs up via Supabase Auth: 
create function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id, email, avatar_url)
  values (new.id, new.email, new.raw_user_meta_data->>'avatar_url');
  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();
create function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id, email, avatar_url)
  values (new.id, new.email, new.raw_user_meta_data->>'avatar_url');
  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();
This approach allows you to work with Prisma and Supabase Auth without running into cross-schema foreign key issues. It also maintains the connection between the auth user and the public profile through the shared UUID. Keep in mind that Prisma doesn't currently support Supabase's Row Level Security (RLS) out of the box, so you'll need to handle that separately if required. For more detailed information and troubleshooting tips, you can refer to the Supabase integration guide for Prisma. Github issue: Cross Schema Foreign Keys Prisma docs: Supabase integration 🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly. In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.
Peacock
PeacockOP•6d ago
This feels contradicting. First it tells you to use the multiSchema preview, then it tells you to avoid it
Nurul
Nurul•4d ago
Hey 👋 I assume your trigger looks something similar to this. I think it is common to rely on a database trigger to create a matching user row in your public schema using the same UUID as the Supabase auth user. I would recommend you to make sure that your trigger includes proper error handling and logging. If the trigger fails, you’ll want to catch that and potentially have a fallback process.

Did you find this page helpful?