Multi-row insert with pg-promise

Question

I would like to insert multiple rows with a single INSERT 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:

[{col_a:'a1',col_b:'b1'},{col_a:'a2',col_b:'b2'}]

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 1

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*/);
    
// 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 pgp.helpers.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 = pgp.helpers.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 = pgp.helpers.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 = () => pgp.helpers.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);

Answer 2

Try https://github.com/datalanche/node-pg-format - e.g.

var format = require('pg-format');

var myNestedArray = [['a', 1], ['b', 2]];
var sql = format('INSERT INTO t (name, age) VALUES %L', myNestedArray); 
console.log(sql); // INSERT INTO t (name, age) VALUES ('a', '1'), ('b', '2')

works similarly with array of objects.

Answer 3

CREATE TABLE "user"
(
    id         BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    first_name VARCHAR(255),
    last_name  VARCHAR(255),
    email      VARCHAR(255),
    password   VARCHAR(60),
    role       VARCHAR(255),
    enabled    BOOLEAN                                 NOT NULL DEFAULT FALSE,
    CONSTRAINT pk_user PRIMARY KEY (id)
);

INSERT INTO "user" (id,
                    first_name,
                    last_name,
                    email,
                    password,
                    role,
                    enabled)
VALUES (generate_series(1, 50),
       substr(gen_random_uuid()::text, 1, 10),
        substr(gen_random_uuid()::text, 1, 10),
        substr(gen_random_uuid()::text, 2, 5 )
            || '@' ||
        substr(gen_random_uuid()::text, 2, 5)
            || '.com',
        substr(gen_random_uuid()::text, 1, 10),
        (array['ADMIN', 'MANAGER', 'USER'])[floor(random() * 3 + 1)],
        (array[true, false])[floor(random() * 2 + 1)]
       );