Support Azure Cosmos DB API for MongoDB

[janpio]

Azure Cosmos DB is a NoSQL database that also provides a MongoDB wire protocol compatible API. It is sometimes used as a drop in replacement for MongoDB, and is promoted by Microsoft/Azure as such, e.g. with a video series.

https://azure.microsoft.com/en-us/services/cosmos-db/

The Azure Cosmos DB API for MongoDB provides you with the ability to interact with Azure Cosmos DB as if it were a MongoDB database, without having to manage the database infrastructure itself. This means that nearly all your favorite MongoDB tooling, SDKs, and applications will continue to work.

https://docs.microsoft.com/en-us/azure/cosmos-db/mongodb/mongodb-introduction#how-the-api-works

Currently known problems

~~The MongoDB API of CosmosDB supports MongoDB 4.0. Prisma only supports MongoDB 4.2+ which means there can be problems when newer features are used under the hood.~~

Additionally, Azure Cosmos DB API for MongoDB 4.0 only supports part of the complete API and has some additional features and limitations compared to "plain" MongoDB.

• Prisma uses multi-document transactions by default. CosmosDB MongoDB API unfortunately does not support these, which means some actions that Prisma executes do not work: https://github.com/prisma/prisma/issues/9566
• Interacting with databases and collections that do not exist yet, behaves different on CosmoDB (error message) than on MongoDB: https://github.com/prisma/prisma/issues/11854#issuecomment-1043145873 (Internal: https://prisma-company.slack.com/archives/C013RMLGE72/p1645025348768419)
• ...

[janpio]

Microsoft/Azure recently released support for the MongoDB 4.2 API: https://azure.microsoft.com/da-dk/updates/general-availability-azure-cosmos-db-api-for-mongodb-supports-version-42-2/?cdn=disable https://devblogs.microsoft.com/cosmosdb/azure-cosmos-db-api-for-mongodb-v4-2-is-here-three-reasons-to-upgrade-now/

Unfortunately the Azure Cosmos DB emulator that we require for local tests did not get an update yet (https://docs.microsoft.com/en-us/azure/cosmos-db/local-emulator-release-notes), and it might be quite some while before that happens. We can not run our tests online as it creates hundreds of thousands of databases and collection, and send even more queries.

[Kondamon]

When a database is created without sharded collection (limit of 20 GB) on Azure Cosmos DB with MongoDB 4.2 API, you receive this error, when an object is created:

  Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: RawDatabaseError { code: "unknown", message: "Command failed (OperationNotSupportedInTransaction): Command insert failed : can not create collection in a multi-document transaction)" } })
    at Object.request (/Users/user/node_modules/@prisma/client/runtime/index.js:45633:15)

[ytsruh]

Seconded. I get the exact same issue as @Kondamon . Ive tried provisioned & serverless CosmosDB without success including using the 4.2 API

[janpio]

Please tell Microsoft to release a way to locally test Cosmos DB with MongoDB 4.2 API - until that does not exist, we can not investigate further. Sorry.

[itpropro]

Any updates here @janpio? Have some projects coming up that all use CosmosDB as their MongoDB service, I would really like to use Prisma there. As support for MongoDB 4.2 is already in the CosmosDB emulator since over 2 month, will there be a fix soon?

EDIT: The documentation for all implementation limitations of the current MongoDB version is also always up to date, as soon as a new version launches. It states the following regarding transactions: "Multi-document transactions are supported within an unsharded collection. Multi-document transactions are not supported across collections or in sharded collections. The timeout for transactions is a fixed 5 seconds." (Updated April 2022). https://docs.microsoft.com/en-us/azure/cosmos-db/mongodb/feature-support-42#transactions

[janpio]

As support for MongoDB 4.2 is already in the CosmosDB emulator since over 2 month, will there be a fix soon?

I was not aware of that. When I last checked on May 12th the release notes had not been updated, and then a backdated updated was done a few days later https://github.com/MicrosoftDocs/azure-docs/blame/main/articles/cosmos-db/local-emulator-release-notes.md#L25 This should now enable us to run tests again.

"Multi-document transactions are not supported across collections or in sharded collections."

This unfortunately does not make me hopeful and could be the reason for the errors @ytsruh and @Kondamon reported. Such limitations of not really supporting the same as the system something is supposed to be compatible with makes this really hard for us unfortunately 😞

[itpropro]

Hey @janpio, I did some tests based on the limitations MS gave us here and it's really hard to work with MongoDB transactions if they are limited to one collection only. Just in case you are testing, here are the common errors, especially "2 Transaction is not active" is important as it is not only thrown when the transaction takes longer than 5 seconds, but also if the transaction spans more than one collection. Do you think it is even realistic that there will be a fix, as for me it looks like you have to implement custom code that detects if it's Cosmos Mongo and then limit/split transaction so they only target a single collection at a time. And we don't know if there are not more limitations after this one has been fixed.

[janpio]

Thanks for the pointer, that error message for that case is indeed a bit ... not obvious 🤷.

Our priority for Cosmos DB support via MongoDB API is definitely not super high. These limitations and also the delayed release of the emulator make it really hard to just quickly jump in here and figure out what is going on, and see if there even is something to fix that does not require to completely change our MongoDB implementation.

Maybe MongoDB API 4.4 support will be better? That would be neat.

[itpropro]

I think even MongoDB API 4.4 or 5.0 wouldn't change this, as it sounds more like a platform implementation. It is a multi-model database with an WireProtocol MongoDB API and not a native Mongo in the end... For my part, I am going to assume that it will not work in the short term with Prisma, so I am going to try CosmosDB SQL API (do you have any experiences there?) or directly going to use Azure SQL. Thanks to Prisma, it doesn't really matter which database to use :)

[janpio]

I don't disagree unfortunately. I would have really liked if this had worked. I have no direct experience with these - please let us know how this goes.

[bradib0y]

To me it feels like there is a demand for Cosmos DB connector from Prisma, which could support SQL API, Mongo API implementations of CosmosDB. Is there something like that in the pipe? I would love to contribute, because I know many great engineers and companies who use CosmosDB, and also many who use Prisma, and a significant portion of these are contemplating the undertaking of integrating these technologies. I would also say that CosmosDB is worth betting on: it is a highly capable database technology with multi-region writes supporting globally distributed services with low latency... etc. (Just looked up quickly last years investor report, Microsoft stated that CosmosDB data volume and transactions doubled compared to last year, which signifies similar popularity to MongoDB Atlas)

[janpio]

We would need an issue for the CosmosDB SQL API support, this one here only covers the MongoDB API. Update: Actually exists here: https://github.com/prisma/prisma/issues/2713#issue-636431388

And for the MongoDB API we just have the problem that CosmosDB does not seem to support the full actual MongoDB API surface, which we unfortunately make use of to guarantee optimal results. See details in https://github.com/prisma/prisma/issues/11921#issuecomment-1173103378 (that of course someone could/should still experimentally confirm by just trying to use Prisma with the current API).

[reachej]

After troubleshooting, its collection/prisma model capitalization matching needed. I found:

When using Cosmos DB running MongDB 4.2.0 Community serverless, you have to make sure the Database and Collection are already created and CAPITALIZATION matters / your Prisma model has to match collection capitalization exactly.

If your model is labeled "ticket" and you have a collection called "Ticket", you'll have errors like this appear when trying to write to a COSMOS DB 4.2 database:
| ConnectorError(ConnectorError { user_facing_error: None, kind: RawDatabaseError { code: "unknown", message: "Command failed (OperationNotSupportedInTransaction): Command insert failed : can not create collection in a multi-document transaction)" } })

*cosmos db 3.6 serverless has the below error when trying to write to it:

  Error in connector: Database error. error code: unknown, error message: Transactions are not supported by this deployment
    at cb (/node_modules/@prisma/client/runtime/index.js:34804:17)
    at processTicksAndRejections (internal/process/task_queues.js:97:5) {
  clientVersion: '3.xx.0'
}

[Kondamon]

@reachej Thanks for sharing this! After manually creating the database and collection does Azure Cosmos DB 4.2 work without any issues??

[reachej]

@reachej Thanks for sharing this! After manually creating the database and collection does Azure Cosmos DB 4.2 work without any issues??

You're welcome! Glad I could help. So far no problems, but I've done very little other than make sure it seems to work. I'll let you know if I find anything else worth mentioning.

[janpio]

Thanks for this info, this seems to limit the error to the creation of models/collections. Maybe we will find the time to adapt our tests in some way - or udnerstand what part of our code causes this issue in Azure Cosmos DB MongoDB context and we can change it.

[reachej]

Thanks for this info, this seems to limit the error to the creation of models/collections. Maybe we will find the time to adapt our tests in some way - or udnerstand what part of our code causes this issue in Azure Cosmost DB MongoDB context and we can change it.

With Atlas MongoDB 5.0.10 Enterprise, everything works, so I wouldn't say the problem is on the Prisma side. Once Cosmos DB allows the use of the 5.0 API, Prisma I believe will work fine without anything needing to be done.

In the mean time, to help make things work as expected by Prisma users, you could adopt/change your code to do something like this upon the first connection:

check if the connection string is to Cosmos DB If it is cosmos:

Have it check if the API version is 4.2

If it is 4.2 get a list of the collections during the first database connection/read , and log it have Prisma create any collection / schema model that doesn't already exist as a collection in the database connected to and log it

If it is greater than 5.0 (should get there eventually) do nothing / things will probably work normally

If it is less than 4.2 log a message saying Prisma only supports 4.2 (I didn't test 4.0, but I know 3.6 only works with reads)

[Kondamon]

According to MS support the RU-based implementation that supports cross-collection and cross-shard multi-document transactions for MongoDB is one year away, at minimum. So, maybe at the end of 2023. However, there is already a private preview for this feature and updates when it will be publicly available is announced here.

[itpropro]

Thanks for this info, this seems to limit the error to the creation of models/collections. Maybe we will find the time to adapt our tests in some way - or udnerstand what part of our code causes this issue in Azure Cosmost DB MongoDB context and we can change it.

With Atlas MongoDB 5.0.10 Enterprise, everything works, so I wouldn't say the problem is on the Prisma side. Once Cosmos DB allows the use of the 5.0 API, Prisma I believe will work fine without anything needing to be done.

In the mean time, to help make things work as expected by Prisma users, you could adopt/change your code to do something like this upon the first connection:

check if the connection string is to Cosmos DB If it is cosmos:

Have it check if the API version is 4.2

If it is 4.2 get a list of the collections during the first database connection/read , and log it have Prisma create any collection / schema model that doesn't already exist as a collection in the database connected to and log it

If it is greater than 5.0 (should get there eventually) do nothing / things will probably work normally

If it is less than 4.2 log a message saying Prisma only supports 4.2 (I didn't test 4.0, but I know 3.6 only works with reads)

The 5.0 API will not change anything for the current problem, as long as the base implementation doesn't change. Don't expect it to work with a newer API version, as it probably won't. You will have to switch to another service tier like @Kondamon already mentioned and if that will be available in the next 1-2 years knows only MS.

[max-programming]

Is this still being worked on?

[janpio]

No, as mentioned above there is nothing Prisma can do at the moment as we can not support older MongoDB versions and we need support for the full feature set of the newer version as we use parts of that. Without that, Cosmos DB via their MongoDB API will not be possible in Prisma.

[itpropro]

Maybe we can close the issue @janpio until the mentioned new Cosmos Service is available. I think that would avoid confusion for people stumbling upon this problem.

[janpio]

Issues are how we track what people want, and what is needed to get it done. This would be the place where, when Azure releases any updates, you or whoever notices could come and tell us about it so we can put this on our roadmap again (or at least investigate).

[tastafur]

@janpio https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/feature-support-42 mongodb 4.2 is already supported and they are on their way to support mongo 5.0

[janpio]

Unfortunately, their support for 4.2 is only partial and they are not planning on fixing the incompatibilities: https://github.com/prisma/prisma/issues/15083#issuecomment-1293183157

[gstockwell]

We are working very closely with Microsoft at fairly high levels on a number of projects and had initially used Prisma/MongoDB for the initial development. We need to move to CosmosDB/MongoDB in order to ensure all data is integrated with our environment and the connection string to CosmosDB is not compatible with Prisma. We are at the point of having to drop Prisma if we can't resolve the issue. Any assistance would be appreciated because we love Prisma!

[reachej]

We are working very closely with Microsoft at fairly high levels on a number of projects and had initially used Prisma/MongoDB for the initial development. We need to move to CosmosDB/MongoDB in order to ensure all data is integrated with our environment and the connection string to CosmosDB is not compatible with Prisma. We are at the point of having to drop Prisma if we can't resolve the issue. Any assistance would be appreciated because we love Prisma!

I’ve used prisma via redwoodjs with both Mongodb and CosmosDB and didn’t have any connection string problems (although I haven’t done this for about a year now). If it might help, the problems I’ve run into using cosmosdb with the 4.2 mongodb api included:

1. Error messages which are hard/impossible to tie the cause of the problem to unless you’ve already figured out the x causes y error message.

   2. CosmosDB used to support collections with names that included brackets [] but about 3 months ago this was changed without warning and it broke things for me. I had to modify and recompile mongodb entities to use BINARY_CHUNKS instead of [BINARY_CHUNKS] for the file entity collection name to get things working again.

   3. The latest mongodb driver didn’t work with the 4.2 api, I have to use the older driver to keep things working for now. I believe I’ll be able to use the latest driver once the 5.0 api is available (I could try the 5.0 but I haven’t yet https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/vcore/quickstart-portal).

   4. If you have the camelcase convention activated it can cause you problems, the collections in CosmosDB had to be already created and the exact same capitalization as your models in order to work.

[janpio]

Our last update from Microsoft on this is from November 2022: https://github.com/prisma/prisma/issues/15083#issuecomment-1326419863 To summarize, they do not support part of the real MongoDB API ($$REMOVE) and have no plans to do so. Prisma's MongoDB support relies on that API, so we unfortunately we can not do anything besides fully rewriting our implementation. And that is just not possible right now as we do not have the capacity to do so. Sorry.

[gstockwell]

Thanks, I will raise the issue with Msft and see if any headway can be made. You never know!

[janpio]

Microsoft has a new offering: Azure Cosmos DB for MongoDB vCore

• https://devblogs.microsoft.com/cosmosdb/azure-cosmos-db-for-mongodb-vcore-helps-developers-build-intelligent-apps-in-the-cloud/
• https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/vcore/introduction
• https://www.infoq.com/news/2023/03/azure-cosmosdb-mongodb-vcore/

It seems to be more like MongoDB Atlas than their existing Azure Cosmos DB for MongoDB Request Unit (RU), and might be more compatible with Prisma. Let's see.

[janpio]

Update: Introspection and Migration work well for the vCore based database, but queries still fail because of the missing $$REMOVE support: https://github.com/prisma/prisma/issues/15083 (As documented https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/vcore/compatibility#system-variables)

[Joeghanoe]

Would love to see this feature as a part of prisma! :)

[Mingyang-Li]

Looking forward to seeing this feature comes to life ;)

[petr-stupka]

Hi @janpio, last month Azure Cosmos DB for MongoDB got a v5.0 and v6.0 API support

According to the docs https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/feature-support-60#system-variables $$REMOVE is supported (reality may differ)

Please be aware the API reference is in the RU version SKU (not the vCore, although it may be the same)

Will be possible to test it?

Than you

https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/change-log

[janpio]

Cool!

RU SKU is definitely different than vCore: https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/choose-model I could not find any vCore documentation that would indicate if $$REMOVE is implemented or not there 😐

The best way to test for you is probably just to create a minimal Prisma project and run it against the connection string.

[petr-stupka]

In the link i posted https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/feature-support-60#system-variables. The downside here is they also claim to have support in version 4.2, however according the Prisma test, it was not the case

[janpio]

(Sorry, had a typo above - I meant vCore. But good that you are pointing out the inconsistency - I guess we should take screenshots for the future.)