How do I properly insert multiple rows into PG with node-postgres?

Question

A single row can be inserted like this:

client.query("insert into tableName (name, email) values ($1, $2) ", ['john', 'john@gmail.com'], callBack)

This approach automatically comments out any special characters.

How do i insert multiple rows at once?

I need to implement this:

"insert into tableName (name, email) values ('john', 'john@gmail.com'), ('jane', 'jane@gmail.com')"

I can just use js string operators to compile such rows manually, but then i need to add special characters escape somehow.

regarding to pg documentation this approach is very undesirable due to performance decrease — stkvtflw, Jan 25 '16 at 10:38
If executing 2 inserts instead of 1 will danger the performance of your application, then `node-postgres`, isn't for you at all. But I believe you are looking at it the wrong way, trying to optimize where you shouldn't. This library can insert 10,000 records in under 1 second easily. — vitaly-t, Jan 25 '16 at 10:47
was the answer satisfactory in your case? If so, please accept it. — vitaly-t, Apr 05 '16 at 20:11

score 8 · Answer 1 · edited May 25 '21 at 11:18

8

One other way using PostgreSQL json functions:

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

edited May 25 '21 at 11:18

Brian Burns

14,953
5
69
59

answered May 11 '16 at 16:16

Sergey Okatov

826
11
18

You'd probably use `NULL::table` for the type, though you'll need to `SELECT` the same individual columns that are listed in the `INSERT`. – Bergi Dec 05 '20 at 16:45

score 8 · Answer 2 · edited May 25 '21 at 11:20

8

Use pg-format like below.

var format = require('pg-format');

var values = [
  [7, 'john22', 'john22@gmail.com', '9999999922'], 
  [6, 'testvk', 'testvk@gmail.com', '88888888888']
];
client.query(format('INSERT INTO users (id, name, email, phone) VALUES %L', values),[], (err, result)=>{
  console.log(err);
  console.log(result);
});

edited May 25 '21 at 11:20

Brian Burns

14,953
5
69
59

answered Jul 30 '20 at 07:11

web.dev.etc

111
1
4

vitaly-t · Accepted Answer · 2019-09-08T07:33:39.107

Following this article: Performance Boost from pg-promise library, and its suggested approach:

// Concatenates an array of objects or arrays of values, according to the template,
// to use with insert queries. Can be used either as a class type or as a function.
//
// template = formatting template string
// data = array of either objects or arrays of values
function Inserts(template, data) {
    if (!(this instanceof Inserts)) {
        return new Inserts(template, data);
    }
    this._rawDBType = true;
    this.formatDBType = function () {
        return data.map(d=>'(' + pgp.as.format(template, d) + ')').join(',');
    };
}

An example of using it, exactly as in your case:

var users = [['John', 23], ['Mike', 30], ['David', 18]];

db.none('INSERT INTO Users(name, age) VALUES $1', Inserts('$1, $2', users))
    .then(data=> {
        // OK, all records have been inserted
    })
    .catch(error=> {
        // Error, no records inserted
    });

And it will work with an array of objects as well:

var users = [{name: 'John', age: 23}, {name: 'Mike', age: 30}, {name: 'David', age: 18}];

db.none('INSERT INTO Users(name, age) VALUES $1', Inserts('${name}, ${age}', users))
    .then(data=> {
        // OK, all records have been inserted
    })
    .catch(error=> {
        // Error, no records inserted
    });

UPDATE-1

For a high-performance approach via a single INSERT query see Multi-row insert with pg-promise.

UPDATE-2

The information here is quite old now, see the latest syntax for Custom Type Formatting. What used to be _rawDBType is now rawType, and formatDBType was renamed into toPostgres.

score 0 · Answer 4 · answered Dec 18 '20 at 18:34

You are going to have to generate the query dynamically. Although possible, this is risky, and could easily lead to SQL Injection vulnerabilities if you do it wrong. It's also easy to end up with off by one errors between the index of your parameters in the query and the parameters you're passing in.

That being said, here is an example of how you could do write this, assuming you have an array of users that looks like {name: string, email: string}:

client.query(
  `INSERT INTO table_name (name, email) VALUES ${users.map(() => `(?, ?)`).join(',')}`,
  users.reduce((params, u) => params.concat([u.name, u.email]), []),
  callBack,
)

An alternative approach, is to use a library like @databases/pg (which I wrote):

await db.query(sql`
  INSERT INTO table_name (name, email)
  VALUES ${sql.join(users.map(u => sql`(${u.name}, ${u.email})`), ',')}
`)

@databases requires the query to be tagged with sql and uses that to ensure any user data you pass is always automatically escaped. This also lets you write the parameters inline, which I think makes the code much more readable.

score -1 · Answer 5 · answered Jan 25 '16 at 10:27

-1

client.query("insert into tableName (name, email) values ($1, $2),($3, $4) ", ['john', 'john@gmail.com','john', 'john@gmail.com'], callBack)

doesn't help? Futher more, you can manually generate a string for query:

insert into tableName (name, email) values (" +var1 + "," + var2 + "),(" +var3 + ", " +var4+ ") "

if you read here, https://github.com/brianc/node-postgres/issues/530 , you can see the same implementation.

answered Jan 25 '16 at 10:27

Vyacheslav

23,112
16
96
174

4

This would work fine for a small number of inserts, but for hundreds of inserts, you need something in a loop. Furthermore, your second suggestion would be vulnerable to SQL injection. – Chris Watts Jun 04 '18 at 15:48

How do I properly insert multiple rows into PG with node-postgres?

5 Answers5

Linked

Related