Add way to exclude fields via query arguments or globally

[pantharshit00]

Problem

Sometimes it is helpful to just remove some fields from the selection set like password so that we accidentally don't expose them to the frontend.

Proposals

Adding a exclude key in the arguments of the queries to easily exclude fields from the selectionSet just like include or select.

prisma.user.findMany({
  exclude:{
    password: true
  }
})

Or

await prisma.user.findMany({
  omit: {
    password: true,
  },
})

Alternatives

1. Use query and list all the fields you want
2. Schema annotation
3. Manual omission via lodash or something !?

Related

• https://github.com/prisma/prisma/issues/2170
• https://github.com/prisma/prisma/issues/3246

[un33k]

@GrzegorzWidla Sorry for the late reply ...

I am sure Prisma will meet this need in the near future, based on priority.

Till then,

I do recommend that each object should have a serializer / scope utility functions / class.

Then all objects will go through their respective serializer / re-scopers prior to being put on the wire.

I am including an example for ref:. (here)

I encourage you folks to adopt similar approach till Prisma gives us a typeORM kind of functionalities.

TLDR; User/Group/Permission are the main issues here, where utmost care must be taken.

So, create a scope class helper utility that knows what to hide based on user's role, permission, etc.

No User Object shall be returned without going through the scope class first.

Hope this helps and feel free to go through fullerstack and look for all libs starting with nsx which denote the backend libs for NestJs. A full solution can be found and used as a ref.

[paulbrickellmaven]

Is there any intention to progress this issue?

[janpio]

Yes, if there was not we would have closed the issue. There is just no communicated timeline, as there is not with most of our issues. (But it is definitely high on our list of things to look at.)

[ghyath5]

I ended up using deepOmit on the result and remove all password fields in all objects and relationships

[scriptcoded]

@un33k

I do recommend that each object should have a serializer / scope utility functions / class.

This would also solve some other issues such as presigning S3 URLs. Currently that must be done through heavy middleware or complex wrappers.

[scriptcoded]

Please, people, have some respect.

You're asking for the Prisma developers to be quicker about something you probably won't even be reimbursing them for. If you want or need the feature to be finished quicker, contribute to the discussion with meaningful thoughts or information or actually do some work yourself and contribute. Expecting other people to do everything for you doesn't help the OSS community or this project in any way, shape or form.

[Gin-Quin]

I think this feature would be awesome but I have to disagree with the "exclude in schema or in options?" consideration.

The perfect solution is actually two features, there should be an exclude at schema-level and another at options-level. Why? Because it looks like they solve the same problem, but actually they don't quite do. Each feature has capabilities the other has not.

With the "exclude in schema" situation, the field can never be fetched from Prisma client, never ever. That's awesome when you needs to protect some private informations like passwords. That's why this feature should exist. But that's not so awesome when you want to casually omit a field for a certain query that you might want for another query...

...and that's why the "exclude in options" feature should also exist.

The argument of "Typescript has not the capacities to correctly type an exclude" is absolutely wrong.

I think it is possible to extend the "select" option to also take care of excludes. Like said earlier, there should be no "true" | 'false" mix in a select option because it makes no sense.

This constraint can actually be typed:

type Select<Entity> = SelectInclude<Entity> | SelectExclude<Entity>

type SelectInclude<Entity> = { [Key in keyof Entity]?: true | Select<Entity[key]> }
type SelectExclude<Entity> = { [Key in keyof Entity]?: false | Select<Entity[key]> }

This way Typescript would complain if you try to mix trues with falses. Of course refinement should be done but this is the general idea. The return type can be well-tuned as well by checking if the select property extends SelectInclude or SelectExclude.

All this just to say it's possible and it would be awesome :)

EDIT

I just realized that changing the type of select will not be retro-compatible so it's actually not an option... There should either be an omit or an exlude additional field. I would rather choose omit because "exclude" would bring ambiguities regarding the include option.

[benhickson]

I'm following this thread cause I know it'll impact things and I'm excited to see the evolution, but in my work, i just wrap all prisma calls in "query" functions. e.g. instead of prisma.user.findUnique i wrap that call in a function like getUserById.

Having this extra layer of abstraction has a few key benefits:

1. Firstly, mainly, I only write queries that are valid for my business logic. Perhaps my record should only change in a particular way that prisma/postgres can't express easily at the database level.
2. I can add side-effects (such as logging, perhaps i want to keep records of every change made to a particular item) to queries easily, while not needing to know about those side-effects elsewhere in the application.
3. I can have my functions configured to omit any fields like password that we might like to not serve up by default, and export a new type like User = Omit<PrismaUser, "password"> for use elsewhere in the application. For when you do need the password, like when signing into an account, just have a query like compareUserPassword that returns a simple boolean and doesn't leak out anything from the prisma calls.

I think item 3 solves the main issue fairly elegantly, I'd encourage anyone here who has the issue to explore wrapping your prisma calls in query functions.

✌️

[jacques-twizeyimana]

vote

This would be a great option. specify it once in Prisma schema and explicitly request it only when needed cause private fields like passwords need to be exposed rarely.

[nemanjam]

Is exclude option implemented already? Do I have to do it like this?

const user = await prisma.user.findFirst({ where: { OR: [{ username }, { email }] } });
delete user.password;
return user;

[jacques-twizeyimana]

Is exclude option implemented already? Do I have to do it like this?

const user = await prisma.user.findFirst({ where: { OR: [{ username }, { email }] } });
delete user.password;
return user;

currently, exclude option is not implemented but omitting a field like this would be a bad option. Instead, you can use the select query option to select fields you want

return await prisma.user.findFirst({ where: { OR: [{ username }, { email }] },
 select:{
id:true,
email:true,
names:true
}
 });

[benhickson]

@nemanjam try this instead:

in a queries file:

export async function getUser(prismaClient, params) {
  const { username, email } = params

  return prismaClient.user.findFirst({
    select: {
      id: true,
      username: true,
      email: true,
      created_at: true,
      updated_at: true,
      // all fields except your password field
    },
    where: { OR: [{ username }, { email }] }
  })
}

then import that function and use it in your code:

const user = await getUser(prisma, { username, email })

[nemanjam]

That is very verbose, I need to repeat all those lines in all endpoints that return a user. Maybe I will define select object for a user in a single place as constant and import it in all queries. That's not ideal too but bearable. All backend frameworks have guarded fields feature.

[benhickson]

@nemanjam you don't have to repeat the lines at all. you define that function in a single file, and then import it elsewhere in your application. the only line you use in your endpoints is:

const user = await getUser(prisma, { username, email })

and yes, if you have multiple queries (getUser, updateUser, createUser, for example), you can define a select object like you mentioned, and then just use it in all your queries. You can use Prisma.validator if you'd like some type safety.

[paulbrickellmaven]

@nemanjam you don't have to repeat the lines at all. you define that function in a single file, and then import it elsewhere in your application. the only line you use in your endpoints is:

const user = await getUser(prisma, { username, email })

and yes, if you have multiple queries (getUser, updateUser, createUser, for example), you can define a select object like you mentioned, and then just use it in all your queries. You can use Prisma.validator if you'd like some type safety.

This pattern is contrary one of the best features of Prisma, that the client can decide what fields to fetch.

[nemanjam]

Should I use Prisma middleware to filter password from all queries that return a user?

https://www.prisma.io/docs/concepts/components/prisma-client/middleware

[paulbrickellmaven]

@nemanjam try this instead:

in a queries file:

export async function getUser(prismaClient, params) {
  const { username, email } = params

  return prismaClient.user.findFirst({
    select: {
      id: true,
      username: true,
      email: true,
      created_at: true,
      updated_at: true,
      // all fields except your password field
    },
    where: { OR: [{ username }, { email }] }
  })
}

then import that function and use it in your code:

const user = await getUser(prisma, { username, email })

Kind of defeats the principal of allowing the client to decide what fields they want.

@nemanjam you don't have to repeat the lines at all. you define that function in a single file, and then import it elsewhere in your application. the only line you use in your endpoints is:

const user = await getUser(prisma, { username, email })

and yes, if you have multiple queries (getUser, updateUser, createUser, for example), you can define a select object like you mentioned, and then just use it in all your queries. You can use Prisma.validator if you'd like some type safety.

It's not the verbosity. It's the fact that Prisma is a good solution to the underfetch/overfetch problem of REST APIs. This wrapper is deciding all the fields the client gets. For more complex user models (with permission or roles etc.) this negates that benefit.

[paulbrickellmaven]

Should I use Prisma middleware to filter password from all queries that return a user?

https://www.prisma.io/docs/concepts/components/prisma-client/middleware

This is the solution I have used. It's not great because you have to work out if any query includes the field in any selects or includes. It's messy.

[paulbrickellmaven]

Another way to potentially solve this would be be to allow mapping more that one one model to the same table. Not sure if that's too hard or not though. Just throwing it out there.

[nephix]

We exclude sensitive fields in the API layer instead of the model/database layer. We use GraphQL with https://github.com/MichalLytek/typegraphql-prisma and then just annotate fields we don't want to be exposed by the API in the prisma schema:

model SomeModel {
  id        Int     @default(autoincrement()) @id
  /// @TypeGraphQL.omit(output: true)
  sensitiveField  String
}

https://prisma.typegraphql.com/docs/advanced/hiding-field

[matthewmueller]

We just wrote some documentation on how to exclude fields in a type-safe way with Prisma: https://www.prisma.io/docs/concepts/components/prisma-client/excluding-fields

We do plan to implement this feature into the Prisma Client, but this should help you along in the meantime. If you'd like to chat about this topic or you have any questions, feel free to book some time with me.

[scriptcoded]

We just wrote some documentation on how to exclude fields in a type-safe way with Prisma: https://www.prisma.io/docs/concepts/components/prisma-client/excluding-fields

@matthewmueller nice! Just want to point out that using delete mutates the original object. Using a shallow clone or deleting using destructing might be a better strategy.

Would raise a PR but I'm on mobile atm.

[nemanjam]

We just wrote some documentation on how to exclude fields in a type-safe way with Prisma: https://www.prisma.io/docs/concepts/components/prisma-client/excluding-fields

We do plan to implement this feature into the Prisma Client, but this should help you along in the meantime. If you'd like to chat about this topic or you have any questions, feel free to book some time with me.

Not very practical for relation models, need to do post.author = exclude(post.author, 'password') all over the code.

[alexis-cortes]

Hi, I have created a possible solution to be able to exclude. I am including an example I hope it helps, you can take a look here

await prisma.user.findMany({
    select: {
      ...exclude("user", ["password", "otherField"]),
      profile: {
        select: {
          ...exclude("profile", ["bio", "otherField"]),
        },
      },
    },
});

[joematune]

Similar to the above solution, I had success abstracting the select query - this way you can include it in relation queries, however, this still requires remembering to use it.

const selectUserInput = Prisma.validator<Prisma.UserSelect>()({
  id: true,
  username: true,
  created_at: true,
  updated_at: true,
  password: false, // `false` or `undefined`
}

// fully typed without password
const user = await prisma.user.findUnique({
  where: { id },
  select: selectUserInput,
});

// fully typed without password - here, an `org` has many `user`s.
const org = await prisma.org.findUnique({
  where: { id },
  include: {
    users: {
      select: selectUserInput,
    }
});

[Janpot]

Few thoughts I had reading through this thread:

1. Another potential way to emulate exclude on top of include in the queries could be by providing model information at runtime

   prisma.user.findUnique({
    where: { id },
    // Imagine prisma.user.model.fields exists
    include: excludeFields(prisma.user.model.fields, ['password']),
   });

   I couldn't find any feature request for it, but I can imagine this sort of runtime information to be useful in other areas as well.

2. Most cited use-case seem to be security reasons, but performance is another one, you may want to avoid columns containing large values from being returned by the database if you know up front that you won't need them. It's minor, but it could be a factor in naming the feature. i.e. @hidden or @exclude would make more sense than @private.

[ajmnz]

Just released prisma-exclude, a fully type-safe solution inspired by @alexis-cortes's proposal, with model name and model fields autocompletion based on the provided Prisma instance.

import { Prisma, PrismaClient } from "@prisma/client";
import { prismaExclude, withExclude } from "prisma-exclude";

// Initialize your Prisma Client and create the exclude function
const prisma = new PrismaClient();
const exclude = prismaExclude(prisma);

await prisma.user.findFirst({
  select: exclude("user", ["password"]) // Model name and fields type-safety & autocompletion
});

// Or use the wrapper
const prisma = withExclude(new PrismaClient());
await prisma.user.findFirst({
  select: prisma.$exclude("user", ["password"])
});

// Also works with Prisma Validator
const userSafeSelect = Prisma.validator<Prisma.UserSelect>()(
    prisma.$exclude("user", ["password", "createdAt"])
);

Hope it helps!

[aindong]

For those who don't want to use a library and make use of Prisma's native suggestion from from the docs https://www.prisma.io/docs/concepts/components/prisma-client/excluding-fields

here's an implementation that you can use not only for users type but for literally everything using generics, you can also create a custom model and an abstract class that implements this exclude function so it will become native to your Prisma models.

export function exclude<T, Key extends keyof T>(resultSet: T, ...keys: Key[]): Omit<T, Key> {
  for (let key of keys) {
    delete resultSet[key];
  }
  return resultSet;
}

[tsongas]

Looking forward to this feature! The trouble with solutions like https://www.prisma.io/docs/concepts/components/prisma-client/excluding-fields is that I have more queries than I can even begin to count, that include users as part of a relation.

[IdanYekutiel]

Same, having a feature like this would be great! In addition, for things like passwords, maybe a way to also exclude fields by default at the schema level would also be extremely useful and much easier to work with.

[nziu]

@Janpot

prisma.user.findUnique({
  where: { id },
  // It really exists
  select: excludeFields(Prisma.UserScalarFieldEnum, ["password"]),
});

function excludeFields<T, K extends keyof T>(fields: T, omit: K[]) {
  const result: Partial<Record<keyof T, boolean>> = {};
  for (const key in fields) {
    if (!omit.includes(key as any)) {
      result[key] = true;
    }
  }
  return result;
}

[jalaln06]

I'd really love to see that happen

[utenma]

on roadmap https://www.notion.so/Prisma-Roadmap-50766227b779464ab98899accb98295f?p=ccb00ca22090437eb3b1726a722d0ae6

[juemrami]

Came looking for a similar feature. Hope this feature makes it to the planned stage. but im also team schema

Imo the suggestion by @darioielardi is the most natural implementation of such a feature.

• It allows you to keep all the important information regarding your models in the schema as mentioned by @flybayer .
• It requires you to be explicit when you want to reveal/expose the hidden field (yay for whitelisting). Additionally along the lines of being explicit i like the idea of using a keyword other than "include" as to prevent accidents when using the include API.
• and it allows you (at least it seems on the surface) to keep the "prisma expressiveness" mentioned by @matthewmueller in the roadmap feature candidate summary.

What about something like an additional expose field exclusively aimed to select hidden columns? Something like:

model User {
  id Int @id
  name String
  password String @hidden
}

prisma.user.findMany({
  expose: { password: true }, // no need to explicitly select the other fields
})

At least for password use case this seems to make the most sense. It seems that some people want a bit of different functionality that allows them to

In the meantime i've taken @benjibuiltit 's suggestion to create a seperate Secrets model to store my password_hash , session_tokens and other bits like created_at. Data that i don't need exposed on the User model being used in the frontend.

[alexwohlbruck]

Should I use Prisma middleware to filter password from all queries that return a user?

https://www.prisma.io/docs/concepts/components/prisma-client/middleware

This seems like the only option at the moment. For those looking to remove user passwords from returning in queries, this solution is working for me:

https://stackoverflow.com/questions/68140035/exclude-users-password-from-query-with-prisma-2/72695395#72695395

[matthewmueller]

This seems like the only option at the moment.

You could also try this approach described in the documentation.

[alexwohlbruck]

@matthewmueller That solution works well for a one-off query, but I was needing something that will work globally and by default, to prevent any situation where the developer might forget to explicitly remove the field.

[zzau13]

import { Prisma } from '@prisma/client';

type A<T extends string> = T extends `${infer U}ScalarFieldEnum` ? U : never;
type Entity = A<keyof typeof Prisma>;
type Keys<T extends Entity> = Extract<
  keyof typeof Prisma[keyof Pick<typeof Prisma, `${T}ScalarFieldEnum`>],
  string
>;

export function excludeFields<T extends Entity, K extends Keys<T>>(
  type: T,
  omit: K[],
) {
  type Key = Exclude<Keys<T>, K>;
  type TMap = Record<Key, true>;
  const result: TMap = {} as TMap;
  for (const key in Prisma[`${type}ScalarFieldEnum`]) {
    if (!omit.includes(key as K)) {
      result[key as Key] = true;
    }
  }
  return result;
}

export function includeFields<T extends Entity, K extends Keys<T>>(
  type: T,
  inc: K[],
) {
  type TMap = Record<K, true>;
  const result: TMap = {} as TMap;
  for (const key of inc) {
    result[key as K] = true;
  }
  return result;
}

const selectExc = excludeFields('Employee', ['passwd']);
const selectInc = includeFields('Employee', ['passwd']);

[platform-kit]

It's out of fucking control that this library is this old and doesn't have a way to exclude fields. This is a universal problem. Prisma, get it together.

[3zbumban]

for those still looking this is the current supported way to go:

// Returns an object or null
const getUser: object | null = await prisma.user.findUnique({
  where: {
    id: 22,
  },
  select: {
    email: true,
    name: true,
  },
})

prisma docs: select fields prisma docs: exclude fields

[juemrami]

@3zbumban I think the problem people are having is that you cannot you use the select and include syntax at the same time. Your proposed solution is okay if your User field consist of nothing else but id email password and a couple of other fields, but when you have to start selecting nested fields it can quickly get out of hands having to manually select every field you need.

[3zbumban]

@juemrami It is possible though to use include and select together see. As i understood the problem is that people want it to be possible the opposite way too (excluding instead of including). That is possible doing it this way. I did not read everything though and just wanted to provide a link to some docs that tackle this topic.

[zzau13]

@3zbumban I think the problem people are having is that you cannot you use the select and include syntax at the same time. Your proposed solution is okay if your User field consist of nothing else but id email password and a couple of other fields, but when you have to start selecting nested fields it can quickly get out of hands having to manually select every field you need.

import { Prisma } from '@prisma/client';

type A<T extends string> = T extends `${infer U}ScalarFieldEnum` ? U : never;
type Entity = A<keyof typeof Prisma>;
type Keys<T extends Entity> = Extract<
  keyof typeof Prisma[keyof Pick<typeof Prisma, `${T}ScalarFieldEnum`>],
  string
>;

export function excludeFields<T extends Entity, K extends Keys<T>>(
  type: T,
  omit: K[],
) {
  type Key = Exclude<Keys<T>, K>;
  type TMap = Record<Key, true>;
  const result: TMap = {} as TMap;
  for (const key in Prisma[`${type}ScalarFieldEnum`]) {
    if (!omit.includes(key as K)) {
      result[key as Key] = true;
    }
  }
  return result;
}

export function includeFields<T extends Entity, K extends Keys<T>>(
  type: T,
  inc: K[],
) {
  type TMap = Record<K, true>;
  const result: TMap = {} as TMap;
  for (const key of inc) {
    result[key as K] = true;
  }
  return result;
}

const selectExc = excludeFields('Employee', ['passwd']);
const selectInc = includeFields('Employee', ['passwd']);
const selectRelated = excludeFields('Related', ['other']);

And just one line

prisma.employee.find({ where, select: {... selectExc, relateds: { select: selectRelated }})

[stubbz]

do it.

[victorlim4]

it would be nice if the exclude parameter was added, to make it easier to work in large applications. For now we can use select, but only in small applications with little information returned.

great solutions guys, hope the prisma hears us💜

[iandoesallthethings]

There's another outstanding issue around BigInt fields erroring the client. As a workaround, I have to manually select every column except id because it errors before I could use the exclude function recommended in the docs.

If we can't get the BigInt serialization fixed, let's at least make it less verbose to exclude a single field!

[glomyst]

@pantharshit00 Despite our suggestions being very similar, I still believe the method I had suggested is better. Instead of having to remember to exclude certain fields every single time we fetch something (Which can still accidentally be forgotten) you specify it once in the Prisma Schema and explicitly request it when it's needed.

2498

Why not both? I need both options in my project. Every case of exclusion is not because the data is private. It maybe because for a specific endpoint we don't need some data.

[ManciuStefan]

I would also benefit greatly from having the exclude option.

[ceconcarlsen]

any updates?

[millsp]

Hey everyone, we have some news on this. We are exited to share the Client Extensions proposal. With this proposal, you will be able to extend your results, and thus exclude fields out of them. If you wanted to exclude fields permanently, you would do it like:

// your original client
const prisma = new PrismaClient()

// your extended client
const prismax = prisma.$extends({
  $result: {
    User: () => ({
      password: undefined // won't be fetched
    }),
  },
})

const user = await prisma.user.findFirst() // { name: string, age: string, password: string }
const user = await prismax.user.findFirst() // { name: string, age: string }

We are aware that you will need more flexibility to do it per query, and we will definitely get to this. In the mean time, we hope that Prisma Client extensions will help you to some extent. Feedback welcome :)