Using multiple fields for a unique key in Prisma

In Prisma you define a unique key across multiple fields with the @@unique() attribute in your model. Then, when you query that model, you reference the compound key by joining the field names with an underscore.

I ran into an issue with Prisma that made me lose a bit of time, so I’ll write how I solved it.

Defining the compound unique key

I was modeling a “like” on a tweet. A user can like a tweet only once, so the combination of user and tweet must be unique. But neither field is unique on its own: a user likes many tweets, and a tweet gets liked by many users.

The model didn’t have an id field marked as @id, so I added a @@unique() to say user and tweet, together, defined the unique constraint:

model Like {
  user      Int
  tweet     Int
  createdAt DateTime @default(now())
  @@unique([user, tweet])
}

This means we can’t have more than 1 entry with the same (user, tweet) pair. The database enforces it: inserting a duplicate fails.

The error I hit

When I tried to delete an entry with

await prisma.like.delete({
  where: {
    user: 1,
    tweet: 1
  }
})

I ran into an error message:

PrismaClientValidationError: 
Invalid `prisma.like.delete()` invocation:

{
  where: {
    user: 12,
    ~~~~
    tweet: 22
    ~~~~~
  }
  ~~~~~~~~~~~
}

Argument where of type LikeWhereUniqueInput needs exactly one argument, but you provided user and tweet. Please choose one. Available args: 
type LikeWhereUniqueInput {
  user_tweet?: LikeUserTweetCompoundUniqueInput
}

Methods like delete(), update() and findUnique() want exactly one unique identifier in where. Two separate fields don’t count as one identifier, even if together they form the unique key.

The fix

Prisma generates a single field name for the compound key: the field names concatenated with an underscore. So user and tweet become user_tweet, and the two values go inside it as an object:

await prisma.like.delete({
  where: {
    user_tweet: {
      user: 1,
      tweet: 1
    }
  }
})

The same shape works for findUnique() and update() too.

In retrospect the error message was sort of explaining this, but I didn’t get it. It even names the generated field, user_tweet, in the LikeWhereUniqueInput type. Now I know to read those types carefully.

If you don’t like the generated name, you can choose your own in the schema:

@@unique(fields: [user, tweet], name: "likeId")

Then you’d write where: { likeId: { user: 1, tweet: 1 } } instead.