How can I perform multi-row inserts efficiently with pg-promise?

Multi-Row Inserts with pg-promise

Problem:
Insertions using parameters of a single object can be easily performed using pg-promise, but multi-row insertions require more complex techniques. Is there a straightforward method to insert multiple rows with a single query?

Solution:

In newer versions of pg-promise, the helpers namespace provides optimized and flexible methods for multi-row inserts. Instead of manually constructing the query string, you can use the insert function within the helpers namespace:

const pgp = require('pg-promise')({
    capSQL: true // capitalize all SQL
});
const db = pgp(/*connection*/);
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);

await db.none(query);

This code generates the following SQL:

<code class="sql">INSERT INTO "tmp"("col_a","col_b") VALUES('a1', 'b1'),('a2', 'b2')</code>

This approach offers several advantages:

• Optimized performance
• Prevents SQL injection
• Centralized query generation for single-row and multi-row inserts or updates

Side Question:

Insertions using parameter notation are protected against SQL injection when parameter names are correctly quoted. However, if dynamic insertion of column or table names is necessary, SQL Names should be used for additional protection.

The above is the detailed content of How can I perform multi-row inserts efficiently with pg-promise?. For more information, please follow other related articles on the PHP Chinese website!