0
# pg-promise
1

2
pg-promise is a comprehensive PostgreSQL interface for Node.js built on top of node-postgres. It provides automatic connection management, promise-based API, automatic transaction handling with support for nested transactions and savepoints, advanced query formatting with named parameters and filtering capabilities, task and transaction management with conditional execution, query file loading and processing, custom type formatting support, and comprehensive error handling.
3

4
## Package Information
5

6
- **Package Name**: pg-promise
7
- **Package Type**: npm
8
- **Language**: JavaScript (with TypeScript support)
9
- **Installation**: `npm install pg-promise`
10

11
## Core Imports
12

13
```javascript
14
const pgp = require('pg-promise')(options);
15
const db = pgp(connectionString);
16
```
17

18
For TypeScript:
19

20
```typescript
21
import pgPromise from 'pg-promise';
22
const pgp = pgPromise(options);
23
const db = pgp(connectionString);
24
```
25

26
## Basic Usage
27

28
```javascript
29
const pgp = require('pg-promise')();
30
const db = pgp('postgres://user:password@host:port/database');
31

32
// Simple query
33
const users = await db.any('SELECT * FROM users WHERE active = $1', [true]);
34

35
// Result-specific methods
36
const user = await db.one('SELECT * FROM users WHERE id = $1', [123]);
37
const newId = await db.one('INSERT INTO users(name, email) VALUES($1, $2) RETURNING id', 
38
  ['John Doe', 'john@example.com'], r => r.id);
39

40
// Transaction
41
await db.tx(async t => {
42
  const userId = await t.one('INSERT INTO users(name) VALUES($1) RETURNING id', ['Jane'], r => r.id);
43
  await t.none('INSERT INTO user_profiles(user_id, bio) VALUES($1, $2)', [userId, 'Engineer']);
44
});
45

46
// Task for multiple queries with shared connection
47
await db.task(async t => {
48
  const users = await t.any('SELECT * FROM users');
49
  const profiles = await t.any('SELECT * FROM user_profiles');
50
  return { users, profiles };
51
});
52
```
53

54
## Architecture
55

56
pg-promise is built around several key components:
57

58
- **Database Factory**: Main initialization function that creates database instances
59
- **Connection Management**: Automatic connection pooling and lifecycle management
60
- **Query Interface**: Result-specific methods (`none`, `one`, `many`, etc.) for type-safe query execution
61
- **Transaction System**: Automatic transaction handling with nested transaction support via savepoints
62
- **Task System**: Shared connection management for multiple queries
63
- **Query Formatting**: Advanced parameter substitution with named parameters and formatting filters
64
- **Helper System**: Query generation utilities for common operations (INSERT, UPDATE, etc.)
65
- **Error Handling**: Comprehensive error types for different failure scenarios
66

67
## Capabilities
68

69
### Database Operations
70

71
Core database query methods with result-specific interfaces for type-safe query execution.
72

73
```javascript { .api }
74
// Result-specific query methods
75
db.none(query, values?): Promise<null>
76
db.one(query, values?, cb?, thisArg?): Promise<T>
77
db.oneOrNone(query, values?, cb?, thisArg?): Promise<T | null>
78
db.many(query, values?): Promise<T[]>
79
db.manyOrNone(query, values?): Promise<T[]>
80
db.any(query, values?): Promise<T[]>
81

82
// Advanced query methods
83
db.result(query, values?, cb?, thisArg?): Promise<IResultExt>
84
db.multiResult(query, values?): Promise<IResult[]>
85
db.multi(query, values?): Promise<Array<T[]>>
86
db.func(funcName, values?, qrm?): Promise<T>
87
db.proc(procName, values?, cb?, thisArg?): Promise<T | null>
88
```
89

90
[Database Operations](./database-operations.md)
91

92
### Task and Transaction Management
93

94
Task and transaction management for shared connections and automatic transaction handling.
95

96
```javascript { .api }
97
// Tasks - shared connection for multiple queries
98
db.task(cb): Promise<T>
99
db.task(tag, cb): Promise<T>
100
db.task(options, cb): Promise<T>
101
db.taskIf(options, cb): Promise<T>
102

103
// Transactions - automatic transaction handling
104
db.tx(cb): Promise<T>
105
db.tx(tag, cb): Promise<T>
106
db.tx(options, cb): Promise<T>
107
db.txIf(options, cb): Promise<T>
108

109
interface ITaskContext {
110
  readonly connected: boolean;
111
  readonly inTransaction: boolean;
112
  readonly level: number;
113
  readonly useCount: number;
114
  readonly isTX: boolean;
115
  readonly start: Date;
116
  readonly tag: any;
117
  readonly dc: any;
118
  readonly finish?: Date;
119
  readonly duration?: number;
120
  readonly success?: boolean;
121
  readonly result?: any;
122
  readonly txLevel?: number;
123
  readonly serverVersion: string;
124
}
125
```
126

127
[Tasks and Transactions](./tasks-transactions.md)
128

129
### Query Formatting and Helpers
130

131
Advanced query formatting with named parameters, formatting filters, and query generation helpers.
132

133
```javascript { .api }
134
// Query formatting namespace (pgp.as)
135
pgp.as.format(query, values?, options?): string
136
pgp.as.name(name): string
137
pgp.as.value(value): string
138
pgp.as.csv(values): string
139
pgp.as.json(data, raw?): string
140

141
// Query helpers namespace (pgp.helpers)
142
pgp.helpers.insert(data, columns?, table?): string
143
pgp.helpers.update(data, columns?, table?, options?): string
144
pgp.helpers.values(data, columns?): string
145
pgp.helpers.sets(data, columns?): string
146
pgp.helpers.concat(queries): string
147
```
148

149
[Query Formatting](./query-formatting.md)
150

151
### Query Files and Prepared Statements
152

153
SQL file management and prepared statement support for better query organization.
154

155
```javascript { .api }
156
// Query File class
157
class QueryFile {
158
  constructor(file: string, options?: IQueryFileOptions)
159
  readonly error: Error
160
  readonly file: string
161
  readonly options: any
162
  prepare(): void
163
  toString(level?: number): string
164
}
165

166
// Prepared Statement class  
167
class PreparedStatement {
168
  constructor(options?: IPreparedStatement)
169
  name: string
170
  text: string | QueryFile
171
  values: any[]
172
  binary: boolean
173
  rowMode: void | 'array'
174
  rows: number
175
  types: ITypes
176
  parse(): IPreparedParsed | PreparedStatementError
177
  toString(level?: number): string
178
}
179

180
// Parameterized Query class
181
class ParameterizedQuery {
182
  constructor(options?: string | QueryFile | IParameterizedQuery)
183
  text: string | QueryFile
184
  values: any[]
185
  binary: boolean
186
  rowMode: void | 'array'
187
  types: ITypes
188
  parse(): IParameterizedParsed | ParameterizedQueryError
189
  toString(level?: number): string
190
}
191
```
192

193
[Query Files and Prepared Statements](./query-files.md)
194

195
### Connection Management
196

197
Connection pooling, direct connections, and connection lifecycle management.
198

199
```javascript { .api }
200
// Connection method
201
db.connect(options?): Promise<IConnected>
202

203
interface IConnectionOptions {
204
  direct?: boolean
205
  onLost?(err: any, e: ILostContext): void
206
}
207

208
interface IConnected {
209
  readonly client: IClient
210
  done(kill?: boolean): void | Promise<void>
211
  // Includes all database query methods
212
}
213
```
214

215
[Connection Management](./connection-management.md)
216

217
### Error Handling
218

219
Comprehensive error types for different failure scenarios with detailed error information.
220

221
```javascript { .api }
222
// Error namespace (pgp.errors)
223
class QueryResultError extends Error {
224
  name: string
225
  message: string
226
  stack: string
227
  result: IResult
228
  received: number
229
  code: queryResultErrorCode
230
  query: string
231
  values: any
232
  toString(): string
233
}
234

235
class QueryFileError extends Error {
236
  name: string
237
  message: string  
238
  stack: string
239
  file: string
240
  options: IQueryFileOptions
241
  error: SQLParsingError
242
  toString(level?: number): string
243
}
244

245
enum queryResultErrorCode {
246
  noData = 0,
247
  notEmpty = 1,
248
  multiple = 2
249
}
250
```
251

252
[Error Handling](./error-handling.md)
253

254
### Configuration and Utilities
255

256
Library initialization options, utility functions, and transaction modes.
257

258
```javascript { .api }
259
// Main initialization function
260
function pgPromise(options?: IInitOptions): IMain
261

262
interface IInitOptions {
263
  pgFormatting?: boolean
264
  pgNative?: boolean
265
  capSQL?: boolean
266
  schema?: ValidSchema | ((dc: any) => ValidSchema)
267
  noWarnings?: boolean
268
  connect?(e: ConnectEvent): void
269
  disconnect?(e: DisconnectEvent): void
270
  query?(e: IEventContext): void
271
  receive?(e: ReceiveEvent): void
272
  task?(e: IEventContext): void
273
  transact?(e: IEventContext): void
274
  error?(err: any, e: IEventContext): void
275
  extend?(obj: IDatabase, dc: any): void
276
}
277

278
// Utility functions (pgp.utils)
279
pgp.utils.camelize(text: string): string
280
pgp.utils.camelizeVar(text: string): string
281
pgp.utils.enumSql(dir: string, options?, cb?): object
282
pgp.utils.taskArgs(args: Arguments): Array
283

284
// Transaction modes (pgp.txMode)
285
enum isolationLevel {
286
  none = 0,
287
  serializable = 1,
288
  repeatableRead = 2,
289
  readCommitted = 3
290
}
291

292
class TransactionMode {
293
  constructor(options?: TransactionModeOptions)
294
  begin(cap?: boolean): string
295
}
296
```
297

298
[Configuration and Utilities](./configuration-utilities.md)
299

300
## Types
301

302
```javascript { .api }
303
// Main types
304
type QueryParam = string | QueryFile | PreparedStatement | ParameterizedQuery | ((values?: any) => QueryParam)
305
type ValidSchema = string | string[] | null | void
306

307
// Query Result Mask
308
enum queryResult {
309
  one = 1,
310
  many = 2,
311
  none = 4,
312
  any = 6
313
}
314

315
// Core interfaces
316
interface IDatabase {
317
  // Query methods
318
  query(query: QueryParam, values?: any, qrm?: queryResult): Promise<any>
319
  none(query: QueryParam, values?: any): Promise<null>
320
  one(query: QueryParam, values?: any, cb?: Function, thisArg?: any): Promise<any>
321
  oneOrNone(query: QueryParam, values?: any, cb?: Function, thisArg?: any): Promise<any>
322
  many(query: QueryParam, values?: any): Promise<any[]>
323
  manyOrNone(query: QueryParam, values?: any): Promise<any[]>
324
  any(query: QueryParam, values?: any): Promise<any[]>
325
  
326
  // Advanced methods
327
  result(query: QueryParam, values?: any, cb?: Function, thisArg?: any): Promise<any>
328
  multiResult(query: QueryParam, values?: any): Promise<IResult[]>
329
  multi(query: QueryParam, values?: any): Promise<Array<any[]>>
330
  stream(qs: ReadableStream, init: Function): Promise<StreamResult>
331
  func(funcName: string, values?: any, qrm?: queryResult): Promise<any>
332
  proc(procName: string, values?: any, cb?: Function, thisArg?: any): Promise<any>
333
  map(query: QueryParam, values: any, cb: Function, thisArg?: any): Promise<any[]>
334
  each(query: QueryParam, values: any, cb: Function, thisArg?: any): Promise<any[]>
335
  
336
  // Tasks and transactions
337
  task(cb: Function): Promise<any>
338
  task(tag: string | number, cb: Function): Promise<any>
339
  task(options: object, cb: Function): Promise<any>
340
  taskIf(options: object, cb: Function): Promise<any>
341
  tx(cb: Function): Promise<any>
342
  tx(tag: string | number, cb: Function): Promise<any>
343
  tx(options: object, cb: Function): Promise<any>
344
  txIf(options: object, cb: Function): Promise<any>
345
  
346
  // Connection
347
  connect(options?: IConnectionOptions): Promise<IConnected>
348
  
349
  // Read-only properties
350
  readonly $config: ILibConfig
351
  readonly $cn: string | IConnectionParameters
352
  readonly $dc: any
353
  readonly $pool: IPool
354
}
355

356
interface IMain {
357
  // Database factory function
358
  (cn: string | IConnectionParameters, dc?: any): IDatabase
359
  
360
  // Static properties
361
  readonly PreparedStatement: typeof PreparedStatement
362
  readonly ParameterizedQuery: typeof ParameterizedQuery
363
  readonly QueryFile: typeof QueryFile
364
  readonly queryResult: typeof queryResult
365
  readonly minify: typeof pgMinify
366
  readonly spex: ISpex
367
  readonly errors: typeof errors
368
  readonly utils: IUtils
369
  readonly txMode: typeof txMode
370
  readonly helpers: IHelpers
371
  readonly as: IFormatting
372
  readonly pg: typeof pg
373
  
374
  end(): void
375
}
376

377
interface StreamResult {
378
  processed: number
379
  duration: number
380
}
381
```