https://www.prisma.io/ logo
Join Slack
Powered by
has anyone gotten prisma to work with supabase?
# orm-help
j
has anyone gotten prisma to work with supabase?
šŸ‘€ 1
a
Iā€™m currently using Prisma and Supabase! Any question in particular?
j
I can't get it to connect at all. Introspection and adding a migration fail
a
Could you provide a specific error message? Iā€™ll double check my setup to see if I had to do anything special.
j
The schema of the introspected database was inconsistent: Illegal cross schema reference from 
public.profiles
to 
auth.users
in constraint 
profiles_id_fkey
. Foreign keys between database schemas are not supported in Prisma. Please follow the GitHub ticket: https://github.com/prisma/prisma/issues/1175
did you move auth.users to public?
i would be perfectly happy to ignore other schemas
a
Do you have any preexisting tables in Supabase that youā€™re trying to introspect?
j
yes i created a few
i would hope there would be a way to restrict prisma to a specific schema
if i try to create an initial migration without doing any introspection with a sample table definition in my schema file i get something like this
Copy code
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
~/dev/code/n-buddy(master*) Ā» npx prisma migrate dev --name init                                                                                                                                           1 ā†µ jmsunseri@Justins-Mac-mini
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "postgres", schema "public" at "<http://db.rtqxfzoiszxomngjapov.supabase.co:6543|db.rtqxfzoiszxomngjapov.supabase.co:6543>"

Error: Database error
Error querying the database: db error: ERROR: unexpected response from login query
   0: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history
             at migration-engine/connectors/sql-migration-connector/src/flavour/postgres.rs:280
   1: sql_migration_connector::sql_database_migration_inferrer::calculate_drift
             at migration-engine/connectors/sql-migration-connector/src/sql_database_migration_inferrer.rs:57
   2: migration_core::api::DevDiagnostic
             at migration-engine/core/src/api.rs:95
a
Yeah restricting the introspect/migration command could be an interesting discussion. I donā€™t think Iā€™ve ran an introspect or migration command yet, which is why I havenā€™t seen this error, but it makes sense since Supabase automatically creates several tables for auth stuff.
Iā€™m using it for a small project so I have exclusively used the db push command
j
hmmm. I guess I could try just using push
Copy code
~/dev/code/n-buddy(master*) Ā» npx prisma db push                                                                                                                                                           
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "postgres", schema "public" at "<http://db.rtqxfzoiszxomngjapov.supabase.co:6543|db.rtqxfzoiszxomngjapov.supabase.co:6543>"
Error: P4002

The schema of the introspected database was inconsistent: Illegal cross schema reference from `public.profiles` to `auth.users` in constraint `profiles_id_fkey`. Foreign keys between database schemas are not supported in Prisma. Please follow the GitHub ticket: <https://github.com/prisma/prisma/issues/1175>
i just used push and still got an introspection error. Iā€™m baffled as to how anyone had success with this
i donā€™t seem to be able to create foreign keys using the supabase table editor. trying to click the drop down only gives me the option none and closes the fly out
a
What does your schema look like?
j
Copy code
datasource db {
  provider = "postgresql"
  url      = env("VITE_PRISMA_DB_URL")
}

generator client {
  provider = "prisma-client-js"
}

model Game {
  box_art_url   String?
  description   String?
  id            String  @id
  last_modified Int
  platform      String
  price_range   String?
  release_date  String?
  slug          String
  title         String
  url           String?
}
a
I donā€™t have a public.profiles table in Supabase for what itā€™s worth. And the cli output shows that Prisma is looking at the ā€œpublicā€ schema specifically
j
hmmm. that table was created by default. I guess I can remove the offending column and pretend like itā€™s not linked to auth in any way
a
Interesting, in any case, it seems like this particular integration needs some work. Or maybe Iā€™m missing something entirely !
By the way, just learned that you can pass a specific schema in your postgres connection string as a query parameter. It already defaults to ā€˜publicā€™ though.
j
The problem is the foreign key to another schema here
Prisma only supports 1 schema, as soon as a second is connected to your first one it fails as we do not have a way to express that yet.
That might be the difference between your projets @Justin Sunseri and @Austin Crim.
This is the issue about that: https://github.com/prisma/prisma/issues/1175
j
FWIW I moved all the tables I wanted to introspect to their own schema with no references to outside tables and I now get this error
Copy code
~/dev/code/n-buddy(master*) Ā» npx prisma introspect                                                                                                                                                                                       1 ā†µ jmsunseri@Justins-Mac-mini
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma

Introspecting based on datasource defined in prisma/schema.prisma ā€¦
Error: Error in connector: Error querying the database: Error querying the database: Error querying the database: db error: ERROR: prepared statement "s0" already exists
i donā€™t think there is anything wrong with the connection string as it was previously (with the same connection string) able to determine the enough about the DB to determine that it would need to query across schemas ( before i modified things )
j
Supabase has 2 connection strings. One unpooled, one pooled. For Introspection and Migrations use the unpooled one. For Prisma ClIent, use the pooled on with the query param 
pgbouncer=true
added at the end.
šŸ“ 1
2 Views
Open in Slack
PreviousNext
Powered by