Import data into retool DB with 50k+ rows (Workflow)

Queries and Resources
1

Hi, I keep hitting issues with Too many request to the database. I've tried a couple of the things seen in the forums. I'm attempting another update that I saw, however I'm getting this: ReferenceErrror: insert_inventory_counts is not defined (line 23). Here is a screenshot showing the javascript and query that is not being called for some reason.

image
image1552×780 122 KB

Not sure why it's saying it's not defined when it it there.

Thanks!

2

Hi @scordlebedrock,

I think this might be your problem:
image

You need to define insert_inventory_counts as a function, not as a block. The block can't be called from another block, the function can. Add it as a function in the side menu of your workflow. Then you can define the additionalScope scope param as well.

image

3

Thanks Mike, that worked. I actually had a function created, but have been trying a bunch of stuff to figure it out. Now my issue is:

image

So i'm definitely hitting this RetoolDB limit, it did end up doing 1000 of the 50k, so 1000 seems to be the number. I've tried changing the batch to 1000, 500, 200, 100, 50. It always ends up inserting 1000 then throwing the error.

I'm trying to go off of this post: How to run a large array of queries in smaller batches

But it still just gets to 1000 entries in the DB and dies

function formatData(data, store_id) {
  const dataArray = Object.entries(data).map(([upc, count]) => ({
    "id": upc + ':' + store_id,
    "upc": upc,
    "inventory_count": count,
    "store_code": store_id
  }));
  return dataArray;
}

let items = [];
for (const [index, value] of get_inventory_counts_for_all_stores.data.entries()) {
  let formattedData = formatData(value.data.stock, value.data.store_id);
  items.push(...formattedData);
}

const queries = items.map((item) => () => {
  return insertInventoryCounts(item);
})

async function runAllQueries(queries) {
  const batches = _.chunk(queries, 500);
  const results = [];
  while (batches.length) {
    const batch = batches.shift();
    // This map here actually runs the promise
    const result = await Promise.all(batch.map(fn => fn()));
    results.push(...result)
  }
  return results
}

return runAllQueries(queries)
4

Hi @scordlebedrock,

I don't know RetoolDB's and don't see any mention in the docs - but maybe try doing a bulk insert instead of thousands of single inserts. Then you just need to chunk your items and pass each chunk to the function.

image
image828×332 13.6 KB

If it still exceeds the rate limit, just await each call and/or add a delay of some sort between calls, const delay = ms => await new Promise(resolve => setTimeout(resolve, ms))