0
# Pool Clusters
1

2
Pool cluster management for multiple connection pools with pattern-based routing, load balancing across multiple databases, and high availability configurations for distributed database architectures.
3

4
## Capabilities
5

6
### Create Pool Cluster
7

8
Creates a pool cluster for managing multiple connection pools.
9

10
```javascript { .api }
11
/**
12
 * Creates a pool cluster
13
 * @param config - Pool cluster configuration options
14
 * @returns PoolCluster instance
15
 */
16
function createPoolCluster(config?: PoolClusterOptions): PoolCluster;
17
```
18

19
**Usage Examples:**
20

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

24
// Create a pool cluster
25
const cluster = mysql.createPoolCluster({
26
  canRetry: true,
27
  removeNodeErrorCount: 5,
28
  restoreNodeTimeout: 50000,
29
  defaultSelector: 'RR'
30
});
31

32
// Add pools to cluster
33
cluster.add('MASTER', {
34
  host: 'master.example.com',
35
  user: 'root',
36
  password: 'password',
37
  database: 'testdb'
38
});
39

40
cluster.add('SLAVE1', {
41
  host: 'slave1.example.com',
42
  user: 'root',
43
  password: 'password',
44
  database: 'testdb'
45
});
46

47
cluster.add('SLAVE2', {
48
  host: 'slave2.example.com',
49
  user: 'root',
50
  password: 'password',
51
  database: 'testdb'
52
});
53

54
// Use cluster for read operations
55
cluster.getConnection('SLAVE*', (err, connection) => {
56
  if (err) throw err;
57
  
58
  connection.query('SELECT * FROM users', (error, results) => {
59
    connection.release();
60
    if (error) throw error;
61
    console.log(results);
62
  });
63
});
64

65
// Use specific pool for write operations
66
cluster.getConnection('MASTER', (err, connection) => {
67
  if (err) throw err;
68
  
69
  connection.query('INSERT INTO users SET ?', userData, (error, results) => {
70
    connection.release();
71
    if (error) throw error;
72
    console.log('User inserted:', results.insertId);
73
  });
74
});
75
```
76

77
### Pool Cluster Class
78

79
Main pool cluster class providing pool management and connection routing methods.
80

81
```typescript { .api }
82
interface PoolCluster extends EventEmitter {
83
  /** Pool cluster configuration */
84
  config: PoolClusterOptions;
85
  
86
  /** Add pool to cluster */
87
  add(config: PoolOptions): void;
88
  add(group: string, config: PoolOptions): void;
89
  add(group: string, connectionUri: string): void;
90
  
91
  /** Remove pool from cluster */
92
  remove(pattern?: string): void;
93
  
94
  /** Get connection from cluster using pattern matching */
95
  getConnection(callback: (err: Error | null, connection?: PoolConnection) => void): void;
96
  getConnection(pattern: string, callback: (err: Error | null, connection?: PoolConnection) => void): void;
97
  getConnection(pattern: string, selector: string, callback: (err: Error | null, connection?: PoolConnection) => void): void;
98
  
99
  /** Get pool namespace for pattern */
100
  of(pattern: string, selector?: string): PoolNamespace;
101
  
102
  /** Execute query on cluster */
103
  query(sql: string, callback?: QueryCallback): Query;
104
  query(sql: string, values: any[], callback?: QueryCallback): Query;
105
  
106
  /** Execute prepared statement on cluster */
107
  execute(sql: string, values?: any[], callback?: ExecuteCallback): void;
108
  
109
  /** Close all pools in cluster */
110
  end(callback?: (err: Error | null) => void): void;
111
}
112
```
113

114
### Pool Namespace
115

116
Namespace interface for executing operations on a subset of pools matching a pattern.
117

118
```typescript { .api }
119
interface PoolNamespace {
120
  /** Execute query on namespace */
121
  query(sql: string, callback?: QueryCallback): Query;
122
  query(sql: string, values: any[], callback?: QueryCallback): Query;
123
  
124
  /** Execute prepared statement on namespace */
125
  execute(sql: string, values?: any[], callback?: ExecuteCallback): void;
126
  
127
  /** Get connection from namespace */
128
  getConnection(callback: (err: Error | null, connection?: PoolConnection) => void): void;
129
}
130
```
131

132
### Pool Cluster Options
133

134
Configuration interface for pool clusters.
135

136
```typescript { .api }
137
interface PoolClusterOptions {
138
  /** Enable connection retry on failure */
139
  canRetry?: boolean;
140
  
141
  /** Remove node after this many errors */
142
  removeNodeErrorCount?: number;
143
  
144
  /** Restore node after this timeout (ms) */
145
  restoreNodeTimeout?: number;
146
  
147
  /** Default connection selector algorithm */
148
  defaultSelector?: 'RR' | 'RANDOM' | 'ORDER';
149
  
150
  /** Maximum reconnection attempts */
151
  maxReconnects?: number;
152
  
153
  /** Selector functions for custom load balancing */
154
  selectors?: {
155
    [name: string]: (pools: Pool[]) => Pool;
156
  };
157
}
158
```
159

160
### Pool Cluster Events
161

162
Pool clusters emit events for pool management and error handling.
163

164
```javascript { .api }
165
// Event: 'remove' - Pool removed from cluster
166
cluster.on('remove', (nodeId) => {
167
  console.log('Pool removed:', nodeId);
168
});
169

170
// Event: 'warn' - Warning occurred
171
cluster.on('warn', (error) => {
172
  console.warn('Cluster warning:', error);
173
});
174

175
// Event: 'online' - Pool came online
176
cluster.on('online', (nodeId) => {
177
  console.log('Pool online:', nodeId);
178
});
179

180
// Event: 'offline' - Pool went offline
181
cluster.on('offline', (nodeId) => {
182
  console.log('Pool offline:', nodeId);
183
});
184
```
185

186
### Advanced Pool Cluster Usage
187

188
**Master-Slave Configuration:**
189

190
```javascript
191
const cluster = mysql.createPoolCluster({
192
  canRetry: true,
193
  removeNodeErrorCount: 3,
194
  restoreNodeTimeout: 60000,
195
  defaultSelector: 'RANDOM'
196
});
197

198
// Add master for writes
199
cluster.add('MASTER', {
200
  host: 'master.db.com',
201
  user: 'app_user',
202
  password: 'password',
203
  database: 'production',
204
  connectionLimit: 5
205
});
206

207
// Add slaves for reads
208
cluster.add('SLAVE1', {
209
  host: 'slave1.db.com',
210
  user: 'app_user',
211
  password: 'password',
212
  database: 'production',
213
  connectionLimit: 10
214
});
215

216
cluster.add('SLAVE2', {
217
  host: 'slave2.db.com',
218
  user: 'app_user',
219
  password: 'password',
220
  database: 'production',
221
  connectionLimit: 10
222
});
223

224
// Create namespaces for different operations
225
const readPool = cluster.of('SLAVE*', 'RANDOM');
226
const writePool = cluster.of('MASTER');
227

228
// Read operations
229
readPool.query('SELECT * FROM users WHERE active = 1', (err, results) => {
230
  if (err) throw err;
231
  console.log('Active users:', results.length);
232
});
233

234
// Write operations
235
writePool.query('UPDATE users SET last_login = NOW() WHERE id = ?', [userId], (err, result) => {
236
  if (err) throw err;
237
  console.log('User updated');
238
});
239
```
240

241
**Geographic Distribution:**
242

243
```javascript
244
const cluster = mysql.createPoolCluster();
245

246
// US East region
247
cluster.add('US_EAST_1', {
248
  host: 'us-east-1.rds.amazonaws.com',
249
  user: 'app_user',
250
  password: 'password',
251
  database: 'app_db'
252
});
253

254
cluster.add('US_EAST_2', {
255
  host: 'us-east-2.rds.amazonaws.com',
256
  user: 'app_user',
257
  password: 'password',
258
  database: 'app_db'
259
});
260

261
// EU region
262
cluster.add('EU_WEST_1', {
263
  host: 'eu-west-1.rds.amazonaws.com',
264
  user: 'app_user',
265
  password: 'password',
266
  database: 'app_db'
267
});
268

269
// Route based on user location
270
function getConnectionForRegion(region) {
271
  const pattern = region === 'EU' ? 'EU_*' : 'US_*';
272
  return cluster.of(pattern, 'RANDOM');
273
}
274
```
275

276
**Custom Selector:**
277

278
```javascript
279
const cluster = mysql.createPoolCluster({
280
  defaultSelector: 'CUSTOM',
281
  selectors: {
282
    CUSTOM: (pools) => {
283
      // Custom load balancing logic
284
      const availablePools = pools.filter(pool => pool._freeConnections.length > 0);
285
      return availablePools.length > 0 
286
        ? availablePools[Math.floor(Math.random() * availablePools.length)]
287
        : pools[0];
288
    }
289
  }
290
});
291
```
292

293
## Pattern Matching
294

295
Pool clusters support pattern-based pool selection:
296

297
- `*` - Matches any characters
298
- `?` - Matches single character
299
- `SLAVE*` - Matches SLAVE1, SLAVE2, SLAVE_BACKUP, etc.
300
- `US_*_1` - Matches US_EAST_1, US_WEST_1, etc.
301

302
## Load Balancing Selectors
303

304
Available selector algorithms:
305

306
- **RR** (Round Robin) - Cycles through pools sequentially
307
- **RANDOM** - Selects pools randomly
308
- **ORDER** - Uses pools in addition order
309

310
## Error Handling and Failover
311

312
Pool clusters provide automatic failover and error recovery:
313

314
```javascript
315
cluster.on('remove', (nodeId) => {
316
  console.log(`Pool ${nodeId} removed due to errors`);
317
  
318
  // Optionally add replacement pool
319
  if (nodeId.startsWith('SLAVE')) {
320
    cluster.add('SLAVE_BACKUP', backupConfig);
321
  }
322
});
323

324
cluster.on('warn', (error) => {
325
  console.warn('Cluster warning:', error.message);
326
  
327
  // Monitor and alert on cluster health
328
  if (error.code === 'POOL_NONEONLINE') {
329
    console.error('All pools offline! Emergency failover needed.');
330
  }
331
});
332
```