Error encountered when running Heroku Node.js getting started example - database section

Question

I have been faithfully following the excellent example on getting started with Heroku and Node.js. Up until the database section.

I have :

Created the Database.
I can access the Database via Heroku Database Dashboard.
I have created a table and inserted data into the table via the heroku pg:psql console.
I can execute a sql query against the table via the console.

But when i try and connect & query the data via a route in index.js, I encounter errors connecting to the DB.

The connection string as generated is as follows from heroku config which contains the required credentials as expected.

=== aqueous-eyrie-10062 Config Vars
DATABASE_URL:         postgres://uqeeuludvmhdzx:6181554101xxxxxxxxxxxxxxxxxxxxxxa@ec2-107-21-113-16.compute-1.amazonaws.com:5432/dbpa7e9l3b2k8g
PAPERTRAIL_API_TOKEN: T8Y4ESxxxxxxxx0rexl
TIMES:                7

After starting the local instance heroku local db2 ( or browsing to localhost:5000/db2) , the console displays the following output: (the user 'rober' is the current windows user b.t.w.)

The error is generated by the pg-query call as well as the pg.connect call in the other route ( /db )

C:\Users\rober\node-js-getting-started>heroku local
[OKAY] Loaded ENV .env File as KEY=VALUE Format
14:23:52 web.1   |  Node app is running on port 5000
14:23:55 web.1   |  (node:13876) DeprecationWarning: PG.connect is deprecated - please see the upgrade guide at https://node-postgres.com/guides/upgrading
14:23:55 web.1   |  result: undefined
14:23:55 web.1   |  rows: undefined
14:23:55 web.1   |  err: error: role "rober" does not exist
14:23:55 web.1   |  err: {"name":"error","length":90,"severity":"FATAL","code":"28000","file":"miscinit.c","line":"494","routine":"InitializeSessionUserId"}

below is index.js

var cool = require('cool-ascii-faces')
var express = require('express');
var app = express();
var pg = require('pg');
var pool = new pg.Pool();
var query2 = require('pg-query');

app.set('port', (process.env.PORT || 5000));

app.use(express.static(__dirname + '/public'));

// views is directory for all template files
app.set('views', __dirname + '/views');
app.set('view engine', 'ejs');

app.get('/db', function (request, response) {
    // ERROR received from below line
    pg.connect(process.env.DATABASE_URL, function(err, client, done) {
        if (client) {
            var date = new Date();
            var query = client.query('SELECT * FROM test_table',function(err, result) {
                if(!err) {
                    response.send(JSON.stringify(result.rows));
                    accounts = JSON.stringify(result.rows);
                } else {
                    response.send('failed');
                }
                done(); // call done to close the conection
            });
        } else {
            response.send('No Client Error - ' + JSON.stringify(err));
            throw(err);
        }
    });
});

app.get('/db2', function(request, response) {
    // ERROR received from below line
    query2.connectionParameters = process.env.DATABASE_URL;

    //accepts optional array of values as 2nd parameter for parameterized queries
    query2('SELECT $1::text as name', ['rob'], function(err, rows, result) {
        console.log('result: ' + result);
        console.log('rows: ' + rows);
        console.log('err: ' + err);
        console.log('err: ' + JSON.stringify(err));
    });
});

my package.json is as follows:

{
  "name": "node-js-getting-started",
  "version": "0.2.6",
  "description": "A sample Node.js app using Express 4",
  "engines": {
    "node": "6.10.2"
  },
  "main": "index.js",
  "scripts": {
    "start": "node index.js"
  },
  "dependencies": {
    "cool-ascii-faces": "1.3.4",
    "ejs": "2.5.6",
    "express": "4.15.2",
    "pg": "6.4.0",
    "pg-query": "0.11.0"
  },
  "repository": {
    "type": "git",
    "url": "https://github.com/heroku/node-js-getting-started"
  },
  "keywords": [
    "node",
    "heroku",
    "express"
  ],
  "license": "MIT"
}

According to my understanding, the db user should be obtained from the connection string and not from the current user. how do i rectify this?

score 0 · Answer 1 · answered Jul 12 '17 at 08:16

OK - thanks to a team member, i have managed to resolve the issue:

my assumption was that heroku config settings were available when running heroku local - WRONG!

To locally test before deploy I needed to set the DATABASE_URL into the windows environment variables using the SET command.

SET DATABASE_URL=postgres://uqexxxxxxhdzx:6181554xxxxxxxxxxxxxxxxxa@ec2-107-21-113-16.compute-1.amazonaws.com:5432/dbpa7e9l3b2k8g

I then proceeded to remove all the additional connection methods from my code above until i had only

var pg = require('pg');

app.get('/db', function (request, response) {
    pg.defaults.ssl = true;
    pg.connect(process.env.DATABASE_URL, function(err, client) {
        if (err) throw err;
        console.log('Connected to postgres! Getting schemas...');

        client
            .query('SELECT table_schema,table_name FROM information_schema.tables;')
            .on('row', function(row) {
                console.log(JSON.stringify(row));
            });
    });
});

Which works perfectly.

score 0 · Answer 2 · edited Sep 21 '17 at 19:29

The following code worked for me.

app.get('/db', function (request, response) {
pg.defaults.ssl = true;
pg.connect(process.env.DATABASE_URL, function(err, client) {
    if (err) throw err;
    console.log('Connected to postgres! Getting schemas...');

    client
        .query('SELECT * FROM test_table;', function(err, result) {

          if (err)
           { console.error(err); response.send("Error " + err); }
          else
           { response.render('pages/db', {results: result.rows} ); }
        });

});

});

Error encountered when running Heroku Node.js getting started example - database section

2 Answers2