Node JS: How to access MySQL remotely using SSH.
Tags: database, sql, ssh, javascript
Published: 2020-06-16 05:56:06

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](https://www.npmjs.com/package/ssh2) and [mysql2](https://www.npmjs.com/package/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](https://akanksha.io/post/node-js-how-to-access-mysql-remotely-using-ssh).