Passing VALUES clause as query parameter

Question

I am trying to refactor the existing code fragments that use postgresql pg module, and look like this:

let sql = `INSERT INTO jobs (title, type, label) VALUES ${stringValues}`;
        let { rows } = await pg.query(sql, []);

Here they have VALUES clause as a calculated stringValues string that looks like this:

"('title1', 'type1', 'label1'),('title2', 'type2', 'label2')"

I was trying to make this fragment more injection-safe and elegant, to pass parameters via $1. I have tried VALUES array[$1] with passing [stringValues.split(',')] as $1 - no luck. Also tried VALUES array[$1::record] , (SELECT array[$1::record]), various jsonb_ conversions, etc - still no luck.

Could anybody please advise any good way to pass such parameters for insert VALUES?

Answer 1

The cleanest way for a relatively small amount of data is using the pg-format library. See this answer from a similar question and this github issue for reference.

import format from "pg-format";

const values = [['title1', 'type1', 'label1'],['title2', 'type2', 'label2']]
const sql = `INSERT INTO jobs (title, type, label) VALUES %L`
const {rows} = await pg.query(format(sql,values))

If you are bulk loading large amounts of data. it might be worth considering using the COPY FROM command functionality implemented in pg-copy-streams which will be much more performant.