Postgres Connection from Node

const express = require('express');
const fs = require('fs');
const { faker } = require('@faker-js/faker');
const { Pool } = require('pg');
const { DefaultAzureCredential} = require('@azure/identity');

const app = express();
const port = process.env.PORT || 3000;

const pg_host = process.env.PG_HOST;
const pg_port = process.env.PG_PORT;
const pg_db = process.env.PG_DB;
const pg_user = process.env.PG_USER;

let dbPool = null;

// Function to create a new pool
async function createDbPool() {
  try {
    const credential = new DefaultAzureCredential();
    const tokenResponse = await credential.getToken("https://ossrdbms-aad.database.windows.net/.default");
    console.log(tokenResponse.token);

    const pool = new Pool({
      host: pg_host,
      port: pg_port,
      database: pg_db,
      user: pg_user,
      password: tokenResponse.token,
      ssl: {
        rejectUnauthorized: false,
        ca: fs.readFileSync("./certs/DigiCertGlobalRootCA.crt").toString()
      },
      max: 30,
      idleTimeoutMillis: 30000,
      connectionTimeoutMillis: 20000
    });

    console.log('New DB pool created');
    console.log('Pool status:');
    console.log('Total clients:', pool.totalCount);    // All clients (idle + checked out)
    console.log('Idle clients:', pool.idleCount);      // Currently idle clients
    console.log('Waiting clients:', pool.waitingCount); // Clients waiting for a connection
    return pool;
  } catch (err) {
    console.error('Failed to create DB pool:', err);
    return null;
  }
}

// Middleware to attach the current pool to each request
app.use((req, res, next) => {
  if (!dbPool) {
    return res.status(500).send('Database pool is not available');
  }
  req.db = dbPool;
  next();
});

// Renew the pool every 60 seconds
async function schedulePoolRenewal() {
  setInterval(async () => {
    console.log('Renewing DB pool...');
    const newPool = await createDbPool();
    if (newPool) {
      const oldPool = dbPool;
      dbPool = newPool;

      // Clean up old pool
      if (oldPool) {
        setTimeout(() => {
          oldPool.end().then(() => console.log('Old pool closed'));
        }, 5000); // delay to avoid interrupting active queries
      }
    }
  }, 60000);
}

app.get('/', async (req, res) => {
  try {
    const result = await req.db.query('SELECT NOW()');
    res.send(`DB time: ${result.rows[0].now}`);
  } catch (err) {
    console.error('Error querying the database:', err);
    res.status(500).send('Error fetching data from the database');
  }
});

// Initialize app
(async () => {
  dbPool = await createDbPool();
  if (!dbPool) {
    console.error('Failed to initialize DB pool. Exiting.');
    process.exit(1);
  }

  schedulePoolRenewal();

  app.listen(port, '0.0.0.0', () => {
    console.log(`App listening on port ${port}`);
  });
})();