Best way to cache/serve a paginated API query ran by JS code to users

Firstly...I love retool. Great work to the retool team.

I have a large Airtable database. As their API only returns 100 data rows at a time, I have JS Code Query that makes repeated API calls and saves all the 100 record iterations into an array. This data rarely changes and the query doesn't need to be updated often at all.

I am trying to cache this query so when end users load the app it doesn't make the tens of API calls.

I thought setting the JS Query advance settings to cache the results for a long period of time and to run the query periodically would do this (and not set it to run when page loads). But my JS query doesn't seem to ever run if I open the app as an end user. Cache for all users is not disabled in my organization's settings. I believe this is because JS Queries might only run when manually triggered (unless set to page load). Though, I could have made a mistake somewhere in my tests or not understand some of the above. If I set the query to run on page load, it successfully runs and the end user can see data.

What is the best way to achieve what I am trying to do? Should I put the query into local_storage or a variable or something of that sort? Should I use a workflow to have this query running behind the scenes so when users open the app they get data. Currently, I seem to only be able to show the end user data by running my query when the page loads and the end user waiting through all the API calls.

Thanks for everyone's time!


so the cache results for query thing just means that after it runs for the 1st time (either from page load, or from manual trigger) it will return the same thing for x amount of time. if the query is triggered and if x amount of time has passed, it will retrieve new data and start the timer over. so it won't automatically update and fetch new results after the cache times out without running the query again.

you could do something like this though:

here it runs once on page load, then it starts a timer to invalidate the cache after 300 seconds. after 301 seconds the cache has expired so the query is ran again to update the cache/retrieve new value.

Got ya, Thanks for that! It did occur to me after I posted that this likely isn't anything just running on background periodically waiting for my users since its just a JS code query.

I guess what I realize I am trying to 'learn' the best way to do is how best to have my query stored in retool such that the user doesn't have to wait for tons of API calls when they open the app. I know I could park data in the Retool Database and that might be faster than API calls, what other options exist (if any)? Perhaps none without the end user opening the app and triggering queries/code? Looks like perhaps variables do this? I'll look around but appreciate any suggestions. Thanks, I'm still learning lots of the retool options.

edit: I gather this might be my answer: " Variables are reset each time your app is loaded. We are currently rolling out support for persisting values across browser sessions. If this isn't yet available for you, you must write the values to a database or localStorage."

another edit: Ok, I think I'm finding the likely answer which I assume is discussed above and in this post (unless there are new options that have emerged):


I think you could look into a workflow to populate the DB or Local Storage for you. Then when the user opens the app it would read from those sources instead of chaining a bunch of the API calls together. The workflows run on a schedule or webhook event so you could figure out the best way to keep the stored info up to date and have on-demand features to trigger the refresh if a user needs to.

Thanks. Sounds like what I'm looking for. I'll play around, I hadn't dove into workflows yet. Appreciate the help.

1 Like