Excluding fields

By default Prisma Client returns all fields from a model. You can use select to narrow the result set, but that can be unwieldy if you have a large model and you only want to exclude one or two fields.

info

As of Prisma ORM 5.16.0, excluding fields globally and locally is supported via the omitApi Preview feature.

Excluding a field globally using omit

The following is a type-safe way to exclude a field globally using the omitApi Preview feature:

Schema

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["omitApi"]
}

model User {
  id        Int      @id @default(autoincrement())
  firstName String
  lastName  String
  email     String   @unique
  password  String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Code

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

// The password field is excluded in all queries, including this one
const user = await prisma.user.findUnique({ where: { id: 1 } })

Excluding a field locally using omit

The following is a type-safe way to exclude a field locally using the omitApi Preview feature:

Schema

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["omitApi"]
}

model User {
  id        Int      @id @default(autoincrement())
  firstName String
  lastName  String
  email     String   @unique
  password  String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Code

const prisma = new PrismaClient()

// The password field is excluded only in this query
const user = await prisma.user.findUnique({
  omit: {
    password: true
  },
  where: { 
    id: 1 
  } 
})

How to omit multiple fields

Omitting multiple fields works the same as selecting multiple fields: add multiple key-value pairs to the omit option. Using the same schema as before, you could omit password and email with the following:

const prisma = new PrismaClient()

// password and email are excluded
const user = await prisma.user.findUnique({
  omit: {
    email: true,
    password: true,
  },
  where: {
    id: 1,
  },
})

Multiple fields can be omitted locally and globally.

How to select a previously omitted field

If you omit a field globally, you can "override" by either selecting the field specifically or by setting omit to false in a query.

Explicit Select

const user = await prisma.user.findUnique({
  select: {
    firstName: true,
    lastName: true,
    password: true // The password field is now selected.
  },
  where: {
    id: 1
  }
})

Omit False

const user = await prisma.user.findUnique({
  omit: {
    password: false // The password field is now selected.
  },
  where: {
    id: 1
  }
})

When to use omit globally or locally

It's important to understand when to omit a field globally or locally:

• If you are omitting a field in order to prevent it from accidentally being included in a query, it's best to omit it globally. For example: Globally omitting the password field from a User model so that sensitive information doesn't accidentally get exposed.
• If you are omitting a field because it's not needed in a query, it's best to omit it locally.

Local omit (when an omit option is provided in a query) only applies to the query it is defined in, while a global omit applies to every query made with the same Prisma Client instance, unless a specific select is used or the omit is overridden.

Excluding the password field without using omit

note

The omitApi Preview feature, released in Prisma ORM 5.13.0, is the preferred way of omitting fields from a query result. The ability to globally omit fields was added to the omitApi Preview feature in Prisma ORM 5.16.0. This documentation is still relevant for versions of Prisma ORM prior to 5.13.0.

The following is a type-safe exclude function returns a user without the password field.

TypeScript

// Exclude keys from user
function exclude<User, Key extends keyof User>(
  user: User,
  keys: Key[]
): Omit<User, Key> {
  return Object.fromEntries(
    Object.entries(user).filter(([key]) => !keys.includes(key))
  )
}

function main() {
  const user = await prisma.user.findUnique({ where: 1 })
  const userWithoutPassword = exclude(user, ['password'])
}

JavaScript

// Exclude keys from user
function exclude(user, keys) {
  return Object.fromEntries(
    Object.entries(user).filter(([key]) => !keys.includes(key))
  );
}

function main() {
  const user = await prisma.user.findUnique({ where: 1 })
  const userWithoutPassword = exclude(user, ['password'])
}

In the TypeScript example, we've provided two generics: User and Key. The Key generic is defined as the keys of a User (e.g. email, password, firstName, etc.).

These generics flow through the logic, returning a User that omits the list of Keys provided.