Using Streams in MySQL with Node

Question

Following the example on Piping results with Streams2, I'm trying to stream results from MySQL to stdout in node.js.

Code looks like this:

connection.query('SELECT * FROM table')
      .stream()
      .pipe(process.stdout);

I get this error: TypeError: invalid data

Phillada

Answer 1

Explanation

From this github issue for the project:

.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

Fix

You can fix this using the csv-stringify module.

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

var stringifier = stringify();


connection.query('SELECT * FROM table')
    .stream()
    .pipe(stringifier).pipe(process.stdout);

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

Phillada

The distinction between string (buffer, really) and object mode is a very important one, but often easily overlooked. Good catch here. – Diocese

Answer 2

There is another solution now with the introduction of pipelinein Node v10 (view documentation).

The pipeline method does several things:

1. Allows you to pipe through as many streams as you like.
2. Provides a callback once completed.
3. Importantly, it provides automatic clean up. Which is a benefit over the standard pipe method.

const fs = require('fs')
const mysql = require('mysql')
const {pipeline} = require('stream')
const stringify = require('csv-stringify')

const stringifier = stringify()
const output = fs.createWriteStream('query.csv')

const connection = mysql.createConnection(...)

const input = connection.query('SELECT * FROM table').stream()

pipeline(input, stringifier, process.stdout, err => {
  if (err) {
    console.log(err)
  } else {
    console.log('Output complete')
  }
}

Protoactinium