node.js 带有 pg-promise 的多行插入

Multi-row insert with pg-promise

提问by Bomaz

I would like to insert multiple rows with a single INSERTquery, for example:

我想用一个INSERT查询插入多行，例如：

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.

我可能会在一个块中得到 500 条记录，因此运行多个查询是不可取的。

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?

作为一个附带问题：使用${}符号的插入是否可以防止 SQL 注入？

回答by vitaly-t

I'm the author of pg-promise.

我是pg-promise的作者。

In older versions of the library this was covered by simplified examples within the Performance Boostarticle, 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.

较新的方法是依赖于helpers 命名空间，它最终是灵活的，并针对性能进行了优化。

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.

请参阅 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

• 单排 INSERT
• 多行 INSERT
• 单排 UPDATE
• 多行 UPDATE

Are insertions using ${} notation protected against sql injection?

使用 ${} 符号的插入是否可以防止 sql 注入？

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.

是的，但并不孤单。如果您要动态插入模式/表/列名称，请务必使用SQL Names，这将保护您的代码免受 SQL 注入。

---

---

Related question: PostgreSQL multi-row updates in Node.js

相关问题：Node.js 中的 PostgreSQL 多行更新

---

---

extras

额外的

Q: How to get idof each new record at the same time?

问：如何同时获取id每条新记录？

A:Simply by appending RETURNING idto your query, and executing it with method many:

答：只需附加RETURNING id到您的查询，并使用方法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:

或者更好的是，获取 id-s，然后使用方法map将结果转换为整数数组：

const res = await db.map(query, [], a => +a.id);
//=> [1, 2, ...]

To understand why we used +there, see: pg-promise returns integers as strings.

要了解我们为什么在那里使用+，请参阅：pg-promise 将整数作为字符串返回。

UPDATE-1

更新-1

For inserting huge number of records, see Data Imports.

要插入大量记录，请参阅数据导入。

UPDATE-2

更新-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:

使用 v8.2.1 及更高版本，您可以将静态查询生成包装到一个函数中，因此它可以在查询方法中生成，以在查询生成失败时拒绝：

// 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);