loosing connection to sql server from express js api

Im building this api that gets data from a database and posts data to another database both located on the same server but it seems that im having problems with the connections when i first run the app everything works fine but after making some api calls it just crashes and doesnt work again my guess is connections are not being closed

this is my config file theres another file just like that but with a different database

const config = {
    user :'user',
    password :'pass',
    server:'sqlserver',
    database:'Stock',
    options:{
        trustedconnection: true,
        enableArithAbort : true, 
    },
    port : 1433
}

these codes prepare the connection pools for the 2 databases

async function connectStock() {
  const pool = new sql.ConnectionPool(config);
  try {
      await pool.connect();
      console.log('Connected to database : ',config);
      return pool;
  }
  catch(err) {
      console.log('Database connection failed!', err);
      return err;
  }
}

async function connectExpiry() {
  const pool = new sql.ConnectionPool(configInsert);
  try {
      await pool.connect();
      console.log('Connected to database : ',configInsert);
      return pool;
  }
  catch(err) {
      console.log('Database connection failed!', err);
      return err;
  }
}

and here is how i get the data

const getInvoiceDetails = async (request, response, Y_FAC, N_FAC) => {
  try {
    //const pool = await sql.connect(config);
    let DB = await connectStock()
    let q =
      "select m.CODE_ART,a.a_code,a.DESC_ART,m.NB_CAISS,sum((m.QCAISSE * m.NB_CAISS) + m.QPIECE + m.MORE_FREE + m.QTY_XTRA) as 'TotalPc' ";
    q +=
      " from cli_fac f inner join cli_mvt m on f.Y_FAC = m.y_fac and f.n_fac = m.n_fac";
    q +=
      " inner join article a on a.code_art = m.CODE_ART where  m.TRANS_TYPE = 'FA'";
    q +=
      " and f.Y_FAC = '" +
      Y_FAC +
      "' and f.N_FAC = '" +
      N_FAC +
      "' group by m.CODE_ART,m.NB_CAISS,a.a_code,a.DESC_ART";  
    console.log("query get in details ", q);
    //const invoicesDet = await pool.query(q);
    const invoicesDet = await DB.query(q);
    return invoicesDet.recordsets;
  } catch (err) {
    console.log("error get inv det " + err.message);
  }
  finally {
    DB.close();
}
};

and thats how im making my api call

router.route('/InvoicesFAC/:Y_FAC/:N_FAC').get((req,res)=>{
   const Y_FAC = req.params.Y_FAC;
   const N_FAC = req.params.N_FAC;
    dboperations.getInvoicesFACS(req,res,Y_FAC,N_FAC).then(result => {
       res.json(result);
    })
})

Answer

There are three issues in your codes.

First, don’t need to catch in DB connection and instead catch in the getInvoiceDetails because you may end up returning a err instead DB instance in your connection function.

async function connectStock() {
    const pool = new sql.ConnectionPool(config);
    await pool.connect();
    console.log("Connected to database : ", config);
    return pool;
}

Second, don’t pass variable directly in SQL because of SQL injection. You should use input() like below to guard the type of variables. You may check against the datatype of specific field for your case.

const getInvoiceDetails = async (request, response, Y_FAC, N_FAC) => {
    try {
        let DB = await connectStock();
        try {
            let q =
                "select m.CODE_ART,a.a_code,a.DESC_ART,m.NB_CAISS,sum((m.QCAISSE * m.NB_CAISS) + m.QPIECE + m.MORE_FREE + m.QTY_XTRA) as 'TotalPc' " +
                " from cli_fac f inner join cli_mvt m on f.Y_FAC = m.y_fac and f.n_fac = m.n_fac" +
                " inner join article a on a.code_art = m.CODE_ART where  m.TRANS_TYPE = 'FA'" +
                " and f.Y_FAC = @Y_FAC" +
                " and f.N_FAC = @N_FAC" +
                "' group by m.CODE_ART,m.NB_CAISS,a.a_code,a.DESC_ART";

            console.log("query get in details ", q);

            const invoicesDet = await DB.request()
                .input("Y_FAC", sql.NVarChar, Y_FAC)
                .input("N_FAC", sql.NVarChar, N_FAC)
                .query(q);

            return invoicesDet.recordsets;
        } catch (err1) {
            console.log("Unable to get Invoice: " + err1.message);
        } finally {
            DB.close();
        }
    } catch (err2) {
        console.log("Unable to Connect to database: ", err2.message);
    }
};

Third, the DB instance in the finally block actually have no reference to the DB instance of the try block because they are in different block. So, you can do like the code I suggest in the Second issue above.