JSON types should return a JSON object and not a JavaScript object

[thesunny]

Bug description

Currently, the TypeScript typings for a json column returns an object; however, a json column will only return a subset of an object type that can be processed through JSON.stringify and JSON.parse.

How to reproduce

Create any schema with a json type and view its type.

Expected behavior

The field type should be returned as a JSON object as opposed to a JavaScript object.

Use case: my API type checks that the return values for an API method are valid JSON objects and returning an object fails that test. I limit API responses to a JSON object (not a JavaScript object) to prevents values like Date objects from being returned which can't be encoded. This is common in many API's including for use in next.js which expects getInitialProps to return JSON values.

This is maybe not 100% a proper JSON value type (I'm unsure about where undefined should be in there) but as a starting point:

export type JSONValue =
  | string
  | number
  | boolean
  | null
  | undefined
  | JSONArray
  | JSONObject

export type JSONArray = Array<JSONValue>

export type JSONObject = {
  [key: string]: JSONValue
}

Prisma information

Any schema with a json column type

Environment & setup

2.0.0-beta.4
typescript only

[pantharshit00]

Thanks for reporting. I can surely confirm that we don't properly type JSON values right now.

But I won't consider this a bug but an improvement so I am labeling it accordingly. But still we need to fix the typings.

[thesunny]

@pantharshit00 The client is returning a JsonValue type but it is not valid JSON:

/**
 * From https://github.com/sindresorhus/type-fest/
 * Matches any valid JSON value.
 */
declare type JsonValue = string | number | boolean | null | Date | JsonObject | JsonArray

It is invalid JSON because it includes the Date object. This breaks code that returns the value through an API which ensures that data sent is JSON compatible. For example NextJS requires data to be in a JSON safe format and does not handle dates.

Related to:
prisma/prisma#2602

[thesunny]

Did a bit more research and it appears that MySQL allows dates into its JSON type which is not compatible with the JSON specification. I suspect this is why the type restriction was loosened; however, Postgres follows the specification properly.

Note: The usage of JSONValue references https://github.com/sindresorhus/type-fest/ but diverges from the reference. I think it may be appropriate to mention this.

May I suggest a switch during compilation or something that allows us to specify a proper JSON typing during the creation of the Prisma Client. This may seem a nitpick but not having this creates a lot of work for anything that needs to pass through an API layer that does not support dates. NextJS is one of the largest web frameworks and has this requirement for example.

What this means is that I have to manually modify the types on every return value from a Prisma call that returns a JSON object and modify it so that it can pass through the API without causing typescript errors. Or I have to forego type safety of dates; however, type safety is the reason I switched my database client to Prisma in the first place.

[Sytten]

I pointed the same thing here prisma/prisma#2602 (comment)

[thesunny]

LOL. I posted there saying the same thing in reverse. :)

If the goal is to support dates in MySQL's JSON, I think this works better as a different column type. It's like the difference between an int and bigint. One is a subset of the other, but they are different types.

Without this distinction, this will cause problems. For example, if an app relies on the json to have dates in it and then they switch from MySQL to Postgres, the app will cease to function. By making the choice explicit, this failure is avoidable in advance.

In order to make this more clear, the Postgres column type can be JsonB and MySQL could be JsonWithTemporal as temporal is what MySQL uses to describe the additions https://dev.mysql.com/doc/refman/8.0/en/json.html like this (Note also that they support date, time and datetime) types.:

As the examples illustrate, JSON arrays and objects can contain scalar values that are strings or numbers, the JSON null literal, or the JSON boolean true or false literals. Keys in JSON objects must be strings. Temporal (date, time, or datetime) scalar values are also permitted:

In my opinion, if we are only going to support one in the near term, it should be to stick to the strict JSON specification because it results in the least amount of surprise. If we develop an app with the stricter spec (a) it is correct to the JSON reference (b) it is named properly and (c) it works across every database type that supports a JSON type.

[Jolg42]

Thanks for your comments here, I will check this today 👍

[Jolg42]

So jsonValue is now the same as from type-fest, we had a misunderstanding internally about that type 😄

This is fixed in the latest alpha.

Thanks again 🙏

[thesunny]

Thank you @Jolg42 for the fix and the responsiveness.