CODEWITHSUNDEEP
node.jsexpresstedious
Eric Cheng
Eric Cheng

Reputation: 517

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

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?

Upvotes: 1

Views: 1460

Answers (2)

Raphael PICCOLO
Raphael PICCOLO

Reputation: 2175

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 connection;
};

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

Upvotes: 0

Senthil
Senthil

Reputation: 2246

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

Upvotes: 0

Related Questions