Pg-promise: Starting a transaction if none found (txIf?)

[rodgarcialima]

I was trying to use txIf for this but it didn't work, or I didn't understand the right concept for it

I have two methods.

Method 1:

const getByCnpj = async (cnpj, t) => {
  return await db.txe(t, async t => {
    return await empresasRepository.getByCnpj(cnpj, t);
  });
};

Method 2:

const update = async (empresa, t) => {
  return await db.txe(t, async t => {
    return await empresasRepository.update(empresa, t);
  });
};

I want my methods on the same transaction, and this is OK, it's already working.
Example 1 (Calling from another service):

const example1 = async cnpj => {
  await db.tx(async t => {
    const emp = await getByCnpj(cnpj, t);
    await update(emp, t);
  });
}

But I want to call my services without starting a transaction.
Example 2 (Calling from a controller):

exports.empresasGetByCnpj = async (req, res) => {
  res.send(await getByCnpj(req.params.cnpj));
};

I extended the db with txe (tx extended):

extend: obj => {
    obj.txe = async (t, cb) =>
      t
        ? await cb(t) // Reuse tran
        : await obj.tx(async t => await cb(t)); // New tran
  },

Is this the right way to do it or I'm using txIf the wrong way?

Thank you, and congrats for the amazing job.

Rod

[vitaly-t]

Is this the right way to do it or I'm using txIf the wrong way?

I do not see a single place where you are using [txIf]. And the API for the method is detailed enough. If it is not, then we can amend it.

What have you tried that didn't work?

You do not need the txe extension, it is equivalent to calling [txIf] with default options.

[rodgarcialima]

Tried to use the txIf but didn't work.

The txe allows me to call a service method without passing a transaction.

Example:

const serviceMethod = (t) => {
  if (t) {
    callAnotherMethod(t);
  } else {
    db.tx(t => {
      callAnotherMethod(t);
    });
  }
}

How can I do the above code with txIf?

Thx

[vitaly-t]

This should work:

db.txIf(callAnotherMethod);

Tried to use the txIf but didn't work.

Tried to use it how? What exactly didn't work?

[rodgarcialima]

Maybe I'm using the wrong way

const insert = async (empresa, t) => {
  return await db.txIf(async t => {
    return await empresasRepository.insert(empresa, t);
  });
};

[vitaly-t]

What is t at the top level, in async (empresa, t)? Is that the current database context? I need to understand the context in which the top-level call occurs.

Also, you do not need to use await after return.

[rodgarcialima]

t should be a transaction or null|undefined. If null|undefined it must start a new transaction and pass it to the calling methods.

[vitaly-t]

Then your code can be something like this:

const insert = async (empresa, context) => {
  return (context || db).txIf(t => empresasRepository.insert(empresa, t));
};

[rodgarcialima]

This is a test using (context || db).txIf...:

This is the same test using txe:

I think I'm gonna use the txe.

Thx

[vitaly-t]

Your code didn't look right from the beginning, trying to use a task or transaction for executing a single query. That's wrong to begin with, you do not need either.

And especially when executing multiple inserts, that's going in the wrong direction. See this question to understand how to do multi-row inserts.

And the code that you showed in the log requires only 2 queries, at most, not 6.

Here's complete example:

// static object:
const cs = new pgp.helpers.ColumnSet(['nome', 'cnpj', 'is_ativo'], {table: 'empresas'});

const data = [
{
    nome: 'Walter Industries Inc',
    cnpj: '51810662080501',
    is_ativo: false
},
/* etc. */
];

db.task('do-it-right', async t => {
    const inserts = pgp.helpers.insert(data, cs);
    await t.none(inserts);

    return t.any('SELECT * FROM empresas WHERE cod_empresa IN (${codes:list})', {
        codes: [1313, 1314, 1315]
    });
});

[rodgarcialima]

I agree with you, but this is just one of my tests.

A more realistic use case would be:

const createOrder = async (budgetId, t) => {
  return db.txe(t, async t => {
    const budget = budgetService.getById(budgetid, t);
    const order = orderService.createOrderFromBudget(budget, t);
    dispatchOrderEmail(order, t);
    dispatchDeliveryMsg(order, t);
    ...
    return order;
  });
};

And createOrder could be called with or without a transaction:

const example1 = () => {
  createOrder(12345);
}

const example2 = (t) => {
  db.txe(t, async t=> {
    createOrder(12345, t);
  });
}

[vitaly-t]

Ok, it is up to you how you use it, according the the actual scenario.

To wrap this up, one uses txIf, if you want a task when already in transaction. But if you do not want to create a task when in transaction, then you can use a simple function for it:

async function txe(cb, dbContext) {
    if(dbContext.ctx && dbContext.ctx.inTransaction) {
         // already in transaction;
        return cb(dbContext);
    } else {
        return dbContext.tx(cb);
    }
}

Or, a proper way to implement it via the protocol extension:

extend(obj) {
    obj.txe = cb => obj.ctx && obj.ctx.inTransaction ? cb(obj) : obj.tx(cb);
  },

[rodgarcialima]

Well, looks like I'm doing something wrong.

I'm extending the protocol with your code:
obj.txe2 = cb => (obj.ctx && obj.ctx.inTransaction ? cb(obj) : obj.tx(cb));

[vitaly-t]

Actually, i think it will all makes sense, if you start tracking your query calls properly.

You can update your extension to fully use transaction/task parameters like this:

extend(obj) {
    obj.txe = function(options, cb) {
        const args = pgp.utils.taskArgs(arguments); // prepare task arguments
        return obj.ctx && obj.ctx.inTransaction ? args.cb(obj) : obj.tx.apply(this, args);
    }
  },

See taskArgs.

The change above makes it possible for you to pass in a tag string as optional first parameter, the same as for standard tasks and transactions.

The changes we made previously also made the code properly asynchronous, while the over-use of await you had before was synchronizing it. You will see it now, by using this updated txe, and passing a string tag into it. Then you will see the actual execution logic/sequence, instead of those nameless tx logs.

[rodgarcialima]

Sorry for bugging you but I don't know what I'm doing wrong:

Extension:

Methods:

Test:

The batch insert is not cool, this is one of the reasons I'm migrating to pg-promise :), and I'm not using it yet.

This is my test result:

[vitaly-t]

I'm not sure I can help any further. It would require seeing the entire code and possibly debugging it to understand what you are doing underneath. You will have to examine it yourself, I'm afraid.

I can only see that you are trying to create multiple transactions, one for each insert, which is wrong, hence the log result. You need one transaction to wrap this up.

[vitaly-t]

@rodgarcialima If you have an update, please post it here. But for now, this has been discussed in length, so I'm closing it.