3

As there is no functionality of foreign Key in Firestore like that of MYSQL, so I am not able to replicate one of my important functionality that is to update a file in one place and it will reflect in every place. Also, Firebase has no functionality to update all the document's specific filed at once.

There are already these kinds of questions but I could not get my solution. Suppose I have a million documents containing a filed which is the density of a material. Later on, I found that my density value was wrong so how to update that value in all documents efficiently. Also, I do not want to use server/admin SDK.

Alex Mamo
  • 130,605
  • 17
  • 163
  • 193
Rafa
  • 153
  • 1
  • 9

2 Answers2

2

If you need to change the contents of 1 million documents, then you will need to query for those 1 million documents, iterate the results, then update each of those 1 million documents individually.

There is no equivalent of a sql "update where" statement that updates multiple documents in one query. It requires one update per document.

Doug Stevenson
  • 297,357
  • 32
  • 422
  • 441
  • can you elaborate with code in context to android? I don't want to use Admin SDK. also how does this effect, cost and efficiency? Because in sql same thing you can do with update just at one place on the other hand you have to update million document. – Rafa Aug 29 '20 at 08:08
  • You have no obligation to use the admin SDK. The strategy for updating doesn't change at all for any other platform. As I said, FIrestore doesn't have an "update where" command that can update multiple documents in one query. – Doug Stevenson Aug 29 '20 at 15:58
0

If don't want to use the Admin SDK, then the option that you have is to update the value of your densityMaterial property on the client, which might not be the best solution. However, if you can divide the update operation in smaller chunks, you might succeed.

If you are using a POJO class to map each document, then you might be interested in my answer from the following post:

How to update one field from all documents using POJO in Firestore?

And if you are not using a POJO class, please check my answer from the following post:

Firestore firebase Android search and update query

Regarding the cost, you'll be billed with one write operation for every document that is updated. If all 1 MIL documents will be updated, then you'll be billed with 1 MIL write operations.

Edit:

Suppose I have a million documents containing a filed which is the density of a material. Later on, I found that my density value was wrong so how to update that value in all documents efficiently.

If all of those 1 MIL documents contain a property called densityMaterial, that holds the exact same value, it doesn't make any sense to store that property within each document. You can create a single document that contains that particular value, and in each and every document of those 1 MIL, simply add only a reference to that document. A DocumentReference is a supported data-type. Now, if you need to change that value, it will incur only a single document write.

However, if you have different values for the densityMaterial property and all of them are wrong, then you don't have a problem with the database, you have a problem with the mechanism/people that are adding data. It's not a matter of a database problem if you have added 1 MIL incorrect documents.

Why not chose MySQL?

MySQL cannot scale in the way Cloud Firestore does. Firestore simply scales massively.

Can I avoid this problem anyhow?

Yes, you can buy using a single document for such details.

Alex Mamo
  • 130,605
  • 17
  • 163
  • 193
  • As you said you i would be billed for 1MN write operations. So Why one would choose Firestore if a single update costs this much? Is there any future plan for this specific issue? – Rafa Aug 29 '20 at 09:02
  • If you update 1 MIL documents, that's the cost that you have to pay. If you would have updated a similar number of objects within a SQL database, I'm afraid that will be something similar in terms of costs. Yes, check [Firestore pricing](https://firebase.google.com/docs/firestore/pricing). – Alex Mamo Aug 29 '20 at 09:27
  • But in this case in MYSQL i need to update thing just at one place and it will be automatically propagated through foreign key reference at every other place, am i right? – Rafa Aug 29 '20 at 10:04
  • The concepts in the NoSQL world are different than the concepts in SQL. If you think that Cloud Firestore is expensive for your app's use case, you might try using [Firebase Realtime Database](https://firebase.google.com/docs/database), even if you are losing some query features that Cloud Firestore has. – Alex Mamo Aug 29 '20 at 10:06
  • I have no specific use case, it's a common scenario where you update some mistake and u expect it should propagated to all other place. In mysql we do this at one place and in Firestore you do it at every place. So how to manage this thing without any efficiency and financial burden.... this is what all i want to ask. – Rafa Aug 29 '20 at 10:20
  • As I already answered, that's the way to do it. There is no "hack" for that. Please note that it doesn't matter if you are using the client or the Admin SDK, the cost will always be the same. NoSQL and SQL, are two totally different concepts. – Alex Mamo Aug 29 '20 at 10:26
  • I still could not find my answer, if this is the only way to update a filed at every place why someone would choose firebase, where one mistake can cause updating million place? Why not chose mysql? Can i avoid this problem anyhow? – Rafa Aug 31 '20 at 15:56
  • @Rafa Please see my updated answer. I hope I was clear enough now this time. Can I help you as well with other information? – Alex Mamo Aug 31 '20 at 16:39
  • one last attempt --> as you said in case we have millions of document referencing a data then just store the reference to that data and not actual data. But data denormalisation is expected in Firestore for faster query. It is a common practice to store same data at many place and not their reference. Again I am giving you an example where there is an e-commerce app in which a Cart can have reference to a product, a reference to a user and many more references. So isn't it a bad practice to store reference and not actual product? And what if i update product or user name? – Rafa Aug 31 '20 at 23:39
  • *It is a common practice to store the same data at many place and not their reference.* Yes, it is. This practice is called denormalization and it's quite common practice when it comes to Cloud Firestore. If you are new to the NoSQL database, for a better understanding, I recommend you see this video, [Denormalization is normal with the Firebase Database](https://www.youtube.com/watch?v=vKqXSZLLnHA). It's for Firebase Realtime Database but the same principles apply to Cloud Firestore. – Alex Mamo Sep 01 '20 at 06:31
  • *So isn't it a bad practice to store reference and not actual product? And what if i update product or user name?* I think that my answer from the this [post](https://stackoverflow.com/questions/63650722/query-on-date-field-in-firestore-not-working/63667839#63667839) will answer all these questions, right? – Alex Mamo Sep 01 '20 at 06:33
  • I know denormalization is common practice in firestore, but this is not my question my question is if i denormalize then I need to update each place. And if there are millions of such instance i need to update at each place. so how to tackle this. what should i prefer? denormalization or just a single document and other place just refer to this document. I mean how to design such problem – Rafa Sep 01 '20 at 16:43
  • Yes, if you denormalize the data, you should update that data in any place it exists, or create a workaround as explained in my answer/edited answer. You should always prefer the solution that is most cheaper and satisfy all your requirements. If a single document that holds that data can solve the problem, then that's the solution to go ahead with. You should always measure the need for heavy updates and try to avoid that using common workarounds. – Alex Mamo Sep 01 '20 at 18:50
  • hi, i was not satisfied with the answer so far. – Rafa Sep 12 '20 at 06:28
  • Please note that what you are looking for is not possible in the way you want. So not getting what you expect, might also be a valid answer, but it's up to you to decide if you accept it or not. – Alex Mamo Sep 12 '20 at 08:21