multiSchema: [Introspection + DB Push on Supabase] Can't push a successfully pulled DB with multiple schemas

[nahtnam]

Bug description

In reference to: #1175 (comment)

Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "postgres", schema "public" at "localhost:54322"
Error: db error: ERROR: cannot drop index auth.users_email_key because constraint users_email_key on table auth.users requires it
HINT: You can drop constraint users_email_key on table auth.users instead.
   0: sql_migration_connector::apply_migration::migration_step
           with step=DropIndex { index_id: IndexId(13) }
             at migration-engine/connectors/sql-migration-connector/src/apply_migration.rs:21
   1: sql_migration_connector::apply_migration::apply_migration
             at migration-engine/connectors/sql-migration-connector/src/apply_migration.rs:10
   2: migration_core::state::SchemaPush
             at migration-engine/core/src/state.rs:402

Ideally this should work but alternatively it would be nice to be able to db push only the public schema. I think part of the issue stems from the fact that supabase adds a bunch of tables locally

How to reproduce

Expected behavior

Should work, and I should have the exact setup (minus the data) in the local environment.

Prisma information

// Add your schema.prisma

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

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

...

model users {
  instance_id                 String?       @db.Uuid
  id                          String        @id @db.Uuid
  aud                         String?       @db.VarChar(255)
  role                        String?       @db.VarChar(255)
  email                       String?       @db.VarChar(255)
  encrypted_password          String?       @db.VarChar(255)
  email_confirmed_at          DateTime?     @db.Timestamptz(6)
  invited_at                  DateTime?     @db.Timestamptz(6)
  confirmation_token          String?       @db.VarChar(255)
  confirmation_sent_at        DateTime?     @db.Timestamptz(6)
  recovery_token              String?       @db.VarChar(255)
  recovery_sent_at            DateTime?     @db.Timestamptz(6)
  email_change_token_new      String?       @db.VarChar(255)
  email_change                String?       @db.VarChar(255)
  email_change_sent_at        DateTime?     @db.Timestamptz(6)
  last_sign_in_at             DateTime?     @db.Timestamptz(6)
  raw_app_meta_data           Json?
  raw_user_meta_data          Json?
  is_super_admin              Boolean?
  created_at                  DateTime?     @db.Timestamptz(6)
  updated_at                  DateTime?     @db.Timestamptz(6)
  phone                       String?       @unique @db.VarChar(15)
  phone_confirmed_at          DateTime?     @db.Timestamptz(6)
  phone_change                String?       @default("") @db.VarChar(15)
  phone_change_token          String?       @default("") @db.VarChar(255)
  phone_change_sent_at        DateTime?     @db.Timestamptz(6)
  confirmed_at                DateTime?     @default(dbgenerated("LEAST(email_confirmed_at, phone_confirmed_at)")) @db.Timestamptz(6)
  email_change_token_current  String?       @default("") @db.VarChar(255)
  email_change_confirm_status Int?          @default(0) @db.SmallInt
  banned_until                DateTime?     @db.Timestamptz(6)
  reauthentication_token      String?       @default("") @db.VarChar(255)
  reauthentication_sent_at    DateTime?     @db.Timestamptz(6)
  is_sso_user                 Boolean       @default(false)
  identities                  identities[]
  mfa_factors                 mfa_factors[]
  sessions                    sessions[]
  profiles                    profiles?

  @@index([instance_id])
  @@schema("auth")
}

...


model profiles {
  id                          String  @id @db.Uuid
  first_name                  String?
  last_name                   String?
  showed_newsletter_subscribe Boolean @default(false)
  users                       users   @relation(fields: [id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@schema("public")
}

Environment & setup

• OS: macOS
• Database: Postgres
• Node.js version: v18.13.0

Prisma Version

prisma                  : 4.8.1
@prisma/client          : 4.8.1
Current platform        : darwin
Query Engine (Node-API) : libquery-engine d6e67a83f971b175a593ccc12e15c4a757f93ffe (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli d6e67a83f971b175a593ccc12e15c4a757f93ffe (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine    : introspection-core d6e67a83f971b175a593ccc12e15c4a757f93ffe (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary           : prisma-fmt d6e67a83f971b175a593ccc12e15c4a757f93ffe (at node_modules/@prisma/engines/prisma-fmt-darwin)
Format Wasm             : @prisma/prisma-fmt-wasm 4.8.0-61.d6e67a83f971b175a593ccc12e15c4a757f93ffe
Default Engines Hash    : d6e67a83f971b175a593ccc12e15c4a757f93ffe
Studio                  : 0.479.0
Preview Features        : multiSchema

[janpio]

Did I understand correctly that you ran db pull on a Supabase database with a public.profiles table, then switched to another Supabase database and tried to run db push there and expected it to recreate the missing tables - but instead you got this error?

[nahtnam]

Yes that is correct. Specifically pull from a production db hosted on supabase.com and push to a locally hosted instance (follow until this step: https://supabase.com/docs/guides/cli/local-development#access-services)