or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

client.mdconnection-string.mdcursor.mdindex.mdpool.mdquery-stream.mdquery.mdtypes.mdutilities.md
tile.json

tessl/npm-pg

PostgreSQL client library for Node.js with both pure JavaScript and optional native libpq bindings

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

To install, run

npx @tessl/cli install tessl/npm-pg@8.16.0

index.mddocs/

pg

pg is a comprehensive PostgreSQL client library for Node.js that provides both pure JavaScript and optional native libpq bindings with a unified API. It features connection pooling, extensible data-type coercion, parameterized queries with query plan caching, asynchronous notifications, and bulk data operations with full transaction support.

Package Information

  • Package Name: pg
  • Package Type: npm
  • Language: JavaScript
  • Installation: npm install pg
  • Optional Native Bindings: npm install pg pg-native

Core Imports

const { Client, Pool, Query } = require('pg');

For ESM:

import { Client, Pool, Query } from 'pg';

For native bindings:

const { Client } = require('pg').native;
// or
import pg from 'pg';
const Client = pg.native.Client;

Basic Usage

Simple Client Connection

const { Client } = require('pg');

const client = new Client({
  user: 'dbuser',
  host: 'database.server.com',
  database: 'mydb',
  password: 'secretpassword',
  port: 5432,
});

await client.connect();

const res = await client.query('SELECT $1::text as message', ['Hello world!']);
console.log(res.rows[0].message); // Hello world!

await client.end();

Connection Pool Usage

const { Pool } = require('pg');

const pool = new Pool({
  user: 'dbuser',
  host: 'database.server.com',
  database: 'mydb',
  password: 'secretpassword',
  port: 5432,
});

const res = await pool.query('SELECT NOW()');
console.log(res.rows[0]);

await pool.end();

Architecture

pg is built around several key components:

  • Client: Core connection management and query execution
  • Pool: Connection pooling for resource optimization
  • Query: Query configuration and execution handling
  • Connection: Low-level PostgreSQL protocol implementation
  • Type System: Extensible type conversion between JavaScript and PostgreSQL
  • Utilities: Helper functions for escaping and query normalization

Capabilities

Client Management

Core PostgreSQL client functionality for establishing connections, executing queries, and managing database transactions.

class Client extends EventEmitter {
  constructor(config?: ClientConfig);
  connect(callback?: (err: Error) => void): Promise<void>;
  query(text: string, values?: any[], callback?: QueryCallback): Promise<QueryResult>;
  query(config: QueryConfig, callback?: QueryCallback): Promise<QueryResult>;
  end(callback?: (err: Error) => void): Promise<void>;
}

interface ClientConfig {
  user?: string;
  password?: string;
  host?: string;
  database?: string;
  port?: number;
  connectionString?: string;
  ssl?: boolean | object;
  binary?: boolean;
  client_encoding?: string;
  application_name?: string;
  statement_timeout?: number | false;
  query_timeout?: number | false;
  connectionTimeoutMillis?: number;
  keepAlive?: boolean;
  keepAliveInitialDelayMillis?: number;
}

Client Management

Connection Pooling

Connection pool management for optimizing database resource usage and handling concurrent connections.

class Pool extends EventEmitter {
  constructor(config?: PoolConfig);
  connect(): Promise<PoolClient>;
  query(text: string, values?: any[], callback?: QueryCallback): Promise<QueryResult>;
  query(config: QueryConfig, callback?: QueryCallback): Promise<QueryResult>;
  end(): Promise<void>;
}

interface PoolConfig extends ClientConfig {
  max?: number;
  min?: number;
  idleTimeoutMillis?: number;
  connectionTimeoutMillis?: number;
  maxUses?: number;
  allowExitOnIdle?: boolean;
}

Connection Pooling

Query Execution

Query configuration, parameter binding, and result handling with support for prepared statements and streaming.

class Query extends EventEmitter {
  constructor(config: QueryConfig, values?: any[], callback?: QueryCallback);
}

interface QueryConfig {
  text: string;
  values?: any[];
  name?: string;
  rowMode?: 'array' | 'object';
  types?: TypeOverrides;
  binary?: boolean;
  portal?: string;
  rows?: number;
}

interface QueryResult {
  command: string;
  rowCount: number;
  oid: number;
  rows: any[];
  fields: FieldDef[];
}

Query Execution

Type System

Type conversion system for handling data transformations between JavaScript and PostgreSQL types.

interface types {
  getTypeParser(oid: number, format?: string): (value: string) => any;
  setTypeParser(oid: number, format: string, parseFn: (value: string) => any): void;
  setTypeParser(oid: number, parseFn: (value: string) => any): void;
}

class TypeOverrides {
  constructor(userTypes?: types);
  setTypeParser(oid: number, format: string, parseFn: (value: string) => any): void;
  getTypeParser(oid: number, format?: string): (value: string) => any;
}

Type System

Utilities

Utility functions for SQL escaping, parameter normalization, and configuration management.

function escapeIdentifier(str: string): string;
function escapeLiteral(str: string): string;

const defaults: {
  host: string;
  user: string;
  database: string;
  password: string;
  port: number;
  rows: number;
  binary: boolean;
  max: number;
  idleTimeoutMillis: number;
  client_encoding: string;
  ssl: boolean;
  application_name: string;
  statement_timeout: number | false;
  query_timeout: number | false;
  connect_timeout: number;
};

Utilities

Connection String Parsing

Parse and normalize PostgreSQL connection strings with comprehensive SSL and configuration support.

function parse(connectionString: string, options?: ParseOptions): ConnectionOptions;
function toClientConfig(config: ConnectionOptions): ClientConfig;
function parseIntoClientConfig(connectionString: string): ClientConfig;

Connection String Parsing

Query Cursors

Server-side cursors for memory-efficient processing of large result sets without loading all rows into memory.

class Cursor extends EventEmitter {
  constructor(text: string, values?: any[], config?: CursorConfig);
  read(rows: number, callback?: QueryCallback): Promise<any[]>;
  close(callback?: (err: Error | null) => void): Promise<void>;
}

Query Cursors

Query Streaming

Node.js Readable stream interface for PostgreSQL query results, enabling streaming processing with standard stream APIs.

class QueryStream extends Readable {
  constructor(text: string, values?: any[], config?: QueryStreamConfig);
}

Query Streaming

Error Handling

pg provides comprehensive error handling through the DatabaseError class and standard EventEmitter patterns:

class DatabaseError extends Error {
  severity: string;
  code: string;
  detail: string;
  hint: string;
  position: string;
  internalPosition: string;
  internalQuery: string;
  where: string;
  schema: string;
  table: string;
  column: string;
  dataType: string;
  constraint: string;
  file: string;
  line: string;
  routine: string;
}

Additional Exported Classes

Result

Query result container with metadata and row data.

class Result {
  constructor(rowMode?: string, types?: TypeOverrides);
  addCommandComplete(msg: any): void;
  command: string | null;
  rowCount: number | null;
  oid: number | null;
  rows: any[];
  fields: FieldDef[];
}

Connection

Low-level PostgreSQL protocol connection handler (advanced usage).

class Connection extends EventEmitter {
  constructor(config?: ConnectionConfig);
  connect(port: number, host: string): void;
  query(text: string): void;
  end(): void;
}

Common Types

type QueryCallback = (err: Error | null, result: QueryResult) => void;

interface FieldDef {
  name: string;
  tableID: number;
  columnID: number;
  dataTypeID: number;
  dataTypeSize: number;
  dataTypeModifier: number;
  format: string;
}

interface PoolClient extends Client {
  release(err?: Error | boolean): void;
}