Using the Node.js Driver¶

The typical workflow for using the driver is:

Establish a connection with Snowflake.
Execute statements, e.g. queries and DDL/DML commands.
Consume the results.
Terminate the connection.

Important

The driver does not currently support the PUT or GET command and, therefore, cannot be used to upload or download files for data loading/unloading. Use SnowSQL (CLI Client) or the JDBC Driver instead.

In this Topic:

Establishing a Connection
- Required Connection Options
- Additional Connection Options
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
Terminating a Connection

Establishing a Connection¶

To execute statements against Snowflake, you first need to establish a connection.

For example:

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

// 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 options described in the section below.

Required Connection Options¶

account

The full name of your account (provided by Snowflake). Note that your full account name might include additional segments that identify the region and cloud platform where your account is hosted.

Account name examples by region

If your account name is xy12345:

Cloud Platform / Region	Full Account Name
AWS
US West (Oregon)	`xy12345`
US East (Ohio)	`xy12345.us-east-2.aws`
US East (N. Virginia)	`xy12345.us-east-1`
US East (Commercial Gov - N. Virginia)	`xy12345.us-east-1-gov.aws`
Canada (Central)	`xy12345.ca-central-1.aws`
EU (Ireland)	`xy12345.eu-west-1`
EU (Frankfurt)	`xy12345.eu-central-1`
Asia Pacific (Tokyo)	`xy12345.ap-northeast-1.aws`
Asia Pacific (Mumbai)	`xy12345.ap-south-1.aws`
Asia Pacific (Singapore)	`xy12345.ap-southeast-1`
Asia Pacific (Sydney)	`xy12345.ap-southeast-2`
GCP
US Central1 (Iowa)	`xy12345.us-central1.gcp`
Europe West2 (London)	`xy12345.europe-west2.gcp`
Europe West4 (Netherlands)	`xy12345.europe-west4.gcp`
Azure
West US 2 (Washington)	`xy12345.west-us-2.azure`
East US 2 (Virginia)	`xy12345.east-us-2.azure`
US Gov Virginia	`xy12345.us-gov-virginia.azure`
Canada Central (Toronto)	`xy12345.canada-central.azure`
West Europe (Netherlands)	`xy12345.west-europe.azure`
Switzerland North (Zurich)	`xy12345.switzerland-north.azure`
Southeast Asia (Singapore)	`xy12345.southeast-asia.azure`
Australia East (New South Wales)	`xy12345.australia-east.azure`

Important

If either of the following conditions is true, your account name is different than the structure described in this example:

If your Snowflake Edition is VPS, please contact Snowflake Support for details about your account name.
If AWS PrivateLink is enabled for your account, your account name requires an additional privatelink segment. For more details, see AWS PrivateLink & Snowflake.

username

Snowflake user login name to connect with.

password

Password for the user.

region — Deprecated

The ID for the region where your account is located.

This parameter is no longer used because the region information, if required, is included as part of the full account name. It is documented here only for backward compatibility.

Additional Connection Options¶

database

The default database to use for the session after connecting.

schema

The default schema to use for the session after connecting.

warehouse

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

role

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

clientSessionKeepAlive

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

If the parameter clientSessionKeepAlive 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 parameter is false.

If you set this parameter 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)

This parameter 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.

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 isn’t set during connection.

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

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

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://xy12345.snowflakecomputing.com',
      username: 'fakeusername',
      password: 'fakepassword',
      account: 'fakeaccount',
      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
    }
  }
});

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