Skip to content

Instantly share code, notes, and snippets.

@abtris

abtris/client.js

Created Jan 9, 2017
Embed
What would you like to do?
Connect to Postgres/Redshift over Socks proxy.
var pg = require('pg'),
url = require('url'),
SocksConnection = require('socksjs');
var db = url.parse(process.env.REDSHIFT_CONN_STRING),
dbAuth = db.auth,
dbUsername = dbAuth.split(':')[0],
dbPassword = dbAuth.split(':')[1],
dbName = db.pathname.replace('/', '');
var proxy = url.parse(process.env.QUOTAGUARDSTATIC_URL),
auth = proxy.auth,
username = auth.split(':')[0],
pass = auth.split(':')[1];
var sock_options = {
host: proxy.hostname,
port: 1080,
user: username,
pass: pass
};
var remote_options = {
host: db.hostname,
port: db.port
};
var sockConn = new SocksConnection(remote_options, sock_options);
var config = {
user: dbUsername,
database: dbName,
password: dbPassword,
stream: sockConn
};
var client = new pg.Client(config);
// connect to our database
client.connect(function (err) {
if (err) throw err;
// execute a query on our database
client.query('SELECT $1::text as name', ['john doe'], function (err, result) {
if (err) throw err;
// just print the result to the console
console.log(result.rows[0]); // outputs: { name: 'apiary' }
// disconnect the client
client.end(function (err) {
if (err) throw err;
});
});
});
@matteoterrinoni

This comment has been minimized.

Copy link

@matteoterrinoni matteoterrinoni commented Jul 5, 2019

I'm curious, where that stream option comes from?

@sachincloudnaut

This comment has been minimized.

Copy link

@sachincloudnaut sachincloudnaut commented Jul 29, 2019

Hi,

can you pls explain how to form process.env.REDSHIFT_CONN_STRING for node js app

@abtris

This comment has been minimized.

Copy link
Owner Author

@abtris abtris commented Jul 29, 2019

As you see using https://nodejs.org/docs/latest/api/url.html for example postgres://user:password@hostname.com nothing special

@abtris

This comment has been minimized.

Copy link
Owner Author

@abtris abtris commented Jul 29, 2019

I'm curious, where that stream option comes from?

https://github.com/brianc/node-postgres/blob/master/lib/connection.js#L22

@sachincloudnaut

This comment has been minimized.

Copy link

@sachincloudnaut sachincloudnaut commented Aug 1, 2019

Hi,

Thanks for reply

I am connecting to aws redshift db and I use Env variable like PGSSLMODE=require and that solved the connectivity issue.

By the way I am using "pg" NPM package for redshift in my Node app and my co-workers think that the client machine must have JAVA runtime or some JDBC/ODBC driver on the computer where Node app is running. Pleases let me know if that is right.

@abtris

This comment has been minimized.

Copy link
Owner Author

@abtris abtris commented Aug 1, 2019

Not sure about that. I don't use java for long time. node-pg using C++ bindings.

@RKutynko

This comment has been minimized.

Copy link

@RKutynko RKutynko commented Oct 24, 2020

not working for me 😕

Debugger attached.
Waiting for the debugger to disconnect...
<proj name>\node_modules\pg\lib\connection.js:38
    this.stream.setNoDelay(true)
                ^

TypeError: this.stream.setNoDelay is not a function
    at Connection.connect (<proj name>\node_modules\pg\lib\connection.js:38:17)
    at Client._connect (<proj name>\node_modules\pg\lib\client.js:104:11)
    at Client.connect (<proj name>\node_modules\pg\lib\client.js:152:12)
    at Object.<anonymous> (<proj name>\test2.js:40:8)
    at Module._compile (internal/modules/cjs/loader.js:1063:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1092:10)
    at Module.load (internal/modules/cjs/loader.js:928:32)
    at Function.Module._load (internal/modules/cjs/loader.js:769:14)
    at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:72:12)
    at internal/main/run_main_module.js:17:47
Process exited with code 1

My packages

  "dependencies": {
    "pg": "^8.4.1",
    "socksjs": "^0.5.0"
  },
@alinstanescu

This comment has been minimized.

Copy link

@alinstanescu alinstanescu commented Jan 11, 2021

Hi, I am facing the same issue. Does someone has an answer for this? thank you.

TypeError: this.stream.setNoDelay is not a function
at Connection.connect (D:\WORK\Brejnholt\2020\ReturnSystem\node_modules\pg\lib\connection.js:38:17)
at Client._connect (D:\WORK\Brejnholt\2020\ReturnSystem\node_modules\pg\lib\client.js:113:11)
at Client.connect (D:\WORK\Brejnholt\2020\ReturnSystem\node_modules\pg\lib\client.js:161:12)
at BoundPool.newClient (D:\WORK\Brejnholt\2020\ReturnSystem\node_modules\pg-pool\index.js:227:12)
at BoundPool.connect (D:\WORK\Brejnholt\2020\ReturnSystem\node_modules\pg-pool\index.js:202:10)
at BoundPool.query (D:\WORK\Brejnholt\2020\ReturnSystem\node_modules\pg-pool\index.js:350:10)
at Object.runQuery (D:\WORK\Brejnholt\2020\ReturnSystem\database\pgdb.js:29:29)
at Object. (D:\WORK\Brejnholt\2020\ReturnSystem\server.js:29:6)
at Module._compile (internal/modules/cjs/loader.js:1063:30)

@abtris

This comment has been minimized.

Copy link
Owner Author

@abtris abtris commented Jan 11, 2021

Example with with pg ~6.x not sure if this should be useful for anyone in these days.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.