Prisma migration fails with postgres

[tareksalem]

Bug description

I have the following schema

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

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  referentialIntegrity = "prisma"
  schemas = ["account", "server"]
}

model User{
  @@map("users")
  id String @id @default(uuid())
  firstName String @map("first_name")
  lastName String @map("last_name")
  username String
  email String @unique
  password String
  role String
  account Account @relation(fields: [accountId], references: [zacId])
  accountId String @map("account_id")
  verified Boolean? @default(false)
  active Boolean? @default(true)
  verificationCode String?
  createdAt DateTime? @default(now()) @map("created_at")
  updatedAt DateTime? @updatedAt @default(now()) @map("updated_at")
  @@index([id, email, verified, verificationCode])

  @@schema("account")
}

model Account {
  @@map("accounts")
  id String @id @default(uuid())
  zacId String @unique
  users User[]
  active Boolean? @default(false)
  paymentMethods PaymentMethod[]
  currentPlan Plan?
  createdAt DateTime? @default(now()) @map("created_at")
  updatedAt DateTime? @updatedAt @default(now()) @map("updated_at")

  @@schema("account")
}

model PaymentMethod {
  @@map("payment_methods")
  id String @id @default(uuid())
  account Account @relation(fields: [accountId], references: [zacId])
  primary Boolean? @default(true)
  accountId String @map("account_id")
  billingAddress BillingAddress @relation(fields: [billingAddressId], references: [id])
  billingAddressId String @unique @map("billing_address_id")
  createdAt DateTime? @default(now()) @map("created_at")
  updatedAt DateTime? @updatedAt @default(now()) @map("updated_at")

  @@schema("account")
}

model BillingAddress {
  @@map("billing_addresses")
  id String @id @default(uuid())
  email String
  address String
  postalCode String @map("postal_code")
  paymentMethod PaymentMethod?
  createdAt DateTime? @default(now()) @map("created_at")
  updatedAt DateTime? @updatedAt @default(now()) @map("updated_at")

  @@schema("account")
}

model Plan{
  @@map("plans")
  id String @id @default(uuid())
  originalId String
  price Decimal
  duration Int @default(0)
  title String
  account Account @relation(fields: [accountId], references: [zacId])
  accountId String @unique @map("account_id")
  features Json
  subscribedAt DateTime? @default(now()) @map("subscribed_at")
  createdAt DateTime? @default(now()) @map("created_at")
  updatedAt DateTime? @updatedAt @default(now()) @map("updated_at")

  @@schema("account")
}

model Project {
  @@map("projects")
  id String @id @default(uuid())
  name String
  accountId String @map("account_id")
  createdAt DateTime? @default(now()) @map("created_at")
  updatedAt DateTime? @updatedAt @default(now()) @map("updated_at")

  @@schema("server")
}

I run the first command to push my schema to db as the following:

npx prisma db push

then I did an update on the following model:

model Project {
  @@map("projects")
  id String @id @default(uuid())
  name String
  testId String?  // added this column
  accountId String @map("account_id")
  createdAt DateTime? @default(now()) @map("created_at")
  updatedAt DateTime? @updatedAt @default(now()) @map("updated_at")

  @@schema("server")
}

then I run the following command:

npx prisma migrate dev

it fails with the following error:

Error: P3006

Migration `20221016115041_project_update` failed to apply cleanly to the shadow database. 
Error:
db error: ERROR: relation "users" already exists
   0: sql_migration_connector::validate_migrations
             at migration-engine/connectors/sql-migration-connector/src/lib.rs:289
   1: migration_core::state::DevDiagnostic
             at migration-engine/core/src/state.rs:251

when I looked on the generated migration script I found that prisma regenerates the migration for the whole schema even the not changed tables!

that's why it outputs this error

Also I don't know why the generated migration script doesn't have CREATE IF NOT EXIST instead of CREATE

because this may fail also!

How to reproduce

Expected behavior

No response

Prisma information

Error: P3006

Migration 20221016115041_project_update failed to apply cleanly to the shadow database.
Error:
db error: ERROR: relation "users" already exists
0: sql_migration_connector::validate_migrations
at migration-engine/connectors/sql-migration-connector/src/lib.rs:289
1: migration_core::state::DevDiagnostic
at migration-engine/core/src/state.rs:251

Environment & setup

• OS: Ubuntu
• Database: PostgreSQL
• Node.js version: 17

Prisma Version

4.3.1

[janpio]

Duplicate of #15826