SNOW-1016934: support setting session variables from Connection

[dhpcc]

Please answer these questions before submitting your issue.
In order to accurately debug the issue this information is required. Thanks!

1. What version of NodeJS driver are you using?
   1.9.0

2. What operating system and processor architecture are you using?
   Mac M2

3. What version of NodeJS are you using?
   (node --version and npm --version)
   v21.6.0

4. What are the component versions in the environment (npm list)?

5.Server version:* E.g. 1.90.1
You may get the server version by running a query:

SELECT CURRENT_VERSION();

6. What did you do?

snowflake.createConnection() receives a config object that specifies a key/value for the session parameter.

const snowflake = require('snowflake-sdk');

const createSnowflakeConnectionWithSessionVar = (orgKey, orgValue) => {
  const {
    SNOWFLAKE_ACCOUNT_IDENTIFIER,
    SNOWFLAKE_USERNAME,
    SNOWFLAKE_PASSWORD,
  } = require('../utils/config');

  // Create a new connection
  const connection = snowflake.createConnection({
    account: SNOWFLAKE_ACCOUNT_IDENTIFIER,
    username: SNOWFLAKE_USERNAME,
    password: SNOWFLAKE_PASSWORD,
    application: 'CORE_PLATFORM',
    clientSessionKeepAlive: true,
    clientSessionKeepAliveHeartbeatFrequency: 3600,
    sessionParameters: {
      [orgKey]: orgValue,
    },
  });

  // Return a Promise that resolves with the connection
  return new Promise((resolve, reject) => {
    connection.connect((err, conn) => {
      if (err) {
        reject(err);
      } else {
        resolve(conn);
      }
    });
  });
};

module.exports = createSnowflakeConnectionWithSessionVar;

Usage in backend middleware function:

try {
    const conn = await createSnowflakeConnectionWithSessionVar('client', org);

    conn.execute({
      sqlText: query,
      complete: function (err, stmt, rows) {
        if (err) {
          console.error('Failed to execute query', err);
          next(err);
        } else {
          const random = rows.map((row) => row.RANDOM);
          res.locals.random = random;
          next();
        }
        conn.destroy((err, conn) => {
          if (err) {
            console.error('Failed to close connection', err);
          }
        });
      },
    });
  } catch (error) {
    console.error('Connection error', error);
    next(error);
  }

7. What did you expect to see?

I expected the session parameter to be set to whatever I call this function with. Instead, the session parameter remains undefined. I looked through the API documentation and can't seem to find how to do this.
Snowflake Session Variables

What should have happened and what happened instead?

9. Can you set logging to DEBUG and collect the logs?

   https://community.snowflake.com/s/article/How-to-generate-log-file-on-Snowflake-connectors

e.g
Add this to get standard output.

var snowflake = require('snowflake-sdk');
snowflake.configure(
{
  logLevel: 'trace'
});

10. What is your Snowflake account identifier, if any? (Optional)

[sfc-gh-dszmolka]

hi and thank you for submitting this issue !

contrary to how the Snowflake JDBC driver supports setting session variables on the connection, the Node.js driver does not at this moment, it does not even support setting custom session parameters yet. (#61)

I'll marked this request as a feature/enhancement request for the team to pick up some day if it'll be deemed something which we'll want to implement, but at this moment I cannot attach any estimated timelines for this request.
If you're already a Snowflake customer, you can reach out to your account team and let them know how important is it to you; it might help with prioritising it for implementation.

But back to the issue, what can you do while the capability gets implemented ? You can utilize the multi-statement functionality as a workaround , to define the session variable as the first SQL statement you execute, then execute the rest of them depending on it.

I created this simple script to demonstrate the functionality:

# cat multi-statement-with-session-variables.js 
var snowflake = require('snowflake-sdk');
require('log-timestamp');

console.log(`\nNode.js process version is: ${process.version}\n`);

var connection = snowflake.createConnection({
    validateDefaultParameters: true,
    account: process.env.SFACCOUNT,
    username: process.env.SFUSER,
    password: process.env.SFPASS,
    warehouse: process.env.SFWH,
    database: process.env.SFDB,
    schema: process.env.SFSCHEMA,
    application: __filename.slice(__dirname.length + 1)
});
connection.connect(function(err, conn) {
    if (err) {
        console.error('Unable to connect: ' + err.message);
    } else {
        console.log('Successfully connected.');
    }
});

const orgKey = 'client';
const orgValue = '\'org\'';

var selectStatement = connection.execute({
    sqlText: `SET ${orgKey}=${orgValue}; SELECT 1 as one; SELECT 2 as two; SELECT $${orgKey} as result;`,
    parameters: { MULTI_STATEMENT_COUNT: 0 },
    complete: function (err, stmt, rows) {
      if (err) {
        console.error('1 Failed to execute statement due to the following error: ' + err.message);
      }
      else {
    console.log(rows);
        if (stmt.hasNext())
        {
          stmt.NextResult();
        }
        else {
          // do something else, e.g. close the connection
        }
      }
    }
});

but probably you'll want to wrap the connection.execute as you did in your example to take the arguments in, which in turn will be passed as key/value for the multi-statement query's first statement, where you set the session variable. Further (driver-agnostic) documentation on running SELECT on session variable is available here.

Running the above simple script results in:

# node multi-statement-with-session-variables.js 
[2024-01-24T10:46:44.731Z] 
Node.js process version is: v18.16.1

[2024-01-24T10:46:45.257Z] Successfully connected.
[2024-01-24T10:46:46.869Z] [ { status: 'Statement executed successfully.' } ]
[2024-01-24T10:46:47.010Z] [ { ONE: 1 } ]
[2024-01-24T10:46:47.100Z] [ { TWO: 2 } ]
[2024-01-24T10:46:47.219Z] [ { RESULT: 'org' } ]

Hopefully this alternative approach with multi-statement queries helps getting you unblocked while the feature gets implemented.