mssql stream out of memory

[dsbert]

Environment

Knex version: "0.20.2"
Database + version: SQL Sever 2016
OS: Windows 10

@smorey2

Bug

1. Explain what kind of behaviour you are getting and how you think it should do

Running into out of memory exceptions when using the stream interface for MSSQL. The stream should handle back-pressure correctly and pause the readable. However, as far as I can tell, there are two problems that prevents this from working.

First Problem:

mssql streams are not not 'nodejs' streams. They are actually an interface which pretends to be a stream. Knex appears to wrap the mssql stream with the assumption that it supports flowing mode. However, the mssql streams do not support flowing mode and do not implement the same behavior one would expect from a nodejs stream. Back-pressure must be handled manually. See tediousjs/node-mssql#776 for more info.

Second Problem:

Manually pausing the stream provided by knex does not seem to work. It does pause processing of rows, but the actual mssql stream continues to buffer new records. I believe this is because knex assumes mssql handles flowing mode correctly.

The following two pieces of code show case this.

Using mssql directly with manual back-pressure handling:

const sql = require("mssql");

        sql
          .connect("mssql://user:password@host/db")
          .then(() => {
			// A wrapper around knex to build a query
			// returns "select * from schema.table"
            const str = query.build(db.conn).toString();

            const request = new sql.Request();
            request.stream = true;
            request.query(str);

            request.on("row", (row: any) => {

			  // pauses the stream and prints the first row
			  // application never runs out of memory no matter how long you wait

              request.pause();
              console.log(row);
            });

            request.on("error", reject);
            request.on("done", () => {
              console.log("done");
              resolve();
            });
          })
          .catch(reject);

Using the stream provided by knex, the following results in an out of memory error.

		// A wrapper around knex to build a query
	    // returns "select * from schema.table"
		const dbStream = query.build(db.conn).stream();

        dbStream.on("data", row => {

		  // logs the first row and pauses
          // However, after a short time, the application runs out of memory because
          // the actual query has not been paused

          console.log(row);
          dbStream.pause();
          console.log(dbStream.isPaused()); // true
        });
        dbStream.on("error", reject);
        dbStream.on("done", () => {
          console.log("done");
          resolve();
        });

2. Error message

FATAL ERROR: Ineffective mark-compacts near heap limit Allocation failed - JavaScript heap out of memory

3. Reduced test code, for example in https://npm.runkit.com/knex or if it needs real
   database connection to MySQL or PostgreSQL, then single file example which initializes
   needed data and demonstrates the problem.

See above for code snippets. I can try to provide a working example if required.