README.hbs

![Rhino](./rhino.png)

[![NPM](https://img.shields.io/npm/v/rhino)](https://npmjs.org/package/rhino)
[![License](https://img.shields.io/npm/l/rhino)](https://npmjs.org/package/rhino)
[![Downloads](https://img.shields.io/npm/dt/rhino)](https://npmjs.org/package/rhino)

# Rhino

Rhino is a tough, production-focused Node.js Microsoft SQL Server driver that incorporates pooling and runs the 
well-supported [tedious](http://tediousjs.github.io/tedious/) package under
the hood, fully utilizing all of it's [available configuration options](http://tediousjs.github.io/tedious/api-connection.html#function_newConnection). 
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](https://code.visualstudio.com/) auto-completion.
- A dependency list so small we can list it here: [tedious](https://github.com/tediousjs/tedious) and [tarn](https://github.com/Vincit/tarn.js).
- 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
- [x] Automatic connection management.
- [x] Query execution:
  - [x] Simple SQL statements.
  - [x] SQL statements with parameters.
  - [x] SQL statements using parameter (mapped) objects.
  - [x] Batch SQL queries (no parameters).
  - [x] Batch SQL queries returning multiple result-sets.
  - [x] Stored procedure execution with parameters.
  - [x] Stored procedures returning multiple result-sets.
- [x] Bulk loads.
- [x] Single-Level transactions.
- [x] Transaction save-point support.
- [ ] Nested transactions. 
- [ ] Streaming query results.

## Installation

```sh
npm i rhino --save
```
*or*
```sh
yarn add rhino
```

## Quick Start

```js
// 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
    }
});
```
```js
// run a simple query
let results = await db.query('SELECT * FROM dbo.People');
console.log(`Count: ${results.count}`);
console.table(results.rows);
```
```js
// 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' }
);
```
```js
// 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;
}
```
```js
// 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();
```
```js
...
// all done, forever!
// clean up resources
db.destroy(); 
```

# API 

{{>main}}

# 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](https://www.docker.com/products/docker-engine) 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](https://github.com/chriseaton/rhino/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](https://github.com/tediousjs/tedious/issues) 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.