0
# postgres.js
1

2
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.
3

4
## Package Information
5

6
- **Package Name**: postgres
7
- **Package Type**: npm
8
- **Language**: JavaScript/TypeScript
9
- **Installation**: `npm install postgres`
10

11
## Core Imports
12

13
```javascript
14
import postgres from "postgres";
15
```
16

17
For CommonJS:
18

19
```javascript
20
const postgres = require("postgres");
21
```
22

23
With TypeScript types:
24

25
```typescript
26
import postgres from "postgres";
27
// Types are included in the package
28
```
29

30
## Basic Usage
31

32
```javascript
33
import postgres from "postgres";
34

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

38
// Or with options object
39
const sql = postgres({
40
  host: "localhost",
41
  port: 5432,
42
  database: "mydb",
43
  username: "user",
44
  password: "pass"
45
});
46

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

52
// Clean up connections
53
await sql.end();
54
```
55

56
## Architecture
57

58
postgres.js is built around several key components:
59

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

68
## Capabilities
69

70
### Core Querying
71

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

74
```javascript { .api }
75
function postgres(options?: ConnectionOptions): Sql;
76
function postgres(url: string, options?: ConnectionOptions): Sql;
77

78
interface Sql {
79
  // Tagged template for SQL queries
80
  <T extends readonly any[] = Row[]>(
81
    template: TemplateStringsArray, 
82
    ...parameters: any[]
83
  ): PendingQuery<T>;
84
  
85
  // Raw SQL execution
86
  unsafe<T extends any[] = Row[]>(
87
    query: string, 
88
    parameters?: any[], 
89
    options?: UnsafeQueryOptions
90
  ): PendingQuery<T>;
91
  
92
  // Execute SQL from file
93
  file<T extends readonly any[] = Row[]>(
94
    path: string | Buffer | URL | number,
95
    args?: any[],
96
    options?: FileQueryOptions
97
  ): PendingQuery<T>;
98
}
99
```
100

101
[Core Querying](./querying.md)
102

103
### Connection Management
104

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

107
```javascript { .api }
108
interface Sql {
109
  // Reserve a dedicated connection
110
  reserve(): Promise<ReservedSql>;
111
  
112
  // Close connections gracefully
113
  end(options?: { timeout?: number }): Promise<void>;
114
  
115
  // Access connection options and parameters
116
  readonly options: ParsedOptions;
117
  readonly parameters: ConnectionParameters;
118
}
119

120
interface ReservedSql extends Sql {
121
  release(): void;
122
}
123
```
124

125
[Connection Management](./connections.md)
126

127
### Transactions
128

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

131
```javascript { .api }
132
interface Sql {
133
  begin<T>(
134
    fn: (sql: TransactionSql) => T | Promise<T>
135
  ): Promise<T>;
136
  
137
  begin<T>(
138
    options: string,
139
    fn: (sql: TransactionSql) => T | Promise<T>
140
  ): Promise<T>;
141
}
142

143
interface TransactionSql extends Sql {
144
  savepoint<T>(
145
    fn: (sql: TransactionSql) => T | Promise<T>
146
  ): Promise<T>;
147
  
148
  savepoint<T>(
149
    name: string,
150
    fn: (sql: TransactionSql) => T | Promise<T>
151
  ): Promise<T>;
152
  
153
  prepare(name: string): void;
154
}
155
```
156

157
[Transactions](./transactions.md)
158

159
### Type System & Parameters
160

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

163
```javascript { .api }
164
interface Sql {
165
  // Create typed parameters
166
  typed<T>(value: T, oid: number): Parameter<T>;
167
  types: typeof typed;
168
  
169
  // Helper methods for common types
170
  array<T>(value: T[], type?: number): ArrayParameter<T>;
171
  json(value: any): Parameter;
172
}
173

174
interface Parameter<T> {
175
  readonly type: number;
176
  readonly value: string | null;
177
  readonly raw: T | null;
178
}
179

180
interface ArrayParameter<T> extends Parameter<T[]> {
181
  readonly array: true;
182
}
183
```
184

185
[Type System](./types.md)
186

187
### Query Processing
188

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

191
```javascript { .api }
192
interface PendingQuery<T> extends Promise<RowList<T>> {
193
  // Execution modes
194
  simple(): this;
195
  execute(): this;
196
  describe(): PendingDescribeQuery;
197
  cancel(): void;
198
  
199
  // Result processing
200
  cursor(rows?: number): AsyncIterable<NonNullable<T[number]>[]>;
201
  forEach(cb: (row: NonNullable<T[number]>, result: ExecutionResult) => void): Promise<ExecutionResult>;
202
  values(): PendingValuesQuery<T>;
203
  raw(): PendingRawQuery<T>;
204
  
205
  // Streaming
206
  readable(): Promise<Readable>;
207
  writable(): Promise<Writable>;
208
}
209
```
210

211
[Query Processing](./query-processing.md)
212

213
### Notifications & Pub/Sub
214

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

217
```javascript { .api }
218
interface Sql {
219
  listen(
220
    channel: string,
221
    onnotify: (payload: string) => void,
222
    onlisten?: () => void
223
  ): ListenRequest;
224
  
225
  notify(channel: string, payload: string): PendingRequest;
226
}
227

228
interface ListenRequest extends Promise<ListenMeta> {}
229

230
interface ListenMeta {
231
  state: ConnectionState;
232
  unlisten(): Promise<void>;
233
}
234
```
235

236
[Notifications](./notifications.md)
237

238
### Logical Replication
239

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

242
```javascript { .api }
243
interface Sql {
244
  subscribe(
245
    event: string,
246
    cb: (row: Row | null, info: ReplicationEvent) => void,
247
    onsubscribe?: () => void,
248
    onerror?: () => any
249
  ): Promise<SubscriptionHandle>;
250
}
251

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

257
interface SubscriptionHandle {
258
  unsubscribe(): void;
259
}
260
```
261

262
[Logical Replication](./replication.md)
263

264
### Large Objects
265

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

268
```javascript { .api }
269
interface Sql {
270
  largeObject(
271
    oid?: number,
272
    mode?: number
273
  ): Promise<LargeObject>;
274
}
275

276
interface LargeObject {
277
  readable(options?: ReadableOptions): Promise<Readable>;
278
  writable(options?: WritableOptions): Promise<Writable>;
279
  close(): Promise<void>;
280
  tell(): Promise<void>;
281
  read(size: number): Promise<void>;
282
  write(buffer: Uint8Array): Promise<[{ data: Uint8Array }]>;
283
  truncate(size: number): Promise<void>;
284
  seek(offset: number, whence?: number): Promise<void>;
285
  size(): Promise<[{ position: bigint, size: bigint }]>;
286
}
287
```
288

289
[Large Objects](./large-objects.md)
290

291
### Error Handling
292

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

295
```javascript { .api }
296
class PostgresError extends Error {
297
  readonly name: 'PostgresError';
298
  readonly severity_local: string;
299
  readonly severity: string;
300
  readonly code: string;
301
  readonly position: string;
302
  readonly file: string;
303
  readonly line: string;
304
  readonly routine: string;
305
  readonly detail?: string;
306
  readonly hint?: string;
307
  readonly schema_name?: string;
308
  readonly table_name?: string;
309
  readonly column_name?: string;
310
  readonly constraint_name?: string;
311
  readonly query?: string;
312
  readonly parameters?: any[];
313
}
314
```
315

316
[Error Handling](./errors.md)
317

318
## Configuration Options
319

320
```javascript { .api }
321
interface ConnectionOptions {
322
  // Connection settings
323
  host?: string | string[];
324
  port?: number | number[];
325
  database?: string;
326
  user?: string;
327
  password?: string | (() => string | Promise<string>);
328
  
329
  // SSL configuration  
330
  ssl?: 'require' | 'allow' | 'prefer' | 'verify-full' | boolean | object;
331
  
332
  // Pool settings
333
  max?: number; // default: 10
334
  idle_timeout?: number;
335
  connect_timeout?: number;
336
  max_lifetime?: number;
337
  
338
  // Query settings
339
  prepare?: boolean; // default: true
340
  fetch_types?: boolean; // default: true
341
  debug?: boolean | ((connection: number, query: string, parameters: any[]) => void);
342
  
343
  // Transform hooks
344
  transform?: {
345
    undefined?: any;
346
    column?: ((column: string) => string) | TransformConfig;
347
    value?: ((value: any) => any) | TransformConfig;
348
    row?: ((row: Row) => any) | TransformConfig;
349
  };
350
  
351
  // Event handlers
352
  onnotice?: (notice: Notice) => void;
353
  onnotify?: (channel: string, payload: string) => void;
354
  onparameter?: (key: string, value: any) => void;
355
  onclose?: (connectionId: number) => void;
356
}
357
```
358

359
## Static Utilities
360

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

363
```javascript { .api }
364
// Access static utilities directly from the postgres function
365
import postgres from "postgres";
366

367
// Case conversion utilities  
368
postgres.toPascal(str: string): string;
369
postgres.fromPascal(str: string): string;
370
postgres.toCamel(str: string): string;
371
postgres.fromCamel(str: string): string;
372
postgres.toKebab(str: string): string;
373
postgres.fromKebab(str: string): string;
374

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

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

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

391
// Error class
392
postgres.PostgresError: typeof PostgresError;
393

394
// Built-in type handlers
395
postgres.BigInt: PostgresType<bigint>;
396
```
397

398
**Usage Examples:**
399

400
```javascript
401
import postgres from "postgres";
402

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

408
// Use transform objects for column/value conversion
409
const sql = postgres({
410
  transform: {
411
    column: postgres.camel.column.from,
412
    value: postgres.camel.value.from
413
  }
414
});
415

416
// Access error class for instanceof checks
417
try {
418
  await sql`SELECT * FROM nonexistent`;
419
} catch (error) {
420
  if (error instanceof postgres.PostgresError) {
421
    console.log('PostgreSQL error:', error.code);
422
  }
423
}
424
```
425

426
## Constants
427

428
Query control constants available on the SQL instance.
429

430
```javascript { .api }
431
interface Sql {
432
  // Query termination constants
433
  readonly CLOSE: {};
434
  readonly END: typeof CLOSE;  // Alias for CLOSE
435
}
436
```
437

438
**Usage Examples:**
439

440
```javascript
441
const sql = postgres();
442

443
// Use CLOSE to terminate cursors early
444
const cursor = sql`SELECT * FROM large_table`.cursor(100);
445
for await (const rows of cursor) {
446
  if (shouldStop) {
447
    await cursor.return(sql.CLOSE);
448
    break;
449
  }
450
  processRows(rows);
451
}
452
```
453

454
## Common Types
455

456
```javascript { .api }
457
interface Row {
458
  [column: string]: any;
459
}
460

461
interface Column<T extends string = string> {
462
  name: T;
463
  type: number;
464
  table: number;
465
  number: number;
466
  parser?: (raw: string) => unknown;
467
}
468

469
interface ConnectionState {
470
  status: string;
471
  pid: number;
472
  secret: number;
473
}
474

475
interface Notice {
476
  [field: string]: string;
477
}
478

479
interface PostgresType<T = any> {
480
  to: number;
481
  from: number[];
482
  serialize: (value: T) => unknown;
483
  parse: (raw: any) => T;
484
}
485
```