Nk-mssql NPM | npm.io

NK-MSSQL

MSSQL Connection Class for the NK Node Package

Installation

Install using NPM

npm i nk-mssql --save

How to use

MSSQL is the Industry leader of Relational Database with Error Protection

Start and connect to server

const NKMSSQL = require( 'nk-mssql' )
//                  dbName,         ip,   port, instance, user, pass, timeoutInMS, useEncryptedConnection, callback
NKMSSQL.start( 'MyDatabase', '127.0.0.1', 1433, null, null, null, null, true, ( isError, errorMessage ) => {
  //Super duper awesome code here!
  console.log( isError, errorMessage )
})

The database connection object is saved in the NKMSSQL Object, indexed by the database name, so there is a caveat to not use the same database name across distinct servers.

Start and connect to multiple servers

const NKMSSQL = require( 'nk-mssql' )
NKMSSQL.start( 'MyDatabase', '127.0.0.1', 1433, null, null, null, ( isError1, errorMessage1 ) => NKMSSQL.start( 'RemoteDB1', 'remote.mydomain.com', 1433, null, null, null, ( isError2, errorMessage2 ) => NKMSSQL.start( 'RemoteDB2', 'remote2.mydomain.com', 1433, null, null, null, ( isError3, errorMessage3 ) => {
  console.log( isError1, errorMessage1, isError2, errorMessage2, isError3, errorMessage3 )
  //WHAT?! Yes, you can connect to multiple servers in the same core, using them as objects for real-time compliances
})))

Common Utility Functions

Insert a new row or a set of rows

//rowOrRows can be an array of objects to insertMany or an individual object to insert a single row.
//                    dbName, table,      rowOrRows,                                                                        callback
NKMSSQL.insert( 'MyDatabase', 'users', { username: 'jose', pass: '123', active: false, added: ( new Date() ).getTime() }, () => console.log( 'all done' ) )

Delete rows from the database

//                    dbName, table,     dataToRemove,                      callback
NKMSSQL.delete( 'MyDatabase', 'users', { myuser: user.id }, () => console.log( 'all done' ) )

Update rows in the database

//                    dbName, table,      dataToUpdate,       newData,        callback
NKMSSQL.update( 'MyDatabase', 'users', { active: false }, { active: true }, () => console.log( 'all done' ) )

Querying the database

Query which should return ONLY ONE ROW

//                        dbName, table,      query,                              callback
NKMSSQL.singleQuery( 'MyDatabase', 'users', { myuser: user.id }, rowFromQuery => console.log( rowFromQuery ) )

This singleQuery is very useful in the authentication methods, e.g.

NKMSSQL.singleQuery( 'MyDatabase', 'users', { loginSessionKey: req.sessionKey }, rowFromQuery => res.json( rowFromQuery? true: false ) )

Run Query

//                  dbName, table,      query,          callback
NKMSSQL.query( 'MyDatabase', 'users', { active: true }, rowsFromQuery => console.log( rowsFromQuery ) )

Run Query with a sort by set of data

//                      dbName, table,    sortBy,       query,              callback
NKMSSQL.querySort( 'MyDatabase', 'users', { added: 1 }, { active: true }, rowsFromQuery => console.log( rowsFromQuery ) )

Run Query with a sort by and limit to row count set of data

//                      dbName,       table,  max,  sortBy,       query,            callback
NKMSSQL.queryLimitSort( 'MyDatabase', 'users', 100, { added: 1 }, { active: true }, rowsFromQuery => console.log( rowsFromQuery ) )

Run a query to perform only one JOIN to another table

//              dbName,     table, tableIDField, joinTo, joinToIDField, joinedToElement, sortBy, query, callback
NKMSSQL.join( 'MyDatabase', 'users', '_id', 'photos', 'user_id', 'photos', { added: 1 }, { myuser: user.id }, rowsFromQuery => console.log( rowsFromQuery ) )

Run a query, performing a list of JOINS defined in the query

const joins = [{ from: 'photos', field: '_id', fromField: 'user_id', as: 'photos' },
              { from: 'history', field: '_id', fromField: 'user_id', as: 'transactions' }]
//                      dbName, table,    joins, max,   sortBy,       query,            callback
NKMSSQL.joinsLimit( 'MyDatabase', 'users', joins, 100, { added: 1 }, { active: true }, rowsFromQuery => console.log( rowsFromQuery ) )