[Feature request] Computed fields

[Azzerty23]

Is your feature request related to a problem? Please describe. Prisma does not allow for easily obtaining computed fields. A great solution has been proposed by the community, but it hasn't been implemented yet: https://github.com/prisma/prisma/issues/3394

Describe the solution you'd like A @computed field attribute mapped to a function defined inside prisma/computed/ModelName.ts

// Example from the related issue
model User {
  id       String @id @default(cuid())
  email    String
  gravatar String @computed
}

// Implement computed fields for User.ts model
export const gravatar = prisma.user.computed(record => {
    const hash = md5(record.email);
    return `https://gravatar.com/avatar/${hash}`
})

Describe alternatives you've considered Extending models is not very convenient as we lose the benefit of the central schema:

• the old way: https://www.prisma.io/docs/concepts/components/prisma-client/custom-models
• the new way: https://www.prisma.io/docs/concepts/components/prisma-client/client-extensions/model#add-a-custom-method-to-a-specific-model

Additional context Prisma's documentation on this topic : https://www.prisma.io/docs/concepts/components/prisma-client/computed-fields

[ymc9]

Hey @Azzerty23 , this is an interesting proposal. Just wanted to understand more about your scenario. For the computed field, do you only need to read it after query, or do you want it to work as part of the query? E.g., for the example you gave, using it in query means something like:

prisma.user.findFirst({ where: { gravatar: { contains: "gravatar" } } });

I guess Prisma's client extension doesn't support it but I haven't tried it yet.

[Azzerty23]

I'm not sure... I suppose like the example you mentioned, it should be part of the query.

The idea is to be able to use this computed field transparently, as if it were a regular field, even though it is not present in the database. This is well handled by many ORMs but unfortunately not by Prisma.

And it's a shame because it goes against the idea of the schema as the 'source of truth'. The client (query builder) no longer really corresponds to the schema if we have to extend it with the $extends method.

I would like to keep custom methods close to the model, alongside the other properties that define it.

A classic use case, the first one that comes to mind, is when we want to control access to a file in an S3 bucket, for example, we will want to retrieve a signed URL (to limit its validity over time). It is easier to be able to abstract the logic directly in the model (with a getter), rather than having to apply a helper on the returned field from the database.

In other words, I would like to be able to do: const user = prisma.user.findFirst(...) then: const userPicture = user.picture => calls the method (getter) picture to generate the URL rather than: const userPicture = getS3FileSignedUrl(user.bucket, user.key) each time I request the user picture.

I wouldn't know how to implement it, but I have a feeling that Zenstack is the ideal toolkit to solve this issue ^^

[Azzerty23]

If the computed field is only supported after the query, that's already a big win! (If I understand correctly, it would be sufficient for the scenario I talked about.)

[Azzerty23]

For what it worth, with Mongoose I can define a getter inside my model:

const exampleSchema = new Schema({
    name: { type: String },
    pictures: {
      type: [{ Bucket: String, Key: String }],
      get: (pictures) =>
        pictures.map((picture) => generatePresignedUrl(picture)),
    },
    things: {
      type: Mixed,
      get: getThings,
    },
    pdf_cover: {
      type: { Bucket: String, Key: String },
      get: generatePresignedUrl,
    },
  });

if (!mongoose.models.Example) {
  mongoose.model('Example', exampleSchema);
}

export default mongoose.models.Example;

Or Sequelize apparently: https://sequelize.org/docs/v6/core-concepts/getters-setters-virtuals/#virtual-fields

And in another context, with Tortoise ORM (python), I can define methods inside the class Model:

# https://tortoise.github.io/examples/pydantic.html#recursive-models-computed-fields

class Employee(Model):
    name = fields.CharField(max_length=50)
    team_members: fields.ReverseRelation["Employee"]

    def name_length(self) -> int:
        # Computes length of name
        return len(self.name)

    def team_size(self) -> int:
        #  Computes team size.
        try:
            return len(self.team_members)
        except NoValuesFetched:
            return -1

    class PydanticMeta:
        computed = ["name_length", "team_size"]

This may explain why so many Prisma users coming from other ORMs are frustrated with the open issue from 3 years ago 👀

[ymc9]

Thanks for sharing the use cases and examples from other ORM. I briefly investigated, and I don't think any "virtual column" implementation from these ORMs (including Prisma's client extension) can be used in queries as regular fields. This is understandable because the virtual column's value is calculated with JS/Python. To make the query work, the calculation needs to be translated to equivalent SQL, which is not always possible.

E.g.: for a calculated fullName field: fullName = firstName + " " " lastName to query with condition where fullName contains "Tom", the SQL needs to look like:

SELECT * FROM User WHERE CONCAT(firstName, " ", lastName) LIKE "%Tom%"

For complex computation, such translation can be impossible.

For "read-only" usage of computed fields, since Prisma already supports it with client extensions (though the syntax is a bit verbose), I don't think ZenStack should rebuild a separate solution. To make the ZModel (and Prisma schema) reflect the computed column, maybe we can use the @ignore attribute?

model User {
    ...
    fullName String @ignore
}

This way the fullName field appears in the schema but is ignored on the database side.

[Azzerty23]

Thank you for the explanation and solution provided (@ignore), I will try it out that way. It's clearer to me now, and after considering it, I think a simple read-only computed field is suitable for my needs.

To summarize and close this issue, instead of implementing a complicated solution like:

model User {
  id         Int     @id @default(autoincrement())
  firstname  String
  lastname   String
  fullname   String  @computed(getter: "getFullname")
}

// prisma/computed/User.ts
function getFullname({ firstname, lastname }) {
  return `${firstname} ${lastname}`;
}

You suggest doing (native solution in Prisma):

model User {
  id         Int     @id @default(autoincrement())
  firstname  String
  lastname   String
  fullname   String  @ignore  // computed field
}

// Prisma extension
const xprisma = prisma.$extends({
  model: {
    user: {
      fullname() {
        return `${prisma.user.fields.firstname} ${prisma.user.fields.lastname}`
      },
    },
  },
})

I really like this solution! If you think to a better way to define a custom getter for a model property (prisma/prisma#11335), I'm still open to suggestions. Thank you very much!

[Azzerty23]

I just found that computed fields was the first use case example when Prisma announced the new Client Extension on their blog : https://www.prisma.io/blog/client-extensions-preview-8t3w27xkrxxn#example-computed-fields

I can definitely close this issue ^^

[ladderschool]

There's a significant limitation to the prisma extension implementation for computed fields. They do not support relations. I was wondering if Zenstack could revisit this idea since the Prisma issue has been open for more than 5 years and perhaps this could be done in the zmodel.

For example. take a user who has a one-to-many transactions relationship. A transaction has a points field and optional reason and comments fields. What we care about are just the points field, though. What I'd like to see is a virtual/computed field points that is the sum of all transaction points.

Currently this is not supported by prisma:

prismaClient.$extends({
  result: {
user: {
      points: {
        needs: { receivedPoints: true },
        compute(user) {
          return user.receivedPoints.reduce(
            (total, transaction) => total + transaction.points,
            0
          )
        },
      },
    },
  }

The issue is that the needs clause does not accept relations. Instead, could we maybe circumvent the prisma extensions and do it directly in the model?

model User {
...
  receivedPoints Transaction[]
  points Int @default(0) @computed([this.receivedPoints.points.sum()])
}

The alternative approach from what I understand is to keep a points field on the user model and each time a new transaction is made, update the user points field. I do this and I incur the cost, of course. The idea behind a computed value would be that it's read-only as it's only useful when querying the user model, to see the total points "virtually". The question is when the calculation would be conducted? Since this problem arises due to the prisma extension solution not working with relations (as they change, when new records are added) I'd be interested in an implementation that computes this value per query to be as "real time" accurate as possible.

The prisma extension works well for fields, so the solution here should focus on dynamic relations rather than static fields.

[nwidynski]

@ymc9 @jiashengguo One solution that has been on my mind lately was whether it would be possible to use computed fields inside access policies by defining them as ignored columns and then re-adding them as a computed field.

In a way tricking the Zenstack compiler to get through compilation, then fulfilling the interface back through extensions. This would be most powerful in combination with new predicates to validate Prisma.JsonValue columns.

Example (pseudo code)

model Post {
  id Int @id @default(autoincrement())

  is_active Boolean @default(false) @ignore

  @@allow("read", is_active)
}

const client = new PrismaClient().$extends({ 
  result: { post : {  is_active: { needs: {}, compute: () => { /*Implement here*/ } } }}
});

const enhanced = enhance(client);

const post = await enhanced.post.create();

// This should throw on access policy
await enhanced.post.findUniqueOrThrow({ where: { id: post.id } });

[ymc9]

@ymc9 @jiashengguo One solution that has been on my mind lately was whether it would be possible to use computed fields inside access policies by defining them as ignored columns and then re-adding them as a computed field.

In a way tricking the Zenstack compiler to get through compilation, then fulfilling the interface back through extensions. This would be most powerful in combination with new predicates to validate Prisma.JsonValue columns.

Example (pseudo code)

model Post {
  id Int @id @default(autoincrement())

  is_active Boolean @default(false) @ignore

  @@allow("read", is_active)
}

const client = new PrismaClient().$extends({ 
  result: { post : {  is_active: { needs: {}, compute: () => { /*Implement here*/ } } }}
});

const enhanced = enhance(client);

const post = await enhanced.post.create();

// This should throw on access policy
await enhanced.post.findUniqueOrThrow({ where: { id: post.id } });

The enforcement of access policies is mostly done by injecting them into the where clause of Prisma queries instead of reading the entities out of the database and then checking. A TypeScript-computed field doesn't fit into this scheme unless we have a way to translate a TS expression into a Prisma query filter, which is not possible in general.

In your case, can the access conditions on is_active represented by ZModel expressions today?

[ymc9]

@ymc9 @jiashengguo One solution that has been on my mind lately was whether it would be possible to use computed fields inside access policies by defining them as ignored columns and then re-adding them as a computed field. In a way tricking the Zenstack compiler to get through compilation, then fulfilling the interface back through extensions. This would be most powerful in combination with new predicates to validate Prisma.JsonValue columns. Example (pseudo code)

model Post {
  id Int @id @default(autoincrement())

  is_active Boolean @default(false) @ignore

  @@allow("read", is_active)
}

const client = new PrismaClient().$extends({ 
  result: { post : {  is_active: { needs: {}, compute: () => { /*Implement here*/ } } }}
});

const enhanced = enhance(client);

const post = await enhanced.post.create();

// This should throw on access policy
await enhanced.post.findUniqueOrThrow({ where: { id: post.id } });

The enforcement of access policies is mostly done by injecting them into the where clause of Prisma queries instead of reading the entities out of the database and then checking. A TypeScript-computed field doesn't fit into this scheme unless we have a way to translate a TS expression into a Prisma query filter, which is not possible in general.

In your case, can the access conditions on is_active be represented by ZModel expressions today?

[nwidynski]

@ymc9 @jiashengguo One solution that has been on my mind lately was whether it would be possible to use computed fields inside access policies by defining them as ignored columns and then re-adding them as a computed field. In a way tricking the Zenstack compiler to get through compilation, then fulfilling the interface back through extensions. This would be most powerful in combination with new predicates to validate Prisma.JsonValue columns. Example (pseudo code)

model Post {
  id Int @id @default(autoincrement())

  is_active Boolean @default(false) @ignore

  @@allow("read", is_active)
}

const client = new PrismaClient().$extends({ 
  result: { post : {  is_active: { needs: {}, compute: () => { /*Implement here*/ } } }}
});

const enhanced = enhance(client);

const post = await enhanced.post.create();

// This should throw on access policy
await enhanced.post.findUniqueOrThrow({ where: { id: post.id } });

The enforcement of access policies is mostly done by injecting them into the where clause of Prisma queries instead of reading the entities out of the database and then checking. A TypeScript-computed field doesn't fit into this scheme unless we have a way to translate a TS expression into a Prisma query filter, which is not possible in general.

In your case, can the access conditions on is_active represented by ZModel expressions today?

@ymc9 Yes, currently we are using a view to accomplish the same. Unfortunately this means we essentially have to maintain access policies for 2 models (one raw, one view) just for one cmputed column.

While i understand validation in JS to be slower, i think it would make working with computed fields much easier, so maybe an opt-in option on policy would work?

Relation fields would have to be excluded in the opt-in policy and instead be validated by performing sub queries in the computed field.