Node-postgres: How to insert multiple rows with a single query?

[zettam]

I have n number of items. I would like to add them all with a single insert query.

insert into testtable(id,name) values (1,"jack"),(2,"john"),(3,"jill");

I have an array for my rows, and I like to add them all in. So, if I provide pg the correct string (after manually forming it in a variable) it works perfectly fine, but this all seemed a bit pointless to me. I expect pg to handle arrays for me as well, but I'm unable to find the option for that. So I'm asking, is this possible?

How can I insert multiple rows from an array of rows, with a single insert query with this library?

Would love to know. Thank you.

[vitaly-t]

No such thing supported. This is not an ORM, this library just executes queries.

[zettam]

hm, okay.
What's the best way to handle this situation in this case? Sending multiple queries is pretty costy hen you can do the same with a single query.

[vitaly-t]

You can concatenate your inserts. However, since this library doesn't provide a separate query formatting, you can't really do it correctly.

You can do it with pg-promise though. Article Performance Boost echoes all your concerns nicely.

[langpavel]

Prepare your insert statement

[rpedela]

Using pg-format with a nested array:

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

var values = [
    [ 1, 'jack' ],
    [ 2, 'john' ],
    [ 3, 'jill' ],
];
console.log(format('INSERT INTO test_table (id, name) VALUES %L', values));
// INSERT INTO test_table (id, name) VALUES ('1', 'jack'), ('2', 'john'), ('3', 'jill')

Disclaimer: I wrote pg-format.

[awenger]

You could also do something like:

var query = 'INSERT INTO table (SELECT (data->>'c1')::int, (data->>'c2')::text FROM (SELECT json_array_elements($1::json) AS data) tmp)'
var data = JSON.stringify([{c1: 1, c2: 'text1'},{c1: 2, c2: 'text2'}])

client.query(query, [data], ...)

Formatted version of the query:

INSERT INTO table (
  SELECT 
    -- extract the props from the json object so that they match the table cols
    (data->>'c1')::int, (data->>'c2')::text
  FROM (
    -- expand the json array
    SELECT json_array_elements($1::json) AS data
  ) tmp
)

[joskuijpers]

@zettam Did any of the above solve the problem for you?

[x0days]

You can user this plugin https://www.npmjs.com/package/mysql-insert-multiple

[charmander]

@x0days That’s for MySQL, not PostgreSQL.

@zettam:

pg.query(
    "INSERT INTO testtable (id, name) SELECT * FROM UNNEST ($1::int[], $2::text[])",
    [
        [1, 2, 3],
        ["Jack", "John", "Jill"],
    ]
)

[barbu110]

This is not an ORM, this library just executes queries.

You don't need an ORM for that. PostgreSQL supports this natively, as stated in their docs

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

[bloodorca]

Since Postgres supports multi-record inserts using syntax.

 INSERT INTO videos (title, author) VALUES 
    ('Peru is a nice country.', 'limaGuy'), 
    ('Breaking bad is a great show.', 'mikeGuy'), 
    ('I like the winter.', 'novemberGuy'); 

I wrote a helper function called expand. It creates the query placeholders.

// expand(3, 2) returns "($1, $2), ($3, $4), ($5, $6)" 
function expand(rowCount, columnCount, startAt=1){
  var index = startAt
  return Array(rowCount).fill(0).map(v => `(${Array(columnCount).fill(0).map(v => `$${index++}`).join(", ")})`).join(", ")
}

// flatten([[1, 2], [3, 4]]) returns [1, 2, 3, 4]
function flatten(arr){
  var newArr = []
  arr.forEach(v => v.forEach(p => newArr.push(p)))
  return newArr
}

var videos = [["Peru is a nice country.", "limaGuy"], ["Breaking bad is a great show.", "mikeGuy"], ["I like the winter.", "novemberGuy"]]

Query(
    `INSERT INTO videos (title, author) VALUES ${expand(videos.length, 2)}`, 
    flatten(videos)
)

[ZiaDev]

You can use this package https://www.npmjs.com/package/pg-essential. You just need to call it's executeBulkInsertion function like

``` let bulkData = [ ];
bulkData.push({
id: 1,
name: "foo"
});
bulkData.push({
id: 2,
name: "foo_2"
});
const columns = ["id","name"];
await db.executeBulkInsertion(bulkData, columns, "[tablename]")

[nhevia]

I wrote a helper function called expand. It creates the query placeholders.

// expand(3, 2) returns "($1, $2), ($3, $4), ($5, $6)" 
function expand(rowCount, columnCount, startAt=1){
  var index = startAt
  return Array(rowCount).fill(0).map(v => `(${Array(columnCount).fill(0).map(v => `$${index++}`).join(", ")})`).join(", ")
}

// flatten([[1, 2], [3, 4]]) returns [1, 2, 3, 4]
function flatten(arr){
  var newArr = []
  arr.forEach(v => v.forEach(p => newArr.push(p)))
  return newArr
}

This is great.
Since ES6 you can simplify using Array.prototype.flat() instead of the flatten function

const arr = [[1, 2], [3, 4]]
console.log(arr.flat()) // [1, 2, 3, 4]