Multi-row insert with pg-promise

问题:

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?

回答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 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.

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, and then shared/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:
db.none(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.

---

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\';

db.many(query)
    .then(data => {
        // data = [{id: 1}, {id: 2}, ...]
    })
    .catch(error => {
        // error;
    });

or even better, get the id-s, and convert the result into array of integers, using method map:

db.map(query, [], a => +a.id)
    .then(data => {
        // data = [1, 2, ...]
    })
    .catch(error => {
        // error;
    });

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:
db.none(query)
    .then(data => {
        // success;
    })
    .catch(error => {
        // error;
        // will get here, even if the query generation fails
    });