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¶
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 locator is
xy12345
:Cloud Platform / . Region
Account Locator . (with additional segments if required)
Amazon Web Services (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
Europe (London)xy12345.eu-west-2.aws
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
Google Cloud Platform (GCP)
US Central1 (Iowa)xy12345.us-central1.gcp
Europe West2 (London)xy12345.europe-west2.gcp
Europe West4 (Netherlands)xy12345.europe-west4.gcp
Microsoft Azure
West US 2 (Washington)xy12345.west-us-2.azure
East US 2 (Virginia)xy12345.east-us-2.azure
US Gov Virginiaxy12345.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 locator is different than the structure described in the above examples:
If your Snowflake Edition is VPS, please contact Snowflake Support for details about your account locator.
If AWS PrivateLink or Azure Private Link is enabled for your account, your account locator requires a
privatelink
segment in place of the region segment.For more details, see:
username
Snowflake user login name to connect with.
password
Password for the user.
region
— DeprecatedThe 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
= 0Number
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
> 0Number
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()); } });