Managing connections¶
To execute statements against Snowflake, you first need to establish a connection. The Snowflake Node.js Driver lets you establish connections as follows:
Important
Beginning with Snowflake version 8.24, network administrators have the option to require multi-factor authentication (MFA) for all connections to Snowflake. If your administrator decides to enable this feature, you must configure your client or driver to use MFA when connecting to Snowflake. For more information, see the following resources:
Creating a single connection¶
To create a single connection to Snowflake:
Call
snowflake.createConnection
to create a newConnection
object, and pass in a JavaScript object that specifies the connection options.Using the
Connection
object, call theconnect
method to establish a connection.Note
If you set the
authenticator
option toEXTERNALBROWSER
(in order to use browser-based SSO) orhttps://<okta_account_name>.okta.com
(in order to use native SSO through Okta), call theconnectAsync
method, rather than theconnect
method.To handle connection errors, pass in a callback function that has the following signature:
function(err, conn)
where:
err
is a JavaScriptError
object.conn
is the currentConnection
object.
If an error occurs during connection, the
connect
method passes anError
object to your callback function. You can use this object in your callback function to get details about the error. If you need information about the currentConnection
object, you can use theconn
argument passed to your callback function.
The following example establishes a connection and uses a password for authentication. To use other authentication methods, see Authentication options.
// Load the Snowflake Node.js driver. var snowflake = require('snowflake-sdk');// Create a Connection object that we can use later to connect. var connection = snowflake.createConnection({ account: account, username: user, password: password, application: application });// Try to connect to Snowflake, and check whether the connection was successful. connection.connect( function(err, conn) { if (err) { console.error('Unable to connect: ' + err.message); } else { console.log('Successfully connected to Snowflake.'); // Optional: store the connection ID. connection_ID = conn.getId(); } } );
When creating a connection, you can set the connection options as described in Options Reference.
Verifying that a connection is ready to receive queries¶
Before submitting Snowflake queries, you can use the connection.isValidAsync()
method (in version 1.6.23 and later)
to ensure the connection is up
and ready to execute requests on Snowflake. The method returns true
if the connection is ready or false
otherwise.
// Create a Connection object and connect to Snowflake
// ..
// Verify if connection is still valid for sending queries over it
const isConnectionValid = await connection.isValidAsync();
// Do further actions based on the value (true or false) of isConnectionValid
Creating a connection pool¶
Instead of creating a connection each time your client application needs to access Snowflake, you can define a cache of Snowflake connections to reuse as needed. Connection pooling usually reduces the lag time to make a connection. However, it can slow down client failover to an alternative DNS when a DNS problem occurs.
To create a connection pool:
Call
snowflake.createPool(connectionOptions, poolOptions)
to create a newConnectionPool
object, and pass in two JavaScript objects that specify the connection options and pool options.Note
The Snowflake Node.js Driver uses the open-source node-pool library for implementing connection pools. For information about the supported
poolOptions
, see the description of theopts
argument in the node-pool library documentation.With the
ConnectionPool
object, call theuse
function to execute statements for a single connection in the connection pool.To handle connection errors, pass in a callback function that has the following signature:
function(err, stmt, rows)
where:
err
is a JavaScriptError
object.stmt
is an object with information about the SQL statement that was executed, including the literal text of the statement.rows
is an array containing the “result set” of the statement.
If an error occurs while executing the statement, the
connect
method passes anError
object to your callback function. You can use this object in your callback function to get details about the error.
The following example creates a connection pool that supports a maximum of ten active connections. It uses a password for authentication. To use other authentication methods, see Authentication options.
// Create the connection pool instance const connectionPool = snowflake.createPool( // connection options { account: account, username: user, password: password }, // pool options { max: 10, // specifies the maximum number of connections in the pool min: 0 // specifies the minimum number of connections in the pool } );
The following example uses the connectionPool.use
method to execute a SQL statement using the connections in the pool.
The clientConnection.execute
method specifies the SQL statement to execute and defines a callback function.
// Use the connection pool and execute a statement connectionPool.use(async (clientConnection) => { const statement = await clientConnection.execute({ sqlText: 'select 1;', complete: function (err, stmt, rows) { var stream = stmt.streamRows(); stream.on('data', function (row) { console.log(row); }); stream.on('end', function (row) { console.log('All rows consumed'); }); } }); });
When creating a connection pool, you can set the connection options as described in Options Reference.
Handling idle connections¶
With the default setting of the node-pool’s evictionRunIntervalMillis
option set to 0, idle connection eviction checks are not run. If you have a longer running application, this behavior can lead to terminated connections lingering around in the connection pool, which when the driver acquires them and tries to send a query over them to Snowflake, causes an error.
To address this behavior in a long-running application, you could consider the following ways to handle it:
Create the Snowflake
ConnectionPool
with an enabled evictor.You can add the
evictionRunIntervalMillis
option to the pool options, as shown in the following example:const pool = snowflake.createPool( { account: account, username: username, //rest of the connection options }, { evictionRunIntervalMillis: 60000 // default = 0, off idleTimeoutMillis: 60000, // default = 30000 max: 2, min: 0, }, );
This example runs the evictor every minute and evicts any connections that are idle for more than one minute. You can also tweak
numTestsPerEvictionRun
(default: 3) to change the number of resources checked during each eviction run.See the node-pool library
documentation < https://github.com/coopernurse/node-pool/blob/master/README.md>
for details and more options.Keep existing connections alive in the pool
If you need to keep a connection alive more frequently than every hour, you can add the following to the pool options:
clientSessionKeepAlive: true
clientSessionKeepAliveHeartbeatFrequency: n
, wheren
is between 900 (15m) and 3600 (1h) seconds (default: 3600).
The following example sends a keep-alive heartbeat every 15 minutes to keep the connection alive even if no other activity, such as a query from a client, occurs.
const pool = snowflake.createPool( { account: account, username: username, // rest of the connection options clientSessionKeepAlive: true, // default = false clientSessionKeepAliveHeartbeatFrequency: 900 // default = 3600 }, { max: 2, min: 0, }, );
You can also use the
clientSessionKeepAlive
option without using pooled connections.For more information about the session keep-alive, see Node.js options reference.
Connecting through a proxy¶
You can connect to Snowflake through a proxy, by supplying the details as connection options when creating a Connection
object.
The following example shows how to connect to a proxy using HTTP:
var connection = snowflake.createConnection({
account: "account",
username: "user",
password: "password",
proxyHost: "localhost",
proxyPort: 3128,
});
Beginning with version 1.15.0, the Snowflake Node.js driver fully supports the HTTP_PROXY
, HTTPS_PROXY
, and NO_PROXY
environment variables in addition to their corresponding connection parameters.
By default, the new useEnvProxy
global configuration setting is set to true
, which enables support for the environment variables.
With the ability to set these proxies both in the Connection
object and in the environment variables, the driver uses the following hierarchy to determine which values to use:
If a proxy is defined in the
Connection
, it takes precedence. The driver ignores theHTTP_PROXY
andHTTPS_PROXY
environment variables.If the Connection does not set the proxy values, the driver uses the values in the
HTTP_PROXY
andHTTPS_PROXY
environment variables if they are defined.If the
useEnvProxy
connection setting is set tofalse
, the driver ignoresHTTP_PROXY
andHTTPS_PROXY
environment variables if they are defined.
If you want to disable support for proxy environment variables, you must disable it in the global configuration, as follows:
const snowflake=require('snowflake-sdk');
snowflake.configure({ useEnvProxy: false });
Note
The environmental variables are case-sensitive on Linux and MacOS. On Windows, they are not.
If both the lower-case (
https_proxy
) and upper-case (HTTPS_PROXY
) variants are defined for the same environment variable, the driver uses the value from the lower-case (https_proxy
) variable.If only the upper-case (
HTTPS_PROXY
) variant is present, the driver use the upper-case variable’s value.
Connecting through an authenticated proxy¶
You can connect to Snowflake through an authenticated proxy by supplying authentication credentials as connection
options when creating a Connection
object.
Note
Connecting through an authenticated proxy server is supported starting with version 1.6.4 of the Snowflake Node.js Driver.
The following example shows how to connect to an authenticated proxy using HTTP:
var connection = snowflake.createConnection({
account: "account",
username: "user",
password: "password",
proxyHost: "localhost",
proxyPort: 3128,
proxyUser: "myname",
proxyPassword: "mypass"
});
To connect to an authenticated proxy using HTTPS you must also provide the proxyProtocol
connection property as shown below:
var connection = snowflake.createConnection({
account: "account",
username: "user",
password: "password",
proxyHost: "localhost",
proxyPort: 3128,
proxyUser: "myname",
proxyPassword: "mypass",
proxyProtocol: "https"
});
Verifying the network connection to Snowflake with SnowCD¶
After configuring your driver, you can evaluate and troubleshoot your network connectivity to Snowflake using SnowCD.
You can use SnowCD during the initial configuration process and on-demand at any time to evaluate and troubleshoot your network connection to Snowflake.
OCSP (online certificate status protocol)¶
When the driver connects, Snowflake sends a certificate to confirm that the connection is to Snowflake rather than to a host that is impersonating Snowflake. The driver sends that certificate to an OCSP (Online Certificate Status Protocol) server to verify that the certificate has not been revoked.
If the driver cannot reach the OCSP server to verify the certificate, the driver can “fail open” or “fail closed”.
Terminating a connection¶
A connection can be terminated by calling the connection.destroy()
method. This immediately ends the session associated with the connection without waiting for running statements to complete:
connection.destroy(function(err, conn) { if (err) { console.error('Unable to disconnect: ' + err.message); } else { console.log('Disconnected connection with id: ' + connection.getId()); } });