0
# Connection Pooling
1

2
Advanced connection pooling system for managing multiple database connections efficiently. Ideal for applications with concurrent database access requirements and production environments where connection reuse is essential for performance.
3

4
## Capabilities
5

6
### Create Pool
7

8
Creates a new connection pool with specified configuration.
9

10
```javascript { .api }
11
/**
12
 * Create a new Pool instance
13
 * @param {object|string} config - Configuration or connection string for new MySQL connections
14
 * @returns {Pool} A new MySQL pool
15
 */
16
function createPool(config);
17

18
// Pool configuration extends ConnectionConfig
19
interface PoolConfig extends ConnectionConfig {
20
  connectionLimit?: number;     // Maximum number of connections in pool (default: 10)
21
  queueLimit?: number;         // Maximum number of queued connection requests (default: 0 - no limit)
22
  acquireTimeout?: number;     // Timeout for getting connection from pool (default: 10000ms)
23
  waitForConnections?: boolean; // Whether to queue connection requests when limit reached (default: true)
24
  reconnect?: boolean;         // Automatically reconnect when connection is lost (default: true)
25
}
26
```
27

28
**Usage Examples:**
29

30
```javascript
31
const mysql = require('mysql');
32

33
// Basic pool configuration
34
const pool = mysql.createPool({
35
  connectionLimit: 10,
36
  host: 'localhost',
37
  user: 'root',
38
  password: 'password',
39
  database: 'myapp',
40
  acquireTimeout: 60000,
41
  reconnect: true
42
});
43

44
// Production pool with SSL
45
const productionPool = mysql.createPool({
46
  connectionLimit: 20,
47
  queueLimit: 0,
48
  host: 'mysql.example.com',
49
  user: 'app_user',
50
  password: 'secure_password',
51
  database: 'production_db',
52
  ssl: {
53
    ca: fs.readFileSync('./server-ca.pem')
54
  },
55
  reconnect: true,
56
  acquireTimeout: 60000
57
});
58
```
59

60
### Pool Methods
61

62
#### Get Connection
63

64
Gets a connection from the pool.
65

66
```javascript { .api }
67
/**
68
 * Gets a connection from pool
69
 * @param {function} callback - Callback function (err, connection)
70
 */
71
pool.getConnection(callback);
72
```
73

74
**Usage Examples:**
75

76
```javascript
77
// Basic connection acquisition
78
pool.getConnection((err, connection) => {
79
  if (err) throw err;
80
  
81
  connection.query('SELECT * FROM users', (error, results) => {
82
    // Release connection back to pool
83
    connection.release();
84
    
85
    if (error) throw error;
86
    console.log(results);
87
  });
88
});
89

90
// With proper error handling
91
pool.getConnection((err, connection) => {
92
  if (err) {
93
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
94
      console.error('Database connection was closed.');
95
    }
96
    if (err.code === 'ER_CON_COUNT_ERROR') {
97
      console.error('Database has too many connections.');
98
    }
99
    if (err.code === 'ECONNREFUSED') {
100
      console.error('Database connection was refused.');
101
    }
102
    return;
103
  }
104
  
105
  // Use connection
106
  connection.query('SELECT something FROM sometable', (error, results) => {
107
    connection.release();
108
    if (error) throw error;
109
    // Handle results
110
  });
111
});
112
```
113

114
#### Direct Query
115

116
Executes a query using an available connection from the pool.
117

118
```javascript { .api }
119
/**
120
 * Executes query using pool connection
121
 * @param {string|object} sql - SQL query string or query object
122
 * @param {array} [values] - Parameter values for prepared statements
123
 * @param {function} [callback] - Callback function (err, results, fields)
124
 * @returns {Query} Query object for event-based processing
125
 */
126
pool.query(sql, values, callback);
127
```
128

129
**Usage Examples:**
130

131
```javascript
132
// Simple pool query
133
pool.query('SELECT * FROM users', (error, results, fields) => {
134
  if (error) throw error;
135
  console.log(results);
136
});
137

138
// Parameterized pool query
139
pool.query('SELECT * FROM users WHERE id = ?', [userId], (error, results) => {
140
  if (error) throw error;
141
  console.log(results[0]);
142
});
143

144
// Event-based pool query
145
const query = pool.query('SELECT * FROM large_table');
146
query.on('result', (row) => {
147
  console.log(row);
148
});
149
query.on('end', () => {
150
  console.log('Query completed');
151
});
152
```
153

154
#### End Pool
155

156
Closes all connections in the pool.
157

158
```javascript { .api }
159
/**
160
 * Closes all connections in pool
161
 * @param {function} [callback] - Callback function (err)
162
 */
163
pool.end(callback);
164
```
165

166
**Usage Example:**
167

168
```javascript
169
// Graceful shutdown
170
process.on('SIGINT', () => {
171
  console.log('Closing connection pool...');
172
  pool.end(() => {
173
    console.log('Pool closed');
174
    process.exit(0);
175
  });
176
});
177
```
178

179
### Pool Connection Methods
180

181
Pool connections are extended Connection instances with additional pool-specific methods.
182

183
#### Release Connection
184

185
Returns a connection to the pool for reuse.
186

187
```javascript { .api }
188
/**
189
 * Releases connection back to pool
190
 */
191
connection.release();
192
```
193

194
#### Destroy Pool Connection
195

196
Removes and destroys a connection from the pool.
197

198
```javascript { .api }
199
/**
200
 * Destroys the connection (removes from pool)
201
 */
202
connection.destroy();
203
```
204

205
**Pool Connection Usage:**
206

207
```javascript
208
pool.getConnection((err, connection) => {
209
  if (err) throw err;
210
  
211
  // Check if connection is still good
212
  connection.ping((err) => {
213
    if (err) {
214
      // Connection is bad, destroy it
215
      connection.destroy();
216
      return;
217
    }
218
    
219
    // Use connection
220
    connection.query('SELECT * FROM users', (error, results) => {
221
      if (error) {
222
        // Release connection even on error
223
        connection.release();
224
        throw error;
225
      }
226
      
227
      // Release connection back to pool
228
      connection.release();
229
      console.log(results);
230
    });
231
  });
232
});
233
```
234

235
### Pool Utility Methods
236

237
#### Escape
238

239
Escapes a value for safe SQL usage.
240

241
```javascript { .api }
242
/**
243
 * Escapes a value for SQL
244
 * @param {*} value - The value to escape
245
 * @returns {string} Escaped string value
246
 */
247
pool.escape(value);
248
```
249

250
#### Escape Identifier
251

252
Escapes an identifier for SQL.
253

254
```javascript { .api }
255
/**
256
 * Escapes an identifier for SQL
257
 * @param {*} value - The value to escape
258
 * @returns {string} Escaped identifier
259
 */
260
pool.escapeId(value);
261
```
262

263
### Pool Properties
264

265
```javascript { .api }
266
// Pool instance properties
267
interface Pool {
268
  config: PoolConfig;          // Pool configuration
269
  _allConnections: Connection[]; // All connections (active + free)
270
  _freeConnections: Connection[]; // Available connections
271
  _acquiringConnections: Connection[]; // Connections being acquired
272
  _connectionQueue: Function[]; // Queue of pending connection requests
273
  _closed: boolean;           // Whether pool is closed
274
}
275
```
276

277
### Pool Events
278

279
#### Connection Event
280

281
Fired when a new connection is created in the pool.
282

283
```javascript { .api }
284
pool.on('connection', (connection) => {
285
  console.log('New connection established as id ' + connection.threadId);
286
});
287
```
288

289
#### Acquire Event
290

291
Fired when a connection is acquired from the pool.
292

293
```javascript { .api }
294
pool.on('acquire', (connection) => {
295
  console.log('Connection %d acquired', connection.threadId);
296
});
297
```
298

299
#### Release Event
300

301
Fired when a connection is released back to the pool.
302

303
```javascript { .api }
304
pool.on('release', (connection) => {
305
  console.log('Connection %d released', connection.threadId);
306
});
307
```
308

309
#### Enqueue Event
310

311
Fired when a connection request is queued due to pool limits.
312

313
```javascript { .api }
314
pool.on('enqueue', () => {
315
  console.log('Waiting for available connection slot');
316
});
317
```
318

319
**Complete Pool Example:**
320

321
```javascript
322
const mysql = require('mysql');
323

324
// Create pool
325
const pool = mysql.createPool({
326
  connectionLimit: 10,
327
  host: 'localhost',
328
  user: 'root',
329
  password: 'password',
330
  database: 'myapp',
331
  acquireTimeout: 60000,
332
  reconnect: true
333
});
334

335
// Set up event handlers
336
pool.on('connection', (connection) => {
337
  console.log('New connection established as id ' + connection.threadId);
338
});
339

340
pool.on('acquire', (connection) => {
341
  console.log('Connection %d acquired', connection.threadId);
342
});
343

344
pool.on('release', (connection) => {
345
  console.log('Connection %d released', connection.threadId);
346
});
347

348
pool.on('enqueue', () => {
349
  console.log('Waiting for available connection slot');
350
});
351

352
// Use pool for queries
353
async function getUser(userId) {
354
  return new Promise((resolve, reject) => {
355
    pool.query('SELECT * FROM users WHERE id = ?', [userId], (error, results) => {
356
      if (error) {
357
        reject(error);
358
      } else {
359
        resolve(results[0]);
360
      }
361
    });
362
  });
363
}
364

365
// Use pool with manual connection management
366
function getUserWithTransaction(userId, callback) {
367
  pool.getConnection((err, connection) => {
368
    if (err) return callback(err);
369
    
370
    connection.beginTransaction((err) => {
371
      if (err) {
372
        connection.release();
373
        return callback(err);
374
      }
375
      
376
      connection.query('SELECT * FROM users WHERE id = ?', [userId], (error, results) => {
377
        if (error) {
378
          return connection.rollback(() => {
379
            connection.release();
380
            callback(error);
381
          });
382
        }
383
        
384
        connection.commit((err) => {
385
          if (err) {
386
            return connection.rollback(() => {
387
              connection.release();
388
              callback(err);
389
            });
390
          }
391
          
392
          connection.release();
393
          callback(null, results[0]);
394
        });
395
      });
396
    });
397
  });
398
}
399

400
// Graceful shutdown
401
process.on('SIGINT', () => {
402
  console.log('Closing connection pool...');
403
  pool.end(() => {
404
    console.log('Pool closed');
405
    process.exit(0);
406
  });
407
});
408
```
409

410
### Pool Best Practices
411

412
1. **Connection Limits**: Set appropriate `connectionLimit` based on your database server capacity and application needs
413
2. **Error Handling**: Always handle connection acquisition errors and release connections properly
414
3. **Timeouts**: Configure `acquireTimeout` to prevent indefinite waiting for connections
415
4. **Monitoring**: Use pool events to monitor connection usage and detect issues
416
5. **Graceful Shutdown**: Always close pools before application shutdown to prevent resource leaks
417
6. **Connection Health**: Consider implementing connection health checks for long-running applications
418
7. **Queue Management**: Set `queueLimit` to prevent memory issues during high load