MySQL connection using SSH in Nodejs

Question

I don`t understand how to create the mysql connection via ssh and export it to access from another file. This is my database.js file, here I created the ssh tunnel. Si I need to access to conn variable from my controller.

const mysql = require("mysql2");
const { Client } = require("ssh2");
const { database, sshConfig, forwardConfig } = require("./keys");

// create an instance of SSH Client
const sshClient = new Client();

const connection = 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 = {
            ...database,
            stream,
          };
          var conn = mysql.createConnection(updatedDbServer);
          conn.connect(function (err) {
            if (err) {
              reject(err);
            } else {
              resolve(conn);
            }
          });
        }
      );
    })
    .connect(sshConfig);
});

module.exports = connection;

But when I use connection in my controller.js file, shows me "TypeError: connection.query is not a function"

const connection = require("../db/database");
    
function getUsers(req, res) {
  try {
    let sqlQuery = `Select * from sUsers`;
    console.log(connection);
    connection().then((conn) => {
      // query database

      conn.query(sqlQuery, (error, results) => {
        if (error) {
          console.log(error);
          return;
        }
        res.json({
          ok: true,
          results,
        });
      });
    });
  } catch (err) {
    console.log(`Error1: ${err}`);
    return res.status(400).json({
      ok: false,
      err,
    });
  }
}

Answer 1

Using sql2 and ssh2, ssh db connection is set up. Make sure that you have installed both these packages and before running following controller script, be connected to your organisation vpn if any.

There will be three files: .env, SSHDBConfig js and controller js file.

1. Create dotenv file that have all necessary hostname, username, password data.
2. Create SSHDBConfig.js to form a general connection by using sql2 and ssh2.

   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 sshTunnelConfig = {
       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 SSHDBConnection = 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) {
                           console.log("error---", error);
                           reject(error);
                       }
                       console.log("Connection Successful");
                       resolve(connection);
                   });
               });
       }).connect(sshTunnelConfig);
   });
   module.exports = SSHDBConnection;

3. Now from your controller file, just import above ceated connection and call it as follows

   const myDBConnectionClient = require('./SSHDBConfig');
   const dbCall = () => {
       myDBConnectionClient.then((conn) => {
           conn.query(`SQL_QUERY_STATEMENT`, (err, result, fields) => {
               if (err) throw err;
               console.log("SQL Query Result-- ", result);
               if (result.length !== 0) {  //considering SQL Select statement
                   result = result[0];
                   //perform your required work on result
               }
   
           });
       })
   
   }
   dbCall();
   

Answer 2

connection is a Promise. You need to wait for it to be resolved or rejected first. If it's resolved, then you need to use the actual connection object that's passed to your then() handler.

Additionally, I'm guessing you meant to do resolve(conn) instead of resolve(connection) when you successfully connect to the database server (and the return conn is unnecessary as the return value gets ignored).

Lastly, because module.exports is an object containing connection, you either need to change to module.exports = connection; or use const { connection } = require("../db/database"); in controller.js.