or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

configuration-utilities.mdconnection-management.mddatabase-operations.mderror-handling.mdindex.mdquery-files.mdquery-formatting.mdtasks-transactions.md
tile.json

tessl/npm-pg-promise

PostgreSQL interface for Node.js built on top of node-postgres, offering 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.

Workspace
tessl
Visibility
Public
Created
Last updated
Describes
npmpkg:npm/pg-promise@12.1.x

To install, run

npx @tessl/cli install tessl/npm-pg-promise@12.1.0

index.mddocs/

pg-promise

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.

Package Information

  • Package Name: pg-promise
  • Package Type: npm
  • Language: JavaScript (with TypeScript support)
  • Installation: npm install pg-promise

Core Imports

const pgp = require('pg-promise')(options);
const db = pgp(connectionString);

For TypeScript:

import pgPromise from 'pg-promise';
const pgp = pgPromise(options);
const db = pgp(connectionString);

Basic Usage

const pgp = require('pg-promise')();
const db = pgp('postgres://user:password@host:port/database');

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

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

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

// Task for multiple queries with shared connection
await db.task(async t => {
  const users = await t.any('SELECT * FROM users');
  const profiles = await t.any('SELECT * FROM user_profiles');
  return { users, profiles };
});

Architecture

pg-promise is built around several key components:

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

Capabilities

Database Operations

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

// Result-specific query methods
db.none(query, values?): Promise<null>
db.one(query, values?, cb?, thisArg?): Promise<T>
db.oneOrNone(query, values?, cb?, thisArg?): Promise<T | null>
db.many(query, values?): Promise<T[]>
db.manyOrNone(query, values?): Promise<T[]>
db.any(query, values?): Promise<T[]>

// Advanced query methods
db.result(query, values?, cb?, thisArg?): Promise<IResultExt>
db.multiResult(query, values?): Promise<IResult[]>
db.multi(query, values?): Promise<Array<T[]>>
db.func(funcName, values?, qrm?): Promise<T>
db.proc(procName, values?, cb?, thisArg?): Promise<T | null>

Database Operations

Task and Transaction Management

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

// Tasks - shared connection for multiple queries
db.task(cb): Promise<T>
db.task(tag, cb): Promise<T>
db.task(options, cb): Promise<T>
db.taskIf(options, cb): Promise<T>

// Transactions - automatic transaction handling
db.tx(cb): Promise<T>
db.tx(tag, cb): Promise<T>
db.tx(options, cb): Promise<T>
db.txIf(options, cb): Promise<T>

interface ITaskContext {
  readonly connected: boolean;
  readonly inTransaction: boolean;
  readonly level: number;
  readonly useCount: number;
  readonly isTX: boolean;
  readonly start: Date;
  readonly tag: any;
  readonly dc: any;
  readonly finish?: Date;
  readonly duration?: number;
  readonly success?: boolean;
  readonly result?: any;
  readonly txLevel?: number;
  readonly serverVersion: string;
}

Tasks and Transactions

Query Formatting and Helpers

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

// Query formatting namespace (pgp.as)
pgp.as.format(query, values?, options?): string
pgp.as.name(name): string
pgp.as.value(value): string
pgp.as.csv(values): string
pgp.as.json(data, raw?): string

// Query helpers namespace (pgp.helpers)
pgp.helpers.insert(data, columns?, table?): string
pgp.helpers.update(data, columns?, table?, options?): string
pgp.helpers.values(data, columns?): string
pgp.helpers.sets(data, columns?): string
pgp.helpers.concat(queries): string

Query Formatting

Query Files and Prepared Statements

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

// Query File class
class QueryFile {
  constructor(file: string, options?: IQueryFileOptions)
  readonly error: Error
  readonly file: string
  readonly options: any
  prepare(): void
  toString(level?: number): string
}

// Prepared Statement class  
class PreparedStatement {
  constructor(options?: IPreparedStatement)
  name: string
  text: string | QueryFile
  values: any[]
  binary: boolean
  rowMode: void | 'array'
  rows: number
  types: ITypes
  parse(): IPreparedParsed | PreparedStatementError
  toString(level?: number): string
}

// Parameterized Query class
class ParameterizedQuery {
  constructor(options?: string | QueryFile | IParameterizedQuery)
  text: string | QueryFile
  values: any[]
  binary: boolean
  rowMode: void | 'array'
  types: ITypes
  parse(): IParameterizedParsed | ParameterizedQueryError
  toString(level?: number): string
}

Query Files and Prepared Statements

Connection Management

Connection pooling, direct connections, and connection lifecycle management.

// Connection method
db.connect(options?): Promise<IConnected>

interface IConnectionOptions {
  direct?: boolean
  onLost?(err: any, e: ILostContext): void
}

interface IConnected {
  readonly client: IClient
  done(kill?: boolean): void | Promise<void>
  // Includes all database query methods
}

Connection Management

Error Handling

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

// Error namespace (pgp.errors)
class QueryResultError extends Error {
  name: string
  message: string
  stack: string
  result: IResult
  received: number
  code: queryResultErrorCode
  query: string
  values: any
  toString(): string
}

class QueryFileError extends Error {
  name: string
  message: string  
  stack: string
  file: string
  options: IQueryFileOptions
  error: SQLParsingError
  toString(level?: number): string
}

enum queryResultErrorCode {
  noData = 0,
  notEmpty = 1,
  multiple = 2
}

Error Handling

Configuration and Utilities

Library initialization options, utility functions, and transaction modes.

// Main initialization function
function pgPromise(options?: IInitOptions): IMain

interface IInitOptions {
  pgFormatting?: boolean
  pgNative?: boolean
  capSQL?: boolean
  schema?: ValidSchema | ((dc: any) => ValidSchema)
  noWarnings?: boolean
  connect?(e: ConnectEvent): void
  disconnect?(e: DisconnectEvent): void
  query?(e: IEventContext): void
  receive?(e: ReceiveEvent): void
  task?(e: IEventContext): void
  transact?(e: IEventContext): void
  error?(err: any, e: IEventContext): void
  extend?(obj: IDatabase, dc: any): void
}

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

// Transaction modes (pgp.txMode)
enum isolationLevel {
  none = 0,
  serializable = 1,
  repeatableRead = 2,
  readCommitted = 3
}

class TransactionMode {
  constructor(options?: TransactionModeOptions)
  begin(cap?: boolean): string
}

Configuration and Utilities

Types

// Main types
type QueryParam = string | QueryFile | PreparedStatement | ParameterizedQuery | ((values?: any) => QueryParam)
type ValidSchema = string | string[] | null | void

// Query Result Mask
enum queryResult {
  one = 1,
  many = 2,
  none = 4,
  any = 6
}

// Core interfaces
interface IDatabase {
  // Query methods
  query(query: QueryParam, values?: any, qrm?: queryResult): Promise<any>
  none(query: QueryParam, values?: any): Promise<null>
  one(query: QueryParam, values?: any, cb?: Function, thisArg?: any): Promise<any>
  oneOrNone(query: QueryParam, values?: any, cb?: Function, thisArg?: any): Promise<any>
  many(query: QueryParam, values?: any): Promise<any[]>
  manyOrNone(query: QueryParam, values?: any): Promise<any[]>
  any(query: QueryParam, values?: any): Promise<any[]>
  
  // Advanced methods
  result(query: QueryParam, values?: any, cb?: Function, thisArg?: any): Promise<any>
  multiResult(query: QueryParam, values?: any): Promise<IResult[]>
  multi(query: QueryParam, values?: any): Promise<Array<any[]>>
  stream(qs: ReadableStream, init: Function): Promise<StreamResult>
  func(funcName: string, values?: any, qrm?: queryResult): Promise<any>
  proc(procName: string, values?: any, cb?: Function, thisArg?: any): Promise<any>
  map(query: QueryParam, values: any, cb: Function, thisArg?: any): Promise<any[]>
  each(query: QueryParam, values: any, cb: Function, thisArg?: any): Promise<any[]>
  
  // Tasks and transactions
  task(cb: Function): Promise<any>
  task(tag: string | number, cb: Function): Promise<any>
  task(options: object, cb: Function): Promise<any>
  taskIf(options: object, cb: Function): Promise<any>
  tx(cb: Function): Promise<any>
  tx(tag: string | number, cb: Function): Promise<any>
  tx(options: object, cb: Function): Promise<any>
  txIf(options: object, cb: Function): Promise<any>
  
  // Connection
  connect(options?: IConnectionOptions): Promise<IConnected>
  
  // Read-only properties
  readonly $config: ILibConfig
  readonly $cn: string | IConnectionParameters
  readonly $dc: any
  readonly $pool: IPool
}

interface IMain {
  // Database factory function
  (cn: string | IConnectionParameters, dc?: any): IDatabase
  
  // Static properties
  readonly PreparedStatement: typeof PreparedStatement
  readonly ParameterizedQuery: typeof ParameterizedQuery
  readonly QueryFile: typeof QueryFile
  readonly queryResult: typeof queryResult
  readonly minify: typeof pgMinify
  readonly spex: ISpex
  readonly errors: typeof errors
  readonly utils: IUtils
  readonly txMode: typeof txMode
  readonly helpers: IHelpers
  readonly as: IFormatting
  readonly pg: typeof pg
  
  end(): void
}

interface StreamResult {
  processed: number
  duration: number
}