Bomaz Bomaz - 2 years ago 358
Node.js Question

Multi-row insert with pg-promise

I would like to insert multiple rows with a single

query, for example:

INSERT INTO tmp(col_a,col_b) VALUES('a1','b1'),('a2','b2')...

Is there a way to do this easily, preferably for an array of objects like these:


I might end up with 500 records in one chunk, so running multiple queries would be undesirable.

So far I have been able to do it for a single object only:

INSERT INTO tmp(col_a,col_b) VALUES(${col_a},${col_b})

As a side question: Are insertions using
notation protected against SQL injections?

Answer Source

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 an important read when writing high-performance database applications.

The newer approach is to rely on the helpers namespace, which is ultimately flexible, and highly optimized for performance (you should use the latest 4.2.2 or newer version of the library).

// performance-optimized, reusable set of columns:
var cs = new pgp.helpers.ColumnSet(['col_a', 'col_b'], {table: 'tmp'});

// input values:
var values = [{col_a: 'a1', col_b: 'b1'}, {col_a: 'a2', col_b: 'b2'}];

// generating a multi-row insert query:
var query = pgp.helpers.insert(values, cs);
//=> INSERT INTO "tmp"("col_a","col_b") VALUES('a1','b1'),('a2','b2')

// executing the query:
    .then(data=> {
        // success;
    .catch(error=> {
        // error;

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download