0
# Query Execution
1

2
Comprehensive query execution with support for text queries, prepared statements, parameter binding, and result streaming for optimal performance and security.
3

4
## Capabilities
5

6
### Query Method
7

8
Execute SQL queries with optional parameter binding and callback handling.
9

10
```javascript { .api }
11
/**
12
 * Execute SQL query
13
 * @param sql - SQL query string
14
 * @param values - Optional parameter values for placeholders
15
 * @param callback - Optional callback function
16
 * @returns Query object for chaining or streaming
17
 */
18
query(sql: string, callback?: QueryCallback): Query;
19
query(sql: string, values: any[], callback?: QueryCallback): Query;
20
query(options: QueryOptions, callback?: QueryCallback): Query;
21
```
22

23
**Usage Examples:**
24

25
```javascript
26
const mysql = require('mysql2');
27
const connection = mysql.createConnection(config);
28

29
// Simple query
30
connection.query('SELECT * FROM users', (error, results, fields) => {
31
  if (error) throw error;
32
  console.log(results);
33
});
34

35
// Query with parameters
36
connection.query('SELECT * FROM users WHERE age > ? AND city = ?', [25, 'New York'], (error, results, fields) => {
37
  if (error) throw error;
38
  console.log('Matching users:', results);
39
});
40

41
// Query with named parameters
42
connection.query('SELECT * FROM users WHERE age > :minAge AND city = :city', {
43
  minAge: 25,
44
  city: 'New York'
45
}, (error, results, fields) => {
46
  if (error) throw error;
47
  console.log('Matching users:', results);
48
});
49

50
// Query with options object
51
connection.query({
52
  sql: 'SELECT * FROM users WHERE created_at > ?',
53
  values: [new Date('2023-01-01')],
54
  timeout: 10000,
55
  typeCast: function(field, next) {
56
    if (field.type === 'TINY' && field.length === 1) {
57
      return (field.string() === '1');
58
    }
59
    return next();
60
  }
61
}, (error, results) => {
62
  if (error) throw error;
63
  console.log(results);
64
});
65
```
66

67
### Execute Method
68

69
Execute prepared statements with parameter binding for optimal performance and security.
70

71
```javascript { .api }
72
/**
73
 * Execute prepared statement
74
 * @param sql - SQL statement with placeholders
75
 * @param values - Parameter values for placeholders
76
 * @param callback - Callback function
77
 */
78
execute(sql: string, values?: any[], callback?: ExecuteCallback): void;
79
```
80

81
**Usage Examples:**
82

83
```javascript
84
// Execute prepared statement
85
connection.execute('SELECT * FROM users WHERE id = ?', [123], (error, results, fields) => {
86
  if (error) throw error;
87
  console.log('User:', results[0]);
88
});
89

90
// Insert with prepared statement
91
connection.execute(
92
  'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
93
  ['John Doe', 'john@example.com', 30],
94
  (error, results, fields) => {
95
    if (error) throw error;
96
    console.log('User inserted with ID:', results.insertId);
97
  }
98
);
99

100
// Update with prepared statement
101
connection.execute(
102
  'UPDATE users SET email = ?, updated_at = NOW() WHERE id = ?',
103
  ['newemail@example.com', 123],
104
  (error, results, fields) => {
105
    if (error) throw error;
106
    console.log('Rows affected:', results.affectedRows);
107
  }
108
);
109
```
110

111
### Query Object
112

113
Query object returned by query method providing streaming and event handling capabilities.
114

115
```typescript { .api }
116
interface Query extends EventEmitter {
117
  /** SQL query string */
118
  sql: string;
119
  
120
  /** Parameter values */
121
  values?: any[];
122
  
123
  /** Query timeout */
124
  timeout?: number;
125
  
126
  /** Custom type casting function */
127
  typeCast?: boolean | TypeCastFunction;
128
  
129
  /** Return rows as arrays instead of objects */
130
  rowsAsArray?: boolean;
131
  
132
  /** Nested table handling */
133
  nestTables?: boolean | string;
134
  
135
  /** Stream query results */
136
  stream(options?: StreamOptions): QueryStream;
137
}
138
```
139

140
### Query Options
141

142
Configuration interface for query execution.
143

144
```typescript { .api }
145
interface QueryOptions {
146
  /** SQL query string */
147
  sql: string;
148
  
149
  /** Parameter values for placeholders */
150
  values?: any[];
151
  
152
  /** Query timeout in milliseconds */
153
  timeout?: number;
154
  
155
  /** Custom type casting function */
156
  typeCast?: boolean | TypeCastFunction;
157
  
158
  /** Return rows as arrays instead of objects */
159
  rowsAsArray?: boolean;
160
  
161
  /** Nested table handling */
162
  nestTables?: boolean | string;
163
  
164
  /** Use named placeholders */
165
  namedPlaceholders?: boolean;
166
  
167
  /** Return dates as strings */
168
  dateStrings?: boolean | string[];
169
  
170
  /** Support big numbers */
171
  supportBigNumbers?: boolean;
172
  
173
  /** Return big numbers as strings */
174
  bigNumberStrings?: boolean;
175
  
176
  /** Insert ID as number */
177
  insertIdAsNumber?: boolean;
178
  
179
  /** Decimal numbers handling */
180
  decimalNumbers?: boolean;
181
}
182
```
183

184
### Type Casting
185

186
Custom type casting for converting MySQL types to JavaScript types.
187

188
```javascript { .api }
189
/**
190
 * Type cast function interface
191
 * @param field - Field metadata
192
 * @param next - Default casting function
193
 * @returns Converted value
194
 */
195
type TypeCastFunction = (field: FieldPacket, next: () => any) => any;
196
```
197

198
**Usage Examples:**
199

200
```javascript
201
// Custom type casting
202
const typeCast = function(field, next) {
203
  // Convert TINYINT(1) to boolean
204
  if (field.type === 'TINY' && field.length === 1) {
205
    return (field.string() === '1');
206
  }
207
  
208
  // Convert DECIMAL to number
209
  if (field.type === 'NEWDECIMAL') {
210
    return parseFloat(field.string());
211
  }
212
  
213
  // Convert DATE to custom format
214
  if (field.type === 'DATE') {
215
    return new Date(field.string() + 'T00:00:00.000Z');
216
  }
217
  
218
  // Use default casting for other types
219
  return next();
220
};
221

222
connection.query({
223
  sql: 'SELECT * FROM products',
224
  typeCast: typeCast
225
}, (error, results) => {
226
  // Results with custom type casting applied
227
});
228
```
229

230
### Prepared Statements
231

232
Manual prepared statement management for advanced usage scenarios.
233

234
```javascript { .api }
235
/**
236
 * Create prepared statement
237
 * @param sql - SQL statement with placeholders
238
 * @param callback - Callback with prepared statement
239
 */
240
prepare(sql: string, callback?: (err: Error | null, statement?: PreparedStatement) => void): void;
241

242
/**
243
 * Remove prepared statement from cache
244
 * @param sql - SQL statement to unprepare
245
 */
246
unprepare(sql: string): void;
247
```
248

249
**Usage Examples:**
250

251
```javascript
252
// Prepare statement
253
connection.prepare('SELECT * FROM users WHERE department = ? AND active = ?', (err, statement) => {
254
  if (err) throw err;
255
  
256
  // Execute multiple times with different parameters
257
  statement.execute(['Engineering', true], (error, results) => {
258
    console.log('Engineering users:', results);
259
  });
260
  
261
  statement.execute(['Marketing', true], (error, results) => {
262
    console.log('Marketing users:', results);
263
  });
264
  
265
  // Close prepared statement
266
  statement.close();
267
});
268
```
269

270
### Result Streaming
271

272
Stream large result sets to handle memory efficiently.
273

274
```javascript { .api }
275
/**
276
 * Stream query results
277
 * @param options - Stream options
278
 * @returns Readable stream of rows
279
 */
280
stream(options?: StreamOptions): QueryStream;
281
```
282

283
**Usage Examples:**
284

285
```javascript
286
// Stream large result set
287
const query = connection.query('SELECT * FROM large_table');
288

289
query.stream({ objectMode: true })
290
  .on('result', (row) => {
291
    // Process each row individually
292
    console.log('Processing row:', row.id);
293
    
294
    // Pause stream if needed
295
    connection.pause();
296
    
297
    // Resume after processing
298
    setTimeout(() => {
299
      connection.resume();
300
    }, 100);
301
  })
302
  .on('error', (error) => {
303
    console.error('Stream error:', error);
304
  })
305
  .on('end', () => {
306
    console.log('Stream ended');
307
  });
308
```
309

310
### Transaction Support
311

312
Transaction methods for maintaining data consistency.
313

314
```javascript { .api }
315
/** Begin transaction */
316
beginTransaction(callback?: (err: Error | null) => void): void;
317

318
/** Commit current transaction */
319
commit(callback?: (err: Error | null) => void): void;
320

321
/** Rollback current transaction */
322
rollback(callback?: (err: Error | null) => void): void;
323
```
324

325
**Usage Examples:**
326

327
```javascript
328
// Transaction example
329
connection.beginTransaction((err) => {
330
  if (err) throw err;
331
  
332
  connection.query('INSERT INTO accounts (name, balance) VALUES (?, ?)', ['Alice', 1000], (error, results) => {
333
    if (error) {
334
      return connection.rollback(() => {
335
        throw error;
336
      });
337
    }
338
    
339
    connection.query('INSERT INTO transactions (account_id, amount, type) VALUES (?, ?, ?)', 
340
      [results.insertId, 1000, 'deposit'], (error, results) => {
341
      if (error) {
342
        return connection.rollback(() => {
343
          throw error;
344
        });
345
      }
346
      
347
      connection.commit((err) => {
348
        if (err) {
349
          return connection.rollback(() => {
350
            throw err;
351
          });
352
        }
353
        console.log('Transaction completed successfully!');
354
      });
355
    });
356
  });
357
});
358
```
359

360
## Query Result Types
361

362
### Select Results
363

364
```typescript { .api }
365
interface RowDataPacket {
366
  [column: string]: any;
367
}
368

369
// Results array with metadata
370
type SelectResults = [RowDataPacket[], FieldPacket[]];
371
```
372

373
### Insert/Update/Delete Results
374

375
```typescript { .api }
376
interface OkPacket {
377
  /** Number of rows affected */
378
  affectedRows: number;
379
  
380
  /** Auto-increment ID of inserted row */
381
  insertId: number;
382
  
383
  /** Server status flags */
384
  serverStatus: number;
385
  
386
  /** Warning count */
387
  warningCount: number;
388
  
389
  /** Info message from server */
390
  message: string;
391
  
392
  /** Protocol version */
393
  protocol41: boolean;
394
  
395
  /** Number of changed rows */
396
  changedRows: number;
397
}
398

399
// Results for modification queries
400
type ModificationResults = [OkPacket, FieldPacket[]];
401
```
402

403
### Field Metadata
404

405
```typescript { .api }
406
interface FieldPacket {
407
  /** Field name */
408
  name: string;
409
  
410
  /** Field type */
411
  type: number;
412
  
413
  /** Field length */
414
  length: number;
415
  
416
  /** Database name */
417
  db: string;
418
  
419
  /** Table name */
420
  table: string;
421
  
422
  /** Original table name */
423
  orgTable: string;
424
  
425
  /** Original field name */
426
  orgName: string;
427
  
428
  /** Character set */
429
  charsetNr: number;
430
  
431
  /** Field flags */
432
  flags: number;
433
  
434
  /** Decimal places */
435
  decimals: number;
436
  
437
  /** Default value */
438
  default?: any;
439
  
440
  /** Zero fill flag */
441
  zeroFill: boolean;
442
  
443
  /** Field protocol version */
444
  protocol41: boolean;
445
}
446
```
447

448
## Error Handling
449

450
Query operations can encounter various error conditions:
451

452
```javascript
453
connection.query('SELECT * FROM invalid_table', (error, results, fields) => {
454
  if (error) {
455
    console.error('Error code:', error.code);
456
    console.error('Error number:', error.errno);
457
    console.error('SQL state:', error.sqlState);
458
    console.error('SQL message:', error.sqlMessage);
459
    console.error('Query:', error.sql);
460
    return;
461
  }
462
  
463
  console.log(results);
464
});
465
```
466

467
Common error codes:
468
- `ER_NO_SUCH_TABLE`: Table doesn't exist
469
- `ER_DUP_ENTRY`: Duplicate key violation
470
- `ER_BAD_FIELD_ERROR`: Unknown column
471
- `ER_PARSE_ERROR`: SQL syntax error
472
- `ER_ACCESS_DENIED_ERROR`: Insufficient privileges