prisma: Running `prisma migrate dev` against Supabase with `multiSchema` throws error: `db error: ERROR: cannot drop table auth.users because other objects depend on it`

I got migration running by updating the prisma/migrations/0_init/migration.sql

diff --git a/prisma/migrations/0_init/migration.sql b/prisma/migrations/0_init/migration.sql
index b21b860..5fa1d42 100644
--- a/prisma/migrations/0_init/migration.sql
+++ b/prisma/migrations/0_init/migration.sql
@@ -26,7 +26,7 @@ CREATE TABLE "auth"."identities" (
     "last_sign_in_at" TIMESTAMPTZ(6),
     "created_at" TIMESTAMPTZ(6),
     "updated_at" TIMESTAMPTZ(6),
-    "email" TEXT DEFAULT lower((identity_data->>'email'::text)),
+    "email" TEXT GENERATED ALWAYS AS (lower((identity_data->>'email'::text))) STORED,
     CONSTRAINT "identities_pkey" PRIMARY KEY ("provider", "id")
 );
 -- CreateTable
@@ -166,7 +166,7 @@ CREATE TABLE "auth"."users" (
     "phone_change" TEXT DEFAULT '',
     "phone_change_token" VARCHAR(255) DEFAULT '',
     "phone_change_sent_at" TIMESTAMPTZ(6),
-    "confirmed_at" TIMESTAMPTZ(6) DEFAULT LEAST(email_confirmed_at, phone_confirmed_at),
+    "confirmed_at" TIMESTAMPTZ(6) GENERATED ALWAYS AS (LEAST(email_confirmed_at, phone_confirmed_at)) STORED,
     "email_change_token_current" VARCHAR(255) DEFAULT '',
     "email_change_confirm_status" SMALLINT DEFAULT 0,
     "banned_until" TIMESTAMPTZ(6),
@@ -176,21 +176,6 @@ CREATE TABLE "auth"."users" (
     "deleted_at" TIMESTAMPTZ(6),
     CONSTRAINT "users_pkey" PRIMARY KEY ("id")
 );

@ruheni hope you don’t mind the ping here, but I noticed that you committed the Supabase auth docs section covering Prisma multi-schema support. The primary use case being creating a relationship between auth.users and public.profiles (or similar). Probably need some additional caveats in the docs, as what is published won’t work out of the box given the issues here.

Specifically we needed to

1. Include “storage” in the schemas array in schema.prisma
2. Baseline our migrations after pulling as described here
3. Change lines in the Prisma generated sql to handle generated columns per @armedi’s comment above
4. I had to setup a standalone shadow db in Supabase to get the uuid_generate_v4() function on storage.objects to pass validation, as the default shadow db generated by Prisma doesn’t support the extension, and using the preview extensions feature created additional migration

There are a decent amount of threads here and on Reddit about making Supabase auth and Prisma work as smoothly together as everything else. Feels like we’re very close to that being a reality.

@janpio lmk if I got anything wrong or left anything out here.

Hey folks, I just actually wrote a pretty long blog post about how to hook up Prisma with RLS from Supabase and everything, as well as Baselining the Database so that things can work! If you are curious, please check it out to get your Supabase working with Prisma with all the benefits of Prisma Client built in!

https://medium.com/@ngoctranfire/using-prisma-with-supabase-row-level-security-and-multi-schema-7c53418adba3

Hey guys, I’m joining the party. Is there a fix planned for this? It can be rather tedious (changing the migrate sql every time, baselining…etc.) every time we change the schema. Ideally a definite fix would be great! Thanks for your work anyways!

To add to what @armedi said, the error also occurs when including the storage schema, specifically on the objects table. I fixed it by replacing the following line in said table.

CREATE TABLE "storage"."objects" (
    "id" UUID NOT NULL DEFAULT uuid_generate_v4(),
    "bucket_id" TEXT,
    "name" TEXT,
    "owner" UUID,
    "created_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
    "last_accessed_at" TIMESTAMPTZ(6) DEFAULT CURRENT_TIMESTAMP,
    "metadata" JSONB,
-  "path_tokens" TEXT[] DEFAULT string_to_array(name, '/'::text),
+  "path_tokens" TEXT[] GENERATED ALWAYS AS (string_to_array(name, '/'::text)) STORED,
    CONSTRAINT "objects_pkey" PRIMARY KEY ("id")
);

I also added

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

to the SQL file to use the uuid_generate_v4 function.

I got this same error when running prisma migrate dev after following all steps from here

Error: P3006

Migration `0_init` failed to apply cleanly to the shadow database. 
Error:
db error: ERROR: cannot use column reference in DEFAULT expression
   0: migration_core::state::DevDiagnostic
             at migration-engine/core/src/state.rs:269

Any new solutions?

For me issue is when i start adding relationship between auth.users to public.users. where i want the auth.users id have relationship in public.users auth_uid.

Also the suggestion to use view from auth.users i can’t add relationship so thats busted.

Does no one see the problem of having auth schema (which is ‘owned’ by supabase) as part of migrations? What happens when supabase make changes to this schema ? You will need to rebase your migrations? Delete all data? Just curious what’s the maintainable approach here ?

No solution ?

I found a solution. https://github.com/prisma/prisma/issues/1122#issuecomment-1510359508

I found a solution. (comment)

IMHO, this is the right approach. I don’t want the auth schema in my Prisma files, just need the user details. To me, trying to cram the auth schema into Prisma feels a lot like “accidental complexity” 😃. Thanks @convcha !

Does no one see the problem of having auth schema (which is ‘owned’ by supabase) as part of migrations? What happens when supabase make changes to this schema ? You will need to rebase your migrations? Delete all data? Just curious what’s the maintainable approach here ?

That’s a valid concern. I would say that if their is a detected migration change, what you can do is see what the change is yourself, and then actually do a resolve–migration to put your database in sync with everything again!

Adding storage to my schemas array (and doing nothing else but migrating right afterward) worked! Thanks - I failed to connect the dots it seems…

Thanks @armedi that solved the issue for me

Adding storage to my schemas array (and doing nothing else but migrating right afterward) worked! Thanks - I failed to connect the dots it seems…

This worked! If anyone encounters the issue this thread started with, here is what I did:

1. Baseline your database: https://www.prisma.io/docs/getting-started/setup-prisma/add-to-existing-project/relational-databases/baseline-your-database-typescript-postgresql

2. Create a prisma.schema, this is what mine was in case of Supabase Postgres (Make sure to add “storage” to the schemas array, it will crash without this step!):

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

datasource db {
    provider  = "postgresql"
    url       = env("DATABASE_URL")
    directUrl = env("DIRECT_URL")
    schemas   = ["public", "auth", "storage"]
}

model Account {
    id                 String    @id @default(cuid())
    userId             String
    providerType       String
    providerId         String
    providerAccountId  String
    refreshToken       String?
    accessToken        String?
    accessTokenExpires DateTime?
    createdAt          DateTime  @default(now())
    updatedAt          DateTime  @updatedAt
    user               User      @relation(fields: [userId], references: [id])

    @@unique([providerId, providerAccountId])
    @@schema("public")
}

model Session {
    id           String   @id @default(cuid())
    userId       String
    expires      DateTime
    sessionToken String   @unique
    accessToken  String   @unique
    createdAt    DateTime @default(now())
    updatedAt    DateTime @updatedAt
    user         User     @relation(fields: [userId], references: [id])

    @@schema("auth")
}

model User {
    id            String    @id @default(cuid())
    name          String?
    email         String?   @unique
    emailVerified DateTime?
    image         String?
    createdAt     DateTime  @default(now())
    updatedAt     DateTime  @updatedAt
    accounts      Account[]
    sessions      Session[]

    @@schema("auth")
}

model VerificationRequest {
    id         String   @id @default(cuid())
    identifier String
    token      String   @unique
    expires    DateTime
    createdAt  DateTime @default(now())
    updatedAt  DateTime @updatedAt

    @@unique([identifier, token])
    @@schema("public")
}

1. Migrate with npx prisma migrate dev, make sure you are not using npx prisma migrate dev --name init as it will not work.

2. Generate a client npx prisma generate , enjoy.

EDIT: these are the connection URLs I used for my project - if you are/will be using this in a serverless environment, you have to create these connection configurations: https://supabase.com/docs/guides/integrations/prisma#connection-pooling-with-supabase

I have followed the steps and have gotten past the migration problem, so am baselined and in sync with the schema. But now when I try to invite a new user using Supabase’s interface for invitations via email I get Failed to invite user: failed to make invite request: Database error finding user. Also, when I navigate to the Storage page in Supabase I get Internal Server Error. Can anyone reproduce this?

I ran into a similar issue. I’m pretty sure the auth schema is getting messed up when migrating. I ended up just using the official Supabase JS library.

This error message just implies that you can not because of the order things are done, or because storage.buckets is not also modified at the same time. That could be solvable by adding storage to your schemas array maybe before you start this process.

You most likely used the connection pooling port instead of the non-pooled one.

Either way, I receive the same (previous) error anway.

Ok good, then this makes a lot more sense.

The drift is detected because you get the schema via db pull, but then later try to use migrate dev - which compares the database state with the migration history. And you have no migration history - so you need to create one via baselining: https://www.prisma.io/docs/getting-started/setup-prisma/add-to-existing-project/relational-databases/baseline-your-database-typescript-postgres

With that in place migrate dev should not detect any drift, not prompt your to reset, and then not crash when you do so (which btw is a real bug and something I do not know yet how we can fix it - but you should never get into this situation).

Thanks for your feedback. I have followed the instruction in the resources you provided. Everything went fine. The last part where I ran prisma migrate dev threw an error. Do you know what might be causing that?

Error: P3006 Migration 0_init failed to apply cleanly to the shadow database. Error: db error: ERROR: cannot use column reference in DEFAULT expression 0: migration_core::state::DevDiagnostic at migration-engine/core/src/state.rs:269