rhino v5.5.8

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

• Automatic connection management.
• Query execution:
  • Simple SQL statements.
  • SQL statements with parameters.
  • SQL statements using parameter (mapped) objects.
  • Batch SQL queries (no parameters).
  • Batch SQL queries returning multiple result-sets.
  • Stored procedure execution with parameters.
  • Stored procedures returning multiple result-sets.
• Bulk loads.
• Single-Level transactions.
• Transaction save-point support.
• Nested transactions.
• Streaming query results.

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

Typedefs

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.

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

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.

BulkQuery.Options

Kind: static typedef of BulkQuery
Properties

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.

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.

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.

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

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

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()
  • instance
    • .listeners : Array.<RegisteredEventListener>
    • .removeFrom(emitter, [event], [unregister])
    • .register(event, ...listeners)
    • .registerOn(emitters, event, ...listeners)
    • .unregister([event], [...listeners])
  • static
    • .RegisteredEventListener

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

eventTracker.register(event, ...listeners)

Registers one or more event listeners.

Kind: instance method of EventTracker

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

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

EventTracker.RegisteredEventListener

Kind: static typedef of EventTracker
Properties

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

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
      • .FLOATING_POINT
      • .DATE
      • .BUFFER
    • .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).

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.

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.

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.

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.

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.

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
  • .FLOATING_POINT
  • .DATE
  • .BUFFER

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

Query.MODE : enum

The mode that determines how the query should be executed.

Kind: static enum of Query
Properties

Query.TDSType

Kind: static typedef of Query
Properties

Query.Parameter

Kind: static typedef of Query
Properties

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.

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

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

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

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

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

Rhino.PoolConfiguration

Please refer to: Tarn on GitHub

Kind: static typedef of Rhino
Properties

Rhino.RhinoBaseConfiguration

Kind: static typedef of Rhino
Properties

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.

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

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.

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

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.

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

PromiseBulkQuery : BulkQuery | Promise.<Result>

Kind: global typedef

PromiseQuery : Query | Promise.<Result>

Kind: global typedef

SQLParameter

Kind: global typedef

QueryTypes

Kind: global typedef
Properties

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.

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