0
# Database Operations
1

2
Core database query methods with result-specific interfaces for type-safe query execution. All methods are promise-based and provide automatic connection management.
3

4
## Capabilities
5

6
### Generic Query Method
7

8
The base query method that all other query methods are built upon.
9

10
```javascript { .api }
11
/**
12
 * Generic query method for executing any SQL query
13
 * @param query - SQL query string, QueryFile, PreparedStatement, ParameterizedQuery, or function
14
 * @param values - Query parameter values (optional)
15
 * @param qrm - Query Result Mask to specify expected result format (optional)
16
 * @returns Promise resolving to query results
17
 */
18
db.query(query: QueryParam, values?: any, qrm?: queryResult): Promise<any>
19
```
20

21
**Usage Examples:**
22

23
```javascript
24
// Basic query with positional parameters
25
const users = await db.query('SELECT * FROM users WHERE age > $1', [25]);
26

27
// Query with Query Result Mask
28
const user = await db.query('SELECT * FROM users WHERE id = $1', [123], pgp.queryResult.one);
29

30
// Using QueryFile
31
const getUsersQuery = new pgp.QueryFile('sql/get-users.sql');
32
const users = await db.query(getUsersQuery, { minAge: 25 });
33
```
34

35
### Result-Specific Query Methods
36

37
#### None Method
38

39
Expects no data to be returned. Rejects if any rows are returned.
40

41
```javascript { .api }
42
/**
43
 * Executes query expecting no data to be returned
44
 * @param query - SQL query
45
 * @param values - Query parameter values (optional)
46
 * @returns Promise resolving to null
47
 * @throws QueryResultError if any rows are returned
48
 */
49
db.none(query: QueryParam, values?: any): Promise<null>
50
```
51

52
**Usage Examples:**
53

54
```javascript
55
// INSERT/UPDATE/DELETE operations
56
await db.none('INSERT INTO users(name, email) VALUES($1, $2)', ['John', 'john@example.com']);
57
await db.none('UPDATE users SET active = $1 WHERE id = $2', [false, 123]);
58
await db.none('DELETE FROM users WHERE id = $1', [123]);
59
```
60

61
#### One Method
62

63
Expects exactly one row to be returned. Rejects if zero rows or multiple rows are returned.
64

65
```javascript { .api }
66
/**
67
 * Executes query expecting exactly one row
68
 * @param query - SQL query
69
 * @param values - Query parameter values (optional)
70
 * @param cb - Optional callback for result transformation
71
 * @param thisArg - Optional 'this' context for callback
72
 * @returns Promise resolving to single result row
73
 * @throws QueryResultError if zero rows or multiple rows returned
74
 */
75
db.one(query: QueryParam, values?: any, cb?: (value: any) => any, thisArg?: any): Promise<any>
76
```
77

78
**Usage Examples:**
79

80
```javascript
81
// Get single user by ID
82
const user = await db.one('SELECT * FROM users WHERE id = $1', [123]);
83

84
// Get specific field with callback transformation
85
const userId = await db.one('INSERT INTO users(name) VALUES($1) RETURNING id', ['Jane'], r => r.id);
86

87
// Get count
88
const count = await db.one('SELECT COUNT(*) as total FROM users', [], r => r.total);
89
```
90

91
#### One or None Method
92

93
Expects zero or one row. Rejects if multiple rows are returned.
94

95
```javascript { .api }
96
/**
97
 * Executes query expecting zero or one row
98
 * @param query - SQL query
99
 * @param values - Query parameter values (optional)
100
 * @param cb - Optional callback for result transformation
101
 * @param thisArg - Optional 'this' context for callback
102
 * @returns Promise resolving to single result row or null
103
 * @throws QueryResultError if multiple rows returned
104
 */
105
db.oneOrNone(query: QueryParam, values?: any, cb?: (value: any) => any, thisArg?: any): Promise<any | null>
106
```
107

108
**Usage Examples:**
109

110
```javascript
111
// Find user by email (may not exist)
112
const user = await db.oneOrNone('SELECT * FROM users WHERE email = $1', ['john@example.com']);
113

114
if (user) {
115
  console.log('User found:', user.name);
116
} else {
117
  console.log('User not found');
118
}
119

120
// Get optional configuration value
121
const config = await db.oneOrNone('SELECT value FROM config WHERE key = $1', ['api_key'], r => r?.value);
122
```
123

124
#### Many Method
125

126
Expects one or more rows. Rejects if zero rows are returned.
127

128
```javascript { .api }
129
/**
130
 * Executes query expecting one or more rows
131
 * @param query - SQL query
132
 * @param values - Query parameter values (optional)
133
 * @returns Promise resolving to array of result rows
134
 * @throws QueryResultError if zero rows returned
135
 */
136
db.many(query: QueryParam, values?: any): Promise<any[]>
137
```
138

139
**Usage Examples:**
140

141
```javascript
142
// Get active users (must have at least one)
143
const activeUsers = await db.many('SELECT * FROM users WHERE active = true');
144

145
// Get user orders (user must have orders)
146
const orders = await db.many('SELECT * FROM orders WHERE user_id = $1', [123]);
147
```
148

149
#### Many or None / Any Method
150

151
Expects zero or more rows. Never rejects based on row count.
152

153
```javascript { .api }
154
/**
155
 * Executes query expecting zero or more rows
156
 * @param query - SQL query
157
 * @param values - Query parameter values (optional)
158
 * @returns Promise resolving to array of result rows (may be empty)
159
 */
160
db.manyOrNone(query: QueryParam, values?: any): Promise<any[]>
161

162
/**
163
 * Alias for manyOrNone - executes query expecting zero or more rows
164
 * @param query - SQL query
165
 * @param values - Query parameter values (optional)
166
 * @returns Promise resolving to array of result rows (may be empty)
167
 */
168
db.any(query: QueryParam, values?: any): Promise<any[]>
169
```
170

171
**Usage Examples:**
172

173
```javascript
174
// Get all users (may be empty)
175
const users = await db.any('SELECT * FROM users');
176

177
// Get filtered results (may be empty)
178
const filteredUsers = await db.manyOrNone('SELECT * FROM users WHERE age > $1', [65]);
179
```
180

181
### Advanced Query Methods
182

183
#### Result Method
184

185
Returns the full result object with metadata instead of just the data rows.
186

187
```javascript { .api }
188
/**
189
 * Executes query and returns full result object with metadata
190
 * @param query - SQL query
191
 * @param values - Query parameter values (optional)
192
 * @param cb - Optional callback for result transformation
193
 * @param thisArg - Optional 'this' context for callback
194
 * @returns Promise resolving to result object with rows, rowCount, command, etc.
195
 */
196
db.result(query: QueryParam, values?: any, cb?: (value: IResultExt) => any, thisArg?: any): Promise<IResultExt>
197
```
198

199
**Usage Examples:**
200

201
```javascript
202
// Get result with metadata
203
const result = await db.result('SELECT * FROM users WHERE age > $1', [25]);
204
console.log(`Found ${result.rowCount} users`);
205
console.log('Rows:', result.rows);
206
console.log('Command:', result.command);
207
console.log('Duration:', result.duration); // Available in 'receive' event context
208

209
// Transform result
210
const count = await db.result('SELECT COUNT(*) FROM users', [], r => r.rows[0].count);
211
```
212

213
#### Multi-Result Method
214

215
Executes multiple queries in sequence and returns array of result objects.
216

217
```javascript { .api }
218
/**
219
 * Executes multiple queries and returns array of result objects
220
 * @param query - SQL query string with multiple statements separated by semicolons
221
 * @param values - Query parameter values (optional)
222
 * @returns Promise resolving to array of result objects
223
 */
224
db.multiResult(query: QueryParam, values?: any): Promise<IResult[]>
225
```
226

227
**Usage Examples:**
228

229
```javascript
230
// Execute multiple queries
231
const results = await db.multiResult(`
232
  SELECT COUNT(*) as user_count FROM users;
233
  SELECT COUNT(*) as order_count FROM orders;
234
  SELECT AVG(amount) as avg_amount FROM orders;
235
`);
236

237
console.log('User count:', results[0].rows[0].user_count);
238
console.log('Order count:', results[1].rows[0].order_count);
239
console.log('Average amount:', results[2].rows[0].avg_amount);
240
```
241

242
#### Multi Method
243

244
Executes multiple queries and returns array of data arrays (not result objects).
245

246
```javascript { .api }
247
/**
248
 * Executes multiple queries and returns array of data arrays
249
 * @param query - SQL query string with multiple statements
250
 * @param values - Query parameter values (optional)
251
 * @returns Promise resolving to array of data arrays
252
 */
253
db.multi(query: QueryParam, values?: any): Promise<Array<any[]>>
254
```
255

256
**Usage Examples:**
257

258
```javascript
259
// Execute multiple queries, get data only
260
const [users, orders, products] = await db.multi(`
261
  SELECT * FROM users WHERE active = true;
262
  SELECT * FROM orders WHERE status = 'pending';
263
  SELECT * FROM products WHERE in_stock = true;
264
`);
265
```
266

267
#### Stream Method
268

269
Executes query using a readable stream for processing large result sets.
270

271
```javascript { .api }
272
/**
273
 * Executes query using readable stream for large result sets
274
 * @param qs - Query stream object (from pg-query-stream)
275
 * @param init - Initialization function called with the stream
276
 * @returns Promise resolving to processing statistics
277
 */
278
db.stream(qs: ReadableStream, init: (stream: ReadableStream) => void): Promise<StreamResult>
279

280
interface StreamResult {
281
  processed: number
282
  duration: number
283
}
284
```
285

286
**Usage Examples:**
287

288
```javascript
289
const QueryStream = require('pg-query-stream');
290

291
// Process large dataset with streaming
292
const stats = await db.stream(
293
  new QueryStream('SELECT * FROM large_table WHERE active = $1', [true]),
294
  stream => {
295
    stream.on('data', row => {
296
      // Process each row
297
      console.log('Processing:', row.id);
298
    });
299
  }
300
);
301

302
console.log(`Processed ${stats.processed} rows in ${stats.duration}ms`);
303
```
304

305
### Database Function Methods
306

307
#### Function Method
308

309
Calls a database function and returns the result.
310

311
```javascript { .api }
312
/**
313
 * Calls a database function
314
 * @param funcName - Name of the database function
315
 * @param values - Function parameter values (optional)
316
 * @param qrm - Query Result Mask for expected result format (optional)
317
 * @returns Promise resolving to function result
318
 */
319
db.func(funcName: string, values?: any, qrm?: queryResult): Promise<any>
320
```
321

322
**Usage Examples:**
323

324
```javascript
325
// Call function expecting single value
326
const result = await db.func('calculate_total', [123], pgp.queryResult.one);
327

328
// Call function expecting multiple rows
329
const stats = await db.func('get_user_statistics', [userId], pgp.queryResult.many);
330

331
// Call function with no parameters
332
const serverTime = await db.func('now', [], pgp.queryResult.one);
333
```
334

335
#### Procedure Method
336

337
Calls a stored procedure and returns the first row or null.
338

339
```javascript { .api }
340
/**
341
 * Calls a stored procedure
342
 * @param procName - Name of the stored procedure
343
 * @param values - Procedure parameter values (optional)
344
 * @param cb - Optional callback for result transformation
345
 * @param thisArg - Optional 'this' context for callback
346
 * @returns Promise resolving to first result row or null
347
 */
348
db.proc(procName: string, values?: any, cb?: (value: any) => any, thisArg?: any): Promise<any | null>
349
```
350

351
**Usage Examples:**
352

353
```javascript
354
// Call stored procedure
355
const result = await db.proc('process_order', [orderId, userId]);
356

357
// Call procedure with result transformation
358
const processedId = await db.proc('create_user_profile', [userData], r => r?.id);
359
```
360

361
### Data Processing Methods
362

363
#### Map Method
364

365
Executes query and maps each row through a transformation function.
366

367
```javascript { .api }
368
/**
369
 * Executes query and maps each row through transformation function
370
 * @param query - SQL query
371
 * @param values - Query parameter values
372
 * @param cb - Mapping function called for each row
373
 * @param thisArg - Optional 'this' context for callback
374
 * @returns Promise resolving to array of transformed values
375
 */
376
db.map(query: QueryParam, values: any, cb: (row: any, index: number, data: any[]) => any, thisArg?: any): Promise<any[]>
377
```
378

379
**Usage Examples:**
380

381
```javascript
382
// Map users to display format
383
const userNames = await db.map(
384
  'SELECT id, first_name, last_name FROM users',
385
  [],
386
  (row, index) => `${row.first_name} ${row.last_name}`
387
);
388

389
// Extract specific values
390
const orderTotals = await db.map(
391
  'SELECT amount, tax FROM orders WHERE user_id = $1',
392
  [userId],
393
  row => row.amount + row.tax
394
);
395
```
396

397
#### Each Method
398

399
Executes query and calls a function for each row (for side effects).
400

401
```javascript { .api }
402
/**
403
 * Executes query and calls function for each row (side effects)
404
 * @param query - SQL query
405
 * @param values - Query parameter values
406
 * @param cb - Function called for each row
407
 * @param thisArg - Optional 'this' context for callback
408
 * @returns Promise resolving to original array of rows
409
 */
410
db.each(query: QueryParam, values: any, cb: (row: any, index: number, data: any[]) => void, thisArg?: any): Promise<any[]>
411
```
412

413
**Usage Examples:**
414

415
```javascript
416
// Log each user
417
const users = await db.each(
418
  'SELECT * FROM users WHERE active = true',
419
  [],
420
  (user, index) => {
421
    console.log(`User ${index + 1}: ${user.name} (${user.email})`);
422
  }
423
);
424

425
// Update external system for each order
426
await db.each(
427
  'SELECT * FROM orders WHERE status = $1',
428
  ['pending'],
429
  async (order) => {
430
    await externalAPI.notifyOrder(order);
431
  }
432
);
433
```
434

435
## Types
436

437
```javascript { .api }
438
// Result object interface
439
interface IResultExt {
440
  rows: any[]
441
  rowCount: number
442
  command: string
443
  oid: number
444
  fields: FieldDef[]
445
  duration?: number // Available in certain contexts
446
}
447

448
// Field definition
449
interface FieldDef {
450
  name: string
451
  tableID: number
452
  columnID: number
453
  dataTypeID: number
454
  dataTypeSize: number
455
  dataTypeModifier: number
456
  format: string
457
}
458

459
// Query result error codes
460
enum queryResultErrorCode {
461
  noData = 0,    // No data returned when expecting some
462
  notEmpty = 1,  // Data returned when expecting none
463
  multiple = 2   // Multiple rows returned when expecting one
464
}
465

466
// Query Result Mask values
467
enum queryResult {
468
  one = 1,   // Expecting exactly one row
469
  many = 2,  // Expecting one or more rows
470
  none = 4,  // Expecting no rows
471
  any = 6    // Expecting zero or more rows (many | none)
472
}
473
```