Connection

Represents a single connection to a PostgreSQL server. A Connection can be created standalone (new Connection(...)) or obtained from a Pool via pool.acquire(). It implements AsyncDisposable, so it can be used with await using.

Constructor

new Connection(config?: ConnectionConfiguration | string)

Argument	Type	Default	Description
config	DatabaseConnectionParams \| `string`		Connection configuration object or a connection string

import { Connection } from 'postgrejs';

const connection = new Connection('postgres://localhost');
await connection.connect();

note

A connection acquired from a Pool is constructed internally (new Connection(pool, intlCon)) and returned to you already wrapped — you never call that overload directly.

Properties

Key	Type	Readonly	Description
config	DatabaseConnectionParams	true	Returns the configuration object
inTransaction	`boolean`	true	Returns `true` if the connection is currently in a transaction
state	`ConnectionState`	true	Returns the current state of the connection
processID	`number \| undefined`	true	Returns the process ID of the current session
host	`string \| undefined`	true	The host this connection reached. With several hosts configured, this is the one it ended up on, which `config.host` doesn't say. `undefined` before the connection is opened. Since 3.15.0
port	`number \| undefined`	true	The port of `host`, 5432 unless configured. `undefined` before the connection is opened. Since 3.15.0
secretKey	`Buffer \| undefined`	true	Returns the secret key of the current session (used for `cancel()`). 4 bytes by default, up to 256 with `longCancelKey`. Was `number` before v3.1 (a breaking change that came with variable-length keys).
sessionParameters	`Record<string, string>`	true	Returns the `ParameterStatus` values reported by the server for the current session
runningQueryCount	`number`	true	Returns the number of queries currently running on this connection
protocolNegotiation	`NegotiateProtocolVersionMessage \| undefined`	true	The server's `NegotiateProtocolVersion` reply, if it sent one during `connect()`. `undefined` means the server fully recognized everything the startup packet asked for (protocol minor version, any `_pq_.*` options); present only when the server is older/stricter than what was requested.

NegotiateProtocolVersionMessage shape:

Key	Type	Description
`supportedVersionMinor`	`number`	Newest minor protocol version the server supports.
`unrecognizedOptions`	`string[]`	Startup packet options this client sent that the server didn't recognize — empty when the only mismatch is the protocol minor version itself.

Methods

connect()

Connects to the server.

connect(): Promise<void>

import { Connection } from 'postgrejs';

const connection = new Connection('postgres://localhost');
await connection.connect();
// ...

close()

For a standalone connection this call closes the connection permanently. For a connection acquired from a Pool, it sends the connection back to the pool instead of closing the socket.

You can define how long the connection will wait for active queries before closing. At the end of that time, it forces the socket closed and emits the terminate event.

close(terminateWait?: number): Promise<void>

Argument	Type	Default	Description
terminateWait	`number`		Time in ms to wait for active queries to finish before forcing the connection closed

import { Connection } from 'postgrejs';

const connection = new Connection('postgres://localhost');
await connection.connect();
connection.on('close', () => {
  console.log('Connection closed');
});
connection.on('terminate', () => {
  console.warn('Connection forced to terminate!');
});
// ...
await connection.close(30000); // will wait 30 secs before terminating the connection

Connection supports the TC39 Explicit Resource Management proposal, so it can be closed automatically.

import { Connection } from 'postgrejs';

{
  // connection will be automatically closed when this scope ends
  await using connection = new Connection('postgres://localhost');
  await connection.connect();
}

execute()

Executes single or multiple SQL scripts using the Simple Query protocol.

execute(sql: string, options?: ScriptExecuteOptions): Promise<ScriptResult>

Argument	Type	Default	Description
sql	`string`		SQL script that will be executed
options	ScriptExecuteOptions		Execute options

Returns ScriptResult

import { Connection } from 'postgrejs';

const connection = new Connection('postgres://localhost');
await connection.connect();
const executeResult = await connection.execute(
    'BEGIN; update my_table set ref=1 where id=1; END;');
// ...
await connection.close();

query()

Executes a single SQL script using the Extended Query protocol.

query(sql: string, options?: QueryOptions): Promise<QueryResult>

Argument	Type	Default	Description
sql	`string`		SQL script that will be executed
options	QueryOptions		Query options

Returns QueryResult

import { Connection } from 'postgrejs';

const connection = new Connection('postgres://localhost');
await connection.connect();
const queryResult = await connection.query(
    'select * from my_table', {
      cursor: true,
      objectRows: true
    });
let row;
while ((row = await queryResult.cursor.next())) {
  // ....
}
await connection.close();

pipeline()

Runs several different statements in one round trip: every Parse/Bind/Describe/Execute goes out before any response is waited for, and a single Sync closes the lot. The statements share one implicit transaction (unless one is already open), and a rejected statement stops the ones behind it from running. It is the counterpart to PreparedStatement.executeBatch(), which runs one statement over many parameter sets.

pipeline(requests: (string | QueryRequest | PipelineRequest)[], options?: QueryOptions): Promise<QueryResult[]>

Argument	Type	Default	Description
requests	`(string \| QueryRequest \| PipelineRequest)[]`		Statements to run, as `sql` tag output, plain SQL strings, or `{ sql, params, paramTypes }` objects
options	QueryOptions		Applied to every statement — minus `fetchCount` and `cursor`, which a pipeline cannot honor

Returns Promise<QueryResult[]> — one entry per statement, in submission order
Throws a DatabaseError carrying failedIndex (which statement was rejected), if the server rejects one
Throws TypeError if requests isn't an array
Throws if options.cursor is set — every statement runs to completion under one Sync, so there's no portal left to fetch from

import { sql } from 'postgrejs';

const [renamed, , total] = await connection.pipeline([
  sql`update users set name = ${name} where id = ${id}`,
  sql`insert into audit(msg) values (${msg})`,
  sql`select count(*)::int as n from users`,
]);
renamed.rowsAffected; // 1
total.rows?.[0];      // [42]

See Extended Query: Multi-Statement Pipelines for the full guide.

copyTo()

Runs a COPY ... TO STDOUT statement and returns its output as a stream of the raw bytes the server sends. Resolves as soon as the server accepts the copy, so a large export is never held in memory.

copyTo(sql: string): Promise<CopyToStream>

Argument	Type	Default	Description
sql	`string`		A `COPY ... TO STDOUT` statement

Returns CopyToStream

import { Connection } from 'postgrejs';
import { pipeline } from 'node:stream/promises';
import fs from 'node:fs';

const connection = new Connection('postgres://localhost');
await connection.connect();
const out = await connection.copyTo(`COPY users TO STDOUT (FORMAT csv)`);
await pipeline(out, fs.createWriteStream('users.csv'));
console.log(out.rowCount);
await connection.close();

copyFrom()

Runs a COPY ... FROM STDIN statement and returns a stream to feed it. Whatever is written is forwarded verbatim, so the statement's own FORMAT decides the encoding.

copyFrom(sql: string): Promise<CopyFromStream>

Argument	Type	Default	Description
sql	`string`		A `COPY ... FROM STDIN` statement

Returns CopyFromStream

import { Connection } from 'postgrejs';
import { pipeline } from 'node:stream/promises';
import fs from 'node:fs';

const connection = new Connection('postgres://localhost');
await connection.connect();
const inp = await connection.copyFrom(`COPY users FROM STDIN (FORMAT csv)`);
await pipeline(fs.createReadStream('users.csv'), inp);
console.log(inp.rowCount);
await connection.close();

copyFromRows()

Loads rows into a table with COPY ... FROM STDIN (FORMAT binary), encoding each value with its destination column's own binary encoder instead of leaving the caller to format a text/CSV payload.

copyFromRows(table: string, source: CopyRowSource, options?: CopyFromRowsOptions): Promise<CopyFromRowsResult>

Argument	Type	Description
table	`string`	Destination table, optionally schema-qualified (`schema.table`)
source	`CopyRowSource`	Rows to load — an `Iterable`/`AsyncIterable` of rows (array or generator), or a `Readable`
options	CopyFromRowsOptions	Column list/types, chunk size, and invalid-value handling

Returns CopyFromRowsResult

const { rowCount } = await connection.copyFromRows('users', [
  [1, 'John', 10.5],
  [2, 'Jane', 20.0],
], { columns: ['id', 'name', 'amount'] });

See COPY TO / COPY FROM: Binary COPY FROM for the full guide.

prepare()

Creates a PreparedStatement instance.

prepare(sql: string, options?: StatementPrepareOptions): Promise<PreparedStatement>

Argument	Type	Default	Description
sql	`string`		SQL script that will be executed
options	StatementPrepareOptions		Prepare options

Returns PreparedStatement

import { Connection, DataTypeOIDs } from 'postgrejs';

const connection = new Connection('postgres://localhost');
await connection.connect();
const statement = await connection.prepare(
    'insert into my_table (ref_number) values ($1)', {
      paramTypes: [DataTypeOIDs.int4]
    });
// Bulk insert 100 rows
for (let i = 0; i < 100; i++) {
  await statement.execute({params: [i]});
}
await statement.close();

createLargeObject()

Creates a large object and opens it for reading and writing. If the connection is not already in a transaction, one is started and committed by the returned object's close().

createLargeObject(mode?: number): Promise<LargeObject>

Argument	Type	Default	Description
mode	`number`	`LargeObjectMode.readWrite`	Open mode, one of the `LargeObjectMode` constants

Returns LargeObject

import { Connection } from 'postgrejs';
import { pipeline } from 'node:stream/promises';
import fs from 'node:fs';

const connection = new Connection('postgres://localhost');
await connection.connect();
const lo = await connection.createLargeObject();
await pipeline(fs.createReadStream('video.mp4'), lo.writable());
await lo.close();
await connection.query('insert into videos (video_oid) values ($1)', {params: [lo.oid]});
await connection.close();

openLargeObject()

Opens an existing large object by its OID. Transaction handling is the same as createLargeObject().

openLargeObject(oid: number, mode?: number): Promise<LargeObject>

Argument	Type	Default	Description
oid	`number`		OID of an existing large object
mode	`number`	`LargeObjectMode.read`	Open mode, one of the `LargeObjectMode` constants

Returns LargeObject

unlinkLargeObject()

Deletes a large object and its data.

unlinkLargeObject(oid: number): Promise<void>

Argument	Type	Default	Description
oid	`number`		OID of the large object to delete

cancel()

Asks the server to cancel whatever this connection is currently running. The request travels on its own short-lived connection. It is a request, not a guarantee: the cancelled call rejects with SQLSTATE 57014 if the server acted on it. Prefer the per-call signal option of query()/execute(), which does this and reports the abort to the right caller.

cancel(): Promise<void>

callFunction()

Uses the legacy Function Call sub-protocol to call a function by OID directly, bypassing SQL entirely. That protocol is superseded by SELECT func(...) over the Simple/Extended Query protocols (what execute()/query() use, and what every current PostgreSQL client uses exclusively), and this method is kept only for wire-protocol completeness. Arguments and the result travel as raw wire-format bytes, not JS values: the caller is responsible for encoding/decoding them (see a DataType's own encodeBinary/decodeBinary for the format a given OID expects).

callFunction(functionId: OID, args: (Buffer | null)[], options?: FunctionCallOptions & { signal?: AbortSignal }): Promise<FunctionCallResult>

Argument	Type	Description
functionId	`OID`	OID of the function to call
args	`(Buffer \| null)[]`	Each argument's own already-encoded wire bytes, or `null` for SQL `NULL`
options	FunctionCallOptions `& { signal?: AbortSignal }`	Argument/result formats, and an optional abort signal

Returns FunctionCallResult

See the Function Call Protocol guide for full examples.

transaction()

Runs fn inside a transaction: commits when it returns, rolls back and rethrows when it throws. A call made while a transaction is already open takes a savepoint instead of a second BEGIN, so a nested scope that fails rolls back only its own work. See Scoped Transactions for the full explanation.

transaction<T>(fn: (connection: this) => Promise<T>, options?: TransactionOptions): Promise<T>

Argument	Type	Default	Description
fn	`(connection: this) => Promise<T>`		Run inside the transaction (or a savepoint, if nested). Receives this same connection.
options	`TransactionOptions`		Isolation level, read-only, deferrable — see Transaction Modes. Throws if `fn` is called while a transaction is already open (nested — modes apply only to the `BEGIN` that opens one)

import { Connection } from 'postgrejs';

const connection = new Connection('postgres://localhost');
await connection.connect();

const id = await connection.transaction(async tx => {
  const r = await tx.query(
    'insert into orders (total) values ($1) returning id',
    { params: [199.99] },
  );
  await tx.query('update stock set n = n - 1 where sku = $1', { params: ['widget-1'] });
  return r.rows![0][0];
});

startTransaction()

Starts a transaction or, if one is already open, marks a nested level of it. Calls are reference-counted: only the outermost call (the one that finds the connection not already in a transaction) actually sends BEGIN. A matching number of commit() calls is then needed to actually commit. See Transactions & Savepoints for the full explanation and a worked example.

startTransaction(options?: TransactionOptions): Promise<void>

Argument	Type	Default	Description
options	`TransactionOptions`		Isolation level, read-only, deferrable, taken on the `BEGIN` itself — see Transaction Modes. Throws if given on a nested call (a transaction is already open)

import { Connection } from 'postgrejs';

const connection = new Connection('postgres://localhost');
await connection.connect();
await connection.startTransaction();
const executeResult = await connection.execute(
    'update my_table set ref=1 where id=1');
// ...... commit or rollback
await connection.close();

commit()

Commits the current transaction. Reference-counted against startTransaction(): if more than one startTransaction() call is still unmatched, this just unwinds one nesting level without sending anything on the wire — the actual COMMIT is only sent once the count reaches zero.

commit(immediate?: boolean): Promise<void>

Argument	Type	Default	Description
immediate	`boolean`	`false`	When `true`, ignores the nesting count and commits right away, regardless of how many `startTransaction()` calls are still unmatched.

await connection.startTransaction();
await connection.execute('update my_table set ref=1 where id=1');
await connection.commit();

rollback()

Rolls back the current transaction. Unlike commit(), this is not reference-counted — it always ends the transaction outright immediately, regardless of how many nested startTransaction() calls are still open.

rollback(): Promise<void>

await connection.startTransaction();
await connection.execute('update my_table set ref=1 where id=1');
await connection.rollback();

prepareTransaction()

Ends the current transaction as a prepared one, for two-phase commit. The transaction stops belonging to this session and waits under name until it is finished by commitPrepared() or rollbackPrepared() — which may run on another connection, in another process. Requires max_prepared_transactions to be set above zero on the server.

prepareTransaction(name: string): Promise<void>

Argument	Type	Default	Description
name	`string`		Name to identify the prepared transaction

await connection.startTransaction();
await connection.query('insert into t values (1)');
await connection.prepareTransaction('tx1');
// ...later, anywhere:
await otherConnection.commitPrepared('tx1');

commitPrepared()

Commits a transaction left waiting by prepareTransaction(), by name. Runs outside any transaction and needs no connection to the session that prepared it.

commitPrepared(name: string): Promise<void>

Argument	Type	Default	Description
name	`string`		Name of the previously prepared transaction

rollbackPrepared()

Discards a transaction left waiting by prepareTransaction(), by name.

rollbackPrepared(name: string): Promise<void>

Argument	Type	Default	Description
name	`string`		Name of the previously prepared transaction

savepoint()

Starts a transaction (if not already in one) and creates a savepoint — or, if a savepoint under the same name is already open, marks a nested level of it. Only the outermost call for a given name actually sends SAVEPOINT; see releaseSavepoint().

savepoint(name: string): Promise<void>

Argument	Type	Default	Description
name	`string`		Name of the savepoint

await connection.savepoint('my_save_point');
const executeResult = await connection.execute(
    'update my_table set ref=1 where id=1');
await connection.rollbackToSavepoint('my_save_point');

rollbackToSavepoint()

Rolls back the current transaction to the given savepoint. Like rollback(), this is not reference-counted — regardless of how many nested savepoint() calls are open for name, it discards the savepoint (and everything done after it) outright and clears that name's nesting count. A releaseSavepoint(name) call still pending further up the call stack is then not a safe no-op: it will unconditionally send RELEASE SAVEPOINT again for a savepoint the server no longer has, which the server rejects with an error.

rollbackToSavepoint(name: string): Promise<void>

Argument	Type	Default	Description
name	`string`		Name of the savepoint

releaseSavepoint()

Releases (destroys) the given savepoint without rolling back the changes made since it was created. Reference-counted per name against savepoint(): only the call that unwinds the last remaining nested level for that name actually sends RELEASE SAVEPOINT.

releaseSavepoint(name: string, immediate?: boolean): Promise<void>

Argument	Type	Default	Description
name	`string`		Name of the savepoint
immediate	`boolean`	`false`	When `true`, ignores the nesting count for this name and releases right away.

listen()

Registers the connection as a listener on the given NOTIFY channel.

listen(channel: string, callback: NotificationCallback): Promise<void>

Argument	Type	Default	Description
channel	`string`		Name of the channel — any name PostgreSQL accepts, up to 63 bytes. Folded to lower case the same way an unquoted `LISTEN`/`NOTIFY` identifier would be, so a mixed-case name matches what the server actually delivers — see Channel name casing
callback	`NotificationCallback`		Listener callback function

await connection.listen('my_event', (msg) => {
  console.log(msg.channel + ' event fired!. processId:', msg.processId, '  payload:', msg.payload);
});

unListen()

Removes the existing registration for NOTIFY events on the given channel.

unListen(channel: string): Promise<void>

Argument	Type	Default	Description
channel	`string`		Name of the channel

await connection.unListen('my_event');

unListenAll()

Removes existing registration for NOTIFY events on all channels.

unListenAll(): Promise<void>

await connection.unListenAll();

Symbol.asyncDispose()

Implements AsyncDisposable; calls close(). Lets a Connection be used with await using, as shown under close() above.

[Symbol.asyncDispose](): Promise<void>

Events

ready

Triggered once the connection has finished start-up (after connect() resolves any search_path/timezone setup statements).

() => void

connecting

Triggered when the connection begins establishing its socket.

() => void

close

Triggered after the connection's socket has closed.

(reason?: ConnectionLostError) => void

Argument	Type	Default	Description
reason	`ConnectionLostError`		Only present when the socket closed on its own (the backend was terminated, a failover, a network fault) rather than because the application called `close()` itself. Whatever query was running at the time rejects with this same object, so a handler that needs both can pair them by identity

terminate

Triggered when close(terminateWait) forces the socket closed because active queries did not finish in time.

() => void

release

Triggered when a pooled connection is returned to its Pool by close(). Never fires on a standalone connection.

() => void

error

Triggered when the connection's socket closes on its own (killed by an administrator, a failover, a network fault) rather than because the application called close(). This is where code ported from pg listens, and it's the only report for a connection that had no query running to reject at the time; one that did also rejects that call and fires 'close' with the same object. Safe to leave unhandled: Connection drops an 'error' with no listener instead of throwing.

(err: ConnectionLostError) => void

Argument	Type	Default	Description
err	`ConnectionLostError`		`code` is always `'08006'`

notice

Triggered whenever the server sends a NoticeResponse on this connection — a RAISE NOTICE, or PostgreSQL's own "table does not exist, skipping" from DROP TABLE IF EXISTS. Never rejects the running call or affects its result; the query that triggered it continues normally. Fires while idle too, not just mid-query.

(msg: DatabaseError) => void

Argument	Type	Default	Description
msg	DatabaseError		The notice, shaped like an error

connection.on('notice', msg => console.log(msg.severity, msg.message));
await connection.query('drop table if exists no_such_table'); // logs: NOTICE table "no_such_table" does not exist, skipping

notification

Triggered when a notification is received on a channel this connection has listen()-ed to.

(msg: NotificationMessage) => void

Argument	Type	Default	Description
msg	`NotificationMessage`		Notification message instance

execute

Triggered right before execute(), copyTo() or copyFrom() sends its SQL to the server.

(sql: string, options?: ScriptExecuteOptions) => void

Argument	Type	Default	Description
sql	`string`		SQL that is about to be sent
options	ScriptExecuteOptions		Options passed to `execute()`

query

Triggered right before query() sends its SQL to the server.

(sql: string, options?: QueryOptions) => void

Argument	Type	Default	Description
sql	`string`		SQL that is about to be sent
options	QueryOptions		Options passed to `query()`

debug

Triggered for internal protocol-level tracing (only computed while at least one listener is attached).

(event: { location: string; message: string; sql?: string; [key: string]: any }) => void

Argument	Type	Default	Description
event	`object`		Debug payload; always has `location` and `message`, sometimes `sql`

Constructor​

Properties​

Methods​

connect()​

close()​

execute()​

query()​

pipeline()​

copyTo()​

copyFrom()​

copyFromRows()​

prepare()​

createLargeObject()​

openLargeObject()​

unlinkLargeObject()​

cancel()​

callFunction()​

transaction()​

startTransaction()​

commit()​

rollback()​

prepareTransaction()​

commitPrepared()​

rollbackPrepared()​

savepoint()​

rollbackToSavepoint()​

releaseSavepoint()​

listen()​

unListen()​

unListenAll()​

Symbol.asyncDispose()​

Events​

ready​

connecting​

close​

terminate​

release​

error​

notice​

notification​

execute​

query​

debug​

Constructor

Properties

Methods

connect()

close()

execute()

query()

pipeline()

copyTo()

copyFrom()

copyFromRows()

prepare()

createLargeObject()

openLargeObject()

unlinkLargeObject()

cancel()

callFunction()

transaction()

startTransaction()

commit()

rollback()

prepareTransaction()

commitPrepared()

rollbackPrepared()

savepoint()

rollbackToSavepoint()

releaseSavepoint()

listen()

unListen()

unListenAll()

Symbol.asyncDispose()

Events

ready

connecting

close

terminate

release

error

notice

notification

execute

query

debug