How to use Postgres pooling on NodeJS/Express server

Question

New to Postgres and the concept of transaction pooling in general. In the documentation, Postgres recommends using pool.query method for single queries, and also warns that "You must always return the client to the pool if you successfully check it out". I took that to mean, you have to call client.release() for clients or pool.end() for pools (correct me if I'm wrong). So in my Node/Express server, I did a simple test:

const { Pool } = require('pg');
const pool = new Pool();

...

router.post('/test', async (req, res) => {
    let { username } = req.body;

    let dbRes;

    try{
        dbRes = await pool.query('SELECT * FROM users WHERE username = $1', [username]);
    } catch(err){
        let errMsg = "Error fetching user data: " + err;
        console.error(errMsg);
        return res.send({"actionSuccess": false, "error": errMsg});
    }

    //do something with dbRes, maybe do an update query;

    try{
        await pool.end();
    } catch(err){
        return "There was an error ending database pool: " + err.stack;
    }

    res.send({"dbRes": dbRes.rows[0]})
});

I run the server, use Postman to make a post call to this /test route, and everything works fine. However, if I make the same call again, this time I get the error Error: Cannot use a pool after calling end on the pool. This makes sense, I ended the pool within this request, but at the same time it doesn't make sense. I'm guessing pools/clients not tied to individual server requests as I originally thought, which means if one request to the Node server ends the pool, it ends the pool for all other requests as well (please correct me if I'm wrong! I'm just guessing here). If that's the case, then I can never call pool.end(), since I'd like to keep tje pool open/alive as long as the Node server is running, for other server requests as well. That begs to question, where DO I end the pool? Is it ok to leave it open forever? Does this clash with the whole You must always return the client to the pool if you successfully check it out rule that is stated in the documentation?

Answer 1

If you are using the await pool.query syntax you do not need to worry about releasing the connection back to the pool. It handles closing the connection for you. This is in my opinion the correct way to use pg pool. You can/should get rid of your 2nd try/catch block that contains the pool.end() code snippet.

If you go with the old school pool.connect syntax you need to call done() to release the connection back into the pool. i.e.

pool.connect(function(err, client, done) {
    var sql = "SELECT * FROM users WHERE username = $1";
    var values = [username];

    client.query(sql, values, function(err, result) {
        done(); // releases connection back to the pool        
        // Handle results
    });
});