CODEWITHSUNDEEP
sqlpostgresqlknex.js
Jan Kowalski
Jan Kowalski

Reputation: 293

knex insert multiple rows

I have a problem inserting many rows into postgres db with knex. I have dynamic number of rows needed to be inserted. The result i expect is:

insert row four times (four is for an example. I dont know exact number of inserts as it comes dynamically from frontend):

How i can achieve that? I tried looping it with forEach, but it's async operation so i can't use .then() as it will be called four times

Here's what i tried. i dont know how to set field_id and req.body to take it dynamically.

fields = [1,2,3,4]

Expected result:

knex creates 4 inserts as follows: field_id: 1, product_id: some static id value: frontValue[1] ETC

knex('metadata').insert(
 [{ field_id: fields, 
    product_id: product_id, 
    value: req.body[fields] 
 }]
)

Upvotes: 19

Views: 36658

Answers (1)

Hunter McMillen
Hunter McMillen

Reputation: 61512

If I understand correctly you want to insert 4 records to your metadata table:

{ field_id: 1, product_id: X, value: req.body[1] },
{ field_id: 2, product_id: X, value: req.body[2] },
{ field_id: 3, product_id: X, value: req.body[3] },
{ field_id: 4, product_id: X, value: req.body[4] }

To insert multiple records in the same statement they each need to be separate elements in the array that you supply to Knex (check out the insert docs for additional examples):

const product_id = X;
const fieldsToInsert = fields.map(field => 
  ({ field_id: field, product_id, value: req.body[field] })); 

return knex('metadata').insert(fieldsToInsert)
  .then(() => { /* handle success */ })
  .catch(() => { /* handle failure */});

Upvotes: 30

Related Questions