P
Prismaā€¢2d ago
saulgoodman

why am I getting this error when trying to update 1 user and using null in where

    await db.user.update({
      where: {
        id: user.id,
        username: null,
      },
      data: {
        username: result.data.username,
      },
    });
    await db.user.update({
      where: {
        id: user.id,
        username: null,
      },
      data: {
        username: result.data.username,
      },
    });
this is my schema 
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions", "strictUndefinedChecks"]
}

datasource db {
  provider   = "postgresql"
  url        = env("DATABASE_URL")
  extensions = [citext(schema: "pg_catalog")]
}

model User {
  id        String  @id @default(cuid())
  username  String? @unique @db.Citext
  githubId  String? @unique
  email     String  @db.VarChar(200)
  avatarUrl String?
  // avatarSource
  Post      Post[]

  @@map("users")
}

model Post {
  id     String @id @default(cuid())
  title  String @db.VarChar(20)
  userId String

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("posts")
}
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions", "strictUndefinedChecks"]
}

datasource db {
  provider   = "postgresql"
  url        = env("DATABASE_URL")
  extensions = [citext(schema: "pg_catalog")]
}

model User {
  id        String  @id @default(cuid())
  username  String? @unique @db.Citext
  githubId  String? @unique
  email     String  @db.VarChar(200)
  avatarUrl String?
  // avatarSource
  Post      Post[]

  @@map("users")
}

model Post {
  id     String @id @default(cuid())
  title  String @db.VarChar(20)
  userId String

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("posts")
}
No description
3 Replies
Prisma AI Help
Prisma AI Helpā€¢2d ago
Skipping coffee for instant knowledge! Let's brew that AI answer now. Based on the information provided in the knowledge sources, I can explain why you're encountering this error when trying to update a user with a null value in the where clause. The error you're experiencing is related to how Prisma handles null values in unique fields when used in where conditions. According to the Github issue #6359, Prisma doesn't support using null values in where conditions for unique fields in upsert operations. Although your case is about an update operation, the same principle applies. The reason for this behavior is that Prisma requires a non-null value to uniquely identify a record. When you use null in the where clause for a unique field (in your case, username), Prisma can't guarantee that it will find a single, unique record to update. To resolve this issue, you have a few options: 1. Remove the username field from the where clause if it's not necessary for identifying the user: 
await db.user.update({
  where: {
    id: user.id,
  },
  data: {
    username: result.data.username,
  },
});
await db.user.update({
  where: {
    id: user.id,
  },
  data: {
    username: result.data.username,
  },
});
2. If you need to check for a null username, you can use a separate findUnique query first, and then update if the user is found: 
const userToUpdate = await db.user.findUnique({
  where: {
    id: user.id,
    username: null,
  },
});

if (userToUpdate) {
  await db.user.update({
    where: { id: user.id },
    data: {
      username: result.data.username,
    },
  });
}
const userToUpdate = await db.user.findUnique({
  where: {
    id: user.id,
    username: null,
  },
});

if (userToUpdate) {
  await db.user.update({
    where: { id: user.id },
    data: {
      username: result.data.username,
    },
  });
}
3. If you're using a recent version of Prisma, you might be able to use the IS NULL condition in a findFirst query followed by an update: 
const userToUpdate = await db.user.findFirst({
  where: {
    id: user.id,
    username: { equals: null },
  },
});

if (userToUpdate) {
  await db.user.update({
    where: { id: user.id },
    data: {
      username: result.data.username,
    },
  });
}
const userToUpdate = await db.user.findFirst({
  where: {
    id: user.id,
    username: { equals: null },
  },
});

if (userToUpdate) {
  await db.user.update({
    where: { id: user.id },
    data: {
      username: result.data.username,
    },
  });
}
Keep in mind that Prisma's behavior with null values can be tricky, as discussed in t he Prisma documentation on null and undefined. If you continue to face issues or need more specific guidance, you might want to open a new issue on the Prisma GitHub repository or consult their documentation for the most up-to-date information on handling null values in queries. šŸ¤” Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly. In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.
saulgoodman
saulgoodmanOPā€¢2d ago
using prisma version 6.2.1 is there any way to do this without making two database operations?
Nurul
Nurulā€¢20h ago
I don't think so, you can use a raw sql if you want to avoid two database queries https://www.prisma.io/docs/orm/prisma-client/using-raw-sql/typedsql
Writing Type-safe SQL with TypedSQL and Prisma Client | Prisma Docu...
Learn how to use TypedSQL to write fully type-safe SQL queries that are compatible with any SQL console and Prisma Client.

Did you find this page helpful?