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 commented Jul 5, 2019

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

@sachincloudnaut

This comment has been minimized.

Copy link

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 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 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 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 commented Aug 1, 2019

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

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.