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
as the 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.