prisma: Incorrect InputJsonValue type

This behaviour makes duplicating documents in mongo very cumbersome. I want to do this:

prisma.content.create({
  data: {
    ...currentDocument
    // change some of it's fields
  }
})

But because of this difference in types I have to manually go through all the json fields. May be I should do this differently?

This is not a bug, we explicitly designed the API this way — but it’s now clear that it might not be optimal or convenient in all cases.

What would have been a bug, though, is if nested null values inside a JSON value were prohibited:

  const data = await prisma.data.create({
    data: {
      json: {
        property: null,
      },
    },
  })

However, this is not the case — the snippet above works fine and doesn’t produce any errors.

As for the top-level nulls in JSON fields, they are prohibited due to being ambiguous. Instead, you have to explicitly specify Prisma.DbNull or Prisma.JsonNull to choose the kind of “null” you want, so Prisma knows what you mean.

We should make the documentation about it more discoverable, because the title of the section refers to “Filtering by null values”, while this also applies to create, insert and upsert — which is also explicitly stated in the documentation, but I understand it might be hard to find it.

Now, as for it not being possible to re-use the value that you got from the database — I agree that it is not very ergonomic. The problem here is that we lose information when retrieving the value from the DB: Prisma returns JavaScript null for both JSON null and DB NULL, while our insert operations are more strict. However, returning Prisma.DbNull and Prisma.JsonNull in results would come with even more ergonomics and DX implications. What we should do here and how we should change it in the future major versions is a subject for discussion, but for now it’s what the API contract is.

The correct way to do this is like this:

const record = await prisma.data.findFirst()

await prisma.data.update({
  where: { id: 1 },
  data: {
    ...record,
    json: record.json !== null
      ? record.json
      : Prisma.JsonNull, // or `Prisma.DbNull` — depending on what you need
  },
})

Do not override the type system and cast it (i.e., jsonField: record.jsonField as Prisma.JsonObject) like some of the workarounds above suggest. The types are correct and represent the actual expectations — what those workarounds suggest will fail at run time if jsonField is null. If null is accepted by runtime validation — please let us know, that would be a bug!

Finally, none of this is relevant to MongoDB, and null must be accepted at the top level there. Please open an issue if that’s not the case for you, and you encounter this issue with MongoDB.

I’ll update the issue description and change the labels accordingly.

work around using spread:

        savedUser = await prisma().user.update({
            where: {
                id: user.id,
            },
            data: {
                ...user,
                postMetaData: user.postMetaData as Prisma.JsonObject,
            },
            include: userForAuthenticationInclude,
        });

This is quite annoying… if I don’t do this I get ->

  Types of property 'postMetaData' are incompatible.
    Type 'JsonValue' is not assignable to type 'string | number | boolean | InputJsonObject | InputJsonArray | undefined'.
      Type 'null' is not assignable to type 'string | number | boolean | InputJsonObject | InputJsonArray | undefined'

Full code for context:

export const saveUser = async function (
    request: FastifyRequest,
    userForAuthentication: UserForAuthentication,
    verifyOrg = false
): Promise<UserForAuthentication> {
    if (verifyOrg) {
        verifyOrganizationInMemberships(request, userForAuthentication.memberships);
    }
    // Need to remove included relations before saving data object
    // eslint-disable-next-line
    const { auth, memberships, ...user } = userForAuthentication;
    let savedUser: UserForAuthentication | null;
    try {
        savedUser = await prisma().user.update({
            where: {
                id: user.id,
            },
            data: {
                ...user,
                postMetaData: user.postMetaData as Prisma.JsonObject,
            },
            include: userForAuthenticationInclude,
        });
    } catch (error) {
        throw request.generateError<SaveUserErrorCodes>(
            httpCodes.INTERNAL_SERVER_ERROR,
            'ERROR_SAVING_USER',
            error
        );
    }

    return savedUser;
};

and then in user.model.ts

import { Prisma } from '@prisma/client';

import { MembershipWithOrgSelect } from '#public-types/membership';

export type AuthInclude = {
    authFacebook: true;
    authGithub: true;
    authGoogle: true;
    authLocal: true;
    authTwitter: true;
};
export type UserForAuthentication = Prisma.UserGetPayload<{
    include: {
        auth: {
            include: AuthInclude;
        };
        memberships: {
            select: MembershipWithOrgSelect;
        };
    };
}>;

export const userForAuthenticationInclude = {
    auth: {
        include: {
            authFacebook: true,
            authGithub: true,
            authGoogle: true,
            authLocal: true,
            authTwitter: true,
        },
    },
    memberships: {
        select: {
            id: true,
            organizationId: true,
            organization: {
                select: {
                    name: true,
                },
            },
            role: {
                select: {
                    name: true,
                },
            },
            groups: {
                select: {
                    id: true,
                    name: true,
                },
            },
        },
    },
};

Just want to confirm this bug still exists in 3.7.0 and I just stumbled upon it today.

Thanks for the issue. For now, the workaround is:

import { Prisma } from "@prisma/client"

const rec = prisma.anyModel.findUnique({});
if (rec.jsonField != null) {
  prisma.anyModel.update({  data: { jsonField: rec.jsonField  } })
} else {
  // decide on the type of 
  // https://www.prisma.io/docs/concepts/components/prisma-client/working-with-fields/working-with-json-fields#filtering-by-null-values
  prisma.anyModel.update({  data: { jsonField: Prisma.JsonNull  } })
}

I think InputJsonObject should allow nulls as its field values even if InputJsonValue does not allow null.

Thanks for the issue. For now, the workaround is:

import { Prisma } from "@prisma/client"

const rec = prisma.anyModel.findUnique({});
if (rec.jsonField != null) {
  prisma.anyModel.update({  data: { jsonField: rec.jsonField  } })
} else {
  // decide on the type of 
  // https://www.prisma.io/docs/concepts/components/prisma-client/working-with-fields/working-with-json-fields#filtering-by-null-values
  prisma.anyModel.update({  data: { jsonField: Prisma.JsonNull  } })
}

Did not work for me, instead, I’m typing the value as a Prisma.JsonObject:

prisma.anyModel.update({
  data: {
    jsonField: rec.jsonField as Prisma.JsonObject
  }
})

Not sure if it works with every use case, though…

Any updates on that? I’m wondering how this issue is not prioritized and have more traction here, because we just updated from 2.3.0 to 3.7.0 and all our code is full with this problem (yes, we have many Json columns 😦 ).

   * `null` cannot be used as the value of a JSON field because its meaning
   * would be ambiguous. Use `Prisma.JsonNull` to store the JSON null value or
   * `Prisma.DbNull` to clear the JSON value and set the field to the database
   * NULL value instead.

It seems like this is actually intentional, but then it’s hard to update a single field of a JsonObject and write the result back into the database — any field might be “null” and the workaround above doesn’t work.