create new tedious connection every time for a new API call?

Question

I am working on creating Express JS as my API server, using tedious to connect to my SQL Server DB.

Currently, in every request logic, I'll create a new tedious Connection object, connect to the DB, execute the query, then close the connection.

import { Connection } from 'tedious';
export class Controller {
  all(_: Request, res: Response): void {

    const connection = new Connection(getConfig()); // create a new connection everytime

    connection.on('connect', (err) => {
      if (err) {
        console.log('Connection Failed');
        throw err;
      }
      getProducts(connection, _, res); // in there at the end, will call connection.close()
    });

    connection.connect();
  }

import { Request, Response } from 'express';
import { Connection, Request as SqlReq } from 'tedious';
export default function getProducts(connection: Connection, _: Request, res: Response) {
    const query = `SELECT * FROM Production.Product FOR JSON PATH;`;
    let resultJson = ''; // prepare this result in return from SQL query

    const sqlReq = new SqlReq(query, (err, _) => {
      if (err) {
        throw err;
      }

      // when request finished
      connection.close();
      res.json(JSON.parse(resultJson));
    });

Is it a good or bad practice to create the connect, connect and close every time for a new API call? If there is a better way to handle the connection, may I have any reference or example?

score 0 · Answer 1 · answered Aug 29 '21 at 22:29

just make sure that the connection is created only once by using this function. It will create the connection only on first call and return the previously created connection on subsequent calls.

var connection = null;
const getConnection = async () => {
  if (connection) return connection;

  connection = new Connection(getConfig());
  return tmp;
};

Then you should leave the connection open by not calling close.

score 0 · Answer 2 · answered Aug 29 '21 at 22:54

Better use connection pooling in mysql. During app startup, you can create a pool of threads used for db connecting purpose. It will be very fast, if you retrieve from the pool and establish the connection. After your query execution/ manipulation, ensure to release the connection. So it will go to connection pool and available for further requests.

Ref : How do I create a MySQL connection pool while working with NodeJS and Express?

Ref : Release connection node.js + mysql connection pooling

create new tedious connection every time for a new API call?

2 Answers2