You could create a wrapper class that keeps a handle on the client connection.

This class acts as an interface and encapsulates the mysql2 logic. If you wanted to change mysql2 out for another module, you would not have to change all the usages of this class across your codebase.

const mysql = require("mysql2");
const { Connection } = require("mysql2/typings/mysql/lib/Connection");

class DatabaseConnection {
  /** @type {Connection} */ #client;

  constructor({ host, user, password, database }) {
    this.#client = mysql.createConnection({
      host,
      user,
      password,
      database,
    });
  }
  async query(sql) {
    new Promise((resolve, reject) => {
      this.#client.query(sql, (err, result, fields) => {
        if (err) {
          reject(err);
        }
        resolve(result, fields);
      });
    });
  }
  open() {
    this.#client.connect((err) => {
      if (err) throw err;
      if (err !== null) {
        console.log(err);
      }
    });
  }
  close() {
    this.#client.end((err) => {
      if (err) {
        return console.log(err.message);
      }
      // close all connections
    });
  }
}

module.exports = { DatabaseConnection };

Update

Here is an example of Evert’s suggestion of mysql2.

Dependencies

"dependencies": {
  "dotenv": "^16.3.1",
  "mysql2": "^3.6.0"
}

Sample SQL

Based on: https://www.w3resource.com/sql/sql-table.php

--
-- Database: `company`
--

CREATE TABLE `agent` (
  `AGENT_CODE` varchar(6) NOT NULL DEFAULT '',
  `AGENT_NAME` varchar(40) DEFAULT NULL,
  `WORKING_AREA` varchar(35) DEFAULT NULL,
  `COMMISSION` decimal(10,2) DEFAULT NULL,
  `PHONE_NO` varchar(15) DEFAULT NULL,
  `COUNTRY` varchar(25) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `agent` (`AGENT_CODE`, `AGENT_NAME`, `WORKING_AREA`, `COMMISSION`, `PHONE_NO`, `COUNTRY`) VALUES
('A007', 'Ramasundar', 'Bangalore', 0.15, '077-25814763', 'India'),
('A003', 'Alex', 'London', 0.13, '075-12458969', 'United Kingdom'),
('A008', 'Alford', 'New York', 0.12, '044-25874365', 'United States'),
('A011', 'Ravi Kumar  ', 'Bangalore', 0.15, '077-45625874', 'India'),
('A010', 'Santakumar', 'Chennai', 0.14, '007-22388644', 'India'),
('A012', 'Lucida', 'San Jose', 0.12, '044-52981425', 'United States'),
('A005', 'Anderson', 'Brisban', 0.13, '045-21447739', 'Australia'),
('A001', 'Subbarao', 'Bangalore', 0.14, '077-12346674', 'India'),
('A002', 'Mukesh', 'Mumbai', 0.11, '029-12358964', 'India'),
('A006', 'McDen', 'London', 0.15, '078-22255588', 'United Kingdom'),
('A004', 'Ivan', 'Torento', 0.15, '008-22544166', 'Canada'),
('A009', 'Benjamin', 'Hampshair', 0.11, '008-22536178', 'United Kingdom');

ALTER TABLE `agent` ADD PRIMARY KEY (`AGENT_CODE`);
COMMIT;

Environment

DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASSWORD=
DB_DATABASE=company
DB_MAX_CONNECTIONS=10

Usage

import dotenv from "dotenv";
import { createPool } from "mysql2/promise";

dotenv.config();

const parseInt2 = (value, defaultValue = 0, radix = 10) =>
  value && !isNaN(value) ? parseInt(value, radix) : defaultValue;

const {
  DB_DATABASE,
  DB_HOST,
  DB_MAX_CONNECTIONS,
  DB_PASSWORD,
  DB_PORT,
  DB_USER,
} = process.env;

const pool = createPool({
  host: DB_HOST,
  port: parseInt2(DB_PORT, 3306),
  user: DB_USER,
  password: DB_PASSWORD,
  database: DB_DATABASE,
  waitForConnections: true,
  connectionLimit: parseInt2(DB_MAX_CONNECTIONS, 10),
  queueLimit: 0,
});

const transaction = async (pool, callback) => {
  const connection = await pool.getConnection();
  await connection.beginTransaction();
  try {
    await callback(connection);
    await connection.commit();
  } catch (err) {
    await connection.rollback();
    throw err; // Throw the error again so others can catch it.
  } finally {
    connection.release();
  }
};

const findAgentById = async (agentId) =>
  new Promise(async (resolve, reject) => {
    await transaction(pool, async (connection) => {
      const [results] = await connection.query(
        "SELECT * from `agent` WHERE `agent_code` = ?",
        [agentId]
      );
      if (results.length < 1) {
        reject("Agent with this id was not found");
      }
      resolve(results.pop());
    });
  });

const findAllAgents = async () =>
  new Promise(async (resolve, reject) => {
    await transaction(pool, async (connection) => {
      const [results] = await connection.query("SELECT * from `agent`");
      if (results.length < 1) {
        reject("No agents exist");
      }
      resolve(results);
    });
  });

const agent = await findAgentById("A003");

console.log("Agent", agent); // Agent: { AGENT_CODE: 'A003', ... }

const agents = await findAllAgents();

console.log("Total agents:", agents.length); // Total agents: 12

pool.end();

Here is an example of creating reusable query functions:

import dotenv from "dotenv";
import { createPool } from "mysql2/promise";

dotenv.config();

const parseInt2 = (value, defaultValue = 0, radix = 10) =>
  value && !isNaN(value) ? parseInt(value, radix) : defaultValue;

const {
  DB_DATABASE,
  DB_HOST,
  DB_MAX_CONNECTIONS,
  DB_PASSWORD,
  DB_PORT,
  DB_USER,
} = process.env;

const pool = createPool({
  host: DB_HOST,
  port: parseInt2(DB_PORT, 3306),
  user: DB_USER,
  password: DB_PASSWORD,
  database: DB_DATABASE,
  waitForConnections: true,
  connectionLimit: parseInt2(DB_MAX_CONNECTIONS, 10),
  queueLimit: 0,
});

const transaction = async (pool, callback) => {
  const connection = await pool.getConnection();
  await connection.beginTransaction();
  try {
    await callback(connection);
    await connection.commit();
  } catch (err) {
    await connection.rollback();
    throw err; // Throw the error again so others can catch it.
  } finally {
    connection.release();
  }
};

const extractTableNameFromQuery = (sql) => {
  const [, tableName] = sql.match(/(?<=from )`?(w+)`?/);
  return tableName.toUpperCase();
};

const findBy = async (pool, sql, id) =>
  new Promise(async (resolve, reject) => {
    await transaction(pool, async (connection) => {
      const [results] = await connection.query(sql, [id]);
      if (results.length < 1) {
        reject(
          `${extractTableNameFromQuery(sql)} with this id=${id} was not found`
        );
      }
      resolve(results.pop());
    });
  });

const findAll = async (pool, sql) =>
  new Promise(async (resolve, reject) => {
    await transaction(pool, async (connection) => {
      const [results] = await connection.query(sql);
      if (results.length < 1) {
        reject(`No ${extractTableNameFromQuery(sql)} records exist`);
      }
      resolve(results);
    });
  });

const findAgentById = async (agentId) =>
  findBy(pool, "SELECT * from `agent` WHERE `agent_code` = ?", [agentId]);

const findAllAgents = async () => findAll(pool, "SELECT * from `agent`");

try {
  const agent = await findAgentById("A003");
  console.log("Agent:", agent);
} catch (e) {
  console.error(e);
}

try {
  const agents = await findAllAgents();
  console.log("Total agents:", agents.length);
} catch (e) {
  console.error(e);
}

pool.end();