Cursor for prepared statements?

[AnyhowStep]

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.

[sidorares]

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 #822 (comment) ( and comment next to break; )

[sidorares]

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

[sidorares]

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.

node-mysql2/lib/commands/query.js

Line 239 in 9404163

this.emit('result', row);

[AnyhowStep]

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

Because MySQL does not currently support the use of cursors...

Ah, well. Thank you for pointing me in the right direction!

[sidorares]

I'll close this issue for now, but if you have opinion on potential new async iterators api feel free to post here or in the linked issue