CODEWITHSUNDEEP
javascriptpostgresqlpsqlknex.js
Ehtisham Shahid Abbasi
Ehtisham Shahid Abbasi

Reputation: 141

How do I can insert array in multiple rows in psql using knexjs

I am using transaction outta knexjs to insert values in 2 tables (1. result, 2. result detail). while inserting values in table 2 (resultdetail), I have an array of objects whose size varies with every entry (based on no. of subjects). 

{
	"name": "Sally",
	"fathername": "John",
	"rollno": "85a76",
	"classname": "class1",
	"detail": [
		{
			"subject": "History",
			"marksobt": "50",
			"totalmarks": "100"
		},
		{
			"subject": "French",
			"marksobt": "75",
			"totalmarks": "75"
		}
	]
}

I am trying following code to do this. (Again, the size of "detail" array varies with no. of subjects)

const { name, fathername, rollno, classname } = req.body;
	db.transaction(trx => {
		db.insert({
			name: name,
			fathername: fathername,
			rollno: rollno,
			classname: classname
		}).into('results')
		.transacting(trx)
		.returning('rollno')
		.then(roll_no => {
			req.body.detail.map(result => {
				return trx('resultdetail')
				.returning('*')
				.insert({
					rollno: roll_no[0],
					subject: result.subject,
					marksobt: result.marksobt,
					totalmarks: result.totalmarks
				}).then(console.log)
			})
		})
		.then(trx.commit)
		.catch(trx.rollback)
	})
	.catch(err => res.status(400).json(err))
But end up getting following error.

Unhandled rejection Error: Transaction query already complete, run with DEBUG=knex:tx for more info

Upvotes: 0

Views: 2631

Answers (1)

Mikael Lepistö
Mikael Lepistö

Reputation: 19728

insertedRows = await knex('results').returning('*').insert([
    {
        "subject": "History",
        "marksobt": "50",
        "totalmarks": "100"
    },
    {
        "subject": "French",
        "marksobt": "75",
        "totalmarks": "75"
    }
]);

But mainly your problem is that you launch multiple queries + commit at the same time without waiting that earlier queries are ready...

When you add stuff to second table it should be something like this:

.then(roll_no => {
  let resultsToAdd = req.body.detail.map(result => {
     return {
       rollno: roll_no[0],
       subject: result.subject,
       marksobt: result.marksobt,
       totalmarks: result.totalmarks
     };
   });

   return return trx('resultdetail')
     .returning('*')
     .insert(resultsToAdd);
})

Upvotes: 2

Related Questions