Node JS: How to access MySQL remotely using SSH.

Akanksha Priyadarshini
Jun 8 · 2 min read
Image for post
Image for post

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.jsconst mysql = require('mysql2');
const { Client } = require('ssh2');
// create an instance of SSH Client
const sshClient = new Client();
...const SSHConnection = new Promise((resolve, reject) => {
sshClient.on('ready', () => {
sshClient.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);
});

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) => {
sshClient.on('ready', () => {
sshClient.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);
});

Now,SSHConnection Promise can be used wherever required.

Originally Published on my website.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store