Skip to main content

Extended Query

connection.query(sql, options?) runs a single SQL statement using PostgreSQL's Extended Query protocol (Parse/Bind/Execute). This is the protocol to use for application queries: it supports bind parameters, per-column format selection, and server-side cursors.

import { Connection } from 'postgrejs';

const connection = new Connection();
await connection.connect();

const result = await connection.query(
  'select * from customers where id = $1',
  { params: [1], objectRows: true },
);

console.log(result.command);   // 'SELECT'
console.log(result.rows);      // [{ id: 1, given_name: 'Wynne', ... }]
console.log(result.rowsAffected);

The result is a QueryResult — it extends CommandResult (command, fields, rows, rowType, executeTime, rowsAffected) and adds an optional cursor, present only when options.cursor is true.

Parameters​

Positional parameters ($1, $2, ...) are supplied via params, sent out of band from the SQL text — values can never be interpreted as SQL:

await connection.query('select * from customers where id = ANY($1)', {
  params: [[1, 2, 3]],
});

See Query Parameters & Type Casting for how parameter types are detected and how to override them with BindParam.

objectRows​

Controls the row shape: array-of-values (default) or array-of-objects keyed by field name.

await connection.query('select id, name from countries'); // rows: [['CA', 'Canada'], ...]

await connection.query('select id, name from countries', { objectRows: true });
// rows: [{ id: 'CA', name: 'Canada' }, ...]

columnFormat​

Selects the wire format(s) PostgreSQL uses to send column values — text or binary — via the DataFormat enum. It accepts either a single value applied to every column, or an array with one entry per column:

import { DataFormat } from 'postgrejs';

await connection.query('select blob, name from files', {
  columnFormat: DataFormat.binary, // default
});

Binary is the default (DataFormat.binary) and is generally faster to decode. postgrejs decodes both formats into the same JS values, so switching format doesn't change what you get back — it only changes what's sent on the wire.

QueryOptions reference​

The full option list — params, objectRows, columnFormat, cursor, fetchCount, autoCommit, rollbackOnError, utcDates, signal, typeMap, fetchAsString, asyncErrorHandling — is documented in QueryOptions. Notable ones:

KeyTypeDefaultDescription
autoCommitbooleantrueWhether to run the statement in auto-commit mode.
cursorbooleanfalseReturn a Cursor on result.cursor instead of eagerly fetching all rows.
fetchCountnumber100Rows fetched per round trip; for a cursor, rows fetched per batch.
rollbackOnErrorbooleantrueWhether an error inside a transaction aborts it or is ignored so the transaction continues.
utcDatesbooleanfalseDecode dates/timestamps in UTC instead of system time offset.
signalAbortSignal—Cancels the running statement on the server when the signal fires.

See QueryOptions for the complete table.

Cursors​

Pass cursor: true to get back a Cursor instead of eagerly buffering every row — essential for large result sets. See Cursors.

See also​