Reload problem with Express JS and database

My index.js looks something like this:

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

const pg = require('pg-promise')(/* options */);
const db = pg('postgres://postgres:postgres@localhost:5432/test');

var memberCount = 0;
db.one('SELECT COUNT(*) from member')
  .then((data) => {
    memberCount = data.count;
  })
  .catch((error) => {
    console.log('ERROR:', error);
  });

/* GET home page. */
router.get('/', function(req, res, next) {
  res.render('index', { title: 'MyTitle', users: memberCount });
});

module.exports = router;

Works great when I start up the application. However, if I then successfully add a row to the member table and reload the page, the count isn't changed. This doesn't appear to be a caching problem, since deleting my cache didn't solve it. When I restart the server, I get the new count, but I cannot be expected to restart the server on every DB change.

You are only fetching total records once globally, so it will only be executed once on start-up. You should call your query and return an updated result on each API call.

Refer to the below code for more clarity. Also, it might not be optimal code in terms of code quality, but it will help you get an idea of your issue and what kind of approach you will need to solve it.

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

const pg = require("pg-promise")(/* options */);
const db = pg("postgres://postgres:postgres@localhost:5432/test");

const getTotalUsers = async () => {
  try {
    const { count } = await db.one("SELECT COUNT(*) from member");

    return count;
  } catch (error) {
    console.log("ERROR:", error);
  }
};

/* GET home page. */
router.get("/", async function (req, res, next) {
  const users = await getTotalUsers();

  res.render("index", { title: "MyTitle", users: users || 0 });
});

module.exports = router;