0
# Connection Pools
1

2
Connection pooling for scalable database access with automatic connection management, load balancing, and efficient resource utilization for high-throughput applications.
3

4
## Capabilities
5

6
### Create Pool
7

8
Creates a connection pool with the specified configuration.
9

10
```javascript { .api }
11
/**
12
 * Creates a connection pool
13
 * @param config - Pool configuration object or connection string
14
 * @returns Pool instance
15
 */
16
function createPool(config: PoolOptions | string): Pool;
17
```
18

19
**Usage Examples:**
20

21
```javascript
22
const mysql = require('mysql2');
23

24
// Create a pool with configuration
25
const pool = mysql.createPool({
26
  host: 'localhost',
27
  user: 'root',
28
  password: 'password',
29
  database: 'testdb',
30
  connectionLimit: 10,
31
  waitForConnections: true,
32
  queueLimit: 0
33
});
34

35
// Execute queries directly on pool
36
pool.query('SELECT * FROM users', (error, results) => {
37
  if (error) throw error;
38
  console.log(results);
39
});
40

41
// Get individual connection from pool
42
pool.getConnection((err, connection) => {
43
  if (err) throw err;
44
  
45
  connection.query('SELECT * FROM products', (error, results) => {
46
    // Release connection back to pool
47
    connection.release();
48
    
49
    if (error) throw error;
50
    console.log(results);
51
  });
52
});
53
```
54

55
### Pool Class
56

57
Main pool class providing connection management and query execution methods.
58

59
```typescript { .api }
60
interface Pool extends EventEmitter {
61
  /** Pool configuration */
62
  config: PoolOptions;
63
  
64
  /** Get connection from pool */
65
  getConnection(callback: (err: Error | null, connection?: PoolConnection) => void): void;
66
  
67
  /** Release connection back to pool */
68
  releaseConnection(connection: PoolConnection): void;
69
  
70
  /** Execute SQL query using pool connection */
71
  query(sql: string, callback?: QueryCallback): Query;
72
  query(sql: string, values: any[], callback?: QueryCallback): Query;
73
  query(options: QueryOptions, callback?: QueryCallback): Query;
74
  
75
  /** Execute prepared statement using pool connection */
76
  execute(sql: string, values?: any[], callback?: ExecuteCallback): void;
77
  
78
  /** Close all connections in pool */
79
  end(callback?: (err: Error | null) => void): void;
80
  
81
  /** Escape SQL value */
82
  escape(value: any): string;
83
  
84
  /** Escape SQL identifier */
85
  escapeId(value: string | string[]): string;
86
  
87
  /** Format SQL query with values */
88
  format(sql: string, values?: any[]): string;
89
}
90
```
91

92
### Pool Connection
93

94
Connection instance obtained from a pool with additional pool-specific methods.
95

96
```typescript { .api }
97
interface PoolConnection extends Connection {
98
  /** Release connection back to pool */
99
  release(): void;
100
  
101
  /** Destroy connection without returning to pool */
102
  destroy(): void;
103
  
104
  /** Underlying connection object */
105
  connection: Connection;
106
}
107
```
108

109
### Pool Options
110

111
Configuration interface for connection pools extending ConnectionOptions.
112

113
```typescript { .api }
114
interface PoolOptions extends ConnectionOptions {
115
  /** Maximum number of connections in pool */
116
  connectionLimit?: number;
117
  
118
  /** Wait for available connection when limit reached */
119
  waitForConnections?: boolean;
120
  
121
  /** Maximum number of queued connection requests */
122
  queueLimit?: number;
123
  
124
  /** Maximum number of idle connections */
125
  maxIdle?: number;
126
  
127
  /** Idle connection timeout in milliseconds */
128
  idleTimeout?: number;
129
  
130
  /** Acquire connection timeout in milliseconds */
131
  acquireTimeout?: number;
132
  
133
  /** Enable connection queue timeout */
134
  enableKeepAlive?: boolean;
135
  
136
  /** Keep alive initial delay */
137
  keepAliveInitialDelay?: number;
138
}
139
```
140

141
### Pool Events
142

143
Pools emit events for connection lifecycle management.
144

145
```javascript { .api }
146
// Event: 'connection' - New connection added to pool
147
pool.on('connection', (connection) => {
148
  console.log('New connection added as id ' + connection.threadId);
149
});
150

151
// Event: 'acquire' - Connection acquired from pool
152
pool.on('acquire', (connection) => {
153
  console.log('Connection %d acquired', connection.threadId);
154
});
155

156
// Event: 'release' - Connection released back to pool
157
pool.on('release', (connection) => {
158
  console.log('Connection %d released', connection.threadId);
159
});
160

161
// Event: 'enqueue' - Connection request queued
162
pool.on('enqueue', () => {
163
  console.log('Waiting for available connection slot');
164
});
165

166
// Event: 'error' - Pool error occurred
167
pool.on('error', (error) => {
168
  console.error('Pool error:', error);
169
});
170
```
171

172
### Advanced Pool Usage
173

174
**Transaction Management with Pools:**
175

176
```javascript
177
pool.getConnection((err, connection) => {
178
  if (err) throw err;
179
  
180
  connection.beginTransaction((err) => {
181
    if (err) {
182
      connection.release();
183
      throw err;
184
    }
185
    
186
    connection.query('INSERT INTO users SET ?', userData, (error, results) => {
187
      if (error) {
188
        return connection.rollback(() => {
189
          connection.release();
190
          throw error;
191
        });
192
      }
193
      
194
      connection.query('INSERT INTO profiles SET ?', profileData, (error, results) => {
195
        if (error) {
196
          return connection.rollback(() => {
197
            connection.release();
198
            throw error;
199
          });
200
        }
201
        
202
        connection.commit((err) => {
203
          if (err) {
204
            return connection.rollback(() => {
205
              connection.release();
206
              throw err;
207
            });
208
          }
209
          
210
          console.log('Transaction completed successfully!');
211
          connection.release();
212
        });
213
      });
214
    });
215
  });
216
});
217
```
218

219
**Pool Monitoring:**
220

221
```javascript
222
// Monitor pool status
223
setInterval(() => {
224
  console.log({
225
    totalConnections: pool._allConnections.length,
226
    freeConnections: pool._freeConnections.length,
227
    connectionQueue: pool._connectionQueue.length
228
  });
229
}, 5000);
230
```
231

232
## Pool Management Best Practices
233

234
### Connection Limits
235

236
```javascript
237
const pool = mysql.createPool({
238
  // Set reasonable connection limit based on database capacity
239
  connectionLimit: 10,
240
  
241
  // Don't let connection requests wait indefinitely
242
  acquireTimeout: 30000,
243
  
244
  // Limit queue size to prevent memory issues
245
  queueLimit: 50,
246
  
247
  // Clean up idle connections
248
  maxIdle: 5,
249
  idleTimeout: 60000
250
});
251
```
252

253
### Graceful Shutdown
254

255
```javascript
256
process.on('SIGINT', async () => {
257
  console.log('Closing connection pool...');
258
  
259
  pool.end((err) => {
260
    if (err) {
261
      console.error('Error closing pool:', err);
262
      process.exit(1);
263
    }
264
    console.log('Pool closed successfully');
265
    process.exit(0);
266
  });
267
});
268
```
269

270
## Error Handling
271

272
Pool operations can encounter various error conditions:
273

274
```javascript
275
pool.getConnection((err, connection) => {
276
  if (err) {
277
    if (err.code === 'POOL_CLOSED') {
278
      console.error('Pool has been closed');
279
    } else if (err.code === 'POOL_CONNLIMIT') {
280
      console.error('Connection limit reached');
281
    } else if (err.code === 'POOL_ENQUEUELIMIT') {
282
      console.error('Queue limit reached');
283
    }
284
    return;
285
  }
286
  
287
  // Use connection...
288
  connection.release();
289
});
290
```