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]

I think that is pretty much expected behavior.

db push and migrate dev are two different systems. The first time you call migrate dev it will try to create the database from scratch. If you use migrate dev for the initial creation, the next time you run it, it should be able to just detect the necessary changes and create those into a file.

(You can also of course keep using db push - but then you have no record of the individual migrations that were created and executed)

[tareksalem]

thank you for your comment, I actually tried to create my DB at first time with the migrate dev command but got the same result, Prisma doesn't record the migration, and everytime regenerate the whole SQL script

also as I mentioned, I don't know why Prisma doesn't add CREATE IF NOT EXIST instead of using directly CREATE to avoid such cases

[janpio]

Because that is exactly the job of migrate, to generate migrations that do not get into such situations.
Can you please try to recreate this again with migrate dev only and then report if you end up in the same situation again?
If so, can you please exactly describe the steps and commands you are running? Thanks.

[JosepAlacid]

So, if you created your DB with db push, you have to update your DB always using db push or your your data will get lost.
If you want to update your db as you go on with your coding, you need to start your db with prisma migrate.

[janpio]

You can also switch from db push to migrate, but then Prisma will treat it as a new start and you will need to use baselining to backfill the initial migration: https://www.prisma.io/docs/guides/database/developing-with-prisma-migrate/baselining Then the next one should properly create a diff for the next migration.

[tareksalem]

I cleared my db and started my schema on a new db with the following command:

prisma migrate dev --name init

this command succeeded and created my db tables and needed schemas

then I added a field to my schema as mentioned in the issue and run this command

prisma migrate dev --name first-update

this command failed and returned that error:

Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20221020203914_update

Database error code: 42P07

Database error:
ERROR: relation "users" already exists

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E42P07), message: "relation \"users\" already exists", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("heap.c"), line: Some(1200), routine: Some("heap_create_with_catalog") }

when I checked the created migration script I found it contains the whole sql commands for the whole schema not just the updated field

here is the migration file content:

-- CreateSchema
CREATE SCHEMA IF NOT EXISTS "account";

-- CreateSchema
CREATE SCHEMA IF NOT EXISTS "server";

-- CreateTable
CREATE TABLE "account"."users" (
    "id" TEXT NOT NULL,
    "first_name" TEXT NOT NULL,
    "last_name" TEXT NOT NULL,
    "username" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "password" TEXT NOT NULL,
    "role" TEXT NOT NULL,
    "account_id" TEXT NOT NULL,
    "verified" BOOLEAN DEFAULT false,
    "active" BOOLEAN DEFAULT true,
    "verificationCode" TEXT,
    "created_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "account"."accounts" (
    "id" TEXT NOT NULL,
    "zacId" TEXT NOT NULL,
    "active" BOOLEAN DEFAULT false,
    "created_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "accounts_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "account"."payment_methods" (
    "id" TEXT NOT NULL,
    "primary" BOOLEAN DEFAULT true,
    "account_id" TEXT NOT NULL,
    "billing_address_id" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "payment_methods_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "account"."billing_addresses" (
    "id" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "address" TEXT NOT NULL,
    "postal_code" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "billing_addresses_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "account"."plans" (
    "id" TEXT NOT NULL,
    "originalId" TEXT NOT NULL,
    "price" DECIMAL(65,30) NOT NULL,
    "duration" INTEGER NOT NULL DEFAULT 0,
    "title" TEXT NOT NULL,
    "account_id" TEXT NOT NULL,
    "features" JSONB NOT NULL,
    "subscribed_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
    "created_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "plans_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "server"."projects" (
    "id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "myId" TEXT NOT NULL,
    "account_id" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "projects_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "users_email_key" ON "account"."users"("email");

-- CreateIndex
CREATE INDEX "users_id_email_verified_verificationCode_idx" ON "account"."users"("id", "email", "verified", "verificationCode");

-- CreateIndex
CREATE UNIQUE INDEX "accounts_zacId_key" ON "account"."accounts"("zacId");

-- CreateIndex
CREATE UNIQUE INDEX "payment_methods_billing_address_id_key" ON "account"."payment_methods"("billing_address_id");

-- CreateIndex
CREATE UNIQUE INDEX "plans_account_id_key" ON "account"."plans"("account_id");

this is the reason that the migration fails

you can do the same steps I mentioned and you will get the same issue

thank you

[tareksalem]

If you noticed, I am using @@schema to specify the related schema for each model

I tried to remove the multiSchema option and added all models to the same schema and the migration is working fine,

the problem is related to multi schema option, it regenerates the whole sql commands again

[janpio]

Oh shoot, I indeed did not notice.

From #1122 (comment):

❗️Migrate and introspection are currently not supported with multiple schemas. If you rely on them, please do not try this preview feature in the main branch of your project or in production.

So unfortunately for now this is expected in this early preview feature.

[pimeys]

Ok, so this issue does not happen anymore with 4.7.1 or main.