0
# Query Files and Prepared Statements
1

2
SQL file management and prepared statement support for better query organization and performance optimization. pg-promise provides classes for managing external SQL files, prepared statements, and parameterized queries.
3

4
## Capabilities
5

6
### Query File Management
7

8
The QueryFile class manages external SQL files with automatic loading, minification, and error handling.
9

10
```javascript { .api }
11
/**
12
 * Query File class for managing external SQL files
13
 */
14
class QueryFile {
15
  constructor(file: string, options?: IQueryFileOptions)
16
  
17
  readonly error: Error // File loading/parsing error (if any)
18
  readonly file: string // Absolute file path
19
  readonly options: any // File processing options
20
  
21
  prepare(): void // Prepare/reload the file
22
  toString(level?: number): string // String representation
23
}
24

25
interface IQueryFileOptions {
26
  debug?: boolean // Enable debug mode
27
  minify?: boolean | 'after' // Minify SQL (before or after parameter formatting)
28
  compress?: boolean // Compress whitespace
29
  params?: any // Default parameters for the query
30
  noWarnings?: boolean // Suppress warnings
31
}
32
```
33

34
**Usage Examples:**
35

36
```javascript
37
// Basic QueryFile usage
38
const getUsersQuery = new pgp.QueryFile('sql/get-users.sql');
39
const users = await db.any(getUsersQuery);
40

41
// QueryFile with options
42
const complexQuery = new pgp.QueryFile('sql/complex-report.sql', {
43
  minify: true,
44
  compress: true,
45
  params: { 
46
    defaultLimit: 100,
47
    defaultOffset: 0
48
  }
49
});
50

51
// Using with parameters
52
const reportData = await db.any(complexQuery, { 
53
  startDate: '2023-01-01',
54
  endDate: '2023-12-31'
55
});
56

57
// Error handling
58
const queryFile = new pgp.QueryFile('sql/might-not-exist.sql');
59
if (queryFile.error) {
60
  console.error('Query file error:', queryFile.error.message);
61
} else {
62
  const results = await db.any(queryFile);
63
}
64

65
// Reloading query files (useful in development)
66
queryFile.prepare(); // Reload from disk
67
```
68

69
### SQL File Organization
70

71
Best practices for organizing SQL files:
72

73
```javascript
74
// Directory structure example:
75
// sql/
76
//   ├── users/
77
//   │   ├── get-user.sql
78
//   │   ├── create-user.sql
79
//   │   └── update-user.sql
80
//   ├── orders/
81
//   │   ├── get-orders.sql
82
//   │   └── create-order.sql
83
//   └── reports/
84
//       └── monthly-sales.sql
85

86
// Loading SQL files with enumSql utility
87
const sql = pgp.utils.enumSql('./sql', { recursive: true }, file => {
88
  return new pgp.QueryFile(file, { minify: true });
89
});
90

91
// Usage:
92
const user = await db.one(sql.users.getUser, [userId]);
93
const orders = await db.any(sql.orders.getOrders, [userId]);
94
const report = await db.any(sql.reports.monthlySales, { month: '2023-01' });
95
```
96

97
### Prepared Statements
98

99
Prepared statements provide performance optimization and parameter binding for frequently executed queries.
100

101
```javascript { .api }
102
/**
103
 * Prepared Statement class for parameterized queries with performance optimization
104
 */
105
class PreparedStatement {
106
  constructor(options?: IPreparedStatement)
107
  
108
  // Standard properties
109
  name: string // Statement name (required)
110
  text: string | QueryFile // SQL query text or QueryFile
111
  values: any[] // Parameter values array
112
  
113
  // Advanced properties
114
  binary: boolean // Use binary format for parameters
115
  rowMode: void | 'array' // Row mode ('array' or default object mode)
116
  rows: number // Maximum rows to return
117
  types: ITypes // Custom type parsers
118
  
119
  parse(): IPreparedParsed | PreparedStatementError // Parse and validate
120
  toString(level?: number): string // String representation
121
}
122

123
interface IPreparedStatement {
124
  name?: string // Statement name
125
  text?: string | QueryFile // Query text
126
  values?: any[] // Parameter values
127
  binary?: boolean // Binary mode
128
  rowMode?: 'array' | null | void // Row mode
129
  rows?: number // Row limit
130
  types?: ITypes // Type parsers
131
}
132

133
interface IPreparedParsed {
134
  name: string // Parsed statement name
135
  text: string // Parsed query text
136
  values: any[] // Parsed parameter values
137
  binary: boolean // Binary mode flag
138
  rowMode: void | 'array' // Row mode setting
139
  rows: number // Row limit
140
}
141
```
142

143
**Usage Examples:**
144

145
```javascript
146
// Basic prepared statement
147
const getUserStmt = new pgp.PreparedStatement({
148
  name: 'get-user-by-id',
149
  text: 'SELECT * FROM users WHERE id = $1'
150
});
151

152
// Execute prepared statement
153
const user = await db.one(getUserStmt, [123]);
154

155
// Prepared statement with QueryFile
156
const complexStmt = new pgp.PreparedStatement({
157
  name: 'complex-report',
158
  text: new pgp.QueryFile('sql/complex-report.sql'),
159
  values: [defaultStartDate, defaultEndDate]
160
});
161

162
// Execute with custom values
163
const report = await db.any(complexStmt, [startDate, endDate]);
164

165
// Advanced prepared statement options
166
const advancedStmt = new pgp.PreparedStatement({
167
  name: 'bulk-insert',
168
  text: 'INSERT INTO logs(timestamp, level, message) VALUES($1, $2, $3)',
169
  binary: true, // Use binary format for better performance
170
  rowMode: 'array' // Return rows as arrays instead of objects
171
});
172

173
// Prepared statement validation
174
const stmt = new pgp.PreparedStatement({ name: 'test', text: 'SELECT $1' });
175
const parsed = stmt.parse();
176

177
if (parsed instanceof pgp.errors.PreparedStatementError) {
178
  console.error('Statement error:', parsed.message);
179
} else {
180
  console.log('Statement is valid:', parsed.name);
181
}
182
```
183

184
### Parameterized Queries
185

186
Parameterized queries provide a simpler alternative to prepared statements for one-time or infrequent queries.
187

188
```javascript { .api }
189
/**
190
 * Parameterized Query class for simple parameter binding
191
 */
192
class ParameterizedQuery {
193
  constructor(options?: string | QueryFile | IParameterizedQuery)
194
  
195
  // Standard properties  
196
  text: string | QueryFile // SQL query text or QueryFile
197
  values: any[] // Parameter values array
198
  
199
  // Advanced properties
200
  binary: boolean // Use binary format for parameters
201
  rowMode: void | 'array' // Row mode ('array' or default object mode)
202
  types: ITypes // Custom type parsers
203
  
204
  parse(): IParameterizedParsed | ParameterizedQueryError // Parse and validate
205
  toString(level?: number): string // String representation
206
}
207

208
interface IParameterizedQuery {
209
  text?: string | QueryFile // Query text
210
  values?: any[] // Parameter values
211
  binary?: boolean // Binary mode
212
  rowMode?: void | 'array' // Row mode
213
  types?: ITypes // Type parsers
214
}
215

216
interface IParameterizedParsed {
217
  text: string // Parsed query text
218
  values: any[] // Parsed parameter values
219
  binary: boolean // Binary mode flag
220
  rowMode: void | 'array' // Row mode setting
221
}
222
```
223

224
**Usage Examples:**
225

226
```javascript
227
// Basic parameterized query
228
const getUserQuery = new pgp.ParameterizedQuery({
229
  text: 'SELECT * FROM users WHERE age > $1 AND status = $2',
230
  values: [25, 'active']
231
});
232

233
const users = await db.any(getUserQuery);
234

235
// Parameterized query with QueryFile
236
const reportQuery = new pgp.ParameterizedQuery({
237
  text: new pgp.QueryFile('sql/user-report.sql'),
238
  values: [startDate, endDate, department]
239
});
240

241
const report = await db.any(reportQuery);
242

243
// Constructor shortcuts
244
const simpleQuery = new pgp.ParameterizedQuery('SELECT * FROM users WHERE id = $1');
245
simpleQuery.values = [123];
246

247
const fileQuery = new pgp.ParameterizedQuery(new pgp.QueryFile('sql/get-orders.sql'));
248
fileQuery.values = [userId];
249

250
// Advanced options
251
const binaryQuery = new pgp.ParameterizedQuery({
252
  text: 'SELECT data FROM binary_table WHERE id = $1',
253
  values: [recordId],
254
  binary: true,
255
  rowMode: 'array'
256
});
257

258
// Query validation
259
const query = new pgp.ParameterizedQuery({ text: 'SELECT $1, $2', values: ['a'] });
260
const parsed = query.parse();
261

262
if (parsed instanceof pgp.errors.ParameterizedQueryError) {
263
  console.error('Query error:', parsed.message);
264
} else {
265
  console.log('Query is valid, has', parsed.text.split('$').length - 1, 'parameters');
266
}
267
```
268

269
### SQL File Utilities
270

271
Utility functions for working with SQL files and query organization.
272

273
```javascript { .api }
274
/**
275
 * Enumerate SQL files in directory structure
276
 * @param dir - Directory path containing SQL files
277
 * @param options - Enumeration options
278
 * @param cb - Optional callback for file processing
279
 * @returns Object tree of SQL files/QueryFiles
280
 */
281
pgp.utils.enumSql(dir: string, options?: IEnumSqlOptions, cb?: (file: string, name: string, path: string) => any): object
282

283
interface IEnumSqlOptions {
284
  recursive?: boolean // Include subdirectories
285
  ignoreErrors?: boolean // Ignore access/naming errors
286
}
287
```
288

289
**Usage Examples:**
290

291
```javascript
292
// Basic SQL file enumeration
293
const sqlFiles = pgp.utils.enumSql('./sql');
294
// Returns: { getUsers: './sql/get-users.sql', createUser: './sql/create-user.sql' }
295

296
// Recursive enumeration with QueryFile creation
297
const sql = pgp.utils.enumSql('./sql', { recursive: true }, file => {
298
  return new pgp.QueryFile(file, { minify: true });
299
});
300

301
// Usage of enumerated files
302
const users = await db.any(sql.users.getActive);
303
const orders = await db.any(sql.orders.getByUser, [userId]);
304

305
// With error handling
306
const sqlSafe = pgp.utils.enumSql('./sql', { 
307
  recursive: true, 
308
  ignoreErrors: true 
309
}, file => {
310
  const queryFile = new pgp.QueryFile(file, { minify: true });
311
  if (queryFile.error) {
312
    console.warn(`Failed to load ${file}:`, queryFile.error.message);
313
    return null;
314
  }
315
  return queryFile;
316
});
317

318
// Custom processing
319
const sqlMetadata = pgp.utils.enumSql('./sql', { recursive: true }, (file, name, path) => {
320
  return {
321
    queryFile: new pgp.QueryFile(file),
322
    name: name,
323
    path: path,
324
    size: require('fs').statSync(file).size
325
  };
326
});
327
```
328

329
### Query File Best Practices
330

331
Examples of well-structured SQL files:
332

333
```sql
334
-- sql/users/get-active-users.sql
335
-- Get all active users with optional filtering
336
SELECT 
337
    u.id,
338
    u.name,
339
    u.email,
340
    u.created_at,
341
    up.bio
342
FROM users u
343
LEFT JOIN user_profiles up ON u.id = up.user_id
344
WHERE u.active = true
345
    AND ($1::varchar IS NULL OR u.name ILIKE '%' || $1 || '%')
346
    AND ($2::date IS NULL OR u.created_at >= $2)
347
ORDER BY u.created_at DESC
348
LIMIT $3::int
349
OFFSET $4::int;
350
```
351

352
```sql
353
-- sql/orders/create-order-with-items.sql  
354
-- Create order with items in a single transaction
355
WITH new_order AS (
356
    INSERT INTO orders (user_id, total_amount, status)
357
    VALUES ($1, $2, 'pending')
358
    RETURNING id, created_at
359
),
360
order_items AS (
361
    INSERT INTO order_items (order_id, product_id, quantity, price)
362
    SELECT 
363
        (SELECT id FROM new_order),
364
        unnest($3::int[]) as product_id,
365
        unnest($4::int[]) as quantity, 
366
        unnest($5::decimal[]) as price
367
    RETURNING *
368
)
369
SELECT 
370
    o.id,
371
    o.created_at,
372
    json_agg(
373
        json_build_object(
374
            'product_id', oi.product_id,
375
            'quantity', oi.quantity,
376
            'price', oi.price
377
        )
378
    ) as items
379
FROM new_order o
380
CROSS JOIN order_items oi
381
GROUP BY o.id, o.created_at;
382
```
383

384
## Types
385

386
```javascript { .api }
387
// Type parser interface for custom types
388
interface ITypes {
389
  getTypeParser(id: number, format?: string): (value: string) => any
390
}
391

392
// Error types for query files and statements
393
class QueryFileError extends Error {
394
  name: string
395
  message: string
396
  stack: string
397
  file: string // File path that caused error
398
  options: IQueryFileOptions // File options used
399
  error: SQLParsingError // Underlying parsing error
400
  toString(level?: number): string
401
}
402

403
class PreparedStatementError extends Error {
404
  name: string
405
  message: string
406
  stack: string
407
  error: QueryFileError // Underlying QueryFile error (if applicable)
408
  toString(level?: number): string
409
}
410

411
class ParameterizedQueryError extends Error {
412
  name: string
413
  message: string
414
  stack: string
415
  error: QueryFileError // Underlying QueryFile error (if applicable)
416
  toString(level?: number): string
417
}
418

419
// SQL parsing error from pg-minify
420
interface SQLParsingError {
421
  name: string
422
  message: string
423
  position: number
424
  line: number
425
  column: number
426
}
427
```