I want to use transaction and multirow so when I have 2 multi row queries and one fails, do the 2 mutli row queries makes a rollback ? I know when I use a multi row with one query and one fails that makes a rollback. But what if I have 2 queries where I both use multi queries like this:
const pgp = require('pg-promise')({
/* initialization options */
capSQL: true // capitalize all generated SQL
});
const db = pgp(/*connection*/);
const cs = new pgp.helpers.ColumnSet(['col_a', 'col_b'], {table: 'tmp'});
const cs2 = new pgp.helpers.ColumnSet(['col_a2', 'col_b2'], {table: 'tmp2'});
const values = [{col_a: 'a1', col_b: 'b1'}, {col_a: 'a2', col_b: 'b2'}];
const values2 = [{col_a: 'a1', col_b: 'b1'}, {col_a: 'a2', col_b: 'b2'}];
const query = pgp.helpers.insert(values, cs);
//=> INSERT INTO "tmp"("col_a","col_b") VALUES('a1','b1'),('a2','b2');
// HERE QUERY 2
const query2 = pgp.helpers.insert(values2, cs2);
//=> INSERT INTO "tmp2"("col_a2","col_b2") VALUES('a1','b1'),('a2','b2');
// executing the query:
await db.none(query);
await db.none(query2);
So how can I say when query 1 or query 2 fail then all should be a rollback ?
So how can I say when query 1 or query 2 fail then all should be a rollback ?
By using transactions:
await db.tx(async t => {
await t.none(query);
await t.none(query2);
);