Hey Rokas, let's figure out what's going wrong here and get your code working smoothly!

The Root Cause #

Your databaseConnection.js is exporting the Promise returned by sql.connect(config), not the actual connection pool instance.

sql.connect() is asynchronous—it doesn't hand you the pool right away, it gives you a Promise that resolves to the pool once the connection is established. So when you try to call db.request() in your route, you're actually trying to run a method on a Promise object, which doesn't have a request() method. That's exactly why you're seeing the TypeError: db.request is not a function error.

Solution 1: Export an Async Function to Get the Pool #

This approach caches the pool so you don't recreate it on every request, which is efficient for connection pooling.

Update your databaseConnection.js:

const sql = require("mssql/msnodesqlv8");
const config = {
  database: process.env.DB_NAME,
  server: process.env.DB_SERVER,
  driver: "msnodesqlv8",
  options: { trustedConnection: true }
};

// Cache the pool globally to avoid reinitializing it
async function getDbPool() {
  if (global.dbPool) {
    return global.dbPool;
  }
  const pool = await sql.connect(config);
  global.dbPool = pool;
  return pool;
}

module.exports = getDbPool;

Then adjust your data.js route file to wait for the pool:

const express = require("express");
const router = express.Router();
const getDbPool = require("../configs/databaseConnection");

router.get("/dataList", async (req, res) => {
  try {
    const db = await getDbPool(); // Wait for the pool to be ready
    const allData = await db.request().query("select * from dataList");
    console.log(allData);
    res.render("dataList", { title: "Data list" });
  } catch (err) {
    console.error("Database error:", err);
    res.status(500).send("Failed to fetch data");
  }
});

module.exports = router;

Solution 2: Export a Pre-Initialized Pool Promise #

This initializes the pool when your app starts, and exports the Promise that resolves to the pool. Your routes will wait for this Promise to resolve before using the pool.

Update databaseConnection.js:

const sql = require("mssql/msnodesqlv8");
const config = {
  database: process.env.DB_NAME,
  server: process.env.DB_SERVER,
  driver: "msnodesqlv8",
  options: { trustedConnection: true }
};

// Initialize the pool and export the Promise
const dbPoolPromise = sql.connect(config)
  .then(pool => {
    console.log("Database pool connected successfully");
    return pool;
  })
  .catch(err => {
    console.error("Failed to initialize database pool:", err);
    throw err; // Fail fast if connection can't be established
  });

module.exports = dbPoolPromise;

Then use it in data.js:

const express = require("express");
const router = express.Router();
const dbPoolPromise = require("../configs/databaseConnection");

router.get("/dataList", async (req, res) => {
  try {
    const db = await dbPoolPromise; // Wait for the pool to be ready
    const allData = await db.request().query("select * from dataList");
    console.log(allData);
    res.render("dataList", { title: "Data list" });
  } catch (err) {
    console.error("Database error:", err);
    res.status(500).send("Failed to fetch data");
  }
});

module.exports = router;

Quick Notes #

• Always handle errors: Both solutions include try/catch or .catch() to avoid unhandled promise rejections (which is what that warning was about).
• Connection pools are meant to be reused: Caching the pool (like in Solution 1) ensures you don't create a new pool for every request, which is the whole point of using a pool.

内容的提问来源于stack exchange，提问作者r.Die