Node-mysql2: using connection.query(...).stream() with promise wrapper

[jomel]

why can't I use connection.query(...).stream() with promise wrapper?
(you get error: TypeError: connection.query(...).stream is not a function)

I'd quite like to be able to do:
let q = await connection.query(someSQL);
as well as

const s1 = connection.query(veryBiqSqlResult).stream();
s1.on('result', async function(row) {...})

in the same script, but atm I need promise api for the first and standard api for the second...

[sidorares]

maybe we need to wait for async generators to land: http://node.green/#ESNEXT-candidate--stage-3--Asynchronous-Iterators-for-await-of-loops

for await (const row of connection.query(veryBiqSqlResult).stream()) {
  console.log(row);
}

( not sure if I can mix buffering .query() => Promise version with non-buffering detecting that .stream() is added, maybe we need another name: `for await (const row of connection.queryStream(veryBiqSqlResult)) {})

You can access non-promise api from wrapper as connection.connection - see https://github.com/sidorares/node-mysql2/blob/97a88530db72c656c16f071a710701ad2830b38d/promise.js#L55

const [rows] = await connection.query(smallSqlResult);
await new Promise((accept, reject) => {
  const s1 = connection.connection.query(veryBiqSqlResult);
  s1.on('result', function(row) {...})
  s1.on('end', accept);
  s1.on('error', reject);
})

[derN3rd]

One note to the connection.connection.query(...).stream() part:
I had problems with it, until I saw the comment https://github.com/sidorares/node-mysql2/issues/770#issuecomment-381295768
After removing .stream() it worked perfectly

[sidorares]

Oh, thanks @derN3rd I'll update my example here

[sidorares]

yes, stream() is just to wrap into pipeable object, and _not_ to enable 'result' events, they are emitted anyway

[terrisgit]

If you shut down the database while processing a large rowset after the 'result' hander is called at least once, neither end() nor error() are ever called. I'll try to create a test program.

[sidorares]

@terrisgit try setting enableKeepAlive option to true - see discussion in https://github.com/sidorares/node-mysql2/pull/1081

Maybe we should change that to be true by default, but not sure if there might be some undesired side effects

[terrisgit]

This promisified example writes a result set to a CSV file. It handles all possible errors (mysql connection, SQL, stream write) without crashing or hanging. Feedback wanted.

const csvstringify = require('csv-stringify');
const fs = require('fs');

const outputStream = fs.createWriteStream('output.csv', {encoding: 'utf8'});

const finishedWriting = new Promise((resolve, reject)=>
  outputStream.on('finished', resolve).on('error', reject));

const BOM = '\ufeff'; // Microsoft Excel needs this
outputStream.write(BOM);

const connection = __Create a mysql2 Connection object here__
const generator = connection.connection.query('SELECT...');
let recordsProcessed = 0;

try {
  await new Promise((resolve, reject) => {
    // When using a connection pool, the 'error' connection event is called only when
    // enableKeepAlive is true. See:
    // https://github.com/sidorares/node-mysql2/issues/677#issuecomment-588530194
    // Without this handler, this code will hang if the database connection is broken
    // while reading the result set.
    connection.on('error', reject);

    generator
      .on('result', row => ++recordsProcessed) // Counting rows just as an example
      .stream({highWaterMark: 10})
      .on('error', reject)
      .on('end', resolve)
      .pipe(csvstringify({header: true}))
      .pipe(outputStream)
      .on('error', error => {
        // Handle stream write error
        // See also https://github.com/sidorares/node-mysql2/issues/664
        // Data is being sent from server to client; without calling destroy, the
        // connection will go back to the pool and will be unusable. The
        // callback provided to destroy() is never called.
        connection.destroy(); // This appears to cause file descriptor leaks
        reject(error);
      });
  });
}
finally {
  connection.on('error', error => console.log(error)); // Remove the handler. Is there a better way?
}

await finishedWriting;

[terrisgit]

I've been doing a lot of error testing. The above code can be forced to fail in many ways but I've been focusing on write errors which you can force via outputStream.close() after you create it. It seems to leak memory if you run the above code in a loop when closing the outputStream. It's not a typical runtime error anyway so it's not a concern.