How can you await the result of a js callback (using the Snowflake SQL API)?

Question

I'm not sure if I'm overcomplicating this, but I don't do js very often... I'm trying to use the Snowflake SQL Node.js library, which includes this example for consuming SQL query results:

https://docs.snowflake.com/en/user-guide/nodejs-driver-use.html#consuming-results

connection.execute({
  sqlText: 'select * from sometable',
  complete: function(err, stmt, rows) {
    if (err) {
      console.error('Failed to execute statement due to the following error: ' + err.message);
    } else {
      console.log('Number of rows produced: ' + rows.length);
    }
  }
});

I also need to use SSO to auth to the DB, for which they provide this example, which will spawn a browser window when it's run:

https://docs.snowflake.com/en/user-guide/nodejs-driver-use.html#using-single-sign-on-sso-through-a-web-browser

// Use a browser to authenticate via SSO.
var connection = snowflake.createConnection({
  ...,
  authenticator: "EXTERNALBROWSER"
});
// Establish a connection. Use connectAsync, rather than connect.
connection.connectAsync(
  function (err, conn)
  {
    ... // Handle any errors.
  }
).then(() =>
{
  // Execute SQL statements.
  var statement = connection.execute({...});
});

Here's the problem: I want to return the result of a SELECT statement back to some other code I'm running. I can do something like this:

async function querySnowflake(input) {
  const query = `...`
  var connection = snowflake.createConnection({...});
  await connection.connectAsync(
    function (err, conn) {
      if (err) {
        console.error('Connection to Snowflake failed');
      } else {
        var statement = connection.execute({
          sqlText: query,
          complete: function (err, stmt, rows) {
            if (err) {
              console.error('Failed to execute statement due to the following error: ' + err.message);
            } else {
              return rows;
            }
          }
        });
      }
    }
  );

But I have no idea how to return the rows selected by the query back to the part of my code that calls querySnowflake() in the first place, because if I await the connection itself, the function gets returned immediately after I perform the auth function, and the callback never fires. I think that both the documentation and my creativity in using js async are lacking, and I'm not sure how to proceed here...

score 0 · Answer 1 · answered Apr 11 '22 at 10:25

looking at the docs, then looking at your last snippet, does changing it to this help?

function querySnowflake(input) { //if you're awaiting this function, no worries since I'm returning a promise
  let query = `...`, resolve=null
  let toReturn = new Promise(r=>resolve=r)
  var connection = snowflake.createConnection({...});
  connection.connectAsync(
    function (err, conn) {
    if (err) {
      console.error('Connection to Snowflake failed');
    }
  })
  .then(()=>{
    var statement = connection.execute({
      sqlText: query,
      complete: function (err, stmt, rows) {
        if (err) {
          console.error('Failed to execute statement due to the following error: ' + err.message);
        } else {
          return resolve(rows);
        }
      }  
    })
  })
  return toReturn //the promise that should get resolved with rows data
}

How can you await the result of a js callback (using the Snowflake SQL API)?

1 Answers1