Whats the best way to save changes (from a table) to a mongoDB?

Hi,

I am new to retool and try to build my first app here. My data is inside a Azure CosmosDB (MongoDB API).

That is the structure of the stored json documents:

{
  "_id": {
    "$oid": "XXXXXXXX"
  },
  "relatedOe": {
    "nameOE1": {
      "name": "company1",
      "sevDeskContactId": "XXXXX",
      "userCount": 23,
      "lastUpdate": "2023-10-31 07:20:13",
      "webinarFlatrate": false,
      "createInvoice": true,
      "dateOfActivePayment": "2023-05-30 00:00:00"
    },
    "nameOE2": {
      "name": "company2",
      "sevDeskContactId": "XXXXXXX",
      "userCount": 54,
      "lastUpdate": "2023-10-31 07:20:12",
      "webinarFlatrate": false,
      "createInvoice": true,
      "dateOfActivePayment": "2023-05-30 00:00:00"
    },
    "nameOE3": {
      "name": "company3",
      "sevDeskContactId": "XXXXXX",
      "userCount": 23,
      "lastUpdate": "2023-10-31 07:20:12",
      "webinarFlatrate": false,
      "createInvoice": true,
      "dateOfActivePayment": "2023-10-01 00:00:00",
      "useSEPA": true,
      "sendInvoice": true,
      "setupIntent": "123123123123123123123123"
    }
  },
  "name": "Big Holding",
  "aggregatedInvoice": false,
  "dateOfActivePayment": "2023-05-30 00:00:00",
  "createInvoice": true,
  "priceModel": "iouaosjdlksjdlkjajsd",
  "priceKey": "12314324234",
  "webinarPriceKey": "123123123",
  "aggregatedUserCount": 100,
  "id": 79,
  "dateCreated": "2023-07-16 18:23:18"
}

I visualize the data in a normal (new) table (objects in relatedOE are not shown in the table) and some columns there are editable. So I was looking for a way to store updates that have been done to the editable columns (relatedOE is not part of it as this is handled in a detailed view.

So the best way That I could find (using Chat GPT) is following:

  • JavaScript to iterade ofer changes in "table1.changesetArray" and calls a MongoDB query to update the documents
  • Resource query MongoDB to update every single object

Here is the code...

JavaScript snippet:

let changesetArray = table1.changesetArray;

changesetArray.forEach(change => {
    let updateFields = {};
    for (let key in change) {
        if (change.hasOwnProperty(key) && key !== '_id') {
            updateFields[key] = change[key];
        }
    }

    // Trigger the update query
    updateCompany.trigger({
      additionalScope: {
        idToUpdate: change._id,
        updateFields: updateFields
      }
    });
});
table1.refresh()

Mongo DB Query:

I tested it and it is working BUT as the MongoDB query is sometime called several times and there is some lag of the Database, it actually feels like not a good solution.

I would be very happy if more expirienced community members here tell me about the best way to handle changes for a mongoDB in retool? Unfrotunately I could not find any Best Practices online.

1 Like

Hey @leonK-DI! Glad to hear it's working at least sometimes, but less glad to hear it's not working consistently.

I assume you've seen this post about our recommended updateMany workaround:

https://community.retool.com/t/mongodb-update-many-example/8889/2

When you say it's called several times, do you mean for the same record? Are there any specific error messages thrown in the debug console?

https://docs.retool.com/apps/management/concepts/debug-tools#console-tab