Multi-Row Inserts with pg-promise

Problem Statement:

How to efficiently insert multiple rows into a database using a single INSERT query in pg-promise?

Solution:

The preferred approach in pg-promise is to use the helpers namespace for high performance and flexibility.

const {ColumnSet, insert} = pgp.helpers;

const cs = new ColumnSet(['col_a', 'col_b'], {table: 'tmp'});
const values = [{col_a: 'a1', col_b: 'b1'}, {col_a: 'a2', col_b: 'b2'}];

const query = insert(values, cs);
// => INSERT INTO "tmp"("col_a","col_b") VALUES('a1','b1'),('a2','b2')

await db.none(query);

Additional Considerations:

• The ColumnSet object should be created statically to cache formatting templates for optimal performance.
• This multi-row insert is transactionless, meaning all or none of the values will be inserted.
• SQL injection protection is provided by using the helpers namespace in conjunction with SQL Names.

Extras:

• To obtain the ID of each new record, append RETURNING id to the query and execute it with db.many().
• For inserting large datasets, consider utilizing the Data Imports feature.
• You can encapsulate query generation within a function for enhanced error handling.