Node-mysql2: Cursor for prepared statements?

Created on 18 Sep 2019 · 5Comments · Source: sidorares/node-mysql2

I was wondering if this library supported cursors with prepared statements.

Something like,

await stmt.execute();
const row0 = await stmt.next();
const row1 = await stmt.next();
const row2 = await stmt.next();
//etc.
await stmt.close();

I couldn't find anything like that in the tests.

My rationale for asking is that the executed query may fetch a lot of rows, and we might only want to load one (or a small handful) of those rows at a given point in time in memory, so we don't get an OOM exception.

Something like,

//The statement may have a result set containing 100M rows
await stmt.execute();
let row = await stmt.next();
while (row != undefined) {
  //Do complicated processing or something
  row = await stmt.next();
}
await stmt.close();

Without a cursor, one can easily use the LIMIT and ORDER BY clauses in a transaction to emulate such behaviour but I was wondering if there was a way to do it already in the library.

enhancement question

Source

AnyhowStep

All 5 comments

I don't think this functionality exist at protocol level ( all rows are sent from server as a response and there is no way to cancel that mid way )

One thing that I do think might be useful is if you on receiving side decided that you don't need more data we could save some CPU by just throwing away rest of incoming packets ( no parsing / saving to results array ) See https://github.com/sidorares/node-mysql2/pull/822#issuecomment-409415308 ( and comment next to break; )

sidorares on 18 Sep 2019

👍1

Also this is not specific to prepared statements, api should be same for both query() and execute()

sidorares on 18 Sep 2019

And right now you have an option to read data row by row ( but no way to short cut and disable parsing ). In the callback api - just don't pass callback parameter and listen for 'result' event.

https://github.com/sidorares/node-mysql2/blob/9404163b0dc4bdc24f6dddd18144532f41115842/lib/commands/query.js#L239

sidorares on 18 Sep 2019

Ah. I see. I always assumed it existed on the protocol level because I remember using cursors with PHP a long time ago. I forgot that it's just an abstraction!

I decided to go back to the PHP documentation and found this,

https://www.php.net/manual/en/pdostatement.fetch.php#114458