0
# Promise Integration
1

2
Promise-based wrappers providing modern async/await support for all database operations with full compatibility for contemporary JavaScript development patterns.
3

4
## Capabilities
5

6
### Promise Module Import
7

8
Import the promise-based version of MySQL2 for async/await support.
9

10
```javascript { .api }
11
// CommonJS
12
const mysql = require('mysql2/promise');
13

14
// ES Modules
15
import mysql from 'mysql2/promise';
16
import { createConnection, createPool, createPoolCluster } from 'mysql2/promise';
17
```
18

19
### Promise Connection Creation
20

21
Creates a promise-based database connection that resolves when the connection is established.
22

23
```javascript { .api }
24
/**
25
 * Create promise-based connection
26
 * @param config - Connection configuration or connection string
27
 * @returns Promise resolving to PromiseConnection
28
 */
29
function createConnection(config: ConnectionOptions | string): Promise<PromiseConnection>;
30
```
31

32
**Usage Examples:**
33

34
```javascript
35
const mysql = require('mysql2/promise');
36

37
async function main() {
38
  try {
39
    // Create connection with async/await
40
    const connection = await mysql.createConnection({
41
      host: 'localhost',
42
      user: 'root',
43
      password: 'password',
44
      database: 'testdb'
45
    });
46
    
47
    console.log('Connected to database');
48
    
49
    // Use connection...
50
    await connection.end();
51
  } catch (error) {
52
    console.error('Connection failed:', error);
53
  }
54
}
55

56
// Using connection string
57
async function connectWithUri() {
58
  const connection = await mysql.createConnection('mysql://user:password@localhost:3306/database');
59
  return connection;
60
}
61
```
62

63
### Promise Pool Creation
64

65
Creates a promise-based connection pool for scalable database operations.
66

67
```javascript { .api }
68
/**
69
 * Create promise-based connection pool
70
 * @param config - Pool configuration or connection string
71
 * @returns PromisePool instance
72
 */
73
function createPool(config: PoolOptions | string): PromisePool;
74
```
75

76
**Usage Examples:**
77

78
```javascript
79
// Create promise pool
80
const pool = mysql.createPool({
81
  host: 'localhost',
82
  user: 'root',
83
  password: 'password',
84
  database: 'testdb',
85
  connectionLimit: 10,
86
  waitForConnections: true
87
});
88

89
// Use pool directly with async/await
90
async function queryUsers() {
91
  try {
92
    const [rows, fields] = await pool.execute('SELECT * FROM users WHERE active = ?', [true]);
93
    return rows;
94
  } catch (error) {
95
    console.error('Query failed:', error);
96
    throw error;
97
  }
98
}
99

100
// Get individual connection from pool
101
async function usePoolConnection() {
102
  const connection = await pool.getConnection();
103
  
104
  try {
105
    await connection.beginTransaction();
106
    
107
    const [result] = await connection.execute('INSERT INTO users (name) VALUES (?)', ['John']);
108
    await connection.execute('INSERT INTO logs (user_id, action) VALUES (?, ?)', [result.insertId, 'created']);
109
    
110
    await connection.commit();
111
    console.log('Transaction completed');
112
  } catch (error) {
113
    await connection.rollback();
114
    console.error('Transaction failed:', error);
115
    throw error;
116
  } finally {
117
    connection.release();
118
  }
119
}
120
```
121

122
### Promise Pool Cluster Creation
123

124
Creates a promise-based pool cluster for multi-database operations.
125

126
```javascript { .api }
127
/**
128
 * Create promise-based pool cluster
129
 * @param config - Pool cluster configuration options
130
 * @returns PromisePoolCluster instance
131
 */
132
function createPoolCluster(config?: PoolClusterOptions): PromisePoolCluster;
133
```
134

135
**Usage Examples:**
136

137
```javascript
138
// Create promise-based pool cluster
139
const cluster = mysql.createPoolCluster({
140
  canRetry: true,
141
  removeNodeErrorCount: 5,
142
  restoreNodeTimeout: 50000
143
});
144

145
// Add pools to cluster
146
cluster.add('MASTER', { host: 'master.db.com', user: 'app', password: 'secret', database: 'prod' });
147
cluster.add('SLAVE1', { host: 'slave1.db.com', user: 'app', password: 'secret', database: 'prod' });
148
cluster.add('SLAVE2', { host: 'slave2.db.com', user: 'app', password: 'secret', database: 'prod' });
149

150
// Use cluster with async/await
151
async function readFromSlaves() {
152
  try {
153
    const [rows] = await cluster.execute('SLAVE*', 'SELECT * FROM products ORDER BY created_at DESC LIMIT 10');
154
    return rows;
155
  } catch (error) {
156
    console.error('Cluster query failed:', error);
157
    throw error;
158
  }
159
}
160

161
async function writeToMaster() {
162
  const connection = await cluster.getConnection('MASTER');
163
  try {
164
    const [result] = await connection.execute('INSERT INTO products (name, price) VALUES (?, ?)', ['Widget', 29.99]);
165
    return result.insertId;
166
  } finally {
167
    connection.release();
168
  }
169
}
170
```
171

172
## Promise Connection Interface
173

174
### PromiseConnection Class
175

176
Promise-based connection providing async/await methods for all database operations.
177

178
```typescript { .api }
179
interface PromiseConnection extends EventEmitter {
180
  /** Connection configuration */
181
  config: ConnectionOptions;
182
  
183
  /** Connection thread ID */
184
  threadId: number;
185
  
186
  /** Execute SQL query with promise */
187
  query(sql: string): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
188
  query(sql: string, values: any[]): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
189
  query(options: QueryOptions): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
190
  
191
  /** Execute prepared statement with promise */
192
  execute(sql: string): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader, FieldPacket[]]>;
193
  execute(sql: string, values: any[]): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader, FieldPacket[]]>;
194
  
195
  /** Create prepared statement with promise */
196
  prepare(sql: string): Promise<PreparedStatementInfo>;
197
  
198
  /** Begin transaction with promise */
199
  beginTransaction(): Promise<void>;
200
  
201
  /** Commit transaction with promise */
202
  commit(): Promise<void>;
203
  
204
  /** Rollback transaction with promise */
205
  rollback(): Promise<void>;
206
  
207
  /** Change user with promise */
208
  changeUser(options: ConnectionOptions): Promise<void>;
209
  
210
  /** Ping server with promise */
211
  ping(): Promise<void>;
212
  
213
  /** End connection with promise */
214
  end(): Promise<void>;
215
  
216
  /** Force close connection */
217
  destroy(): void;
218
  
219
  /** Pause connection */
220
  pause(): void;
221
  
222
  /** Resume connection */
223
  resume(): void;
224
  
225
  /** Escape SQL value */
226
  escape(value: any): string;
227
  
228
  /** Escape SQL identifier */
229
  escapeId(value: string | string[]): string;
230
  
231
  /** Format SQL query */
232
  format(sql: string, values?: any[]): string;
233
}
234
```
235

236
### PreparedStatementInfo Interface
237

238
Promise-based prepared statement interface.
239

240
```typescript { .api }
241
interface PreparedStatementInfo {
242
  /** Execute prepared statement with parameters */
243
  execute(parameters?: any | any[] | { [param: string]: any }): Promise<[
244
    RowDataPacket[][] | RowDataPacket[] | OkPacket | OkPacket[] | ResultSetHeader,
245
    FieldPacket[]
246
  ]>;
247
  
248
  /** Close prepared statement */
249
  close(): Promise<void>;
250
}
251
```
252

253
## Promise Pool Interface
254

255
### PromisePool Class
256

257
Promise-based pool providing async connection management and query execution.
258

259
```typescript { .api }
260
interface PromisePool extends EventEmitter {
261
  /** Get connection from pool with promise */
262
  getConnection(): Promise<PromisePoolConnection>;
263
  
264
  /** Release connection back to pool */
265
  releaseConnection(connection: PromisePoolConnection): void;
266
  
267
  /** Execute query on pool with promise */
268
  query(sql: string): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
269
  query(sql: string, values: any[]): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
270
  
271
  /** Execute prepared statement on pool with promise */
272
  execute(sql: string): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader, FieldPacket[]]>;
273
  execute(sql: string, values: any[]): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader, FieldPacket[]]>;
274
  
275
  /** Close pool with promise */
276
  end(): Promise<void>;
277
  
278
  /** Escape SQL value */
279
  escape(value: any): string;
280
  
281
  /** Escape SQL identifier */
282
  escapeId(value: string | string[]): string;
283
  
284
  /** Format SQL query */
285
  format(sql: string, values?: any[]): string;
286
  
287
  /** Underlying pool object */
288
  pool: Pool;
289
}
290
```
291

292
### PromisePoolConnection Interface
293

294
Promise-based pool connection with release method.
295

296
```typescript { .api }
297
interface PromisePoolConnection extends PromiseConnection {
298
  /** Release connection back to pool */
299
  release(): void;
300
  
301
  /** Underlying connection object */
302
  connection: PromiseConnection;
303
}
304
```
305

306
## Promise Pool Cluster Interface
307

308
### PromisePoolCluster Class
309

310
Promise-based pool cluster for multi-database operations.
311

312
```typescript { .api }
313
interface PromisePoolCluster extends EventEmitter {
314
  /** Cluster configuration */
315
  config: PoolClusterOptions;
316
  
317
  /** Add pool to cluster */
318
  add(config: PoolOptions): void;
319
  add(group: string, config: PoolOptions): void;
320
  add(group: string, connectionUri: string): void;
321
  
322
  /** Remove pool from cluster */
323
  remove(pattern?: string): void;
324
  
325
  /** Get connection from cluster with promise */
326
  getConnection(): Promise<PromisePoolConnection>;
327
  getConnection(group: string): Promise<PromisePoolConnection>;
328
  getConnection(group: string, selector: string): Promise<PromisePoolConnection>;
329
  
330
  /** Get pool namespace for pattern */
331
  of(pattern: string, selector?: string): PromisePoolNamespace;
332
  
333
  /** Execute query on cluster with promise */
334
  query(sql: string): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
335
  query(sql: string, values: any[]): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
336
  
337
  /** Execute prepared statement on cluster with promise */
338
  execute(sql: string): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader, FieldPacket[]]>;
339
  execute(sql: string, values: any[]): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader, FieldPacket[]]>;
340
  
341
  /** Close cluster with promise */
342
  end(): Promise<void>;
343
}
344
```
345

346
### PromisePoolNamespace Interface
347

348
Promise-based pool namespace for pattern-based operations.
349

350
```typescript { .api }
351
interface PromisePoolNamespace {
352
  /** Get connection from namespace with promise */
353
  getConnection(): Promise<PromisePoolConnection>;
354
  
355
  /** Execute query on namespace with promise */
356
  query(sql: string): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
357
  query(sql: string, values: any[]): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
358
  
359
  /** Execute prepared statement on namespace with promise */
360
  execute(sql: string): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader, FieldPacket[]]>;
361
  execute(sql: string, values: any[]): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader, FieldPacket[]]>;
362
}
363
```
364

365
## Advanced Promise Patterns
366

367
### Connection Pooling with Async/Await
368

369
```javascript
370
const mysql = require('mysql2/promise');
371

372
class DatabaseService {
373
  constructor() {
374
    this.pool = mysql.createPool({
375
      host: process.env.DB_HOST,
376
      user: process.env.DB_USER,
377
      password: process.env.DB_PASSWORD,
378
      database: process.env.DB_NAME,
379
      connectionLimit: 10,
380
      acquireTimeout: 30000,
381
      waitForConnections: true
382
    });
383
  }
384
  
385
  async getUser(id) {
386
    const [rows] = await this.pool.execute('SELECT * FROM users WHERE id = ?', [id]);
387
    return rows[0];
388
  }
389
  
390
  async createUser(userData) {
391
    const [result] = await this.pool.execute(
392
      'INSERT INTO users (name, email, created_at) VALUES (?, ?, NOW())',
393
      [userData.name, userData.email]
394
    );
395
    return result.insertId;
396
  }
397
  
398
  async updateUser(id, updates) {
399
    const fields = Object.keys(updates).map(key => `${key} = ?`).join(', ');
400
    const values = [...Object.values(updates), id];
401
    
402
    const [result] = await this.pool.execute(
403
      `UPDATE users SET ${fields} WHERE id = ?`,
404
      values
405
    );
406
    return result.affectedRows > 0;
407
  }
408
  
409
  async close() {
410
    await this.pool.end();
411
  }
412
}
413
```
414

415
### Transaction Management with Promises
416

417
```javascript
418
async function transferFunds(fromAccountId, toAccountId, amount) {
419
  const connection = await pool.getConnection();
420
  
421
  try {
422
    await connection.beginTransaction();
423
    
424
    // Check source account balance
425
    const [fromAccount] = await connection.execute(
426
      'SELECT balance FROM accounts WHERE id = ? FOR UPDATE',
427
      [fromAccountId]
428
    );
429
    
430
    if (fromAccount[0].balance < amount) {
431
      throw new Error('Insufficient funds');
432
    }
433
    
434
    // Debit source account
435
    await connection.execute(
436
      'UPDATE accounts SET balance = balance - ? WHERE id = ?',
437
      [amount, fromAccountId]
438
    );
439
    
440
    // Credit destination account
441
    await connection.execute(
442
      'UPDATE accounts SET balance = balance + ? WHERE id = ?',
443
      [amount, toAccountId]
444
    );
445
    
446
    // Record transaction
447
    await connection.execute(
448
      'INSERT INTO transactions (from_account, to_account, amount, created_at) VALUES (?, ?, ?, NOW())',
449
      [fromAccountId, toAccountId, amount]
450
    );
451
    
452
    await connection.commit();
453
    console.log('Transfer completed successfully');
454
    
455
  } catch (error) {
456
    await connection.rollback();
457
    console.error('Transfer failed:', error.message);
458
    throw error;
459
  } finally {
460
    connection.release();
461
  }
462
}
463
```
464

465
### Prepared Statements with Promises
466

467
```javascript
468
async function optimizedUserQueries() {
469
  const connection = await mysql.createConnection(config);
470
  
471
  try {
472
    // Prepare statement once
473
    const getUserStatement = await connection.prepare('SELECT * FROM users WHERE department = ? AND active = ?');
474
    
475
    // Execute multiple times with different parameters
476
    const [engineering] = await getUserStatement.execute(['Engineering', true]);
477
    const [marketing] = await getUserStatement.execute(['Marketing', true]);
478
    const [sales] = await getUserStatement.execute(['Sales', true]);
479
    
480
    console.log('Engineering users:', engineering.length);
481
    console.log('Marketing users:', marketing.length);
482
    console.log('Sales users:', sales.length);
483
    
484
    // Close prepared statement
485
    await getUserStatement.close();
486
    
487
  } finally {
488
    await connection.end();
489
  }
490
}
491
```
492

493
### Error Handling with Async/Await
494

495
```javascript
496
async function robustDatabaseOperation() {
497
  let connection;
498
  
499
  try {
500
    connection = await mysql.createConnection(config);
501
    
502
    const [results] = await connection.execute('SELECT * FROM users WHERE active = ?', [true]);
503
    
504
    return results.map(user => ({
505
      id: user.id,
506
      name: user.name,
507
      email: user.email
508
    }));
509
    
510
  } catch (error) {
511
    if (error.code === 'ER_NO_SUCH_TABLE') {
512
      console.error('Table does not exist');
513
    } else if (error.code === 'ECONNREFUSED') {
514
      console.error('Database connection refused');
515
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
516
      console.error('Database access denied');
517
    } else {
518
      console.error('Unexpected database error:', error);
519
    }
520
    
521
    throw error;
522
  } finally {
523
    if (connection) {
524
      await connection.end();
525
    }
526
  }
527
}
528
```
529

530
## Utility Functions
531

532
The promise module includes the same utility functions as the callback module:
533

534
```javascript { .api }
535
/** Escape SQL value */
536
const escape = mysql.escape;
537

538
/** Escape SQL identifier */
539
const escapeId = mysql.escapeId;
540

541
/** Format SQL query */
542
const format = mysql.format;
543

544
/** Create raw SQL object */
545
const raw = mysql.raw;
546

547
/** Set parser cache size */
548
const setMaxParserCache = mysql.setMaxParserCache;
549

550
/** Clear parser cache */
551
const clearParserCache = mysql.clearParserCache;
552
```
553

554
## Constants
555

556
All constants are available in the promise module:
557

558
```javascript { .api }
559
/** MySQL data types */
560
const Types = mysql.Types;
561

562
/** MySQL charsets */
563
const Charsets = mysql.Charsets;
564

565
/** Charset to encoding mappings */
566
const CharsetToEncoding = mysql.CharsetToEncoding;
567
```