I'm working on a project where I need to make API calls to fetch conversion rates based on dates and many source currencies from a table. The goal is to have an extra column which will contain the converted amount in Euro. The API I'm using enforces a rate limit of 1 request per second, and I'm trying to optimize my calls to avoid hitting this limit while ensuring I don't make unnecessary duplicate requests for the same date and currency combination.
Here's the approach I've taken:
const delay = (duration) => new Promise((resolve) => setTimeout(resolve, duration));
// Initialize a cache object
const cache = {};
// Initialize counters for debugging
let counterSEK = 0;
let counterCacheHit = 0;
let counterAPICall = 0;
const promises = refundsTable.data.map((row, index) => {
// Generate a unique key for each combination of date, sourceCurrency, and targetCurrency
const cacheKey = `${moment(row.day).format('YYYY-MM-DD')}_${row.currency}_EUR`;
console.log(cacheKey);
if (row.currency === "EUR") {
counterEUR++; // Increment counter for 'EUR'
return Promise.resolve({ "data": 1 });
} else if (cache[cacheKey]) {
counterCacheHit++; // Increment counter for cache hit
return Promise.resolve(cache[cacheKey]);
} else {
return delay(index * 1000).then(() =>
getConversionRates.trigger({
additionalScope: {
conversionDate: moment(row.day).format('YYYY-MM-DD'),
sourceCurrency: row.currency,
targetCurrency: 'EUR',
},
}).then((result) => {
counterAPICall++; // Increment counter for API call
// Store the result in cache before returning it
console.log(result.data);
cache[cacheKey] = result.data;
return result;
})
);
}
});
// Return data
//return Promise.all(promises);
// Debugging
Promise.all(promises).then(() => {
// Log the counters after all promises have resolved
console.log(cache);
console.log(`EUR Direct Returns: ${counterEUR}`);
console.log(`Cache Hits: ${counterCacheHit}`);
console.log(`API Calls Made: ${counterAPICall}`);
});
Unfortunately, the caching mechanism does not work as it never gets hit. When the source currency is not 'EUR', it always revert to making API calls - which are cached, but still I feel I could gain seconds in the end if it directly hit the function cache instead.
I'm not entirely sure what you're returning from this function, but I think Retool has already solved your issue.
If you look at the logs, the last 4 green lines to getConversionRates all say "from cache". If you go to the advanced tab on your function, you probably have "cache the result of this query" checked off. You can see based on the times listed, that the calls marked as "from cache" rand 3-4x faster than the others. Your counter still went up, because you are incrementing it any time you call the function - but the function itself is keeping a cache and responding immediately with the appropriate conversion rate.
For the 1s limit, you can use the Delay between runs property on the Advanced tab, set it to 1000, and Retool should limit you to 1 actual call/second.
I understand your statement about the cache query and the delay I'm introducing. It makes sense to only rely on the cache query, not to introduce more delay, I get it.
Though I can't find "Delay between runs" in the query "Advanced" tab. Could you share a screenshot?
Also, does it make sense to cache both the REST query and the JS query, or should I apply it only to the REST/JS?
It is only available on Resource Queries, not JS queries. I'm not sure I understand your question about JS Query Caching vs. REST. Are you storing the converted amount in your DB table, or just trying to show it as an extra column in your UI? I'd need to understand the what you're trying to accomplish a bit better before trying to make that kind of suggestion. If you can provide more detail I can try to help.
Unfortunately, the Delay between runs option does not appear when the POST method is selected in the Resource query.
Sure, let me explain with more details:
I'm fetching data from a MySQL database and use it to feed a table. There are 3 columns: product, price and currency.
My goal is to create a 4th column converted_price which will display a conversion of the price value of each row (to Euro). It's meant for display only, I am not planning to save it in the database.
To convert the source price, I am using an API which takes three parameters: date, source and target, and uses the POST method. It has a restriction of 1 request per second. I have created a Resource query and specified these parameters, and I ticked the option to enable caching. This resource is named getConversionRates. Response payload example: { "data": 1.51024947 }
To pass the price of each row, and have it converted, I have created a JS query named getRate, which is referenced from the 4th table column with {{ currentSourceRow.price * getRate.data[i].data }}. Initially, it was simple query inspired from this code. But I was getting lots of errors as initial queries (before caching) were not throttled to a 1000ms delay. Since I could not find a native way to delay requests with Retool - and it does not seem to be possible with POST requests - I updated the code to implement a delay between requests.
Implementing the delay fixed the API restriction issue, but it was very slow. Then, I thought of adding a cache of already converted currencies, so there will be a lookup in the cache before triggering getConvertedRates - which responses are also cached, but it was still slow, and that might be because of the way I have implemented the delay in the code if I understood your replies correctly?
I hope it's a bit more clear, and thank you for your help!
I don't know the volume of data you're dealing with, but you might want to consider writing the results to the DB (either per row, or save the date, source, target values in a table, then only querying for the missing ones. That would make more sense if you're viewing the same tables regularly.
You could then create a function for your final column that takes in the params, checks for them in the db, falling back on the API and then updating the DB for future requests. That isn't going to make it easy to solve your throttle issue though - so you might need to make it a function that runs on page load, and then setting the table to not display until the function is done running. To solve for your throttle limit I'd leverage a package like PQueue and make use of the interval and intervalCap options.
Even if you don't want to store the conversions in the DB, this is how I imagine you'd need to set up your flow.
// Function that runs on page load, called "conversionValues"
import PQueue from 'p-queue';
const queue = new PQueue({interval: 1000, intervalCap: 1});
// This can come from a call like "SELECT DISTINCT date, source FROM your_table"
const uniqueData = [{date: "1/1/2024", source: "SEK"}, {date: "1/2/2024", source: "SEK"}, {date: "1/1/2024", source: "EUR"}]
// To keep the results
const results = {}
uniqueData.forEach(data => {
let key = `${data.date}_${data.source}`
if(data.source === 'EUR') {
results[key] = 1
} else {
queue.add( async () => {
let res = await getConversionRates(data.date, data.source)
results[key] = res
}
}
})
queue.on('error', (e) => {
// Handle error
}
await queue.onIdle()
return results
After the above runs, you new column can just look to the results from the function and find the conversion rate based on the key (which you have in your table anyway), so the new column could be something like
if all you need is a delay between runs and ypu dont care abput using weird workarounds. I believe you could make a new REST Request Resource and set it to GET and https://google.com or something fast. then add a Success Event Handler and trigger your intended query. finally. its a little wonky but now you have a dummy GET query where you can set the Delay Between Runs to 1s
@MikeCB, the volume of data is around 150 rows a day. But your suggestion gives me an idea, I could add a column converted_price_eur in the table and have a workflow to populate that data on a daily basis. Then the logic would only lookup for rates when the value of the row is empty. Thanks for the function!
@bobthebear, I wanted to try your workaround but I don't understand how to implement it. I can set the GET query to trigger (on success) the POST one, but how do you pass the parameters? How would the POST query return the data back to the JS query that triggered the GET?
I could not implement PQueue as per your suggestion @MikeCB because it did not seem compatible with Retool. The library is added via a CDN link, but is not accessible from queries. Maybe it's the same problem as described here. Anyways, instead I found an alternative called Bottleneck.
Here's my implementation for everyone interested in throttling POST requests:
const limiter = new Bottleneck({
maxConcurrent: 1,
minTime: 1000 // At most 1 request per second
});
const uniqueData = formatDataAsArray(getUniqueCurrencies.data)
const results = {};
// Use Promise.all to wait for all scheduled tasks to complete
await Promise.all(uniqueData.map(data => {
const key = `${data.date}_${data.source}`;
if (data.source === 'EUR') {
results[key] = 1; // Directly assign value if source is 'EUR'
return Promise.resolve(); // Immediately resolve for 'EUR'
} else {
// Schedule the task and update results within the promise
return limiter.schedule(() =>
getConversionRates.trigger({
additionalScope: {
conversionDate: data.date,
sourceCurrency: data.source,
targetCurrency: 'EUR',
},
}).then(res => {
results[key] = res.data;
}));
}
}));
return results;
It works and it's faster than before. Sometimes I would notice "rate exceeded" warnings, but I guess I could increase the minTime to make sure it observes more than a second between requests. I'm sure this snippet could be improved.
ah, yea you would need to use the Run Script action for the onSuccess event handler. then just use myPostQueryToTrigger.trigger({additonalSource:{ paramName: paramValue }}), afterwards if you go to the query that makes the POST request you can directly use 'paramName'. the POST query wouldn't need to return anything to the GET query. if you need the results from the POST query tho you can just use it as normal in JS as myPostQueryToTrigger.data or set it's onSuccess/onFailure events... you can honestly just ignore the GET query alltogether at this point, it's only purpose is to always be successful and do so every so often (however many seconds you have Delay Between Runs set to).
things to keep in mind:
the GET query will ALWAYS run every so often and it will ALWAYS be successful.
use Delay Between Runs to set how many seconds to wait before running the query again.
use the 'Disable Query' property under the Advanced tab if you need to interrupt this and prevent it from running (it's the same as setting the delay to infinite or removing it). the statement you put here determines if the query will run after the set time, if it evaluates to 'true' the query is disabled and won't run no matter what
this will effectively disable the GET query only, so it will no longer call the POST query every so often.
this does not disable the POST query, you can still manually trigger it using myPostQueryToTrigger.trigger() or you can trigger it from the onSuccess/Failure event from some other query
if you were to set the Disable Query on the POST query instead, it would still function the same but you would now have the GET query being triggered every few seconds for no reason even if the POST one is disabled, which is gonna nick your efficiency a bit. it's best to disable the chain of query calls (from onSuccess/onFailure) as soon as possible and in this case that would be the 'timer' (our GET query) and not the action repeated at the end of the timer (the POST query)
