Auto-inclusion of deletedAt: null with oneToOne "field": { is: null } in findFirst

[ivatra]

Whenever I try to perform a findFirst operation with a condition where "field": { is: null }, it automatically includes deletedAt: null in the query. Here is the code that reproduces the problem:

import { PrismaClient } from "@prisma/client";
import { createSoftDeleteMiddleware } from "prisma-soft-delete-middleware";

const prisma = new PrismaClient();

prisma.$use(
  createSoftDeleteMiddleware({
    models: {
      Key: true,
      UserKey: true,
    },
    defaultConfig: {
      field: "deletedAt",
      createValue(deleted) {
        if (deleted) return new Date();
        return null;
      },
    },
  }),
);

prisma.key
  .findFirst({
    where: {
      userKey: { is: null },
    },
  })
  .then((k) => console.log(k));

Here are the schema models:

model Key {
  id        Int       @id @default(autoincrement())
  key       String    @unique
  deletedAt DateTime?
  userKey UserKey?
}

model UserKey {
  id        Int       @id @default(autoincrement())
  keyId     Int       @unique
  deletedAt DateTime?

  key  Key  @relation(fields: [keyId], references: [id])
} 

And this is the error:

PrismaClientValidationError:
Invalid `.findFirst()` invocation in
C:\Coding\ivatra\prod\javascript\phoenix_dashboard\nest_backend\src\prisma_test.ts:23:4

  20 );
  21
  22 prisma.key
→ 23   .findFirst({
         where: {
           userKey: {
             is: null,
             deletedAt: null,
             ~~~~~~~~~
       ?     isNot?: UserKeyWhereInput | Null
           },
           deletedAt: null
         }
       })

Unknown argument `deletedAt`. Available options are listed in green.
    at xn (C:\Coding\ivatra\prod\javascript\phoenix_dashboard\nest_backend\node_modules\@prisma\client\runtime\library.js:116:5852)
    at vn.handleRequestError (C:\Coding\ivatra\prod\javascript\phoenix_dashboard\nest_backend\node_modules\@prisma\client\runtime\library.js:123:6429)
    at vn.handleAndLogRequestError (C:\Coding\ivatra\prod\javascript\phoenix_dashboard\nest_backend\node_modules\@prisma\client\runtime\library.js:123:6119)
    at vn.request (C:\Coding\ivatra\prod\javascript\phoenix_dashboard\nest_backend\node_modules\@prisma\client\runtime\library.js:123:5839)
    at async l (C:\Coding\ivatra\prod\javascript\phoenix_dashboard\nest_backend\node_modules\@prisma\client\runtime\library.js:128:9763)
    at async nestedMiddleware (C:\Coding\ivatra\prod\javascript\phoenix_dashboard\nest_backend\node_modules\prisma-nested-middleware\dist\lib\createNestedMiddleware.js:32:28) {
  clientVersion: '5.2.0'
}

Expected Behavior

The query should be executed with only userKey: { is: null } without automatically including deletedAt: null.

Actual Behavior

The deletedAt: null condition is automatically added when userKey: { is: null } is used, causing an error.

Environment

Prisma-soft-delete-middleware version: 1.1.2
Prisma version: 5.2.0
Node.js version: 18.12.1

[olivierwilkinson]

Heya, Thanks for raising this!

When you are querying for { is: null } I'm guessing it's to ensure the relation is unset right? If that is the case this is an interesting problem, because if the model at the other end of the relation is one configured for soft-delete then the query may result in incorrect results. For example if UserKey has been soft-deleted the relation between Key and UserKey still exists so { is: null } is not true, however you probably want it to treat the relation as if it was null right?

I think probably the best bet is to handle this case by changing { where: { is: null } } to the following or something similar:

{
  where: {
    OR: [
      {  is: null },
      { NOT: { deletedAt: null } }
    ],
  },
}

Would that work for your use case?

[ivatra]

I figured out a way out of this situation. Instead {"relation":{is:null}}, you can use {"relation":null} and it will work as expected. I didn't fully understand difference between null and is:null, only that is:null is newer. Using plain prisma with both cases i achieve the same result, so middleware should also work properly in both cases, i believe. This is a working query:

prisma.key
  .findFirst({
    where: {
      userKey: null
    },
  })
  .then((k) => console.log(k));

[olivierwilkinson]

I'm glad you found an alternative! I'll leave this open because it's still true that {is: null} doesn't work as expected.