Best practices for using and managing MySQL connection pools in Node.js

How to correctly use and manage MySQL connection pool in Node.js program?

Node.js has become a very popular server-side development language. Its asynchronous, event-driven features make it excellent at handling high concurrent requests. The database is an integral part of the development process. As a mature and reliable relational database management system, MySQL is also one of the commonly used databases in Node.js development.

In Node.js, in order to efficiently manage database connections, developers often use connection pools. Using a connection pool can reduce the overhead of establishing and closing database connections in each request, improving performance and efficiency. The following will introduce how to correctly use and manage the MySQL connection pool in Node.js programs.

1. Installing dependent libraries
   First, before starting, you need to install the two npm packages mysql and mysql2. It can be installed using the following naming:

npm install mysql mysql2 --save

2. Creating a database connection pool
   In Node.js, you can create a MySQL connection pool using the mysql or mysql2 library. The following is a sample code to create a connection pool:

const mysql = require('mysql');
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'database_name',
  connectionLimit: 10  // 连接池中的最大连接数
});

module.exports = pool;

In this example, we use the mysql library to create a connection pool named pool, specifying the host name of the database , user name, password, database name and the maximum number of connections in the connection pool.

3. Use connection pool for database operations
   Through the connection pool, we can easily obtain a connection from the connection pool, perform database operations, and release the connection back to the connection pool after the operation is completed. The following is an example of using a connection pool for database operations:

const pool = require('./db');  // 引入连接池

pool.getConnection((error, connection) => {
  if (error) throw error;

  // 执行数据库操作
  connection.query('SELECT * FROM table_name', (error, results, fields) => {
    // 处理查询结果
    if (error) throw error;
    console.log(results);

    // 释放连接
    connection.release();
  });
});

In this example, we use the pool.getConnection method to obtain a connection object from the connection pool, and then pass this The connection object performs database operations. After the operation is completed, you need to call the connection.release() method to put the connection back into the connection pool so that other requests can continue to use it.

4. Error handling
   When using the connection pool for database operations, you must pay attention to error handling. Errors can be handled by checking the error parameter in the callback function. For example, you can use a try-catch block to catch errors and handle them, or you can pass errors to global error handling middleware for unified processing.

pool.getConnection((error, connection) => {
  if (error) {
    // 处理错误
    console.error(error);
    return;
  }

  // 执行数据库操作
  connection.query('SELECT * FROM table_name', (error, results, fields) => {
    if (error) {
      // 处理错误
      console.error(error);
      return;
    }

    console.log(results);

    // 释放连接
    connection.release();
  });
});

5. Automatic release of connection pool
   In some cases, we may forget to release the connection manually. In order to avoid connection leaks, you can set the options of the connection pool and set waitForConnections to true, so that when the connection pool has no idle connections, new connection requests will Delayed until a connection is available.

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'database_name',
  connectionLimit: 10,
  waitForConnections: true  // 设置为true
});

Summary:
By correctly using and managing the MySQL connection pool, the performance and reliability of Node.js programs can be improved. It should be noted that the size of the connection pool should be adjusted according to the specific situation to avoid the problem of the connection pool being too large or too small. In addition, errors in database operations must be properly handled to avoid potential security issues and data consistency issues.

The above is the detailed content of Best practices for using and managing MySQL connection pools in Node.js. For more information, please follow other related articles on the PHP Chinese website!