[BUG] Prisma not supporting optional @unique indexes in MongoDB

[Nik-Novak]

Bug description

** THE PROBLEM**
OPTIONAL @unique indexes are erroring with duplicate null/undefined values.
This means that two records that omit an optionally @unique field will clash

THE SOLUTION
What Prisma should be doing is setting Sparse = true on any index of an optional @unique field
But I am having to manually remember to do this for every index from an optional @unique

If we mark an OPTIONAL field foo @unique, run:
db.collection.createIndex( { foo: 1 }, { unique:true, sparse: true } )

If we mark a REQUIRED field foo @unique, run:
db.collection.createIndex( { foo: 1 }, { unique:true )

^ This is all prisma would have to do, currently it runs the second in both cases

This is how I'm manually editing the indexes created by Prisma:

How to reproduce

Expected behavior

No response

Prisma information

model Player {
  id        String    @id @default(auto()) @map("_id") @db.ObjectId
  steam_id  String    @unique
  ...
  user      User?     @relation(fields: [user_id], references: [id])
  user_id   String?    @db.ObjectId @unique
  ...
  @@map("players")
}
model User {
  id            String    @id @default(auto()) @map("_id") @db.ObjectId
  name          String?
  email         String?   @unique
  ...
  player        Player?
  ...
  @@map("users")
}

//create two players and don't assign them to a User
database.player.create( ... )
database.player.create ( ... )
//commandline
prisma db push (FAILS)

Environment & setup

• OS: Debian/Windows
• Database: MongoDB
• Node.js version: 20.12.0

Prisma Version

5.11.0

[janpio]

Prisma currently does not support sparse unique indexes. There is a feature request tracking them here: #3419 Would that solve your problem if/when we implement this @Nik-Novak?

[Nik-Novak]

Prisma currently does not support sparse unique indexes. There is a feature request tracking them here: #3419 Would that solve your problem if/when we implement this @Nik-Novak?

Absolutely