Optional Unique ( String? @unique) not working in MongoDB

[muhad-bk]

Bug description

MongoDB Partial Indexes not creating

ts-node --transpile-only ./src/index.ts

PrismaClientKnownRequestError:
Invalid `prisma.user.create()` invocation in
PS C:\Users\muhad.bk\Desktop\playground\userOnboard> npm run tests

> useronboard@1.0.0 tests
> ts-node --transpile-only ./src/test.ts

done first
PrismaClientKnownRequestError:
Invalid `prisma.user.create()` invocation in
C:\Users\muhad.bk\Desktop\playground\userOnboard\src\test.ts:17:23

 
→ 17 await prisma.user.create(
  Unique constraint failed on the constraint: `User_mobile_key`
    at Object.request (\playground\userOnboard\node_modules\@prisma\client\runtime\index.js:45578:15)
    at async PrismaClient._request (\playground\userOnboard\node_modules\@prisma\client\runtime\index.js:46405:18) {
  code: 'P2002',
  clientVersion: '3.12.0',
  meta: { target: 'User_mobile_key' }
}

How to reproduce

schema

model User {
  id    String  @id @default(auto()) @map("_id") @db.ObjectId
  email     String?  @unique
  mobile    String?  @unique
} 

index.ts

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
    await prisma.$connect();

    await prisma.user.create({
        data: {
            email: "test1@gmail.com",
        }
    });
    console.log("done first");
    await prisma.user.create({
        data: {
            email: "test2@gmail.com",
        }
    });
}

main().catch(console.log).finally(async () => {
    await prisma.$disconnect();
});

Expected behavior

MongoDB Partial Indexes

Prisma Doc

It will create two new records where the email is set to NULL in the database.

Prisma information

model User {
  id    String  @id @default(auto()) @map("_id") @db.ObjectId
  email     String?  @unique
  mobile    String?  @unique
} 

Environment & setup

• OS: Windows
• Database: Mongo DB
• Node.js version: 16
• Prisma 3.12

Prisma Version

3.12

[janpio]

Did you run npx prisma db push to create the unique constraint and index on the database?

[muhad-bk]

Yes ,

npx prisma db push command running before project starts, that command created all the collections and unique constraints and indexes.

db: free shared mongodb atles instance

[muhad-bk]

Ref:
MongoDB Partial Indexes
Stack overflow Issue

NOTE:
As noted in the query coverage documentation for partial indexes:
Since MongoDB will not use the partial index for a query or sort operation if using the index results in an incomplete result set.
To use the partial index, a query must contain the filter expression (or a modified filter expression that specifies a subset of the filter expression) as part of its query condition.
EX. to use partial index for $type filter you have to use below filter:
{ "email": { "$eq": "foo@mail.com", "$type": "string" } }
// or
{ $and: [{ "email": "foo@mail.com" }, { "email": { $type: "string" } }] }
Playground

[janpio]

I do not understand.

Your initial message shows how the insert is declined as there is already a value. And that is also how it looks to me after running db push:

PS C:\Users\Jan\Documents\throwaway\12827> node .\index.js
done first
done second
PS C:\Users\Jan\Documents\throwaway\12827> npx prisma db push
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db"
Applying the following changes:

[+] Unique index `User_email_key` on ({"email":1})
[+] Unique index `User_mobile_key` on ({"mobile":1})


Your database is now in sync with your schema. Done in 1.77s

✔ Generated Prisma Client (3.12.0 | library) to .\node_modules\@prisma\client in 54ms

PS C:\Users\Jan\Documents\throwaway\12827> node .\index.js   
done first
PrismaClientKnownRequestError: 
Invalid `prisma.user.create()` invocation in
C:\Users\Jan\Documents\throwaway\12827\index.js:14:23

  11     }
  12 });
  13 console.log("done first");
→ 14 await prisma.user.create(
  Unique constraint failed on the constraint: `User_mobile_key`
    at Object.request (C:\Users\Jan\Documents\throwaway\12827\node_modules\@prisma\client\runtime\index.js:45578:15)
    at async PrismaClient._request (C:\Users\Jan\Documents\throwaway\12827\node_modules\@prisma\client\runtime\index.js:46405:18)
    at async main (C:\Users\Jan\Documents\throwaway\12827\index.js:14:5) {
  code: 'P2002',
  clientVersion: '3.12.0',
  meta: { target: 'User_mobile_key' }
}

What is "not working" for you then?

[muhad-bk]

Hi @janpio ,

Prisma 3.12 MongoDB not support partial unique indexs.

Here in the code, The first insert have a null value in that field, then other insertions are declined as there is already a value if the field is null or empty.

And posgre SQL prisma client support the same

[janpio]

@unique indeed only allows you to create a full unique index as described at https://www.mongodb.com/docs/manual/core/index-unique/.

If you need a partial unique index (as maybe described at https://www.mongodb.com/docs/manual/core/index-partial/#partial-index-with-unique-constraint), that would be a new feature for Prisma that we do not support at all yet and would need a feature request issue.

[dieeisenefaust]

Was doing some googling to make sure partial (and maybe wildcard?) indexes were on the roadmap. Glad to have found this issue.

@janpio - looks like you have slated this as an enhancement. From your comment above, I just wanted to make sure you didn't still need someone to open a feature request issue. I can do that, if required.

[janpio]

No, all good: #3076 or #6974

[okanji]

Any update on this?

[clearly-outsane]

This is driving me mad - is there no way to use partial indexes in prisma? maybe with a rawquery?

[muhad-bk]

@clearly-outsane , I think you can set manually in the MongoDB/ atlas console.

[amysak]

Currently facing the same issue while having an optionally present 1-to-1 relationship between models. As stated in mongodb docs mentioned in this comment, to handle such behavior, it would require creating partial index and only indexing documents with relation id field present (?). So, due to not being able to do so, I actually have no idea how can one handle optional 1-to-1's. Is this possible with PostgreSQL?

[janpio]

Feature request for this kind of index is now tracked at #6974. Closing this issue.