0
# Query Formatting
1

2
Advanced query formatting with named parameters, formatting filters, and query generation helpers. pg-promise provides a powerful query formatting engine that supports named parameters, formatting filters, and query generation utilities.
3

4
## Capabilities
5

6
### Query Formatting Functions
7

8
Core formatting functions available in the `pgp.as` namespace for converting values to SQL-safe strings.
9

10
```javascript { .api }
11
/**
12
 * Format a query with values and options
13
 * @param query - SQL query template with parameter placeholders
14
 * @param values - Parameter values (array, object, or single value)
15
 * @param options - Formatting options
16
 * @returns Formatted SQL query string
17
 */
18
pgp.as.format(query: string | QueryFile, values?: any, options?: IFormattingOptions): string
19

20
interface IFormattingOptions {
21
  capSQL?: boolean // Capitalize SQL keywords
22
  partial?: boolean // Allow partial formatting
23
  def?: any // Default value for missing parameters
24
}
25
```
26

27
**Usage Examples:**
28

29
```javascript
30
// Basic parameter substitution
31
const query1 = pgp.as.format('SELECT * FROM users WHERE id = $1', [123]);
32
// Result: "SELECT * FROM users WHERE id = 123"
33

34
// Named parameters
35
const query2 = pgp.as.format('SELECT * FROM users WHERE name = ${name} AND age > ${age}', {
36
  name: 'John',
37
  age: 25
38
});
39
// Result: "SELECT * FROM users WHERE name = 'John' AND age > 25"
40

41
// With formatting options
42
const query3 = pgp.as.format('select * from users where id = $1', [123], { capSQL: true });
43
// Result: "SELECT * FROM users WHERE id = 123"
44
```
45

46
### Value Formatting Functions
47

48
Functions for formatting specific value types.
49

50
```javascript { .api }
51
/**
52
 * Format a value for SQL
53
 * @param value - Value to format (any type)
54
 * @returns SQL-safe string representation
55
 */
56
pgp.as.value(value: any | (() => any)): string
57

58
/**
59
 * Format a SQL name (identifier)
60
 * @param name - SQL identifier name
61
 * @returns Properly escaped SQL identifier
62
 */
63
pgp.as.name(name: any | (() => any)): string
64

65
/**
66
 * Format an alias (quoted identifier)
67
 * @param name - Alias name or function returning name
68
 * @returns Quoted SQL alias
69
 */
70
pgp.as.alias(name: string | (() => string)): string
71

72
/**
73
 * Format a number value
74
 * @param value - Number or bigint value to format
75
 * @returns String representation of number
76
 */
77
pgp.as.number(value: number | bigint | (() => number | bigint)): string
78

79
/**
80
 * Format a boolean value
81
 * @param value - Boolean value to format
82
 * @returns 'true' or 'false' string
83
 */
84
pgp.as.bool(value: any | (() => any)): string
85

86
/**
87
 * Format a text string
88
 * @param value - Text value to format
89
 * @param raw - Whether to return raw (unquoted) text
90
 * @returns SQL-safe quoted string or raw text
91
 */
92
pgp.as.text(value: any | (() => any), raw?: boolean): string
93

94
/**
95
 * Format a Date object
96
 * @param date - Date object to format
97
 * @param raw - Whether to return raw timestamp
98
 * @returns SQL timestamp string
99
 */
100
pgp.as.date(date: Date | (() => Date), raw?: boolean): string
101
```
102

103
**Usage Examples:**
104

105
```javascript
106
// Value formatting
107
const userValue = pgp.as.value(123); // "123"
108
const nameValue = pgp.as.value("John O'Connor"); // "'John O''Connor'"
109
const nullValue = pgp.as.value(null); // "null"
110

111
// Name formatting
112
const tableName = pgp.as.name('user_table'); // "user_table"
113
const complexName = pgp.as.name('table with spaces'); // '"table with spaces"'
114

115
// Alias formatting  
116
const alias = pgp.as.alias('user_count'); // '"user_count"'
117

118
// Type-specific formatting
119
const numStr = pgp.as.number(123.45); // "123.45"
120
const boolStr = pgp.as.bool(true); // "true"
121
const textStr = pgp.as.text('Hello World'); // "'Hello World'"
122
const dateStr = pgp.as.date(new Date('2023-01-01')); // "'2023-01-01T00:00:00.000Z'"
123
```
124

125
### Array and Collection Formatting
126

127
Functions for formatting arrays and collections.
128

129
```javascript { .api }
130
/**
131
 * Format an array as SQL array literal
132
 * @param arr - Array to format
133
 * @param options - Array formatting options
134
 * @returns SQL array literal string
135
 */
136
pgp.as.array(arr: any[] | (() => any[]), options?: { capSQL?: boolean }): string
137

138
/**
139
 * Format values as CSV (comma-separated values)
140
 * @param values - Values to format as CSV
141
 * @returns Comma-separated SQL values
142
 */
143
pgp.as.csv(values: any | (() => any)): string
144

145
/**
146
 * Format data as JSON
147
 * @param data - Data to format as JSON
148
 * @param raw - Whether to return raw JSON string
149
 * @returns SQL JSON string
150
 */
151
pgp.as.json(data: any | (() => any), raw?: boolean): string
152
```
153

154
**Usage Examples:**
155

156
```javascript
157
// Array formatting
158
const arrayStr = pgp.as.array([1, 2, 3]); // "ARRAY[1,2,3]"
159
const stringArray = pgp.as.array(['a', 'b', 'c']); // "ARRAY['a','b','c']"
160

161
// CSV formatting
162
const csvStr = pgp.as.csv([1, 'text', true]); // "1,'text',true"
163
const csvQuery = `SELECT * FROM users WHERE id IN (${pgp.as.csv([1, 2, 3])})`;
164

165
// JSON formatting
166
const jsonStr = pgp.as.json({ name: 'John', age: 30 }); // "'{\"name\":\"John\",\"age\":30}'"
167
const rawJson = pgp.as.json({ key: 'value' }, true); // "{\"key\":\"value\"}"
168
```
169

170
### Advanced Formatting Functions
171

172
Advanced formatting capabilities for complex scenarios.
173

174
```javascript { .api }
175
/**
176
 * Format a buffer object
177
 * @param obj - Buffer object to format
178
 * @param raw - Whether to return raw buffer data
179
 * @returns SQL bytea string
180
 */
181
pgp.as.buffer(obj: object | (() => object), raw?: boolean): string
182

183
/**
184
 * Format a function call result
185
 * @param func - Function to call and format result
186
 * @param raw - Whether to return raw result
187
 * @param cc - Custom context for function call
188
 * @returns Formatted function result
189
 */
190
pgp.as.func(func: (cc: any) => any, raw?: boolean, cc?: any): string
191
```
192

193
**Usage Examples:**
194

195
```javascript
196
// Buffer formatting
197
const bufferStr = pgp.as.buffer(Buffer.from('hello')); // "\\x68656c6c6f"
198

199
// Function formatting
200
const dynamicValue = pgp.as.func(() => new Date().getTime()); // Current timestamp
201
const contextValue = pgp.as.func(cc => cc.userId, false, { userId: 123 }); // "123"
202
```
203

204
### Query Generation Helpers
205

206
Helper functions for generating common SQL patterns, available in `pgp.helpers` namespace.
207

208
```javascript { .api }
209
/**
210
 * Generate INSERT query for single or multiple records
211
 * @param data - Data object or array of objects to insert
212
 * @param columns - Column configuration (optional)
213
 * @param table - Target table name (optional)
214
 * @returns INSERT SQL query string
215
 */
216
pgp.helpers.insert(data: object | object[], columns?: QueryColumns<any> | null, table?: string | ITable | TableName): string
217

218
/**
219
 * Generate UPDATE query for single or multiple records
220
 * @param data - Data object or array of objects to update
221
 * @param columns - Column configuration (optional)  
222
 * @param table - Target table name (optional)
223
 * @param options - Update options
224
 * @returns UPDATE SQL query string
225
 */
226
pgp.helpers.update(data: object | object[], columns?: QueryColumns<any> | null, table?: string | ITable | TableName, options?: IUpdateOptions): string
227

228
/**
229
 * Generate VALUES clause for multi-row operations
230
 * @param data - Data array to generate values for
231
 * @param columns - Column configuration (optional)
232
 * @returns VALUES clause string
233
 */
234
pgp.helpers.values(data: object | object[], columns?: QueryColumns<any> | null): string
235

236
/**
237
 * Generate SET clause for UPDATE operations
238
 * @param data - Data object with update values
239
 * @param columns - Column configuration (optional)
240
 * @returns SET clause string
241
 */
242
pgp.helpers.sets(data: object, columns?: QueryColumns<any> | null): string
243

244
/**
245
 * Concatenate multiple queries into single query string
246
 * @param queries - Array of query objects or strings
247
 * @returns Concatenated query string
248
 */
249
pgp.helpers.concat(queries: Array<string | QueryFile | IQueryConfig>): string
250

251
/**
252
 * Create TableName object from dot-separated path
253
 * @param path - Dot-separated table path (e.g., 'schema.table')
254
 * @param ...args - Additional arguments for TableName construction
255
 * @returns TableName instance
256
 */
257
pgp.helpers._TN(path: string, ...args: any[]): TableName
258

259
interface IUpdateOptions {
260
  tableAlias?: string // Alias for target table
261
  valueAlias?: string // Alias for values table
262
  emptyUpdate?: any // Value to return for empty updates
263
}
264

265
interface IQueryConfig {
266
  query: string | QueryFile // Query text
267
  values?: any // Query parameters
268
  options?: IFormattingOptions // Formatting options
269
}
270
```
271

272
**Usage Examples:**
273

274
```javascript
275
// INSERT helper
276
const insertQuery = pgp.helpers.insert([
277
  { name: 'John', email: 'john@example.com' },
278
  { name: 'Jane', email: 'jane@example.com' }
279
], null, 'users');
280
// Result: INSERT INTO "users"("name","email") VALUES('John','john@example.com'),('Jane','jane@example.com')
281

282
// UPDATE helper
283
const updateQuery = pgp.helpers.update(
284
  { name: 'John Updated', email: 'john.new@example.com' },
285
  null,
286
  'users'
287
) + ' WHERE id = 123';
288

289
// VALUES helper for custom operations
290
const valuesClause = pgp.helpers.values([
291
  { id: 1, value: 'a' },
292
  { id: 2, value: 'b' }
293
]);
294
const customQuery = `INSERT INTO temp_table(id, value) ${valuesClause} ON CONFLICT (id) DO UPDATE SET value = EXCLUDED.value`;
295

296
// SET helper
297
const setClause = pgp.helpers.sets({ name: 'Updated', status: 'active' });
298
const updateQuery2 = `UPDATE users SET ${setClause} WHERE id = $1`;
299

300
// Concatenate queries
301
const batchQuery = pgp.helpers.concat([
302
  'DELETE FROM temp_data',
303
  { query: 'INSERT INTO temp_data SELECT * FROM source WHERE date > $1', values: [yesterday] },
304
  'ANALYZE temp_data'
305
]);
306

307
// Table name helper with dot notation
308
const table = pgp.helpers._TN('app.users'); // Creates TableName for 'app'.'users'
309
const insertQuery = pgp.helpers.insert(userData, null, table);
310
```
311

312
### Column and Table Helpers
313

314
Helper classes for advanced column and table management.
315

316
```javascript { .api }
317
/**
318
 * Table name helper class
319
 */
320
class TableName {
321
  constructor(table: string | ITable)
322
  readonly name: string // Full table name with schema
323
  readonly table: string // Table name only
324
  readonly schema: string // Schema name only
325
  toString(): string
326
  toPostgres(): string // Returns PostgreSQL-formatted name
327
}
328

329
/**
330
 * Column configuration helper class
331
 */
332
class Column<T> {
333
  constructor(col: string | IColumnConfig<T>)
334
  readonly name: string // Column name
335
  readonly prop: string // Property name
336
  readonly mod: FormattingFilter // Formatting modifier
337
  readonly cast: string // Type cast
338
  readonly cnd: boolean // Conditional column
339
  readonly def: any // Default value
340
  readonly castText: string // Cast text representation
341
  readonly escapedName: string // Escaped column name
342
  readonly variable: string // Variable placeholder
343
  readonly init: (col: IColumnDescriptor<T>) => any // Initialization function
344
  readonly skip: (col: IColumnDescriptor<T>) => boolean // Skip condition function
345
  toString(level?: number): string
346
}
347

348
/**
349
 * Column set manager class
350
 */
351
class ColumnSet<T> {
352
  constructor(columns: Column<T> | Array<string | IColumnConfig<T> | Column<T>>, options?: IColumnSetOptions)
353
  readonly columns: Column<T>[] // Array of columns
354
  readonly names: string // Column names string
355
  readonly table: TableName // Associated table
356
  readonly variables: string // Variable placeholders string
357
  
358
  assign(source?: IAssignOptions): string
359
  assignColumns(options?: IAssignColumnsOptions): string
360
  extend<S>(columns: Column<T> | ColumnSet<T> | Array<string | IColumnConfig<T> | Column<T>>): ColumnSet<S>
361
  merge<S>(columns: Column<T> | ColumnSet<T> | Array<string | IColumnConfig<T> | Column<T>>): ColumnSet<S>
362
  prepare(obj: object): object
363
  toString(level?: number): string
364
}
365

366
interface ITable {
367
  schema?: string // Schema name
368
  table: string // Table name
369
}
370

371
interface IColumnConfig<T> {
372
  name: string // Column name
373
  prop?: string // Source property name
374
  mod?: FormattingFilter // Formatting filter
375
  cast?: string // Type cast
376
  cnd?: boolean // Conditional column
377
  def?: any // Default value
378
  init?(col: IColumnDescriptor<T>): any // Initialization function
379
  skip?(col: IColumnDescriptor<T>): boolean // Skip condition
380
}
381

382
interface IColumnSetOptions {
383
  table?: string | ITable | TableName // Associated table
384
  inherit?: boolean // Inherit from parent
385
}
386
```
387

388
**Usage Examples:**
389

390
```javascript
391
// Table name helper
392
const table = new pgp.helpers.TableName({ schema: 'public', table: 'users' });
393
console.log(table.name); // "public"."users"
394

395
// Column configuration
396
const columns = new pgp.helpers.ColumnSet([
397
  'id',
398
  'name',
399
  { name: 'email', prop: 'email_address' },
400
  { name: 'created_at', def: () => new Date() },
401
  { name: 'data', mod: ':json' }
402
], { table: 'users' });
403

404
// Advanced INSERT with column set
405
const insertQuery = pgp.helpers.insert(userData, columns);
406

407
// Column assignment for UPDATE
408
const assignQuery = columns.assign({ from: 'source', to: 'target' });
409
const updateQuery = `UPDATE users SET ${assignQuery} FROM (VALUES ${pgp.helpers.values(userData, columns)}) AS source(id, name, email, created_at, data) WHERE users.id = source.id`;
410
```
411

412
### Formatting Filters
413

414
Formatting filters for specialized value formatting.
415

416
```javascript { .api }
417
// Available formatting filters
418
type FormattingFilter = 
419
  | '^'      // Raw text (no escaping)
420
  | '~'      // SQL name formatting
421
  | '#'      // SQL identifier formatting
422
  | ':raw'   // Raw value (no quotes)
423
  | ':alias' // Quoted alias
424
  | ':name'  // SQL name
425
  | ':json'  // JSON formatting
426
  | ':csv'   // CSV formatting
427
  | ':list'  // List formatting
428
  | ':value' // Standard value formatting
429
```
430

431
**Usage Examples:**
432

433
```javascript
434
// Using formatting filters in queries
435
const query = 'SELECT ${fields^} FROM ${table~} WHERE ${condition^}';
436
const formatted = pgp.as.format(query, {
437
  fields: 'id, name, email',
438
  table: 'users',
439
  condition: 'active = true'
440
});
441
// Result: SELECT id, name, email FROM "users" WHERE active = true
442

443
// Using filters in column configuration
444
const columns = new pgp.helpers.ColumnSet([
445
  { name: 'data', mod: ':json' },
446
  { name: 'tags', mod: ':csv' },
447
  { name: 'raw_sql', mod: '^' }
448
]);
449
```
450

451
## Types
452

453
```javascript { .api }
454
// Query parameter types
455
type QueryColumns<T> = Column<T> | ColumnSet<T> | Array<string | IColumnConfig<T> | Column<T>>
456

457
// Column descriptor for runtime information
458
interface IColumnDescriptor<T> {
459
  source: T // Source data object
460
  name: string // Column name
461
  value: any // Column value
462
  exists: boolean // Whether value exists in source
463
}
464

465
// Assignment options for column sets
466
interface IAssignOptions {
467
  source?: object // Source object configuration
468
  prefix?: string // Prefix for assignments
469
}
470

471
interface IAssignColumnsOptions {
472
  from?: string // Source table alias
473
  to?: string // Target table alias  
474
  skip?: string | string[] | ((c: Column<any>) => boolean) // Columns to skip
475
}
476

477
// Custom Type Formatting object
478
interface ICTFObject {
479
  toPostgres(a: any): any // Convert to PostgreSQL format
480
}
481

482
// Formatting symbols for custom type formatting
483
interface ICTF {
484
  toPostgres: symbol // Symbol for toPostgres method
485
  rawType: symbol // Symbol for raw type indication
486
}
487
```