Prisma Client: mongodb-azure-cosmosdb `client.user.create()` fails with `Command failed (17276): Use of undefined variable: REMOVE)`

Jolg42 commented 2 years ago

Bug description

Our ecosystem-test for mongodb-azure-cosmosdb is failing since 4.3.0-dev.81 https://github.com/prisma/ecosystem-tests/runs/8069434845?check_suite_focus=true#step:6:490

PrismaClientUnknownRequestError: 
Invalid `client.user.create()` invocation in
/home/runner/work/ecosystem-tests/ecosystem-tests/databases/mongodb-azure-cosmosdb/script.ts:11:21

   8 
   9 const objectId = '6d795f757365725f69643030'
  10 
→ 11 await client.user.create(
  Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: RawDatabaseError { code: "unknown", message: "Command failed (17276): Use of undefined variable: REMOVE)" } })
    at RequestHandler.handleRequestError (/home/runner/work/ecosystem-tests/ecosystem-tests/databases/mongodb-azure-cosmosdb/node_modules/@prisma/client/runtime/index.js:29631:13)
    at RequestHandler.request (/home/runner/work/ecosystem-tests/ecosystem-tests/databases/mongodb-azure-cosmosdb/node_modules/@prisma/client/runtime/index.js:29609:12)
    at async PrismaClient._request (/home/runner/work/ecosystem-tests/ecosystem-tests/databases/mongodb-azure-cosmosdb/node_modules/@prisma/client/runtime/index.js:30582:16) {
  clientVersion: '4.3.0-dev.81'
}

It seems likely from Flavian’s PR from query-engine/connectors/mongodb-query-connector/src/filter.rs https://github.com/prisma/prisma-engines/pull/3088/files#diff-36ea7d9312307ee40fa5e22d8299b057ec85c8fc46bcf523caa494026c2cdf33R1024-R1032

How to reproduce

Test is here https://github.com/prisma/ecosystem-tests/tree/dev/databases/mongodb-azure-cosmosdb

import { PrismaClient } from '@prisma/client'
const client = new PrismaClient()

async function main() {
  await client.user.deleteMany({})

  const objectId = '6d795f757365725f69643030'

  await client.user.create({
    data: {
      id: objectId,
      email: 'alice@prisma.io',
      name: 'Alice',
    },
  })

  await client.$disconnect()
}

main()
  .catch(async (e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => {
    await client.$disconnect()
  })

Prisma information

datasource db {
  provider = "mongodb"
  url      = env("DATABASE_URL_DB_MONGODB_AZURE_COSMOSDB")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id    String  @id @default(auto()) @map("_id") @db.ObjectId
  email String  @unique
  name  String?
}

model Post {
  id    String @id @map("_id")
  title String
}

Prisma Version

4.3.0-dev.81 and up 4.3.0

janpio commented 2 years ago

Not a bug we care about too much right now as Azure CosmosDB is not one of our officially supported databases.

Just shows again that The MongoDB API of CosmosDB is unfortunately not as compatible to MongoDB as they claim 😢 (Per docs $$REMOVE should be available since 3.6: https://docs.microsoft.com/en-us/azure/cosmos-db/mongodb/feature-support-36)

Change that caused this: https://github.com/prisma/prisma-engines/pull/3088/files

Leads to queries like this:

prisma:query db.User.aggregate([ { $match: { $expr: { $and: [ { $and: [ { $eq: [ "$_id", ObjectId("6d795f757365725f69643030"), ], }, { $ne: [ "$_id", "$$REMOVE", ], }, ], }, ], }, }, }, { $project: { _id: 1, email: 1, name: 1, }, }, ])

Jolg42 commented 2 years ago

Note We were running on a 4.0 CosmosDB database. Since we only support MongoDB 4.2+, see https://www.prisma.io/docs/reference/database-reference/supported-databases I upgraded the CosmosDB settings to 4.2 https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/upgrade-version But our CosmosDB test still fails with the same error https://github.com/prisma/ecosystem-tests/actions/runs/3335397850/jobs/5520029210

I created a support ticket on Azure

Jolg42 commented 2 years ago

I got news from support

As per the update we got from our product team, the $$REMOVE variable is not supported in the $match stage of the aggregation pipeline. And also, they don't have any plans on implementing this feature in their immediate roadmap.

lonffreytu commented 1 year ago

Note We were running on a 4.0 CosmosDB database. Since we only support MongoDB 4.2+, see https://www.prisma.io/docs/reference/database-reference/supported-databases I upgraded the CosmosDB settings to 4.2 https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/upgrade-version But our CosmosDB test still fails with the same error https://github.com/prisma/ecosystem-tests/actions/runs/3335397850/jobs/5520029210

I created a support ticket on Azure

same issue. they still haven't fixed it

mazer-rakham commented 1 year ago

Having this issue ATM, any update? Workaround? Thank you!

rac146 commented 1 year ago

are there any workarounds for this issue?

janpio commented 1 year ago

No, unfortunately Azure CosmosDB for MongoDB does not support $$REMOVE - but Prisma uses $$REMOVE to run the queries it needs. As long as that is the state, there is no solution for this problem.

itpropro commented 3 months ago

There are a lot of limitations with MongoDB on RU tier, which will probably never be supported by Prisma or another ORM, as it is not wire compatible with MongoDB API. The only thing you can try would be to use the vCore MongoDB tier, as that one is actual MongoDB under the hood and more comparable to Atlas. If you need serverless, you would be better off using the NoSQL API, as there is a real chance to get support by Prisma (#2713).

prisma / prisma