New feature request: table.rows.clear()

[ttemple06]

Would like to have a new method to clear out the table.rows array so that rows can be bulk inserted in batches of n rows at a time. This is particularly helpful when trying to bulk insert millions of rows from one MSSQL db to another, and wanting to do so in an offset of n records at a time. Trying to bulk insert millions of rows, even when pausing the request, row events are arriving faster than they can be processed, as described inside this SO article:
https://stackoverflow.com/questions/48645466/how-to-stream-2-million-rows-from-sql-server-without-crashing-node

Expected behaviour:

Having a table.rows.clear() method which simply clears the table.rows array would resolve this issue.

Actual behaviour:

Without a table.rows.clear() method, it is not possible to table.rows.add() and bulk insert in batches. What ends up happening is that the table.rows get bulk inserted the first time (e.g. let's say 10,000 is the offset), and the next time, those 10,000 plus another 10,000 get inserted, and so on, making the end result compounded.

Configuration:

Having a table.rows.clear() method which simply clears the table.rows array would resolve this issue. This method can be defined inside tables.js

Object.defineProperty(this.rows, 'clear', { value () { return this.splice(0, this.length) } })

Software versions

• NodeJS: v12.16.1
• node-mssql: ^6.2.0
• SQL Server: MSSQL Server 2012 (SP4-GDR)

[ttemple06]

Here is a code sample showing the new table.rows.clear() method being called from inside processRows:

async function doSomething () {

    const sql_table = await methodToCreateDestinationTableAndAddColumns()

    const request = new sql.Request();
    request.stream = true;
    request.query('select * from dbo.SourceTable');
    let rowsToProcess = [];
    const num_of_loops = 100
    let loop_count = 1;

    request.on('row', row => {
        // Emitted for each row in a recordset
        rowsToProcess.push(row);
        if (rowsToProcess.length >= 10000) {
            request.pause();
            await processRows();
          }
     });

    request.on('error', err => {
        // May be emitted multiple times
        console.log(err);
    });

    request.on('done', result => {
        // Always emitted as the last one
        await processRows();
        // close connections...

    });

    async function processRows () {
        console.log(`processing loop number ${loop_count} of ${num_of_loops}`)
        // here is the new method:
        sql_table.rows.clear()
        await methodToAddRowsToDestinationTable()
        await methodToDoBulkInsert()
        rowsToProcess = [];
        loop_count++
        request.resume();
      }
}