Postgres - Error: P3006 - Migration failed to apply cleanly to the shadow database

[exort-apps]

Question

I've been having issues applying changes with prisma migrate dev.
I was following the Baselining a database and Getting started with Prisma Migrate documentation pages.

Previously I was able to use migrate dev without issue but then a different member of the team made changes to the schema.prisma and pushed it without the migrate dev.

I understand that this is probably or most certainly the issue. Now I am trying to Baseline the database again and start all over just as I did in the beginning but I keep getting this error:

Error: P3006

Migration `baselining_dev_database` failed to apply cleanly to the shadow database.
Error:
ERROR: syntax error at or near ""
   0: schema_core::state::DevDiagnostic
             at schema-engine\core\src\state.rs:267

I removed the /migrations/previous_changes folders and introduced a new folder in the /migrations to generate the migration.sql file based on the current state of the database and then marked it as --applied before introducing new changes to the prisma.schema and using migrate dev

How do I target the shadow db and mark the changes as --applied there as well, if that is a possibility at all?

I tried db push on an exact copy of the database created with pg_restore that includes the data and it is working, I am just kind of afraid to use it on the production database.

The changes involve creating an Enum, a new table, and adding a column to an existing table for 1-to-many relation with the new table. So, no breaking changes, alas.

How to reproduce (optional)

Not sure how to reproduce from the get go as the issue could be just the shadow db.

Expected behavior (optional)

No response

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

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

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

model User {
  id                    String                 @id @default(cuid())
  name                  String?
  email                 String?                @unique
  emailVerified         DateTime?
  image                 String?
  isTwoFactorEnabled    Boolean                @default(false)
  password              String?
  role                  UserRole               @default(USER)
  accounts              Account[]
  twoFactorConfirmation TwoFactorConfirmation?
  a_webuser             a_webuser?
}

model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String?
  access_token      String?
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String?
  session_state     String?
  user              User    @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

model VerificationToken {
  id      String   @id @default(cuid())
  email   String
  token   String   @unique
  expires DateTime

  @@unique([email, token])
}

model PasswordResetToken {
  id      String   @id @default(cuid())
  email   String
  token   String   @unique
  expires DateTime

  @@unique([email, token])
}

model TwoFactorToken {
  id      String   @id @default(cuid())
  email   String
  token   String   @unique
  expires DateTime

  @@unique([email, token])
}

model TwoFactorConfirmation {
  id     String @id @default(cuid())
  userId String @unique
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model a_company {
  id         BigInt      @id @default(autoincrement())
  created_at DateTime    @default(now()) @db.Timestamptz(6)
  updated_at DateTime    @default(now()) @db.Timestamptz(6)
  name       String?     @db.VarChar
  a_project  a_project[]
  a_vendor   a_vendor[]
  a_webuser  a_webuser[]
}

model a_equipment {
  id                  BigInt                @id @default(autoincrement())
  created_at          DateTime              @default(now()) @db.Timestamptz(6)
  updated_at          DateTime              @default(now()) @db.Timestamptz(6)
  name                String?               @db.VarChar
  group_id            BigInt?
  type_id             BigInt?
  quote_generated     Boolean?
  quote_id            BigInt?
  a_group             a_group?              @relation(fields: [group_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "group_id")
  a_quote             a_quote?              @relation(fields: [quote_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "quote_id")
  a_type              a_type?               @relation(fields: [type_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "type_id")
  a_equipmentproperty a_equipmentproperty[]
  a_packageequipment  a_packageequipment[]

  @@index([quote_id], map: "fki_quote_id")
}

model a_budgetingequipment {
  id                 BigInt               @id @default(autoincrement())
  created_at         DateTime             @default(now()) @db.Timestamptz(6)
  updated_at         DateTime             @default(now()) @db.Timestamptz(6)
  name               String?              @db.VarChar
  group_id           BigInt?
  type_id            BigInt?
  quote_id           BigInt?
  quote_generated    Boolean              @default(false)
  a_budgeteqprop     a_budgeteqprop[]
  a_group            a_group?             @relation(fields: [group_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "group_id")
  a_quote            a_quote?             @relation(fields: [quote_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "quote_id")
  a_type             a_type?              @relation(fields: [type_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "type_id")
  a_packageequipment a_packageequipment[]

  @@index([quote_id], map: "fki_budgeteq_quote_id")
}

model a_equipmentproperty {
  id              BigInt          @id @default(autoincrement())
  created_at      DateTime        @default(now()) @db.Timestamptz(6)
  updated_at      DateTime        @default(now()) @db.Timestamptz(6)
  value           String?         @db.VarChar
  equipment_id    BigInt?
  typeproperty_id BigInt?
  comment         String?         @db.VarChar(500)
  compliancy_tag  CompliancyTags?
  a_equipment     a_equipment?    @relation(fields: [equipment_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "equipment_id")
  a_typeproperty  a_typeproperty? @relation(fields: [typeproperty_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "typeproperty_id")
}

model a_budgeteqprop {
  id                   BigInt                @id @default(autoincrement())
  created_at           DateTime              @default(now()) @db.Timestamptz(6)
  updated_at           DateTime              @default(now()) @db.Timestamptz(6)
  value                String?
  budgeting_eq_id      BigInt?
  compliancy_tag       CompliancyTags?
  comment              String?
  property_id          BigInt?
  property_name        String?
  label_id             BigInt?
  label_name           String?
  type_property_id     BigInt?
  label_section_rank   BigInt? 
  a_budgetingequipment a_budgetingequipment? @relation(fields: [budgeting_eq_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "budgeting_eq_id")
}

model a_group {
  id                   BigInt                 @id @default(autoincrement())
  created_at           DateTime               @default(now()) @db.Timestamptz(6)
  updated_at           DateTime               @default(now()) @db.Timestamptz(6)
  name                 String?                @db.VarChar
  a_budgetingequipment a_budgetingequipment[]
  a_equipment          a_equipment[]
  a_type               a_type[]
}

model a_invite {
  id         BigInt   @id @default(autoincrement())
  created_at DateTime @default(now()) @db.Timestamptz(6)
  email      String   @unique
  isUsed     Boolean  @default(false)
  forRole    UserRole @default(USER)
  forCompany BigInt?
  forVendor  BigInt?
}

model a_label {
  id             BigInt           @id @default(autoincrement())
  created_at     DateTime         @default(now()) @db.Timestamptz(6)
  updated_at     DateTime         @default(now()) @db.Timestamptz(6)
  label          String?          @db.VarChar
  section_rank   BigInt           @default(autoincrement())
  a_typeproperty a_typeproperty[]
}

model a_package {
  id                 BigInt               @id @default(autoincrement())
  created_at         DateTime             @default(now()) @db.Timestamptz(6)
  updated_at         DateTime             @default(now()) @db.Timestamptz(6)
  project_id         BigInt?
  status_id          BigInt?
  package_type       String?
  a_project          a_project?           @relation(fields: [project_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "project_id")
  a_status           a_status?            @relation(fields: [status_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "status_id")
  a_packageequipment a_packageequipment[]
  a_quote            a_quote[]
}

model a_packageequipment {
  id                   BigInt                @id @default(autoincrement())
  created_at           DateTime              @default(now()) @db.Timestamptz(6)
  updated_at           DateTime              @default(now()) @db.Timestamptz(6)
  equipment_id         BigInt?
  package_id           BigInt?
  budgeting_eq_id      BigInt?
  a_budgetingequipment a_budgetingequipment? @relation(fields: [budgeting_eq_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "budgeting_eq_id")
  a_equipment          a_equipment?          @relation(fields: [equipment_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "equipment_id")
  a_package            a_package?            @relation(fields: [package_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "package_id")
}

model a_project {
  id          BigInt      @id @default(autoincrement())
  created_at  DateTime    @default(now()) @db.Timestamptz(6)
  updated_at  DateTime    @default(now()) @db.Timestamptz(6)
  name        String?     @db.VarChar
  description String?     @db.VarChar
  company_id  BigInt?
  webuser_id  BigInt?
  status_id   BigInt?
  a_package   a_package[]
  a_company   a_company?  @relation(fields: [company_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "company_id")
  a_status    a_status?   @relation(fields: [status_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "status_id")
  a_webuser   a_webuser?  @relation(fields: [webuser_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "webuser_id")
}

model a_property {
  id             BigInt           @id @default(autoincrement())
  created_at     DateTime         @default(now()) @db.Timestamptz(6)
  updated_at     DateTime         @default(now()) @db.Timestamptz(6)
  name           String?          @db.VarChar
  a_typeproperty a_typeproperty[]
}

model a_quote {
  id                   BigInt                 @id @default(autoincrement())
  created_at           DateTime               @default(now()) @db.Timestamptz(6)
  updated_at           DateTime               @default(now()) @db.Timestamptz(6)
  package_id           BigInt?
  vendor_id            BigInt?
  single_equipment     Boolean?
  quote_reference      String?                @default("") @db.VarChar
  contact_name         String?                @db.VarChar
  tenderers_reference  String?                @db.VarChar
  location             String?                @db.VarChar
  telephone            String?                @db.VarChar
  telephone_mobile     String?                @db.VarChar
  fascimile            String?                @db.VarChar
  email                String?                @db.VarChar
  status_id            BigInt?
  a_budgetingequipment a_budgetingequipment[]
  a_equipment          a_equipment[]
  a_package            a_package?             @relation(fields: [package_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "package_id")
  a_status             a_status?              @relation(fields: [status_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "status_id")
  a_vendor             a_vendor?              @relation(fields: [vendor_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "vendor_id")
}

model a_status {
  id                BigInt      @id @default(autoincrement())
  created_at        DateTime    @default(now()) @db.Timestamptz(6)
  updated_at        DateTime    @default(now()) @db.Timestamptz(6)
  name              String      @db.VarChar
  is_package_status Boolean     @default(false)
  is_quote_status   Boolean     @default(false)
  is_project_status Boolean     @default(false)
  a_package         a_package[]
  a_project         a_project[]
  a_quote           a_quote[]
}

model a_type {
  id                   BigInt                 @id @default(autoincrement())
  created_at           DateTime               @default(now()) @db.Timestamptz(6)
  updated_at           DateTime               @default(now()) @db.Timestamptz(6)
  name                 String?                @db.VarChar
  group_id             BigInt?
  a_budgetingequipment a_budgetingequipment[]
  a_equipment          a_equipment[]
  a_group              a_group?               @relation(fields: [group_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "group_id")
  a_typeproperty       a_typeproperty[]
}

model a_typeproperty {
  id                  BigInt                @id @default(autoincrement())
  created_at          DateTime              @default(now()) @db.Timestamptz(6)
  updated_at          DateTime              @default(now()) @db.Timestamptz(6)
  name                String?               @db.VarChar
  type_id             BigInt?
  label_id            BigInt?
  property_id         BigInt?
  a_equipmentproperty a_equipmentproperty[]
  a_label             a_label?              @relation(fields: [label_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "lable_id")
  a_property          a_property?           @relation(fields: [property_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "property_id")
  a_type              a_type?               @relation(fields: [type_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "type_id")
}

model a_vendor {
  id                  BigInt      @id @default(autoincrement())
  created_at          DateTime    @default(now()) @db.Timestamptz(6)
  updated_at          DateTime    @default(now()) @db.Timestamptz(6)
  name                String?     @db.VarChar
  contact_name        String?     @default("Contact name") @db.VarChar
  tenderers_reference String?     @default("Tenderer's Reference") @db.VarChar
  location            String?     @default("Location") @db.VarChar
  telephone           String?     @default("Telephone") @db.VarChar
  telephone_mobile    String?     @default("Mobile") @db.VarChar
  fascimile           String?     @default("Fascimile") @db.VarChar
  email               String?     @default("email@placeholder.com") @db.VarChar
  company_id          BigInt?
  a_quote             a_quote[]
  a_company           a_company?  @relation(fields: [company_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
  a_webuser           a_webuser[]
}

model a_webuser {
  id         BigInt      @id @default(autoincrement())
  created_at DateTime    @default(now()) @db.Timestamptz(6)
  updated_at DateTime    @default(now()) @db.Timestamptz(6)
  name       String?     @db.VarChar
  company_id BigInt?
  user_id    String?     @unique
  can_invite Boolean?    @default(false)
  vendor_id  BigInt?
  a_project  a_project[]
  user       User?       @relation(fields: [user_id], references: [id])
  a_company  a_company?  @relation(fields: [company_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "company_id")
  a_vendor   a_vendor?   @relation(fields: [vendor_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "vendor_id")
}

model br_equipmentgroup_spec {
  id                BigInt   @id @default(autoincrement())
  created_at        DateTime @default(now()) @db.Timestamptz(6)
  updated_at        DateTime @default(now()) @db.Timestamptz(6)
  value             String?  @db.VarChar
  equipmentgroup_id BigInt?
  groupspec_id      BigInt?
}

model br_equipmenttype_spec {
  id               BigInt   @id @default(autoincrement())
  created_at       DateTime @db.Timestamptz(6)
  updated_at       DateTime @db.Timestamptz(6)
  value            String?  @db.VarChar
  equipmenttype_id BigInt?
  typespec_id      BigInt?
}

model equipment {
  id         BigInt   @id @default(autoincrement())
  created_at DateTime @db.Timestamptz(6)
  updated_at DateTime @db.Timestamptz(6)
  name       String?  @db.VarChar
}

model equipmentgroup {
  id          BigInt   @id @default(autoincrement())
  created_at  DateTime @default(now()) @db.Timestamptz(6)
  updated_at  DateTime @default(now()) @db.Timestamptz(6)
  name        String?  @db.VarChar
  description String?  @db.VarChar
}

model equipmenttype {
  id          BigInt   @id @default(autoincrement())
  created_at  DateTime @db.Timestamptz(6)
  updated_at  DateTime @db.Timestamptz(6)
  name        String?  @db.VarChar
  description String?  @db.VarChar
}

model frontenduser {
  first_name String? @db.VarChar(30)
  last_name  String? @db.VarChar(30)
  email      String  @unique
  password   String
  id         Int     @id @default(autoincrement())
}

model groupspec {
  id         BigInt   @id(map: "groupspecs_pkey") @default(autoincrement())
  created_at DateTime @default(now()) @db.Timestamptz(6)
  updated_at DateTime @default(now()) @db.Timestamptz(6)
  name       String?  @db.VarChar
  value      String?  @db.VarChar
  unit       String?  @db.VarChar
}

model typespec {
  id         BigInt   @id @default(autoincrement())
  created_at DateTime @default(now()) @db.Timetz(6)
  updated_at DateTime @default(now()) @db.Timetz(6)
  name       String?  @db.VarChar
  value      String?  @db.VarChar
  unit       String?  @db.VarChar
}

model unit {
  id         BigInt   @id @default(autoincrement())
  created_at DateTime @default(now()) @db.Timestamptz(6)
  updated_at DateTime @default(now()) @db.Timetz(6)
  unit       String   @db.VarChar
}

enum UserRole {
  ADMIN
  USER
  VENDOR
}

enum CompliancyTags {
  COMPLIANT
  COMPLIANT_WITH_QUALIFICATIONS
  NON_COMPLIANT
  CLARIFICATION_REQUIRED
}

// Add any relevant Prisma Client queries here

• OS: Windows 11
• Database: Neon PostgreSQL through Vercel
• Node.js version: v20.9.0
  Prisma version -- prisma: 5.11.0 -- @prisma/client: 5.11.0

[exort-apps]

Alrighty, how I solved this shadow database issue of mine...

One part of the problem was migrate diff created a migration.sql file with UTF-8 with BOM encoding instead of just UTF-8.
I was able to pass through the 'baselining_dev_database' migration file after changing to UTF-8, but I came across a different shadow database error on some relations already existing.

Since I don't particularly care for the long-term tracking of changes in the database structure, I decided to delete all the records in the "_prisma_migrations" table in the database and removed the "/prisma/migrations" folder.

Started the "Baselining a database" steps all over again, and this time no issues on shadow database after introducing changes with migrate dev.

The main issue is not the lack of documentation or existing bugs, but rather trying it all out by yourself, misstepping, and maybe not knowing it will cause an issue in the future.

[Zabi17]

@exort-apps Bro help me out !!
the same thing happened to me as well but even after deleting the migration folder im unable to fix it:
i tried this : prisma migrate dev --name init --create-only
if no error then i would have done this next: npx prisma migrate resolve --applied 20260416_init

Error: P3006

Migration 000_baseline failed to apply cleanly to the shadow database.
Error code: P1001
Error:
Can't reach database server at ep-sweet-bread-an0f991o92.c-5.us-east-1.aws.neon.tech:5432

Please make sure your database server is running at ep-sweet-bread-an0f1o92.c-5.us-east-1.aws.neon.tech:5432. @exort-apps

[exort-apps]

Hi @Zabi17, first and foremost are you sure you can actually reach the database at AWS?

For example, I get the same error if I don't whitelist my current IP address on my database hosting platform...