Partial unique indexes are being recreated on every migration with prisma migrate

[JoniBR]

Bug description

We are using partial unique indexes, since we are implementing soft delete-adding deletedAt column to our models.
Since we want the columns to still behave as unique, we mark them with @unique,
and modify the migration file to add where ("deletedAt" is null) to the index creation.
For example:

CREATE UNIQUE INDEX "Employee_profileId_key" ON "Employee"("profileId") WHERE ("deletedAt" IS NULL);

Whenever we want to create a new migration file this warning shows:

• A unique constraint covering the columns `[profileId]` on the table `Employee` will be added. If there are existing duplicate values, this will fail.

although the index is already created, and the created index statement is added to the migration file.

How to reproduce

1. Mark a column as unique on the schema file.
2. Modify the migration file and add a condition to the index.
3. Run the migration.
4. Create a new migration.

Expected behavior

Should not try to create unique indexes again.

Prisma information

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

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

model Employee {
  id            String       @id @default(cuid())
  profileId     Int          @unique()
  deletedAt DateTime?

}

Environment & setup

• OS: MacOS Montrerey
• Database: PostgreSQL
• Node.js version: v16.14.0

Prisma Version

3.14.0

[jackbravo]

related to #3076 and #6974

[kicozaninetti]

Still same problem in version 5.3

@@index([index_field, created_at(sort: Desc)], map: "index_field_created_at_index")

When "prisma migrate dev" to deletes the index and re-create it again, generating a new migration folder.

[jove4015]

@janpio I appreciate that the best fix for this issue would be to support conditional uniqueness. However, it seems like it's going to take a long time for Prisma to get to this, if it ever does. Is there any way in the meantime to prioritize simply having Prisma ignore the conditions so that it doesn't try to keep recreating the index? That's all the OP is really asking for.

[janpio]

Simple answer: No. The correct way to deal with this is for Prisma to support partial indexes.
We can't spend implementation time on a configuration option to ignore specific things, that would just be obsolete when the actual feature is implemented.

[trevorr]

@janpio We agree that the correct way is supporting partial indexes, but since that feature hasn't progressed since it was requested nearly 4 years ago, maybe it's time to consider enabling a workaround?

[benjidotsh]

This is a really frustrating issue considering this exact issue is one of the reasons we switched from TypeORM to Prisma...

I get that Prisma can't support every niche feature a RDBMS has to offer like partial indexes, but if Prisma truly wants to "simplify working and interacting with databases", at least give us the choice to take matter into our own hands without getting in the way.

Ideally, we comment the wrong part of the migration out and it doesn't appear anymore.