Mimic node-postgres "Row Mode"

[TGlock]

See - https://node-postgres.com/features/queries Row Mode

The concept is to be able create a result with similar shape as:

let res = await sql`select first_name, last_name, email from tbl_users`

res would have the shape
{
   cols: { first_name: { type : 1043 }, last_name: { type : 1043 }, email: { type : 1043 } },
   rows: ['John', 'Smith', '[email protected]']
}

This can significantly reduce http response size since repeated attribute names are omitted.

cols can simply utilize the statement.columns already provided.

If there is a means to use raw() please provide an example.

Alternatively perhaps consider, ".rowmode()" but it is not clear if that could be combined with existing .unsafe()

There may be existing codebases seeking to migrate from node-postgres and in short-term utilizing .unsafe()

[porsager]

Have you seen .raw() ?

We could allow providing an options object eg .raw({ parse: true }) to parse the Buffer values?

[TGlock]

I will explore .raw() further and revert back.

I like your option idea {parse : true}

Thanks for quick reply!

[porsager]

Great :) Looking forward to hear what you find. The docs could be improved for .raw() so suggestions are welcome.

This can significantly reduce http response size since repeated attribute names are omitted.

Keep in mind that simply using compression for your http responses will remove almost all overhead of sending a long array of full objects. I would highly recommend you test that out in practice before assuming arrays will be much smaller than arrays. For any case I have had the difference was so negligible that the overhead in handling array indexes in code would never be worth it.

[porsager]

I'm thinking adding this feature as it's own method instead so you'd do eg sql`select generate_series(1, 5)`.values(). I've got it ready, but have not decided on the naming yet. I'm currently juggling .values(), .array() and .flat(), but leaning on .values() - any ideas?

[TGlock]

Thanks for this - I would also lean toward values(). Apologies for delay!

[porsager]

Perfect :) It's added.. Will be included in next release

[TGlock]

Regrettably compression is not always in play. For results of ~500+ rows significant content-length reduction is seen. Bear in mind, database column names can be legacy, poorly chosen and long.

On client side, I have found processing to be slightly easier with array of arrays vs array of objects but codebase is almost identical.

I will add a single array of objects could have smaller memory impact than "array of arrays" but I've yet to take time to measure and ultimately it will vary depending on client (or server) implementation.

Again I will explore .raw() and see what I might learn.

[TGlock]

Thank you for this - gave it a try and thus far is perfect.