Unable to query PG database using koa-pg middleware on Koa on localhost

Question

In the past I've been able to connect to a postgres db using koa-pg middleware connected to a database hosted on Heroku, but I'm having problems connecting to a locally-hosted database.

Specifically, the error I have is TypeError: Cannot read property 'client' of undefined.

The following is my setup on the single-file app:

const koa = require('koa');
let route = require('koa-route'); // For calling specific routes
let request = require('koa-request');  // For RESTful requests
let paramify = require('koa-params'); 
var koaPg = require('koa-pg');
let pg = require('pg'); // .native;
let cors = require('koa-cors');
let parser = require('xml2js').parseString;

// pg.defaults.ssl = true;

route = paramify(route);
let param = route.param;
let get = route.get;

let app = koa();
let appPort = (process.env.PORT || 3000)
app.use(cors());
app.use(koaPg('postgres://localhost:5432/ttc_clustering_dev'));

And the following is the route where the issue lies:

app.use(route.get('/initialDefaultRouteQuery', function *() {
  let options = {
    url: 'http://webservices.nextbus.com/service/publicXMLFeed?command=vehicleLocations&a=ttc&r=60'
  }
  let xmlResponse = yield request(options)
  let jsResponse = ''
  parser(xmlResponse.body, function(err,result){
    //Extract the value from the data element
    jsResponse = result
    if (err !== null) {
      console.log(`Error: ${err}`)
    } else {
      console.log('Success in parsing from XML to JSON')
    }
  });

  let i = 0
  while (i < jsResponse.body.vehicle.length) {
    let query_content = `INSERT INTO temp_records (route_id, bus_id, capture_time, geometry) VALUES ('60', '${jsResponse.body.vehicle[i].$.id}', ${Date.now() - (jsResponse.body.vehicle[i].$.secsSinceReport * 1000)}, ST_GeomFromText('POINT(${jsResponse.body.vehicle[i].$.lng} ${jsResponse.body.vehicle[i].$.lat})'))` 

    let result = yield pg.db.client.query_(query_content)
    console.log('result:' + result)
    i += 1;
  }
  this.body = 'Finished!'
}));

It appears I've used the proper setup according to the docs, but there's likely something I'm missing here. Does anyone else see where I'm falling flat?

The full file can be found here: https://github.com/brianbancroft/ttc-clustering/blob/add-records-to-db/server/app.js

Answer 1

Due to the docs it should be:

let result = yield this.pg.db.client.query_(query_content)

instead of

let result = yield pg.db.client.query_(query_content)

So the this. is missing.

And reviewing your code, you are explicitly requiring pg, so your code is calling that one instead the one from koa-pg. Therefore pg.db seems not to be defined. Makes sense?