Async/Await Node-Postgres Queries Within ForEach Loops

Viewed 4429

EDIT: I'm using node v8.0.0

I just started learning how to access SQL databases with node-postgres, and I'm having a little bit of trouble accessing multiple databases to collect the data in a work able format, particularly with executing multiple queries within forEach loops. After a few tries, I'm trying async/await, but I get the following error:

await client.connect()
  ^^^^^^
SyntaxError: Unexpected identifier

When I tried using a pool or calling .query sequentially, I would get something along the lines of

1
[]
could not connect to postgres Error: Connection terminated

Here is an abbreviated version of my code:

const { Client } = require('pg');
const moment = require('moment');
const _ = require('lodash');
const turf = require('@turf/turf');

const connString = // connection string
var collected = []
const CID = 300
const snaptimes = // array of times
var counter=0;
const client = new Client(connString);

function createArray(i,j) {
     // return array of i arrays of length j
}

await client.connect()

snaptimes.forEach(function(snaptime){
  var info = {}; // an object of objects
  // get information at given snaptime from database 1
  const query1 = // parametrized query selecting two columns from database 1
  const result1 = await client.query(query1, [CID,snaptime]);
  var x = result1.rows;
  for (var i = 0; i < x.length; i++) {
     // store data from database 1 into info
     // each row is an object with two fields
  }

  // line up subjects on the hole
  const query2 = // parametrized query grabbing JSON string from database 2
  const result2 = await client.query(query2, [CID,snaptime]);
  const raw = result2.rows[0].JSON_col;
  const line = createArray(19,0); // an array of 19 empty arrays
  for (var i = 0; i < raw.length; i++) {
     // parse JSON object and record data into line 
  }

  // begin to collect data
  var n = 0;
  var g = 0;
  // walk down the line
  for (var i = 18; i > 0; i--) {
    // if no subjects are found at spot i, do nothing, except maybe update g
    if ((line[i] === undefined || line[i].length == 0) && g == 0){
      g = i;
    } else if (line[i] !== undefined && line[i].length != 0) {
      // collect data for each subject if subjects are found
      line[i].forEach(function(subject){
        const query 3 = // parametrized query grabbing data for each subject 
        const result3 = await client.query(query3,[CID,subject,snaptime]);
        x = result3.rows;
        const y = moment(x[0].end_time).diff(moment(snaptime),'minutes');
        var yhat = 0;
        // the summation over info depends on g
        if (g===0){
          for (var j = i; j <= 18; j++){
            yhat = moment.duration(info[j].field1).add(yhat,'m').asMinutes();
          }
        } else {
          for (var j = i; j <= 18; j++){
            if (i<j && j<g+1) {
              yhat = moment.duration(info[j].field2).add(yhat,'m').asMinutes();
            } else {
              yhat = moment.duration(info[j].field1).add(yhat,'m').asMinutes();
            }
          }
        }
        collected.push([y,yhat,n,i]);
      });
    }
    n+=line[i].length;
    g=0;
  }
  // really rough work-around I once used for printing results after a forEach of queries
  counter++;
  if (counter===snaptimes.length){
    console.log(counter);
    console.log(collected);
    client.end(); 
  }
});
3 Answers
Related