I'm the author of pg-promise.
In older versions of the library this was covered by simplified examples within the Performance Boost article, which is still a good read when writing high-performance database applications.
The newer approach is to rely on the helpers namespace, which is ultimately flexible, and optimised for performance.
const pgp = require('pg-promise')({
/* initialization options */
capSQL: true // capitalize all generated SQL
});
const db = pgp(/*connection*/);
const {ColumnSet, insert} = pgp.helpers;
// our set of columns, to be created only once (statically), and then reused,
// to let it cache up its formatting templates for high performance:
const cs = new ColumnSet(['col_a', 'col_b'], {table: 'tmp'});
// data input values:
const values = [{col_a: 'a1', col_b: 'b1'}, {col_a: 'a2', col_b: 'b2'}];
// generating a multi-row insert query:
const query = insert(values, cs);
//=> INSERT INTO "tmp"("col_a","col_b") VALUES('a1','b1'),('a2','b2')
// executing the query:
await db.none(query);
See API: ColumnSet, insert.
Such an insert doesn't even require a transaction, because if one set of values fails to insert, none will insert.
And you can use the same approach to generate any of the following queries:
- single-row
INSERT
- multi-row
INSERT
- single-row
UPDATE
- multi-row
UPDATE
Are insertions using ${} notation protected against sql injection?
Yes, but not alone. If you are inserting schema/table/column names dynamically, it is important to use SQL Names, which in combination will protect your code from SQL injection.
Related question: PostgreSQL multi-row updates in Node.js
extras
Q: How to get id of each new record at the same time?
A: Simply by appending RETURNING id to your query, and executing it with method many:
const query = insert(values, cs) + ' RETURNING id';
const res = await db.many(query);
//=> [{id: 1}, {id: 2}, ...]
or even better, get the id-s, and convert the result into array of integers, using method map:
const res = await db.map(query, undefined, a => +a.id);
//=> [1, 2, ...]
To understand why we used + there, see: pg-promise returns integers as strings.
UPDATE-1
For inserting huge number of records, see Data Imports.
UPDATE-2
Using v8.2.1 and later, you can wrap the static query-generation into a function, so it can be generated within the query method, to reject when the query generation fails:
// generating a multi-row insert query inside a function:
const query = () => insert(values, cs);
//=> INSERT INTO "tmp"("col_a","col_b") VALUES('a1','b1'),('a2','b2')
// executing the query as a function that generates the query:
await db.none(query);
