Connecting to SQream Using Node.JS

The SQream DB Node.JS driver allows Javascript applications and tools connect to SQream DB. This tutorial shows you how to write a Node application using the Node.JS interface.

The driver requires Node 10 or newer.

Installing the Node.JS driver

Prerequisites

  • Node.JS 10 or newer. Follow instructions at nodejs.org .

Install with NPM

Installing with npm is the easiest and most reliable method. If you need to install the driver in an offline system, see the offline method below.

$ npm install @sqream/sqreamdb

Install from an offline package

The Node driver is provided as a tarball for download from the SQream Drivers page .

After downloading the tarball, use npm to install the offline package.

$ sudo npm install sqreamdb-4.0.0.tgz

Connect to SQream DB with a Node.JS application

Create a simple test

Replace the connection parameters with real parameters for a SQream DB installation.

sqreamdb-test.js
const Connection = require('@sqream/sqreamdb');

const config  =  {
  host: 'localhost',
  port: 3109,
  username: 'rhendricks',
  password: 'super_secret_password',
  connectDatabase: 'raviga',
  cluster: true,
  is_ssl: true,
  service: 'sqream'
  };

const query1  =  'SELECT 1 AS test, 2*6 AS "dozen"';

const sqream = new Connection(config);
sqream.execute(query1).then((data) => {
   console.log(data);
}, (err) => {
   console.error(err);
});

Run the test

A successful run should look like this:

$ node sqreamdb-test.js
[  { test: 1, dozen: 12  }  ]

API reference

Connection parameters

Item

Optional

Default

Description

host

✗

None

Hostname for SQream DB worker. For example, 127.0.0.1, sqream.mynetwork.co

port

✗

None

Port for SQream DB end-point. For example, 3108 for the load balancer, 5000 for a worker.

username

✗

None

Username of a role to use for connection. For example, rhendricks

password

✗

None

Specifies the password of the selected role. For example, Tr0ub4dor&3

connectDatabase

✗

None

Database name to connect to. For example, master

service

✓

sqream

Specifices service queue to use. For example, etl

is_ssl

✓

false

Specifies SSL for this connection. For example, true

cluster

✓

false

Connect via load balancer (use only if exists, and check port). For example, true

Events

The connector handles event returns with an event emitter

getConnectionId

The getConnectionId event returns the executing connection ID.

getStatementId

The getStatementId event returns the executing statement ID.

getTypes

The getTypes event returns the results columns types.

Example

const myConnection  = new Connection(config);

myConnection.runQuery(query1, function  (err, data){
  myConnection.events.on('getConnectionId', function(data){
      console.log('getConnectionId', data);
  });

  myConnection.events.on('getStatementId', function(data){
      console.log('getStatementId', data);
  });

  myConnection.events.on('getTypes', function(data){
      console.log('getTypes', data);
  });
});

Input placeholders

The Node.JS driver can replace parameters in a statement.

Input placeholders allow values like user input to be passed as parameters into queries, with proper escaping.

The valid placeholder formats are provided in the table below.

Placeholder

Type

%i

Identifier (e.g. table name, column name)

%s

A text string

%d

A number value

%b

A boolean value

See the input placeholders example below.

Examples

Setting configuration flags

SQream DB configuration flags can be set per statement, as a parameter to runQuery.

For example:

const setFlag  =  'SET showfullexceptioninfo = true;';

const query_string = 'SELECT 1';

const myConnection  = new Connection(config);
myConnection.runQuery(query_string, function  (err, data){
  console.log(err, data);
}, setFlag);

Lazyloading

To process rows without keeping them in memory, you can lazyload the rows with an async:

const Connection = require('@sqream/sqreamdb');

const config  =  {
  host: 'localhost',
  port: 3109,
  username: 'rhendricks',
  password: 'super_secret_password',
  connectDatabase: 'raviga',
  cluster: true,
  is_ssl: true,
  service: 'sqream'
  };

const sqream = new Connection(config);

const query = "SELECT * FROM public.a_very_large_table";

(async () => {
  const cursor = await sqream.executeCursor(query);
  let count = 0;
  for await (let rows of cursor.fetchIterator(100)) {
    // fetch rows in chunks of 100
    count += rows.length;
  }
  await cursor.close();
  return count;
})().then((total) => {
  console.log('Total rows', total);
}, (err) => {
  console.error(err);
});

Reusing a connection

It is possible to execeute multiple queries with the same connection (although only one query can be executed at a time).

const Connection = require('@sqream/sqreamdb');

const config  =  {
  host: 'localhost',
  port: 3109,
  username: 'rhendricks',
  password: 'super_secret_password',
  connectDatabase: 'raviga',
  cluster: true,
  is_ssl: true,
  service: 'sqream'
  };

const sqream = new Connection(config);

(async () => {

  const conn = await sqream.connect();
  try {
    const res1 = await conn.execute("SELECT 1");
    const res2 = await conn.execute("SELECT 2");
    const res3 = await conn.execute("SELECT 3");
    conn.disconnect();
    return {res1, res2, res3};
  } catch (err) {
    conn.disconnect();
    throw err;
  }

})().then((res) => {
  console.log('Results', res)
}, (err) => {
  console.error(err);
});

Using placeholders in queries

Input placeholders allow values like user input to be passed as parameters into queries, with proper escaping.

const Connection = require('@sqream/sqreamdb');

const config  =  {
  host: 'localhost',
  port: 3109,
  username: 'rhendricks',
  password: 'super_secret_password',
  connectDatabase: 'raviga',
  cluster: true,
  is_ssl: true,
  service: 'sqream'
  };

const sqream = new Connection(config);

const sql = "SELECT %i FROM public.%i WHERE name = %s AND num > %d AND active = %b";

sqream.execute(sql, "col1", "table2", "john's", 50, true);

The query that will run is SELECT col1 FROM public.table2 WHERE name = 'john''s' AND num > 50 AND active = true