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!

darksinge · Answer

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

Mikael Lepistö · Answer

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.

Print complete SQL for all queries made by objection.js

2 Answers

Add your own answers!

Ask a Question