多行插入与pg-promise

Question

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

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

是否有一种方法可以轻松做到这一点，最好是针对如下对象数组：

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

我最终可能会在一个块中有500条记录，因此不希望运行多个查询。

到目前为止，我只能为单个对象执行此操作：

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

顺便问一个问题:使用< code>${}符号的插入是否可以防止SQL注入？

Answer 1

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

Answer 2

尝试 https://github.com/datalanche/node-pg-format - 例如

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')

与对象数组的工作方式类似。

Answer 3

我是pgpromise的作者。

在该库的旧版本中，这在性能提升文章中的简化示例中有所涉及，在编写高性能数据库应用程序时，这仍然是一个很好的读物。

较新的方法是依赖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);

请参见API:列集，插入。

这样的插入甚至不需要事务，因为如果一组值插入失败，就不会插入。

您可以使用相同的方法来生成以下任何查询：

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

使用 ${} 表示法的插入是否防止 sql 注入？

是的，但不是单独的。如果您要动态插入模式/表/列名，使用SQL名称很重要，这将保护您的代码免受SQL注入。

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

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

答：只需将RETURning id附加到您的查询中，并使用方法any执行它：

const query = pgp.helpers.insert(values, cs) + ' RETURNING id';
    
const res = await db.many(query);
//=> [{id: 1}, {id: 2}, ...]

或者更好，获得id-s，并使用方法map将结果转换为整数数组:

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

要理解我们为什么在这里使用< code> ，请参见:pg-promise将整数作为字符串返回。

UPDATE-1

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

更新-2

使用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);