ms sql resource management using bluebird promises

Question

I'm using the mssql module to connect to a sql server database using node. Bluebird has a feature that's similar to resource management in c#. It has a 'using' method to avoid having to use try/catch/finall to dispose of the resources. They have examples for pg and mysql, but they don't have an example for mssql which doesn't create a connection the same way as pg and mysql. Here's an example of how to use it:

using(getConnection(),
  fs.readFileAsync("file.sql", "utf8"), function(connection, fileContents) {
  return connection.query(fileContents);
}).then(function() {
  console.log("query successful and connection closed");
});

But to be able to use this method, you need to create a connection method which describes how to close the connection. Here's an example for pg:

function getSqlConnection(connectionString) {
   var close;
   return pg.connectAsync(connectionString).spread(function(client, done) {
      close = done;
      return client;
   }).disposer(function(client) {
      if (close) close(client);
   });
}

The problem I'm having with mssql module is that the connect method doesn't return a connection object like pg or even the mysql module. Has anyone been able to do this with mssql?

Update 1:

Here's how I made the transaction disposer:

function getTransaction(connection) {
   return new Promise(function(resolve, reject) {
       var tx = sql.Transaction(connection);
       tx.beginAsync().then(function(err) {
           if(err) {
             tx = null;
             return reject(err);
           }
           return resolve(tx);
       });
   }).disposer(function(tx, promise) {
       if(promise.isFulfilled()) {
          return tx.commitAsync();
       }
       else {
          return tx.rollbackAsync();
       }
   });
}

It seems to be working, but not sure if this is efficient. Now I need to figure out how to catch errors on a query.

This is how I'm doing a transaction:

using(getConnection(), function(connection) {
   return using(getTransaction(connection), function(tx) {
          return query(queryString, tx).then(function() {
               console.log('first query in transaction completed.');
               console.log('starting second query in transaction.');
               return query(anotherQueryString, tx);
          });
   });
});

If I tag a single catch to the outer 'using', will that catch all errors from the whole transaction?

Answer 1

Good question, mssql has really tricky API (constructors taking callbacks!) so this is good addition to the documentation.

var Promise = require("bluebird");
var sql = Promise.promisifyAll(require("mssql"));
global.using = Promise.using;

function getConnection(config) {
    var connection;
    return new Promise(function(resolve, reject) 
        connection = new sql.Connection(config, function(err) {
            if (err) {
                connection = null;
                return reject(err);
            }
            resolve(connection);
        });
    }).disposer(function() {
      if (connection) connection.close();
   });
}

var config = {
    user: '...',
    password: '...',
    server: 'localhost',
    database: '...',
};

using(getConnection(config), function(connection) {
    var request = new sql.Request(connection);
    return request.queryAsync("select 1 as number").then(function(recordSet) {
        console.log("got record set", recordSet);
        return request.queryAsync("select 10 as number");
    });
}).then(function(recordSet) {
    console.log("got record set", recordSet);
})

To use the transaction, try implementing getTransaction like:

function getTransaction(connection) {
  var tx = new sql.Transaction(connection);
  return tx.beginAsync().thenReturn(tx).disposer(function(tx, promise) {
    return promise.isFulfilled() ? tx.commitAsync() : tx.rollbackAsync();
  });
}

And using it like:

using(getConnection(), function(connection) {
  return using(getTransaction(connection), function(tx) {
    var request = new sql.Request(tx);
    return request.queryAsync("INSERT 1...").then(function() {
      return request.queryAsync("INSERT 2...");
    }).then(function() {
      return request.queryAsync("INSERT 3...");
    });
  });
});

Error handling:

using(getConnection(), function(connection) {
  return using(getTransaction(connection), function(tx) {
    var request = new sql.Request(tx);
    return request.queryAsync("INSERT...");
  });
}).catch(sql.TransactionError, function(e) {
  console.log("transaction failed", e);
}).catch(sql.ConnectionError, function(e) {
  console.log("connection failed", e);
}).catch(sql.RequestError, function(e) {
  console.log("invalid query", e);
});