Issues cross-referencing tables from different schemas using Prisma

[edgarasben]

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 schemas. I have auth.users and public.profiles.

Describe the solution you'd like

I would like to be able to use Prisma to manage Supabase DB, but currently I cannot use Auth schema if it's referencing any of the tables in the public schema.

Is there are way around this issue?

Additional context

Error message from Prisma CLI:

npx prisma db pull

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma

Introspecting based on datasource defined in prisma/schema.prisma …
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

[kiwicopple]

It looks like Prisma doesn't allow cross schema joins, which would need to be fixed on their side.

Is there are way around this issue?

I think the best way around it is to copy the changes of auth.users into your public schema using triggers. You can see an example of the trigger here: https://supabase.io/docs/guides/auth#create-a-publicusers-table

I'll close this one because I'm not sure there is anything we can do on our side. Feel free to drop some comments if you get stuck though and I'll reopen

[edgarasben]

Thank you for sharing this. Are there any limitations/drawbacks using public.users instead of auth.users?

Also besides auth.users there are 4 other tables in the auth schema: audit_log_entries, instances, refresh_tokens and schema_migrations. Do I need to migrate those to the public schema too?

[kiwicopple]

if you using Supabase for user management (log in, out, etc), then we put everything in the auth schema because it's more secure (not exposed on the API).

The drawbacks would be:

• security
• data duplication

there are 4 other tables

It depends on what you are doing with the tables, but I doubt you will need to copy the data over to the public schema - just the users

If you aren't using Supabase for User management then you can actually ignore all these tables completely and just build everything into the public schema

[edgarasben]

Makes sense. I am still considering to use Supabase Auth or Next Auth. Maybe Next Auth is a bit more customizable at this point. But looking to the future, all the Row Level Security with Supabase Auth stuff seems quite intriguing.

[jondcallahan]

For anyone coming here in the future, the page linked by @kiwicopple above no longer contains the needed information but I found it in the git history here https://github.com/supabase/supabase/blob/1315bac01bec4fc70ebc0184b524cd9d890e749d/web/docs/guides/auth.mdx#create-a-publicusers-table

[kiwicopple]

We have a doc here for managing user data: https://supabase.io/docs/guides/auth/managing-user-data

[NixBiks]

Makes sense. I am still considering to use Supabase Auth or Next Auth. Maybe Next Auth is a bit more customizable at this point. But looking to the future, all the Row Level Security with Supabase Auth stuff seems quite intriguing.

Can you actually use Row Level Security when using Prisma? I.e. can you use the auth helper functions auth.uid() etc. when using Prisma instead of the Data API from supabase?

[kvnam]

Is there any update on this? Especially @mr-bjerre 's question.. I'm trying to implement the same thing, but Prisma by passes any RLS poilcy set up (most likely as it's using the postgres role which is allowed all access?) I would appreciate a response here so I can quickly move on if this is not feasible. Thanks!

[kaufmann42]

Following as well. This would be the perfect combo (supabase & prisma) but it seems as if there's no progress on ether side.

[lumenwrites]

I'm looking to set up the exact same thing, I want to manage my tables using Prisma, but have the auth automatically handled by Supabase, it would be amazing.

According to this thread:

There are a lot of developers using this setup.

But I can't seem to figure out how, and I can't find any tutorials or code examples. I'm new to Supabase, and I'd really love it if someone could help me to find a way to set this up correctly.

Did any of you guys find a solution?

[coopermaruyama]

This worked for me:

schema.prisma:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider          = "postgresql"
  url                   =  env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
  schemas           = ["auth", "public"]
}

then prisma db pull.

This requires every model to have define an additional property @@schema("public") or whatever so it knows which model belongs to which schema

[leimeter-joaquin]

For anyone wondering how to create the trigger, the link referenced in this thread is deprecated. This is the new link to the supabase docs https://supabase.com/docs/guides/auth/managing-user-data#advanced-techniques

[robertwt7]

This worked for me:

schema.prisma:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider          = "postgresql"
  url                   =  env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
  schemas           = ["auth", "public"]
}

then prisma db pull.

This requires every model to have define an additional property @@schema("public") or whatever so it knows which model belongs to which schema

I tried to do this as well today, however after adding all my new Models, the prisma migrate dev includes the existing auth table and it wants to delete all of the previous data. how'd you end up doing migration because there's no initial history for the auth schema?

[robertwt7]

Apparently after doing prisma db pull, you all can follow this to apply the initial migration:

https://www.prisma.io/docs/orm/prisma-migrate/getting-started#adding-prisma-migrate-to-an-existing-project

https://www.prisma.io/docs/orm/prisma-schema/data-model/multi-schema