pg-promise: use result of one query in next query within a transaction

node.js, pg-promise, postgresql, transactions

Solution

Method sequence is there to run infinite sequences, which got nothing to do with what you are trying to achieve - a standard / trivial transaction:

await db.tx(async t => {
    const id = await t.one('INSERT INTO table1(a, b) VALUES($1, $2) RETURNING id', [1, 2], a => +a.id);
    await t.none('INSERT INTO table2(id, a_id) VALUES($1, $2)', [1, id]);
});

Problem

I am node.js with pg-promise for postgres, trying to do a transaction with 2 inserts in sequence. The result id of the 1st insert should be used in the next insert in the transaction. Rollback if any of the query fails. Nesting 2nd `db.none` inside `.then()` of 1st `db.one` will NOT rollback 1st query. So using a transaction here. I am stuck at using the result of 1st query in 2nd. Here is what I have now. ``` db.tx(function (t) { return t.sequence([ // generated using pgp.helpers.insert for singleData query t.one("INSERT INTO table(a, b) VALUES('a', 'b') RETURNING id"), t.none("INSERT INTO another_table(id, a_id) VALUES(1, <above_id>), (2, <above_id>)") ]); })... ``` 2nd query is generated using pgp.helpers.insert for multiData query. But that's not feasible wanting to use the previous query's result. isn't it ! Is there a way to get `id` i.e. `<above_id>` from the 1st INSERT ?

Original source