tessl/npm-postgres

Fastest full featured PostgreSQL client for Node.js and Deno with tagged template literals, transactions, streaming, and logical replication support

—

Pending

Quality

Pending

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Overview

Eval results

Files

postgres.js

Name: tessl/npm-postgres
Author: tessl

postgres.js is the fastest full-featured PostgreSQL client for Node.js and Deno environments. It provides a modern API built around ES6 tagged template literals for writing SQL queries, offering both safety through parameterized queries and intuitive syntax. The library supports advanced PostgreSQL features including transactions, listen/notify, real-time subscriptions, custom types, connection pooling, and comprehensive error handling.

Package Information

Package Name: postgres
Package Type: npm
Language: JavaScript/TypeScript
Installation: npm install postgres

Core Imports

import postgres from "postgres";

For CommonJS:

const postgres = require("postgres");

With TypeScript types:

import postgres from "postgres";
// Types are included in the package

Basic Usage

import postgres from "postgres";

// Connect with connection string
const sql = postgres("postgres://username:password@host:port/database");

// Or with options object
const sql = postgres({
  host: "localhost",
  port: 5432,
  database: "mydb",
  username: "user",
  password: "pass"
});

// Execute queries using tagged template literals
const users = await sql`
  SELECT * FROM users WHERE age > ${25}
`;

// Clean up connections
await sql.end();

Architecture

postgres.js is built around several key components:

Connection Factory: The main postgres() function creates configured SQL instances with connection pooling
Tagged Template Interface: SQL queries use template literals for safe parameter binding
Connection Pool: Automatic connection management with configurable pool settings
Type System: Automatic PostgreSQL type conversion with custom type support
Transaction Support: Full transaction management including savepoints
Streaming Interface: Support for large result sets and COPY operations
Pub/Sub System: PostgreSQL LISTEN/NOTIFY and logical replication support

Capabilities

Core Querying

Essential SQL query execution using tagged template literals with automatic parameter binding and type conversion.

function postgres(options?: ConnectionOptions): Sql;
function postgres(url: string, options?: ConnectionOptions): Sql;

interface Sql {
  // Tagged template for SQL queries
  <T extends readonly any[] = Row[]>(
    template: TemplateStringsArray, 
    ...parameters: any[]
  ): PendingQuery<T>;
  
  // Raw SQL execution
  unsafe<T extends any[] = Row[]>(
    query: string, 
    parameters?: any[], 
    options?: UnsafeQueryOptions
  ): PendingQuery<T>;
  
  // Execute SQL from file
  file<T extends readonly any[] = Row[]>(
    path: string | Buffer | URL | number,
    args?: any[],
    options?: FileQueryOptions
  ): PendingQuery<T>;
}

Core Querying

Connection Management

Connection pooling, lifecycle management, and advanced connection options for production use.

interface Sql {
  // Reserve a dedicated connection
  reserve(): Promise<ReservedSql>;
  
  // Close connections gracefully
  end(options?: { timeout?: number }): Promise<void>;
  
  // Access connection options and parameters
  readonly options: ParsedOptions;
  readonly parameters: ConnectionParameters;
}

interface ReservedSql extends Sql {
  release(): void;
}

Connection Management

Transactions

Complete transaction support including nested transactions via savepoints and two-phase commit preparation.

interface Sql {
  begin<T>(
    fn: (sql: TransactionSql) => T | Promise<T>
  ): Promise<T>;
  
  begin<T>(
    options: string,
    fn: (sql: TransactionSql) => T | Promise<T>
  ): Promise<T>;
}

interface TransactionSql extends Sql {
  savepoint<T>(
    fn: (sql: TransactionSql) => T | Promise<T>
  ): Promise<T>;
  
  savepoint<T>(
    name: string,
    fn: (sql: TransactionSql) => T | Promise<T>
  ): Promise<T>;
  
  prepare(name: string): void;
}

Transactions

Type System & Parameters

Type-safe parameter binding, custom PostgreSQL types, and automatic type conversion.

interface Sql {
  // Create typed parameters
  typed<T>(value: T, oid: number): Parameter<T>;
  types: typeof typed;
  
  // Helper methods for common types
  array<T>(value: T[], type?: number): ArrayParameter<T>;
  json(value: any): Parameter;
}

interface Parameter<T> {
  readonly type: number;
  readonly value: string | null;
  readonly raw: T | null;
}

interface ArrayParameter<T> extends Parameter<T[]> {
  readonly array: true;
}

Type System

Query Processing

Advanced query execution modes including streaming, cursors, and result format options.

interface PendingQuery<T> extends Promise<RowList<T>> {
  // Execution modes
  simple(): this;
  execute(): this;
  describe(): PendingDescribeQuery;
  cancel(): void;
  
  // Result processing
  cursor(rows?: number): AsyncIterable<NonNullable<T[number]>[]>;
  forEach(cb: (row: NonNullable<T[number]>, result: ExecutionResult) => void): Promise<ExecutionResult>;
  values(): PendingValuesQuery<T>;
  raw(): PendingRawQuery<T>;
  
  // Streaming
  readable(): Promise<Readable>;
  writable(): Promise<Writable>;
}

Query Processing

Notifications & Pub/Sub

PostgreSQL LISTEN/NOTIFY support for real-time messaging and event-driven architectures.

interface Sql {
  listen(
    channel: string,
    onnotify: (payload: string) => void,
    onlisten?: () => void
  ): ListenRequest;
  
  notify(channel: string, payload: string): PendingRequest;
}

interface ListenRequest extends Promise<ListenMeta> {}

interface ListenMeta {
  state: ConnectionState;
  unlisten(): Promise<void>;
}

Notifications

Logical Replication

Real-time data streaming through PostgreSQL logical replication for change data capture.

interface Sql {
  subscribe(
    event: string,
    cb: (row: Row | null, info: ReplicationEvent) => void,
    onsubscribe?: () => void,
    onerror?: () => any
  ): Promise<SubscriptionHandle>;
}

type ReplicationEvent =
  | { command: 'insert', relation: RelationInfo }
  | { command: 'update', relation: RelationInfo, key: boolean, old: Row | null }
  | { command: 'delete', relation: RelationInfo, key: boolean };

interface SubscriptionHandle {
  unsubscribe(): void;
}

Logical Replication

Large Objects

PostgreSQL large object support for handling binary data and files larger than 1GB.

interface Sql {
  largeObject(
    oid?: number,
    mode?: number
  ): Promise<LargeObject>;
}

interface LargeObject {
  readable(options?: ReadableOptions): Promise<Readable>;
  writable(options?: WritableOptions): Promise<Writable>;
  close(): Promise<void>;
  tell(): Promise<void>;
  read(size: number): Promise<void>;
  write(buffer: Uint8Array): Promise<[{ data: Uint8Array }]>;
  truncate(size: number): Promise<void>;
  seek(offset: number, whence?: number): Promise<void>;
  size(): Promise<[{ position: bigint, size: bigint }]>;
}

Large Objects

Error Handling

Comprehensive PostgreSQL error handling with detailed error information and custom error types.

class PostgresError extends Error {
  readonly name: 'PostgresError';
  readonly severity_local: string;
  readonly severity: string;
  readonly code: string;
  readonly position: string;
  readonly file: string;
  readonly line: string;
  readonly routine: string;
  readonly detail?: string;
  readonly hint?: string;
  readonly schema_name?: string;
  readonly table_name?: string;
  readonly column_name?: string;
  readonly constraint_name?: string;
  readonly query?: string;
  readonly parameters?: any[];
}

Error Handling

Configuration Options

interface ConnectionOptions {
  // Connection settings
  host?: string | string[];
  port?: number | number[];
  database?: string;
  user?: string;
  password?: string | (() => string | Promise<string>);
  
  // SSL configuration  
  ssl?: 'require' | 'allow' | 'prefer' | 'verify-full' | boolean | object;
  
  // Pool settings
  max?: number; // default: 10
  idle_timeout?: number;
  connect_timeout?: number;
  max_lifetime?: number;
  
  // Query settings
  prepare?: boolean; // default: true
  fetch_types?: boolean; // default: true
  debug?: boolean | ((connection: number, query: string, parameters: any[]) => void);
  
  // Transform hooks
  transform?: {
    undefined?: any;
    column?: ((column: string) => string) | TransformConfig;
    value?: ((value: any) => any) | TransformConfig;
    row?: ((row: Row) => any) | TransformConfig;
  };
  
  // Event handlers
  onnotice?: (notice: Notice) => void;
  onnotify?: (channel: string, payload: string) => void;
  onparameter?: (key: string, value: any) => void;
  onclose?: (connectionId: number) => void;
}

Static Utilities

Static utility functions and objects available directly on the postgres function.

// Access static utilities directly from the postgres function
import postgres from "postgres";

// Case conversion utilities  
postgres.toPascal(str: string): string;
postgres.fromPascal(str: string): string;
postgres.toCamel(str: string): string;
postgres.fromCamel(str: string): string;
postgres.toKebab(str: string): string;
postgres.fromKebab(str: string): string;

// Namespace objects with column/value transformers
postgres.pascal: {
  column: { from: (str: string) => string; to: (str: string) => string };
  value: { from: (str: unknown, column: Column) => string };
};

postgres.camel: {
  column: { from: (str: string) => string; to: (str: string) => string };
  value: { from: (str: unknown, column: Column) => string };
};

postgres.kebab: {
  column: { from: (str: string) => string; to: (str: string) => string };
  value: { from: (str: unknown, column: Column) => string };
};

// Error class
postgres.PostgresError: typeof PostgresError;

// Built-in type handlers
postgres.BigInt: PostgresType<bigint>;

Usage Examples:

import postgres from "postgres";

// Use case conversion utilities
const snakeCase = "user_name";
const camelCase = postgres.toCamel(snakeCase); // "userName"
const pascalCase = postgres.toPascal(snakeCase); // "UserName"

// Use transform objects for column/value conversion
const sql = postgres({
  transform: {
    column: postgres.camel.column.from,
    value: postgres.camel.value.from
  }
});

// Access error class for instanceof checks
try {
  await sql`SELECT * FROM nonexistent`;
} catch (error) {
  if (error instanceof postgres.PostgresError) {
    console.log('PostgreSQL error:', error.code);
  }
}

Constants

Query control constants available on the SQL instance.

interface Sql {
  // Query termination constants
  readonly CLOSE: {};
  readonly END: typeof CLOSE;  // Alias for CLOSE
}

Usage Examples:

const sql = postgres();

// Use CLOSE to terminate cursors early
const cursor = sql`SELECT * FROM large_table`.cursor(100);
for await (const rows of cursor) {
  if (shouldStop) {
    await cursor.return(sql.CLOSE);
    break;
  }
  processRows(rows);
}

Common Types

interface Row {
  [column: string]: any;
}

interface Column<T extends string = string> {
  name: T;
  type: number;
  table: number;
  number: number;
  parser?: (raw: string) => unknown;
}

interface ConnectionState {
  status: string;
  pid: number;
  secret: number;
}

interface Notice {
  [field: string]: string;
}

interface PostgresType<T = any> {
  to: number;
  from: number[];
  serialize: (value: T) => unknown;
  parse: (raw: any) => T;
}

Install with Tessl CLI

npx tessl i tessl/npm-postgres

Workspace: tessl
Visibility: Public
Created: 6 months ago
Last updated: about 1 month ago
Describes: pkg:npm/postgres@3.4.x
Publish Source: CLI
Badge