How to use Prisma with Supabase Auth?
Hi, does anyone know how to set up Prisma with Supabase Auth? I'm using Supabase Auth and when I try to set up Prisma, it gives the error "The schema of the introspected database was inconsistent: Illegal cross schema reference from public.profiles to auth.users"
š¦ Would anyone please help me?
60 Replies
GitHub
Issues cross-referencing tables from different schemas using Prisma...
Feature request Is your feature request related to a problem? Please describe. I am trying to use Supabase together with Prisma. There seems to be a problem cross-referencing tables in different sc...
For my case, I do not put the trigger from
auth.users -> public.profiles inside Prisma migrations . I apply it externally via psql.
I created a Discussion for Prisma+Supabase issues here: https://github.com/supabase/supabase/discussions/7659
Your Issue is #1,2GitHub
Supabase and Prisma workflow compilation Ā· Discussion #7659 Ā· supab...
Note: This is a updated copy from the discord's help channel to increase visibility. I hope this can also be a central source for pointing to Prisma issues and workaround. Cross-posted to p...
@NanoBit what do you mean by "apply it externally via psql"?
Ah, so I have a package.json file which I added a script: say:
prisma:reset: "yarn prisma migrate reset && psql -f /path/to/sql/file $DATABASE_URL"
so, it always applies the psql after applying prisma migrations. Of course, it's not the best solution and I'm open to ideas..:/ gotchuu
what im doing right now is just
setting up a trigger for when auth.user is created to create a public.profile
setting up a trigger for when auth.user is deleted to delete the row in public.profile
and then just using public.profile to add data
but public.profile's id isnt linked to the auth.user it's just a plain text of the id
im not sure if that's how im supposed to do it though, or what limitations ill run into later š¢
yes, that's similar to what I have
but, how do you apply the trigger
i have a trigger set up linked to a function on supabase
ah, you mean on the supabase hosted db right?
yeah
its on the actual supabase.com
yea, you can run it once, and it'll be good
yeah
for me, this is a major problem on dev
because prisma migrations always reset
i dont really understand haha
so would my solution not work for dev?
It would definitely
It's just that, it may get unapplied when you call
prisma migrate resetwhat does that command do again? when would you iuse it?
it resets the development db and adds all the migrations back
gotchuu
ive never used that command before
since i just started out with prisma
i think it's just a quality of life to not have to manually go to the DB and paste all the triggers
every time we start supabase local development
yeah
do you understand postgres?
can you help me look over a snippet of code?
to make sure that it does what i intend it to do?
sure
begin
delete from public.profiles
where id = old.id;
return old;
end;
this is the function i attached to a trigger for when the auth.user is deleted
this function will only delete the row from public.profiles that has the id as the auth.user deleted right?
is it correct to return "old"?
You can see the previous Discussion linked if you meet other Prisma + Supabase issues. It's well worth it (imo), but need to accept drawbacks.
im scared it will somehow delete more than one profile row haha
yeah ill check it out š
Hm, so you want that if you delete a user from
auth.users to also delete in profile.users rightno i want to delete a user from auth.users and also delete a profile for that user in public.profiles
but only just one profile
ah gotcha
for that one user deleted
I would prefer being explicit in the calls in case in confuses which table
id belongs to.
ahh ok
this should be correct
š thanks NanoBit
Yes, from what I read. It should be good
yay
thanks thanks!!
If you are worried, you can test it. Create two users
and delete one
yep ill do that right now
cool!
thanks a ton
@NanoBit Hello,
When I make migrate and reset database my functions and triggers get deleted.
How could this be fixed?
Which functions/triggers?
Database functions that I have created
specifically, functions that are only in public schema, or cross schema (like auth)?
public schema
You could put them inside the prisma migrations
It will be restored when you
prisma migrateI have only
schema pgsodiumUhm, I'm not sure what that is
Perhaps, we can move to a different thread as your problem seems different from the OP?
You mean in
seed.ts ?
Yes.You could put the SQL into one of the earliest migration.sql scripts for Prisma
Yep, for stuff within public schema. This is the way to go.
However, this won't work for cross-schema triggers/functions. That's why I suggested a separate file applied externally via
psql. Unless you have an alternative? @santiFigured something out for the thing I needed @NanoBit
We use prisma. Learned that
prisma.$queryRawUnsafe('SELECT * FROM auth."users"') works fine
In my case, I wanted to get the id of a user by phone
So prisma.$queryRawUnsafe('SELECT * FROM auth."users" WHERE phone = \'something\'')Really?
That's interesting to say. I have not tested it
I'm curious though. @santi Are you using prisma with postgres role or a separate role? I would not imagine that other roles have access to
auth schemapostgres role
Oh!
I see. I'll have to note that down. Thanks! I'm using RLS with prisma, so I don't have that option
Hmm interesting
Do you query prisma directly from the client-side?
For us it goes through an API
no, it goes through API, but I mimic postgrest's method of applying RLS
Hmm interesting
In my head I thought the main use-case for RLS was to allow users to query the Prisma themselves, but with limited scope
Ah yes. I just used an API in the middle to prevent users from direct access to db (so I can edit data etc before it's saved to db)
but also so that, only users with correct permission is allowed to access/write data to db
I don't do that check in the API layer
do u check user permission in API layer?
Yes
can i ask what u use? is it just a few global permissions (like teacher vs student) or very finetuned permissions (can access resource A, but not B)?
It's pretty simple
Basically there's a few routes where the table being edited is user data, so I'll need a userId
So I make sure the auth is valid and use that userId when I do the query or mutation
Oh, I get you somewhat
Good to know!
I was actually curious how others implement access control with Prisma