3

Let's say I have two functions: 

export const setThingAlertsInactive = (userID, thingIDs) => {
    return db.any(' UPDATE thing_alerts SET is_active = false WHERE IN (Select * from thing_alerts where user_id = $1 and thing_id IN ($2:csv))', [userID.toString(), thingIDs])
}

export const archiveOrRestoreThings = (thingIDs, archive) => {
    let archivedStatement =''
    if(archive === true){
        archivedStatement = 'archived = current_timestamp'
    } else if(archive === false){
        archivedStatement = 'archived = NULL'
    }
    return db.none(`UPDATE things SET ${archivedStatement} WHERE id IN ($1:csv)`, [thingIDs])
}

I want to run them together so if one fails, the other rolls back. In fact I deliberately left an error in the first SQL Query.

Here is my tx function:

export const archiveOrRestoreThingsAndSetAlert = (userID, thingsIDs, archive) => {
    return db.tx((transaction) => {
        const queries = [archiveOrRestoreThings(thingIDs, archive), setThingAlertsInactive(userID, projectIDs)]
        return transaction.batch(queries)
    })
}

The first query runs and works. The second fails. I need to be able to roll them back in that case. Thanks!

ShadyAmoeba
  • 527
  • 1
  • 4
  • 15

2 Answers2

3

From the author of pg-promise.

The reason it doesn't work for you is because the two query functions use the root database connection context, and not the transaction context/scope, i.e. you are executing the queries outside of the transaction connection/scope.

You can change them to support optional task/transaction context:

export const setThingAlertsInactive = (userID, thingIDs, t) => {
    return (t || db).none(`UPDATE thing_alerts SET is_active = false WHERE
           IN (Select * from thing_alerts where user_id = $1 and thing_id IN ($2:csv))`,
           [userID.toString(), thingIDs]);
}

export const archiveOrRestoreThings = (thingIDs, archive, t) => {
    let archivedStatement = '';
    if(archive === true) {
        archivedStatement = 'archived = current_timestamp'
    } else if(archive === false) {
        archivedStatement = 'archived = NULL'
    }
    return (t || db).none(`UPDATE things SET ${archivedStatement} WHERE id IN ($1:csv)`, 
                          [thingIDs]);
}

And there is no point using batch, which is a legacy method, needed only in special cases:

export const archiveOrRestoreThingsAndSetAlert = (userID, thingsIDs, archive) => {
    return db.tx(async t => {
        await archiveOrRestoreThings(thingIDs, archive, t);
        await setThingAlertsInactive(userID, projectIDs, t);
    })
}
vitaly-t
  • 24,279
  • 15
  • 116
  • 138
1

You need to pass the transaction to archiveOrRestoreThings and setThingAlertsInactive and call .none and .any on the transaction instead of the db. See example code for reference.

JustAnotherDev
  • 11
  • 1