how to download larger binary object from postgres using nodejs express?

Question

I have node js rest api script to query from Postgres tables. It's working fine, but When I try to fetch a large objects using "lo_get" of Postgres, I get

JavaScript heap out of memory

Below is the toy example.

index.js

const express = require('express')
const bodyParser = require('body-parser')
const app = express()
const port = 8000
var controller= require('./src/controller');

app.use(bodyParser.json())
app.use(
  bodyParser.urlencoded({
    extended: true,
  })
)

app.use('/cont', controller);

app.listen(port, () => {
  console.log(`App running on port ${port}.`)
})

controller.js

var express = require('express');
var router = express.Router();

router.get('/fileID/:lo_oid/', function( req, res) {
    const lo_oid =  parseInt(req.params.lo_oid)
    model.exportLoSaint(lo_oid, function (err, object) {
        if (err) return res.send(err);
   #  Fixed the typo
   #     response.status(200).file(file);
        res.status(200).objects;
    });
});

module.exports = router;

Model.js

const Pool = require('pg').Pool
var exportLoSaintQuery= "SELECT lo_get($1)"

const exportLoSaint = (lo_oid, callback) => {
    pool.query( exportLoSaintQuery ,[lo_oid], (error, results) => {
                if (error) {
                    callback(error);
                }
                else {
                    callback(results.rows)
                }
            })
        }

on sending request .. http://ipaddress:8000/cont/fileID/<fileID>/ getting below error.

<--- JS stacktrace --->

==== JS stack trace =========================================

    0: ExitFrame [pc: 0x1b2cbd65be1d]
Security context: 0x2bf3bbc08bd9 <JSObject>
    1: toJSON [0x259ded584f29] [buffer.js:~979] [pc=0x1b2cbd665145](this=0x306d2be17731 <Uint8Array map = 0x9dd44699a69>)
    2: arguments adaptor frame: 1->0
    3: InternalFrame [pc: 0x1b2cbd60ee75]
    4: EntryFrame [pc: 0x1b2cbd6092c1]
    5: builtin exit frame: stringify(this=0x0a6855c58f61 <Object map = 0x428c84042a9>,0x2160e26026f1 <undefined>,0x2160e...

FATAL ERROR: CALL_AND_RETRY_LAST Allocation failed - JavaScript heap out of memory
 1: 0x56228da912e4 node::Abort() [node]
 2: 0x56228da91332  [node]
 3: 0x56228dc86d4a v8::Utils::ReportOOMFailure(v8::internal::Isolate*, char const*, bool) [node]
 4: 0x56228dc86fc5 v8::internal::V8::FatalProcessOutOfMemory(v8::internal::Isolate*, char const*, bool) [node]
 5: 0x56228e0387d6  [node]
 6: 0x56228e04e018 v8::internal::Heap::AllocateRawWithRetryOrFail(int, v8::internal::AllocationSpace, v8::internal::AllocationAlignment) [node]
 7: 0x56228e01a63b v8::internal::Factory::AllocateRawArray(int, v8::internal::PretenureFlag) [node]
 8: 0x56228e01aad8 v8::internal::Factory::NewFixedArrayWithFiller(v8::internal::Heap::RootListIndex, int, v8::internal::Object*, v8::internal::PretenureFlag) [node]
 9: 0x56228dfe48fe  [node]
10: 0x56228dfe4ad3  [node]
11: 0x56228e143566 v8::internal::JSObject::AddDataElement(v8::internal::Handle<v8::internal::JSObject>, unsigned int, v8::internal::Handle<v8::internal::Object>, v8::internal::PropertyAttributes, v8::internal::ShouldThrow) [node]
12: 0x56228e17897e v8::internal::Object::SetProperty(v8::internal::LookupIterator*, v8::internal::Handle<v8::internal::Object>, v8::internal::LanguageMode, v8::internal::Object::StoreFromKeyed) [node]
13: 0x56228e2c1299 v8::internal::Runtime::SetObjectProperty(v8::internal::Isolate*, v8::internal::Handle<v8::internal::Object>, v8::internal::Handle<v8::internal::Object>, v8::internal::Handle<v8::internal::Object>, v8::internal::LanguageMode) [node]
14: 0x56228e0a9b45 v8::internal::Runtime_KeyedStoreIC_Slow(int, v8::internal::Object**, v8::internal::Isolate*) [node]
15: 0x1b2cbd65be1d 

The size of binary object in postgres is only 128 mb. So, I increased the size of node js to 1gb, it did not help. Other solution I found was not using express to download binary from postgres.

******************* update 1 *********** ************

Model.js

const exportLoSaintForMaster = (oid, fileName, callback) => {
    var fileStream = require('fs').createWriteStream(fileName);
    var man = new LargeObjectManager({ pg: pool });
    pool.query('BEGIN', function (err, result) {
        if (err) {
            callback(err);
            pool.emit('error', err);
        }
        console.log("\nthe oid : %d\n", oid)
        var bufferSize = 16384;
        man.openAndReadableStream(oid, bufferSize, function (err, size, stream) {
            if (err) {
                callback(err);
                return console.error('Unable to read the given large object', err);
            }

            console.log('Streaming a large object with a total size of', size);
            stream.on('end', function () {
                pool.query('COMMIT', callback);
            });

            // Store it as an image

            stream.pipe(fileStream);
        });
    });
    callback(fileStream)
};

The scripts waits for a long time and then error outs with the below message ..

        response.status(200).objects;
        ^

ReferenceError: response is not defined

******************* Update 2 *********************

After fixing the typo above error is no more. yet I see some problems.. as the client-side is not able to download it. it shows below error on client side ..

i tried to do get . I get below message

{"_writableState":{"objectMode":false,"highWaterMark":16384,"finalCalled":false,"needDrain":false,"ending":false,"ended":false,"finished":false,"destroyed":false,"decodeStrings":true,"defaultEncoding":"utf8","length":0,"writing":false,"corked":0,"sync":true,"bufferProcessing":false,"writecb":null,"writelen":0,"bufferedRequest":null,"lastBufferedRequest":null,"pendingcb":0,"prefinished":false,"errorEmitted":false,"emitClose":false,"autoDestroy":false,"bufferedRequestCount":0,"corkedRequestsFree":{"next":null,"entry":null}},"writable":true,"_events":{},"_eventsCount":0,"path":"myfile1","fd":null,"flags":"w","mode":438,"autoClose":true,"bytesWritten":0,"closed":false}

I am using a postman to download it. I think the issue may be because of the way controller.js is invoking the script in model.js .

Please suggest me

******************* Update 3 ****************

controller.js

router.get('/fileID/:lo_oid/fileName/:fileName', function (req, res) {
    const oid = parseInt(req.params.lo_oid)
    const fileName = req.params.fileName
    model.exportLoSaintForMaster(oid,fileName, (err, stream) => stream.pipe(res));
});

model.js

const exportLoSaintForMaster = (oid, fileName, callback) => {
    var fileStream = require('fs').createWriteStream(fileName);
    var man = new LargeObjectManager({ pg: pool });
    pool.query('BEGIN', function (err, result) {
        if (err) {
            callback(err);
            pool.emit('error', err);
        }
        console.log("\nthe oid : %d\n", oid)
        var bufferSize = 16384;
        man.openAndReadableStream(oid, bufferSize, function (err, size, stream) {
            if (err) {
                callback(err);
                return console.error('Unable to read the given large object', err);
            }

            console.log('Streaming a large object with a total size of', size);
            stream.on('end', function () {
                pool.query('COMMIT', callback);
            });

            // Store it as an image

            stream.pipe(fileStream);
        });
    });
    callback(null, fileStream)
}; 

module.exports = {

    exportLoSaintForMaster
}

getting below error

Error [ERR_STREAM_CANNOT_PIPE]: Cannot pipe, not readable
    at WriteStream.Writable.pipe (_stream_writable.js:243:24)
    ...
    ...
    

Answer 1

As you've mentioned you could increase NodeJS heap size witht the flag -max-old-space-size, for example set it to 4GB :

 node --max-old-space-size=4096 index.js

But it's not a proper solution. The right thing to do would be to handle it with streams as Aviv Lo mentioned it in the comment. It will allow you to reduce the memory footprint of your script by handling data chunk by chunk.

For handling PostgreSQL Large Objects, you can use node-pg-large-objects (npm i -s pg-large-object) :

const { LargeObjectManager } = require('pg-large-object');
const { createWriteStream } = require('fs');

router.get('/fileID/:lo_oid/', (req, res, next) => {

    // assuming db is your Database connection
    // When working with Large Objects, always use a transaction                                                                                                                                              
    db.tx(tx => {
        const man = new LargeObjectManager({pgPromise: tx});
        const lo_oid = parseInt(req.params.lo_oid);

        const bufferSize = 16384; // the size of each chunk, customize it                                                                                                                                                                     

        return man.openAndReadableStreamAsync(oid, bufferSize)
            .then(([size, stream]) => {
                stream.pipe(res); // It will take care of binding to data and end events.                                                                                                                                                     
            });
    }).catch(error => {
        next(error); // pass to express error handler                                                                                                                                                                                         
    });

}

This way you will handle your large object as a stream and pipe it to your client for download.

With this solution, the maximum heap size occupied by this part of your script would be the bufferSize and not the whole file size as previously.

---

Update 3 edit

In your controller :

router.get('/fileID/:lo_oid/fileName/:fileName', function (req, res, next) {
    const oid = parseInt(req.params.lo_oid);
    const fileName = req.params.fileName;
    res.header('Content-Disposition', `attachment; filename="${fileName}"`);

    model.exportLoSaintForMaster(oid, (err, stream) => { 
        if (err) return next(err);
        stream.pipe(res);
    });
});

In your model :

const exportLoSaintForMaster = (oid, callback) => {
    const man = new LargeObjectManager({ pg: pool });
    pool.query('BEGIN', function (err, result) {
        if (err) {
            callback(err);
            pool.emit('error', err);
        }
        console.log("\nthe oid : %d\n", oid)
        const bufferSize = 16384;
        man.openAndReadableStream(oid, bufferSize, function (err, size, stream) {
            if (err) {
                return callback(err);
            }

            console.log(`Streaming a large object with a total size of ${size}`);
            stream.on('end', _ => pool.query('COMMIT'));

            callback(null, stream);
        });
    });
}; 

Warning : I don't know how your pool object works, so this code may not work exactly as expected.

Answer 2

to resolve response is not defined you have to change response to res in controller.js

var express = require('express');
var router = express.Router();

router.get('/fileID/:lo_oid/', function(req, res) {
    const lo_oid =  parseInt(req.params.lo_oid)
    model.exportLoSaint(lo_oid, function (err, file) {
        if (err) return res.send(err);
        res.status(200).file(file);
    });
});

module.exports = router;