0
# Connection Pooling
1

2
Connection pooling system for managing multiple database connections efficiently. Connection pools provide automatic connection lifecycle management, configurable pool sizing, and resource optimization for high-performance applications.
3

4
## Capabilities
5

6
### Create Pool
7

8
Creates a new connection pool with the specified configuration.
9

10
```typescript { .api }
11
/**
12
 * Create a new connection pool (Promise-based API)
13
 * @param config - Pool configuration object or connection string
14
 * @returns Pool instance
15
 */
16
function createPool(config: string | PoolConfig): Pool;
17
```
18

19
**Usage Example:**
20

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

24
const pool = mariadb.createPool({
25
  host: "localhost",
26
  user: "root",
27
  password: "password",
28
  database: "test",
29
  connectionLimit: 10,
30
  acquireTimeout: 10000,
31
  idleTimeout: 1800
32
});
33

34
// Execute query directly on pool
35
const rows = await pool.query("SELECT * FROM users");
36

37
// Get dedicated connection from pool
38
const connection = await pool.getConnection();
39
try {
40
  await connection.query("START TRANSACTION");
41
  await connection.query("INSERT INTO users (name) VALUES (?)", ["Alice"]);
42
  await connection.commit();
43
} finally {
44
  await connection.release(); // Return connection to pool
45
}
46
```
47

48
### Pool Interface (Promise-based)
49

50
Main pool interface providing Promise-based database operations and pool management.
51

52
```typescript { .api }
53
interface Pool extends EventEmitter {
54
  /** Whether the pool is closed */
55
  closed: boolean;
56
  
57
  /** Get connection from pool */
58
  getConnection(): Promise<PoolConnection>;
59
  
60
  /** Execute query on pool connection */
61
  query<T = any>(sql: string | QueryOptions, values?: any): Promise<T>;
62
  
63
  /** Execute prepared statement on pool connection */
64
  execute<T = any>(sql: string | QueryOptions, values?: any): Promise<T>;
65
  
66
  /** Execute batch operations on pool connection */
67
  batch<T = UpsertResult | UpsertResult[]>(sql: string | QueryOptions, values?: any): Promise<T>;
68
  
69
  /** Import SQL file using pool connection */
70
  importFile(config: SqlImportOptions): Promise<void>;
71
  
72
  /** Close all connections in pool */
73
  end(): Promise<void>;
74
  
75
  /** Get number of active connections */
76
  activeConnections(): number;
77
  
78
  /** Get total number of connections */
79
  totalConnections(): number;
80
  
81
  /** Get number of idle connections */
82
  idleConnections(): number;
83
  
84
  /** Get number of queued connection requests */
85
  taskQueueSize(): number;
86
  
87
  /** Escape SQL parameter */
88
  escape(value: any): string;
89
  
90
  /** Escape SQL identifier */
91
  escapeId(identifier: string): string;
92
  
93
  /** Pool event listeners */
94
  on(event: 'acquire', listener: (conn: Connection) => void): Pool;
95
  on(event: 'connection', listener: (conn: Connection) => void): Pool;
96
  on(event: 'enqueue', listener: () => void): Pool;
97
  on(event: 'release', listener: (conn: Connection) => void): Pool;
98
  on(event: 'error', listener: (err: SqlError) => void): Pool;
99
}
100
```
101

102
### Pool Connection Interface
103

104
Pool connections extend regular connections with pool-specific functionality.
105

106
```typescript { .api }
107
interface PoolConnection extends Connection {
108
  /** Release connection back to pool */
109
  release(): Promise<void>;
110
}
111
```
112

113
### Pool Interface (Callback-based)
114

115
Alternative callback-based pool interface.
116

117
```typescript { .api }
118
interface Pool extends EventEmitter {
119
  closed: boolean;
120
  
121
  /** All methods use Node.js callback pattern */
122
  getConnection(callback: (err: SqlError | null, conn?: PoolConnection) => void): void;
123
  query<T>(sql: string | QueryOptions, values: any, callback: (err: SqlError | null, result?: T, meta?: FieldInfo[]) => void): void;
124
  query<T>(sql: string | QueryOptions, callback: (err: SqlError | null, result?: T, meta?: FieldInfo[]) => void): void;
125
  execute<T>(sql: string | QueryOptions, values: any, callback: (err: SqlError | null, result?: T, meta?: FieldInfo[]) => void): void;
126
  execute<T>(sql: string | QueryOptions, callback: (err: SqlError | null, result?: T, meta?: FieldInfo[]) => void): void;
127
  batch<T>(sql: string | QueryOptions, values: any, callback: (err: SqlError | null, result?: T) => void): void;
128
  batch<T>(sql: string | QueryOptions, callback: (err: SqlError | null, result?: T) => void): void;
129
  importFile(config: SqlImportOptions, callback: (err: SqlError | null) => void): void;
130
  end(callback: (err: SqlError | null) => void): void;
131
  
132
  /** Pool statistics remain synchronous */
133
  activeConnections(): number;
134
  totalConnections(): number;
135
  idleConnections(): number;
136
  taskQueueSize(): number;
137
  escape(value: any): string;
138
  escapeId(identifier: string): string;
139
}
140
```
141

142
### Pool Configuration
143

144
Comprehensive configuration options for pool behavior and connection management.
145

146
```typescript { .api }
147
interface PoolConfig extends ConnectionConfig {
148
  /** Maximum time to wait for connection acquisition (default: 10000ms) */
149
  acquireTimeout?: number;
150
  
151
  /** Maximum number of connections in pool (default: 10) */
152
  connectionLimit?: number;
153
  
154
  /** Idle timeout before connection is released (default: 1800s) */
155
  idleTimeout?: number;
156
  
157
  /** Connection leak detection timeout (default: 0 - disabled) */
158
  leakDetectionTimeout?: number;
159
  
160
  /** Timeout for pool initialization */
161
  initializationTimeout?: number;
162
  
163
  /** Minimum delay between connection validations (default: 500ms) */
164
  minDelayValidation?: number;
165
  
166
  /** Minimum number of idle connections to maintain */
167
  minimumIdle?: number;
168
  
169
  /** Skip connection reset/rollback when returning to pool (default: false) */
170
  noControlAfterUse?: boolean;
171
  
172
  /** Use COM_STMT_RESET when returning connection to pool (default: true) */
173
  resetAfterUse?: boolean;
174
}
175
```
176

177
**Configuration Example:**
178

179
```typescript
180
const pool = mariadb.createPool({
181
  // Connection settings
182
  host: "localhost",
183
  user: "dbuser",
184
  password: "dbpass",
185
  database: "myapp",
186
  
187
  // Pool settings
188
  connectionLimit: 20,
189
  acquireTimeout: 15000,
190
  idleTimeout: 900, // 15 minutes
191
  leakDetectionTimeout: 30000, // Log potential leaks after 30s
192
  
193
  // Connection validation
194
  minDelayValidation: 200,
195
  resetAfterUse: true,
196
  
197
  // Performance settings
198
  compress: true,
199
  pipelining: true,
200
  bulk: true
201
});
202
```
203

204
### Pool Events
205

206
Pools emit events for monitoring connection lifecycle and pool health.
207

208
```typescript { .api }
209
// Connection acquired from pool
210
pool.on('acquire', (connection: Connection) => {
211
  console.log('Connection acquired:', connection.threadId);
212
});
213

214
// New connection created
215
pool.on('connection', (connection: Connection) => {
216
  console.log('New connection created:', connection.threadId);
217
});
218

219
// Connection request enqueued (waiting for available connection)
220
pool.on('enqueue', () => {
221
  console.log('Connection request enqueued');
222
});
223

224
// Connection released back to pool
225
pool.on('release', (connection: Connection) => {
226
  console.log('Connection released:', connection.threadId);
227
});
228

229
// Pool error
230
pool.on('error', (err: SqlError) => {
231
  console.error('Pool error:', err);
232
});
233
```
234

235
### Pool Statistics and Monitoring
236

237
Monitor pool health and performance with built-in statistics.
238

239
```typescript { .api }
240
// Check pool statistics
241
console.log('Total connections:', pool.totalConnections());
242
console.log('Active connections:', pool.activeConnections());
243
console.log('Idle connections:', pool.idleConnections());
244
console.log('Queued requests:', pool.taskQueueSize());
245

246
// Pool health check
247
if (pool.taskQueueSize() > 10) {
248
  console.warn('High queue size - consider increasing connectionLimit');
249
}
250

251
if (pool.idleConnections() === 0 && pool.activeConnections() === pool.totalConnections()) {
252
  console.warn('Pool at maximum capacity');
253
}
254
```
255

256
### Connection Leak Detection
257

258
Enable connection leak detection to identify connections that aren't properly released.
259

260
```typescript { .api }
261
const pool = mariadb.createPool({
262
  host: "localhost",
263
  user: "root",
264
  password: "password",
265
  database: "test",
266
  connectionLimit: 10,
267
  leakDetectionTimeout: 60000 // Log if connection not released after 60s
268
});
269

270
// This will trigger leak detection if connection.release() is forgotten
271
const connection = await pool.getConnection();
272
// ... perform operations
273
// await connection.release(); // Don't forget this!
274
```
275

276
### Pool Best Practices
277

278
**Proper Connection Management:**
279

280
```typescript
281
// Good: Always release connections
282
const pool = mariadb.createPool(config);
283

284
async function processUser(userId: number) {
285
  const connection = await pool.getConnection();
286
  try {
287
    const user = await connection.query("SELECT * FROM users WHERE id = ?", [userId]);
288
    return user;
289
  } finally {
290
    await connection.release(); // Always release in finally block
291
  }
292
}
293

294
// Better: Use pool methods directly for simple queries
295
async function getUsers() {
296
  return await pool.query("SELECT * FROM users");
297
}
298
```
299

300
**Transaction Handling:**
301

302
```typescript
303
async function transferFunds(fromId: number, toId: number, amount: number) {
304
  const connection = await pool.getConnection();
305
  try {
306
    await connection.beginTransaction();
307
    
308
    await connection.query(
309
      "UPDATE accounts SET balance = balance - ? WHERE id = ?",
310
      [amount, fromId]
311
    );
312
    
313
    await connection.query(
314
      "UPDATE accounts SET balance = balance + ? WHERE id = ?", 
315
      [amount, toId]
316
    );
317
    
318
    await connection.commit();
319
  } catch (error) {
320
    await connection.rollback();
321
    throw error;
322
  } finally {
323
    await connection.release();
324
  }
325
}
326
```