Pg-promise: Question: Async/Await Transactions

[jdalrymple]

Is there a way to run transactions in this fashion:

const t = db.tx('my transaction');

try {
    const value1 = t.one('my query)

    await t.commit()
} catch(e) {
    await t.rollback()
}

Similar to : https://node-postgres.com/features/transactions

Thank you !

[vitaly-t]

Transactions in this library are automatic, one never needs to do COMMIT or ROLLBACK manually.

[jdalrymple]

I understand! I want to be able to access a transaction from outside of the callback for a specific implementation, but I can't do that currently. That's why I I'm asking if the implementation shown in node-postgres is possible with pg promise :)

[vitaly-t]

access a transaction from outside of the callback for a specific implementation

Such ideas typically point at misunderstanding of how automatic transactions work, and then looking for a solution to an non-existing problem.

If you can provide an explanation of your specific case, then I should be able to point you at how to approach it within the existing protocol.

[jdalrymple]

Perhaps my solution isn't an automatic solutions I guess.

I'm trying to begin a transaction and maintain a id to that transaction

Perform some queries in that transaction
And then separately commit/rollback that transaction.

Practically exactly like my example code above.

I would like to execute it this way because at the time of the beginning the transaction all the queries that I would like to run within that transaction are not known yet. That's why i can't use the implementation in the docs ( where the execution happens within the callback)

[vitaly-t]

I would like to execute it this way because at the time of the beginning the transaction all the queries that I would like to run within that transaction are not known yet.

So here's the misunderstanding: The callback function returns a promise, so you can chain any dynamic context/queries into it.

The whole protocol is fully dynamic, as it is based on promises.

[jdalrymple]

Ah, Hmm, so how would I execute that? Could you provide an example? Thank you!

[vitaly-t]

Are you asking to how to chain promises? That's all it takes for your dynamic queries to work, but it is not a question to be asking here.

If you are not familiar with promises, you should read about those. I wouldn't be getting into such basics of asynchronous programming here.

[jdalrymple]

😂 No i meant with reference to the db.tx.

Do you mean like this:

db.tx('my transaction', t => {
    // creating a sequence of transaction queries:
    const q1 = t.none('UPDATE users SET active = $1 WHERE id = $2', [true, 123]);
})
.then(t => {
    const q2= t.none('UPDATE users SET active = $1 WHERE id = $2', [true, 123]);
})

Does that mean it could also then be executed like:

await db.tx('my transaction', t => {
    // creating a sequence of transaction queries:
    const q1 = t.none('UPDATE users SET active = $1 WHERE id = $2', [true, 123]);
})

await db.tx('my transaction', t => {
    const q2= t.none('UPDATE users SET active = $1 WHERE id = $2', [true, 123]);
})

Where the second query (q2) is part of the same transaction?

[vitaly-t]

No, both examples you provided are wrong...

Depending on asynchronous pattern required, you change the implementation, but always inside the callback function:

db.tx(t => {
    /*
      either chain you queries one by one, or via batch, or via ES6 generator
   */
})
.then(data => {
    // data = what transaction returned
})
.catch(error => {
    // error
});

Both this website and StackOverflow has hundreds of examples.

[jdalrymple]

So clarifying, transaction queries must happen within the same transaction callback. Thanks