Piping mssql results to Express response

2
votes

I'm using mssql in NodeJS to fetch a large number of rows.

I'd like to pipe the results to a HTTP response in Express, but I can't see how to achieve this.

db.js

const sql = require('mssql');

const config = {
  user: 'user',
  password: 'pass',
  server: 'host',
  database: 'db',
};

index.js

I am able to stream results to the command line:

router.get('/', function (req, res, next) {
  new sql.ConnectionPool(config).connect().then(pool => {
    const request = new sql.Request(pool);
    request.stream = true;
    request.query(`SELECT col1, col2 FROM mytable`);

    request.on('row', row => {
      console.log(row);
    });

  });
});

module.exports = router;

However, using request.pipe() results in an error:

router.get('/', function (req, res, next) {
  new sql.ConnectionPool(config).connect().then(pool => {

    const request = new sql.Request(pool);
    request.pipe(res);
    request.query(`SELECT col1, col2 FROM mytable`);

    request.on('finish', () => {
      res.end();
    });

  });
});

_http_outgoing.js:647 throw new errors.TypeError('ERR_INVALID_ARG_TYPE', 'first argument', ^

TypeError [ERR_INVALID_ARG_TYPE]: The first argument must be one of type string or Buffer at write_ (_http_outgoing.js:647:11) at ServerResponse.write (_http_outgoing.js:622:10) at Request.emit (events.js:180:13

2
sql-servernode.jsexpress
You are using two requests, one, to sql and the other is, the, presumably, http req object. You are able to stream results to the console because, the request object checks on the 'row' event whereas, you are expecting the 'finish' event on the same object rather than the 'req' object. - user2347763
Correction to my above comment: you are calling the 'pipe' method on the same object rather than the 'req' object - user2347763

2 Answers

0
votes

edit: if streaming is your concern, why dont you store it in an object

router.get('/', function (req, res, next) {
  new sql.ConnectionPool(config).connect().then(pool => {
    const request = new sql.Request(pool);
    request.stream = true;
    request.query(`SELECT col1, col2 FROM mytable`);
    var output = [];
    request.on('row', row => {
      console.log(row);
      output.push(row)
    });

     request.on('finish', () => {
       res.send(output);
     });
  });
});
-1
votes
app.get("/", (req, res) => {
    new sql.ConnectionPool(config).connect().then(pool => {
    const request = new sql.Request(pool);
    request.stream = true;
    request.query('select * from tableName');
    request.on("row", async (row) => {
        if (
            !res.write(JSON.stringify(row), (err) => {
                if (err) {
                    console.error("Failed to send data");                   
                }
            })
        ) {
            request.pause();
            await new Promise((resolve) => res.once("drain", resolve));
            request.resume();
        }
    });
    request.on("error", (err) => {
        res.status(500).send({ message: err });
        res.end();
    });
    request.on("done", (result) => {
        res.end();
        console.log("Data sending complete");
    });
});
});