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
• Add omit option for field selection #3246

[thecodeah]

@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

[pantharshit00]

Hey @thecodeah, I think I linked the same thing twice in your issue. Added a note in your issue: #2498 (comment)

[ghost]

At least for me, this is a very wanted feature now because TS forbids using the delete operator since TS 4.0:

microsoft/vscode#96022 (comment)

As a workaround I'm using the object rest syntax { password, ...userWithoutPassword } = user, but the unused variable warnings are kinda annoying.

[darioielardi]

This becomes an even bigger problem when the column I want to hide is in a nested object as a result of an include query.

I think an omit clause would work but a schema annotation would be even better, as suggested by @thecodeah.

[matthewmueller]

@thecodeah, @darioielardi, and @flybayer expressed concerns with this proposal. Each query you write needs to explicitly exclude private fields. They would prefer an "exclude-by-default" approach.

To complicate matters, being able to somehow access excluded fields from the Prisma Client is important for tests. Any solution we decide on needs to have this feature.

Solution A: add an attribute to the schema

One idea proposed by @darioielardi is to add an attribute in the schema that will exclude a field. @flybayer suggested that if you want to include an excluded-by-default field (for tests) you could select that field explicitly.

Concretely, that would look like this:

model User {
  id Int @id
  password String @hidden // or @private or @exclude
}

prisma.user.findFirst({})
// { id: 1 }

prisma.user.findFirst({
  select: { 
    password: true
  }
})
// { password: "8674305" }

The downside of this is that when you do want to include an excluded field, you need to explicitly select all the other fields, like id.

Solution B: exclude when you initialize the client

Another possibility is excluding fields when you initialize the client. Something like:

const prisma = new PrismaClient({
  exclude: { 
    user: {
      password: true 
    }
  }
})

---

What do you think? Do you have other ideas on how we could solve this?

[flybayer]

I would prefer it in the schema so you can see everything in one view and easily see if you forgot to hide a certain field. Whereas if it's in another file (and one that is rarely viewed), you can easily forget about it.

[darioielardi]

I agree with @flybayer, I would definitely prefer to have it in the schema.

However I'm concerned about the need to add every field to the select object to explicitly select a hidden column.

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
})

That's probably a bit too much, but it might work.

[ValentinFunk]

I think a feature where you list the fields that are included by default would be better than a list of fields that are excluded, because as you add new fields to the db someone will forget to add @hidden and accidentally expose it in some API that simply returns say the result of the findOne as JSON. Maybe you could instead explicitly list the fields that are returned by default? Adding to that list will make it intentional, whereas when just adding a field to the DB you probably didn't want to change your API.

You could also leave the decision (list include or list exclude) up to the developer. Algolia does this for example here: https://www.algolia.com/doc/api-reference/api-parameters/attributesToRetrieve/. On an "index" which is basically a table you can specify which fields will be retreived by the client by default. It can be overwritten at query level. Here is how they solved it:

'attributesToRetrieve' => [
  'attribute1', // list of attributes to retrieve
  'attribute2'
]
'attributesToRetrieve' => [
  '*' // retrieves all attributes
]
'attributesToRetrieve' => [
  '*', // retrieves all attributes
  '-attribute1', // except this list of attributes (starting with a '-')
  '-attribute2'
]

Maybe this approach could work as well?

[scriptcoded]

I think @kamshak's point about forgetting to add @hidden to a field makes sense to some degree, although at the same time I believe that the majority of fields on any given model will not be hidden. This means that for every field added you also have to explicitly mark those fields as non-hidden.

const prisma = new PrismaClient({
  exclude: { password: true }
})

I also believe that there is an issue with this method of excluding fields suggested by @matthewmueller. There might might be multiple models with the same field, but all models shouldn't necessarily have them be hidden. Also, it might become unclear that a field is hidden when creating a new model and the creator of that model is unaware of a field being excluded "globally".

The one time I could see this being useful would be when you want to exclude something on all models, like createdAt. (see https://github.com/prisma/prisma-client-js/issues/649#issuecomment-712228596)

Personally I think adding a @hidden attribute makes a lot more sense as it reduces the amount of code necessary for each model, keeps the option at model level, and is only adding an attribute which makes it backwards compatible.

[matthewmueller]

Thanks @scriptcoded, that's indeed a mistake in the code on my part, the proposal should be:

const prisma = new PrismaClient({
  exclude: { 
    user: {
      password: true 
    }
  }
})

[samrith-s]

While this is good, we would still have to manually exclude things everywhere we use it, right?

Or does this mean we simply override parameters in the middleware?

[YannSuissa]

Any progress on this exclude issue?
I'm struggling to hide a private field.. on client side it's easy to hide it but if you use a graphql tool like playground it's easy to hack.
An exclude keyword would be perfect.
Thanks

[benjibuiltit]

Came here looking for a similar feature. I'll add my input that I'd also like to see an attribute at the model level and then an explicit opt-in when necessary to avoid any accidental leaking.

I'll also offer up an alternative workaround for those who may stumble across this issue before the feature is released. You can create a model for the sensitive data which you want to hide and use a relation to map between the main model and the sensitive data. The sensitive data will live in an alternate table which you can query for when you do need it, but your query for your main model won't have to be "massaged" so frequently. For example:

model Users {
  id  String  @id @default(uuid())
  email String
}

model HashedPasswords {
  userId String @id
  user Users @relation(fields: [userId], references: [id])
  password String
}

This will allow you to execute a nested query w/ a transactional guarantee that your sensitive data is written/created w/ your main model.

const result = await prisma.hashedPasswords.create({
      data: {
        password: hashedPassword,
        user: {
          create: {
            email
          }
        }
      }
    });

Because the sensitive data exists in a separate table you can query your main table w/o worrying about leaking anything.

And finally, to explicitly fetch the password for validation...

const password = await prisma.hashedPasswords.findUnique({
      where: {
        userId: {
          equals: userId
        }
      }
})

[ragokan]

I do totally agree on this, especially for the things like password that you don't want to show to users. Mongoose have a great feature, -select which just removes password, I hope that prisma listens us.

[muhdfaiz]

Hi, I'm looking for a solution to hide the field and found this topic. Just want to give input that I prefer the way mentioned by @matthewmueller by specifying @hidden or @exclude or @private in the schema.

[IamManchanda]

exclude is really needed especially for hiding "column id's" & passwords

We can do it either like

    const users = await prisma.user.findMany({
      include: { posts: true },
      exclude: { id: true, password: true }
    });
    return res.status(200).json(users);

or, as @matthewmueller specified, using @hidden or @exclude or @private in the schema

[itsjbecks]

I'd vote in favour of the @private on the schema.

I'd argue that if I'm adding sensitive data to the database I'm generally aware it's sensitive at that point. I imagine it would be far easier to neglect explicitly writing exclude: { password: true } when another developer (or let's face it, even me) is creating an endpoint related about fetching posts & comments

Having to also explicitly exclude sensitive data fields each time feels like extra work to get baseline security into products.

That all being said, something I think important to consider is the unintended side-effects of naming collisions.

const users = await prisma.user.findMany({
  include: {
    private: true
  },
})

const users = await prisma.user.findMany({
  include: {
    private: {
      password: true,
    },
  },
})

This no longer would allow private/hidden/exclude to be columns or relations.

model User {
  id        Int     @id
  name      String
  password  String  @private
  private   Boolean
}

[omar-dulaimi]

Can't wait to test this feature in 2.18.0 this tuesday
I'm currently facing a use case regarding hiding certain fields from playground/client

[janpio]

Can't wait to test this feature in 2.18.0 this tuesday

This issue was not closed, so this will unfortuately not be part of 2.18.0.

[gregg-cbs]

@timsuchanek @Jolg42 Can you help us prioritize this feature? Highly requested by users, but no attention is given, resulting in a 4 year wait.

This feature, is the most sought after feature in the whole of prisma and this request will be 4 years old this year:

[incrediblezayed]

+1

[yangfuzhang]

This feature is what I need.

[austinkelleher]

We just found this issue when looking for a method of preventing sensitive fields from being queried accidentally.

[vinaykharayat]

One more feature that is probably useful but prisma team chose not to work on it, similar to GeoLocation/Spartial type support. SMH, really need to reconsider using prisma for my new projects.

[tuedodev]

Four years of discussion that has led to no result. Some of the main points that speak against it, or at least would be difficult to implement, were ambivalent results in testing or naming collisions. But what I can't get my head around is the question of why I can simply include one or more fields in the query, but excluding them is supposed to be the big problem. I would like to call this an unintuitive anti-pattern. And the solutions that are offered are too verbose imo. Parturient montes, nascetur ridiculus mus. Have a nice day.

[Sirjoseph94]

But really what is the reason behind why it isn't implemented? Because this is a very much needed feature.

[janpio]

Please don't @ mention random team members @lucasmtav, this is impolite towards the people working here (or not working here any more). Thank you.

[janpio]

We are aware of the importance of this functionality for many of our users. We have not had the capacity yet to work on this though, as we were busy with other things. That the issue was not closed means that we consider this a valid feature request that we will prioritize against all the other issues and feature requests that exist as soon as possible. We are a small team that serves hundreds of thousands of users, that created thousands of issues - and this is one of the unfortunately many issues that did not get resolved as fast as we all wish they would.

I actually hope to have a happier message here soon.

Until then, I hope we can all keep this discussion civilized and constructive. Telling us over and over that it has been 4 years, does not really help us - or the many people subscribed to this issue.

[emrahaydemir]

that would be great!

const prisma = new PrismaClient({
  exclude: { 
    user: {
      password: true 
    }
  }
})

Looking forward to it! Good luck with your hard work!

for now 🙂 :

delete response.password;

[smohammadhn]

So, stuff is happening ... can't wait to see the results

[smohammadhn]

In the meantime, let's use this workaround:
(inspired from the official docs, completely type-safe)

Define these two functions in your utilities file:

// Exclude keys from an object
export function excludeFromObject<T, K extends keyof T>(obj: T, keys: K[]): Omit<T, K> {
  return Object.fromEntries(Object.entries(obj).filter(([key]) => !keys.includes(key as K))) as Omit<T, K>
}

// Exclude keys from objects in a list
export function excludeFromList<T, K extends keyof T>(objects: T[], keysToDelete: K[]): Omit<T, K>[] {
  return objects.map((obj) => excludeFromObject(obj, keysToDelete)) as Omit<T, K>[]
}

Then use them like this:

  async findAll() {
    const allItems = await this.prisma.user.findMany()

    return excludeFromList(allItems, ['password'])
  }

  async findOne(id: number) {
    const foundItem = await this.prisma.user.findUnique({
      where: {
        id,
      },
    })

    return excludeFromObject(foundItem, ['password'])
  }

(hint: for usage, you don't need to pass the generics, they will be inferred by the type of your first & second argument)

[ravshansbox]

In the meantime, let's use this workaround: (inspired from the official docs, completely type-safe)

Define these two functions in your utilities file:

// Exclude keys from an object
export function excludeFromObject<T, K extends keyof T>(obj: T, keys: K[]): Omit<T, K> {
  return Object.fromEntries(Object.entries(obj).filter(([key]) => !keys.includes(key as K))) as Omit<T, K>
}

// Exclude keys from objects in a list
export function excludeFromList<T, K extends keyof T>(objects: T[], keysToDelete: K[]): Omit<T, K>[] {
  return objects.map((obj) => excludeFromObject(obj, keysToDelete)) as Omit<T, K>[]
}

Then use them like this:

  async findAll() {
    const allItems = await this.prisma.user.findMany()

    return excludeFromList(allItems, ['password'])
  }

  async findOne(id: number) {
    const foundItem = await this.prisma.user.findUnique({
      where: {
        id,
      },
    })

    return excludeFromObject(foundItem, ['password'])
  }

(hint: for usage, you don't need to pass the generics, they will be inferred by the type of your first & second argument)

Just use lodash.omit()

[janpio]

Hey, we just released the first part to supporting exclusion of fields from query results, the omit query option that can be used to exclude individual fields on a per Prisma Client query basis. It is behind the omitApi preview feature, so you can all test this first and we can collect feedback.

• Documentation: https://www.prisma.io/docs/orm/reference/prisma-client-reference#omit-preview
• Feedback discussion: Preview feature feedback: `omitApi`, exclude fields from Prisma Client query results #23924

We'll work on a more global option to remove fields from all Prisma Client queries next.

[julienfdev]

oh wow 👏
I'll miss this issue 😅

[janpio]

Same here, but we haven't fully implemented all the requested functionality yet (global exclusion of fields from the result set), so we all have a few more weeks to mentally prepare ourselves to let go of it 😆

[ravshansbox]

@janpio this is already a huge step and work, thank you all who was involved to!

[Sirjoseph94]

Awesome. At long last, thanks guys for the efforts.

[gregg-cbs]

Wow im smiling. What a moment! It really exists: