Support multiple collections in stored procedures
Currently CosmosDB Stored procedures only allow access to a SINGLE collection (because they seem to be scoped to collections rather than to the database), via getContext() :
var collection = getContext().getCollection();
But any "useful" / "real-life" application using ComosDB will typically have to interact with more than one collection. For example, it is currently IMPOSSIBLE to insert documents into two distinct collections within a database using a single stored procedure -- a VERY SERIOUS drawback / limitation.
I would propose that stored procedures be scoped to the database level and then we would be allowed to do something like:
var widgetscollection = getContext().getCollection( "widgets" );
var gadgetscollection = getContext().getCollection( "gadgets" );
A single stored procedure would have access to any collection within the database. Also, operations on documents within multiple collections would be a single atomic transaction.
Updating this to declined. As a partitioned data store, stored procedures are scoped to a partition and cannot span beyond that.
To take advantage of a partitioned data store, data must be modeled in such a way it can infinitely scale. This feature bounds independent compute resources (partitions) together so cannot scale predictably.
Zahir Rafi commented
The Hybrid data modelling section in the below link reads otherwise. Please clarify.
I assume multi document transaction refers to documents in different collections as I have seen multi-item is used to refer items in same collection.
Excerpt from the above link
"The ability to have a model with pre-calculated fields is made possible because Azure Cosmos DB supports multi-document transactions. Many NoSQL stores cannot do transactions across documents and therefore advocate design decisions, such as "always embed everything", due to this limitation. With Azure Cosmos DB, you can use server-side triggers, or stored procedures, that insert books and update authors all within an ACID transaction. Now you don't have to embed everything into one document just to be sure that your data remains consistent."
Jim Lynch commented
Hello, I am a new developer to CosmosDB (although I have used other document-based databases), and I am quite disturbed that this concept of transactional writes does not exist for Cosmos as is does for many other databases...
Sergey points out that you can use some pre or post trigger or change feed handler to update the other collection(s), but the writes fail at any point then your data between collections becomes out of sync meaning your data somewhere will be incorrect.
Andrea Gariboldi commented
AWS DynamoDB also supports it.
Folks, it's not a limitation, it's a distributed storage, design your data model properly with eventual consistency in mind. Use Change Feed and trigger cascade changes with Azure Functions.
Hamdy Ghanem commented
Don't believe this is just a limitation, there is no even a workaround to achieve this
The link is dead...
I just noticed this limitation, and it SUCKS. As mentioned in the description, how can anyone take CosmosDB seriously if we can't even update multiple collections in a single transaction? And this is NOT on your roadmap. Very disappointed indeed.
I am using cosmo db for my application. How can I query data from multiple collections, perform operations on it and then save it in another collection. If stored procedures are at collection level, what is the work around to do this type of stuff. Should I switch to other NoSql databases like MongoDB or Cassandra
Alexander Arvei Yngling commented
Google AppEngine Datastore supports cross-entity-group transactions for up to 25 entity groups (cf. https://cloud.google.com/appengine/docs/standard/java/datastore/transactions ), and is a feature I have used heavily, but never with more than 5 groups, and usually only 2.
Being able to do a cross-partition transaction with only 2 partitions would be extremely useful for me, 5 would be even better.
Takekazu Omi commented
I want a transaction of the document that has been saved in multiple collections.
Such as global transaction.
It does not matter even if there is a limit to the consistency of this model.