0
# Query Execution
1

2
The Query class and query execution system provides comprehensive SQL query handling including parameter binding, prepared statements, result processing, and streaming capabilities.
3

4
## Capabilities
5

6
### Query Constructor
7

8
Creates a new query instance with configuration, parameters, and callback.
9

10
```javascript { .api }
11
/**
12
 * Creates a new query instance
13
 * @param config - Query configuration object or SQL text
14
 * @param values - Optional parameter values
15
 * @param callback - Optional callback for query completion
16
 */
17
class Query extends EventEmitter {
18
  constructor(config: QueryConfig | string, values?: any[], callback?: QueryCallback);
19
}
20

21
interface QueryConfig {
22
  /** SQL query text */
23
  text: string;
24
  /** Parameter values for query */
25
  values?: any[];
26
  /** Named prepared statement identifier */
27
  name?: string;
28
  /** Row output mode ('array' or 'object') */
29
  rowMode?: 'array' | 'object';
30
  /** Custom type overrides */
31
  types?: TypeOverrides;
32
  /** Binary result format */
33
  binary?: boolean;
34
  /** Portal name for prepared statements */
35
  portal?: string;
36
  /** Maximum rows to return per execute */
37
  rows?: number;
38
  /** Callback function for query completion */
39
  callback?: QueryCallback;
40
  /** Query execution mode */
41
  queryMode?: 'simple' | 'extended';
42
}
43

44
type QueryCallback = (err: Error | null, result: QueryResult) => void;
45
```
46

47
**Usage Examples:**
48

49
```javascript
50
const { Query } = require('pg');
51

52
// Basic query
53
const query1 = new Query('SELECT * FROM users');
54

55
// Parameterized query
56
const query2 = new Query('SELECT * FROM users WHERE id = $1', [123]);
57

58
// Named prepared statement
59
const query3 = new Query({
60
  name: 'get-user',
61
  text: 'SELECT * FROM users WHERE id = $1',
62
  values: [123]
63
});
64

65
// Query with callback
66
const query4 = new Query('SELECT NOW()', [], (err, result) => {
67
  if (err) throw err;
68
  console.log(result.rows[0]);
69
});
70

71
// Array row mode
72
const query5 = new Query({
73
  text: 'SELECT name, email FROM users',
74
  rowMode: 'array'
75
});
76
```
77

78
### Query Result Structure
79

80
Query results contain metadata and data from executed SQL statements.
81

82
```javascript { .api }
83
interface QueryResult {
84
  /** SQL command that was executed */
85
  command: string;
86
  /** Number of rows affected by the command */
87
  rowCount: number;
88
  /** Object ID returned by INSERT operations */
89
  oid: number;
90
  /** Array of result rows */
91
  rows: any[];
92
  /** Field definitions for result columns */
93
  fields: FieldDef[];
94
  /** Row constructor function (when using custom row types) */
95
  _parsers?: any[];
96
  /** Query duration in milliseconds */
97
  duration?: number;
98
}
99

100
interface FieldDef {
101
  /** Column name */
102
  name: string;
103
  /** Table OID */
104
  tableID: number;
105
  /** Column attribute number */
106
  columnID: number;
107
  /** Data type OID */
108
  dataTypeID: number;
109
  /** Data type size */
110
  dataTypeSize: number;
111
  /** Type modifier */
112
  dataTypeModifier: number;
113
  /** Result format ('text' or 'binary') */
114
  format: string;
115
}
116
```
117

118
**Usage Examples:**
119

120
```javascript
121
const result = await client.query('SELECT id, name FROM users WHERE active = true');
122

123
console.log('Command:', result.command);     // 'SELECT'
124
console.log('Row count:', result.rowCount);  // Number of rows returned
125
console.log('Rows:', result.rows);           // [{ id: 1, name: 'Alice' }, ...]
126

127
// Field information
128
result.fields.forEach(field => {
129
  console.log(`Column: ${field.name}, Type: ${field.dataTypeID}`);
130
});
131

132
// INSERT result
133
const insertResult = await client.query('INSERT INTO users (name) VALUES ($1) RETURNING id', ['Bob']);
134
console.log('New ID:', insertResult.rows[0].id);
135
console.log('OID:', insertResult.oid);
136
```
137

138
### Prepared Statements
139

140
Named prepared statements for query plan caching and performance optimization.
141

142
```javascript { .api }
143
/**
144
 * Execute a named prepared statement
145
 * Automatically prepared on first execution and cached thereafter
146
 */
147
interface PreparedStatement {
148
  name: string;
149
  text: string;
150
  values?: any[];
151
}
152
```
153

154
**Usage Examples:**
155

156
```javascript
157
// Define prepared statement
158
const getUserQuery = {
159
  name: 'get-user-by-id',
160
  text: 'SELECT * FROM users WHERE id = $1'
161
};
162

163
// First execution: prepares and executes
164
const user1 = await client.query(getUserQuery, [123]);
165

166
// Subsequent executions: uses cached plan
167
const user2 = await client.query(getUserQuery, [456]);
168

169
// Prepared statement with multiple parameters
170
const complexQuery = {
171
  name: 'user-search',
172
  text: 'SELECT * FROM users WHERE age BETWEEN $1 AND $2 AND city = $3 ORDER BY name',
173
  values: [25, 65, 'San Francisco']
174
};
175

176
const results = await client.query(complexQuery);
177
```
178

179
### Parameter Binding
180

181
Safe parameter binding to prevent SQL injection and handle type conversion.
182

183
```javascript { .api }
184
/**
185
 * Parameter placeholder format: $1, $2, $3, etc.
186
 * Values are automatically escaped and type-converted
187
 */
188
```
189

190
**Usage Examples:**
191

192
```javascript
193
// Basic parameter binding
194
await client.query('SELECT * FROM users WHERE name = $1', ['Alice']);
195

196
// Multiple parameters
197
await client.query(
198
  'SELECT * FROM orders WHERE user_id = $1 AND created_at > $2 AND amount >= $3',
199
  [123, new Date('2023-01-01'), 100.00]
200
);
201

202
// Array values (PostgreSQL arrays)
203
await client.query('SELECT * FROM products WHERE tags && $1', [['electronics', 'mobile']]);
204

205
// JSON parameters
206
await client.query('INSERT INTO logs (data) VALUES ($1)', [{ event: 'login', user: 123 }]);
207

208
// Buffer/binary data
209
const imageBuffer = Buffer.from('...image data...');
210
await client.query('INSERT INTO images (data) VALUES ($1)', [imageBuffer]);
211

212
// Date/time parameters
213
await client.query('INSERT INTO events (scheduled_at) VALUES ($1)', [new Date()]);
214
```
215

216
### Row Modes
217

218
Control how query results are returned - as objects or arrays.
219

220
```javascript { .api }
221
/**
222
 * Row mode configuration affects result structure
223
 * - 'object': rows as objects with column names as keys (default)
224
 * - 'array': rows as arrays with values in column order
225
 */
226
```
227

228
**Usage Examples:**
229

230
```javascript
231
// Default object mode
232
const objectResult = await client.query('SELECT id, name, email FROM users LIMIT 1');
233
console.log(objectResult.rows[0]);
234
// { id: 1, name: 'Alice', email: 'alice@example.com' }
235

236
// Array mode
237
const arrayResult = await client.query({
238
  text: 'SELECT id, name, email FROM users LIMIT 1',
239
  rowMode: 'array'
240
});
241
console.log(arrayResult.rows[0]);
242
// [1, 'Alice', 'alice@example.com']
243

244
// Array mode useful for performance-critical operations
245
const performanceQuery = {
246
  text: 'SELECT * FROM large_table',
247
  rowMode: 'array'
248
};
249
const results = await client.query(performanceQuery);
250
// Less memory overhead, faster processing
251
```
252

253
### Portal-Based Query Execution
254

255
Execute queries with portal-based paging to process large result sets efficiently.
256

257
```javascript { .api }
258
/**
259
 * Portal-based query execution for large result sets
260
 * Fetches results in batches rather than all at once
261
 */
262
interface PortalConfig extends QueryConfig {
263
  /** Number of rows to fetch per portal execution */
264
  rows: number;
265
  /** Portal name for prepared statements */
266
  portal?: string;
267
}
268
```
269

270
**Usage Examples:**
271

272
```javascript
273
// Portal-based execution (fetches in batches)
274
const query = new Query({
275
  text: 'SELECT * FROM large_table',
276
  rows: 1000  // Fetch 1000 rows at a time
277
});
278

279
query.on('row', (row) => {
280
  // Process each row individually
281
  console.log('Processing row:', row.id);
282
});
283

284
query.on('end', (result) => {
285
  console.log('Query completed, total rows:', result.rowCount);
286
});
287

288
query.on('error', (err) => {
289
  console.error('Query error:', err);
290
});
291

292
client.query(query);
293

294
// For true streaming, use the separate pg-query-stream package
295
// const QueryStream = require('pg-query-stream');
296
// const queryStream = new QueryStream('SELECT * FROM huge_table');
297
// const stream = client.query(queryStream);
298
```
299

300
### Query Events
301

302
Query instances emit events during execution lifecycle.
303

304
```javascript { .api }
305
// Query execution events
306
query.on('row', (row, result) => {
307
  // Emitted for each row (when using streaming)
308
});
309

310
query.on('end', (result) => {
311
  // Query completed successfully
312
});
313

314
query.on('error', (err) => {
315
  // Query failed with error
316
});
317
```
318

319
**Usage Examples:**
320

321
```javascript
322
const query = new Query('SELECT * FROM users');
323

324
query.on('row', (row) => {
325
  console.log('User:', row.name);
326
});
327

328
query.on('end', (result) => {
329
  console.log(`Query completed: ${result.rowCount} rows`);
330
});
331

332
query.on('error', (err) => {
333
  console.error('Query failed:', err.message);
334
});
335

336
client.query(query);
337
```
338

339
### Query Execution Modes
340

341
Control how queries are executed on the PostgreSQL server.
342

343
```javascript { .api }
344
interface QueryModes {
345
  /** Simple query protocol (default for ad-hoc queries) */
346
  simple: 'simple';
347
  /** Extended query protocol (required for prepared statements) */
348
  extended: 'extended';
349
}
350
```
351

352
**Usage Examples:**
353

354
```javascript
355
// Simple protocol (default)
356
await client.query('SELECT * FROM users');
357

358
// Force extended protocol
359
await client.query({
360
  text: 'SELECT * FROM users WHERE id = $1',
361
  values: [123],
362
  queryMode: 'extended'
363
});
364

365
// Extended protocol automatically used for:
366
// - Named statements
367
// - Binary mode
368
// - Custom row limits
369
const extendedQuery = {
370
  name: 'get-users',
371
  text: 'SELECT * FROM users',
372
  binary: true,
373
  rows: 100
374
};
375
```
376

377
### Query Validation
378

379
Built-in validation and error handling for queries.
380

381
```javascript { .api }
382
/**
383
 * Query validation occurs before execution
384
 * Common validations:
385
 * - Text cannot be empty
386
 * - Parameter count must match placeholders  
387
 * - Named queries must have valid names
388
 */
389
```
390

391
**Usage Examples:**
392

393
```javascript
394
// Valid queries
395
await client.query('SELECT 1');
396
await client.query('SELECT * FROM users WHERE id = $1', [123]);
397

398
// Invalid queries (will throw errors)
399
try {
400
  await client.query('');  // Empty query text
401
} catch (err) {
402
  console.error('Empty query error:', err.message);
403
}
404

405
try {
406
  await client.query('SELECT * FROM users WHERE id = $1');  // Missing parameter
407
} catch (err) {
408
  console.error('Missing parameter error:', err.message);
409
}
410

411
try {
412
  await client.query('SELECT * FROM users WHERE id = $1', [123, 456]);  // Too many parameters
413
} catch (err) {
414
  console.error('Parameter mismatch error:', err.message);
415
}
416
```
417

418
## Query Types
419

420
```javascript { .api }
421
type QueryConfig = {
422
  text: string;
423
  values?: any[];
424
  name?: string;
425
  rowMode?: 'array' | 'object';
426
  types?: TypeOverrides;
427
  binary?: boolean;
428
  portal?: string;
429
  rows?: number;
430
  callback?: QueryCallback;
431
  queryMode?: 'simple' | 'extended';
432
};
433

434
type QueryCallback = (err: Error | null, result: QueryResult) => void;
435

436
interface QueryResult {
437
  command: string;
438
  rowCount: number;
439
  oid: number;
440
  rows: any[];
441
  fields: FieldDef[];
442
  duration?: number;
443
}
444

445
interface FieldDef {
446
  name: string;
447
  tableID: number;
448
  columnID: number;
449
  dataTypeID: number;
450
  dataTypeSize: number;
451
  dataTypeModifier: number;
452
  format: string;
453
}
454
```