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 ) )

Contributing

Pull requests are welcome. For major changes, please open an issue first to discuss what you would like to change.

Please make sure to update tests as appropriate.

License

MIT