Prisma client not marking `Json` property as `null`

[thoughtspeed7]

Bug description

I am using Prisma version 3.8.1. Prisma client does not mark the User.oauthData property as nullable in TS. Can someone help? Prisma schema and generated SQL files below:

How to reproduce

Run prisma generate with attached schema file on MacOS (Postgres)

Expected behavior

No response

Prisma information

Schema:

generator client {
  provider = "prisma-client-js"
  binaryTargets = ["native", "rhel-openssl-1.0.x"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Account {
  id BigInt @id
  createdAt DateTime @db.Timestamptz(3) @default(now())
  updatedAt DateTime @db.Timestamptz(3) @updatedAt
  name String
  users User[]
}

model User {
  id BigInt @id
  createdAt DateTime @db.Timestamptz(3) @default(now())
  updatedAt DateTime @db.Timestamptz(3) @updatedAt
  accountId BigInt
  account Account @relation(fields: [accountId], references: [id])
  fullName String
  email String
  oauthData Json?
}

SQL:

-- CreateTable
CREATE TABLE "Account" (
    "id" BIGINT NOT NULL,
    "createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMPTZ(3) NOT NULL,
    "name" TEXT NOT NULL,

    CONSTRAINT "Account_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "User" (
    "id" BIGINT NOT NULL,
    "createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMPTZ(3) NOT NULL,
    "accountId" BIGINT NOT NULL,
    "fullName" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "oauthData" JSONB,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

-- AddForeignKey
ALTER TABLE "User" ADD CONSTRAINT "User_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "Account"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

Environment & setup

• OS: Mac OS
• Database: PostgreSQL
• Node.js version: 16.13.1

Prisma Version

prisma                  : 3.8.1
@prisma/client          : 3.8.1
Current platform        : darwin
Query Engine (Node-API) : libquery-engine 34df67547cf5598f5a6cd3eb45f14ee70c3fb86f (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli 34df67547cf5598f5a6cd3eb45f14ee70c3fb86f (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine    : introspection-core 34df67547cf5598f5a6cd3eb45f14ee70c3fb86f (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary           : prisma-fmt 34df67547cf5598f5a6cd3eb45f14ee70c3fb86f (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash    : 34df67547cf5598f5a6cd3eb45f14ee70c3fb86f
Studio                  : 0.452.0

[janpio]

Hm, when I try to create a User with no data, this is the error message I get:

C:\Users\Jan\Documents\throwaway\11779>node script.js
PrismaClientValidationError: 
Invalid `prisma.user.create()` invocation in
C:\Users\Jan\Documents\throwaway\11779\script.js:8:35

   5 // A `main` function so that we can use async/await
   6 async function main() {
   7   // Seed the database with users and posts
→  8   const user1 = await prisma.user.create({
         data: {
       +   id: BigInt,
       +   fullName: String,
       +   email: String,
       +   account: {
       +     create?: AccountCreateWithoutUsersInput | AccountUncheckedCreateWithoutUsersInput,     
       +     connectOrCreate?: AccountCreateOrConnectWithoutUsersInput,
       +     connect?: AccountWhereUniqueInput
       +   },
       ?   createdAt?: DateTime,
       ?   updatedAt?: DateTime,
       ?   oauthData?: NullableJsonNullValueInput | Json
         }
       })

Argument id for data.id is missing.
Argument fullName for data.fullName is missing.
Argument email for data.email is missing.
Argument account for data.account is missing.

This indicates that oauthData is indeed nullable.

[janpio]

When I remove the account I can also execute this minimal script:

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
  const user1 = await prisma.user.create({
    data: {
      id: 1n,
      fullName: "fullName",
      email: "foo@example.org"
    },

  })
  console.log("user1", user1)

}

main()
  .catch((e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

which outputs:

C:\Users\Jan\Documents\throwaway\11779>node script.js     
user1 {
  id: 1n,
  createdAt: 2022-02-14T11:23:40.255Z,
  updatedAt: 2022-02-14T11:23:40.255Z,
  fullName: 'fullName',
  email: 'foo@example.org',
  oauthData: null
}

Am I missing something?

[fjeddy]

I have the same issue, it seems that JSON type can't be set to null, even if it allows it in the database.

model guild {
  id                         Int                          @id @default(autoincrement()) @db.UnsignedInt
  guild_id                   String                       @unique(map: "guild_id") @db.VarChar(20)
  owner_id                   String                       @db.VarChar(20)
  name                       String?                      @db.VarChar(255)
  locale                     String                       @default("en") @db.VarChar(5)
  icon                       String?                      @db.VarChar(255)
  modules                    Json?
  allow_admins               Int                          @default(1) @db.UnsignedTinyInt
  settings                   Json?
  removed_at                 DateTime?                    @db.DateTime(0)
  created_at                 DateTime                     @default(now()) @db.DateTime(0)
  guild_channels             guild_channels[]
  guild_members              guild_members[]
  guild_members_activity     guild_members_activity[]
  guild_members_transactions guild_members_transactions[]
  guild_roles                guild_roles[]
}

Argument modules for update.modules must not be null. Please use undefined instead.
Argument settings for update.settings must not be null. Please use undefined instead.
Argument modules for create.modules must not be null. Please use undefined instead.
Argument settings for create.settings must not be null. Please use undefined instead.

[janpio]

Can you provide a minimal script similar to the one I posted above to reproduce this problem @fjeddy? Thanks.

[fjeddy]

Thank you for getting back to me @janpio, as requested.

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = "mysql://"
}

model janpio {
  id         Int      @id @default(autoincrement()) @db.UnsignedInt
  name       String   @db.VarChar(255)
  prisma     Json?
  prisimo    Json?
  created_at DateTime @default(now()) @db.DateTime(0)
}

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
  const workingInsert = await prisma.janpio.create({
    data: {
      name: 'Jan Piotrowski',
      prisma: ['array1', 'array2'],
      prisimo: { test: 'hello' }
    }
  })
  console.log('workingInsert', workingInsert)

  const failingInsert = await prisma.janpio.create({
    data: {
      name: 'Freddy Lundekvam',
      prisma: null,
      prisimo: { test: 'hello' }
    }
  })
  console.log('failingInsert', failingInsert)
}

main()
  .catch((e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

Results in

workingInsert {
  id: 5,
  name: 'Jan Piotrowski',
  prisma: [ 'array1', 'array2' ],
  prisimo: { test: 'hello' },
  created_at: 2022-03-31T00:03:31.000Z
}
PrismaClientValidationError:
Invalid `prisma.janpio.create()` invocation in
C:\Users\Freddy\Sites\prisma-test\seed.js:15:45

  12 })
  13 console.log('workingInsert', workingInsert)
  14
→ 15 const failingInsert = await prisma.janpio.create({
       data: {
         name: 'Freddy Lundekvam',
         prisma: null,
                 ~~~~
         prisimo: {
           test: 'hello'
         }
       }
     })

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

    at Object.validate (C:\Users\Freddy\Sites\prisma-test\node_modules\@prisma\client\runtime\index.js:38614:20)
    at PrismaClient._executeRequest (C:\Users\Freddy\Sites\prisma-test\node_modules\@prisma\client\runtime\index.js:40702:17)
    at consumer (C:\Users\Freddy\Sites\prisma-test\node_modules\@prisma\client\runtime\index.js:40646:23)
    at C:\Users\Freddy\Sites\prisma-test\node_modules\@prisma\client\runtime\index.js:40650:76
    at runInChildSpan (C:\Users\Freddy\Sites\prisma-test\node_modules\@prisma\client\runtime\index.js:39891:12)
    at C:\Users\Freddy\Sites\prisma-test\node_modules\@prisma\client\runtime\index.js:40650:20
    at AsyncResource.runInAsyncScope (node:async_hooks:201:9)
    at PrismaClient._request (C:\Users\Freddy\Sites\prisma-test\node_modules\@prisma\client\runtime\index.js:40649:86)
    at C:\Users\Freddy\Sites\prisma-test\node_modules\@prisma\client\runtime\index.js:37277:25
    at _callback (C:\Users\Freddy\Sites\prisma-test\node_modules\@prisma\client\runtime\index.js:37041:52) {
  clientVersion: '3.11.1'
}

[fjeddy]

Am I missing something?

Just an FYI in your test there, you are explicitly removing the JSON field from your create statement, leaving the default value to be handled by the database, not prisma. The problem occures when you're trying to specifically, purposely, set a JSON value as null.

Prismas validators let the JSON field pass as optional when not defined, (undefined), but it's wrongly marking it as invalid when you explictly set the JSON field to null :/ In other words, like in my case, I can create a row with null fields by not including the field / setting them as undefined, but once I set some data in the JSON field, I can never change it back to null, I have temporarily solved it by using empty arrays.

[aqrln]

Since null is ambiguous for Json fields, you can't use the JavaScript null value by design for them, even if they are nullable.

If you want to clear the stored value and store a database NULL, you need to pass Prisma.DbNull. If you want to store a JSON null value, you need to pass Prisma.JsonNull. These are special values that Prisma uses to differentiate between these two possible cases. When filtering, you can additionally use a third value — Prisma.AnyNull — if you want to filter on any kind of a null value in a nullable Json field. Please see the docs for more details: https://www.prisma.io/docs/concepts/components/prisma-client/working-with-fields/working-with-json-fields#filtering-by-null-values

That said, the error message here is misleading and should be improved. We should detect this case and tell about Prisma.DbNull/Prisma.JsonNull in the validation error instead of suggesting to use undefined.

[eviefp]

Just to clarify: this is about the error message the engine throws at runtime. The above code does produce a type error when compiling in Typescript.

One easy way to reproduce is,

model Repro {
  id          Int        @id @default(autoincrement())
  json        Json?
}

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient()

async function main() {
  await prisma.repro.create({
    data: {
      j: null
    }
  })
}

main()
  .catch((e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

And just run the above javascript file directly.

[janpio]

Next step: Reconfirm bug again with most recent Prisma version, compare error message according to https://github.com/prisma/team-orm/issues/222.