Oro MS SQL

• Overview
• Installation
• Example
• Methods
  • new OMSSql()
  • await .poolOpen()
  • await .poolClose()
  • .getClient()
  • .getDB()
  • .getInfo()
  • .getStatus()
  • .getAllQueries( raw = false )
  • .getLastQuery( offset = 0, raw = false )
  • .getFirstQuery( offset = 0, raw = false )
  • .getAffectedRows()
  • .sanitize( value )
  • await .queryOnce( query, format = 'default', valueKey = 0, valueId = 0, fnSanitize = null )
  • await .query( query, format = 'default', valueKey = 0, valueId = 0, fnSanitize = null )

Overview

Class OroMSSql is a wrapper of node-mssql to use async/await.

node-mssql is a Microsoft SQL Server client API Wrapper for node.js.

Installation

npm install oro-mssql

Example:

const { OMSSql } = require( 'oro-mssql' );

const settings = {
    host: 'localhost',
    database: 'custom-database',
    user: 'custom-user',
    password: 'custom-password',
}

const server = new OMSSql( { settings } );

const poolOpen = await server.poolOpen();
if( ! poolOpen.status ) { return poolOpen; }

const arr = server.query( "SELECT * FROM table", 'array' );
// [ row, ... ]

const row = server.query( "SELECT * FROM table WHERE id = 7", 'row' );
// { column: value, ... }

const poolClose = await server.poolClose();
if( ! poolClose.status ) { return poolClose; }

Methods

new OMSSql( { settings } )

const { OMSSql } = require( 'oro-mssql' );

const settings = {
    host: 'localhost',
    database: '',
    user: 'root',
    password: ''
}

const server = new OMSSql( { settings } );

await .poolOpen()

When it opens pool, the connection to database is created to execute queries.

const poolOpen = await server.poolOpen();
console.log( poolOpen ); // { status: true|false }

await .poolClose()

To close the opened pool.

const poolOpen = await server.poolOpen();
console.log( poolOpen ); // { status: true|false }

.getClient()

If you want to use the library mssql, you can get the class.

const mssql = server.getClient();

.getDB()

When pool is opened, you can get the npm-mssql conn object.

const db = server.getDB();

.getInfo()

Get settings info (without the password).

const info = server.getInfo();

.getStatus()

Get the status object. If status is false, show the error message.

status is only true when pool is opened and it's enabled to call a query.

const statusObj = server.getStatus();
console.log( statusObj ); // { status: true|false }

Another way to simplify getting the status is directly with using the property server.status.

console.log( server.status ); // true|false

.getAllQueries( raw = false )

Get all resultArray of the queries are saved in a heap.

Note: By default, you get a deep copy of each resultArray to avoid modify data, but if you need a better performance and you understand what are you doing, you can get the resultArray as shallow copy.

const allResults = server.getAllQueries();
console.log( allResults ); // [ resultArray, ... ]

.getLastQuery( offset = 0, raw = false )

Get the last resultArray of the queries, with the param offset you can get the preceding queries.

Note: By default, you get a deep copy of the resultArray to avoid modify data, but if you need a better performance and you understand what are you doing, you can get the resultArray as shallow copy.

const lastResult = server.getLastQuery();
console.log( lastResult ); // resultArray

.getFirstQuery( offset = 0, raw = false )

Get the first resultArray of the queries, with the param offset you can get the following queries.

Note: By default, you get a deep copy of each resultArray to avoid modify data, but if you need a better performance and you understand what are you doing, you can get the resultArray as shallow copy.

const firstResult = server.getFirstQuery();
console.log( firstResult ); // resultArray

.getAffectedRows()

Get the total number of rows that are affected in the last query.

const count = server.getAffectedRows();
console.log( count ); // integer

.sanitize( value )

Sanitize the value to avoid code injections.

const valNumber = server.sanitize( 20 );
console.log( valNumber ); // `20`

const valString = server.sanitize( "chacho" );
console.log( valString ); // `'chacho'`

const valInjection = server.sanitize( "' OR 1 = 1" );
console.log( valInjection ); // `'\' OR 1 = 1'`

Note: It could be called as static too.

await .queryOnce( query, format = 'default', valueKey = 0, valueId = 0, fnSanitize = null )

If you just need to call only one query, this function calls poolOpen, query, poolClose respectively.

await .query( query, format = 'default', valueKey = 0, valueId = 0, fnSanitize = null )

You can choose the format that return the query.

By default the returned object is resultArray. This object extends from Array and it has extra params.

{
    status = true || false,
    count = 0, // affected row
    statement = 'QUERY';
    columns = []; // table columns data
    error?: { msg: 'error reason', ... } // only when status is false
}

(await .query) Parameters

1. query: String "SELECT * FROM table".
2. format: String, Allowed values: default,id,bool,count,value,values,valuesById,array,arrayById,rowStrict,row.
3. valueKey: String|Number, name or position of the column to get the value.
4. valueId: String|Number, name or position of the column to use as param.
5. fnSanitize: Null|Function, function to map each value. Note: If format is row|array, it maps each column-value, not the whole object.

(await .query) Formats

• default, return object resultArray.

const resultArray = server.query( "SELECT * FROM table" );
// [ 
//   0: { ... }, 
//   1: { ... }
//   status: true, 
//   count: 2, 
//   statement: "SELECT * FROM table", 
//   columns: [ ... ] 
// ]

• id, if the query is an INSERT and the table has an AUTO_INCREMENT value (i.e. a primary key), this incremented value is returned.

const id = server.query( "INSERT INTO table VALUES ( ... )", 'id' );
// 17

• bool, if the query has affected rows it returned true.

const result = server.query( "UPDATE table SET value WHERE condition", 'bool' );
// true

• count, return number of affected rows.

const count = server.query( "SELECT * FROM table", 'count' );
// 2

• value, return the first column value.

const value = server.query( "SELECT column FROM table", 'value' );
// column-value

const value2 = server.query( "SELECT * FROM table", 'value' );
// column1-value

const value2 = server.query( "SELECT * FROM table", 'value', 'column_size' );
// column_size-value

• values, return array of column values.

const values = server.query( "SELECT column FROM table", 'values' );
// [ column-value, ... ]

const values = server.query( "SELECT * FROM table", 'values', 'column2' );
// [ column2-value, ... ]

• valuesById, return object of values with key as second column-value.

const valuesById = server.query( "SELECT * FROM table", 'valuesById', 'column', 'column2' );
// { "column2-value": column-value, ... }

• array, return array of object-row.

const arr = server.query( "SELECT * FROM table", 'array' );
// [ row, ... ]

• arrayById, return object of object-row with key as column-value.

const arr = server.query( "SELECT * FROM table", 'arrayById', 'column' );
// { "column-value": row, ... }

• row, return object row.

const arr = server.query( "SELECT * FROM table", 'row' );
// row

• rowStrict, return object row without columns with falsy values.

const arr = server.query( "SELECT * FROM table", 'rowStrict' );
// { row }

Testing

If you want to run npm run test, you can create your own ./test/config.json (you can copypaste it from ./test/config-default.json).

{
  "host": "localhost",
  "database": null,
  "user": "root",
  "password": ""
}

ADVISE: When run the testing, it's created and removed the database test_oromssql, so if config.user has not permission to create database, you should create the database test_oromssql manually.

On the other hand, if in your mssql already exist test_oromssql and it's required for you, avoid to run test.