I'm working on a Retool app that needs to run a Postgres query whenever a user enters a product code (scanCode). However, I'm consistently seeing old results from the previous search instead of the current one. Sometimes on the second or third try, it finally shows the correct data, but then stays one step behind again for subsequent searches.
Details so far:
I’ve tried adding a dynamic “forceRefresh” parameter (using something like moment().unix()) to avoid caching, but Retool either rejects extra parameters (due to prepared statements) or it still returns the result from the previous code.
The query is set to manual trigger, no automatic running on input change, and no “run on page load”.
I’ve confirmed the correct values (inputScanCode.value, etc.) via console.log right before calling queryName.trigger(). In the Retool query “Preview” tab, the exact same parameter values return the correct rows, but in the actual app code it shows old or empty data the first time, then the correct data on the next attempt (always one cycle behind).
I also tried constructing the entire WHERE clause as a single string (one parameter in the SQL), forcing a brand-new value every time, and adding a small delay (await new Promise(r => setTimeout(r, 200))) before triggering the query—yet it still yields the previous search’s data.
There’s no visible option to disable prepared statements in my Retool environment, so each {{ }} reference is treated as a separate parameter. I’ve had to consolidate them, but the old-results issue persists.
What I Need:
A reliable way to ensure that each query execution uses the current parameters (especially the product code) and does not reuse or return old results.
Any steps or best practices to eliminate caching or delayed state updates in this scenario.
Would you be able to share the setup of your queries and their Advanced tabs? In each query there is an option for caching results that may have been enabled.
Also, if you are looking for a way to execute a statement without using the setting in the resource to disable converting to a prepared statement you can create a JS Query to "prepare" your statements and then EXEC the returned string (Dynamically generated SQL query in Retool - #2 by pyrrho). This will also let you check on the values being passed directly to the query.
... which is there for good reason! I do encourage anyone using this method to ensure that options are rigidly defined (or otherwise sanitized in the JS Query) so that mischievous beings aren't tempted to try and Bobby Tables you.
Hi Darren, not proud about it. In the script of my the button I created a logic that forces the user to click on it 2 times... the first time just trigger the query with the values of the text input... the second time asign my variables the results of the query. Never understood why it didn't work on just 1 script (trigger query + asigning results to variables)... and as I'm not a developer, I wasnt able to understand and apply the solution provided by Pyrrho.
Seeing the script that you're running is probably sufficient! Based off of what you're described so far, though, my guess is that you can fix the issue by toggling on the following option in the script's Advanced tab.
thanks!
