Streaming data from oracle db to browser with node.js

Question

I am learning node.js and database. I am trying to stream heavy data about 7,700,000 rows and 96 columns from oracle to client. Later i use that data for virtual table. But in client it is showing only one row and then in node command error is displaying "Cannot set headers after they are sent to the client". How to stream data in client. Please help

var oracledb = require('oracledb');
const cors = require('cors');
var express = require('express');
var app = express();
app.use(cors());

oracledb.outFormat = oracledb.ARRAY;

oracledb.getConnection({
        user: 'user',
        password: 'password',
        connectString: 'some string'
    },
    (err, connection) => {
        if (err) {
            console.error(err.message);
            return;
        }
        var rowsProcessed = 0;
        var startTime = Date.now();
        var dataSize = 0;

        var stream = connection.queryStream(
            'SELECT * FROM table',
        );

        // stream.on('data', function (data) {
    //     rowsProcessed++;
    //     // console.log(JSON.stringify(data));
    //     // console.log(data);

    //     dataSize = dataSize + data.length;
    //     // oracleData.push(data);
    //     // console.log("pushing");
    //     // console.log(oracleData);
    //     // app.get('/data', (req, res) => {
    //     //     res.send(data);
    //     // })
    //     // console.log(data);
    // });

        app.get('/data', (req, res) => {
            stream.on('data', (data) => {
                rowsProcessed++;
                dataSize = dataSize + data.length;
                res.send(JSON.stringify(data));
            })
        })

        stream.on('end', function () {
            var t = ((Date.now() - startTime) / 1000);
            console.log('queryStream(): rows: ' + rowsProcessed +
                ', seconds: ' + t);
            // console.log(dataSize + ' bytes');
            connection.close(
                function (err) {
                    if (err) {
                        console.error(err.message);
                    } else {
                        console.log("connection closed")
                    }
                }
            )
        })

    }
);


app.listen(5000, () => {
    console.log('Listening at 5000')
})

I tried using above approach. But it is failing. How can I achieve the output? The browser is freezing if I output entire data at single time that's why I am trying to use streaming and in the node command prompt it is displaying out of memory if I load entire data at single time.

Thank you.

Answer 1

The first thing you'll want to do is organize your app a little better. Separation of concerns is important, you should have a connection pool, etc. Have a look at this series for some ideas: https://jsao.io/2018/03/creating-a-rest-api-with-node-js-and-oracle-database/

Once you get the organization figured out, incorporate this example of streaming a large result set out.

const oracledb = require('oracledb');

async function get(req, res, next) {
  try {
    const conn = await oracledb.getConnection();

    const stream = await conn.queryStream('select * from employees', [], {outFormat: oracledb.OBJECT});

    res.writeHead(200, {'Content-Type': 'application/json'});

    res.write('[');

    stream.on('data', (row) => {
      res.write(JSON.stringify(row));
      res.write(',');
    });

    stream.on('end', () => {
      res.end(']');
    });

    stream.on('close', async () => {
      try {
        await conn.close();
      } catch (err) {
        console.log(err);
      }
    });

    stream.on('error', async (err) => {
      next(err);

      try {
        await conn.close();
      } catch (err) {
        console.log(err);
      }
    });
  } catch (err) {
    next(err);
  }
}

module.exports.get = get;

If you find you're doing this a lot, simplify things by creating a reusable transform stream:

const oracledb = require('oracledb');
const { Transform } = require('stream');

class ToJSONArray extends Transform {
  constructor() {
    super({objectMode: true});

    this.push('[');
  }

  _transform (row, encoding, callback) {
    if (this._prevRow) {
      this.push(JSON.stringify(this._prevRow));
      this.push(',');
    }

    this._prevRow = row;

    callback(null);
  }

  _flush (done) {
    if (this._prevRow) {
      this.push(JSON.stringify(this._prevRow));
    }

    this.push(']');

    delete this._prevRow;

    done();
  }
}

async function get(req, res, next) {
  try {
    const toJSONArray = new ToJSONArray();
    const conn = await oracledb.getConnection();

    const stream = await conn.queryStream('select * from employees', [], {outFormat: oracledb.OBJECT});

    res.writeHead(200, {'Content-Type': 'application/json'});

    stream.pipe(toJSONArray).pipe(res);

    stream.on('close', async () => {
      try {
        await conn.close();
      } catch (err) {
        console.log(err);
      }
    });

    stream.on('error', async (err) => {
      next(err);

      try {
        await conn.close();
      } catch (err) {
        console.log(err);
      }
    });
  } catch (err) {
    next(err);
  }
}

module.exports.get = get;