How do you make multiple database calls from a single connection/transaction with Node.js and Tedious

Question

I am attempting to use NodeJS with the Tedious (http://pekim.github.io/tedious/) sql server plugin to make multiple database calls. My intent is to: 1. Open a connection 2. Start a transaction 3. Make multiple database (stored procedure) calls, which will not return any data. 4. Commit transaction (or roll back on error). 5. Close connection

Here is an example .js file, (without using a transaction) for NodeJS where I am attempting to make multiple database calls and it is failing with the error "Requests can only be made in the LoggedIn state, not the SentClientRequest state." Nothing I try resolves this issue.

Does anyone know how to resolve this?

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;

var config = {
    userName: 'login',
    password: 'password',
    server: '127.0.0.1',
    options: { rowCollectionOnDone: true }
};

var max = 1;
for (var i = 0; i < max; i++) {
    var connection = new Connection(config);

    function executeStatement() {
        request = new Request("select 42, 'hello world'", function (err, rowCount) {
            if (err) {
                console.log(err);
            } else {
                console.log(rowCount + ' rows');
            }
        });

        request.on('row', function (columns) {
            columns.forEach(function (column) {
                console.log(column.value);
            });
        });

        request.on('doneInProc', function (rowCount, more, rows) {
        });

        request.on('doneProc', function (rowCount, more, rows) {
            console.log('statement completed!')
            connection.execSql(request);
        });

        request.on('returnStatus', function (status) {
            console.log('statement completed!')
        });

        connection.execSql(request);
    }

    connection.on('connect', function (err) {
        // If no error, then good to go...
        executeStatement();
    });
}
console.log('Done!');

Answer 1

You're trying to execute a statement on a connection that is not established. You're missing an error handler before you call executeStatement.

connection.on('connect', function (err) {
    if (err) {
        console.log(err); // replace with your code
        return;
    };

    // If no error, then good to go...
    executeStatement();
});

Edit:

How to execute multiple statements in a transaction in serial:

var statements = ["select 1", "select 2", "select 3"];

var transaction = new sql.Transaction(connection);
transaction.begin(function(err) {
    // ... error checks

    async.mapSeries(statements, function(statement, next) {
        var request = new sql.Request(transaction);
        request.query(statement, next);
    }, function(err, results) {
        // ... error checks

        transaction.commit(function(err, recordset) {
            // ... error checks

            console.log("Transaction commited.");
        });
    });
});

Answer 2

you can use Tedious Connection pools https://github.com/pekim/tedious-connection-pool

Answer 3

You should use tedious connection pools to create a pool of multiple connections. For node js, a npm module is available at : https://www.npmjs.com/package/tedious-connection-pool

For every new value inside for loop you can acquire a new connection and use connection.reset on doneInProc event. The case which you have been doing is performing 1st iteration of for loop correctly(LoggedIn State) and as you have proceeded without closing or releasing the connection you are using same connection object (SentClientRequest state). Hence the same object is at final state when the code reaches second iteration of for loop. Hope it resolves your issue

Answer 4

As @zevsuld and @mannutech said, tedious-connection-pool will enable multiple connections, and prevent erring out when simultaneous requests come into your server.

Below is a generic example that allows you to write multiple queries within one connection pool, and expose them for use in your api. I'm just adding this in case others come along who are trying to accomplish this type of implementation.

const ConnectionPool = require('tedious-connection-pool');
const path = require('path');
require('dotenv').config({
  path: path.join(__dirname, '../../.env')
})

let Request = require('tedious').Request;

let poolConfig = {
  min: 10,
  max: 50,
  log: true
}

let connectionConfig  = {
  userName: process.env.user,
  password: process.env.password,
  server: process.env.server
};

//create the pool
let pool = new ConnectionPool(poolConfig, connectionConfig);

pool.on('error', function(err) {
  console.error(err);
});

// At this point in the code, we have established a connection pool.  If you run node, you'll see it log out all then connections to your database. 
// Let's add some methods which your server might use in fulfilling requests to various endpoints.  

let query1 = (cb, res, query) => {
  // acquire a connection:
  pool.acquire(function(err, connection) {
    if (err) {
      console.error(err);
      return;
    } else {
      // form your query
      let sql_query = `SELECT column1, colum2 from TABLE WHERE column1 LIKE '${query.param}%%' ORDER BY column1 ASC`
      // use the connection as usual:
      request = new Request(sql_query, (err, rowCount) => {
        if (err) {
          console.log(err);
          return;
        } else {
          // console.log('rowCount:', rowCount);
        }
        //release the connection back to the pool when finished
        connection.release();
      });
      let records = [];
      request.on("row", function(columns) {
        let rowArray = [];
        columns.forEach(function(column) {
          rowArray.push(column.value);
        });
        records.push(rowArray);
      });
      request.on("doneInProc", function() {
        cb(records, res);
      });
      // lastly exectue the request on the open connection.
      connection.execSql(request);
    }
  });
};

let query2 = (cb, res, query) => {
  // acquire a connection:
  pool.acquire(function(err, connection) {
    if (err) {
      console.error(err);
      return;
    } else {
      // form your query
      let sql_query = `SELECT column3, colum4 from TABLE2 WHERE column3 LIKE '${query.param}%%' ORDER BY column3 ASC`;
      // use the connection as usual:
      request = new Request(sql_query, (err, rowCount) => {
        if (err) {
          console.log(err);
          return;
        } else {
          // console.log('rowCount:', rowCount);
        }
        //release the connection back to the pool when finished
        connection.release();
      });
      let records = [];
      request.on("row", function(columns) {
        let rowArray = [];
        columns.forEach(function(column) {
          rowArray.push(column.value);
        });
        records.push(rowArray);
      });
      request.on("doneInProc", function() {
        cb(records, res);
      });
      // lastly exectue the request on the open connection.
      connection.execSql(request);
    }
  });
};

// Let's expose these two functions to the rest of your API:
module.exports = {
  query1,
  query2
}