Running `prisma migrate dev` against Supabase with `multiSchema` throws error: `db error: ERROR: cannot drop table auth.users because other objects depend on it`
Problem
Bug description `prisma migrate dev` command fails when using `multiSchema` with Supabase. How to reproduce 1. Create a new Supabase project and copy the database connection string. Make sure to set up and use the shadow database by following this: https://supabase.com/docs/guides/integrations/prisma 2. Create a new Nuxt3 app and set up environmental variables 3. Set your `schema.prisma` as shown below 4. Run `prisma db pull` to pull to introspect. This will pull all tables in public and auth schema 5. Run `prisma generate ` 6. Next, run `prism migrate dev --name init` 7. See the error: [code block] Expected behavior After setting up schema.prisma to use multischema, and successfully running prisma db pull, the migrate command should work successfully with a message such as: [code block] Prisma information [code block] Environment & setup - OS: macOS - Database: PostgreSQL - Node.js version: v16.14.1 Prisma Version [code block]
Error Output
error: db error: ERROR: cannot drop table auth.users because other objects depend on it
Unverified for your environment
Select your OS to check compatibility.
1 Fix
Solution: Running `prisma migrate dev` against Supabase with `multiSchema` throws error: `db error: ERROR: cannot drop table auth.users because other objects depend on it`
> 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/bas
Trust Score
3 verifications
- 1
> 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 -
- 2
Thanks for your feedback. I have followed the instruction in the resources you p
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?
- 3
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`
Validation
Resolved in prisma/prisma GitHub issue #17734. Community reactions: 4 upvotes.
Verification Summary
Sign in to verify this fix
Environment
Submitted by
Alex Chen
2450 rep