Bind cloud foundry services not working

Question

I created a postgress service (via cf create-service) from the market and I want to use it in my node.js application (I was able to check it locally, which works) ive two questions

1.and tried the following, and the application cannot start in the log that I received it as my value for 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 

is it like my value for 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: mlofvwfsxmf7bqjr@10.0.97.135 :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 Jerome's 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: hvevfgpjjtyqpr1d@10.0.97.140 :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 node -dbi code , 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 the error message

 ERROR: connect: Error: connect ECONNREFUSED 10.0.97.135:5432 

your code tries to connect to the default postgres port, 5432, which hints that the port is not properly redirected to pg. The source code for node -dbi does not use the cfg.port variable, so this may explain your error, since the pg library is never provided with port 34607, which your postgres instance is supposed to listen on, if we believe in the ENV variable, is displayed in your message.

Note that node-dbi prefers dsn when it exists:

 this._pgUrl = connectionParams["dsn"]; 

You seem to have a dsn in ENV called 'url'.

It's hard to give you an accurate fix outside, but I believe that 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

This solution results in an error.

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

which shows that dsn is not given or analyzed accordingly on pg . This led me to the conclusion that node-dbi uses a very old version of pg . Therefore, we should not try to use the connection parameter as a string.

Do you confirm that the version of node-dbi 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); 

therefore, this code does not match what we see on github. In this version, dsn is not processed.

This can be confirmed using https://github.com/DrBenton/Node-DBI/commits/master . Version 0.7.1 dates from January 2014, and postgres dsn processing dates back to January 2015, so it has not yet been published ...

There is a mismatch in your log, and I don’t understand why it indicates that the host is undefined, so I will bypass it with uri directly, since uri seems to be correct.

Seeing how node-dbi builds a 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 earn it, it is probably time to wonder why node-dbi seems so "fragile" and uses another database wrapper, such as sequelize , if you do not want to use pg directly.

Then he must work with

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

according to http://docs.sequelizejs.com/en/latest/api/sequelize/