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
```