Rhino

Rhino is a tough, production-focused Node.js Microsoft SQL Server driver that incorporates pooling and runs the well-supported tedious package under the hood, fully utilizing all of it's available configuration options. Rhino was built to take the frustration out of running database queries and let you, the developer, focus on running queries and getting reliable, fast, results.

Rhino is a solid choice because...

It fully implements JSdoc and is tested with VS Code auto-completion.
A dependency list so small we can list it here: tedious and tarn.
It is a solid, modern, unit-tested implementation built for heavy production use.
Employs async/await/Promise functions to let you work asynchronously.
Manages connections for you using an internal pool, stop worrying and query!
Open-source and accepting pull requests.

Feature list

Installation

npm i rhino --save

or

yarn add rhino

Quick Start

// create the rhino pool.
const rhino = require('rhino');

...
let db = await rhino.create({
    //tedious config options, see: https://tediousjs.github.io/tedious/api-connection.html
    server: 'localhost',
    authentication: {
        options: {  
            userName: "testuser",
            password: "mypassword"
        }
    },
    //tarn pooling options
    pool: {
        min: 0,
        max: 10
    }
});

// run a simple query
let results = await db.query('SELECT * FROM dbo.People');
console.log(`Count: ${results.count}`);
console.table(results.rows);

// run a parameterized query
results = await db
    .query(`SELECT @valid=IsCustomer 
            FROM contacts 
            WHERE name LIKE @firstName AND account = @number`)
    .in('firstName', 'John')
    .in('account', 23494893, Query.TYPE.INT)
    .out('valid', undefined, 'BIT');
console.log(`Count: ${results.count}`);
console.table(results.rows);
//use object parameters
results = await db.query(
    'SELECT TOP 10 FROM addresses WHERE street LIKE @street', 
    { street: '% Avenue' }
);

// run queries in a transaction
let tx = db.transaction();
try {
    tx.query('INSERT INTO dbo.People (Code, FullName) VALUES (434,\'John Bircham\')');
    tx.query('INSERT INTO dbo.People (Code, FullName) VALUES (@code, @name)', { code: 322, name: 'Amy Smith' });
    tx.query('DELETE FROM dbo.People WHERE Code = 341');
    let results = await tx.commit();
    console.log('Transaction committed.');
} catch (err) {
    tx.rollback();
    console.info('Transaction rolled back.');
    throw err;
}
// run transactions with save-points.
let tx = db.transaction();
try {
    tx.query('INSERT INTO dbo.Addresses (Street) VALUES (@st)', { st: '12431 NE Martin St.' });
    tx.savePoint('mysavepoint');
    tx.query('INSERT INTO dbo.Addresses (ID) VALUES (1);');
    let results = await tx.commit();
} catch (err) {
    tx.rollback('mysavepoint');
    console.info('Transaction rolled back to save-point.');
    throw err;
}

// run a bulk-load
let bulk = db.bulk('dbo.Theme', { timeout: 10000 });
await bk.column('Name', Query.TYPE.VarChar, { nullable: false, length: 512 });
await bk.column('HexCode', Query.TYPE.VarChar, { nullable: false, length: 512 });
for (let x = 0; x < 1000; x++) {
    //add rows
    bk.add({ Name: `name${x}`, HexCode: `#000${x}${x}${x}` });
}
let result = await bk.execute();

...
// all done, forever!
// clean up resources
db.destroy();

API

Classes

BulkQuery

Provides promise extensions to a BulkQuery object and allows it to be executed on an aquired connection.

ConnectedQuery

Provides promise extensions to a Query object and allows it to be executed on an aquired connection.

Connection

Provides access to the database through a TDS connection.

EventTracker

Provides tooling to easily track event listeners and remove them from EventEmitter instances without affecting listeners added from other operations.

Log

This logging class utilizes 3 modes of logging: error, warn, and debug. The mode can be set by specifying one of the modes in the RHINO_LOGGING environmental variable, or through a rhino instance's config.logging.mode property.

Query

Wraps a SQL query and provides helper functions for managing parameters.

Rhino

Rhino is a managed Microsoft SQL Server driver powered by tedious and node-pool. This class defines functionality to execute queries and utlize transactions. Under the hood it handles all connection pooling, including opening and closing of connections to the database.

You can use multiple instances of the Rhino class in your application - each one can utilize a different configuration.

Transaction

The Transaction class provides the ability to queue multiple queries for execution under a SQL transaction, optionally including save-points. It exposes methods to commit and rollback the entire set of queries or to a particular save-point.

Typedefs

PromiseBulkQuery : BulkQuery | Promise.<Result>
PromiseQuery : Query | Promise.<Result>
SQLParameter
QueryTypes

BulkQuery

Provides promise extensions to a BulkQuery object and allows it to be executed on an aquired connection.

Kind: global class

BulkQuery
- new BulkQuery(tableName, options, pool)
- instance
  - .tableName : String
  - .options : Options
  - .pool : tarn.Pool
  - .aquire() ⇒ BulkQuery
  - .execute()
  - .column(name, type, options) ⇒ BulkQuery
  - .add(...rows) ⇒ BulkQuery
- static
  - .Options

new BulkQuery(tableName, options, pool)

Creates a new instance of a BulkQuery.

Param	Type	Description
tableName	`String`	The name of the table to perform the bulk insert.
options	`Options`	Options to pass to the bulk query.
pool	`tarn.Pool`	The connection pool to utilize for aquiring the connection.

bulkQuery.tableName : `String`

Kind: instance property of BulkQuery

bulkQuery.options : `Options`

Kind: instance property of BulkQuery

bulkQuery.pool : `tarn.Pool`

The tarn.Pool instance linked to this query.

Kind: instance property of BulkQuery

bulkQuery.aquire() ⇒ `BulkQuery`

Establishes a connection to begin a bulk-load operation.
This is called automatically upon column or row, so you generally do not need to call it explicitly.

Kind: instance method of BulkQuery

bulkQuery.execute()

Fire and complete the bulk-load.

Kind: instance method of BulkQuery

bulkQuery.column(name, type, options) ⇒ `BulkQuery`

Adds a column to the bulk query.

Kind: instance method of BulkQuery

Param	Type	Description
name	`String`	The column name.
type	`QueryTypes`	The TDS type of the column.
options	`*`	column options.

bulkQuery.add(...rows) ⇒ `BulkQuery`

Adds a row to the bulk query.

Any row argument that is null or undefined is ignored (skipped).

Kind: instance method of BulkQuery
Throws:

Error when the row is non-null, non-undefined, and not an object.

Param	Type	Description
...rows	`Object`	A spread of row objects. If an object it should have key/value pairs representing column name and value. If an array then it should represent the values of each column in the same order which they were added to the `BulkQuery` object.

BulkQuery.Options

Kind: static typedef of BulkQuery
Properties

Name	Type	Description
checkConstraints	`Boolean`	Honors constraints during bulk load, using T-SQL CHECK_CONSTRAINTS.
fireTriggers	`Boolean`	Honors insert triggers during bulk load, using the T-SQL FIRE_TRIGGERS.
keepNulls	`Boolean`	Honors null value passed, ignores the default values set on table, using T-SQL KEEP_NULLS.
tableLock	`Boolean`	Places a bulk update(BU) lock on table while performing bulk load, using T-SQL TABLOCK.
timeout	`Number`	The number of milliseconds before the bulk load is considered failed, or 0 for no timeout.

ConnectedQuery

Provides promise extensions to a Query object and allows it to be executed on an aquired connection.

Kind: global class

ConnectedQuery
- new ConnectedQuery(pool)
- .pool : tarn.Pool
- .then([resolve], [reject]) ⇒ Promise.<(Result|Array.<Result>)>

new ConnectedQuery(pool)

Creates a new instance of a ConnectedQuery.

Param	Type	Description
pool	`tarn.Pool`	The connection pool to utilize for aquiring the connection.

connectedQuery.pool : `tarn.Pool`

The tarn.Pool instance linked to this query.

Kind: instance property of ConnectedQuery

connectedQuery.then([resolve], [reject]) ⇒ `Promise.<(Result|Array.<Result>)>`

Thenable executor of this query using the linked connection or transaction.

Kind: instance method of ConnectedQuery
Throw: Error if the pool property is falsey.

Param	Type	Description
[resolve]	`function`	Promise callback called when the work completes successfully.
[reject]	`function`	Promise callback called when the work fails.

Connection

Provides access to the database through a TDS connection.

Kind: global class

Connection
- new Connection(tdsConfig, log)
- instance
  - .config : TediousConfiguration
  - .log : Log
  - .connected : Boolean
  - .state : Number
  - .id : String
  - .connect() ⇒ Promise.<Connection>
  - .disconnect() ⇒ Promise.<Connection>
  - "transition"
- static
  - .CONNECTION_STATE : enum
  - .TediousConfiguration

new Connection(tdsConfig, log)

Creates a new Connection instance.

Param	Type	Description
tdsConfig	`TediousConfiguration`	The configuration for the connection.
log	`Log`	A loging instance. if not provided, one is created using the given configuration.

connection.config : `TediousConfiguration`

Kind: instance property of Connection

connection.log : `Log`

Kind: instance property of Connection

connection.connected : `Boolean`

Boolean flag indicating whether the connection is valid and alive.

Kind: instance property of Connection

connection.state : `Number`

Returns the processing state of the connection.

Accessible through the Connection.CONNECTION_STATES object.

Kind: instance property of Connection

connection.id : `String`

Randomly generated connection identifier. Output in debugging messages.

Kind: instance property of Connection

connection.connect() ⇒ `Promise.<Connection>`

Ensures the connection to the database has been established.

If the connection is already connected then no action occurs and this function returns normally and only emits the connected event.

If the connection is already attempting to connect, this call will (a)wait for it to complete and emit a connected event if successful.

If the connection is not established, it will be attempted and the connecting and connected events will be emitted.

Kind: instance method of Connection
Emits: event:connecting, event:connected

connection.disconnect() ⇒ `Promise.<Connection>`

Disconnects from the database.

Kind: instance method of Connection
Emits: event:disconnected

"transition"

Transition event fired when the connection state is changed.

Kind: event emitted by Connection
Properties

Name	Type
newState	`Number`
oldState	`Number`
meta	`*`

Connection.CONNECTION_STATE : `enum`

Enumeration of connection states that a connection can be in.

1 = IDLE
2 = CONNECTING
3 = DISCONNECTING
4 = TRANSACTING
5 = EXECUTING

Kind: static enum of Connection
Read only: true

Connection.TediousConfiguration

The tedious configuration options are all fully supported. Some options support default values from environmental variables, all of which use the RHINO_MSSQL_ prefix.

For more details, please refer to: Tedious on GitHub

Kind: static typedef of Connection
Properties

Name	Type	Default	Description
[server]	`String`	`"localhost"`	A default value is checked for under the `RHINO_MSSQL_HOST` then `RHINO_MSSQL_SERVER` environmental variables.
[authentication]	`Object`
[authentication.type]	`String`	`"default"`	A default value is checked for under the `RHINO_MSSQL_AUTH_TYPE` environmental variable.
[authentication.options]	`Object`
[authentication.options.userName]	`String`		A default value is checked for under the `RHINO_MSSQL_USER` then `RHINO_MSSQL_AUTH_USER` environmental variables.
[authentication.options.password]	`String`		A default value is checked for under the `RHINO_MSSQL_PASSWORD` then `RHINO_MSSQL_AUTH_PASSWORD` environmental variables.
[authentication.options.domain]	`String`		A default value is checked for under the `RHINO_MSSQL_DOMAIN` then `RHINO_MSSQL_AUTH_DOMAIN` environmental variables.
[options]	`Object`
[options.port]	`Number`	`1433`	A default value is checked for under the `RHINO_MSSQL_PORT` environmental variable.
[options.instanceName]	`String`		A default value is checked for under the `RHINO_MSSQL_INSTANCE` then `RHINO_MSSQL_INSTANCE_NAME` environmental variables.
[options.database]	`String`	`"master"`	A default value is checked for under the `RHINO_MSSQL_DATABASE` environmental variable.
[options.appName]	`String`	`""`	A default value is checked for under the `RHINO_MSSQL_APP_NAME` environmental variable.
[options.connectTimeout]	`Number`	`15000`
[options.requestTimeout]	`Number`	`15000`
[options.cancelTimeout]	`Number`	`5000`
[options.connectionRetryInterval]	`Number`	`500`
[options.encrypt]	`Boolean`	`false`	A default value is checked for under the `RHINO_MSSQL_ENCRYPT` environmental variable.
[options.tdsVersion]	`String`	`"7_4"`
[options.dateFormat]	`String`	`"mdy"`
[options.fallbackToDefaultDb]	`Boolean`	`false`
[options.enableAnsiNull]	`Boolean`	`true`
[options.enableAnsiNullDefault]	`Boolean`	`true`
[options.enableAnsiPadding]	`Boolean`	`true`
[options.enableAnsiWarnings]	`Boolean`	`true`
[options.enableConcatNullYieldsNull]	`Boolean`	`true`
[options.enableCursorCloseOnCommit]	`Boolean`	`false`
[options.enableImplicitTransactions]	`Boolean`	`false`
[options.enableNumericRoundabort]	`Boolean`	`false`
[options.enableQuotedIdentifier]	`Boolean`	`true`
[options.rowCollectionOnDone]	`Boolean`	`false`
[options.rowCollectionOnRequestCompletion]	`Boolean`	`false`
[options.packetSize]	`Number`	`4096`
[options.useUTC]	`Boolean`	`true`
[options.abortTransactionOnError]	`Boolean`
[options.localAddress]	`String`
[options.useColumnNames]	`Boolean`	`false`
[options.camelCaseColumns]	`Boolean`	`false`
[options.columnNameReplacer]	`Boolean`
[options.isolationLevel]	`String`	`"READ_COMMITED"`
[options.connectionIsolationLevel]	`String`	`"READ_COMMITED"`
[options.readOnlyIntent]	`Boolean`	`false`
[options.cryptoCredentialsDetails]	`Object`
[options.debug]	`Object`
[options.debug.packet]	`Boolean`	`false`
[options.debug.data]	`Boolean`	`false`
[options.debug.payload]	`Boolean`	`false`
[options.debug.token]	`Boolean`	`false`

EventTracker

Provides tooling to easily track event listeners and remove them from EventEmitter instances without affecting listeners added from other operations.

Kind: global class

EventTracker

new EventTracker()

Creates a new EventTracker instance.

eventTracker.listeners : `Array.<RegisteredEventListener>`

Array containing all of the registered event listeners in this tracker instance.

Kind: instance property of EventTracker

eventTracker.removeFrom(emitter, [event], [unregister])

Removes all registered matching event listeners from the specified emitter.

Kind: instance method of EventTracker

Param	Type	Description
emitter	`EventEmitter`	The instance implementing the EventEmitter "removeListener" function.
[event]	`String` \| `symbol`	Optional event to target for removal. Only listeners under the event will be removed.
[unregister]	`Boolean`	Removes the registered listeners after they have been removed from the emitter. Works with the `event` parameter, if specified. If a listerner is not found, on the emitter, it is not unregistered.

eventTracker.register(event, ...listeners)

Registers one or more event listeners.

Kind: instance method of EventTracker

Param	Type	Description
event	`String` \| `Symbol`	The event name or symbol.
...listeners	`function`	The listener functions.

eventTracker.registerOn(emitters, event, ...listeners)

Registers one or more event listeners in the tracker and on the specified target objects.

Kind: instance method of EventTracker

Param	Type	Description
emitters	`EventEmitter` \| `Array.<EventEmitter>`	An `EventEmitter` instance or array of instances to add the specified event listeners on using the `addListener` function call.
event	`String` \| `Symbol`	The event name or symbol.
...listeners	`function`	The listener functions.

eventTracker.unregister([event], [...listeners])

Un-registers one or more event listeners by matching the event and/or listener function(s). Either, both, or none of the parameters may be specified. If both event and listerner(s) are not specified, all listeners are unregistered.

Kind: instance method of EventTracker

Param	Type	Description
[event]	`String` \| `Symbol`	The event name or symbol to match for unregistering listeners.
[...listeners]	`function`	The listener functions to unregister. If none are specified, all listeners under the event are unregistered.

EventTracker.RegisteredEventListener

Kind: static typedef of EventTracker
Properties

Name	Type
event	`String` \| `Symbol`
listener	`function`

Log

This logging class utilizes 3 modes of logging: error, warn, and debug. The mode can be set by specifying one of the modes in the RHINO_LOGGING environmental variable, or through a rhino instance's config.logging.mode property.

Kind: global class

Log
- instance
  - .config : LogConfiguration
  - .error()
  - .warn()
  - .debug()
- static
  - .LogConfiguration

log.config : `LogConfiguration`

Kind: instance property of Log

log.error()

Logs an error to the configured error function, or if not specifed, to the console.error.

Kind: instance method of Log

log.warn()

Logs a warning message to the configured warn function, or if not specifed, to the console.warn.

Kind: instance method of Log

log.debug()

Logs a debug message to the configured debug function, or if not specifed, to the console.debug.

Kind: instance method of Log

Log.LogConfiguration

Kind: static typedef of Log
Properties

Name	Type	Default	Description
mode	`Boolean` \| `String`		Can be 'none', 'error', 'warn', or 'debug for enabled logging levels. A falsey value will disable logging. A truthy value that is not a string will assume 'warn' mode.
[connections]	`Boolean`	`false`	Flag that indicates whether to log connection state messages. These messages are entered on the debug log.
[tds]	`Boolean`	`false`	Indicates whether to log debug and info messages from underlying TDS connections. These messages are entered on the debug log.

Query

Wraps a SQL query and provides helper functions for managing parameters.

Kind: global class

Query
- new Query()
- instance
  - .statement : String
  - .params : Map.<String, Query.Parameter>
  - .mode
  - .requestTimeout : Number
  - .timeout(ms) ⇒ Query
  - .sql(statement, [params]) ⇒ Query
  - .batch() ⇒ Query
  - .exec() ⇒ Query
  - .param(name, [value], [type], [dir], [options]) ⇒ Query
  - .in(name, [value], [type], [options]) ⇒ Query
  - .out(name, [value], [type], [options]) ⇒ Query
  - .remove(name) ⇒ Boolean
  - .clear()
- static
  - .TYPE : QueryTypes
  - .AUTODETECT_TYPES
  - .PARAM_DIR : enum
  - .MODE : enum
  - .TDSType
  - .Parameter

new Query()

Creates a new instance of the Query class.

Example
The following example shows how to build a query for use in Rhino.

let q = Query
         .sql(`SELECT @valid=IsCustomer 
               FROM contacts 
               WHERE name LIKE @firstName AND account = @number`)
         .in('firstName', 'John')
         .in('account', Query.TYPE.INT, 23494893)
         .out('valid', Query.TYPE.BIT);
//remove a parameter by name
q.remove('account');
//reset everything
q.clear();

query.statement : `String`

The SQL statement.

Kind: instance property of Query

query.params : `Map.<String, Query.Parameter>`

The parameters and values to use on the query.

Kind: instance property of Query

query.mode

The query execution mode.

Kind: instance property of Query

query.requestTimeout : `Number`

Command timeout value set for this query. A null value indicates the default will be used.

Kind: instance property of Query

query.timeout(ms) ⇒ `Query`

Sets the SQL query request timeout.

Kind: instance method of Query
Throws:

Error if the ms argument less than 0 or not a number (or null).

Param	Type	Description
ms	`Number`	The timeout in milliseconds, or `null` to use configured defaults.

query.sql(statement, [params]) ⇒ `Query`

Sets the SQL query text (statment). Calling this function resets the query mode to an automatically determined value.

Kind: instance method of Query
Throws:

Error if the statement argument is falsey.
Error if the statement argument is not a string.

Param	Type	Description
statement	`String`	The SQL query text to be executed.
[params]	`Map.<String, *>` \| `Object`	Optional parameters `Object` or `Map` that will be added to the "in" parameters of the query. Keys and property names are used as the parameter name, and the value as the parameter values.

query.batch() ⇒ `Query`

Forces the query into BATCH mode.

Kind: instance method of Query
Throws:

Error if the query contains parameters.

query.exec() ⇒ `Query`

Forces the query into EXEC mode.

Kind: instance method of Query

query.param(name, [value], [type], [dir], [options]) ⇒ `Query`

Adds or updates a parameter for the query.
Calling this when the query mode is set to BATCH will reset the mode to QUERY.

Kind: instance method of Query
Throws:

Error if the name argument is falsey.
Error if the name argument is not a string.
Error if the name argument has already been specified or is not specified as a string.
Error if the type and value arguments are not specified or falsey when the direction is out.

Param	Type	Default	Description
name	`String`		The parameter name, can be specified with the '@' character or not.
[value]	`String` \| `Number` \| `Date` \| `Buffer` \| `Object` \| `*`		The value of the parameter.
[type]	`String` \| `TDSType`		The explicit database type to use, if not specified, it is auto-determined.
[dir]	`PARAM_DIR`	`Query.PARAM_DIR.IN`	The direction of the parameter.
[options]	`*`		Any additional `tedious` parameter options.

query.in(name, [value], [type], [options]) ⇒ `Query`

Adds an input parameter to the query.
Calling this when the query mode is set to BATCH will reset the mode to QUERY.

Kind: instance method of Query
Throws:

Error if the name argument is falsey.
Error if the name argument is not a string.
Error if the name argument has already been specified or is not specified as a string.

Param	Type	Description
name	`String` \| `Map.<String, SQLParameter>` \| `Array.<SQLParameter>` \| `Object`	A number of options for specifying the parameter, either giving the name, or giving a Map, Array, or object. If a Map, Array or object is specified, the other arguments are ignored.
[value]	`String` \| `Number` \| `Date` \| `Buffer` \| `Object` \| `*`	The value of the parameter.
[type]	`String` \| `TDSType`	The explicit database type to use, if not specified, it is auto-determined.
[options]	`*`	Any additional `tedious` parameter options.

query.out(name, [value], [type], [options]) ⇒ `Query`

Adds an output parameter to the query.
Calling this when the query mode is set to BATCH will reset the mode to QUERY.

Kind: instance method of Query
Throws:

Error if the name argument is falsey.
Error if the name argument is not a string.
Error if the name argument has already been specified or is not specified as a string.

Param	Type	Description
name	`String` \| `Map.<String, SQLParameter>` \| `Array.<SQLParameter>` \| `SQLParameter`	A number of options for specifying the parameter, either giving the name, or giving a Map, Array, or single instance of the SQLParameter object. If a Map, Array or SQLParameter is specified, the other arguments are ignored.
[value]	`String` \| `Number` \| `Date` \| `Buffer` \| `Object` \| `*`	The value of the parameter.
[type]	`String` \| `TDSType`	The explicit database type to use, if not specified, it is auto-determined.
[options]	`*`	Any additional `tedious` parameter options.

query.remove(name) ⇒ `Boolean`

Removes a parameter by name.

Kind: instance method of Query
Returns: Boolean - Returns true if a parameter with the name was found and removed, or false if no parameter was found with the given name.
Throws:

Error if the name argument is falsey.
Error if the name argument is not a string.

Param	Type	Description
name	`String`	The name of the parameter to remove.

query.clear()

Clears all query criteria, including SQL statement values and parameters. The Query instance is fully reset to a blank slate.

Kind: instance method of Query

Query.TYPE : `QueryTypes`

TDS column types.

Kind: static property of Query

Query.AUTODETECT_TYPES

Auto-detection types used when a type is not specifically detected, but a value is provided. Only certain types can be configured.

Kind: static property of Query

.AUTODETECT_TYPES

AUTODETECT_TYPES.FLOATING_POINT

The TDS type used when a floating point number value is detected. Defaults to Float.

Kind: static property of AUTODETECT_TYPES

AUTODETECT_TYPES.DATE

The TDS type used when a Date object value is detected. Defaults to DateTimeOffset.

Kind: static property of AUTODETECT_TYPES

AUTODETECT_TYPES.BUFFER

The TDS type used when a Buffer object value is detected. Defaults to VarBinary.

Kind: static property of AUTODETECT_TYPES

Query.PARAM_DIR : `enum`

The parameter direction. Defaults to 'IN'.

Kind: static enum of Query
Properties

Name	Type	Default
IN	`String`	`in`
OUT	`String`	`out`

Query.MODE : `enum`

The mode that determines how the query should be executed.

Kind: static enum of Query
Properties

Name	Type	Default	Description
QUERY	`Number`	`0`	Indicates the query should be run using the `execSql` function. This is the most common mode that supports parameters.
BATCH	`Number`	`1`	This mode indicates the query should run using the `execSqlBatch` function. This mode does not support parameters and is meant for multi-statement queries.
EXEC	`Number`	`2`	This mode indicates the query is a stored procedure call, and is executed using the `callProcedure` function.

Query.TDSType

Kind: static typedef of Query
Properties

Name	Type
id	`Number`
name	`String`
type	`String`

Query.Parameter

Kind: static typedef of Query
Properties

Name	Type
output	`Boolean`
type	`TDSType`
value	`*`
options	`Object`
options.length	`Number`
options.precision	`Number`
options.scale	`Number`

Rhino

Rhino is a managed Microsoft SQL Server driver powered by tedious and node-pool. This class defines functionality to execute queries and utlize transactions. Under the hood it handles all connection pooling, including opening and closing of connections to the database.

You can use multiple instances of the Rhino class in your application - each one can utilize a different configuration.

Kind: global class

Rhino
- new Rhino([config])
- instance
  - .config : RhinoConfiguration
  - .log : Log
  - .destroy([done])
  - .ping() ⇒ Boolean
  - .query(sql, [params]) ⇒ ConnectedQuery | Promise.<Result>
  - .bulk(tableName, options) ⇒ BulkQuery
- static
  - .create([config]) ⇒ Rhino
  - .defaultConfig([config]) ⇒ RhinoConfiguration
  - .PoolConfiguration
  - .RhinoBaseConfiguration
  - .RhinoConfiguration : TediousConfiguration | RhinoBaseConfiguration

new Rhino([config])

Constructs a Rhino instance using the specified config values.

Param	Type	Description
[config]	`RhinoConfiguration`	Configuration values to use in this `Rhino` instance. Any properties not explicitly specified will use the default values.

rhino.config : `RhinoConfiguration`

Kind: instance property of Rhino

rhino.log : `Log`

Kind: instance property of Rhino

rhino.destroy([done])

Destroys internal pooled resources in this instance. This is called automatically when the process exits.

Kind: instance method of Rhino

Param	Type	Description
[done]	`function`	Callback function when the destruction is complete.

rhino.ping() ⇒ `Boolean`

Attempts to connect to the database. This method utilizes the internal connection pool, and will return true if a connection is already opened and active. If the connection cannot be established for any reason, including an error, a false is returned.

Note that if an error occurs in this function call, it is not thrown, but it will be logged normally.

Kind: instance method of Rhino
Returns: Boolean - Returns true when a connection was successfully aquired. A false value is returned if the connection cannot be aquired for any reason.

rhino.query(sql, [params]) ⇒ `ConnectedQuery` | `Promise.<Result>`

Runs a SQL statement on the database and returns the results.

Kind: instance method of Rhino

Param	Type	Description
sql	`String`	The SQL statement to execute.
[params]	`Map.<String, *>` \| `Object`	Optional parameters `Object` or `Map` that will be added to the "in" parameters of the query. Keys and property names are used as the parameter name, and the value as the parameter values.

rhino.bulk(tableName, options) ⇒ `BulkQuery`

Creates a new bulk-loading query that can be used to rapidly insert large amounts of data.

Kind: instance method of Rhino

Param	Type	Description
tableName	`String`	The name of the table to perform the bulk insert.
options	`Options`	Options to pass to the bulk query.

Rhino.create([config]) ⇒ `Rhino`

This function creates a new Rhino instance to act as a pool for executing database queries. You can create multiple Rhino instances to manage multiple pools of connections or for different databases.

Kind: static method of Rhino

Param	Type	Description
[config]	`RhinoConfiguration`	Configuration values to use in this `Rhino` instance. Any properties not explicitly specified will use the default values.

Example

const rhino = require('rhino');

let pool1 = rhino.create({
        server: 'server-001',
        database: 'databaseA' 
        ... 
    });
let pool2 = rhino.create({
        server: 'server-002',
        database: 'databaseB' 
        ... 
    });

Rhino.defaultConfig([config]) ⇒ `RhinoConfiguration`

Returns a default RhinoConfiguration object. Default values are first searched for in environmental variables then, if not found, with hard-coded default values.

Kind: static method of Rhino

Param	Type	Description
[config]	`RhinoConfiguration`	Optional configuration value overrides.

Rhino.PoolConfiguration

Please refer to: Tarn on GitHub

Kind: static typedef of Rhino
Properties

Name	Type	Default
[max]	`Number`	`1`
[min]	`Number`	`0`
[acquireTimeoutMillis]	`Number`	`30000`
[createTimeoutMillis]	`Number`	`30000`
[idleTimeoutMillis]	`Number`	`30000`
[reapIntervalMillis]	`Number`	`1000`
[createRetryIntervalMillis]	`Number`	`200`

Rhino.RhinoBaseConfiguration

Kind: static typedef of Rhino
Properties

Name	Type
[pool]	`PoolConfiguration`
[logging]	`LogConfiguration`

Rhino.RhinoConfiguration : `TediousConfiguration` | `RhinoBaseConfiguration`

Rhino's configuration fully implements all configuration properties from tedious.

Kind: static typedef of Rhino
See

Tedious
Tarn

Transaction

The Transaction class provides the ability to queue multiple queries for execution under a SQL transaction, optionally including save-points. It exposes methods to commit and rollback the entire set of queries or to a particular save-point.

Kind: global class

Transaction
- new Transaction(pool)
- instance
  - .pool : tarn.Pool
  - .queries : Array.<(Query|Transaction.SavePoint)>
  - .query(sql, [params]) ⇒ Query
  - .savePoint([name]) ⇒ String
  - .clear()
  - .commit([txName], [isolation]) ⇒ Promise.<(Result|Array.<Result>)>
  - .rollback([name])
  - ._releaseConnection()
- static
  - .SavePoint : Object

new Transaction(pool)

Creates a new instance of a Transaction.

Param	Type	Description
pool	`tarn.Pool`	The connection pool to utilize for aquiring the connection.

transaction.pool : `tarn.Pool`

The tarn.Pool instance linked to this query.

Kind: instance property of Transaction

transaction.queries : `Array.<(Query|Transaction.SavePoint)>`

Kind: instance property of Transaction

transaction.query(sql, [params]) ⇒ `Query`

Runs a SQL statement on the database and returns the results.

Kind: instance method of Transaction

Param	Type	Description
sql	`String`	The SQL statement to execute.
[params]	`Map.<String, *>` \| `Object`	Optional parameters `Object` or `Map` that will be added to the "in" parameters of the query. Keys and property names are used as the parameter name, and the value as the parameter values.

transaction.savePoint([name]) ⇒ `String`

Add a save-point to the transaction. This will follow the previously added query.

Kind: instance method of Transaction
Returns: String - Returns the name of the save-point.
Throws:

Error if no queries are present. A save-point should follow at least one query.

Param	Type	Description
[name]	`String`	The name of the transaction savepoint. If no name is specified, one is automatically generated. You can use this name with the rollback command.

transaction.clear()

Remove all queued queries from the transaction.

Kind: instance method of Transaction

transaction.commit([txName], [isolation]) ⇒ `Promise.<(Result|Array.<Result>)>`

Commits all queries in the transaction queue.

Kind: instance method of Transaction
Throws:

Error if the pool property is falsey.
Error when a txName argument is not present and an isolation argument is specified.
Error if there is an active connection already processing a transaction.

See

Microsoft documentation on transaction isolation levels.
Connection.TediousConfiguration.options.isolationLevel
Connection.TediousConfiguration.options.connectionIsolationLevel

Param	Type	Description
[txName]	`String`	= A name associated with the transaction - this is required when specifying an `isolation` argument value.
[isolation]	`tedious.ISOLATION_LEVEL` \| `Number` \| `String`	The isolation level of the transaction. Values can be numbers or strings corresponding to the `Transaction.ISOLATION_LEVEL` enum. For example: - READ_UNCOMMITTED - READ_COMMITTED - REPEATABLE_READ - SERIALIZABLE - SNAPSHOT Defaults to the connection's isolation level, which is usually "READ_COMMITED".

transaction.rollback([name])

Rolls back the active transaction.

Kind: instance method of Transaction
Throws:

Error if the pool property is falsey.
Error if there is no active transaction connection.
Error if the active connection does not have an active transaction.

Param	Type	Description
[name]	`String`	The name of a savepoint to rollback to. If not specified, the entire transaction will be rolled back.

transaction._releaseConnection()

Releases the connection if it is attached. The connection is released back to the rhino pool.

Kind: instance method of Transaction

Transaction.SavePoint : `Object`

Kind: static typedef of Transaction
Properties

Name	Type	Default
savepoint	`Boolean`	`true`
name	`String`

PromiseBulkQuery : `BulkQuery` | `Promise.<Result>`

Kind: global typedef

PromiseQuery : `Query` | `Promise.<Result>`

Kind: global typedef

SQLParameter

Kind: global typedef

Param	Type
value	`*`
type	`TDSType`
options	`Object`

QueryTypes

Kind: global typedef
Properties

Name	Type
TinyInt	`TDSType`
Bit	`TDSType`
SmallInt	`TDSType`
Int	`TDSType`
SmallDateTime	`TDSType`
Real	`TDSType`
Money	`TDSType`
DateTime	`TDSType`
Float	`TDSType`
Decimal	`TDSType`
Numeric	`TDSType`
SmallMoney	`TDSType`
BigInt	`TDSType`
Image	`TDSType`
Text	`TDSType`
UniqueIdentifier	`TDSType`
NText	`TDSType`
VarBinary	`TDSType`
VarChar	`TDSType`
Binary	`TDSType`
Char	`TDSType`
NVarChar	`TDSType`
NChar	`TDSType`
Xml	`TDSType`
Time	`TDSType`
Date	`TDSType`
DateTime2	`TDSType`
DateTimeOffset	`TDSType`
UDT	`TDSType`
TVP	`TDSType`
Variant	`TDSType`

Project Maintenance

Unit Testing

Unit-testing this driver requires a Microsoft SQL Server instance running in docker from the chriseaton/adventureworks image. Due to the fragile nature of the database unit-testing, and to avoid collisions with other users, it's recommended to use the process described below (docker is required).

1. Run the container.

You need to run the chriseaton/adventureworks container from the built image. This will spin up the server and run the install script. It is usually ideal to run the container in daemon mode (-d), as the container will stay alive until stopped.

docker run -p 1433:1433 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=YourStr0ng_PasswordHERE' --name rhino_test -d chriseaton/adventureworks:latest

When run using the command above, the docker server will be accessible on localhost port 1433. To kill the container, run:

docker rm -f rhino_test

2. Setup testing environment.

Configure a .env file in the root project folder and define the variables for connecting:

RHINO_MSSQL_HOST = localhost
RHINO_MSSQL_USER = sa
RHINO_MSSQL_PASSWORD = YourStr0ng_PasswordHERE
RHINO_MSSQL_DATABASE = AdventureWorks

You should repleace the RHINO_MSSQL_PASSWORD password with your own uniquely generated strong password used in the docker run command from step 1.

3. Run tests.

If you just executed the docker run command in step 1, you may need to wait a few seconds for the container to finish loading.

You can check if loading is complete when the docker logs rhino_test | grep 'Server is ready.' returns a ready message. Now that the test database server is up and running, you can run the Rhino unit-tests:

npm test

Troubleshooting

You can view the container logs to see the output from the server, including any runtime failures.

Show the running containers:

docker ls

Show the output from a container:

docker logs {container ID or Name here}

Updating the API/Readme

The README.md file in this project is generated using the js-to-markdown package, essentially merging the JSdoc output into the README.hbs handlebars template file.

To rebuild the README.md file, simply run:

npm run doc

Issues / Requests / Contributing

Please utilize the issues on the project to report a problem or provide feedback. Additional contributors are welcome.

Make sure the issue is with rhino and not the tedious package.
Gather details, your node and rhino version.
Provide as much information as possible, including steps to reporoduce the issue. Or better yet, provide a resolution with a merge request.

Name		Name	Last commit message	Last commit date
Latest commit History 121 Commits
.github/ISSUE_TEMPLATE		.github/ISSUE_TEMPLATE
test		test
.eslintignore		.eslintignore
.eslintrc.json		.eslintrc.json
.gitignore		.gitignore
LICENSE		LICENSE
README.hbs		README.hbs
README.md		README.md
bulk-query.js		bulk-query.js
connected-query.js		connected-query.js
connected-query.test.js		connected-query.test.js
connection.js		connection.js
connection.test.js		connection.test.js
event-tracker.js		event-tracker.js
event-tracker.test.js		event-tracker.test.js
log.js		log.js
package-lock.json		package-lock.json
package.json		package.json
query.js		query.js
query.test.js		query.test.js
result.js		result.js
result.test.js		result.test.js
rhino.js		rhino.js
rhino.png		rhino.png
rhino.test.js		rhino.test.js
transaction.js		transaction.js
transaction.test.js		transaction.test.js

License

chriseaton/rhino

Folders and files

Latest commit

History

Repository files navigation

Rhino

Feature list

Installation

Quick Start

API

Classes

Typedefs

BulkQuery

new BulkQuery(tableName, options, pool)

bulkQuery.tableName : String

bulkQuery.options : Options

bulkQuery.pool : tarn.Pool

bulkQuery.aquire() ⇒ BulkQuery

bulkQuery.execute()

bulkQuery.column(name, type, options) ⇒ BulkQuery

bulkQuery.add(...rows) ⇒ BulkQuery

BulkQuery.Options

ConnectedQuery

new ConnectedQuery(pool)

connectedQuery.pool : tarn.Pool

connectedQuery.then([resolve], [reject]) ⇒ Promise.<(Result|Array.<Result>)>

Connection

new Connection(tdsConfig, log)

connection.config : TediousConfiguration

connection.log : Log

connection.connected : Boolean

connection.state : Number

connection.id : String

connection.connect() ⇒ Promise.<Connection>

connection.disconnect() ⇒ Promise.<Connection>

"transition"

Connection.CONNECTION_STATE : enum

Connection.TediousConfiguration

EventTracker

new EventTracker()

eventTracker.listeners : Array.<RegisteredEventListener>

eventTracker.removeFrom(emitter, [event], [unregister])

eventTracker.register(event, ...listeners)

eventTracker.registerOn(emitters, event, ...listeners)

eventTracker.unregister([event], [...listeners])

EventTracker.RegisteredEventListener

Log

log.config : LogConfiguration

log.error()

log.warn()

log.debug()

Log.LogConfiguration

Query

new Query()

query.statement : String

query.params : Map.<String, Query.Parameter>

query.mode

query.requestTimeout : Number

query.timeout(ms) ⇒ Query

query.sql(statement, [params]) ⇒ Query

query.batch() ⇒ Query

query.exec() ⇒ Query

query.param(name, [value], [type], [dir], [options]) ⇒ Query

query.in(name, [value], [type], [options]) ⇒ Query

query.out(name, [value], [type], [options]) ⇒ Query

query.remove(name) ⇒ Boolean

query.clear()

Query.TYPE : QueryTypes

Query.AUTODETECT_TYPES

AUTODETECT_TYPES.FLOATING_POINT

AUTODETECT_TYPES.DATE

AUTODETECT_TYPES.BUFFER

Query.PARAM_DIR : enum

Query.MODE : enum

Query.TDSType

Query.Parameter

Rhino

new Rhino([config])

rhino.config : RhinoConfiguration

bulkQuery.tableName : `String`

bulkQuery.options : `Options`

bulkQuery.pool : `tarn.Pool`

bulkQuery.aquire() ⇒ `BulkQuery`

bulkQuery.column(name, type, options) ⇒ `BulkQuery`

bulkQuery.add(...rows) ⇒ `BulkQuery`

connectedQuery.pool : `tarn.Pool`

connectedQuery.then([resolve], [reject]) ⇒ `Promise.<(Result|Array.<Result>)>`

connection.config : `TediousConfiguration`

connection.log : `Log`

connection.connected : `Boolean`

connection.state : `Number`

connection.id : `String`

connection.connect() ⇒ `Promise.<Connection>`

connection.disconnect() ⇒ `Promise.<Connection>`

Connection.CONNECTION_STATE : `enum`

eventTracker.listeners : `Array.<RegisteredEventListener>`

log.config : `LogConfiguration`

query.statement : `String`

query.params : `Map.<String, Query.Parameter>`

query.requestTimeout : `Number`

query.timeout(ms) ⇒ `Query`

query.sql(statement, [params]) ⇒ `Query`

query.batch() ⇒ `Query`

query.exec() ⇒ `Query`

query.param(name, [value], [type], [dir], [options]) ⇒ `Query`

query.in(name, [value], [type], [options]) ⇒ `Query`

query.out(name, [value], [type], [options]) ⇒ `Query`

query.remove(name) ⇒ `Boolean`

Query.TYPE : `QueryTypes`

Query.PARAM_DIR : `enum`

Query.MODE : `enum`

rhino.config : `RhinoConfiguration`

rhino.log : `Log`

rhino.ping() ⇒ `Boolean`

rhino.query(sql, [params]) ⇒ `ConnectedQuery` | `Promise.<Result>`

rhino.bulk(tableName, options) ⇒ `BulkQuery`

Rhino.create([config]) ⇒ `Rhino`

Rhino.defaultConfig([config]) ⇒ `RhinoConfiguration`

Rhino.RhinoConfiguration : `TediousConfiguration` | `RhinoBaseConfiguration`

transaction.pool : `tarn.Pool`

transaction.queries : `Array.<(Query|Transaction.SavePoint)>`

transaction.query(sql, [params]) ⇒ `Query`

transaction.savePoint([name]) ⇒ `String`

transaction.commit([txName], [isolation]) ⇒ `Promise.<(Result|Array.<Result>)>`

Transaction.SavePoint : `Object`

PromiseBulkQuery : `BulkQuery` | `Promise.<Result>`

PromiseQuery : `Query` | `Promise.<Result>`

Packages