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, suspended) 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 / rowDecoder​

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' }, ...]

rowDecoder: 'object' does the same thing as objectRows: true — objectRows is deprecated in its favor, though it keeps working unchanged. rowDecoder also accepts a RowDecoder subclass to take over row decoding entirely, e.g. for lazy per-cell decoding. See Custom Row Decoding.

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, rowDecoder, 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 rows into result.rows.
fetchCountnumber0 (unlimited) for query(), 100 per batch for a CursorMaximum rows to fetch — see below.
preparebooleanthe connection's own setting (true)Overrides the connection's prepare setting for this call — see Automatic Statement Caching.
rowDecoder'array' | 'object' | RowDecoder'array'How a row's raw wire data becomes a value — see above.
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.

fetchCount and suspended results​

query() fetches every row a statement produces by default — fetchCount is an explicit limit, not a page size:

const r = await connection.query('select i from generate_series(1, 1000) i');
r.rows.length; // 1000 - every row

const limited = await connection.query(
  'select i from generate_series(1, 1000) i',
  { fetchCount: 100 },
);
limited.rows.length;  // 100
limited.suspended;    // true - the server stopped at the limit

suspended is set only when an explicit fetchCount was given, and it means "the limit was reached," not "there are definitely more rows" — a result exactly fetchCount rows long suspends too, since the server doesn't look ahead. There's no way to fetch what's left afterward: the portal behind a suspended result is discarded by the Sync that ends the call. Reach for a Cursor instead when the point is to read a large result a piece at a time — there, fetchCount is the batch size per round trip rather than a cap on the whole result, and still defaults to 100.

fetchCount: 0 means unlimited, as it does in the protocol — the same as omitting it.

Cursors​

Pass cursor: true to get back a Cursor instead of eagerly buffering every row. Large result sets need one. See Cursors.

Automatic Statement Caching​

query()/execute() reuse a server-side prepared statement for SQL a connection has already run, instead of parsing it again every time, so a repeated query sends Bind/Execute instead of Parse/Bind/Describe/Execute. This is separate from the explicit connection.prepare() API and happens underneath it; you don't have to opt in, and there's no PreparedStatement object to manage.

Nothing is cached the first time a piece of SQL text is seen, so a query that only ever runs once costs exactly what it always did. The second call with the same SQL text is what gets a server-side name and starts being reused — including a second call that arrives while the first is still preparing, in a burst: only one of them claims the name, so a burst of the same statement never leaves the others' prepared statements orphaned on the server:

await connection.query('select * from customers where id = $1', { params: [1] }); // Parse + Bind + Describe + Execute
await connection.query('select * from customers where id = $1', { params: [2] }); // now prepared: Bind + Execute
await connection.query('select * from customers where id = $1', { params: [3] }); // Bind + Execute

It's on by default, controlled by prepare on DatabaseConnectionParams (per connection) or QueryOptions (per call, overriding the connection's setting):

const connection = new Connection({ prepare: false }); // never cache on this connection

await connection.query(sql, { prepare: false }); // skip the cache for just this call

Turn it off when named prepared statements can't survive between calls on the same connection — the main case being PgBouncer in transaction pooling mode before 1.21, which hands each transaction a different backend server, so a statement prepared on one is simply missing on the next.

The cache serves statements inside an open transaction too, not just standalone calls — a repeated query costs the same Bind/Execute there as outside one. When rollbackOnError also applies, a cached statement's SAVEPOINT/RELEASE pair rides along in the same round trip instead of costing one each.

The cache is bounded per connection: preparedStatementCacheSize (default 64) caps how many statements it holds, evicting (and closing server-side) the least-recently-used one once full, so an application that builds SQL text dynamically can't accumulate statements on the server without limit. A cached plan that's invalidated underneath it is dropped from the cache automatically and the query re-runs unprepared, so it recovers instead of failing every call from then on. That covers both an ALTER TABLE between two calls (0A000, "cached plan must not change result type") and a type the statement refers to being dropped and recreated (XX000, "cache lookup failed for type ..."). Only those two SQLSTATEs retire the entry; an ordinary error (a unique violation on a hot upsert, say) leaves a perfectly good prepared statement in the cache.

Multi-Statement Pipelines​

connection.pipeline(requests, options?) 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. It's the counterpart to PreparedStatement.executeBatch(), which runs one statement over many parameter sets — pipeline() runs many different statements once each:

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]

Statements can be sql tag output, plain SQL strings, or { sql, params, paramTypes } objects — mix and match freely in the same array. Results map back to statements by position, decoded the same way query() would (rowDecoder included).

The single Sync carries the same three consequences as executeBatch():

  • One shared transaction. Unless an explicit transaction is already open, the statements commit or roll back together.
  • A failing statement stops the rest. PostgreSQL discards everything between an error and the Sync, so statements after a rejected one never run. The thrown DatabaseError carries failedIndex, naming which statement the server actually rejected.
  • No statement can see another's results. They're all sent before any reply arrives, so anything conditional on an earlier statement's output belongs in a separate call.

fetchCount doesn't apply and cursor: true throws, for the same reason as executeBatch(): every statement must run to completion under the shared Sync, with no portal left over to fetch from afterward.

Statements the connection has already run bind to their cached prepared name inside the pipeline too — the same automatic caching that a standalone query() gets — so a repeated set of statements sends no Parse at all the second time through, worth 2.5x on twenty statements.

This is a different mechanism from Pool's opt-in pipelining ({ pipeline: true } on pool.query()), which lets independent one-shot queries share a connection without waiting on each other but still gives each its own Sync — connection.pipeline() is what collapses several known statements into a single round trip.

Empty Statements​

query('') — and a statement that's only whitespace or only a comment — returns an ordinary empty result (no command, no rows, no fields) instead of throwing. PostgreSQL answers such a statement with EmptyQueryResponse rather than a CommandComplete, which is what pg also treats as a harmless empty result for the same input.

See also​