Support Azure Cosmos for NoSQL, SQL API

[jferrettiboke]

It would be awesome to have support for Azure Cosmos DB.

Read this for more info and the benefits of using Azure Cosmos DB.

Discussion: #2704

[mavilein]

@kylehayes @jferrettiboke: Thanks for reaching out and Cosmos is definitely on our mind! I have 2 questions for you that would help us greatly in setting priorities:

1. How would you rank the various flavours of Cosmos in regards to popularity from your perspective?
2. What's the more prominent choice nowadays for using a SQL database on Azure? Azure SQL or Cosmos' SQL flavour?

[homerjam]

I thought Cosmos was multi model?

Whichever flavour supports serverless would get my vote!

[kylehayes]

That’s a great question. Of the teams I know using it, it seems split down the middle between the SQL and Mongo flavor. It does support other models as well like a graph db but not sure how popular those are. The SQL one is more well supported in the Azure ecosystem but the Mongo flavor I can see people choosing to be Mongo compatible. My vote would be SQL. This is relatively moot for our team as we are currently converting from Cosmos to Postgres and switching our custom ORM over to Prisma.

[nooikko]

My company has been using Cosmos as our main database for a bit now. We worked directly with Microsoft for configuration. Serverless is a terrible idea to support. It is in "Preview" and has massive restrictions around it's scalability and no planned release date.

The better one to support would be Autoscale as it has most of the features (though not all) of Serverless, much more support from MS, and has a stable release.

MS also recommends using the SQL version of the API as the mongo one is significantly less featureful. Though it was recently updated to version 4.0

[ShanonJackson]

SQL API is considered the "main" CosmoDB API; Unfortunately its also the API with the most restrictions I.E no createMany,

CosmoDB does not allow any kind of lookups based on the result of a sub query or cross document joins this restriction is quite painful to have to work around.

I'd love Prisma for the "main" CosmoDB SQL API is this is the API we use on our IoT project, yes prisma will essentially launch defacto support for the MongoDB CosmoDB API Soon, however people on the SQL API can't migrate to the MongoDB API in a non-trivial way, the containers would have to be re-created from scratch and data migrated over aswell as "id" fields mapped to their CosmoDB equivalent.

[ShanonJackson]

Just decided to build this myself, already most of the way through will post a npm link here when its done but this is the high level plan which could technically be replicated out to other databases.

• Define your CosmosDB schema in schema.prisma using provider "mongodb" (note we're using SQL api in real world).
• Define relationships between tables even though cosmos is not a relational database and use "type" syntax of the mongodb provider for embedded documents
• use https://www.npmjs.com/package/prisma-generator-cosmos which I wrote to "Extract" those relationships out of the schema.prisma file no docs yet until its done
• use the PrismaClient "type" but actually just implement the whole object -> query translation layer from scratch, this means essentially that Prisma is only providing the "types"
• for "includes" will involve multiple database round trips as it would without prisma anyway.
• https://imgur.com/a/uhZdYRi

The gif is a proof of concept ignore "distinct" not being added to the query that's on the way but nested and/or's work

[guerrato]

If you let me express my opinion, I need support for SQL as well. The project I am working on is a very good example of this necessity. For now, I am using MongoDB, however, we're are keen to step out from it to another database, on Cosmos the issue would be kind of solved, but for the code itself I still wish using Prisma as my repository layer, which makes life easier to migrate from DBMS, given I just need to change the services that uses it and easily migrate the DBs.
The reason why I bet in SQL for this is due to the SQL being a "universal" DB language to get information, being easily to be updated and more flexible.
I hope to have this feature released soon!

[janpio]

Can someone provide current documentation links for the Cosmos DB, SQL API?
(Azure Cosmos DB for PostgreSQL seems to be something different, and based on Citus)

[petr-stupka]

@janpio it's called Azure Cosmos DB for NoSQL https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/

[janpio]

Thank you. That naming really takes some effort to wrap your head around. But indeed:

NoSQL accounts provide support for querying items using the Structured Query Language (SQL) syntax, one of the most familiar and popular query languages to query JSON objects.

https://learn.microsoft.com/en-us/azure/cosmos-db/choose-api#coresql-api

And then: https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/

[petr-stupka]

You can also use free tier for the dev purposes > https://learn.microsoft.com/en-us/azure/cosmos-db/free-tier (one per subscription) or serverless which is charging per usage (without monthly fee)