使用brianc/node-postgres进行Postgres的批量插入

Question

我在nodejs中使用pg (https://github.com/brianc/node-postgres) 编写了以下代码。

我的代码用于为员工创建订阅，如下所示。

    client.query(
      'INSERT INTO subscriptions (subscription_guid, employer_guid, employee_guid) 
       values ($1,$2,$3)', [
        datasetArr[0].subscription_guid,
        datasetArr[0].employer_guid,
        datasetArr[0].employee_guid
      ],


      function(err, result) {
        done();

        if (err) {
          set_response(500, err, res);
          logger.error('error running query', err);
          return console.error('error running query', err);
        }

        logger.info('subscription with created');
        set_response(201);

      });

正如您已经注意到的那样，datasetArr是一个数组。我想一次为多个员工创建大量订阅。但是我不想通过循环遍历数组来实现。有没有使用pg默认功能的方法来完成？

- lonelymo

---

使用Node提供的任何接口来调用PostgreSQL的COPY命令。 - Richard Huxton

@RichardHuxton：根据http：//www.postgresql.org/docs/9.1/static/sql-copy.html，'COPY'命令只能使用STDIN（csv /文件上传）工作。我如何让它与数组一起工作？ - lonelymo

我不知道 - 所以这是一条注释而不是答案。您需要阅读 node-postgres 库的文档。 - Richard Huxton

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - vitaly-t

Answer 1

我认为最好的方法是使用PostgreSQL的json函数：

client.query('INSERT INTO table (columns) ' +
        'SELECT m.* FROM json_populate_recordset(null::your_custom_type, $1) AS m',
        [JSON.stringify(your_json_object_array)], function(err, result) {
      if(err) {
            console.log(err);
      } else {
            console.log(result);
      }
});

- Sergey Okatov

---

不知道为什么这个没有更多的赞！它可以在不使用任何库的情况下完成工作。 - PirateApp

Answer 2

我搜索了同样的问题，但目前还没有找到解决方案。 使用异步库很容易多次使用查询，并进行必要的错误处理。

也许这个代码变体会有所帮助。 （将10,000个小json对象插入一个空数据库需要6秒）。

克里斯托夫

function insertData(item,callback) {
  client.query('INSERT INTO subscriptions (subscription_guid, employer_guid, employee_guid)
       values ($1,$2,$3)', [
        item.subscription_guid,
        item.employer_guid,
        item.employee_guid
       ], 
  function(err,result) {
    // return any err to async.each iterator
    callback(err);
  })
}
async.each(datasetArr,insertData,function(err) {
  // Release the client to the pg module
  done();
  if (err) {
    set_response(500, err, res);
    logger.error('error running query', err);
    return console.error('error running query', err);
  }
  logger.info('subscription with created');
  set_response(201);
})

- TheFive

Answer 3

要从NodeJS批量插入到Postgresql，更好的选择是使用Postgres提供的“COPY”命令和pg-copy-streams。

代码片段来自：https://gist.github.com/sairamkrish/477d20980611202f46a2d44648f7b14b

/*
  Pseudo code - to serve as a help guide. 
*/
const copyFrom = require('pg-copy-streams').from;
const Readable = require('stream').Readable;
const { Pool,Client } = require('pg');
const fs = require('fs');
const path = require('path');
const datasourcesConfigFilePath = path.join(__dirname,'..','..','server','datasources.json');
const datasources = JSON.parse(fs.readFileSync(datasourcesConfigFilePath, 'utf8'));

const pool = new Pool({
    user: datasources.PG.user,
    host: datasources.PG.host,
    database: datasources.PG.database,
    password: datasources.PG.password,
    port: datasources.PG.port,
});

export const bulkInsert = (employees) => {
  pool.connect().then(client=>{
    let done = () => {
      client.release();
    }
    var stream = client.query(copyFrom('COPY employee (name,age,salary) FROM STDIN'));
    var rs = new Readable;
    let currentIndex = 0;
    rs._read = function () {
      if (currentIndex === employees.length) {
        rs.push(null);
      } else {
        let employee = employees[currentIndex];
        rs.push(employee.name + '\t' + employee.age + '\t' + employee.salary + '\n');
        currentIndex = currentIndex+1;
      }
    };
    let onError = strErr => {
      console.error('Something went wrong:', strErr);
      done();
    };
    rs.on('error', onError);
    stream.on('error', onError);
    stream.on('end',done);
    rs.pipe(stream);
  });
}

更细节的细节 在此链接中解释

- Sairam Krish

Answer 4

创建您的数据结构如下：

[ [val1,val2],[val1,val2] ...]

然后将其转换为字符串：

 JSON.stringify([['a','b'],['c']]).replace(/\[/g,"(").replace(/\]/g,")").replace(/"/g,'\'').slice(1,-1)

将其添加到查询中，你就完成了！同意它有字符串解析成本，但比单个插入要便宜得多。

- Shoeb Ahmed Mogal

---

这不是适当的转义，会导致 SQL 注入。 - Bergi

Answer 5

你可以在Postgresql中使用json_to_recordset来解析JSON。

client.query(
  'SELECT col1, col2
   FROM json_to_recordset($1) AS x("col1" int, "col2" VARCHAR(255));'
  , [JSON.stringify(your_json_object_array)]
)

这与Sergey Okatov的回答非常相似，只是使用了json_populate_recordset代替。

我不知道两种方法之间的区别，但是使用这种方法在处理多列时语法更清晰。

- Madacol

---

使用json_populate_recordset意味着您无需手动输入列名和类型，只要JSON具有正确的属性名称，您只需指定表（行）类型并使用INSERT INTO my_table SELECT * FROM json_populate_recordset(NULL::my_table, $1)即可。 - undefined

Answer 6

使用ORM，例如：Objection。

此外，根据您的数据库服务器和所需的活动连接数量增加连接池大小。

someMovie
  .$relatedQuery('actors')
  .insert([
    {firstName: 'Jennifer', lastName: 'Lawrence'},
    {firstName: 'Bradley', lastName: 'Cooper'}
  ])
  .then(function (actors) {
    console.log(actors[0].firstName);
    console.log(actors[1].firstName);
  });

- arunravimv