Sequelize: can you use hooks to add a comment to a query?

Question

Heroku recently posted a list of some good tips for postgres. I was most intreged by the Track the Source of Your Queries section. I was curious if this was something that's possible to use with Sequelize. I know that sequelize has hooks, but wasn't sure if hooks could be used to make actual query string adjustments.

I'm curious if it's possible to use a hook or another Sequelize method to append a comment to Sequelize query (without using .raw) to keep track of where the query was called from.

(Appending and prepending to queries would also be helpful for implementing row-level security, specifically set role / reset role)

Edit: Would it be possible to use sequelize.fn() for this?

Answer 1

If you want to just insert a "tag" into the SQL query you could use Sequelize.literal() to pass a literal string to the query generator. Adding this to options.attributes.include will add it, however it will also need an alias so you would have to pass some kind of value as well.

 Model.findById(id, {
   attributes: {
     include: [
       [Sequelize.literal('/* your comment */ 1'), 'an_alias'],
     ],
   },
 });

This would produce SQL along the lines of

SELECT `model`.`id`, /* your comment */ 1 as `an_alias`
FROM `model` as `model`
WHERE `model`.`id` = ???

I played around with automating this a bit and it probably goes beyond the scope of this answer, but you could modify the Sequelize.Model.prototype before you create a connection using new Sequelize() to tweak the handling of the methods. You would need to do this for all the methods you want to "tag".

 // alias findById() so we can call it once we fiddle with the input
 Sequelize.Model.prototype.findById_untagged = Sequelize.Model.prototype.findById;

 // override the findbyId() method so we can intercept the options.
 Sequelize.Model.prototype.findById = function findById(id, options) {
   // get the caller somehow (I was having trouble accessing the call stack properly)
   const caller = ???;

   // you need to make sure it's defined and you aren't overriding settings, etc
   options.attributes.include.push([Sequelize.literal('/* your comment */ 1'), 'an_alias']);

   // pass it off to the aliased method to continue as normal
   return this.findById_untagged(id, options);
 }

 // create the connection
 const connection = new Sequelize(...);

Note: it may not be possible to do this automagically as Sequelize has use strict so the arguments.caller and arguments.callee properties are not accessible.

2nd Note: if you don't care about modifying the Sequelize.Model prototypes you can also abstract your calls to the Sequelize methods and tweak the options there.

function Wrapper(model) {
  return {
    findById(id, options) {
      // do your stuff
      return model.findById(id, options);
    },
  };
}

Wrapper(Model).findById(id, options);

3rd Note: You can also submit a pull request to add this functionality to Sequelize under a new option value, like options.comment, which is added at the end of the query.

Answer 2

This overrides the sequelize.query() method that's internally used by Sequelize for all queries to add a comment showing the location of the query in the code. It also adds the stack trace to errors thrown.

const excludeLineTexts = ['node_modules', 'internal/process', ' anonymous ', 'runMicrotasks', 'Promise.'];

// overwrite the query() method that Sequelize uses internally for all queries so the error shows where in the code the query is from
sequelize.query = function () {
    let stack;
    const getStack = () => {
        if (!stack) {
            const o = {};
            Error.captureStackTrace(o, sequelize.query);
            stack = o.stack;
        }
        return stack;
    };

    const lines = getStack().split(/\n/g).slice(1);
    const line = lines.find((l) => !excludeLineTexts.some((t) => l.includes(t)));

    if (line) {
        const methodAndPath = line.replace(/(\s+at (async )?|[^a-z0-9.:/\\\-_ ]|:\d+\)?$)/gi, '');

        if (methodAndPath) {
            const comment = `/* ${methodAndPath} */`;
            if (arguments[0]?.query) {
                arguments[0].query = `${comment} ${arguments[0].query}`;
            } else {
                arguments[0] = `${comment} ${arguments[0]}`;
            }
        }
    }

    return Sequelize.prototype.query.apply(this, arguments).catch((err) => {
        err.fullStack = getStack();
        throw err;
    });
};