Prisma client - computed fields v2

[mishase]

Problem

I often select user's firstName and lastName to just concat it or password to tell if user has it and then cut off from the rest of response

Suggested solution

Computed fields

For example i'll use this user model:

model User {
  id        String  @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  firstName String  @db.VarChar(64)
  lastName  String  @db.VarChar(64)
  email     String  @unique @db.VarChar(256)
  password  String? @db.Char(128)
}

I want to have ability to extend prisma client, for example:

const client = new PrismaClient({
  extensions: {
    user: {
      name: {
        select: {
          firstName: true,
          lastName: true,
        },
        callback(user) {
          return `${user.firstName} ${user.lastName}`;
        },
      },
      hasPassword: {
        select: {
          password: true,
        },
        callback(user) {
          return !!user.password;
        },
      },
    },
  },
});

Then query this fields:

const user = await client.user.findUnique({
  select: {
    id: true,
    firstName: true,
    name: true,
    hasPassword: true,
  },
  where: {
    id,
  },
});

I expect the user object to have this fields:

Defined: id, firstName, name, hasPassword
Undefined (selected internally but not passed to result): lastName, password

Alternatives

This is the same aggregation as we saw before but it is not flexible and requires me to cut off some fields every time:

const { lastName, password, ...rest } = await client.user.findUnique({
  select: {
    id: true,
    firstName: true,
    lastName: true,
    password: true,
  },
  where: {
    id,
  },
});

return { name: `${user.firstName} ${lastName}`, hasPassword: !!password, ...rest };

Additional context

Example implementation:

removeFalse(object) function that removes all object fields with false value, example:

Before:

{
  firstName: true,
  lastName: false,
}

After:

{
  firstName: true,
}

present(result, query) function that truncates query result to match select statement, example:

Select statement (query argument):

{
  firstName: true,
  lastName: true,
}

Before:

{
  id: "00000000-0000-0000-0000-000000000000",
  firstName: "John",
  lastName: "Doe",
}

After (selects firstName and lastName):

{
  firstName: "John",
  lastName: "Doe",
}

Now we will use three layers of query:

presentation - defines what user sees
subquery - adds fields to database query
database - selects all required fields from db

Assuming presentation query to be:

{
  id: true,
  firstName: true,
  name: true,
  hasPassword: true,
}

We see two subqueries in presentation query:

{
  firstName: true,
  lastName: true,
}

{
  password: true,
}

Let's build database query, execute it and save result as dbResult:

Warning: we should cut off all false select statements to make object joins correctly

{
  ...presentationQuery,
  ...removeFalse(subquery1.select),
  ...removeFalse(subquery2.select),
}

Now call the subqueries callbacks like:

const name = subquery1.callback(present(dbResult, subquery1.select));
const hasPassword = subquery2.callback(present(dbResult, subquery2.select));

Concat subquery results with dbResult and save it as result:

{
  ...dbResult,
  name,
  hasPassword,
}

And return presented result:

present(result, presentation.select)

We finally got:

{
  id: "00000000-0000-0000-0000-000000000000",
  firstName: "John",
  name: "John Doe",
  hasPassword: true,
}

Also it would be good to have support for nesting subqueries and async subquery callback

[janpio]

I think this feature request ist mostly covered by our Client Extensions: https://www.prisma.io/docs/orm/prisma-client/queries/computed-fields

(This issue was taken into account during the design phase already, as discussed here: #15074 (comment))