Skip to content

Instantly share code, notes, and snippets.

@slavivanov
Created June 18, 2020 09:12
Show Gist options
  • Save slavivanov/29b9f479219b4e3a1b559a2468886e2f to your computer and use it in GitHub Desktop.
Save slavivanov/29b9f479219b4e3a1b559a2468886e2f to your computer and use it in GitHub Desktop.
Get generated SQL query from Sequelize
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);
}
});
})
);
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment