Preview feature feedback: Extended where unique

[floelhoeffel]

Please use this issue to share any feedback about the extendedWhereUnique functionality released in v4.5.0.

Documentation can be found here.

If you encounter a bug, please open a bug report in this repo.

Many thanks 👏🏻 from the Prisma team.

[capaj]

The link to docs is not working 🥲

[Weakky]

It will be as soon as the feature is actually released. Just a bit more patience and you'll have it 🙈. The documentation will be referred to from the release notes as well.

[wiverson]

Just noticed that optimistic concurrency is closed as implemented by this feature (yay!). Not clear from this issue how to implement, probably just a docs issue?

[janpio]

That was a bit over eager by @Weakky, only one part of that is implemented. I re-opened the issue and we will follow up there soon @wiverson.

[janpio]

@capaj Docs link should also work now.

[KATT]

"The preview feature "extendedWhereUnique" is not known"

Edit: Solved by reinstalling node_modules.

It works but my VSCode complains with The preview feature "extendedWhereUnique" is not known. Expected one of: referentialIntegrity, interactiveTransactions, fullTextSearch, fullTextIndex, tracing, metrics, orderByNulls, filteredRelationCount, fieldReference

I updated my packages using npx npm-check-updates /prisma/ -u && npm i

Update: you can ignore this, I did the rm package-lock.json && rm -rf node_modules && npm i-classic

package.json etc

package.json:

    "@prisma/client": "^4.5.0",
    "prettier-plugin-prisma": "^4.4.0",
    "prisma": "^4.5.0",

package-lock.json:

    "node_modules/@prisma/client": {
      "version": "4.5.0",
      "resolved": "https://registry.npmjs.org/@prisma/client/-/client-4.5.0.tgz",
      "integrity": "sha512-B2cV0OPI1smhdYUxsJoLYQLoMlLH06MUxgFUWQnHodGMX98VRVXKmQE/9OcrTNkqtke5RC+YU24Szxd04tZA2g==",
      "hasInstallScript": true,
      "dependencies": {
        "@prisma/engines-version": "4.5.0-43.0362da9eebca54d94c8ef5edd3b2e90af99ba452"
      },
      "engines": {
        "node": ">=14.17"
      },
      "peerDependencies": {
        "prisma": "*"
      },
      "peerDependenciesMeta": {
        "prisma": {
          "optional": true
        }
      }
    },
    "node_modules/@prisma/engines": {
      "version": "4.5.0",
      "resolved": "https://registry.npmjs.org/@prisma/engines/-/engines-4.5.0.tgz",
      "integrity": "sha512-4t9ir2SbQQr/wMCNU4YpHWp5hU14J2m3wHUZnGJPpmBF8YtkisxyVyQsKd1e6FyLTaGq8LOLhm6VLYHKqKNm+g==",
      "devOptional": true,
      "hasInstallScript": true
    },
    "node_modules/@prisma/engines-version": {
      "version": "4.5.0-43.0362da9eebca54d94c8ef5edd3b2e90af99ba452",
      "resolved": "https://registry.npmjs.org/@prisma/engines-version/-/engines-version-4.5.0-43.0362da9eebca54d94c8ef5edd3b2e90af99ba452.tgz",
      "integrity": "sha512-o7LyVx8PPJBLrEzLl6lpxxk2D5VnlM4Fwmrbq0NoT6pr5aa1OuHD9ZG+WJY6TlR/iD9bhmo2LNcxddCMr5Rv2A=="
    },
    "node_modules/@prisma/prisma-fmt-wasm": {
      "version": "4.4.0-66.f352a33b70356f46311da8b00d83386dd9f145d6",
      "resolved": "https://registry.npmjs.org/@prisma/prisma-fmt-wasm/-/prisma-fmt-wasm-4.4.0-66.f352a33b70356f46311da8b00d83386dd9f145d6.tgz",
      "integrity": "sha512-Hc2i5nfAt3nLDUkQNWJcKFJaA9Avd5zz6t85w9SW7P0vGtFXScQ+xIu6znbULr9bc0pgTWejY1We2u/7EMxHWw==",
      "dev": true
    },

---

The feature is very useful.

Not sure if it's better -- maybe it should be a distinct conscious choice when you narrow the selection?

await prisma.article.update({
  where: {
   id: 5,
   $narrow: { version: 1 },
  },
  data: { /* ... */ }
})

Furthermore - I've always found it a bit confusing that .update doesn't work like UPDATE in sql and would kinda like if it was called .updateUnique [and then you could add an updateFirst later if/when wanted].

[Weakky]

@KATT We designed a very similar API while researching the topic. Here were the 3 designs we had:

1) Adapt WhereUniqueInput

await prisma.user.update({
  where: { email: "alice@prisma.io", OR: [{ name: "Alice" }, { name: "Bob" }] }
  data: { email: "alice2@prisma.io" }
})

2) Nested if / where / filter

await prisma.user.update({
  where: { email: "alice@prisma.io", _if: { OR: [{ email: "" }], isDeleted: false }},
  data: { email: "alice2@prisma.io" },
})

3) Additional if / where / filter

await prisma.user.update({
  where: { email: "alice@prisma.io" }, // required
  data: { email: "alice2@prisma.io" },
  if: { isDeleted: false } // optional
})

You can see how design 2) is very similar to yours. We ultimately decided to go with design 1) because 2) and 3) felt confusing from a SQL perspective. There's no such thing as if or narrow in SQL. That being said, it is a preview feature and we're open to suggestions.

As for design 3), it was too hard to integrate with our existing API without adding lots of union input types.

One downside of the current design is how it clutters the autocompletion and makes it hard to find the unique fields that need to be set. On the other hands, it's very transparent wrt. how SQL works.

Let us know what you all think 🙏

[YaakovR]

@floelhoeffel Thanks for adding this feature!
Thus far it is working for me as expected, except when including a one-to-one relation.

model User {
  id         Int      @id
  name       String
  address_id Int?     @unique
  address    Address? @relation(fields: [address_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
}

model Address {
  id         Int       @id
  street     String
  deleted_at DateTime?
  user       User?
}

prisma.user.findUnique({
  where: {
    id: 1,
  },
  include: {
    address: {
        where: {
          delete_at: null,
        },
      },
    },
  },
});

Running the above, produces the following error:

Unknown arg `where` in include.address.where for type Contact. Did you mean `select`? Available args:
type address {

}

Is my scenario currently possible to achieve?

[Weakky]

Hey @YaakovR,

Good catch and apologies, this is an oversight from myself. Your use case should work but it's currently not supported. We'll add that for the next Prisma release.

To be clear:

• ✅ 1-1 optional relations should have a WhereInput on nested reads
• ❌ 1-1 required relations should not have a WhereInput on nested reads.

Adding a WhereInput on required 1-1 relations could break the contract (your datamodel and thus your TS typings) and return null when the types always expect to return something.

[YaakovR]

Thanks @Weakky! That makes sense.

I'll look out for the next release!

[jacobchrismarsh]

This is awesome!

Are findUnique calls with non-unique fields in the where clause still batched together like described here?

[SamuelMS]

@Weakky I'm also seeing this error after bumping to 4.5.0. Have even tried nuking my node_modules directory, but for some reason the Prisma CLI is reporting it's still on version 4.3.1. Let me know if I'm just doing something dumb.

error: The preview feature "extendedWhereUnique" is not known. Expected one of: referentialIntegrity, interactiveTransactions, fullTextSearch, fullTextIndex, tracing, metrics, orderByNulls, filteredRelationCount, fieldReference

package.json:

"dependencies": {
  "@prisma/client": "4.5.0",
},
"devDependencies": {
  "prisma": "4.5.0",
},

yarn prisma --version
prisma                  : 4.5.0
@prisma/client          : 4.5.0
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine 0362da9eebca54d94c8ef5edd3b2e90af99ba452 (at node_modules/prisma/node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli 0362da9eebca54d94c8ef5edd3b2e90af99ba452 (at node_modules/prisma/node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine    : introspection-core 0362da9eebca54d94c8ef5edd3b2e90af99ba452 (at node_modules/prisma/node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary           : prisma-fmt 0362da9eebca54d94c8ef5edd3b2e90af99ba452 (at node_modules/prisma/node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Format Wasm             : @prisma/prisma-fmt-wasm 4.5.0-43.0362da9eebca54d94c8ef5edd3b2e90af99ba452
Default Engines Hash    : 0362da9eebca54d94c8ef5edd3b2e90af99ba452
Studio                  : 0.476.0

Working with the schema (i.e. running prisma generate) causes the error:

Environment variables loaded from .env
Prisma schema loaded from src/server/db/schema.prisma
Datasource "db": PostgreSQL database "betterform", schema "public" at "localhost:5832"

Already in sync, no schema change or pending migration was found.

✔ Generated Prisma Client (4.5.0 | library) to ./node_modules/@prisma/client in 76ms
Schema validation error - Error (query-engine-node-api library)
Error code: P1012
error: The preview feature "extendedWhereUnique" is not known. Expected one of: referentialIntegrity, interactiveTransactions, fullTextSearch, fullTextIndex, tracing, metrics, orderByNulls, filteredRelationCount, fieldReference
  -->  schema.prisma:8
   |
 7 |   provider = "prisma-client-js"
 8 |   previewFeatures = ["extendedWhereUnique"]
   |

Validation Error Count: 1
[Context: getDmmf]

Prisma CLI Version : 4.3.1

[Weakky]

Hey @jacobchrismarsh,

Batching is currently broken in 4.5.0. However, we have a PR that will land next release which will fix that. Docs will be updated accordingly.

TLDR is: The batching will still work if you only use unique & non-unique scalar fields of the model you're querying and if the filter applied is equals (or the shortand syntax: field: <value>. So { field: { equals: <value> } } or { field: <value> }).

If you use any boolean operators, relation filters or any other filters than equal on a scalar field, then we will not try to batch findUniques in a findMany and will instead run them independently.

@SamuelMS Are you sure your Prisma CLI is not globally installed and you haven't forgotten to update it ?

[SamuelMS]

@Weakky Hmm, fairly certain – it's being installed in the repo and this is what I get from npm list --global:

$HOME/.nvm/versions/node/v16.13.2/lib
├── corepack@0.10.0
├── npm@8.19.2
└── vercel@28.4.12

[janpio]

@SamuelMS The end of your output shows that this is somehow running Prisma CLI Version : 4.3.1. Something is off in your project. (This looks like output of prisma migrate dev which runs some prisma generate under the hood. Do you get the same error when you run npx prisma generate in the root of your project manually?)

[Weakky]

Apologies for the false promise @YaakovR 🙈, we couldn't get the where argument on optional 1-1 read in time for the release.

It should be in the next release though, bear with us 🙏

[YaakovR]

I was wondering about that. Thanks @Weakky for clarifying.

[leppaott]

Does upsert support boolean operators even though they use the same WhereUnique type than update?
I didn't exactly test this flag but seems upsert isn't supported without extended?

await prisma.user.update({
  where: { id: 1, OR: [{ email: "bob@prisma.io" }, { email: "alice@prisma.io" }] },
        // ^^^ Valid: the expression specifies a unique field (`id`) outside of any boolean operators
  data: { ... }
})

[tonypeng]

Hey @YaakovR,

Good catch and apologies, this is an oversight from myself. Your use case should work but it's currently not supported. We'll add that for the next Prisma release.

To be clear:

• ✅ 1-1 optional relations should have a WhereInput on nested reads
• ❌ 1-1 required relations should not have a WhereInput on nested reads.

Adding a WhereInput on required 1-1 relations could break the contract (your datamodel and thus your TS typings) and return null when the types always expect to return something.

@Weakky what about having a different TS return typing when a WhereInput is specified? Or a new _optional option under include and select? There can be instances in which there is a difference between the data model (e.g. a User always has a Profile) and the view model (e.g. the frontend may be able to view a User but not their Profile if the profile is set to private); we would still like to enforce that 1-1 relation in the database (the source of truth), but we may want to hide it from the consumer (e.g. the front-end)

My use case is implementing access control policies at the query level (e.g. something like queryWithPolicy('user', loggedInUser).findUnique({ where: {id: <userId>}, include: {profile: true} }); for one-to-one relations right now since I cannot inject a where clause into the include query I need to do a double read to re-fetch the model on the other side of the relation and inject a where clause there and then logically AND the two results together. It would be a lot painless (and more performant, especially when there are multiple levels of relations leading to an exponential number of models and additional queries to check) if we could simply inject the where clause into the original query.

Something like this I think could be ideal, preserving the data-model contract and introducing optionality:

model User {
  id         Int      @id
  name       String
  address_id Int?     @unique
  address    Address @relation(fields: [address_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
}

model Address {
  id         Int       @id
  street     String
  deleted_at DateTime?
  user       User
}

prisma.user.findUnique({
  where: {
    id: 1,
  },
  include: {
    _optional: {
      address: {
          where: {
            delete_at: null,
          },
        },
      },
    },
  },
});

A few of us have run into this issue here: #16049

[kylejw2]

Love this feature! Thank you Prisma team!

[cyrilchapon]

Adding a feedback to the discussion, I'm encountering a trouble trying to filter like where some_field IS NULL, like so :

model User {
  id String @id @default(dbgenerated("gen_random_uuid()")) @map("id") @db.Uuid()

  authAccount String? @unique
}

Trying to make a safe atomic update :

    await prismaClient.user.update({
      where: { id: userId, authAccount: null },
      data: { authAccount: createdAuthAccount.id },
    })

I'm getting a Typescript error on authAccount: null saying

Impossible to assign type 'null' to type 'string | undefined'. ts(2322)

---

Temporary workaround : wrap in a dumb OR or AND clause :

    await prismaClient.user.update({
      where: { id: userId, OR([{ authAccount: null }]) },
      data: { authAccount: createdAuthAccount.id },
    })

[Weakky]

Hey @cyrilchapon,

That's an interesting problem that we haven't considered in our design and one that'll be hard/convoluted to solve with the current approach. The reason you can't filter authAccount with null is because we need filters on unique fields to guarantee a unique result but null wouldn't guarantee that (despite the field being marked as @unique).

However, with the extension of unique where, we've loosened the rules so that you only need a single unique criterion to be present in your filter while allowing more "non-unique" filters.

The issue in your case, is that we should make authAccount accept nulls only when the filter already contains a unique criterion (i.e one that will guarantee a unique result). In your case, the filter on the id field would guarantee that the query returns a single unique record.

We're unfortunately not in a position to make that happen atm though. It would be possible from a typing perspective, but harder from a query validation perspective. We validate queries against a schema, similar to GraphQL, and that schema does not support "conditional typings" on fields.

The good news is, there's a simple workaround. The bad news is, it's confusing. I'll have a chat with the team about that.

[cyrilchapon]

Hey @Weakky ,

That's what I guessed. Well; to circumvent this, and since the "dumb AND/OR workaround" works; I feel like arguing the syntax could be reworked in a less-sexy but way-easier-to-type-and-parse :

prismaClient.someModel.update({
  where: {
    // unique fields at the root,
    // easy to parse-and-type
    id: 2,

    // Non unique ones wrapped in a key
    // (could be renamed ^^)
    NON_UNIQUE: {
      someNonUniqueField: null
    }
  },
})

or even

prismaClient.someModel.update({
  where: {
    // unique fields at the root,
    // easy to parse-and-type
    id: 2,
  },

  // Non unique ones wrapped in an entire clause
  // (could also be renamed ^^)
  atomicUpdateWhere: {
    someNonUniqueField: null
  }
})

[karimcambridge]

Hello. I didn't read much of everything but it's actually crazy of what’s going on. I'm trying to upsert oauth2 tokens

CREATE TABLE `rider_tokens` (
  `id` int(10) UNSIGNED NOT NULL,
  `rider_id` mediumint(8) UNSIGNED NOT NULL,
  `access_token` text COLLATE utf8_unicode_ci NOT NULL,
  `refresh_token` text COLLATE utf8_unicode_ci NOT NULL,
  `expiration` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `rider_tokens`
  ADD PRIMARY KEY (`id`),
  ADD KEY `rider_id` (`rider_id`);

ALTER TABLE `rider_tokens`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `rider_tokens`
  ADD CONSTRAINT `rider_tokens_ibfk_1` FOREIGN KEY (`rider_id`) REFERENCES `riders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

rider_id is an index and id is an auto increment primary key. I never need to use id really, it’s just there for indexing perposes and rider_id is what will always be used.

async saveRiderTokens(rider_id: number, data: any): Promise<any> {
		return this.prisma.rider_tokens.upsert({
			where: { // here
				rider_id,
			},
			update: {
				access_token: await this.hash(data.access_token),
				refresh_token: await this.hash(data.access_token),
			},
			create: {
				rider_id,
				access_token: await this.hash(data.access_token),
				refresh_token: await this.hash(data.refresh_token),
			},
		});
	}

it doesn't allow me to do this because it expects id to always be there.

error:

src/services/auth/auth.service.ts:65:4 - error TS2322: Type '{ rider_id: number; }' is not assignable to type 'rider_tokensWhereUniqueInput'.
  Type '{ rider_id: number; }' is not assignable to type '{ id: number; } & { id?: number; AND?: Enumerable<rider_tokensWhereInput>; OR?: Enumerable<rider_tokensWhereInput>; ... 5 more ...; riders?: (Without<...> & ridersWhereInput) | (Without<...> & RidersRelationFilter); }'.
    Property 'id' is missing in type '{ rider_id: number; }' but required in type '{ id: number; }'.

65    where: {
      ~~~~~

  node_modules/.prisma/client/index.d.ts:41766:5
    41766     where: rider_tokensWhereUniqueInput
              ~~~~~
    The expected type comes from property 'where' which is declared here on type '{ select?: rider_tokensSelect; include?: rider_tokensInclude; where: rider_tokensWhereUniqueInput; create: (Without<...> & rider_tokensUncheckedCreateInput) | (Without<...> & rider_tokensCreateInput); update: (Without<...> & rider_tokensUncheckedUpdateInput) | (Without<...> & rider_tokensUpdateInput); }'

[Jolg42]

Thank you everyone for the feedback 🙌🏼

We’re excited to announce the extendedWhereUnique Preview feature is now Generally Available. This means you can use the feature without the Preview feature flag in the Prisma schema.

We first introduced this feature in version 4.5.0 to add support for non-unique columns inside where clauses for queries that operate on unique records, such as findUnique, update, and delete, which was previously not possible.

For example, consider the following model:

model Article {
  id      Int    @id @default(autoincrement())
  content String
  version Int
}

See Prisma 5.0.0 release notes.