Get generated SQL query from Sequelize

get_raw_sql.ts

import * as Sequelize from "sequelize";
import uuidv1 = require("uuid/v4");
import { SequelizeModelStatic } from "./sequelize";
import * as pLimit from "p-limit";
import _ = require("lodash");

/**
 * Get generated SQL query from sequelize. Returns a promise that:
 * 1. Adds a hook that receives the prepared options from Sequelize
 *  - Since hooks work on the whole model,
 *    we need to ensure that only one hook is being setup at a time
 *    (using plimit) and that other queries to the same model are not affected.
 * 2. Uses Sequelize QueryGenerator with the options retrived by the hook
 * to generate the query.
 * 3. Removes the hook.
 * 5. Cancel the query in a hook, by throwing an error (which gets ignored in the findAll catch)
 * 6. Resolve the promise with the generated query.
 *
 * This whole approach is one giant hack, but it works for the time being.
 */

// Use queues to generate only one query at a time, since it uses hooks
const findAllHookQueue = pLimit(1);
const createHookQueue = pLimit(1);

const cancelQueryError = "CancelQueryError";
export const getSQLFromFindAll = (
  Model: SequelizeModelStatic<any>,
  options: Sequelize.FindOptions
) => {
  return findAllHookQueue(
    () =>
      new Promise<string>((resolve, reject) => {
        const id = uuidv1();
        Model.addHook(
          "beforeFindAfterOptions",
          id,
          (
            hookOptions: Sequelize.QueryOptions & {
              getGeneratedQueryOnly?: true;
            }
          ) => {
            // if this query is not about generating raw sql, pass it through
            if (!hookOptions.getGeneratedQueryOnly) return;
            Model.removeHook("beforeFindAfterOptions", id);
            resolve(
              (Model.sequelize as any).dialect.QueryGenerator.selectQuery(
                Model.getTableName(),
                _.omit(hookOptions, "getGeneratedQueryOnly"),
                Model
              ).slice(0, -1)
            );
            // Throw error to cancel query execution
            throw new Error(cancelQueryError);
          }
        );
        return Model.findAll({
          ...options,
          getGeneratedQueryOnly: true,
        } as any).catch((e) => {
          if (e.message === cancelQueryError) {
            // this is expected, so ignore the error
          } else {
            // other error, reject with it
            reject(e);
          }
        });
      })
  );
};

export const getSQLFromCreate = (
  Model: SequelizeModelStatic<any>,
  values: Dictionary<any>,
  options: Sequelize.CreateOptions
) => {
  return createHookQueue(
    () =>
      new Promise<string>((resolve, reject) => {
        const id = uuidv1();
        Model.addHook(
          "beforeSave",
          id,
          (
            _model,
            hookOptions: Sequelize.QueryOptions & {
              getGeneratedQueryOnly?: true;
            }
          ) => {
            // if this query is not about generating raw sql, pass it through
            if (!hookOptions.getGeneratedQueryOnly) return;

            Model.removeHook("beforeSave", id);
            resolve(
              (Model.sequelize as any).dialect.QueryGenerator.insertQuery(
                Model.getTableName(),
                values,
                Model && Model.rawAttributes,
                // dont bind parameters, so that we get the full query
                { ...hookOptions, bindParam: false }
              ).query.slice(0, -1)
            );
            // Throw error to cancel query execution
            throw new Error(cancelQueryError);
          }
        );

        return Model.create(values, {
          ...options,
          getGeneratedQueryOnly: true,
        } as any).catch((e) => {
          if (e.message === cancelQueryError) {
            // this is expected, so ignore the error
          } else {
            // other error, reject with it
            reject(e);
          }
        });
      })
  );
};