node.js - How to make pg-promise return rows as arrays?

Question

How can we make pg-promise return an array of rows from a query, as opposed to array of row objects?

Answer 1

pg-promise supports advanced parameters for Prepared Statements and Parameterized Queries, exactly as they are in node-postgres. One such parameter - rowMode, can be set to array to make the driver return rows as arrays.

// Prepared Statement:
await db.any({
    name: 'my-prep-statement',
    text: 'select ...', // a query or a QueryFile object (see PreparedStatement)
    values: [],
    rowMode: 'array'
});

// Parameterized Query:
await db.any({
    text: 'select ...', // a query or a QueryFile object (see ParameterizedQuery)
    values: [],
    rowMode: 'array'
});

See also types that wrap them: PreparedStatement and ParameterizedQuery.

The code below is equivalent to the one above, but offers better performance + re-usability + flexibility of setting values separately.

const ps = new pgp.PreparedStatement({
    name: 'my-prep-statement',
    text: 'select ...', // a query or a QueryFile object (see PreparedStatement)
    values: [], // alternatively, can be set when calling a query method
    rowMode: 'array'
});

await db.any(ps);

const pq = new pgp.ParameterizedQuery({
    text: 'select ...', // a query or a QueryFile object (see ParameterizedQuery)
    values: [], // alternatively, can be set when calling a query method
    rowMode: 'array'
});

await db.any(pq);