After first successful query to heroku postgres table, every query after fails

Question

This is a server-side web application (Node.js and Express) deployed to Heroku. I'm using the Heroku Postgres add-on (client refers to connection to my Heroku Postgres database).

I structured my promise using this as a reference.

The first GET request is successful and returns result of client.query('SELECT * FROM passenger WHERE username = $1;', [username]). The second GET request receives an Internal Server Error and every GET Request after receives the same error. I don't know where to begin looking for what's causing the issue? I'm not sure why it would work once and then fail the same GET request right after.

JS script

var express = require('express')
var app = express()
var bodyParser = require("body-parser")
var validator = require('validator')
var { check, validationResult } = require('express-validator')
const router = express.Router()
const data = require('./app.json')
const PORT = process.env.PORT || 5000

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

var cors = require('cors')
var corsOptions = {
    origin: '*',
    optionsSuccessStatus: 200
}

var connectionString = *censored*;
var { Client } = require('pg')
var client = new Client({
    connectionString: process.env.DATABASE_URL || connectionString,
    ssl:{
        rejectUnauthorized: false
    }
})
client.connect()

app.use('/', router)

router.get('/passenger.json', cors(corsOptions), check('username'), (req, res) => {
    var errors = validationResult(req)
    if (!errors.isEmpty() ||  Object.keys(req.query).length === 0){
        res.json([])
    }

    var username = req.query.username
    username = validator.escape(username)

    client
        .query('SELECT * FROM passenger WHERE username = $1;', [username])
        .then(result => res.json(result.rows))
        .catch(e => res.sendStatus(500))
        .then(() => client.end())
})

app.listen(PORT, () => console.log(`Listening on ${ PORT }`))

GET Request attempts:

% curl -X GET "https://agile-dusk-02160.herokuapp.com/passenger.json?username=xIHNcana"
[{"username":"xIHNcana","lat":42.385408,"lng":-71.08689919999999},{"username":"xIHNcana","lat":42.385408,"lng":-71.08689919999999},{"username":"xIHNcana","lat":42.385408,"lng":-71.08689919999999},{"username":"xIHNcana","lat":42.385408,"lng":-71.08689919999999}]%                                         
% curl -X GET "https://agile-dusk-02160.herokuapp.com/passenger.json?username=xIHNcana"
Internal Server Error%  

Heroku logs that reference the two GET requests:

2020-06-24T22:25:14.123304+00:00 heroku[router]: at=info method=GET path="/passenger.json?username=xIHNcana" host=agile-dusk-02160.herokuapp.com request_id=07eca9c7-aaa2-45fd-a32c-55fe0551e4f6 fwd="73.126.3.226" dyno=web.1 connect=0ms service=20ms status=200 bytes=502 protocol=https
2020-06-24T22:25:15.520949+00:00 heroku[router]: at=info method=GET path="/passenger.json?username=xIHNcana" host=agile-dusk-02160.herokuapp.com request_id=28c34b65-c9cf-439f-901d-51ae220e90bf fwd="73.126.3.226" dyno=web.1 connect=0ms service=3ms status=500 bytes=273 protocol=https

This is what the heroku postgres table looks like:

agile-????-?????::DATABASE=> select * from passenger;
 username |    lat    |        lng         
----------+-----------+--------------------
 xIHNcana | 42.385408 | -71.08689919999999
 USERNAME |     12.12 |              12.12
 xIHNcana | 42.385408 | -71.08689919999999
 USERNAME |     12.12 |              12.12
 xIHNcana | 42.385408 | -71.08689919999999
 USERNAME |     12.12 |              12.12
 xIHNcana | 42.385408 | -71.08689919999999
 USERNAME |     12.12 |              12.12
(8 rows)

Answer 1

I switched from Client (single client connection) to Pool (connection pooling) via the pg-pool module and it's working now. From here, node-postgres notes using a connection pool is best for a web app that handles frequent queries. I think these were the issues with my first attempt:

• PostgreSQL server can only handle a limited number of clients at a time. Depending on the available memory of your PostgreSQL server you may even crash the server if you connect an unbounded number of clients.
• PostgreSQL can only process one query at a time on a single connected client in a first-in first-out manner. If your multi-tenant web app is using only a single connected client all queries among all simultaneous requests will be pipelined and executed serially, one after the other. No Good!

var express = require('express')
var app = express()
var bodyParser = require("body-parser")
var validator = require('validator')
var { check, validationResult } = require('express-validator')
const router = express.Router()
const data = require('./app.json')
const PORT = process.env.PORT || 5000

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

var cors = require('cors')
var corsOptions = {
    origin: '*',
    optionsSuccessStatus: 200
}

var connectionString = *censored*;
const { Pool } = require('pg')
const pool = new Pool({
    connectionString: process.env.DATABASE_URL || connectionString,
    ssl:{
        rejectUnauthorized: false
    }
})
pool.on('error', (err, client) => {
  console.error('Unexpected error on idle client', err)
  process.exit(-1)
})

router.get('/passenger.json', cors(corsOptions), check('username'), (req, res) => {
    var errors = validationResult(req)
    if (!errors.isEmpty() ||  Object.keys(req.query).length === 0){
        res.json([])
        return
    }

    var username = req.query.username
    username = validator.escape(username)

    pool.connect((err, client, done) => {
      if (err) throw err
      client.query('SELECT * FROM passenger WHERE username = $1;', [username], (err, result) => {
        done()
        if (err) {
          res.sendStatus(500)
        } else {
          res.json(result.rows)
        }
      })
    })
})