How to use the Pool function from mysql2

mysql2.Pool creates a connection pool that manages a set of database connections and ensures their reuse.

How does mysql2.Pool work?

mysql2.Pool is a class that creates a pool of reusable database connections that can be used to execute multiple queries to a MySQL database efficiently and concurrently. It automatically manages the connections and ensures that they are properly released after they are no longer needed to avoid connection leaks.

Ai Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
const mysql = require("mysql2/promise");

const pool = mysql.createPool({
  host: "localhost",
  user: "root",
  password: "password",
  database: "mydatabase",
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

const getConnection = async () => {
  try {
    return await pool.getConnection();
  } catch (err) {
    console.error("Error getting connection from pool", err);
  }
};

const query = async (sql, values) => {
  let conn;
  try {
    conn = await getConnection();
    const [rows] = await conn.execute(sql, values);
    return rows;
  } catch (err) {
    console.error("Error executing query", err);
  } finally {
    if (conn) {
      conn.release();
    }
  }
};

query("SELECT * FROM users").then(console.log).catch(console.error);

In this example, mysql2.Pool is used to create a connection pool with a maximum limit of 10 connections. The getConnection() function is used to retrieve a connection from the pool, while the query() function is used to execute SQL queries on the database using the acquired connection. Finally, the release() method is called to release the connection back to the pool when the query is finished executing.