Using the Node.js Driver

The typical workflow for using the driver is:

  1. Establish a connection with Snowflake.
  2. Execute statements, e.g. queries and DDL/DML commands.
  3. Consume the results.
  4. 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

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

For example:

When creating a connection, you can set the following options:

Required Connection Options

account

The full name of your account (provided by Snowflake). Depending on the cloud platform (AWS or Azure) and Snowflake Region where your account is hosted, the full account name may require additional segments:

Account name details
Structure of Snowflake account hostnames

For example, if your account name is xy12345:

Snowflake Region: Full Account Name:
AWS - US West xy12345
AWS - US East xy12345.us-east-1
AWS - Canada xy12345.ca-central-1
AWS - EU (Dublin) xy12345.eu-west-1
AWS - EU (Frankfurt) xy12345.eu-central-1
AWS - Asia Pacific (Singapore) xy12345.ap-southeast-1
AWS - Asia Pacific (Sydney) xy12345.ap-southeast-2
   
Azure - East US 2 xy12345.east-us-2.azure
Azure - US Gov Virginia xy12345.us-gov-virginia.azure
Azure - West Europe xy12345.west-europe.azure
Azure - Australia East xy12345.australia-east.azure

Important

If either of the following conditions is true, your account name is different from the structure described above:

  • Your Snowflake Edition is VPS. For account name details, please contact Snowflake Support.
  • AWS PrivateLink is enabled for your account, in which case your account name requires an additional privatelink segment. For more details, see AWS PrivateLink & Snowflake.

For more details about regions and platforms, see Snowflake Regions and Cloud Platforms.

username
Snowflake user login name to connect with.
password
Password for the user.
regionDeprecated

The ID for the Snowflake 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

This parameter applies only when clientSessionKeepAlive is true.

This parameter sets the frequency (the 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 3 hours of inactivity, the heartbeat resets the timer so that the timeout won’t occur until at least 3 hours after the most recent heartbeat (or query).

The default value is 3600 seconds (one hour). The valid range of values is 900 - 3600. Since timeouts usually occur after at least 3 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 doesn’t 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.

OCSP (Online Certificate Status Protocol)

Snowflake uses OCSP to evaluate the certificate chain when making a connection to Snowflake. The driver or connector version and its configuration both determine the OCSP behavior. For more information about the driver or connector version, their configuration, and OCSP behavior, see OCSP Client & Driver 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 may 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]
});

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 may 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:

JavaScript Data Type SQL Data Type
String
  • VARCHAR , CHAR , CHARACTER , STRING , TEXT
  • TIME
Number
  • NUMBER , DECIMAL , NUMERIC , INT , INTEGER , BIGINT , SMALLINT
  • FLOAT , FLOAT4 , FLOAT8 , DOUBLE , DOUBLE PRECISION , REAL
Date
  • DATE
  • TIMESTAMP , TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ
Boolean
  • BOOLEAN
JSON
  • VARIANT
  • ARRAY
  • OBJECT

Note that the TIME data type in SQL has no equivalent in JavaScript so it is mapped to a JavaScript string.

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