Small issue with transactions and query objects

[disbelief]

I got tripped up by a subtle bug when chaining together queries in a transaction using the (awesome) helper methods that serverless-mysql provides.

The problem arises from this line

Javascript's fn.apply() expects the second argument to be an array. This works fine when calling query with the array argument format:

query(['INSERT INTO users (`name`, `email`) VALUES (?, ?)', ['joe', '[email protected]']])

However I'm using the "advanced options" argument format which is an Object, and not an array:

query({
  sql: 'INSERT INTO users (`name`, `email`) VALUES (?, ?)',
  values: ['joe', '[email protected]']
});

And so I found that fn.apply(this, optionsObject) was actually being called with an empty arguments array fn([]).

Here's my full (non-working) code for clarity:

let userQuery = {
  sql: 'INSERT INTO users (`name`, `email`) VALUES (?, ?)',
  values: ['joe', '[email protected]']
};

let result = await connection
    .transaction()
    .query(userQuery)
    .query(({ insertId }) => {
      return {
        sql: 'INSERT INTO companies (`name`, `user_id`) VALUES (?, ?)',
        values: ['Acme Inc.', insertId]
      };
    })
    .rollback(e => console.error(e))
    .commit();

The above inserts a user, does not insert a company, and does not throw an error or rollback the transaction.

To fix this on the caller side, I just have to make the chained query() callbacks return their query object wrapped in an array:

let userQuery = {
  sql: 'INSERT INTO users (`name`, `email`) VALUES (?, ?)',
  values: ['joe', '[email protected]']
};

let result = await connection
    .transaction()
    .query(userQuery)
    .query(({ insertId }) => {
      return [
        {
          sql: 'INSERT INTO companies (`name`, `user_id`) VALUES (?, ?)',
          values: ['Acme Inc.', insertId]
        }
      ];
    })
    .rollback(e => console.error(e))
    .commit();

The first userQuery works as is because it's not a function argument and just a simple query.

This seems like something that should be handled by the library though in my opinion. Perhaps by wrapping the result of the chained function in an array if it is not one already.

[ddanielgc]

Yes. I think my case is related somehow with yours.

Even the documentation example with transaction, when you have a conditional statement only works with a return like this:

let results = await mysql.transaction()
  .query('DELETE FROM table WHERE id = ?', [someVar])
  .query((r) => {
    if (r.affectedRows > 0) {
      return ['UPDATE anotherTable SET x = 1 WHERE id = ?', [someVar]]
    } else {
      return null
    }
  })
  .rollback(e => { /* do something with the error */ }) // optional
  .commit() // execute the queries

I figured it out because my standard lint was complaining about it:

"Expected an assignment or function call and instead saw an expression"