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,
    });
  }
}

mscdex · Accepted Answer · 2021-02-06 15:44:51Z

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.

edited Feb 6, 2021 at 15:44

answered Feb 6, 2021 at 6:02

mscdex

107k15 gold badges201 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Erick Fariñas Over a year ago

I have made all the changes, but I keep getting the error "connection is not a function"

mscdex Over a year ago

connection is a Promise, not a function.

UdayanBKamble · Accepted Answer · 2022-07-26 11:44:54Z

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.

Create dotenv file that have all necessary hostname, username, password data.
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;

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();

Collectives™ on Stack Overflow

MySQL connection using SSH in Nodejs

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related