Detailed explanation of common methods of MySQL connection pooling (Pool)

Detailed explanation of common methods for MySQL connection pooling (Pool)

1. Create a connection pool

First, you need to create a connection pool instance:

const mysql = require('mysql2/promise'); // Use Promise version
const pool = ({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'test',
  waitForConnections: true,
  connectionLimit: 10, // Maximum number of connections  queueLimit: 0 // Unlimited queue requests});

2. Core Methods

2.1 (sqlString, [values])

effect: The easiest way to execute SQL queries
Features：
- Automatically get and release connections
- Support parameterized query
Return value：[rows, fields]
Example：

const [rows] = await ('SELECT * FROM users WHERE age > ?', [18]);

2.2 (sqlString, [values])

effect: Execute preprocessing statements
Features：
- Comparequery()More efficient (especially repeated inquiries)
- Automatically create and cache preprocessing statements
Return value：[rows, fields]
Example：

const [rows] = await ('SELECT * FROM products WHERE price > ?', [100]);

2.3 ()

effect: explicitly get a connection
Use scenarios：
- Need to execute transactions
- Multiple related queries are required
Notice: The connection must be released manually
Example：

const connection = await ();
try {
  // Use connection to execute query} finally {
  (); // Must be released}

3. Connection method

passgetConnection()There are the following methods for obtaining the connection object:

3.1 ()

same(), but execute on a specific connection

3.2 ()

same(), but execute on a specific connection

3.3 ()

effect: Start a transaction
Example：

await ();

3.4 ()

effect: Submit transactions
Example：

await ();

3.5 ()

effect: Rollback transactions
Example：

await ();

3.6 ()

effect: Release the connection back to the connection pool
important: Must be called, otherwise it will cause the connection to leak

4. Connection pool management method

4.1 ()

effect: Elegantly close the connection pool
Example：

await (); // Close all connections

4.2 (value)

effect: Manually escape the value
Example：

const name = (userInput); // Prevent SQL injection

4.3 (identifier)

effect: Escape identifier (table name, column name)
Example：

const tableName = ('user table');

5. Complete example of transaction processing

const connection = await ();
try {
  await ();
  
  // Perform multiple operations  await ('UPDATE accounts SET balance = balance - ? WHERE id = ?', [100, 1]);
  await ('UPDATE accounts SET balance = balance + ? WHERE id = ?', [100, 2]);
  
  await ();
} catch (err) {
  await ();
  throw err;
} finally {
  ();
}

6. Event listening

The connection pool can listen for the following events:

6.1 'acquire'

Triggered when getting a connection from the pool

('acquire', (connection) => {
  ('Connection %d acquired', );
});

6.2 'release'

Triggered when the connection is released back into the pool

('release', (connection) => {
  ('Connection %d released', );
});

7. Best Practices

Always use parameterized queryPrevent SQL injection
Release the connection in timeAvoid connection leakage
Set the connection pool size reasonablyAdjust according to application load
Use try-catch in transactionsMake sure errors are handled correctly
Consider using ORMSuch as Sequelize and TypeORM simplify complex operations

8. Performance Tips

For high-frequency queries, useexecute()Comparequery()More efficient
Batch operation considers a single connection using a connection pool
The connection pool that has not been used for a long time should be calledend()closure

These methods cover most usage scenarios of MySQL connection pooling, and rational use can build efficient and reliable database applications.

This is the end of this article about the detailed explanation of the commonly used methods of MySQL connection pool. For more information about the usage of MySQL connection pool, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!