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