Using a proxy

[zachweinberg]

It would be nice to have proxy support for Sequelize. Using Sequelize on a host with rotating IP addresses makes it near impossible to whitelist connections to an external database. I understand that this would depend on the connection package (node-mysql, pg). These packages do offer support for this feature, but there is no way to initialize Sequelize using those stream options. For example, with the pg package:

const pg = require('pg');
const SocksConnection = require('socksjs');

const pgServer = {
  host: 'YOUR-HOST',
  port: 5432
};

const proxyConnection = new SocksConnection(pgServer, {
  user: process.env.PROXY_USER,
  pass: process.env.PROXY_PASS,
  host: process.env.PROXY_HOST,
  port: process.env.PROXY_PORT,
});

const connectionConfig = {
  user: 'YOUR-DB-USERNAME',
  password: 'YOUR-DB-PASSWORD',
  database: 'YOUR-DATABASE',
  stream: proxyConnection,
  ssl: true // Optional, depending on db config
};

const client = new pg.Client(connectionConfig);

// client.connect() { ... }

Since we can't pass a custom pg instance into Sequelize, how would we go about making this work?

[papb]

Since we can't pass a custom pg instance into Sequelize, how would we go about making this work?

There are a few options in the Sequelize constructor that provide some flexibility for the connection package. Have you considered options.dialectModule, options.dialectModulePath and/or options.dialectOptions?

[vitalbone]

I think it's possible using dialectOptions:

const Sequelize = require('sequelize');
const SocksConnection = require('socksjs');
const { FIXIE_VALUES } = require('./config');

const DB = {
  host: process.env.DATABASE_HOST || '127.0.0.1',
  port: process.env.DATABASE_PORT || 5432
};

const fixieConnection = new SocksConnection(DB, {
    user: FIXIE_VALUES[0],
    pass: FIXIE_VALUES[1],
    host: FIXIE_VALUES[2],
    port: FIXIE_VALUES[3],
  });
}

const client = new Sequelize(DATABASE_URL, {
  dialect: 'postgres',
  ssl: true,
  dialectOptions: {
    ssl: {
      require: true
    },
    stream: fixieConnection,
  }
});

// client.sync()

Although I am still running into a connection problem:

(node:36479) UnhandledPromiseRejectionWarning: SequelizeConnectionError: connect ETIMEDOUT 167.71.XXX.XXX:XXXXX

Are there examples of alternate ways to specify a connection stream directly to Postgres?

[papb]

@vitalbone Do you also get this ETIMEDOUT error when using pg directly (without Sequelize)?

[vitalbone]

@vitalbone Do you also get this ETIMEDOUT error when using pg directly (without Sequelize)?

Unfortunately I can't confirm because I don't have the ability to test the connection to the same static IP or remote database anymore.

[balexandre]

I agree it should be so much easier to actually set up a socket proxy in Sequelize...

As hosting in Heroku (dynamic IP infrastructure) and needed to set up Azure firewall (MS SQL) correctly without open the database to all IP's, the only way is a proxy connection, but I can't make this work :(

tried your example @vitalbone as:

const { DB_HOST, DB_DATABASE, DB_USER, DB_PASS, QUOTAGUARDSTATIC_URL } = process.env;

// SETUP PROXY
const proxy = url.parse(QUOTAGUARDSTATIC_URL);
const proxyConnection = new SocksConnection({
    host: DB_HOST,
    port: 1433, // MS SQL port
}, {
    user: proxy.auth.split(':')[0],
    pass: proxy.auth.split(':')[1],
    host: proxy.hostname,
    port: proxy.port,
});

// SETUP CONNECTION
const sequelize = new Sequelize(DB_DATABASE, DB_USER, DB_PASS, {
    host: DB_HOST,
    dialect: 'mssql',
    logging: process.env.APP_ENVIRONMENT === 'local' ? console.log : false,
    pool: {
        max: 5,
        min: 0,
        idle: 10000,
    },
    define: {
        engine: 'InnoDB',
        collate: 'latin1_swedish_ci',
    },
    dialectOptions: {
        stream: proxyConnection,
        options: {
            encrypt: true,
            requestTimeout: 300000,
        },
    },
});

it does not use that proxy as a connection but used my own IP Address :(

[iamakimmer]

@balexandre have you found any other workaround? I'm at the point where I would have to move off heroku to secure ips :(

[Noerdsteil]

@balexandre same here.
@ZachJames did u find a workaround/solution?
Heroku + Sequelize getting timeout.

I also added a working example with mysql2 (see below)

var mysql = require('mysql2'),
    url = require('url'),
    SocksConnection = require('socksjs');
  const Sequelize = require('sequelize')

var remote_options = {
  host: 'db_host_url',
  port: 3306,
}


const urlString = 'http://proxy:888888888888@proxy-88-888-888-888.proximo.io'
var proxy = url.parse(urlString)
var auth = proxy.auth
var username = auth.split(':')[0]
var pass = auth.split(':')[1]

var sock_options = {
  host: proxy.hostname,
  user: username,
  port: 1080,
  pass: pass,
}

var sockConn = new SocksConnection(remote_options, sock_options)

const config = {
  host: 'db_host_url', // seems to be ignored due to remote_options
  dialect: 'mysql',
  logging: console.log,
  dialectOptions: {
    stream: sockConn,
  },
}
const sequelize = new Sequelize('db_name', 'user_name', 'password', config)

  sequelize
  .authenticate()
  .then(() => {
      console.log('Connection has been established successfully.');
  })
  .catch((err) => {
      console.log('Unable to connect to the database:', err);
  })

Output is always TimeoutError and I tried a lot of things. Sequelize seems to pass stream option, but couldn't resolve the issue so far.

Unable to connect to the database: { SequelizeConnectionError: connect ETIMEDOUT
    at Promise.tap.then.catch.err (/project_root/node_modules/sequelize/lib/dialects/mysql/connection-manager.js:133:19)
    at tryCatcher (/project_root/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/project_root/node_modules/bluebird/js/release/promise.js:547:31)
    at Promise._settlePromise (/project_root/node_modules/bluebird/js/release/promise.js:604:18)
    at Promise._settlePromise0 (/project_root/node_modules/bluebird/js/release/promise.js:649:10)
    at Promise._settlePromises (/project_root/node_modules/bluebird/js/release/promise.js:725:18)
    at _drainQueueStep (/project_root/node_modules/bluebird/js/release/async.js:93:12)
    at _drainQueue (/project_root/node_modules/bluebird/js/release/async.js:86:9)
    at Async._drainQueues (/project_root/node_modules/bluebird/js/release/async.js:102:5)
    at Immediate.Async.drainQueues [as _onImmediate] (/project_root/node_modules/bluebird/js/release/async.js:15:14)
    at runCallback (timers.js:705:18)
    at tryOnImmediate (timers.js:676:5)
    at processImmediate (timers.js:658:5)
  name: 'SequelizeConnectionError',
  parent:
   { Error: connect ETIMEDOUT
       at Connection._handleTimeoutError (/project_root/node_modules/mysql2/lib/connection.js:173:17)
       at ontimeout (timers.js:436:11)
       at tryOnTimeout (timers.js:300:5)
       at listOnTimeout (timers.js:263:5)
       at Timer.processTimers (timers.js:223:10)
     errorno: 'ETIMEDOUT',
     code: 'ETIMEDOUT',
     syscall: 'connect',
     fatal: true },
  original:
   { Error: connect ETIMEDOUT
       at Connection._handleTimeoutError (/project_root/node_modules/mysql2/lib/connection.js:173:17)
       at ontimeout (timers.js:436:11)
       at tryOnTimeout (timers.js:300:5)
       at listOnTimeout (timers.js:263:5)
       at Timer.processTimers (timers.js:223:10)
     errorno: 'ETIMEDOUT',
     code: 'ETIMEDOUT',
     syscall: 'connect',
     fatal: true } }

This is working for me

var mysql = require('mysql2'),
    url = require('url'),
    SocksConnection = require('socksjs');

var remote_options = {
    host:'db_host_url',
    port: 3306
};

const urlString = "http://proxy:888888888888@proxy-88-888-888-888.proximo.io"
var proxy = url.parse(urlString),
    auth = proxy.auth,
    username = auth.split(':')[0],
    pass = auth.split(':')[1];

var sock_options = {
    host: proxy.hostname,
    port: 1080,
    user: username,
    pass: pass
};

var sockConn = new SocksConnection(remote_options, sock_options);
var dbConnection = mysql.createConnection({
    user: 'db_user_name',
    database: 'db_name',
    password: 'password',
    stream: sockConn
});
dbConnection.query('SELECT 1+1 as test1;', function(err, rows, fields) {
    if (err) throw err;

    console.log('Result: ', rows);
    sockConn.dispose();
});
dbConnection.end();

Output as expected: Result: [ TextRow { test1: 2 } ]

[zachweinberg]

@Noerdsteil No I've found no solution to this while using Heroku unfortunately.

[papb]

Related: #9780

[mgoria]

stream option needs to be a function returning a new Socks connection, something like:

const createNewProxyStream = (proxyUrl) => {
 const proxyUrlObj = new URL(proxyUrl)

  const remoteOptions = {
    host: DB_HOST,
    port: DB_PORT,
  }

  const sockOptions = {
    host: proxyUrlObj.hostname,
    port: 1080,
    user: proxyUrlObj.username,
    pass: proxyUrlObj.password,
  }

  return new SocksConnection(remoteOptions, sockOptions)
}

const config = {
  host: DB_HOST,
  dialect: 'mysql',
  dialectOptions: {
    // IMPORTANT: the stream needs to be created all the time since new pooled connection needs new stream
    stream: (options) => {
      console.log('[sequelize-connection] - creating new proxy stream.')
      return createNewProxyStream()
    },
  },
}

const sequelize = new Sequelize(DB_NAME, DB_USER, DB_PASS, config);

[sikios182]

So? Is it impossible to use sequelize with a proxy? :(

[imjeffparedes]

Our database connection increased heavily after we migrated our apps to AWS Lambda. The RDS DB Proxy did not worked.

[jackblk]

I spent too much time on this so I hope this helps you.

I need to connect my Heroku dyno to RDS DB which limits to some inbound IP addresses. I use Postgres and its client does not allow stream option, so cannot directly use SOCKS5 proxy (I think some are able to make it work with mysql).

My solution is to use qgtunnel from QuotaGuard. I've tried Proximo but it does not have a working wrapper client for SOCKS5 proxy.

Setup a transparent tunnel, add the wrapper to your repo & Procfile and it should work seamlessly. Free advertising for them I guess.

[kellynauert]

@jackblk Could you provide an example of your working QuotaGuard qgtunnel? I'm still getting a timeout error with this method, and I'm not very familiar with networking so I can't tell from the documentation what I may be doing wrong.

[muhammadtayyab3411]

Has anyone found a solution to this?