Is there a way to pass additional non-unique conditions to a

where

block in a Prisma

*Unique

operation? In our case, we have a

tenantId

on a model and would like users to only be able to read/edit/delete objects with a

tenantId

that matches the tenant they belong to. Ideally, we could just pass the

tenantId

along with a unique field (like

id

) e.g.

prisma.model.findUnique({where: {id: <id>, tenantId: <tenantId>}})

. For finds, this isn't really an issue because you can always check the

tenantId

after you fetch the object, but for mutations, it currently requires either, fetching the object first, verifying it's

tenantId

and then performing the mutation, or using a Prisma

updateMany

or

deleteMany

and passing both the

id

and

tenantId

into the

where

block of the

*Many

operation. Using a

*Many

operation concerns me a bit because if the

id

is ever

undefined

the operation would then apply to significantly more objects than intended; whereas a

*Unique

operation would just fail. I'm going to guess that Prisma doesn't support this as they use the

*Many

trick in the soft-deletion middleware docs (here). One workaround we've found is that you can add a

@@unique

constraint to

id

+

tenantId

(e.g.

@@unique([id, tenantId])

). This then allows you to perform any

*Unique

operation against an

id

+

tenantId

pair. Is this the best way to do this as we are now introducing an additional index? Alternatively, you could make it a composite id with

@@id([id, tenantId])

, but then

id

field would no longer be required to be unique unless you additionally added

@unique

to the

id

field. Has anyone used any of these method and found something they recommend?

@Casey Graff 👋 The only way for now as you suggested is using a composite index via

@@unique

. This will help you perform operations with

update

instead of

updateMany

.

Hey Ryan, thanks for the update. For my own edification, do you know if this (adding additional non-unique filters) isn't supported just because it hasn't been added or if this is specifically not provided for some performance, database compatibility, etc. reason?

I would say it’s just a separation of operations.

update

is only for a single record whereas

updateMany

is for multiple records. To make sure just a single record is updated and returned, querying a unique index is necessary.