CODEWITHSUNDEEP
node.jsknex.js
John Lee
John Lee

Reputation: 21

Knex transaction query and Postgres foreign key constraint

I'm using knex.transaction to create a record in an appointments table and multiple records in a user_appointments table where the appointment_id in the latter table references the same field in the former (foreign key constraint). 

db.transaction(trx => {
        return (appointment_id ? 
        trx('appointments')
        .update({type: appointment_type, time_from: time_from, time_to: time_to, title: title, note: note})
        .where('appointment_id', '=', appointment_id)
        .returning('appointment_id')
        : 
        trx
        .insert({type: appointment_type, time_from: time_from, time_to: time_to, title: title, note: note})
        .into('appointments')
        .returning('appointment_id'))
        .then(apptId => {
            return Promise.all(user_ids.map((userid) => {
                console.log('inserting row user id:', userid);
                return db.insert({appointment_id: apptId[0], user_id: userid})
                .into('user_appointment');
            }));
        })
    })
    .then(() => {
        res.json('success');
    })
    .catch(err => {
        res.status(400).json('failed');
    });

I've run into an error because the first query to create an appointment record is not executed before the subsequent queries that reference the appointment id: Key (appointment_id)=(6) is not present in table "appointments".

Am I incorrect in trying to execute these queries in a single transaction (i.e. if there is a foreign key constraint, execute separately)?

Upvotes: 1

Views: 779

Answers (1)

John Lee
John Lee

Reputation: 21

I found the problem. I was referencing 'db' in the insert to 'user_appointment' table, I had to reference 'trx' instead:

return trx.insert({appointment_id: apptId[0], user_id: userid})
                .into('user_appointment');

Upvotes: 1

Related Questions