0
# Query Execution
1

2
Comprehensive query execution system supporting parameterized queries, streaming results, and event-driven processing. Includes full transaction support, prepared statements, and advanced query options for optimal performance and security.
3

4
## Capabilities
5

6
### Create Query
7

8
Creates a new query object for execution.
9

10
```javascript { .api }
11
/**
12
 * Create a new Query instance
13
 * @param {string} sql - The SQL for the query
14
 * @param {array} [values] - Any values to insert into placeholders in sql
15
 * @param {function} [callback] - The callback to use when query is complete
16
 * @returns {Query} New query object
17
 */
18
function createQuery(sql, values, callback);
19

20
// Query options object
21
interface QueryOptions {
22
  sql: string;                    // SQL query string
23
  values?: any[];                 // Parameter values for placeholders
24
  timeout?: number;               // Query timeout in milliseconds
25
  nestTables?: boolean | string;  // Nest result tables by table name
26
  typeCast?: boolean | function;  // Enable/disable or custom type casting
27
}
28
```
29

30
**Usage Examples:**
31

32
```javascript
33
const mysql = require('mysql');
34

35
// Create query with SQL string
36
const query1 = mysql.createQuery('SELECT * FROM users');
37

38
// Create query with parameters
39
const query2 = mysql.createQuery('SELECT * FROM users WHERE id = ?', [1]);
40

41
// Create query with callback
42
const query3 = mysql.createQuery('SELECT * FROM users', (error, results) => {
43
  if (error) throw error;
44
  console.log(results);
45
});
46

47
// Create query with options object
48
const query4 = mysql.createQuery({
49
  sql: 'SELECT * FROM users WHERE active = ?',
50
  values: [true],
51
  timeout: 30000,
52
  nestTables: true
53
});
54
```
55

56
### Query Execution Methods
57

58
Queries can be executed using connections, pools, or directly on query objects.
59

60
#### Connection Query
61

62
Execute query on a specific connection.
63

64
```javascript { .api }
65
/**
66
 * Executes a SQL query on connection
67
 * @param {string|object} sql - SQL query string or query object
68
 * @param {array} [values] - Parameter values for prepared statements
69
 * @param {function} [callback] - Callback function (err, results, fields)
70
 * @returns {Query} Query object for event-based processing
71
 */
72
connection.query(sql, values, callback);
73
```
74

75
#### Pool Query
76

77
Execute query using pool connection.
78

79
```javascript { .api }
80
/**
81
 * Executes query using pool connection
82
 * @param {string|object} sql - SQL query string or query object
83
 * @param {array} [values] - Parameter values for prepared statements
84
 * @param {function} [callback] - Callback function (err, results, fields)
85
 * @returns {Query} Query object for event-based processing
86
 */
87
pool.query(sql, values, callback);
88
```
89

90
**Basic Query Examples:**
91

92
```javascript
93
// Simple SELECT query
94
connection.query('SELECT * FROM users', (error, results, fields) => {
95
  if (error) throw error;
96
  console.log('Users:', results);
97
  console.log('Field info:', fields);
98
});
99

100
// Parameterized query (prevents SQL injection)
101
connection.query('SELECT * FROM users WHERE id = ? AND active = ?', [userId, true], (error, results) => {
102
  if (error) throw error;
103
  console.log('User:', results[0]);
104
});
105

106
// INSERT query
107
connection.query('INSERT INTO users SET ?', {
108
  name: 'John Doe',
109
  email: 'john@example.com',
110
  active: true
111
}, (error, results) => {
112
  if (error) throw error;
113
  console.log('Inserted user with ID:', results.insertId);
114
});
115

116
// UPDATE query
117
connection.query('UPDATE users SET ? WHERE id = ?', [{name: 'Jane Doe'}, userId], (error, results) => {
118
  if (error) throw error;
119
  console.log('Updated rows:', results.affectedRows);
120
});
121

122
// DELETE query
123
connection.query('DELETE FROM users WHERE id = ?', [userId], (error, results) => {
124
  if (error) throw error;
125
  console.log('Deleted rows:', results.affectedRows);
126
});
127
```
128

129
### Advanced Query Options
130

131
#### Nested Tables
132

133
Organize results by table name when joining multiple tables.
134

135
```javascript { .api }
136
// Query with nested tables
137
connection.query({
138
  sql: 'SELECT u.*, p.* FROM users u LEFT JOIN profiles p ON u.id = p.user_id',
139
  nestTables: true
140
}, (error, results) => {
141
  if (error) throw error;
142
  
143
  results.forEach(row => {
144
    console.log('User:', row.u);      // User table data
145
    console.log('Profile:', row.p);   // Profile table data
146
  });
147
});
148

149
// Nested tables with separator
150
connection.query({
151
  sql: 'SELECT u.*, p.* FROM users u LEFT JOIN profiles p ON u.id = p.user_id',
152
  nestTables: '_'
153
}, (error, results) => {
154
  if (error) throw error;
155
  
156
  results.forEach(row => {
157
    // Fields will be named like: u_id, u_name, p_bio, etc.
158
    console.log(row);
159
  });
160
});
161
```
162

163
#### Type Casting
164

165
Control how MySQL types are converted to JavaScript types.
166

167
```javascript { .api }
168
// Disable type casting (return raw buffer data)
169
connection.query({
170
  sql: 'SELECT * FROM users',
171
  typeCast: false
172
}, (error, results) => {
173
  if (error) throw error;
174
  console.log(results); // Raw buffer data
175
});
176

177
// Custom type casting function
178
connection.query({
179
  sql: 'SELECT * FROM users',
180
  typeCast: function (field, next) {
181
    if (field.type === 'TINY' && field.length === 1) {
182
      return (field.string() === '1'); // Convert TINYINT(1) to boolean
183
    }
184
    if (field.type === 'DATETIME') {
185
      return new Date(field.string()); // Convert to Date object
186
    }
187
    return next(); // Use default type casting
188
  }
189
}, (error, results) => {
190
  if (error) throw error;
191
  console.log(results);
192
});
193
```
194

195
#### Query Timeout
196

197
Set timeout for individual queries.
198

199
```javascript { .api }
200
connection.query({
201
  sql: 'SELECT * FROM large_table',
202
  timeout: 30000 // 30 seconds
203
}, (error, results) => {
204
  if (error) {
205
    if (error.code === 'PROTOCOL_SEQUENCE_TIMEOUT') {
206
      console.error('Query timed out');
207
    }
208
    throw error;
209
  }
210
  console.log(results);
211
});
212
```
213

214
### Event-Driven Query Processing
215

216
Queries return EventEmitter objects for advanced result handling.
217

218
#### Query Events
219

220
```javascript { .api }
221
// Query event handlers
222
const query = connection.query('SELECT * FROM users');
223

224
query.on('result', (row, index) => {
225
  console.log('Row %d:', index, row);
226
});
227

228
query.on('fields', (fields, index) => {
229
  console.log('Fields for result set %d:', index, fields);
230
});
231

232
query.on('error', (err) => {
233
  console.error('Query error:', err);
234
});
235

236
query.on('end', () => {
237
  console.log('Query completed');
238
});
239
```
240

241
**Large Result Set Handling:**
242

243
```javascript
244
const query = connection.query('SELECT * FROM large_table');
245

246
let rowCount = 0;
247

248
query.on('result', (row) => {
249
  // Pause connection to prevent memory overflow
250
  connection.pause();
251
  
252
  rowCount++;
253
  
254
  // Process row asynchronously
255
  processRow(row, () => {
256
    // Resume connection after processing
257
    connection.resume();
258
  });
259
});
260

261
query.on('end', () => {
262
  console.log('Processed %d rows', rowCount);
263
});
264

265
function processRow(row, callback) {
266
  // Simulate async processing
267
  setTimeout(() => {
268
    console.log('Processed row:', row.id);
269
    callback();
270
  }, 10);
271
}
272
```
273

274
### Streaming Query Results
275

276
Convert query results to readable streams for memory-efficient processing.
277

278
```javascript { .api }
279
/**
280
 * Returns query results as a readable stream
281
 * @param {object} [options] - Stream options
282
 * @param {number} [options.highWaterMark] - Stream buffer size (default: 16)
283
 * @param {string} [options.encoding] - Stream encoding (default: null)
284
 * @param {boolean} [options.objectMode] - Object mode (default: true for MySQL streams)
285
 * @returns {ReadableStream} Readable stream of query results
286
 */
287
query.stream(options);
288

289
// Stream options interface
290
interface StreamOptions {
291
  highWaterMark?: number;  // Buffer size for stream backpressure control
292
  encoding?: string;       // Stream encoding (usually null for object streams)
293
  objectMode?: boolean;    // Whether stream operates in object mode
294
}
295
```
296

297
**Streaming Example:**
298

299
```javascript
300
const query = connection.query('SELECT * FROM large_table');
301
const stream = query.stream({highWaterMark: 5});
302

303
stream.on('data', (row) => {
304
  console.log('Stream row:', row);
305
});
306

307
stream.on('end', () => {
308
  console.log('Stream ended');
309
});
310

311
stream.on('error', (err) => {
312
  console.error('Stream error:', err);
313
});
314
```
315

316
### Prepared Statements
317

318
MySQL package supports prepared statements through parameterized queries.
319

320
```javascript { .api }
321
// Prepared statement with single parameter
322
const stmt1 = 'SELECT * FROM users WHERE id = ?';
323
connection.query(stmt1, [userId], callback);
324

325
// Prepared statement with multiple parameters
326
const stmt2 = 'SELECT * FROM users WHERE age > ? AND city = ?';
327
connection.query(stmt2, [25, 'New York'], callback);
328

329
// Prepared statement with object parameters
330
const stmt3 = 'INSERT INTO users SET ?';
331
connection.query(stmt3, [{name: 'John', email: 'john@example.com'}], callback);
332

333
// Prepared statement with mixed parameters
334
const stmt4 = 'UPDATE users SET ? WHERE id = ?';
335
connection.query(stmt4, [{name: 'Jane'}, userId], callback);
336
```
337

338
### Multiple Statements
339

340
Execute multiple SQL statements in a single query (requires `multipleStatements: true`).
341

342
```javascript { .api }
343
// Enable multiple statements in connection config
344
const connection = mysql.createConnection({
345
  host: 'localhost',
346
  user: 'root',
347
  password: 'password',
348
  database: 'mydb',
349
  multipleStatements: true
350
});
351

352
// Execute multiple statements
353
connection.query('SELECT 1; SELECT 2; SELECT 3', (error, results) => {
354
  if (error) throw error;
355
  
356
  console.log('First result:', results[0]);  // SELECT 1 results
357
  console.log('Second result:', results[1]); // SELECT 2 results  
358
  console.log('Third result:', results[2]);  // SELECT 3 results
359
});
360
```
361

362
### Query Properties and Methods
363

364
```javascript { .api }
365
// Query instance properties
366
interface Query extends EventEmitter {
367
  sql: string;           // SQL query string
368
  values: any[];         // Parameter values
369
  
370
  // Methods
371
  stream(options?: StreamOptions): ReadableStream;
372
}
373

374
// Query result structure
375
interface QueryResult {
376
  // For SELECT queries
377
  results?: any[];       // Result rows
378
  fields?: FieldInfo[];  // Field metadata
379
  
380
  // For INSERT queries
381
  insertId?: number;     // Auto-increment ID of inserted row
382
  affectedRows?: number; // Number of affected rows
383
  
384
  // For UPDATE/DELETE queries
385
  affectedRows?: number; // Number of affected rows
386
  changedRows?: number;  // Number of actually changed rows
387
  
388
  // General properties
389
  fieldCount?: number;   // Number of fields
390
  serverStatus?: number; // Server status flags
391
  warningCount?: number; // Number of warnings
392
  message?: string;      // Server message
393
}
394

395
// Field information
396
interface FieldInfo {
397
  catalog: string;       // Catalog name
398
  db: string;           // Database name
399
  table: string;        // Table name
400
  orgTable: string;     // Original table name
401
  name: string;         // Column name
402
  orgName: string;      // Original column name
403
  charsetNr: number;    // Character set number
404
  length: number;       // Column length
405
  type: number;         // Column type (see Types constants)
406
  flags: number;        // Column flags
407
  decimals: number;     // Decimal places
408
  default?: any;        // Default value
409
  zeroFill: boolean;    // Zero fill flag
410
  protocol41: boolean;  // Protocol 4.1 flag
411
}
412
```
413

414
### Transaction Queries
415

416
Queries within transactions follow the same patterns but require proper transaction management.
417

418
```javascript { .api }
419
function performTransaction(callback) {
420
  connection.beginTransaction((err) => {
421
    if (err) return callback(err);
422
    
423
    // First query
424
    connection.query('INSERT INTO users SET ?', {name: 'John'}, (error, results) => {
425
      if (error) {
426
        return connection.rollback(() => {
427
          callback(error);
428
        });
429
      }
430
      
431
      const userId = results.insertId;
432
      
433
      // Second query
434
      connection.query('INSERT INTO profiles SET ?', {user_id: userId, bio: 'Developer'}, (error, results) => {
435
        if (error) {
436
          return connection.rollback(() => {
437
            callback(error);
438
          });
439
        }
440
        
441
        // Commit transaction
442
        connection.commit((err) => {
443
          if (err) {
444
            return connection.rollback(() => {
445
              callback(err);
446
            });
447
          }
448
          
449
          callback(null, {userId, profileId: results.insertId});
450
        });
451
      });
452
    });
453
  });
454
}
455
```
456

457
### Query Best Practices
458

459
1. **Parameterized Queries**: Always use parameterized queries to prevent SQL injection
460
2. **Error Handling**: Implement comprehensive error handling for all query types
461
3. **Connection Management**: Release connections promptly after query completion
462
4. **Large Results**: Use streaming or pagination for large result sets
463
5. **Timeouts**: Set appropriate query timeouts for long-running operations
464
6. **Type Casting**: Use custom type casting for application-specific data transformations
465
7. **Transaction Management**: Always handle transaction rollback scenarios
466
8. **Memory Management**: Pause connections when processing large result sets to prevent memory issues