In our app we cache the database queries. We also invalidate them when a user makes changes to the database. This way the app only runs the query when needed.
However, we now also change the database records outside the app. The app doesn't know the records have changed and it needs to invalidate/clear the cache.
What would be a good approach to get this working again?
+1
We're also trying to figure out a way to do this since one of external API connections is quite slow and we'd love to figure out a way to speed it up.
The only thing I can come up with is creating a database table with records of cache names. Then the second column is a boolean.
Then periodically you retrieve this table from the database in the app. And based on the values you invalidate the caches for those queries. Then you have to set the value to false again otherwise you keep clearing the cache.
The table can be updated from outside the app. You can use a workflow with an endpoint enabled for that for instance. I use AWS lambdas to do a postgresql update query to the retool database usually.
Seems a lot of hassle just for clearing the cache from outside the app. Would've been nice to have the ability to clear it from outside the app.
Anyone got other suggestions?
Hey @Steven_W! Thanks for reaching out.
This is an interesting idea. 
I'll talk to the team about potentially exposing an API endpoint for invalidating a query's cache.
For any orgs running a self-hosted instance, this is technically already possible! It might be a little tricky to configure but having access to the primary instance database unlocks a ton of utility.
Otherwise, the workaround that you've proposed is where my mind went, as well. There's not really a better alternative to polling if you want to trigger an action within an app - certainly not one that's built in, at least. It might be feasible to build a custom component with a websocket connection similar to what @ZeroCodez showed off here. 
That would only work for clients with a live session, though. It might take a combination of the two approaches to get fully functional.
I'm curious if anybody else has suggestions, but will start a conversation internally and let you know here as soon as I have an update!
Well I made it all work except for the part where the cache actually gets invalidated. Somehow I still get cached results after invalidating the query.
It might be caused by this:
I’m not able to push to latest version. So it’s annoying that I can’t check if my code works or not.
Clearing the cache using the three dots on the query window works. But with JavaScript does not.
Did you end up implementing a workaround similar to what you described above? Actually invalidating the cache seems like an important step. 
It's possible that there are other factors at play, but I verified that the issue you linked doesn't seem to be present anymore. I'm able to successfully invalidate the cache for a given query when viewing an older release. I did run into an issue the other day in which module queries couldn't be invalidated from within the parent app, but I don't know if that is relevant to your use case.
Can you share the code that you're currently using to invalidate the query? And have you verified that it's actually running? I definitely want to help you get this figured out!
I haven't been able to double check it since the version I'm working on is not yet published. I'm testing it in the browser. It's retool mobile by the way.
I tried these:
Not sure if I need to use () at the end or not.
Basically the getRows.trigger() is cached. And then I run invalidateCaches.trigger() to invalidate them. Then I run getRows.trigger() again. But the results don't change. You would expect that if you re-run getRows.trigger() that then the new results should appear.
It's not possible to clear a cache but at the same time run getRows() and that the old results get cached?
You do need the parentheses, as invalidateCache is a function to be invoked! Also, if you aren't already doing so, I recommend using the debug console for better insight into whether certain query results are cached or not:
Just for clarity, note that the third line in the above screenshot is the result of directly invalidating the cache through the query menu, not calling invalidateCache().
I think I've mentioned this before, but it's important to keep asynchronicity in mind when doing all of this. Something like the below should definitely work:
await getRows.trigger() // should *not* see this run from cache
await getRows.trigger() // should see this run from cache
await invalidateCaches.trigger()
await getRows.trigger() // should *not* see this run from cache
I’m going to try this asap. I’ll let you know. Thanks for the help and explanation! 
I tried it with no luck. I changed the js query to be able to test it.
Check this out:
This doesn't work. No mention of any invalidation. Just the log that it finished the query (name is invalidateCaches, sorry for that):
Then I try this. All from cache. Await doesn't seem to work:
Then I try this. I invalidate manually via the dots-button on the query.
I try again. As you can see, the first run is not from cache. But the second is. Even though there's a invalidateCache() in between with await.
Did you test with retool mobile?
What is your code?
You are 100% right, @Steven_W! I was not testing with a mobile app and am now able to reproduce the issue. I will talk to our team about a fix!
I’m so glad I’m not going crazy 
Were you able to report the bug/inform the devs ? Just checking. My app is almost finished but one of the things I need to do is optimize and refactor it. Caching is on my list 
I wonder, is there a bug repository where you can follow the status? Would be nice. Something like a change log but for bugs to see what the dev team is working on.
Yep! I've reported the relevant bug but it hasn't yet been picked up. On the topic of visibility into the status of certain bugs, nothing like that currently exists. We've definitely talked about a customer-facing bug tracker and I think something like that is on the roadmap.
