Node streaming SQL Server mssql
I'm new to node and working with mssql
to connect to SQL Server. can anyone please help me give a more complete example of streaming mssql
. I find the git example vague and don't know where to start. Any help 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
});
});
source to share
I am going to Sacred Necro this post because I faced the same problem today and would like to leave something that might help me in the future.
According to the ExpressJs
documentation , the correct way to stream any large dataset is write
his answer, the flush
answer sometimes, and then when done, the end
answer.
mssql
on NPM states that there are several events that you can subscribe to, such as 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 (maybe not the best, but hey it works)
The idea is to pass the data from the SQL record for writing, but only flush the data up to the caller in batches of 50. Once complete, complete the response.
I also needed it back in format Array
, so I had to build start, delimiters and finish 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();
};
};
};
source to share