0
# Connection Management
1

2
Core database connection functionality providing direct MySQL server connectivity with full lifecycle management, transaction support, and event-driven architecture.
3

4
## Capabilities
5

6
### Create Connection
7

8
Creates a new MySQL connection instance with specified configuration.
9

10
```javascript { .api }
11
/**
12
 * Create a new Connection instance
13
 * @param {object|string} config - Configuration or connection string for new MySQL connection
14
 * @returns {Connection} A new MySQL connection
15
 */
16
function createConnection(config);
17

18
// Connection configuration options
19
interface ConnectionConfig {
20
  host?: string;           // MySQL server hostname (default: 'localhost')
21
  port?: number;           // MySQL server port (default: 3306)
22
  localAddress?: string;   // Local interface to bind for network connections
23
  socketPath?: string;     // Path to Unix socket file (alternative to host/port)
24
  user?: string;           // MySQL username
25
  password?: string;       // MySQL password
26
  database?: string;       // Database name to connect to
27
  charset?: string;        // Connection charset (default: 'UTF8_GENERAL_CI')
28
  charsetNumber?: number;  // Character set number (internal)
29
  timezone?: string;       // Timezone for date/time values (default: 'local')
30
  connectTimeout?: number; // Connection timeout in milliseconds (default: 10000)
31
  acquireTimeout?: number; // Timeout for acquiring connection (default: 10000)
32
  timeout?: number;        // Query timeout in milliseconds (default: 0 - no timeout)
33
  reconnect?: boolean;     // Automatically reconnect when connection is lost (default: true)
34
  ssl?: boolean | object;  // SSL configuration or boolean to enable SSL
35
  debug?: boolean | string[]; // Enable debug logging
36
  trace?: boolean;         // Generate stack traces on errors (default: true)
37
  insecureAuth?: boolean;  // Allow old authentication method (default: false)
38
  supportBigNumbers?: boolean;  // Support big numbers (more than 2^53) (default: false)
39
  bigNumberStrings?: boolean;   // Force big numbers to be returned as strings (default: false)
40
  dateStrings?: boolean;        // Force date types to be returned as strings (default: false)
41
  stringifyObjects?: boolean;   // Stringify objects instead of converting to values (default: false)
42
  multipleStatements?: boolean; // Allow multiple SQL statements per query (default: false)
43
  localInfile?: boolean;        // Allow LOAD DATA LOCAL INFILE (default: true)
44
  typeCast?: boolean | function; // Cast field types on receipt (default: true)
45
  queryFormat?: function;       // Custom query format function
46
  flags?: string;               // Connection flags
47
  clientFlags?: number;         // Client capability flags (bitwise OR of CLIENT_* constants)
48
  maxPacketSize?: number;       // Maximum packet size in bytes (default: 0 - use server default)
49
  acquireTimeout?: number;      // Timeout for acquiring connection in milliseconds (default: 10000)
50
  timeout?: number;             // Timeout for all queries in milliseconds (default: 0 - no timeout)
51
  pingInterval?: number;        // Interval for automatic pings in milliseconds (default: 0 - disabled)
52
}
53
```
54

55
**Usage Examples:**
56

57
```javascript
58
const mysql = require('mysql');
59

60
// Basic connection
61
const connection = mysql.createConnection({
62
  host: 'localhost',
63
  user: 'root',
64
  password: 'password',
65
  database: 'myapp'
66
});
67

68
// Connection with SSL
69
const secureConnection = mysql.createConnection({
70
  host: 'mysql.example.com',
71
  user: 'app_user',
72
  password: 'secure_password',
73
  database: 'production_db',
74
  ssl: {
75
    ca: fs.readFileSync('./server-ca.pem'),
76
    cert: fs.readFileSync('./client-cert.pem'),
77
    key: fs.readFileSync('./client-key.pem')
78
  }
79
});
80

81
// Connection string format
82
const connection2 = mysql.createConnection('mysql://user:password@host:port/database');
83

84
// Advanced connection with extended options
85
const advancedConnection = mysql.createConnection({
86
  host: 'mysql.example.com',
87
  user: 'app_user',
88
  password: 'secure_password',
89
  database: 'production_db',
90
  supportBigNumbers: true,
91
  bigNumberStrings: true,
92
  dateStrings: false,
93
  timezone: 'UTC',
94
  typeCast: function (field, next) {
95
    if (field.type === 'TINY' && field.length === 1) {
96
      return (field.string() === '1'); // Convert TINYINT(1) to boolean
97
    }
98
    return next();
99
  },
100
  localInfile: false,
101
  stringifyObjects: false,
102
  trace: true
103
});
104
```
105

106
### Connection Methods
107

108
#### Connect
109

110
Establishes the connection to the MySQL server.
111

112
```javascript { .api }
113
/**
114
 * Establishes connection to MySQL server
115
 * @param {object} [options] - Connection options
116
 * @param {function} [callback] - Callback function (err)
117
 */
118
connection.connect(options, callback);
119
```
120

121
#### Change User
122

123
Changes the user for the current connection.
124

125
```javascript { .api }
126
/**
127
 * Changes the user for the connection
128
 * @param {object} options - User change options
129
 * @param {string} options.user - New username
130
 * @param {string} options.password - New password
131
 * @param {string} [options.charset] - Connection charset
132
 * @param {string} [options.database] - Database to switch to
133
 * @param {function} [callback] - Callback function (err)
134
 */
135
connection.changeUser(options, callback);
136
```
137

138
#### Query
139

140
Executes a SQL query on the connection.
141

142
```javascript { .api }
143
/**
144
 * Executes a SQL query
145
 * @param {string|object} sql - SQL query string or query object
146
 * @param {array} [values] - Parameter values for prepared statements
147
 * @param {function} [callback] - Callback function (err, results, fields)
148
 * @returns {Query} Query object for event-based processing
149
 */
150
connection.query(sql, values, callback);
151
```
152

153
**Usage Examples:**
154

155
```javascript
156
// Simple query with callback
157
connection.query('SELECT * FROM users', (error, results, fields) => {
158
  if (error) throw error;
159
  console.log(results);
160
});
161

162
// Parameterized query (prevents SQL injection)
163
connection.query('SELECT * FROM users WHERE id = ?', [userId], (error, results) => {
164
  if (error) throw error;
165
  console.log(results[0]);
166
});
167

168
// Event-based query handling
169
const query = connection.query('SELECT * FROM large_table');
170
query.on('result', (row) => {
171
  console.log(row);
172
});
173
query.on('end', () => {
174
  console.log('Query completed');
175
});
176
```
177

178
### Transaction Support
179

180
#### Begin Transaction
181

182
Starts a new transaction on the connection.
183

184
```javascript { .api }
185
/**
186
 * Starts a transaction
187
 * @param {object} [options] - Transaction options
188
 * @param {function} [callback] - Callback function (err)
189
 */
190
connection.beginTransaction(options, callback);
191
```
192

193
#### Commit
194

195
Commits the current transaction.
196

197
```javascript { .api }
198
/**
199
 * Commits current transaction
200
 * @param {object} [options] - Commit options
201
 * @param {function} [callback] - Callback function (err)
202
 */
203
connection.commit(options, callback);
204
```
205

206
#### Rollback
207

208
Rolls back the current transaction.
209

210
```javascript { .api }
211
/**
212
 * Rolls back current transaction
213
 * @param {object} [options] - Rollback options
214
 * @param {function} [callback] - Callback function (err)
215
 */
216
connection.rollback(options, callback);
217
```
218

219
**Transaction Usage Example:**
220

221
```javascript
222
connection.beginTransaction((err) => {
223
  if (err) throw err;
224
  
225
  connection.query('INSERT INTO users SET ?', {name: 'John', email: 'john@example.com'}, (error, results) => {
226
    if (error) {
227
      return connection.rollback(() => {
228
        throw error;
229
      });
230
    }
231
    
232
    connection.query('INSERT INTO profiles SET ?', {user_id: results.insertId, bio: 'Developer'}, (error, results) => {
233
      if (error) {
234
        return connection.rollback(() => {
235
          throw error;
236
        });
237
      }
238
      
239
      connection.commit((err) => {
240
        if (err) {
241
          return connection.rollback(() => {
242
            throw err;
243
          });
244
        }
245
        console.log('Transaction completed!');
246
      });
247
    });
248
  });
249
});
250
```
251

252
### Connection Lifecycle
253

254
#### End Connection
255

256
Gracefully closes the connection.
257

258
```javascript { .api }
259
/**
260
 * Gracefully closes connection
261
 * @param {object} [options] - End options
262
 * @param {function} [callback] - Callback function (err)
263
 */
264
connection.end(options, callback);
265
```
266

267
#### Destroy Connection
268

269
Forcefully closes the connection.
270

271
```javascript { .api }
272
/**
273
 * Forcefully closes connection
274
 */
275
connection.destroy();
276
```
277

278
#### Pause/Resume
279

280
Controls the flow of data from the connection. Useful for managing memory usage when processing large result sets.
281

282
```javascript { .api }
283
/**
284
 * Pauses the connection (stops reading from socket)
285
 */
286
connection.pause();
287

288
/**
289
 * Resumes the connection (restarts reading from socket)
290
 */
291
connection.resume();
292
```
293

294
**Usage Example:**
295

296
```javascript
297
const query = connection.query('SELECT * FROM large_table');
298

299
query.on('result', (row) => {
300
  // Pause to prevent memory overflow
301
  connection.pause();
302
  
303
  // Process row asynchronously
304
  processRowAsync(row, () => {
305
    // Resume after processing
306
    connection.resume();
307
  });
308
});
309
```
310

311
### Server Operations
312

313
#### Ping
314

315
Pings the MySQL server to keep the connection alive.
316

317
```javascript { .api }
318
/**
319
 * Pings the MySQL server
320
 * @param {object} [options] - Ping options
321
 * @param {function} [callback] - Callback function (err)
322
 */
323
connection.ping(options, callback);
324
```
325

326
#### Statistics
327

328
Gets server statistics from the MySQL server.
329

330
```javascript { .api }
331
/**
332
 * Gets server statistics
333
 * @param {object} [options] - Statistics options
334
 * @param {function} [callback] - Callback function (err, stats)
335
 */
336
connection.statistics(options, callback);
337
```
338

339
**Usage Example:**
340

341
```javascript
342
connection.statistics((error, stats) => {
343
  if (error) throw error;
344
  console.log('Server statistics:', stats);
345
  // Stats is a string containing various server metrics
346
});
347
```
348

349
### Utility Methods
350

351
#### Escape
352

353
Escapes a value for safe SQL usage.
354

355
```javascript { .api }
356
/**
357
 * Escapes a value for SQL
358
 * @param {*} value - The value to escape
359
 * @returns {string} Escaped string value
360
 */
361
connection.escape(value);
362
```
363

364
#### Escape Identifier
365

366
Escapes an identifier for SQL.
367

368
```javascript { .api }
369
/**
370
 * Escapes an identifier for SQL
371
 * @param {*} value - The value to escape
372
 * @returns {string} Escaped identifier
373
 */
374
connection.escapeId(value);
375
```
376

377
#### Format
378

379
Formats SQL with replacement values.
380

381
```javascript { .api }
382
/**
383
 * Formats SQL with values
384
 * @param {string} sql - SQL string with placeholders
385
 * @param {array} values - Values to insert
386
 * @returns {string} Formatted SQL string
387
 */
388
connection.format(sql, values);
389
```
390

391
### Connection Properties
392

393
```javascript { .api }
394
// Connection instance properties
395
interface Connection {
396
  config: ConnectionConfig;    // Connection configuration
397
  state: string;              // Connection state: 'disconnected', 'connected', 'authenticated', 'protocol_error'
398
  threadId: number | null;    // MySQL thread ID for this connection
399
}
400
```
401

402
### Connection Events
403

404
#### Connect Event
405

406
Fired when connection is established.
407

408
```javascript { .api }
409
connection.on('connect', () => {
410
  console.log('Connected to MySQL server');
411
});
412
```
413

414
#### Error Event
415

416
Fired on connection errors.
417

418
```javascript { .api }
419
connection.on('error', (err) => {
420
  console.error('Connection error:', err);
421
  if (err.code === 'PROTOCOL_CONNECTION_LOST') {
422
    // Handle connection lost
423
  }
424
});
425
```
426

427
#### End Event
428

429
Fired when connection ends.
430

431
```javascript { .api }
432
connection.on('end', (err) => {
433
  console.log('Connection ended');
434
});
435
```
436

437
#### Drain Event
438

439
Fired when write buffer is drained.
440

441
```javascript { .api }
442
connection.on('drain', () => {
443
  console.log('Write buffer drained');
444
});
445
```
446

447
#### Enqueue Event
448

449
Fired when a sequence is enqueued.
450

451
```javascript { .api }
452
connection.on('enqueue', (sequence) => {
453
  console.log('Sequence enqueued:', sequence);
454
});
455
```
456

457
**Complete Connection Example:**
458

459
```javascript
460
const mysql = require('mysql');
461

462
const connection = mysql.createConnection({
463
  host: 'localhost',
464
  user: 'root',
465
  password: 'password',
466
  database: 'myapp',
467
  reconnect: true
468
});
469

470
// Set up event handlers
471
connection.on('connect', () => {
472
  console.log('Connected to MySQL server');
473
});
474

475
connection.on('error', (err) => {
476
  console.error('Database error:', err);
477
  if (err.code === 'PROTOCOL_CONNECTION_LOST') {
478
    console.log('Reconnecting...');
479
  }
480
});
481

482
// Connect and use
483
connection.connect((err) => {
484
  if (err) {
485
    console.error('Error connecting:', err);
486
    return;
487
  }
488
  
489
  // Perform queries...
490
  connection.query('SELECT NOW() as now', (error, results) => {
491
    if (error) throw error;
492
    console.log('Server time:', results[0].now);
493
    
494
    connection.end();
495
  });
496
});
497
```