I'm getting "Your database schema is not in sync with your migration history" every time I change something

[tigran1100]

Bug description

Every time I want to make a change in my schema.prisma file, the terminal is saying that I have to reset my database.

For example this is my "Issues" model

model Issue {
  id               Int      @id @default(autoincrement())
  title            String   @db.VarChar(255)
  description      String   @db.Text
  status           String   @default("open")
  created_at       DateTime @default(now())
  updated_at       DateTime @updatedAt
  assigned_to_user_id String   @db.VarChar(255)
  assigned_to_user User? @relation(fields: [assigned_to_user_id], references: [id])
}

When I add new column, for example`
just_test_column String @db.Text

And the I run npx prisma migrate dev, it's asking me to reset my database.

How to reproduce

This is the error message`

PS E:\Win11Dev\Xampp\htdocs\issue-tracker-v4> npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": MySQL database "Issue_tracker" at "localhost:3306"

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

[*] Changed the account table
[-] Removed foreign key on columns (userId)

[*] Changed the issue table
[-] Removed foreign key on columns (assigned_to_user_id)

[*] Changed the session table
[-] Removed foreign key on columns (userId)

× We need to reset the MySQL database "Issue_tracker" at "localhost:3306"
Do you want to continue? All data will be lost. ... no

Reset cancelled.

Expected behavior

I expect prisma to not require reset every time I change something

Prisma information

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

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

model Issue {
  id               Int      @id @default(autoincrement())
  title            String   @db.VarChar(255)
  description      String   @db.Text
  descriptasdion      String   @db.Text
  status           String   @default("open")
  created_at       DateTime @default(now())
  updated_at       DateTime @updatedAt
  assigned_to_user_id String   @db.VarChar(255)
  assigned_to_user User? @relation(fields: [assigned_to_user_id], references: [id])
}

model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String? @db.Text
  access_token      String? @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String? @db.Text
  session_state     String?

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

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

model User {
  id            String    @id @default(cuid())
  name          String?
  email         String?   @unique
  emailVerified DateTime?
  image         String?
  accounts      Account[]
  sessions      Session[]
  assigned_issues Issue[]
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

Environment & setup

• OS: Windows 11
• Database: MySql
• Node.js version: 20.11.0

Prisma Version

"@auth/prisma-adapter": "^1.3.3",
"@next-auth/prisma-adapter": "^1.0.7",
"@prisma/client": "^5.9.1",
"prisma": "^5.9.1",

[ludralph]

Hi @tigran1100 👋

Thank you for raising this question.

When you attempt to run npx prisma migrate dev after making changes to your schema.prisma file, Prisma Migrate checks for drift and, if detected, suggests resetting the database to ensure that the migration history and the database schema are in sync. This is a safety mechanism to prevent unintended data loss or schema inconsistencies. Drift occurs when the current state of your database schema does not match the expected schema based on your migration history. This can happen for various reasons, such as manual changes to the database or discrepancies between the schema and migration files.

If you do not want to reset the database, you can baseline the database by using prisma migrate diff to compare your schema and database, and save the output into a SQL file. The steps to baseline are written in the documentation.

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 🙏

[King-witcher]

Where is the migration history actually stored in the dev mode? I noticed that it's not on _migrations_history because I have deleted this table and Prisma still recognizes when the database is or not in sync somehow.

[B3nsten]

Where is the migration history actually stored in the dev mode? I noticed that it's not on _migrations_history because I have deleted this table and Prisma still recognizes when the database is or not in sync somehow.

Did you find a solution to this? I'm facing the same issue. No table on the database itself, no migrations folder present. Yet it detects something somewhere.

[leoamato10]

same here

[binarybaba]

I think the only thing which is saved is in _prisma_migrations table.

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that haven’t had any recent activity and appear to be outdated.

If this discussion is still important to you or unresolved, we’d love to hear from you! Feel free to reopen it or start a new one with updated details.

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and being part of the community!

[JefferyHus]

This is absolutely one of the reasons I regret choosing Prisma, where the hell is your history saved? The default shadow database according to your documentation is auto created everytime I run the migrations, but the historical updates are nowhere to be found which keeps causing this issue for me

[grinbeard92]

Have to agree with this, I'm moving over to a direct Postgres library like https://github.com/porsager/postgres... huge waste of time going back and forth on migrations switching between dev and prod.

Especially terrible if trying to migrate an existing database into prisma, there should be a straightforward way to force prisma to respect the database schema if the user wants to go that route instead of clearing the database.

[antolabr]

i have the answer to prevent that bug : you just have to remove all uppercase letter in you db name, like changing Issue_tracker to issue_tracker. i made a github issue (#27712 )

[AtumaDavid]

this should help: Prisma Migration Tax