Securely Caching Sensitive Query Results Locally

💬 Queries and Resources
1

Goal:
I want to cache query results locally to avoid repeated database calls on each page load. For example, I have a contacts table commonly used as dropdown selections in multiple apps. Currently, I'm running this query on every page load:

SELECT * FROM contacts

For non-sensitive data, I've been using localStorage with a timestamp to cache and refresh data when expired. However, I don't want to store sensitive data (like all our contacts) in localStorage.

This query is placed within a header module that's included on every page in the app, resulting in repeated database queries each time the page loads.

Question:
What's the recommended and secure method to locally cache sensitive query results without querying the database on every page load? The "Cache query" toggle in the Advanced tab appears to be server-side caching, so the query still executes each time.

2

Hi. The cache query result option does store/cache the results in your browser's cache (locally).

image
image1002×344 11.4 KB

3

I do not believe this is true, the documentation shows that it is a server side cache ment to reduce resource utilization but it is not designed to speed up apps locally.

I have cache enabled on a number of queries and they still run every time and have roughly the same load time in the debug timeline.

4

@kyleteal My knowledge was based on the concept of how cache headers work. It seems Retool is storing its own cache in their servers.

In that case here's what you can do (assume cache duration of 1 min):

  1. Add a onSuccess event handler to the query to your database, and set local storage to cache the results of the query locally. Also, set the run behavior to manual.

    image
    image930×1184 71.4 KB

    image
    image1042×310 21.7 KB

  2. Under query settings -> Advanced section, run the query every 1 min (this will allow you to update the local storage with new data every minute). With that in place, you will also add a page load delay.

    image
    image1004×500 12.6 KB

  3. Create a JS query which sets the local cache if it doesn't exist. Also, enable the setting "Run this query on page load".

    image
    image1004×428 28.4 KB

  4. At last, in your table's data source, use {{ localStorage.values.table1_cache }}.

5

I appreciate your reply's and help on this, what you are suggesting with localStorage is what I am currently doing for non-sensitive data like categories, and other drop-down.

The issue is that localStorage, from my understanding, is not a good place to store sensitive data (like all of our companies and contacts data). So I am wondering if there is a way to store data in memory or some other secure method.

6

Hey @kyleteal - thanks for reaching out. I can confirm that Retool's caching is done on our backend servers.

In this case, your options for securely caching data on the client itself are pretty limited. I'd probably recommend using the Crypto API or something similar to encrypt the data before putting it in localStorage. I can't find a comprehensive guide on the topic but am happy to answer any additional questions you might have about such an implementation!

1 Like