Node JS: How to access MySQL remotely using SSH.

Recently I was working on a Data Migration Project. For one of the cases, I had to connect to a MySql database through an SSH Tunnel.

Here, I will show you how to get it done quickly.

We will be using ssh2 and mysql2 npm packages. So, make sure to install these.

Let’s get started by writing our database config file.

// dbConfig.js
// define connection config for the database
const dbServer = {
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    user: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE
}
// define connection config for the ssh tunnel
const tunnelConfig = {
    host: process.env.DB_SSH_HOST,
    port: 22,
    username: process.env.DB_SSH_USER,
    password: process.env.DB_SSH_PASSWORD
}

If you have to connect to SSH using a permission file, use privateKey key in place of password in the tunnelConfig object.

Example:

const tunnelConfig = {
    host: process.env.DB_SSH_HOST,
    port: 22,
    username: process.env.DB_SSH_USER,
    privateKey:
    require('fs').readFileSync('<path to your permission file>')
}

Now, we will specify the forward configuration for SSH.

// dbConfig.js
...
const forwardConfig = {
    srcHost: '127.0.0.1', // any valid address
    srcPort: 3306, // any valid port
    dstHost: dbServer.host, // destination database
    dstPort: dbServer.port // destination port
};

We are good to set up an SSH connection now.

// dbConfig.js
const mysql = require('mysql2');
const { Client } = require('ssh2');
// create an instance of SSH Client
const sshClient = new Client();
...
const SSHConnection = new Promise((resolve, reject) => {
    ssh.on('ready', () => {
        ssh.forwardOut(
        forwardConfig.srcHost,
        forwardConfig.srcPort,
        forwardConfig.dstHost,
        forwardConfig.dstPort,
        (err, stream) => {
             if (err) reject(err);

            // create a new DB server object including stream
            const updatedDbServer = {
                 ...dbServer,
                 stream
            };
            // connect to mysql
            const connection =  mysql.createConnection(updatedDbServer);
            // check for successful connection
           //  resolve or reject the Promise accordingly          
           connection.connect((error) => {
            if (error) {
                reject(error);
            }
            resolve(connection);
            });
       });
    }).connect(tunnelConfig.sshConfig);
});

Final Database Config File:

const mysql = require('mysql2');
const { Client } = require('ssh2');
const sshClient = new Client();
const dbServer = {
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    user: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE
}
const tunnelConfig = {
    host: process.env.DB_SSH_HOST,
    port: 22,
    username: process.env.DB_SSH_USER,
    password: process.env.DB_SSH_PASSWORD
}
const forwardConfig = {
    srcHost: '127.0.0.1',
    srcPort: 3306,
    dstHost: dbServer.host,
    dstPort: dbServer.port
};
const SSHConnection = new Promise((resolve, reject) => {
    ssh.on('ready', () => {
        ssh.forwardOut(
        forwardConfig.srcHost,
        forwardConfig.srcPort,
        forwardConfig.dstHost,
        forwardConfig.dstPort,
        (err, stream) => {
             if (err) reject(err);
             const updatedDbServer = {
                 ...dbServer,
                 stream
            };
            const connection =  mysql.createConnection(updatedDbServer);
           connection.connect((error) => {
            if (error) {
                reject(error);
            }
            resolve(connection);
            });
        });
    }).connect(tunnelConfig.sshConfig);
});

Now, SSHConnection Promise can be used wherever required.

Originally published on my website.

Comments

[Matt Voboril]

This was super close to what I'm trying to do. I need something that will produce a connection pool, not just a single connection.

I've tried doing this...

const defaults = {
    db: {
        host: 'localhost',
        port: 3306
    },
};

const tunnel = {}; // some meta object that i'm using to store connections.

const createPool = async (sshConfig, dbConfig) => {
        return new Promise((resolve, reject) => {
            tunnel._conn = new Client();
            tunnel._conn.on('ready', () => {
                tunnel._conn.forwardOut(
                  '127.0.0.1',
                  3306,
                  dbConfig.host,
                  dbConfig.port,
                  (err, stream) => {
                      if (err) {
                          tunnel.close()
                          return reject(err.reason === 'CONNECT_FAILED'
                            ? 'Connection failed.'
                            : err);
                      }

                      // override db host, since we're operating from within the SSH tunnel
                      tunnel._sql = mysql.createPool({
                          ...defaults.db,
                          ...dbConfig,
                          stream,
                      });
                      resolve(tunnel._sql);
                  }
                )
            }).connect(sshConfig)
        })
    }

but whenever I connect, I get

Warning: got packets out of order. Expected 0 but received 1
TypeError: Cannot read property 'slice' of undefined {whole bunch of stack trace}

The database config I'm using works if I manually forward my local 3306 to the remote server.