Migration ... failed to apply cleanly to the shadow database. The relation "..." already exists

[kravenhunter]

Question

Bug description
Hello. I added a new model to prisma schema and tried to create a new migration and got this error.

npx prisma migrate dev --name changes                 
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": PostgreSQL database "postgres", schema "public" at "aws-0-eu-central-1.pooler.supabase.com:5432"    

Error: P3006

Migration `20231004180313_init` failed to apply cleanly to the shadow database.
Error:
db error: ERROR: relation "PageContents" already exists
   0: schema_core::state::DevDiagnostic
             at schema-engine\core\src\state.rs:270

But first migration had created fine.

The error occure when I add the Contacts model

New model

model Contacts {
  id                String      @id @default(uuid())
  timeStamp         DateTime    @default(now())
  imageBgLink        String
  imagePreviewLink   String? 
  title             String     
  shortDescription String?
  description_1    String?
  description_2    String?
  description_3    String?
  description_4    String?
  phone             String?
  mobile           String?
  email             String?
  socialLink_1       String?
  socialLink_2       String?
  socialLink_3       String?
  socialLink_4       String?
  socialLink_5       String?
  copyright          String? 
     
}

How to reproduce (optional)

Expected behavior (optional)

I expected opportunity to create new tables in my database when some tables in db allready have filled without dropping all tables,migration adn create it again

Information about Prisma Schema, Client Queries and Environment (optional)

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}



model PageContents {
  id               String   @id @default(uuid())
  timeStamp        DateTime @default(now())
  imageBgLink      String
  imagePreviewLink String?
  galary_link_1    String?
  galary_link_2    String?
  galary_link_3    String?
  galary_link_4    String?
  title            String
  subTitle         String
  shortDescription String
  description_1    String
  description_2    String?
  description_3    String?
  description_4    String?
}

model UserProfile {  
  id         String   @id  @unique
  email       String
  timeStamp   DateTime @default(now())
  date_of_birth  String
  phone      String
  login_name  String
  first_name String
  last_name  String
  country    String
  adress     String
  avatar     String
  avatarPreview String
  type       UserTypeStatus

}

model Basket {
  id         String @id @default(uuid())
 timeStamp        DateTime @default(now())
  product_id String
  count      Int
  user_id    String
}

model Product {
  id               String   @id @default(uuid())
  timeStamp        DateTime @default(now())
  imageBgLink      String
  imagePreviewLink String?
  galary_link_1    String
  galary_link_2    String
  galary_link_3    String
  galary_link_4    String
  likes            Int
  dislikes         Int
  title            String
  subTitle         String
  category         Category
  price            Decimal
  productCode      Decimal
  discont          Int
  size             String
  weight           Decimal
  dimension        String
  dough            String
  calories         Decimal
  fat              Decimal
  arbohydrates     Decimal
  carbohydrates    Decimal
  shortDescription String
  description_1    String
  description_2    String?
  description_3    String?
  description_4    String?
}
enum UserTypeStatus {
  Administrator
  Moderator
  User
}

enum Category {
  Pizza
  Burger
  Salad
  Soup
  Sushi
  Dessert
  Drinks
  First
  Second
  FastFood
  Additionals
}`

Prisma Client queries

CREATE TABLE "PageContents" (
    "id" TEXT NOT NULL,
    "timeStamp" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "imageBgLink" TEXT NOT NULL,
    "imagePreviewLink" TEXT,
    "galary_link_1" TEXT,
    "galary_link_2" TEXT,
    "galary_link_3" TEXT,
    "galary_link_4" TEXT,
    "title" TEXT NOT NULL,
    "subTitle" TEXT NOT NULL,
    "shortDescription" TEXT NOT NULL,
    "description_1" TEXT NOT NULL,
    "description_2" TEXT,
    "description_3" TEXT,
    "description_4" TEXT,

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

-- CreateTable
CREATE TABLE "UserProfile" (
    "id" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "timeStamp" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "date_of_birth" TEXT NOT NULL,
    "phone" TEXT NOT NULL,
    "login_name" TEXT NOT NULL,
    "first_name" TEXT NOT NULL,
    "last_name" TEXT NOT NULL,
    "country" TEXT NOT NULL,
    "adress" TEXT NOT NULL,
    "avatar" TEXT NOT NULL,
    "avatarPreview" TEXT NOT NULL,
    "type" "UserTypeStatus" NOT NULL,

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

-- CreateTable
CREATE TABLE "Basket" (
    "id" TEXT NOT NULL,
    "timeStamp" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "product_id" TEXT NOT NULL,
    "count" INTEGER NOT NULL,
    "user_id" TEXT NOT NULL,

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

-- CreateTable
CREATE TABLE "Product" (
    "id" TEXT NOT NULL,
    "timeStamp" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "imageBgLink" TEXT NOT NULL,
    "imagePreviewLink" TEXT,
    "galary_link_1" TEXT NOT NULL,
    "galary_link_2" TEXT NOT NULL,
    "galary_link_3" TEXT NOT NULL,
    "galary_link_4" TEXT NOT NULL,
    "likes" INTEGER NOT NULL,
    "dislikes" INTEGER NOT NULL,
    "title" TEXT NOT NULL,
    "subTitle" TEXT NOT NULL,
    "category" "Category" NOT NULL,
    "price" DECIMAL(65,30) NOT NULL,
    "productCode" DECIMAL(65,30) NOT NULL,
    "discont" INTEGER NOT NULL,
    "size" TEXT NOT NULL,
    "weight" DECIMAL(65,30) NOT NULL,
    "dimension" TEXT NOT NULL,
    "dough" TEXT NOT NULL,
    "calories" DECIMAL(65,30) NOT NULL,
    "fat" DECIMAL(65,30) NOT NULL,
    "arbohydrates" DECIMAL(65,30) NOT NULL,
    "carbohydrates" DECIMAL(65,30) NOT NULL,
    "shortDescription" TEXT NOT NULL,
    "description_1" TEXT NOT NULL,
    "description_2" TEXT,
    "description_3" TEXT,
    "description_4" TEXT,

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

-- CreateIndex
CREATE UNIQUE INDEX "UserProfile_id_key" ON "UserProfile"("id");

• OS: Windows 11

• I use Supabase service with PostgreSQL Database

• Node.js version: v18.16.0

• Prisma and Prisma Client version 5.3.1

[ludralph]

Hi @kravenhunter 👋

Thank you for raising this question.

You are getting this error because the state of your local development database and Prisma schema are not in sync.

You can try resetting the database using the prisma migrate reset command and then try reapplying the migrations again using the prisma migrate dev command again.

If this answers your question, it would be great if you could mark this Discussion as answered to indicate that it has been resolved.

Otherwise please let us know how else we can help you further or close the Discussion if it was resolved in some other way 🙏

[kravenhunter]

I'm sorry that I didn't answer about the pooler connection because at first didn't get what you meant. But yes, you was right. I used the Pooling Custom Configuration instead of the NodeJS connection. So ,I 've change it to the NodeJS connection string and now I'm getting a new error:

Error: P3006
Migration `20231004180313_init` failed to apply cleanly to the shadow database.
Error:
db error: ERROR: type "UserTypeStatus" does not exist
   0: schema_core::state::DevDiagnostic
             at schema-engine\core\src\state.rs:270

That's strange , because I have the UserProfile model created in database and which uses the UserTypeStatus enum type.

-- CreateTable
CREATE TABLE "UserProfile" (
    "id" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "timeStamp" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "date_of_birth" TEXT NOT NULL,
    "phone" TEXT NOT NULL,
    "login_name" TEXT NOT NULL,
    "first_name" TEXT NOT NULL,
    "last_name" TEXT NOT NULL,
    "country" TEXT NOT NULL,
    "adress" TEXT NOT NULL,
    "avatar" TEXT NOT NULL,
    "avatarPreview" TEXT NOT NULL,
    "type" "UserTypeStatus" NOT NULL,

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

I've created a user in the UserProfile table and tried run migration again but it didn.'t worked, the same error , that type "UserTypeStatus" does not exist.

[janpio]

In the migration file that is committed to your repository it indeed uses the UserTypeStatus enum type, but does not actually create it:
https://github.com/kravenhunter/pizza/blob/fae0bc9959709c97a0c98dc7d264426094f64f03/prisma/migrations/20231004180313_init/migration.sql#L36C13-L36C27

It should actually look like this:

-- CreateEnum
CREATE TYPE "UserTypeStatus" AS ENUM ('Administrator', 'Moderator', 'User');

-- CreateEnum
CREATE TYPE "Category" AS ENUM ('Pizza', 'Burger', 'Salad', 'Soup', 'Sushi', 'Dessert', 'Drinks', 'First', 'Second', 'FastFood', 'Additionals');

-- CreateTable
CREATE TABLE "PageContents" (
    "id" TEXT NOT NULL,
    "timeStamp" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "imageBgLink" TEXT NOT NULL,
    "imagePreviewLink" TEXT,
    "galary_link_1" TEXT,
    "galary_link_2" TEXT,
    "galary_link_3" TEXT,
    "galary_link_4" TEXT,
    "title" TEXT NOT NULL,
    "subTitle" TEXT NOT NULL,
    "shortDescription" TEXT NOT NULL,
    "description_1" TEXT NOT NULL,
    "description_2" TEXT,
    "description_3" TEXT,
    "description_4" TEXT,

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

-- CreateTable
CREATE TABLE "UserProfile" (
    "id" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "timeStamp" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "date_of_birth" TEXT NOT NULL,
    "phone" TEXT NOT NULL,
    "login_name" TEXT NOT NULL,
    "first_name" TEXT NOT NULL,
    "last_name" TEXT NOT NULL,
    "country" TEXT NOT NULL,
    "adress" TEXT NOT NULL,
    "avatar" TEXT NOT NULL,
    "avatarPreview" TEXT NOT NULL,
    "type" "UserTypeStatus" NOT NULL,

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

-- CreateTable
CREATE TABLE "Basket" (
    "id" TEXT NOT NULL,
    "timeStamp" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "product_id" TEXT NOT NULL,
    "count" INTEGER NOT NULL,
    "user_id" TEXT NOT NULL,

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

-- CreateTable
CREATE TABLE "Product" (
    "id" TEXT NOT NULL,
    "timeStamp" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "imageBgLink" TEXT NOT NULL,
    "imagePreviewLink" TEXT,
    "galary_link_1" TEXT NOT NULL,
    "galary_link_2" TEXT NOT NULL,
    "galary_link_3" TEXT NOT NULL,
    "galary_link_4" TEXT NOT NULL,
    "likes" INTEGER NOT NULL,
    "dislikes" INTEGER NOT NULL,
    "title" TEXT NOT NULL,
    "subTitle" TEXT NOT NULL,
    "category" "Category" NOT NULL,
    "price" DECIMAL(65,30) NOT NULL,
    "productCode" DECIMAL(65,30) NOT NULL,
    "discont" INTEGER NOT NULL,
    "size" TEXT NOT NULL,
    "weight" DECIMAL(65,30) NOT NULL,
    "dimension" TEXT NOT NULL,
    "dough" TEXT NOT NULL,
    "calories" DECIMAL(65,30) NOT NULL,
    "fat" DECIMAL(65,30) NOT NULL,
    "arbohydrates" DECIMAL(65,30) NOT NULL,
    "carbohydrates" DECIMAL(65,30) NOT NULL,
    "shortDescription" TEXT NOT NULL,
    "description_1" TEXT NOT NULL,
    "description_2" TEXT,
    "description_3" TEXT,
    "description_4" TEXT,

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

-- CreateIndex
CREATE UNIQUE INDEX "UserProfile_id_key" ON "UserProfile"("id");

Did you modify the migration file manually?

[kravenhunter]

Absolutely not. I only worked with prisma schema. And It looks strange because I can add Product records and UserProfile records to the database that contains Category enum and UserTypeStatus enum. and I don't recieve any error. I guess the Category and UserTypeStatus somehow had been created in the database but didn't added to the migration file. And I checked a timestamp of the migration file and the created time matches to the change time till seconds, so the migration file definitely didn't edit.

[janpio]

Can you start over, create a new database and delete the migration file to see if you can reproduce this again?

[kravenhunter]

It's finally started working. I saved my data , then I add 2 strings to migration file where creates UserTypeStatus and Category enums. Then I tried to run command "npx prisma migrate dev --name changes" and the prisma ran reset command and delete all my tables ,then
it recreated the migration and tables. So, now I can add new models and create new migration without any error. @janpio @ludralph thank you very much for your help and your detailed answers!!

[janpio]

Awesome.

Let us know when you figure out what went wrong, maybe there is a way we avoid that in the future for other people.

[johannbuscail]

@kravenhunter can you explain how you solved it ? I'm facing the same problem here.

[kravenhunter]

@johannbuscail If youre using Supabase then go to your supabase accout and copy the NodeJS connection string instead . of using Pooling Custom Configuration . Then run next command npm prisma migrate reset and try to create a new migration npm prisma migrate dev -name

[shoyeb001]

This error occurs when you delete you previous migration folders or some of the migration folders are missing. So you can manage the previous migration files and run npx prisma migrate reset. But if you dont get the previous folder then delete all the migrations and run npx prisma migrate reset and then npx prisma migrate dev --name init.