Define type of content of Json field

[MaximNd]

Problem

Right now if you have the following schema with Json field:

model User {
  id               Int  @default(autoincrement()) @id
  name             String?
  extendedProfile  Json
}

You'll end up with a problem that you don't have strict type for extendedProfile field in .ts.

const user = prismaService.user.findOne(...);
user.extendedProfile // we don't know the type of extendedProfile

The one way to fix it, is specify some interface in your code and use it like this:

interface UserProfile {
    field1: string;
    field2: number;
}

const user = prismaService.user.findOne(...);
(user.extendedProfile as UserProfile).field1; // now we have autocompletion

But it's not really comfortable to use it like that each time.

Also we can create some class and create instance of it like that:

interface UserProfile {
    field1: string;
    field2: number;
}

class User {
    id: string;
    name?: string;
    extendedProfile: UserProfile;

    constructor(user: PrismaUser /* user object returned by prisma */) {
        // ... initialize
    }
}

const user = new User(prismaService.user.findOne(...));

But this solution creates some overhead due to the creation of an additional object.

Suggested solution

Maybe we can specify type in schema.prisma file like that?

json ExtendedUserProfileJson {
    field1  String
    field2  Int
}

model User {
  id               Int  @default(autoincrement()) @id
  name             String?
  extendedProfile  ExtendedUserProfileJson
}

Alternatives

Alternatively, we can somehow manage this in the typescript.

[MaximNd]

Have you looked into this?

Advanced usage of generated types

Yes, but i don't understand how it can help in this problem. I may be wrong, but you cannot define the shape of the Json field using this tutorial..

[Sytten]

Interesting idea, though all fields should be nullable since there is no guarantee for JSON format enforced by the database.

[MaximNd]

Interesting idea, though all fields should be nullable since there is no guarantee for JSON format enforced by the database.

Probably, if the database you are using does not have JSON support, then you simply cannot use the Json field type and this feature.

[pantharshit00]

Probably, if the database you are using does not have JSON support, then you simply cannot use the Json field type and this feature.

I think @Sytten is talking about the fact in Json type say in postgres doesn't enforce any schema. It will not guarantee that all data there follows the shape you define. The proposal you have defined here is will all be enforced on application level.

Also, @MaximNd why not define a 1-1 relation here if you want a schema to be maintained, just interested to know. The main selling point of Json type personally for me is it allows me to throw data in it without worrying about it's shape.

[MaximNd]

Probably, if the database you are using does not have JSON support, then you simply cannot use the Json field type and this feature.

I think @Sytten is talking about the fact in Json type say in postgres doesn't enforce any schema. It will not guarantee that all data there follows the shape you define. The proposal you have defined here is will all be enforced on application level.

Also, @MaximNd why not define a 1-1 relation here if you want a schema to be maintained, just interested to know. The main selling point of Json type personally for me is it allows me to throw data in it without worrying about it's shape.

Yes you are right. In this example I can define a 1-1 relationship, or if I store an array in a Json field, I can define a 1-m relationship. But sometimes, when you have a simple data structure (for example, only a few fields) and you know that this json data will be relate only to this entry, then it is easier to define a Json field. The advantage of this is that you don't need to run an additional query or use JOINS to get the relational data. There might also be a rare case when some users that move their database from NoSql like Mongo to relational database. And there will be a lot of destructured data.

[janpio]

Being able to type your Json fields is a simple and understandable feature requests. Although there might be workarounds, this might very well be something that Prisma could offer in the future on its own - so having this feature request is valid.

[husayt]

I have tried the following workaround. It works fine, until I need a field other than number or string, e.g. Date. Without date field the approach below works. I tried to use transformers for string to date conversion, but that contradicts Prisma.InputJsonObject definition.

import { Type } from "class-transformer/decorators";
import { IsOptional, Length } from "class-validator";

export class Qualification implements Prisma.InputJsonObject {
  @Length(1, 30)
  name?: string;

  @IsOptional()
  age?:number;

  @IsOptional()
  @Type(() => Date)
  birthday?: Date;

  [index: string]: Prisma.JsonValue ;
}

Any suggestions?

[ahouck]

@husayt What is this approach? I googled "InputJsonObject" and prisma and only came up with this post and prisma/docs#669 which only has "InputJsonObject" in a list

[luxaritas]

A note on potential interaction with #2431 and #2444: As JSON may be used for static or dynamic content, this should be opt in - though ideally, you could still select and filter on specific keys even if you have not typed the JSON field. In the specific use case I'm thinking of, I would actually maintain a separate table that would define which rows contain which keys. Specifically, I have a puzzle with multiple solutions. A solution has a JSON field defining some details which depend on the configuration of the puzzle it is for.

[zackdotcomputer]

I just ran into a need for this similar to OP's - I am using JSON as a way to store reliably structured data that is small, has several child fields, and is always 1:1 linked with the parent. Because of that, it feels wasteful to break it out into another table and require JOINs on every query.

I was expecting something to be available that was akin to the custom scalar operator in the GraphQL SDL - where you can define a name for a field type (and, in Prisma's case, define what type the column should be given in the schema) and then you are responsible for defining the shape yourself in the client code. You could imagine it working something like:

generator client {
  provider = "prisma-client-js"
  scalarDefinitions {
    RuleContents = "../../../prisma-scalars/RuleContents.ts"
  }
}

scalar RuleContents @underlyingType(Json)

model Rule {
  id  String  @id @default(uuid())
  rejectRule  RuleContents?
  favoriteRule  RuleContents?
}

Then the referenced Typescript file would export an interface with the same name as your scalar. In the simplest form, the client would just be applying a cast for data read from the DB and type checking on data written to or queried from it. As an optional future enhancement the scalar definition file might even be able to export a custom serialize and deserialize function that the Prisma client would use to transform data before writing to/reading from the database.

Finally, if you were generating a client where the scalar type wasn't provided, the client could fall back to the @underlyingType and you'd get the same behavior we have now - e.g. if your underlying type was Json it would fallback to a basic any in Typescript, if your underlying was Unsupported(Polygon) it would fall back to hiding the field, etc.

[mmahalwy]

Adding some light to this, it's a common problem with GraphQL. If an input sets the shape of an object, this will fail type validation when interacting with Prisma. Here is an example where ApplicationFieldConfig[] is coming from my GraphQL library.

[kladnik]

This would be a huge lifesaver! Right now I have to cast each property to unknown and then my own type, while also removing the need of having our own handling for JSON types.

[adrianthedev]

I don't want to start a bad trend here, but +1 from me too! I'd like to be able to customize/extend Json column types in TypeScript.

Thanks for all your work here 🔺!

[glebbash]

Solved the issue with typechecking in Nest.js application by using class-transformer's classToPlain + plainToClass:

[sandbox-apps]

I don't want to start a bad trend here, but +1 from me too! I'd like to be able to customize/extend Json column types in TypeScript.

Thanks for all your work here 🔺!

Yes really mainly because MongoDB is now supported we needed a deep level type-safety, and will also be applicable to Postgres JSON data type

@glebbash solutions works as of now. But at the end you will also wanted a single source of truth, your schema.prisma file

[p6l-richard]

I think this should now be possible with the client extensions:

https://twitter.com/prisma/status/1672234723769212930?s=46&t=8FLs07ZKHr7bDSpaRl2Ekw

You should be able to define a zod schema for your json and then validate on the extension.

[jakeleventhal]

Not sure if this is a great solution. Requires you to update for all query types

[janpio]

Client Extensions definitely are not the solution to this issue, but they probably make a more maintainable and working workaround than before was possible. We'll keep this issue open for the actual, proper type support for Json.

[arthurfiorette]

prisma-json-types-generator is working very well to solve this for now, you should try it.

[khalil-omer]

This is an open source tool that we all use for free. It's fine that a feature request has remained open.

…

On Fri, Jul 07, 2023 at 10:30 AM, Mohamed El Mahallawy < ***@***.*** > wrote: The fact that this has been open since August 2020 is... — Reply to this email directly, view it on GitHub ( #3219 (comment) ) , or unsubscribe ( https://github.com/notifications/unsubscribe-auth/AQIFNCLNKCZMWONVSY5KPDTXPBBSVANCNFSM4PWKOEXQ ). You are receiving this because you are subscribed to this thread. Message ID: <prisma/prisma/issues/3219/1625723312 @ github. com>

[group900-3]

I am using trpc and prisma together, and the json data returned by the server has no type, which is a serious problem. But I don't think prisma should be allowed to specify the type for json data. Maybe it is safer to use some tools to verify and specify the type. I found that using the result extension can effectively specify a type for a field.（for post a reply I'm using translation software）

import { PrismaClient } from "@prisma/client";
import { z } from "zod";

const goodsSchema = z
  .array(
    z.object({
      name: z.string(),
      count: z.number()
    })
  )
  .optional();
export const db = new PrismaClient().$extends({
  result: {
    warehouse: {
      goods: {
        needs: {goods: true },
        compute(warehouse) {
          // if you don't want to throw error you can use .safeParse
          return goodsSchema.parse(room.goods);
        },
      },
    },
  },
});

[vimtor]

I am using trpc and prisma together, and the json data returned by the server has no type, which is a serious problem. But I don't think prisma should be allowed to specify the type for json data. Maybe it is safer to use some tools to verify and specify the type. I found that using the result extension can effectively specify a type for a field.（for post a reply I'm using translation software）

import { PrismaClient } from "@prisma/client";
import { z } from "zod";

const goodsSchema = z
  .array(
    z.object({
      name: z.string(),
      count: z.number()
    })
  )
  .optional();
export const db = new PrismaClient().$extends({
  result: {
    warehouse: {
      goods: {
        needs: {goods: true },
        compute(warehouse) {
          // if you don't want to throw error you can use .safeParse
          return goodsSchema.parse(room.goods);
        },
      },
    },
  },
});

I think that both approaches should not be incompatible. If you plan on having a predefined JSON structure, this code you wrote could be generated by Prisma.

[arthurfiorette]

Just a reminder: If you are using prisma v5+, please stick with prisma-json-types-generator@beta until an official 3.0.0 relase is ready. Current beta, beta.4 is stable and can already be used in production.

Prisma Json Types Generator@3+ is stable and ready to be used with Prisma@5+. We also removed support for Prisma@4.

[pieterjandebruyne]

Declaring the namespace inside a dts file is an option that also works but sometimes requires changes inside tsconfig. I'll try to write something in the readme ASAIC to clarify this.

@arthurfiorette Did you by any chance got time to set up an example of this?

We tried adding a src/@types/prismaJson.d.ts file:

export {};

declare global {
  // eslint-disable-next-line @typescript-eslint/no-namespace
  namespace PrismaJson {
    export interface IMachineAMeta {
      firmware: string;
      beltSpeed: number;
      active: boolean
    }
  }
}

We then added the path in our tsconfig:

"typeRoots": [
      "node_modules/@types",
      "src/@types"
    ],

our prisma schema includes this config:

generator json {
  provider = "prisma-json-types-generator"
}

We then want to use it like this:
// machineAMeta.type.ts
@ObjectType('MachineAMeta')
@inputType('MachineAMetaInput')
export class MachineAMeta implements PrismaJson.IMachineAMeta {
...
}

this gives no errors in the IDE but when we run it we get:
`Namespace 'global.PrismaJson' has no exported member 'MachineAMeta'`

If we rename to `.ts` and use `import` inside the file `machineAMeta.type.ts` it does work but we would really prefer using global d.ts files that we don't have to include.

Any ideas what might be wrong?

[arthurfiorette]

Hey @pieterjandebruyne prisma-json-types-generator has its own repository outside of prisma. Please open a issue there. Don't need to spam this prisma thread.

If we rename to .ts and use import inside the file machineAMeta.type.ts it does work but we would really prefer using global d.ts files that we don't have to include.

Not a issue with our side. you are not including .d.ts correctly inside your tsconfig as youself said .ts file will work. What's the problem with .ts file? isn't it like what we use to code the rest of the application?

Idk, dts files inside src have multiple behaviors for multiple setups and compilerOptions, too much trouble to be debugging out why typescript is not recognizing it.

I think you can just add a import type {} from 'src/@types/prismaJson' somewhere would work too.

[nash-an]

+1!
I would really appreciate if this feature was added.

The only way to assign types I've found is either by double typecasting or converting to string and json

const ex1 = orderFilledFields as unknown as App.Field[] // Using Object[] instead of unknown also seems to work
const ex2 = JSON.parse(JSON.stringify(orderFilledFields)) as App.Field[]

This is especially annoying because in a library such as SvelteKit, typescript raises errors if you try to iterate through a JsonValue object so you are forced to change its type.

[arthurfiorette]

@nash-an #3219 (comment)

[nash-an]

@nash-an #3219 (comment)

I'm aware of this repository, but I am also voicing support for this to be a standard library feature alongside 496 others.

[nkorent]

sooooooo, where we at? 👀

seems like this would be a very useful feature, especially if nosql used. Is there any news on the progress from Prisma side about this?

[VSKor]

Hey all, happy new year!
Im kinda just starting my jorney with prisma, so have more of a question here.
Shouldnt I use some type of relation in such cases instead of defining Json type ? Basically using relation we have a typed JSON in the end any way. I might be mistakken, due to small experience in DB stuff, but even if end table grows a lot, but you never going to querying it, "connected" records will be found without extra performace overhead.
Sounds close to true ?

[matsgm]

Hey all, happy new year! Im kinda just starting my jorney with prisma, so have more of a question here. Shouldnt I use some type of relation in such cases instead of defining Json type ? Basically using relation we have a typed JSON in the end any way. I might be mistakken, due to small experience in DB stuff, but even if end table grows a lot, but you never going to querying it, "connected" records will be found without extra performace overhead. Sounds close to true ?

I've only POC'ed Prisma, but I would try an ORM that has typed JSON support like Sequelize or TypeORM.

[boredland]

Mikro ORM is really neat and iirc, this worked fine (with PostgreSQL).

[AlexJWayne]

Shouldn't I use some type of relation in such cases instead of defining Json type?

Generally, yes, you are correct.

But when to use JSON and when to model your data as individual tables is not really the point here. It has it's uses. Like when you have a very complex and deeply nested bit of data that is completely self contained, then a JSON column might make sense.

[arthurfiorette]

Despite prisma not having direct support for typed JSON, the prisma-json-types-generator package can solve this problem for you. Prisma can have a neat DX, specially for newer devs.

[boredland]

we're using prisma-json-types-generator and it makes the life better in regards to output types, but the inputs and queries are not typed accordingly, sub-queries inside not possible (and not typed), indexes via prisma don't work on json fields, not to mention unique indexes, there might be more features missing. We created input/output wrappers for the relevant entities using zod. this also helps us to infer the type from zod for the types-generator. works quite nice, but still does feel hacky and brittle.

[matsgm]

Despite prisma not having direct support for typed JSON, the prisma-json-types-generator package completely solves this problem for you. Prisma can have a neat DX, specially for newer devs.

@arthurfiorette : As mentioned above... if queries are not typed, how does this solve the problem "completely"?