4

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 ?

mythicalcoder
  • 3,143
  • 1
  • 32
  • 42

1 Answers1

9

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]);
});
vitaly-t
  • 24,279
  • 15
  • 116
  • 138
  • ok, Standard transaction is the way to go then. Now I can created the second insert query with `pgp.helpers.insert` after `.then(id => {`. Isn't it ? I will try this out. – mythicalcoder May 05 '17 at 09:26
  • just wanted to take the id from previous transaction and create multiData insert query pgp helper. It worked. Thanks. – mythicalcoder May 05 '17 at 09:49
  • How would you do the same using `async`/`await`? Is `var id = await t.one('INSERT...` and then use `id` in the second query equivalent to a nested transaction? – ezorita Oct 25 '18 at 17:12
  • 1
    @ezorita https://github.com/vitaly-t/pg-promise/blob/master/examples/select-insert.md – vitaly-t Oct 25 '18 at 17:35
  • 1
    upvoted! just wanted to say you are totally awesome , anything and almost everything is either answered in your github issues or here – PirateApp Jun 25 '20 at 06:52
  • Updated to use `async` syntax. – vitaly-t Jun 25 '20 at 15:48