Ok, so I need to connect to a MySQL database through SSH and the connection works fine. I am able to execute queries with no problem. I can also get the results and print it out. The thing is, I need something simpler since I will have to send a lot of queries to this database. Below is the code for a promise which creates a connection to this database.

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);   // Return error
                    }
                    resolve(connection); // OK : return connection to database
                });
        });
    }).connect(tunnelConfig);
});

Then I have this piece of code that gives me access to said connection and allows me to send queries. The problem is that I need the return value of my queries and be able to use it in other modules for my project. For example, export a single function to be used to send queries like sendQuery('Enter SQL here').

function sendQuery(sql) {
    SSHConnection.then(
        function(connection) {
            connection.query(
                sql,
                function(err, results, fields) {
                    return results; // <---------- I want to return this from the function 'sendQuery()'
                }
            );
        },
        function(error) {
            console.log("Something wrong happened");
        }
    );
}

I can work with the results inside SSHConnection.then() but this isn't functional for me. I would like to have something similar below to work.

// Main function
(async function() {
    let res = sendQuery(`SELECT 23 2 AS Sum;`);
    console.log(res); // Outputs Sum: 25
})();

So to my question. Is there a way to access the results from a query inside of a promise.then(), from the outside?

CodePudding user response：

I think the problem is you need to add another return statement to your code.

function sendQuery(sql) {
    return SSHConnection.then(
        function(connection) {
            return connection.query(
                sql,
                function(err, results, fields) {
                    return results; // <---------- I want to return this from the function 'sendQuery()'
                }
            );
        },
        function(error) {
            console.log("Something wrong happened");
        }
    );
}

This should return the results from the query properly IF connection.query returns a promise. I'm not sure if it does. If it does then you can just execute the function like so.

// Main function
(async function() {
    let res = await sendQuery(`SELECT 23 2 AS Sum;`);
    console.log(res); // Outputs Sum: 25
})();

If connection.query does not return a promise then I suppose you could wrap it in a promise like so.

function sendQuery (sql) {
  return SSHConnection.then(
    function (connection) {
      return new Promise((resolve, reject) => {
        connection.query(
          sql,
          function (err, results, fields) {
            if (err)reject(err)
            resolve(results) // <---------- I want to return this from the function 'sendQuery()'
          }
        )
      })
    },
    function (error) {
      console.log('Something wrong happened')
    }
  )
}

CodePudding user response：

love the name by the way...really liked that movie. As to your question, I'd suggest a couple of things:

1. if you have a lot of queries coming, you might consider moving the connection independent of the query, so that the connection setup and teardown isn't part of the cost of time for the query itself. If you have a single connection, single DB, etc., then you could instantiate the connection once, at startup, and then leave the connection open, and reference it in your queries.

2. your question:

function sendQuery(sql) {
    const resValues = await SSHConnection.then(
        function(connection) {
            connection.query(
                sql,
                function(err, results, fields) {
                    return results; // <---------- I want to return this from the function 'sendQuery()'
                }
            );
        },
        function(error) {
            console.log("Something wrong happened");
        }
    );
    return(resValues); // or handle error cases
}

Note I added a return value from the ".then()" call, which captures your "results" return value, and then returns that from the parent function (sendQuery)