How do I keep a table updated?

Queries and Resources
1

Most of my data lives in DB1. I want this to be read-only.

I also have DB2, which is where I’m writing. I’m using retool to query DB1 and store the results in DB2. The problem is that some of the tables I’m trying to write in DB2 have more than 1000 rows and when I do a Bulk upsert by primary key, I get a time out.

I guess one option would be to create INSERT... ON CONFLICT UPDATE statements for every row coming from DB1.

I know how to do this in JS, but other than copying and pasting the output directly into DB2, I don’t know how to use retool to do this automatically as part of a query.

I’m currently stuck at this point. I would appreciate any help.

2

Hi Nacho!

Our scripting Retool documentation has a few examples of firing a query for every row in a dataset (or item in an array) – which sounds exactly like your use case. The docs will walk you through creating an Update a record, or create a new record if it doesn’t exist query for a single record (or write your own INSERT... ON CONFLICT UPDATE query if you don’t want to use the Retool UI) and then writing a Run JS Code query that loops through all the rows in the result of your DB1 query.

Out of curiosity – how large are the tables in DB2 and are you trying to insert on a primary key or an index?

Hope this helps! Let me know if you have any followup questions.
Jane

3

I tried to follow the example:

  1. I have a button which runs my codeQuery when clicked

  2. codeQuery is a JS query that gets its rows from the dataQuery and triggers the upsertQuery:

    var rows = dataQuery.data;
var errors = '';
var total = rows.length;

function runQuery (i) {
  if (i >= rows.length) {
    console.log('Finished running all queries');
    return;
  }

  console.log('Running query for row', i);

  upsertQuery.trigger({
    additionalScope: { i: i },
    onSuccess: function(data) {
      runQuery(i + 1);
    },
    onFailure: function(error) {
      errors += 'Found error at line ' + i.toString() + ':  ' + error +  '\n\n';
      console.log('error', error);
      runQuery(i + 1);
    }
  });
}

runQuery(0);

  3. dataQuery is just a postgres SELECT query from a readable DB

  4. upsertQuery is a postgres query that does a non-bulk upsert in a writable DB using the single row data from dataQuery.

    Each row has an id (which I use in the filter step) and other fields (which I use in the changeset):

    image
    image1750×600 65.7 KB

The problem is that each upsert takes around 3 seconds, so at 2000 rows, it would take me more than 1.5 hours to run this query. Is there a better way?

When I tried to use the Bulk Upsert by Primary Key it timed out after 60 seconds.

4

I think you would have better luck by constructing one big query string and then running it as one query

1 Like
5

Thanks, Byron. This is what I ended up doing and it worked. Thanks @justin for the idea:

  1. Create a read connection for the DB I want to write to and make sure you select "Disable converting queries to prepared statements" (understand the security implications of doing this)

    image
    image1252×356 26.1 KB

    image
    image1316×294 18.7 KB

  2. Create a transformer or a JS query to reshape the data into SQL values. This is the function I added to my Organization Settings / Advanced / Preloaded Javascript and in my transformer I just did return toSqlValues(data, columnNames);:

// given
// columnNames = [ "id", "post_id", "student_username", "requested_at", "actual_role"];
// arrayOfObjects = [
//   {id: "1499_129",post_id: 12594,student_username: "o'henry",requested_at: "2020-01-14T04:39:42.983Z",actual_role: null},
//   {id: "1499_129", post_id: 12594, student_username: "o'henry", requested_at: "2020-01-14T04:39:42.983Z", actual_role: null},
// ];
//
// returns
// "('1499_129',12594,'o''henry','2020-01-14T04:39:42.983Z',null), ('1499_129',12594,'o''henry','2020-01-14T04:39:42.983Z',null)"
window.toSqlValues = function (arrayOfObjects, columnNames) {
  return arrayOfObjects
    .map((rowObj) => {
      const rowArray = columnNames.map((columnName) => rowObj[columnName]);
      return JSON.stringify(rowArray)
        .replace(/\[([^\]]+)\]/, "($1)")
        .replace(/'/g, "''")
        .replace(/"/g, "'");
    })
    .join(", ");
};
  1. Create a query for your readable DB and write the UPSERT statement. You could do the whole thing in JS as well, but I wanted to keep it separate and just interpolate the values:
INSERT INTO
  class_attendance (
    id,
    topic_id,
    student_id,
    student_username
  )
VALUES {{ myTransformer.value }} ON CONFLICT (id) DO UPDATE
SET
  topic_id = excluded.topic_id,
  student_id = excluded.student_id,
  student_username = excluded.student_username;
  1. Assign this query to a button and run it. I was able to UPSERT 2000 rows in 3 seconds instead of timing out with the default bulk upsert query.

If anyone (@alex-w, @justin) can recommend a better way of doing this, I'm all ears.

1 Like