Must each Knex command in a transaction be submitted only after the previous command's promise has been fulfilled if their order is important?

Question

I'm using the second form of knex transactions (e.g., with 'transacting'). However, this question is probably valid for the first form as well.

The documentation sample shows this:

// Using trx as a transaction object:
knex.transaction(function(trx) {

  const books = [
    {title: 'Canterbury Tales'},
    {title: 'Moby Dick'},
    {title: 'Hamlet'}
  ];

  knex.insert({name: 'Old Books'}, 'id')
    .into('catalogues')
    .transacting(trx)
    .then(function(ids) {
      books.forEach((book) => book.catalogue_id = ids[0]);
      return knex('books').insert(books).transacting(trx);
    })
    .then(trx.commit)
    .catch(trx.rollback);
})

Two "insert" commands are being executed in the transaction. The second one is submitted only after the first one's promise has been fulfilled (i.e., in the promise's 'then' callback).

In the above example this is necessary, since the second command can only be formulated after the result of the first command has come back.

However, there are cases where multiple commands can be formulated in advance. As such, one might execute code like this:

promise1 = knex('table1').insert(...).transacting(trx).then(...)
promise2 = knex('table2').insert(...).transacting(trx).then(...)

Use Promise.all to wait for completion and eventually commit or rollback.

In fact, this code will execute. Knex will queue the commands and send them out over the connection assigned to the transaction one at a time.

However, and this is the gist of my question, empirical evidence while using knex with PostgreSQL seems to show that the order in which the commands are sent to the database is not maintained. In other words, the order in which the commands were submitted to knex is not necessarily the order in which they are sent to the database. It seems that the only way to guarantee that commands are sent in a specific order is to create a promise chain whereby each command is submitted only after the previous command has been fulfilled.

Can anyone comment on this? Is this by design? Might this be a bug?

Answer 1

However, and this is the gist of my question, empirical evidence while using knex with PostgreSQL seems to show that the order in which the commands are sent to the database is not maintained. In other words, the order in which the commands were submitted to knex is not necessarily the order in which they are sent to the database. It seems that the only way to guarantee that commands are sent in a specific order is to create a promise chain whereby each command is submitted only after the previous command has been fulfilled.

Sounds like your code doesn't do what you think it does.

Knex sends queries to the database driver in that order, how you tell it to execute them (by awaiting query builder or by executing .then() method).

If you execute more than 1 query to the same transaction, before earlier has finished node-pg driver buffers the second query and sends it only after the first query has first returned its response.

Use Promise.all to wait for completion and eventually commit or rollback.

If there is no error thrown, this should work.

Promise.all([p1,p2fails,p3,p4,p5])

However if there is exception thrown for example from p2fails promise, then Promise.all will reject instantly and rollback will be executed. After that p3,p4 and p5 are still tried to be executed, since they were already buffered in node-pg to be sent.

Would be safer, more memory efficient (query is stored twice, once in knex builder and once as buffered raw query inside pg driver with large inserts this might be significant) and easier to just wait each query to return before sending the next one. Using Promise.all here doesn't even give any additional concurrency benefits, since queries are still executed sequentially through the same connection.