Node SQL Server mssql streaming

Question

I am new to node and am working with mssql in order to connection to SQL Server. would anyone be able to assist in giving me a fuller example of mssql streaming. I find the git example vague and don't know where to start. Any assistance would be much appreciated.

var sql = require('mssql'); 

var config = {
    user: '...',
    password: '...',
    server: 'localhost', // You can use 'localhost\\instance' to connect to named instance
    database: '...',
    stream: true, 

    options: {// Use this if you're on Windows Azure
    }
}

sql.connect(config, function(err) {


    var request = new sql.Request();
    request.stream = true; // You can set streaming differently for each request
    request.query('select * from verylargetable'); // or request.execute(procedure);

    request.on('recordset', function(columns) {
        // Emitted once for each recordset in a query
    });

    request.on('row', function(row) {
        // Emitted for each row in a recordset
    });

    request.on('error', function(err) {
        // May be emitted multiple times
    });

    request.on('done', function(returnValue) {
        // Always emitted as the last one
    });
});

Answer 1

I'm going to Holy Necro this post because I ran into the same problem today and would like to leave something that might help future me.

According to ExpressJs documentation, the proper way to stream any large set of data is to write it to the response, flush the response occasionally and then when done, end the response.

mssql on NPM states that there are a few events you can subscribe to, like the ones listed in your excerpt from their documentation. This is great, but how do you integrate the two?

Well, I came up with the following solution (might not be the best, but hey, it works)

The idea is to stream the data from SQL record for record, but only flush the data to the caller in batches of 50. Once done, end the response.

I also needed it back in Array format so I had to construct the beginning, separators and end myself for this.

exports.listAllRecordsInReallyBigDataTable = (req, res) => {
  const config = {
    ...
  }

  sql.connect(config, () => {
    res.setHeader('Cache-Control', 'no-cache');

    const request = new sql.Request();
    request.stream = true;
    request.query('select * from myBigTableOrView');

    let rowCount = 0;
    const BATCH_SIZE = 50;

    request.on('recordset', () => {
      res.setHeader('Content-Type', 'application/json');
      res.write('[');
    }

    request.on('row', row => {
      if (rowCount > 0)
        res.write(',');

      if (rows % BATCH_SIZE === 0)
        res.flush();

      res.write(JSON.stringify(row));
      rowCount++;
    }

    request.on('done', ()=> {
      res.write(']');
      sql.close();
      res.end();
    };
  };
};