Search code examples
javascriptknex.js

knex query builder and the order of methods called in a chain

Reading knex documentation and knex cheat sheet, I discovered that in provided examples .where() is always called right after table referencing: 

knex('users')
  .where({ id: 2 })
  .update({ name: 'Homer' })
// UPDATE `users` SET `name`='Homer` WHERE `id`=2

The code above looks logical because full data set is narrowed before running .update().

What confused me was that swapping methods produces the same SQL query: 

knex('users')
  .update({ name: 'Homer' }) 
  .where({ id: 2 })
// UPDATE `users` SET `name`='Homer` WHERE `id`=2

a newcomer might intuitively expect WHERE to be applied after UPDATE has been made, in analogy with javascript Array methods.

Trying other examples, like knex.select().table('books'), experimentally, I have figured out that query builder simply "collects" all the methods between knex and .then() (or the end of the statement), and composes them into a single query, and runs it.

I would be completely satisfied if someone confirms the above statement with a link to documentation or some other knex resource.

Solution

  • Usually query builder collects all called builder methods that has been called and order of calling them doesn't matter. This is because generated SQL cannot be generated in any other order.

    However there are some methods, where order of calling them actually matters. Most notable with .clearSelect, .clearWhere and .clearOrder methods http://knexjs.org/#Builder-clear

    Also when multiple columns are updates in single statement, order of updates actually reflects order where SET statements are output.

    Probably there are some other subtleties, where order does matter, but I don't believe that anyone has ever traced all of them.