[solved] Supabase prisma collision

Get Help and Help Others
1

When I add migrations to my Supabase project that added a trigger to add public.profile records when auth.user records are created I get prisma errors

What to do?

Command failed with exit code 1: yarn rw prisma migrate dev --name create_data_migrations --create-only
Error: P4002

The schema of the introspected database was inconsistent: Illegal cross schema reference from `public.profile` 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

error Command failed with exit code 1.
$ /Users/ajoslin/Documents/Als/Development/vaxxifi/production-candidates/vaccess/node_modules/.bin/rw prisma migrate dev --name create_data_migrations --create-only

Running Prisma CLI:
yarn prisma migrate dev --name create_data_migrations --create-only --schema "/Users/ajoslin/Documents/Als/Development/vaxxifi/production-candidates/vaccess/api/db/schema.prisma" 

Prisma schema loaded from db/schema.prisma
Datasource "DS": PostgreSQL database "postgres", schema "public" at "db.scbqrtiaoyjyyqxuxzjl.supabase.co:5432"

info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
2

I ran into the exact same problem a few days ago. It’s unfortunately a known limitation with Prisma. [Introspection] Cross Schema Foreign Keys · Issue #1175 · prisma/prisma · GitHub

For this reason I decided to do it all myself instead, in my frontend code. When the signup promise resolves I fire off a “create” mutation to set up everything in my public schema.

3

@ajoslin103 another option in addition to @Tobbe’s solution (which has the added benefit of making such any data/table in your non-public schema is rather shielded perhaps unwanted queries … like PII such as email or social security number in a user’s profile) is to:

  • write your joins and run that SQL in a queryRaw
  • write a SQL view (to simplify the raw query) and run that SQL in a queryRaw

You can still create your profile schema and tables via traditional SQL scripts – or look into dbmate GitHub - amacneil/dbmate: A lightweight, framework-agnostic database migration tool. to manage just the non-public schema migrations.

4

Thanks @dthyresson @Tobbe both interesting ideas…

I wonder if there is any way to --force Prisma to just accept the error and move on?

5

Note sure. You’d probably remove that offending foreign key and add that constraint outside of Prisma schema using dbmate etc.

I might be worth asking Prisma in their Community Community | Prisma and Slack support channels.

6

I went with @Tobbe 's answer in a slightly modified way

I wait until the User wants to save something outside of Auth and I then create them

It’s more indempotent, and easier to deal with in DEV

7

Thanks for getting back to us with a solution that works for you @ajoslin103. I’m sure it will be helpful to others in the future :+1:

8

YW, just wish I could add my customary [solved] to the title - guess it’s been too long

9

image
image1177×105 7.37 KB

10

Blow Your Mind