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

I have the following code in nodejs that uses the pg (https://github.com/brianc/node-postgres) My code to create subscriptions for an employee is as such.

我在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);

      });

As you have already noticed datasetArr is an array. I would like to create mass subscriptions for more than one employee at a time. However I would not like to loop through the array. Is there a way to do it out of the box with pg?

您已经注意到datasetArr是一个数组。我想一次为多个员工创建大量订阅。但是我不想循环遍历数组。有没有办法用pg开箱即用？

5 个解决方案

#1

I did a search for the same question, but found no solution yet. With the async library it is very simple to use the query several times, and do the necessary error handling.

我搜索了同样的问题，但还没有找到解决方案。使用异步库，可以非常简单地多次使用查询，并进行必要的错误处理。

May be this code variant helps. (for inserting 10.000 small json objects to an empty database it took 6 sec).

可能是这个代码变体有帮助。（用于将10.000个小json对象插入空数据库，花费6秒）。

Christoph

克里斯托夫

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

#2

It looks for me that the best way is the usage PostgreSQL json functions:

它找我，最好的方法是使用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);
      }
});

#3

To do Bulk insert into Postgresql from NodeJS, the better option would be to use 'COPY' Command provided by Postgres and pg-copy-streams.

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

Code snippet from : https://gist.github.com/sairamkrish/477d20980611202f46a2d44648f7b14b

代码片段来自：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);
  });
}

Finer details explained in this link

此链接中解释了更精细的细节

#4

Create your data structure as:

创建您的数据结构：

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

Then convert it into a string:

然后将其转换为字符串：

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

append it to the query and you are done!

将它追加到查询中就完成了！

Agreed it has string parsing costs but its way cheaper than single inserts.

同意它有字符串解析成本，但它比单插入更便宜。

#5

-2

Use an ORM; eg: Objection.

使用ORM;例如：异议。

Also, Increase the Connection pool size based on your db server and the number of active connection you need.

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

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

#1