layout: doc title: Batch row lookups with node-postgres seo_title: “Batch PostgreSQL Row Lookups with node-postgres” description: Use pg_local_cache 2.0 from Node.js with a parameterized bigint array. Decode text[] results, preserve order and nulls, and compare with a prepared ANY query. section: Node.js permalink: /docs/node-postgres.html

last_modified_at: “2026-09-14”

Batch row lookups with node-postgres

The extension exposes a SQL function that works with existing PostgreSQL clients.

Start the demo, install the example’s pinned dependency, and run its integration assertions:

npm --prefix examples/node-postgres ci --ignore-scripts
npm --prefix examples/node-postgres run demo

Send one parameterized query

Given a connected node-postgres client or pool:

const result = await client.query({
  name: 'items-mget',
  text: "SELECT local_cache.mget('public.items'::regclass, $1::bigint[]) AS rows",
  values: [[42, 7, 42, null, 999999]],
});
const rows = result.rows[0].rows.map(row =>
  row === null ? null : JSON.parse(row)
);

There is one result record containing a text[]. Each non-null element is a serialized row, so the driver does not automatically decode it as a JSON object. The returned positions match the input positions. Missing keys and null inputs both produce null.

Keep the table name fixed in application code. The array is a query parameter, not SQL assembled by joining IDs into a string. See node-postgres documentation for parameters and named prepared statements.

The runnable helper rejects batches over 1,024 keys and returns [] without a query for an empty batch. It uses safe integer demo IDs. PostgreSQL bigint and numeric fields in JSON can exceed JavaScript’s exact numeric range; choose a lossless JSON parser or an explicit serialization contract before using such values. Merely passing a key as a string does not fix precision in the returned JSON payload.

Compare with the existing batch query

The baseline uses:

SELECT id::text AS key, row_to_json(i)::text AS row
FROM public.items AS i
WHERE id = ANY($1::bigint[]);

ANY does not preserve input order or duplicate requested positions. The example restores them on the client and supplies null for missing rows before comparing results. Both that work and JSON parsing are included in the benchmark.

The implementation and unit tests are in examples/node-postgres. The helper takes an existing client rather than creating a pool per call.

Transactions and application boundaries

Use one acquired client throughout a transaction. mget does not change that rule. Reads after writes in the same transaction use PostgreSQL’s source-table path. The demo checks this with separate reader and writer connections; see cache invalidation.

This example does not patch an ORM, transparently intercept SELECT, provide an application cache, or replace a connection pool. To decide where to integrate it, measure a specific repeated whole-row lookup first. An endpoint dominated by joins or network latency is a different problem.