Prisma-client-js: PostgreSQL `<table>WhereUniqueInput` including a column which isn't unique when composite primary key is used

[miller-productions]

Bug description

I have observed what looks like a bug with how prisma is generating a <table>WhereUniqueInput.

Setup is this...

PostgreSQL database, tables being defined with composite primary key as an experiment to see what is possible for supporting a SAAS multi-tenant with shared database/shared schema.

Two relevant tables for this are User and Tenant.

Tenant has columns... id, name
Primary key for Tenant is: id

User has columns... userId, tenant, email (uniqueconstraint), other-columns-etc...
Primary key (composite) for User table is userId, tenant
User.tenant is part of a foreign key relationship to Tenant.id
User.tenant is integer not null;

How to reproduce

Steps to reproduce the behavior:

1. Confirm table setup as above
2. Run prisma introspect, then prisma generate
3. See result...

In generated prisma client, see the following...

export type UserWhereUniqueInput = {
  email?: string | null
  tenant?: number | null
  userId_tenant?: UserIdTenantCompoundUniqueInput | null
}

Expected behavior

Would not expect to see tenant as one of the possible keys in UserWhereUniqueInput. In other words, would expect to see...

export type UserWhereUniqueInput = {
  email?: string | null
  userId_tenant?: UserIdTenantCompoundUniqueInput | null
}

Prisma information

model User {
  email                         String  @unique
  firstname                     String
  lastname                      String?
  tenant                        Int
  userId                        String  @default(dbgenerated()) @unique
  Tenant                        Tenant  @relation(fields: [tenant], references: [id])

  @@id([userId, tenant])
  @@index([tenant], name: "fki_User_Tenant_id_fkey")
}

model Tenant {
  id           Int            @default(autoincrement()) @id
  name         String
  User         User[]
}

Environment & setup

• OS: Windows
• Database: PostgreSQL 12.2
• Prisma version: @prisma/cli : 2.0.0-beta.4
• Node.js version: v13.9.0

[pantharshit00]

This is expected. Composite filters are in unique filter as combination of fields of the composite filter yields a unique result.

For you example, client will have a composite filter like so:

await prisma.user.findOne({
  where:{
    userId_tenant:{
      userId: "something",
      tenant: "something_else"
   }
})

Combination of these two is unique so it is in the filter.

[miller-productions]

Hi @pantharshit00 ,

Thanks for replying.

Yes, I understand that the composite filter, userId_tenant: is available as one of the possible options for UserWhereUniqueInput.

This is due to @@id([userId, tenant]) in the schema for User. This is expected :white_check_mark:

---

What I don't understand is... why would tenant be available as an alternative option in UserWhereUniqueInput :confused:

In my schema, tenant does not uniquely identify User.

i.e. in this particular schema, you shouldn't be able to do the following, should you?

await prisma.user.findOne({
  where:{
    tenant: "something_else"
   }
})

[pantharshit00]

@miller-productions Can you please share the schema for that model?

[miller-productions]

Sure thing @pantharshit00, I have added it to the Prisma Information section above.

[pantharshit00]

@miller-productions Sorry for the late reply here? Can you please try again with the latest version? The input is not nullable anymore:

This is with version 2.1.3

[pantharshit00]

We did some fixes recently so this might get patched there.

[miller-productions]

Hi @pantharshit00,

Just dropping back in on this.

I'm currently running version 2.6.1 . The issue still exists.

This is what is being generated.

export type UserWhereUniqueInput = {
  userId?: string
  email?: string
  tenant?: number
  userId_tenant?: UserIdTenantCompoundUniqueInput
}

It still doesn't make sense to me. The primary key defined in the database is userId + tenant, a composite key. So, I would assume that you would need to provide a value for both when trying to select unique results.

[pantharshit00]

Hey @miller-productions

Looks like I initially misread this issue. I can confirm it now that we should not generate tenant or userId in the unique input.

Thanks for your patience here.

[dpetrick]

Fix available from 2.9.0-dev.58 onward. Will be included in the 2.9.0 release.