.query(...).stream().pipe(process.stdout) throws TypeError: invalid data

[JHKennedy4]

I'm using the following code to test out the functionality of .stream().pipe() and I'm getting an error when I pass fairly typical stream outputs I've seem used elsewhere to .pipe(). I'm wondering if I'm totally off base or if this functionality is no longer supported.

pool.getConnection(function (err, connection) {
    connection.query('SELECT * FROM wp_users WHERE ID = ?', [req.params.id])
    .stream({highWaterMark: 5})
    .pipe(process.stdout);
});

I get "TypeError: first argument must be a string or Buffer" when I call .pipe(res).

[sidorares]

.stream() returns stream in "objectMode". You can't pipe it to stdout or network socket because "data" events have rows as payload, not Buffer chunks. You need to serialize as text/buffer manually

[ZJONSSON]

Exactly. Here is an example of a stringify transform:

var stream = require('stream'),
    util = require('util');

function StringifyStream(options) {
  if (!(this instanceof StringifyStream))
    return new StringifyStream(options);

  options = options || {};
  options.objectMode = true;

  stream.Transform.call(this,options);
}

util.inherits(StringifyStream,stream.Transform);

StringifyStream.prototype._transform = function(d,e,callback) {
  this.push(JSON.stringify(d));
  callback();
};

which can be inserted into your pipeline like this:

pool.getConnection(function (err, connection) {
    connection.query('SELECT * FROM wp_users WHERE ID = ?', [req.params.id])
    .stream({highWaterMark: 5})
    .pipe(StringifyStream())
    .pipe(process.stdout);
});

[JHKennedy4]

Thank you so much for the clarification and example.

[waylonflinn]

You can also use the csv-stringify module.

at the command line in the root of your project, run:

npm install csv-stringify

then, in your file:

var stringify = require('csv-stringify');

var stringifier = stringify();

pool.getConnection(function (err, connection) {
    connection.query('SELECT * FROM wp_users WHERE ID = ?', [req.params.id])
    .stream({highWaterMark: 5})
    .pipe(stringifier).pipe(process.stdout);
});

notice the extra .pipe(stringifier) before the .pipe(process.stdout)

[gentunian]

Excellent examples and documentation. I've tried this for piping to stdout and works ok. The only issue I'm having is that I wan't to pipe large datasets. For some reason, highWaterMark option will only pipe the specified amount of objects, as the nodejs documentations states:

From buffering

For streams operating in object mode, the highWaterMark specifies a total number of objects.

From stream options

highWaterMark Buffer level when stream.write() starts returning false. Defaults to 16384 (16kb), or 16 for objectMode streams.

Saying that and without knowing how many rows should I return (most than 1.000), how could I pipe this into another process?

EDIT: nevermind, I thought that default highWaterMark will return only 16 objects but seems to works without setting any value (default). I found documentation a little bit odd. Sadly I needed to go for a try and error approach to find out this. Anyway, I would love if anyone may explain this not-documented behaviour or what thing I missunderstood of nodejs docs.

[germansokolov13]

@sidorares I think you should write this fact in here:
https://github.com/mysqljs/mysql#piping-results-with-streams
In this paragraph it doesn't say what becomes data in the data event of streams, a row, a collection of rows, a byte or what. It becomes clear only with your comment in here.