bind services in cloud foundry not working

Question

i've created postgress service(via cf create-service) from the market-place and I want to use it in my node.js app.(I was able to test it locally which works) ive two question

1.i've tried the following and the application doesnt able to start and in the log I got this as my value for the env varible what am I missing here?

This is the code:

OK i've tried the following and the application doesnt able to start and in the log I got 

this as my value for the env varible what am I missing here?

     OUT env variable host: 10.0.97.139
     OUT port: 34807
     OUT user: qmxgvfybloierztm
     OUT password: mlofvwfsxmf7bqjr
     OUT database: r8n13yjyql7hwrgc
     OUT url: postgres://qmxgvfybloierztm:[email protected]:34607/r8n13yjyql7hwrgc
     OUT start create table
     OUT ERROR: connect: Error: connect ECONNREFUSED 10.0.97.135:5432




(function(){

    if (null == process.env.VCAP_SERVICES) {
        var url = 'postgress://localhost:27017/local';
    }
    else {
        var vcap_services = JSON.parse(process.env.VCAP_SERVICES);

        console.log("postgress URL: ", url);
    };

    var DBWrapper = require('node-dbi').DBWrapper;

    var dbConnectionConfig = {
        uri: vcap_services['postgresql'][0].credentials.uri,
        host: vcap_services['postgresql'][0].credentials.hostname,
        port: vcap_services['postgresql'][0].credentials.port,
        database: vcap_services['postgresql'][0].credentials.dbname,
        user: vcap_services['postgresql'][0].credentials.username,
        password: vcap_services['postgresql'][0].credentials.password
    };

    var dbWrapper = new DBWrapper('pg', dbConnectionConfig);

    dbWrapper.connect();

    console.log("start create table");
    dbWrapper.query("CREATE TABLE IF NOT EXISTS TAB1 ( firstname  TEXT primary key, lastname varchar(20) )", function (err, results) {
        if (err) {
            console.log("error while inserting data " + err);
        } else {
            console.log("success to insert data: ");
        }
    });

    })();

update (after the Jerome answer...) This is all my code!!!

"use strict";

var express = require("express");
var path = require("path");


var app = express();


var port = process.env.PORT || 3000;

app.listen(port, function () {


    (function () {


        var DBWrapper = require('node-dbi').DBWrapper;
        var vcap_services = JSON.loparse(process.env.VCAP_SERVICES);
        var pgconf = vcap_services['postgresql'][0].credentials;
        var dbConnectionConfig = {
            dsn: pgconf.uri
        };
        var dbWrapper = new DBWrapper('pg', dbConnectionConfig);
        dbWrapper.connect();


        console.log("env variable host: " + pgconf.hostname)
        console.log("port: " + pgconf.port);
        console.log("user: " + pgconf.user);
        console.log("password: " + pgconf.password);
        console.log("database: " + pgconf.database);
        console.log("url: " + pgconf.uri);


        console.log("start create table");
        dbWrapper.query("CREATE TABLE IF NOT EXISTS USER ( firstname  TEXT primary key, lastname varchar(20) )", function (err, results) {
            if (err) {

                console.log("error while inserting data " + err);
            } else {
                console.log("success to insert data: ");
            }
        });


        var data =
        {
            firstname: 'John5',
            lastname: 'Foo4444'
        };

        //insert data
        dbWrapper.insert('USER', data, function (err, data) {
            if (err) {
                console.log("error to insert data: " + err);
                // John has been inserted in our table, with its properties safely escaped
            } else {
                console.log("test" + data);
            }

        });

        //read data
        dbWrapper.fetchAll("SELECT * FROM USER", null, function (err, result) {
            if (!err) {
                console.log("Data came back from the DB.", result);
            } else {
                console.log("DB returned an error: %s", err);
            }

            dbWrapper.close(function (close_err) {
                if (close_err) {
                    console.log("Error while disconnecting: %s", close_err);
                }
            });
        });

    })();


});

Now I got this error

2016-07-26T11:55:49.69+0300 [App/0]      OUT env variable host: undefined
2016-07-26T11:55:49.69+0300 [App/0]      OUT port: 35058
2016-07-26T11:55:49.69+0300 [App/0]      OUT user: undefined
2016-07-26T11:55:49.69+0300 [App/0]      OUT password: hvevfgpjjtyqpr1d
2016-07-26T11:55:49.69+0300 [App/0]      OUT database: undefined
2016-07-26T11:55:49.69+0300 [App/0]      OUT url: postgres://fakttklwkxtfprgv:[email protected]:35058/ispkmc5psgdrwj4e
2016-07-26T11:55:49.69+0300 [App/0]      OUT start create table
2016-07-26T11:55:49.69+0300 [App/0]      OUT ERROR: connect: Error: getaddrinfo ENOTFOUND undefined undefined:5432

Answer 1

According to the code of node-dbi, the pg adapter builds the connection url as

var cfg = this._connectionParams;
if (connectionParams["dsn"]) {
  this._pgUrl = connectionParams["dsn"];
} else {
  this._pgUrl = 'pg://'+cfg.user+':'+cfg.password+'@'+cfg.host+'/'+cfg.database;
}
this._dbClient = new pg.Client(this._pgUrl);

Seeing your error message,

ERROR: connect: Error: connect ECONNREFUSED 10.0.97.135:5432

your code is trying to connect to the default postgres port, 5432, which hints at the fact that the port is not correctly forwarded to pg. The node-dbi source code does not use the cfg.port variable so this could explain your bug since the pg library is never given the 34607 port on which your postgres instance is supposedly listening if we believe the 'url' ENV variable that is shown in your post.

Note also that node-dbi prefers the dsn when it exists :

this._pgUrl = connectionParams["dsn"];

You seem to have the dsn in the ENV, under the name 'url'.

It is hard to give you the exact fix from the outside, but I believe it should work with

var DBWrapper = require('node-dbi').DBWrapper;
var pgconf = vcap_services['postgresql'][0].credentials;
var dbConnectionConfig = {
    dsn: pgconf.url
};
var dbWrapper = new DBWrapper('pg', dbConnectionConfig);
dbWrapper.connect();

Update

The given solution leads to the error

OUT ERROR: connect: Error: getaddrinfo ENOTFOUND undefined undefined:5432

which tends to show that the dsn is not given or not analyzed correcly by pg. This lead me to see that node-dbi uses a very old version of pg. So we should not try maybe to use the connection param as a string.

Do you confirm that the node-dbi version you are using is 0.7.1 ?

I just installed node-dbi. It gave me 0.7.1, where I can read

 var cfg = this._connectionParams;
 this._pgUrl = 'pg://' + cfg.user + ':' + cfg.password + '@' + cfg.host + '/' + cfg.database;
 this._dbClient = new pg.Client(this._pgUrl);

so this code does not match what we see on github. The dsn is not handled in this version.

This can be confirmed via https://github.com/DrBenton/Node-DBI/commits/master . The 0.7.1 version dates from Jan 2014 and the postgres dsn handling is from Jan 2015 so it has not been published yet ...

There is a discrepancy in your log and i don't understand why it states that host is undefined so I will bypass it using uri directly since the uri seems correct.

Seeing how node-dbi builds the pg connection string, you can try

var url = require('url');
var DBWrapper = require('node-dbi').DBWrapper;
var pgconf = vcap_services['postgresql'][0].credentials;
console.log(pgconf.uri);
var parts = url.parse(pgconf.uri);
var dbConnectionConfig = {
    user: parts.auth.split(':')[0],
    password: parts.auth.split(':')[1],
    host: parts.host,
    database: parts.pathname.substr(1)
};
var dbWrapper = new DBWrapper('pg', dbConnectionConfig);
dbWrapper.connect();

Once you get it working, it will be time to probably wonder why node-dbi seem so "fragile" and use another database wrapper like sequelize for instance if you do not want to directly use pg.

It should then work with

var sequelize = new Sequelize(pgconf.uri, {})

as per http://docs.sequelizejs.com/en/latest/api/sequelize/