HDB connection pool
SAP HANA connection pool based on node-hdb driver and node-pool generic pool.
The HDB connection pool is able to connect with:
- username + password
- SAML bearer assertion
- username + SAP HANA session cookie
The connection pool can be used in standalone mode, or with a webserver in HTTP mode.
Installation
$ npm install node-hdb-pool
Getting started
This is a very simple example how to configure the pool:
var hdbPool = ; var hpool = hdbPool;
And how to use it in standalone mode:
if hpool var sql = '...'; // can be either DDL or DML var prepArgs = ; // prepared statement arguments hpool
And how to use it in HTTP mode:
var express = app = ; app; app;
And how to use it in HTTP mode with streaming:
var express = app = ; app; app;
And how to use it with socket.io:
var express = http = app = ; var HttpServer = http SocketIO = HttpServer; SocketIO;
Config parameter details:
host
: hostname. Required.port
: port. Required.env
: short symbolic name of the environment, e.g. SAND, PROD, etc. Optional, if not specifiedhostname
will be used.user
: username for SAP HANA Database. Optional whenassertion
is used, otherwise required.password
: password for the SAP HANA user. Optional whenassertion
orsessionCookie
is used, otherwise required.assertion
: a signed SAML bearer assertion or assertion factory function. See more.sessionCookie
: an SAP HANA sessionCookie. See more.maxPoolSize
: maximum number of resources to create at any given time. Optional, default to 1.minPoolSize
: minimum number of resources to keep in the pool at any given time. Optional, default to 0.idleTimeoutMillis
: max milliseconds a resource can stay idle in the pool before being removed. Optional, default to 30000.refreshIdle
: boolean that specifies whether idle resources at or below the min threshold should be destroyed/re-created. Optional, default to true.genericPoolLog
: if verbose log of node-pool package should be displayed. Optional, default to false.resphPrefix
: Optional prefix concatenated to response header in case of HTTP mode.logger
: Optional. False by default. In order to enable logging, reference to a configured tracer logger should be set.defaultSchema
: Optional. Default schema. By default no schema is setup, so the default schema of theuser
will be used.
Recommended logger setup:
var colors = require('colors');
var logger = require('tracer').colorConsole({
dateformat: 'HH:MM:ss',
stackIndex: 1, // this is important to be setup in order to see a full log
filters: {
trace: colors.cyan,
debug: colors.yellow,
info: colors.green,
warn: colors.yellow,
error: [ colors.red ]
}
});
Standalone mode
Pool configuration connecting with username + password
var hdbPool = ; var hpool = hdbPool;
Pool configuration connecting with SAML bearer assertion
var hdbPool = fs = ; // fake pid to pass to HANA in order to retain sessionCookie between restartshdbPool; var hpool = hdbPool;
Pool configuration connecting with SAP HANA session cookie
var hdbPool = ; // fake pid to pass to HANA in order to retain sessionCookie between restartshdbPool; var hpool = hdbPool;
Usage of the pool in standalone mode
if hpool hpool;
HTTP mode
Pool configuration connecting with username + password
var hdbPool = ; var hpool = hdbPool;
Pool configuration connecting with SAML bearer assertion
var hdbPool = fs = ; // fake pid to pass to HANA in order to retain sessionCookie between restartshdbPool; var hpool = hdbPool;
Usage of the pool in HTTP mode
Default response headers:
- query-time: actual execution time in milliseconds (it does not include waiting time for free resource)
- waiting-time: time in milliseconds spent on waiting for free resource
// setup a servervar express = app = ; var sql = 'SELECT system_id SID, host, version FROM M_DATABASE';var prepArgs = ; // prepared statement arguments app; app; // NOTE: in case of minPoolSize > 0, pool needs to be killed manually; // kill the pool periodically in every hour
Usage of the pool in HTTP mode with streaming
Available streaming outputs:
- CSV (Comma Separated Values): hdbPool.createCsvStringifier
- SSV (Semi-colon Separated Values): hdbPool.createSsvStringifier
- JSON: hdbPool.createJSONStringifier
Default response headers:
- query-time: actual execution time in milliseconds (it does not include waiting time for free resource)
- waiting-time: time in milliseconds spent on waiting for free resource
// setup a servervar express = app = ; var sql = 'SELECT system_id SID, host, version FROM M_DATABASE';var prepArgs = ; // prepared statement arguments app; app; // NOTE: in case of minPoolSize > 0, pool needs to be killed manually; // kill the pool periodically in every hour
Configure the pool with SessionStore
In case of HTTP mode if minPoolSize > 0
and SAML bearer assertion is used for connecting to SAP HANA, the acquired HANA session cookie can be stored in the sessionStore by simply passing the req
as second argument.
var hpool = hdbPool.createPool(poolOpts, req);
Notes about SAML bearer assertion and HANA session cookie
- SAML bearer assertion has to be signed otherwise SAP HANA does not accept it
- in SAP HANA, one needs to setup the public key-pair of the private key which was used to sign the assertion
- an example SAML bearer assertion is
USERNAME urn:oasis:names:tc:SAML:2.0:ac:classes:PasswordProtectedTransport
- once connection with a valid SAML bearer assertion is done, the user gets a HANA session cookie
- HANA session cookies can be seen in SESSION_COOKIES system view
- HANA session cookies without alive connections are wiped out periodically
- quick reconnect with a previously used cookie usually works
- a HANA session cookie is valid only for a given user from a given host+PID (OS process ID)
- if the OS process is restarted and it gets a new PID, a previously used session cookie cannot be used to connect to HANA
- to avoid this behaviour, use the
hdbPool.setPid(XXXX);
to setup a static PID before callinghdbPool.createPool(...)
Query metadata
Metadata for a query can be retrieved by hpool.meta()
.
if hpool hpool;
Running tests
For the tests a database connection has to be established to an SAP HANA database. Therefore you need to copy the configuration test/config.js.tpl
file into test/config.js
and change it with your connection data. An example config file can be seen here:
var fs = ; var config = host: 'example.host.com' port: 30015 user: 'username' password: 'secret' assertion: fs; exportsconfig = config;
To run the unit tests go to the node-hdb-pool
folder and type:
$ npm test
Todo
- Implement periodic reconnection-logic if database host becomes unavailable
- Improve documentation
- Improve test coverage
FAQ
Calling stored procedures and sending messages
Example stored procedure to insert a user into a table:
CREATE PROCEDURE INSERTUSER ( p_username varchar(255))SQL SECURITY DEFINER ASBEGIN SEQUENTIAL EXECUTION DECLARE STATUS_MESSAGE CONDITION FOR SQL_ERROR_CODE 10001; DECLARE EXIT HANDLER FOR STATUS_MESSAGE SELECT ::SQL_ERROR_CODE AS "errno", ::SQL_ERROR_MESSAGE AS "errmsg" FROM DUMMY; DECLARE USER_EXISTS INT = 0; SELECT COUNT(USER_NAME) INTO USER_EXISTS FROM USERS WHERE USER_NAME = :p_username; IF (:USER_EXISTS > 0) THEN SIGNAL STATUS_MESSAGE SET MESSAGE_TEXT = '{"status": "error", "code": 1, "message": "user already exist"}'; ELSE INSERT INTO USERS VALUES (:p_username); SIGNAL STATUS_MESSAGE SET MESSAGE_TEXT = \'{"status": "success", "code": 0, "message": "user created"}'; END IF;END;
Example code how to call the above procedure:
hpool;