prisma findUnique where takes only one unique argument

Question

I ran into an issue where I need to check if a user exists by his username and email since both are unique fields in the database, but I got an error. Argument where of type UserWhereUniqueInput needs exactly one argument, but you provided username and email. Please choose one. so, is there a way to execute this query just once?. instead of running one for each like the following

const user = await prisma.user.findUnique({
        where: {
          username,
          email,
        },
      });

and not like this

const user = await prisma.user.findUnique({
        where: {
          username,
        },
      });

const user = await prisma.user.findUnique({
        where: {
          email,
        },
      });

Galsworthy

Are both username and email required on your User model or is one of them marked as optional with a ? in the Prisma schema? – Scurrile

Answer 1

I am not entirely certain, but prisma returns a JS object... So perhaps a query like this should work:

const query = await prisma.user.findUnique({
        where: {
          user: user.username
        },
       select: {
         user: true,
         email: true
      }
      });

I believe this should work, but it still only finds by one unique. I am not sure exactly why you would select user + email as both unique, as I would assume that one is tied to the other anyhow.

I would take a look over here for further answers if my solution was not able to work: https://www.prisma.io/docs/concepts/components/prisma-client/crud#findfirst

Postdiluvian

Nikolas from the Prisma team here. I don't think the select makes a difference but this is key: "I am not sure exactly why you would select user + email as both unique, as I would assume that one is tied to the other anyhow." Since both id and email are already unique properties, it doesn't make any sense to provide both in a findUnique query. @Galsworthy you should be able to just use one of them and get the same result. – Scurrile

Answer 2

if you are looking for a unique value that would bring you a single result you can use findFirst as well. which would give you Object instead of Array. findMany returns an Array even though you are looking for a unique value.

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

Proglottis

I still don't know why this won't work for me. I am doing exactly this and I get "PrismaClientValidationError". I tried chatgpt, bard, google, no one seems to have solution to why this doesn't work. – Mime

Answer 3

Logically speaking, the email is a unique field (there won't be the same email shared by two persons). The username field isn't unique (multiple user's with same name).
This below code alone is enough to fetch that unique user.

const user = await prisma.user.findUnique({
        where: {
          email
        },
      });

But let's just assume that email or username alone isn't unique and a combination of email and username is unique.

Modify your schema shown below

model user {
  username : String
  email    : String
  @@unique([username, email])
}

You can now query by unique email and username without providing multiple where arguments

   const user = await prisma.findUnique({
        where: {
          username_email : { username, email },
        },
      });

References :

• docs
• composite keys

Wernher

I agree that logically we should all have unique emails, unless we're talking about company. Let's say you work at xy and your name is John Doe. You get fired and your email is destroyed or whatever. Then another John Doe comes and gets the same email. This can also work for goverments, etc. So, email uniqueness is out of the window 😁 (I am working right now on a project that will have that problem). – Mime

@Mime query by the unique id then. – Wernher

Answer 4

const users = await prisma.user.findMany({
       where: {
        OR: [
          {username},
          {email}
        ]
      }
  });
 if (users.length !== 0) {...}

Stoker

Answer 5

I used this for a signin and compare the password with bcrypt, this is a example of what i do:

const customerSignIn = async (req, res) => {
  const { email } = req.body
  try {
    const getUser = await customers.findUnique({
      where: { email },
      select: {
        email: true,
        password: true,
      },
    }) || null
    console.log(getUser)
    const compare = await comparePassword(req.body.password, getUser.password)
    console.log(compare)
    // getUser && getUser.password === req.body.password ? ....
    getUser && compare ? res.json({....

This work's for me good! you can check this and use this. Not problem, you can call the password here too

const { email, password } = req.body

and then pass this to the comparePassword method.

Rounding