prisma: After upgrading to 3.0.2, can't create or update records when json field is null

I’m having a similar error to https://github.com/prisma/prisma/issues/9199 after upgrading to Prisma 3.0.2 (from 2.x)

I have also ran prisma migrate with no changes in order to cohere with Upgrade Path for referential actions and named constraints

datamodel in particualar:

model Room {
  id        String    @id @default(cuid()) @db.VarChar(30)
  name      String
  updatedAt DateTime  @updatedAt
  createdAt DateTime  @default(now())
  accountId String    @map("account") @db.VarChar(30)
  bgColor   Json?
  account   Account   @relation(fields: [accountId], references: [id])
  bookings  Booking[]

  @@index([accountId])
}

Mutation from Nexus:

    t.field('createRoom', {
      type: 'Room',
      args: {
        name: nonNull(stringArg()),
        accountId: nonNull(idArg()),
        bgColor: arg({type: "Json"}),
      },
      resolve: (_parent, args, ctx) => ctx.prisma.room.create({
        data: {
          ...args
        }
      })
    });

Error when bgColor is null (the same thing when creating or updating):

Invalid `prisma.room.create()` invocation: { data: { name: 'Test Room', accountId: 'ckm7v6zpi00130huil8hbwvlk', bgColor: null ~~~~ } } Argument bgColor for data.bgColor must not be null. Please use undefined instead.```

_Originally posted by @chemicalkosek in https://github.com/prisma/prisma/issues/9199#issuecomment-919430264_

About this issue

Original URL
State: closed
Created 3 years ago
Reactions: 1
Comments: 19 (6 by maintainers)

Most upvoted comments

That would mean rewriting even the simplest resolvers such as

 resolve: (_parent, args, ctx) => ctx.prisma.room.create({
        data: {
          ...args
        }
      })

to check for json null values. Can we make a default that will transform the null value to the previous behaviour? Or such thing will not be possible?

+23

chemicalkosek on Sep 21, 2021

Hey everyone, sorry for the confusion here. This is expected, but we missed this in our documentation. We stopped allowing nulls for JSON field inputs because:

prisma.foo.update({ where: { ... }, data: { someJsonColumn: null }});

The above code is ambiguous:

Did you mean setting someJsonField to the value null?
Or did you mean setting someJsonField to the database NULL?

The solution is to use DbNull and JsonNull. To insert a null value into a Json field, you would write:

import { Prisma } from '@prisma/client'

prisma.log.create({
  data: {
    meta: Prisma.JsonNull,
  },
})

And to insert a database NULL into a Json field, you would write:

import { Prisma } from '@prisma/client'

prisma.log.create({
  data: {
    meta: Prisma.DbNull,
  },
})

We’re going to improve this error message and also fix up our documentation.

matthewmueller on Sep 20, 2021

Hi, I’m also running into the same issue. I’m doing:

prisma.foo.update({ where: { ... }, data: { someJsonColumn: null }});

And am receiving the error message:

Argument someJsonColumn for data.someJsonColumn must not be null. Please use undefined instead.

However, if I understood things correctly, I’m not sure that this can be quite right:

By using null, I want to set a MySQL NULL value, regardless of what was there before
If I used undefined, I think that would let the value unchanged from whatever it was before

Is that a correct understanding? If so I think this is a genuine bug, and not an expected change?

robmurtagh on Sep 16, 2021

Would be nice to have a simple option that converts nulls to DbNulls automatically.

baptisteArno on Feb 8, 2023

I’d say that a good spot would be in “Null and undefined” section, as an explanation that e.g. “for data consistency purposes, Prisma utilises helper types, such as DbNull or JsonNull”. That’d explain, why not: null is misbehaving with Json type fields. Of course now that I’ve localised the exact explanation as mentioned, I can’t say it’s nonexistent - nevertheless, it would be of great help to put Json type null-ish values near the null-related topic.

mkramek on Jul 6, 2023

Hello! Can it be mentioned somewhere in the docs? I’ve stumbled upon this, since I’ve been looking for:

where: {
    field: {
        not: null
    }
}

on a Json type field, and Prisma was giving me errors. The solution was:

where: {
    field: {
        not: Prisma.DbNull
    }
}

Not sure if it’s mentioned, since my Googling didn’t show anything of the sort. Thanks in advance!

Edit: nevermind, just found it

mkramek on Jul 5, 2023

here is a handy function to convert all null to DbNull if that can help

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

export const replaceNullWithDbNull = (obj: Record<string, any>) => {
	for (const key in obj) {
		if (obj[key] === null) {
			obj[key] = Prisma.DbNull;
		} else if (typeof obj[key] === `object`) {
			replaceNullWithDbNull(obj[key]);
		}
	}

	return obj;
};

schulzf on Dec 19, 2022

I don’t think it is expected, we’ve been through it already in the linked issue. I’m not doing any filtering by Json. It’s about creating or updating a record with a null Json field value in the args

chemicalkosek on Sep 16, 2021