Mysql: .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.