Why is Prisma Migrate detecting a drift

[machhindra10]

Question

While creating from second migration and when "relationMode=foreignKey" then Why is Prisma Migrate detecting a drift and explaining it as foreign keys were removed and then the same foreign keys were added again? and saying data loss message do you want to proceed, if yes then prisma applies all the migrations with data loss

How to reproduce (optional)

Expected behavior (optional)

expected behavior should be create migration and apply only one migration that is created

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

// Add your schema.prisma

// Add any relevant Prisma Client queries here

• OS:

• Database:

• Node.js version:

• Run prisma -v to see your Prisma version and paste it

[ludralph]

Hi @machhindra10 👋

Prisma Migrate detects a drift when the current state of the database schema has diverged from the end-state of the migration history. This can occur due to manual changes in the database or due to changes in the Prisma schema that are not yet reflected in the database. (https://www.prisma.io/docs/orm/prisma-migrate/workflows/troubleshooting#schema-drift)

However, the relation mode foreignKey or prisma is not directly responsible for the drift. The drift is detected when there are changes in the database schema that are not reflected in the migration history. If you switch the relation mode and don't create a new migration, this could indeed cause a drift.

The data loss message is a warning that Prisma Migrate provides when it determines that the changes cannot be applied without potentially losing data. This is to ensure that you are aware of the potential consequences before proceeding with the migration. If you need to apply migrations to other environments (like production) where data loss is not acceptable, you should use prisma migrate deploy, which applies migrations without resetting the database.

To resolve schema drifts, you can use the migrate diff command to compare your migration history and revert changes made to your database schema. You can then apply the changes to your database using the db execute command. See the documentation

[machhindra10]

Hi @ludralph

Yes you are correct and I understand why drift happens,

but in my case, when I am using relationMode = "prisma" and creates the initial migration then in the migratin file prisma does not create foreign key constraint even if we provided @relation attribute in schema.prisma file so no issues of drift and working fine.

But when using relationMode = "foreignKeys" and at the time of creating initial migration prisma creates the initial migration file with foreign key contraints like

-- AddForeignKey
ALTER TABLE appointments ADD CONSTRAINT FK_Appointments_MUser_DoctorId FOREIGN KEY (doctorId) REFERENCES mUser(id) ON DELETE RESTRICT ON UPDATE NO ACTION;

So after that I did not change in the database and while creating second and further migrations prisma detects (assumes) that these contraints has been removed and displays drift detected message with data loss prompt, and if we say Y (yes) then prisma applies its all migrations again while creating foreign key contraints again and again

please try to understand the issue with scenario, if you have any doubt then feel free to ask

Thanks
@machhindra10