Using the Node.js Driver¶

The typical workflow for using the driver is:

Create a connection to Snowflake.
Run statements (e.g. SQL queries or DDL/DML commands).
Consume the query results returned from Snowflake.
Terminate the connection.

Important

To upload and download files from a Snowflake stage, you must use the following minimum versions of the driver:

Version 1.6.2 to upload files (using the PUT command)
Version 1.6.6 to download files (using the GET command)

In this Topic:

Establishing Connections
Connecting Through an Authenticated Proxy
Verifying the Network Connection to Snowflake with SnowCD
OCSP (Online Certificate Status Protocol)
- Choosing Fail-Open or Fail-Close Mode
- Verifying the OCSP Connector or Driver Version
Executing Statements
Consuming Results
Uploading a File to a Snowflake Stage
Terminating a Connection

Establishing Connections¶

To execute statements against Snowflake, you first need to establish a connection. The Snowflake Node.js Driver lets you establish connections in the following ways:

Create a single connection
Create a pool of connections

Creating a Single Connection¶

To create a single connection to Snowflake:

Call snowflake.createConnection to create a new Connection object, and pass in a JavaScript object that specifies the connection options.
Using the Connection object, call the connect method to establish a connection.

Note

If you set the authenticator option to EXTERNALBROWSER (in order to use browser-based SSO) or https://<okta_account_name>.okta.com (in order to use native SSO through Okta), call the connectAsync method, rather than the connect method.

To handle connection errors, pass in a callback function that has the following signature:
```
function(err, conn)
```
where:
- err is a JavaScript Error object.
- conn is the current Connection object.
If an error occurs during connection, the connect method passes an Error 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 current Connection object, you can use the conn 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 Setting the Connection Options.

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 that can be reused 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 new ConnectionPool 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 the opts argument in the node-pool library documentation.
With the ConnectionPool object, call the use function to execute statements for a single connecion 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 JavaScript Error 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 an Error 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 Setting the Connection Options.

Setting the Connection Options¶

When constructing a new Connection object, you pass in a JavaScript object that specifies the options for the connection (e.g. your account identifier, your user name, etc.). The following sections describe the options that you can set. To set an option, specify the option name as the property name in the JavaScript object.

Required Connection Options
Authentication Options
Additional Connection Options

Required Connection Options¶

account

Your account identifier.

username

The login name for your Snowflake user or your Identity Provider (e.g. your login name for Okta).

region (Deprecated)

The ID for the region where your account is located.

Note

This option is deprecated and is included here only for backward compatibility. Snowflake recommends transitioning to embedding the region in the account identifier, as described in Using an Account Locator as an Identifier, such as follows.

var connection = snowflake.createConnection({
  account: "myaccount.us-east-2",
  username: "myusername",
  password: "mypassword"
});

In addition, you must specify the options for authenticating to the server.

Authentication Options¶

application

Specifies the name of the client application connecting to Snowflake.

authenticator

Specifies the authenticator to use for verifying user login credentials. You can set this to one of the following values:

Value	Description
`SNOWFLAKE`	Use the internal Snowflake authenticator. You must also set the `password` option.
`EXTERNALBROWSER`	Use your web browser to authenticate with Okta, ADFS, or any other SAML 2.0-compliant identity provider (IdP) that has been defined for your account.
`https://<okta_account_name>.okta.com`	Use Native SSO through Okta.
`OAUTH`	Use OAuth for authentication. You must also set the `token` option to the OAuth token (see below).
`SNOWFLAKE_JWT`	Use key pair authentication. See Using Key Pair Authentication & Key Pair Rotation.

The default value is SNOWFLAKE.

For more information on authentication, see Managing/Using Federated Authentication and OAuth with Clients, Drivers, and Connectors.

password

Password for the user. Set this option if you set the authenticator option to SNOWFLAKE or the Okta URL endpoint for your Okta account (e.g. https://<okta_account_name>.okta.com) or if you left the authenticator option unset.

token: Specifies the OAuth token to use for authentication. Set this option if you set the authenticator option to OAUTH.
privateKey: Specifies the private key (in PEM format) for key pair authentication. For details, see Using Key Pair Authentication & Key Pair Rotation.
privateKeyPath: Specifies the local path to the private key file (e.g. rsa_key.p8). For details, see Using Key Pair Authentication & Key Pair Rotation.
privateKeyPass: Specifies the passcode to decrypt the private key file, if the file is encrypted. For details, see Using Key Pair Authentication & Key Pair Rotation.

Additional Connection Options¶

clientSessionKeepAlive

By default, client connections typically time out approximately 3-4 hours after the most recent query was executed.

If the clientSessionKeepAlive option is set to true, the client’s connection to the server will be kept alive indefinitely, even if no queries are executed.

The default setting of this option is false.

If you set this option to true, make sure that your program explicitly disconnects from the server when your program has finished. Do not exit without disconnecting.

clientSessionKeepAliveHeartbeatFrequency

(Applies only when clientSessionKeepAlive is true)

Sets the frequency (interval in seconds) between heartbeat messages.

You can loosely think of a connection heartbeat message as substituting for a query and restarting the timeout countdown for the connection. In other words, if the connection would time out after at least 4 hours of inactivity, the heartbeat resets the timer so that the timeout will not occur until at least 4 hours after the most recent heartbeat (or query).

The default value is 3600 seconds (one hour). The valid range of values is 900 - 3600. Because timeouts usually occur after at least 4 hours, a heartbeat every 1 hour is normally sufficient to keep the connection alive. Heartbeat intervals of less than 3600 seconds are rarely necessary or useful.

database

The default database to use for the session after connecting.

proxyHost

Specifies the hostname of an authenticated proxy server.

proxyPassword

Specifies the password for the user specified by proxyUser.

proxyPort

Specifies the port of an authenticated proxy server.

proxyProtocol

Specifies the protocol used to connect to the authenticated proxy server. Use this property to specify the HTTP protocol: http or https.

proxyUser

Specifies the username used to connect to an authenticated proxy server.

resultPrefetch

Number of threads for clients to use to prefetch large result sets. Valid values: 1-10.

role

The default security role to use for the session after connecting.

schema

The default schema to use for the session after connecting.

timeout

Number of seconds to keep the connection alive with no response. Default: 60.

warehouse

The default virtual warehouse to use for the session after connecting. Used for performing queries, loading data, etc.

Some connection options assume that the specified database object (database, schema, warehouse, or role) already exists in the system. If the specified object does not exist, a default is not set during connection.

After connecting, all of the optional connection options can also be set or overridden through the USE <object> command.

Authenticating to Snowflake¶

To authenticate to Snowflake, you can use one of the following options:

Password-based authentication. To use this, set the password option when establishing the connection.
Single sign-on (SSO) through a web browser.
Native SSO through Okta.
Key pair authentication.
OAuth.

Using Single Sign-on (SSO) Through a Web Browser¶

If you have configured Snowflake to use single sign-on (SSO), you can configure your client application to use browser-based SSO for authentication.

In your application code:

Set the authenticator option to EXTERNALBROWSER.
To establish a connection, call the connectAsync method, rather than the connect method.

For example:

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

For more information about using browser-based SSO for authentication, see Browser-based SSO.

Using Native SSO Through Okta¶

If you have configured Snowflake to use single sign-on (SSO) through Okta, you can configure your client application to use native SSO authentication through Okta.

In your application code:

Set the following options:
- Set the authenticator option to the Okta URL endpoint for your Okta account (e.g. https://<okta_account_name>.okta.com).
- Set the username and password options to the user name and password for your Identity Provider (IdP).
To establish a connection, call the connectAsync method, rather than the connect method.

For example:

// Use native SSO authentication through Okta.
var connection = snowflake.createConnection({
  ...,
  username: '<user_name_for_okta>',
  password: '<password_for_okta>',
  authenticator: "https://myaccount.okta.com"
});

// Establish a connection.
connection.connectAsync(
  function (err, conn)
  {
    ... // Handle any errors.
  }
);

// Execute SQL statements.
var statement = connection.execute({...});

For more information about using native SSO authentication through Okta, see Native SSO — Okta Only.

Using Key Pair Authentication & Key Pair Rotation¶

The driver supports key pair authentication and key rotation. In order to use this:

Configure key pair authentication, as explained in Key Pair Authentication & Key Pair Rotation.
In your application code:
1. Set the authenticator option to SNOWFLAKE_JWT.
2. Use the private key to authenticate in one of the following ways:
  - Set the privateKey option to the private key.
  - Set the privateKeyPath option to the path to the private key file.
    
    If the file is encrypted, you must also set the privateKeyPass option to the passphrase to decrypt the private key.

The following example loads the private key from a file and sets the privateKey option to the private key:

// Read the private key file from the filesystem.
var crypto = require('crypto');
var fs = require('fs');
var privateKeyFile = fs.readFileSync('<path_to_private_key_file>/rsa_key.p8');

// Get the private key from the file as an object.
const privateKeyObject = crypto.createPrivateKey({
  key: privateKeyFile,
  format: 'pem',
  passphrase: 'passphrase'
});

// Extract the private key from the object as a PEM-encoded string.
var privateKey = privateKeyObject.export({
  format: 'pem',
  type: 'pkcs8'
});

// Use the private key for authentication.
var connection = snowflake.createConnection({
  ...
  authenticator: "SNOWFLAKE_JWT",
  privateKey: privateKey
});

// Establish a connection.
connection.connect(
  function (err, conn)
  {
    ... // Handle any errors.
  }
);

// Execute SQL statements.
var statement = connection.execute({...});

The following example sets the privateKeyPath option to an encrypted private key file and sets the privateKeyPass option to the passphrase used to decrypt the private key:

// Use an encrypted private key file for authentication.
// Specify the passphrase for decrypting the key.
var connection = snowflake.createConnection({
  ...
  authenticator: "SNOWFLAKE_JWT",
  privateKeyPath: "<path-to-privatekey>/privatekey.p8",
  privateKeyPass: '<passphrase_to_decrypt_the_private_key>'
});

// Establish a connection.
connection.connect(
  function (err, conn)
  {
    ... // Handle any errors.
  }
);

// Execute SQL statements.
var statement = connection.execute({...});

Using OAuth¶

To connect using OAuth, set the authenticator option to OAUTH and the token option to the OAuth access token. For example:

// Use OAuth for authentication.
var connection = snowflake.createConnection({
  ...
  authenticator: "OAUTH",
  token: "<your_oauth_token>"
});

// Establish a connection.
connection.connect(
  function (err, conn)
  {
    ... // Handle any errors.
  }
);

// Execute SQL statements.
var statement = connection.execute({...});

For more information, see OAuth with Clients, Drivers, and Connectors.

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”.

Choosing Fail-Open or Fail-Close Mode¶

Node.js Driver versions prior to 1.2.0 default to fail-close. Versions 1.2.0 and later default to fail-open. You can override the default behavior by setting the ocspFailOpen global parameter before calling the snowflake.createConnection() method. You can set the parameter to true or false.

snowflake.configure( {ocspFailOpen: false} );
const connection = snowflake.createConnection(
    {
    account: <account_identifier>,
    ...
    }
);

Verifying the OCSP Connector or Driver Version¶

For more information about the driver or connector version, configuration, and OCSP behavior, see OCSP Configuration.

Executing Statements¶

Statements can be executed by calling the connection.execute() method. The execute() method accepts an options object that can be used to specify the SQL text and a complete callback. The complete callback is invoked when a statement has finished executing and the result is ready to be consumed:

var statement = connection.execute({
  sqlText: 'create database testdb',
  complete: function(err, stmt, rows) {
    if (err) {
      console.error('Failed to execute statement due to the following error: ' + err.message);
    } else {
      console.log('Successfully executed statement: ' + stmt.getSqlText());
    }
  }
});

Binding Statement Parameters¶

Occasionally, you might want to bind data in a statement with a placeholder. Executing statements in this manner is useful because it helps prevent SQL injection attacks. Consider the following statement:

connection.execute({
  sqlText: 'select c1 from (select 1 as c1 union all select 2 as c1) where c1 = 1;'
});

You can achieve the same result using the following bindings:

connection.execute({
  sqlText: 'select c1 from (select :1 as c1 union all select :2 as c1) where c1 = :1;',
  binds: [1, 2]
});

The ? syntax for bindings is also supported:

connection.execute({
  sqlText: 'select c1 from (select ? as c1 union all select ? as c1) where c1 = ?;',
  binds: [1, 2, 1]
});

Note

There is an upper limit to the size of data that you can bind, or that you can combine in a batch. For details, see Limits on Query Text Size.

Binding Array for Bulk Insert¶

Binding an array of data is supported for bulk INSERT operation. Pass an array of array as follows:

connection.execute({
  sqlText: 'insert into t(c1, c2, c3) values(?, ?, ?)',
  binds: [[1, 'string1', 2.0], [2, 'string2', 4.0], [3, 'string3', 6.0]]
});

Note

Binding a large array will impact performance and might be rejected if the size of data is too large to be handled by the server.

Canceling Statements¶

A statement can be canceled by calling the statement.cancel() method:

statement.cancel(function(err, stmt) {
  if (err) {
    console.error('Unable to abort statement due to the following error: ' + err.message);
  } else {
    console.log('Successfully aborted statement');
  }
});

Consuming Results¶

Returning Results Inline¶

The most common way of consuming results is by passing a complete callback to connection.execute(). When the statement has finished executing and the result is ready to be consumed, the complete callback is invoked with the result rows returned inline:

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

Streaming Results¶

It is also possible to consume a result as a stream of rows. This can be done by calling the statement.streamRows() method; this returns a Node.js Readable stream that can be used to consume rows as they are received. For more information about the Readable stream, see the Node.js documentation.

For example:

var statement = connection.execute({
  sqlText: 'select * from sometable'
});

var stream = statement.streamRows();

stream.on('error', function(err) {
  console.error('Unable to consume all rows');
});

stream.on('data', function(row) {
  // consume result row...
});

stream.on('end', function() {
  console.log('All rows consumed');
});

Batch Processing Results¶

By default, the statement.streamRows() method produces a stream that includes every row in the result. However, if you only want to consume a subset of the result, or if you want to consume result rows in batches, you can call streamRows() with start and end arguments. When these additional options are specified, only rows in the requested range are streamed:

connection.execute({
  sqlText: 'select * from sometable',
  streamResult: true, // prevent rows from being returned inline in the complete callback
  complete: function(err, stmt, rows) {
    // no rows returned inline because streamResult was set to true
    console.log('rows: ' + rows); // 'rows: undefined'

    // only consume at most the last 5 rows in the result
    rows = [];
    stmt.streamRows({
      start: Math.max(0, stmt.getNumRows() - 5),
      end: stmt.getNumRows() - 1,
    })
    .on('error', function(err) {
      console.error('Unable to consume requested rows');
    })
    .on('data', function(row) {
      rows.push(row);
    })
    .on('end', function() {
      console.log('Number of rows consumed: ' + rows.length);
    });
  }
})

Data Type Casting¶

When result rows are produced, the driver automatically maps SQL data types to their corresponding JavaScript equivalents. For example, values of type TIMESTAMP and DATE are returned as JavaScript Date objects.

For the full mapping of JavaScript to SQL data types, see the table below:

SQL Data Type

JavaScript Data Type

Notes

VARCHAR, CHAR, CHARACTER, STRING, TEXT

String

INT, INTEGER, BIGINT, SMALLINT

Number

This is the default mapping. Use the session parameter JS_TREAT_INTEGER_AS_BIGINT to map to JavaScript Bigint.

NUMBER(precision, scale), DECIMAL(p, s), NUMERIC(p, s) where scale = 0

Number

This is the default mapping. Use the session parameter JS_TREAT_INTEGER_AS_BIGINT to map to JavaScript Bigint.

NUMBER(precision, scale), DECIMAL(p, s), NUMERIC(p, s) where scale > 0

Number

FLOAT, FLOAT4, FLOAT8, DOUBLE, DOUBLE PRECISION, REAL

Number

TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ

Date

TIMESTAMP_NTZ values are returned in UTC.

DATE

Date

TIME

String

The TIME data type in SQL has no equivalent in JavaScript, so it is mapped to a JavaScript string.

BOOLEAN

Boolean

VARIANT, ARRAY, OBJECT

JSON

Fetching Integer Data Types as Bigint¶

By default, Snowflake INTEGER columns (including BIGINT, NUMBER(p, 0), etc.) are converted to JavaScript’s Number data type. However, the largest legal Snowflake integer values are larger than the largest legal JavaScript Number values. To convert Snowflake INTEGER columns to JavaScript Bigint, which can store larger values than JavaScript Number, set the session parameter JS_TREAT_INTEGER_AS_BIGINT.

There are two ways to set this parameter:

Use the ALTER SESSION statement, as shown below:

connection.execute( {
                    sqlText: 'ALTER SESSION SET JS_TREAT_INTEGER_AS_BIGINT = TRUE',
                    complete: function ...
                    }
                  );

Specify the parameter in the connection configuration information:

var connection = snowflake.createConnection(
      {
      accessUrl: 'http://myorganization-myaccount.snowflakecomputing.com',
      username: 'fakeusername',
      password: 'fakepassword',
      account: 'fakeaccountidentifier',
      jsTreatIntegerAsBigInt: true
      }
    );

Fetching Data Types as Strings¶

When connection.execute() is called, the fetchAsString option can be set to force all numbers or dates to be returned as strings. This can be used to get:

Formatted versions of values of type DATE and TIMESTAMP (or its variants).
String versions of numerical SQL types that can’t be converted to JavaScript numbers without loss in precision.

For example:

connection.execute({
  sqlText: 'select 1.123456789123456789123456789 as "c1"',
  fetchAsString: ['Number'],
  complete: function(err, stmt, rows) {
    if (err) {
      console.error('Failed to execute statement due to the following error: ' + err.message);
    } else {
      console.log('c1: ' + rows[0].c1); // c1: 1.123456789123456789123456789
    }
  }
});

Uploading a File to a Snowflake Stage¶

To upload a file to a Snowflake stage, use the PUT command.

The following example shows how to upload a file to an internal stage for a table named TABLE. As each row of the data is ingested, it is logged to the console.

connection.execute({
  sqlText: 'PUT file://C:\\Users\\Username\\Files\\employees0*.csv @DATABASE.SCHEMA.%TABLE;',
  complete: function (err)
  {
    var stream = statement.streamRows();
    stream.on('data', function (row)
    {
      console.log(row);
    });
    stream.on('end', function (row)
    {
      console.log('All rows consumed');
    });
  }
});

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