https://supabase.com/ logo
Powered by
I'm trying to use supabase auth with prisma and ge...
# help
h
I'm trying to use supabase auth with prisma and getting this error. the github discussions about triggers seem a little confusing. any idea
n
Hello @hanako! This thread has been automatically created from your message in #843999948717555735 a few seconds ago. We have already mentioned the @User so that they can see your message and help you as soon as possible! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ``...`` menu) and select "Leave Thread" to unsubscribe from future updates. Want to change the title? Use the ``/title`` command! We have solved your problem? Click the button below to archive it.
o
Hi As the error says, Prisma can't link foreign keys from different schemas @hanako
You have ``public`` and ``auth`` schemas. Both are different schemas
h
I'm guessing this is from prisma side
any idea how to fix this?
o
Don't link foreign keys between schemas ¯\_(ツ)_/¯
You can create a ``auth.users`` equivalent table named "profile": https://supabase.com/docs/guides/auth/managing-user-data#advanced-techniques
h
will try, thanks. that was fast
I put the sql code in sql editor and got 
Failed to run sql query: function "handle_new_user" already exists with same argument types
o
It means you are already using this function somewhere
h
😕 hmmm. I've just followed the nextjs guide and only added magic link authentication as of yet. I will try to fix it
Oo I think I got it
n
Yep, you also need to run the command externally via 
psql -f
to be able to reference the auth schema.
h
where do I run this command 🤔
n
@hanako yep, for my case, I have a package.json. I add a script
Copy code
json
"prisma:setup": "psql -f path/to/file.sql $DATABASE_URL ",
Then I run that, AFTER I run my migrate command. You can run it separately or
Copy code
json
"prisma:migrate": "yarn prisma migrate dev && yarn run prisma:setup",
Note: This is still a workflow testing in progress. At the end, I will post my results to the discussions https://github.com/supabase/supabase/discussions/7659 where I can hopefully explain how I combine Prisma and Supabase
h
wow thanks for the detailed answer. will try
n
No problem!
h
so I put the first one in package.json and ran 
npm run prisma:setup
but got this -
Copy code
Password for user asus:
psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user 
"asus"
I did not make the user 
asus
it came out of nowhere. I don't know its password and hence this error
tried every password from supabase to system lol but no luck
n
may I ask what's your psql command? (redact the sensitive info tho)
h
I just added 
"prisma:setup": "psql -f path/to/file.sql $DATABASE_URL ",
in package.json
n
I assume 
$DATABASE_URL
points to your DB url (which you can get from Supabase dashboard)?
and the 
path/to/file.sql
is pointing to an actual file?
h
here's the prisma schema
😕 don't know about this. thought it was internal thing so just copy pasted the earlier line you sent
n
I am unsure if package.json reads the .env file.
Ahhh no, it's just a placeholder for you to replace with your path to your SQL file
h
oh
I dont have one
do I need to? like, aside from linking supabase db?
n
it's the SQL file that can contain anything you want to bootstrap your db with. For ex, the script in the link here
The reason we call it externally, is because Prisma cannot deal with cross-schema. So anything cross-schema has to be applied outside (aka via 
psql
)
h
yea I read about that
so should I create a 
file.sql
in the project directory?
sorry if I sound dumb lol I'm quite new to this
n
I have never done this, but a quick search for using variables from 
.env
points me to https://stackoverflow.com/a/60701796
it is up to you where you want to place it. I prefer in the 
prisma
directory. But it's up to you
You have the control. Just make sure to replace 
path/to/file.sql
with the path of the file
that's ok! we all start somewhere. I also got stumped with this 
path/to/file
format in the past.
h
hmm so if I create it in 
/prisma
then it'll be 
prisma/file.sql
?
n
yep, if your file is called 
file.sql
. It is also up to you how you want to name it
h
got it
thank you very much. you're awesome
n
No problem man
I hope I could like simplify this process, maybe write a doc on this later (hence compiling it on the Discussion link above)
h
apparently it reads the env when I add both the lines
Copy code
"prisma:setup": "psql -f prisma/file.sql DATABASE_URL",
"prisma:migrate": "npx prisma migrate dev && npm run prisma:setup"
but now this
Copy code
Error: db error: ERROR: database "prisma_migrate_shadow_db_ccead842-6b6e-4416-a568-486137389446" is being accessed by other users
DETAIL: There is 1 other session using the database.
   0: sql_migration_connector::validate_migrations
             at migration-engine\connectors\sql-migration-connector\src\lib.rs:272
   1: migration_core::state::DevDiagnostic
             at migration-engine\core\src\state.rs:250
I do not have a shadow db as supabase doesn't need it iirc
n
Yes, I did not have to as well. I initially was doing it, but it was redundant.
I am not sure about this error. Perhaps you can try restart supabase db and retry? The command looks correct to me, but maybe you had another session open? Is this local db or Supabase hosted db?
h
Its supabase hosted db
will search about it
n
oh wait
migrate dev is not for production
it's something llike migrate deploy
are u doing it for testing or production?
If you do find out the solution, please do tell me. I'll add it to the collection of prisma-supabase issues. I have been fully working on local dev only for now
h
production ig
it's just a side project for learning purposes
so apparently postgres tries to login with the default account that doesn't even exist (
asus
in my case, which is my pc's name)
wasn't sure about this so tried db push
updated scripts -
Copy code
"prisma:setup": "psql -U postgres prisma/file.sql DATABASE_URL",
"prisma:push": "npx prisma db push && npm run prisma:setup"
It works, well almost
Copy code
psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  database "prisma/file.sql" does not exist
If I remove 
prisma/file.sql
then it still says the same but now 
"DATABASE_URL" does not exist
😅 I feel like I'm closer to make it work now lol
n
Does 
file.sql
exist in 
prisma
folder?
h
yes I created the file. It's empty though.
n
is 
DATABASE_URL
a placeholder for your real URL?
For supabase hosted db, you can find it in 
Settings>Database>Connection String
. Then choose 
psql
. You can even try running that command solo to see if you connect successfully
h
I have 
DATABASE_URL
= supabase connection string in .env
yeah I'm using that one. that psql command also works
it all works on its own. I just want to make users logged in through supabase magic link to show up in users table
n
For this, you may need to open a different thread.
ahh, good to know
h
n
Does it not work by default though?
h
🧐 hmm.. I have created user, post etc in prisma schema. when I login through magic link let's say, it shows up in authentication>users but not in table>editor>all tables>users
I guess I need to have it in both tables to make posts?
I'm gonna read some stuff on this
n
yes. you need to make this trigger in the 
file.sql
for the 
psql
command. (adapted to your needs ofc)
h
will check
n
Thread was archived by @hanako. Anyone can send a message to unarchive it.
4 Views
PreviousNext
Powered by