0
# Pool Clustering
1

2
Pool clustering functionality for load balancing across multiple database pools and high availability scenarios. Pool clusters provide node management, automatic failover, and flexible connection routing for distributed database architectures.
3

4
## Capabilities
5

6
### Create Pool Cluster
7

8
Creates a new pool cluster for managing multiple database pools.
9

10
```typescript { .api }
11
/**
12
 * Create a new pool cluster
13
 * @param config - Optional cluster configuration
14
 * @returns PoolCluster instance
15
 */
16
function createPoolCluster(config?: PoolClusterConfig): PoolCluster;
17
```
18

19
**Usage Example:**
20

21
```typescript
22
import mariadb from "mariadb";
23

24
const cluster = mariadb.createPoolCluster({
25
  canRetry: true,
26
  removeNodeErrorCount: 5,
27
  restoreNodeTimeout: 2000,
28
  defaultSelector: 'RR'
29
});
30

31
// Add database nodes
32
cluster.add('master', {
33
  host: 'master.db.example.com',
34
  user: 'root',
35
  password: 'password',
36
  database: 'app'
37
});
38

39
cluster.add('slave1', {
40
  host: 'slave1.db.example.com',
41
  user: 'readonly',
42
  password: 'password',
43
  database: 'app'
44
});
45

46
cluster.add('slave2', {
47
  host: 'slave2.db.example.com',
48
  user: 'readonly', 
49
  password: 'password',
50
  database: 'app'
51
});
52

53
// Get connection from cluster (automatic load balancing)
54
const connection = await cluster.getConnection();
55
const rows = await connection.query("SELECT * FROM users");
56
await connection.release();
57

58
// Target specific node pattern
59
const masterConn = await cluster.getConnection('master');
60
await masterConn.query("INSERT INTO logs (message) VALUES (?)", ["New entry"]);
61
await masterConn.release();
62

63
// Use filtered cluster for read operations
64
const slaves = cluster.of('slave*', 'RANDOM');
65
const readConn = await slaves.getConnection();
66
const data = await readConn.query("SELECT * FROM reports");
67
await readConn.release();
68
```
69

70
### PoolCluster Interface (Promise-based)
71

72
Main cluster interface for managing multiple database pools.
73

74
```typescript { .api }
75
interface PoolCluster extends EventEmitter {
76
  /** Add pool node to cluster */
77
  add(id: string, config: PoolConfig): void;
78
  
79
  /** Remove pool nodes matching pattern */
80
  remove(pattern: string): void;
81
  
82
  /** Close all pools in cluster */
83
  end(): Promise<void>;
84
  
85
  /** Create filtered cluster view */
86
  of(pattern?: string, selector?: string): FilteredPoolCluster;
87
  of(pattern: undefined | null | false, selector: string): FilteredPoolCluster;
88
  
89
  /** Get connection from cluster with optional routing */
90
  getConnection(pattern?: string, selector?: string): Promise<PoolConnection>;
91
  
92
  /** Cluster event listeners */
93
  on(event: 'remove', listener: (nodeKey: string) => void): PoolCluster;
94
}
95
```
96

97
### FilteredPoolCluster Interface
98

99
Filtered view of cluster nodes for targeted operations.
100

101
```typescript { .api }
102
interface FilteredPoolCluster {
103
  /** Get connection from filtered nodes */
104
  getConnection(): Promise<PoolConnection>;
105
  
106
  /** Execute query on filtered cluster */
107
  query<T = any>(sql: string | QueryOptions, values?: any): Promise<T>;
108
  
109
  /** Execute prepared statement on filtered cluster */
110
  execute<T = any>(sql: string | QueryOptions, values?: any): Promise<T>;
111
  
112
  /** Execute batch operations on filtered cluster */
113
  batch<T = UpsertResult | UpsertResult[]>(sql: string | QueryOptions, values?: any): Promise<T>;
114
}
115
```
116

117
### PoolCluster Interface (Callback-based)
118

119
Alternative callback-based cluster interface.
120

121
```typescript { .api }
122
interface PoolCluster extends EventEmitter {
123
  /** Add and remove methods remain synchronous */
124
  add(id: string, config: PoolConfig): void;
125
  remove(pattern: string): void;
126
  
127
  /** Callback-based methods */
128
  end(callback: (err: SqlError | null) => void): void;
129
  getConnection(pattern: string | undefined | null, selector: string | undefined | null, callback: (err: SqlError | null, conn?: PoolConnection) => void): void;
130
  getConnection(pattern: string | undefined | null, callback: (err: SqlError | null, conn?: PoolConnection) => void): void;
131
  getConnection(callback: (err: SqlError | null, conn?: PoolConnection) => void): void;
132
  
133
  /** Filtered cluster creation remains synchronous */
134
  of(pattern: string, selector?: string): FilteredPoolCluster;
135
  of(pattern: undefined | null | false, selector: string): FilteredPoolCluster;
136
  
137
  on(event: 'remove', listener: (nodeKey: string) => void): PoolCluster;
138
}
139
```
140

141
### Cluster Configuration
142

143
Configuration options for cluster behavior and node management.
144

145
```typescript { .api }
146
interface PoolClusterConfig {
147
  /** Enable connection retry on failure (default: true) */
148
  canRetry?: boolean;
149
  
150
  /** Error count before removing node (default: Infinity) */
151
  removeNodeErrorCount?: number;
152
  
153
  /** Milliseconds before attempting node restoration (default: 1000) */
154
  restoreNodeTimeout?: number;
155
  
156
  /** Default load balancing selector (default: 'RR') */
157
  defaultSelector?: string;
158
}
159
```
160

161
**Selector Options:**
162
- `'RR'`: Round-Robin - Rotate through nodes sequentially
163
- `'RANDOM'`: Random selection from available nodes  
164
- `'ORDER'`: Always select first available node
165

166
### Node Management
167

168
Add, remove, and manage database nodes in the cluster.
169

170
```typescript { .api }
171
// Add nodes with different roles
172
cluster.add('master', {
173
  host: 'master.db.com',
174
  user: 'admin',
175
  password: 'secret',
176
  database: 'app',
177
  connectionLimit: 20
178
});
179

180
cluster.add('replica-us-east', {
181
  host: 'replica-east.db.com', 
182
  user: 'readonly',
183
  password: 'secret',
184
  database: 'app',
185
  connectionLimit: 10
186
});
187

188
cluster.add('replica-us-west', {
189
  host: 'replica-west.db.com',
190
  user: 'readonly', 
191
  password: 'secret',
192
  database: 'app',
193
  connectionLimit: 10
194
});
195

196
// Remove nodes by pattern
197
cluster.remove('replica-*'); // Remove all replica nodes
198
cluster.remove('master');    // Remove master node
199
```
200

201
### Connection Routing
202

203
Route connections to specific nodes or groups using patterns and selectors.
204

205
```typescript { .api }
206
// Get connection from any node (default selector)
207
const anyConn = await cluster.getConnection();
208

209
// Get connection from specific node
210
const masterConn = await cluster.getConnection('master');
211

212
// Get connection using specific selector
213
const randomConn = await cluster.getConnection('replica-*', 'RANDOM');
214

215
// Use filtered clusters for targeted operations
216
const writeCluster = cluster.of('master');
217
const readCluster = cluster.of('replica-*', 'RR');
218

219
// Execute operations on different clusters
220
await writeCluster.query("INSERT INTO users (name) VALUES (?)", ["Alice"]);
221
const users = await readCluster.query("SELECT * FROM users");
222
```
223

224
### Pattern Matching
225

226
Use glob-style patterns to match node groups.
227

228
```typescript { .api }
229
// Add nodes with hierarchical naming
230
cluster.add('prod-master-01', config1);
231
cluster.add('prod-replica-us-east-01', config2);
232
cluster.add('prod-replica-us-east-02', config3);
233
cluster.add('prod-replica-us-west-01', config4);
234
cluster.add('test-master-01', config5);
235

236
// Pattern examples
237
const prodNodes = cluster.of('prod-*');           // All production nodes
238
const replicas = cluster.of('*-replica-*');       // All replica nodes
239
const eastReplicas = cluster.of('*-us-east-*');   // East coast replicas
240
const testEnv = cluster.of('test-*');             // Test environment nodes
241
```
242

243
### High Availability Configuration
244

245
Configure automatic failover and node recovery for high availability.
246

247
```typescript { .api }
248
const cluster = mariadb.createPoolCluster({
249
  canRetry: true,              // Enable retry on connection failure
250
  removeNodeErrorCount: 3,     // Remove node after 3 consecutive errors
251
  restoreNodeTimeout: 5000,    // Try to restore failed nodes after 5 seconds
252
  defaultSelector: 'RR'        // Round-robin load balancing
253
});
254

255
// Configure pools with health checking
256
cluster.add('primary', {
257
  host: 'primary.db.com',
258
  user: 'app',
259
  password: 'secret',
260
  database: 'production',
261
  acquireTimeout: 3000,        // Fail fast on connection issues
262
  idleTimeout: 300,            // Keep connections fresh
263
  connectionLimit: 15
264
});
265

266
cluster.add('secondary', {
267
  host: 'secondary.db.com',
268
  user: 'app', 
269
  password: 'secret',
270
  database: 'production',
271
  acquireTimeout: 3000,
272
  idleTimeout: 300,
273
  connectionLimit: 10
274
});
275
```
276

277
### Cluster Events
278

279
Monitor cluster health and node status with event listeners.
280

281
```typescript { .api }
282
// Node removed from cluster due to errors
283
cluster.on('remove', (nodeKey: string) => {
284
  console.log(`Node removed from cluster: ${nodeKey}`);
285
  
286
  // Alert monitoring system
287
  alerting.send(`Database node ${nodeKey} has been removed from cluster`);
288
  
289
  // Update application metrics
290
  metrics.increment('cluster.node.removed', { node: nodeKey });
291
});
292
```
293

294
### Best Practices
295

296
**Read/Write Splitting:**
297

298
```typescript
299
const cluster = mariadb.createPoolCluster();
300

301
cluster.add('master', {
302
  host: 'master.db.com',
303
  user: 'app_write',
304
  password: 'secret',
305
  database: 'app'
306
});
307

308
cluster.add('slave1', {
309
  host: 'slave1.db.com', 
310
  user: 'app_read',
311
  password: 'secret',
312
  database: 'app'
313
});
314

315
cluster.add('slave2', {
316
  host: 'slave2.db.com',
317
  user: 'app_read', 
318
  password: 'secret',
319
  database: 'app'
320
});
321

322
// Create specialized clusters
323
const writeCluster = cluster.of('master');
324
const readCluster = cluster.of('slave*', 'RANDOM');
325

326
// Use appropriate cluster for operations
327
async function createUser(userData: UserData) {
328
  const conn = await writeCluster.getConnection();
329
  try {
330
    const result = await conn.query(
331
      "INSERT INTO users (name, email) VALUES (?, ?)",
332
      [userData.name, userData.email]
333
    );
334
    return result.insertId;
335
  } finally {
336
    await conn.release();
337
  }
338
}
339

340
async function getUsers() {
341
  const conn = await readCluster.getConnection();
342
  try {
343
    return await conn.query("SELECT * FROM users");
344
  } finally {
345
    await conn.release();
346
  }
347
}
348
```
349

350
**Geographic Distribution:**
351

352
```typescript
353
const cluster = mariadb.createPoolCluster({
354
  defaultSelector: 'RR'
355
});
356

357
// Add geographically distributed nodes
358
cluster.add('us-east-1', {
359
  host: 'db-east-1.us.example.com',
360
  // ... config
361
});
362

363
cluster.add('us-west-1', {
364
  host: 'db-west-1.us.example.com', 
365
  // ... config
366
});
367

368
cluster.add('eu-central-1', {
369
  host: 'db-central-1.eu.example.com',
370
  // ... config
371
});
372

373
// Route based on user location
374
function getClusterForRegion(region: string) {
375
  switch (region) {
376
    case 'us-east':
377
      return cluster.of('us-east-*');
378
    case 'us-west':
379
      return cluster.of('us-west-*');
380
    case 'europe':
381
      return cluster.of('eu-*');
382
    default:
383
      return cluster; // Use default balancing
384
  }
385
}
386
```