vq-mssql

A simple wrapper for the MSSQL-Server library node-sqlserver-v8.

All connection methods like .query() or .transaction() supports an optional callback. If the callback is missing, a promise object will be returned.

Install

npm install vq-mssql --save

Examples

var MsSqlConnection = require('vq-mssql');
var connectionString = `Driver={SQL Server Native Client 11.0};Server=localhost;Database=MyDatabase;Trusted_Connection={Yes};`

var db1 = new MsSqlConnection(connectionString);

//Use a callback
db1.query('SELECT * FROM Table1', function(err, result) {
  if(err) throw err;
  console.log(result);
});

//get a promise
db1.query('SELECT * FROM Table1')
.then(function(result) {
  console.log(result)
})
.catch(function(err) {
  throw err;
});

API

MsSqlConnection(options)

Creates a new database connection. If options is a string, it is used as connection string.

var db1 = new MsSqlConnection(`Driver={SQL Server Native Client 11.0};Server=db1-muc\\SQL5;Database=Database_1;Trusted_Connection={Yes};`);

options object

• connectionString
• connectionSettings
• driver
• provider
• server
• database
• useTrustedConnection
• dbUser
• dbPassword
• logger (provide a log function like winston.log)

var db1 = new MsSqlConnection({
  connectionSettings: {
    driver: '{SQL Server Native Client 11.0}',
    server: 'localhost\\SQL5',
    database: 'Database_1',
    useTrustedConnection: true
  }
});

var db2 = new MsSqlConnection({
  connectionSettings: {
    driver: '{SQL Server Native Client 11.0}',
    server: 'localhost\\SQL5',
    database: 'Database_2',
    dbUser: '...',
    dbPassword: '...'
  }
});

Connection

.query(queryStr [, parameters] [, callback])

var db1 = new MsSqlConnection(connectionString);
//Use a callback
db1.query('SELECT ID FROM Table1', function(err, result) {
  if(err) throw err;
  console.log(result);
});
//return a promise if the callback is missing
db1.query('SELECT ID FROM Table1')
.then(function(result){
  console.log(result);
})
.catch(function(err){
  throw err;
});

The parameter source is used for logging.

.transaction(queryStr [, callback])

Creates a transaction with auto-commit and auto-rollback on error.

var db1 = new MsSqlConnection(connectionString);
db1.transaction(`
  INSERT INTO dbo.Table1 (id, col1) values (1, 'test 1')
  INSERT INTO dbo.Table1 (id, col1) values (2, 'test 2')
  INSERT INTO dbo.Table1 (id, col1) values (3, 'test 3')
  INSERT INTO dbo.Table1 (id, col1) values (4, 'test 4')
  INSERT INTO dbo.Table1 (id, col1) values (5, 'test 5')
`)
.then(function() {
  console.log('Commited');
})
.catch(function(err) {
  throw err;
})

//with array of querys
var queries = [
    `INSERT INTO dbo.Table1 (id, col1) values (-9991, 'test 1')`,
    `INSERT INTO dbo.Table1 (id, col1) values (-9992, 'test 2')`,
    `INSERT INTO dbo.Table1 (id, col1) values (-9993, 'test 3')`,
    `INSERT INTO dbo.Table1 (id, col1) values (-9994, 'test 4')`,
    `INSERT INTO dbo.Table1 (id, col1) values (-9995, 'test 5')`
  ];
db1.transaction(queries)
.then(function() {
  console.log('Commited');
})
.catch(function(err) {
  throw err;
})

.bulkInsert(table, data [, callback])

var db1 = new MsSqlConnection(connectionString);
db1.bulkInsert('dbo.Table1', [{
  ID: 9981,
  Col1: 'col1 9981',
  Col2: 'col2 9981'
}, {
  ID: 9982,
  Col1: 'col1 9982',
  Col2: 'col2 9982'
}, {
  ID: 9983,
  Col1: 'col1 9983',
  Col2: 'col2 9983'
}, {
  ID: 9984,
  Col1: 'col1 9984',
  Col2: 'col2 9984'
}, {
  ID: 9985,
  Col1: 'col1 9985',
  Col2: 'col2 9985'
}])
.then(function() {
  console.log('Done')
})
.catch(function(err) {
  throw err;
})

.procedure(storedProcedureName [, parameters] [, callback])

Stored Procedure support

// without parameters
db1.procedure('dbo.sp_test1')
.then(function (data) {
  t.same([{col1: 'Test1'}], data.result)
  t.end()
})
.catch(function (err) {
  t.error(err)
  t.end()
})

// with 1 parameter 
db1.proc('[dbo].[sp_test2]', 'A test')
.then((result) => {
  console.log(result)
})
.catch((err) => {
  console.error(err)
})

// with 2 or more parameters
db1.proc('[dbo].[sp_test3]', [123, 987])
.then((result) => {
  console.log(result)
})
.catch((err) => {
  console.error(err)
})

Synchronized queries

With the power of the DeAsync.js module (https://github.com/abbr/deasync)

querySync(queryStr [, parameters])

try {
  var result = db1.querySync('SELECT id FROM dbo.Table1 where id=?', sql.Int(1))
  console.log(result)
} catch (err) {
  console.error(err)
}