Knex transactions having queries on multiple table?

Question

I have two tables and I want to write a query that does the following.

Get some data from table1. based on the value received in the above query, insert in table2. I have written the query as following:

await Table1Model.transaction(async trx => {
                // First check if the meeting exists?
                const room = await this.**table1**.query()
                    .select('*')
                    .where({ id: this.m_id, is_deleted: false });

                if (typeof room !== "undefined") {
                    await this.**table2**.query().insert(newUser);
                }
                else {
                    // do something else
                }
            });

Please suggest any better way to do something like this.

I want to know the correct way of writing this kind of transactions using knex or objection js.

Answer 1

You could probably do it like this (untyped example):

async yourFunction(id, newUser) {
  let trx;
  try {
    const room = await this.knex('table1')
                         .select() // no need for '*'
                         .where({ id, is_deleted: false })
                         .transacting(trx); // here is how you bind the transaction
    if (!room?.length) {
      await this.knex('table2').insert(newUser).transacting(trx); // trx again :)
    }

    await trx.commit(); // don't forget to commit!
  catch (error) {
    if (trx) {
      await trx.rollback(); // rollback in case something went wrong
    }
    // handle your error here
}

await yourFunction(1, { name: 'Kumar', col1: 'val1' });