I am using Node.js Express with postgres db for some data displaying and I've built an api that gives me data in CSV format. In order to achieve optimal solution, I'm using streams. So I just used pg.Pool, pg-query-stream and basically use the code shown as example:
pg.connect((err, client, done) => {
if (err) throw err;
const query = new QueryStream('SELECT * FROM table')
const stream = client.query(query)
//release the client when the stream is finished
stream.on('end', done)
stream.pipe(JSONStream.stringify()).pipe(res)
})
Only difference between this and an example is that I stream to the response instead of process.stdout.
This solution worked fine, but I have noticed, that sometimes the client is not returned to the pool and hang in the database forever. I did some testing and found out, that when I create multiple queries to the API (refreshing page F5,... ), response stream is (somehow) destroyed before client.query stream finishes writing and therefore stream.on('end', done) never happens. In the moment my solution is, that I release the client not when the writing stream is done, but when response stream closes, as below.
pg.connect((err, client, done) => {
if (err) throw err;
//***NEW CODE***
res.on('close', function() {
stream.destroy();
done();
});
const query = new QueryStream('SELECT * FROM table')
const stream = client.query(query)
//release the client when the stream is finished
//stream.on('end', done)
stream.pipe(JSONStream.stringify()).pipe(res)
})
All I wanted to know, if this is standard solution for standard problem, or is this not a good and potentially dangerous practice.
Thanks for your comments.
step
Versions:
"node": "12.16.1" "pg": "^8.0.0", "pg-pool": "^3.0.0", "pg-query-stream": "^3.0.4", "express": "~4.16.0"