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!

Answer 1

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'])