Print complete SQL for all queries made by objection.js

Question

I'm looking for a way to capture the raw SQL for all the queries that the Objection.js library executes with the bindings interpolated into the SQL string.

I realize that there's a Knex event handler that I can take advantage of but the second argument to the on('query', data) is an object containing an SQL template with the bindings separate.

e.g.

{
  sql: "select \"accounts\".* from \"accounts\" where \"id\" = ?",
  bindings: [1]
}

I'm wondering if the most elegant way to do this would be to use something like the .toString() method that exists on the QueryBuilder but I don't think a specific instance of a QueryBuilder is available in the callback. Ideally I don't reinvent the wheel and re-write Knex's interpolation method.

Any pointers would be greatly appreciated.

Thank you!

Coarse

Answer 1

Knex / objection.js does not provide any methods that can securely do the interpolation. .toString() can produce invalid results in some cases and they can be vulnerable to sql injection attacks.

If it is only for debugging purposes looking how .toQuery() is implemented helps. https://github.com/knex/knex/blob/e37aeaa31c8ef9c1b07d2e4d3ec6607e557d800d/lib/interface.js#L12

knex.client._formatQuery(sql, bindings, tz)

It is not a public API though so it is not guaranteed to be the same even between patch versions of knex.

Masorete

This is exactly what I was looking for. Thanks for the heads regarding the pitfalls. I'm only using this for debugging purposes. Thank you! – Coarse

Answer 2

You can use the .toKnexQuery() function to pull out the underlying knex query builder and gain access to .toSQL() and .toQuery().

I tested and verified the following example using version 2 of Objection. I couldn't find .toKnexQuery() in the version 1 docs and therefore can't verify it will work with earlier versions of Objection.

// Users.js
const { Model } = require('objection')

class Users extends Model {
  static get tableName() { return 'users' }
  // Insert jsonSchema, relationMappings, etc. here
}

module.exports = Users

const Users = require('./path/to/Users')

const builder = Users.query()
  .findById(1)
  .toKnexQuery()

console.log(builder.toQuery())
// "select `users`.* from `users` where `users`.`id` = 1"

console.log(builder.toSQL())
// {
//   method: 'select',
//   bindings: [ 1 ],
//   sql: 'select `users`.* from `users` where `users`.`id` = ?'
// }

---

It should probably be reiterated that in addition to .toString(), .toQuery() can also be vulnerable to SQL injection attacks (see here).

A more "responsible" way to modify the query might be something like this (with MySQL):

const { sql, bindings } = Users.query()
  .insert({ id: 1 })
  .toKnexQuery()
  .toSQL()
  .toNative()

Users.knex().raw(`${sql} ON DUPLICATE KEY UPDATE foo = ?`, [...bindings, 'bar'])

Outport

This is a useful answer, but did not work regarding the "all queries" consideration. I am using the withGraphFetched() method to get related data and these techniques did not print out the queries for the "subsequent" queries (not sure what to call them... Objection.js doesn't use joins with withGraphFetched but runs follow-up queries). – Rumery