Feature Request: Ability to get raw sql query · Issue #2325 · sequelize/sequelize

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

会搭讪的饭盒 · 删掉Godot C#版本后出现的包引用问题 ...· 2 周前 ·

一直单身的抽屉 · 翻10倍！季后赛打出身价球员诞生，热火备好大 ...· 1 月前 ·

私奔的帽子 · Qu'est-ce qu'une URL ...· 1 月前 ·

活泼的枕头 · 2021年上半年工作总结和下半年工作打算· 2 月前 ·

温暖的煎饼 · 如何使用 C# 将 SVG 转换为 PNG· 3 月前 ·

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement . We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account status: in discussion type: feature All reactions

This is not built in, and since this is not a feature rquest we've see before, it's probably low on the list

Your best bet would probably be to hook directly into the query generator. But mind you, that it's not part of the public API and the arguments might be slightly different.

May I ask why you want the SQL without executing the query?

Sql listeners have beeen removed after we went from event emitters to promises

I have been thinking a bit about the way to do this, and I have an idea - not sure how good / pretty it is though ...

Basically you want to be able to do all the stuff that you normally want with sequelize, except sending the query to the DB. So ideally:

var sql = Model.findAll({ 
  where { foo { $gt: 'bar' }}
});

However, findAll contains promise chains that we cannot easily circumvent. This means that this is more likely

Model.findAll({ 
  where { foo { $gt: 'bar' }}
}).then(function (sql) {
});

Its not pretty, but its the way that would require the least amount of changes.

I'm imagining you either do Model.sql().findAll or pass { sql: true } as an option to the call - which would make sequelize.query return the sql instead of executing it.

Another option might be to limit the sql generation to select / at least to a few query types. In that case we could create a sync function

Model.prototype.select = function (options) { return queryinterface.selectQuery(options)

+1 for this feature and +1 for { sql: true } option.

My use case is to get the sql schema of a model (a.k.a CREATE TABLE sql), so that I can use a sql diff tool for auto-migration. With { sql: true } option, I can use: User.force({ sql: true }).then(executeSQLDiff) .

btw, is there any other existing way to get the sql schema of a model?

{ sql: true } will become an option that entirely changes the behavior of the function. Model.sql() clearly expresses the intention and also it will make it easier to maintain since no function will have to be patched to understand that new option. I think Model.sql() is easier to implement, maintain and express. Besides { sql: true } in the options can become a very rare breaking change if some existing code pass evil multi-purpose options object around that happen to have a sql option!

P.S. all functions defined on the object returned by Model.sql() can be synchronous, since no round-tripping is necessary. @luin isn't

let oldQuery = getTheOldOne(); 
let newQuery = User.sql().force();
let diffReport = diff(oldQuery, newQuery);

more expressive than

User.force({ sql: true }).then(newQuery => {
    let oldQuery = getTheOldOne();
    let diffReport = diff(oldQuery, newQuery);
});

@subjectix even if the sql doesn't hit the database, the call will likely still be async due to hooks.

If you look at the code for findAll you'll see that calling hooks is wrapped in a promise. That means that adding .sql that returns the sql synchronously would take a lot more effort than just making .query return the sql.

With something like co it would be a lot easier since yield runHooks would not need to be wrapped in a promise - it can simply return a promise or a sync value depending on the mode. This would then mean that async hooks could not run in sql mode, so perhaps it would be better to not hooks at all in sql mode?

I completely agree that having it synchronous would be ideal, but it would require a fair deal of work to achieve

I'd suggest against adding more dependencies to the project that it needs. If it must be async then let it be. Doesn't really matter. But if the functions can be somehow re-written to be more defensive in the sense of being sync and also ignore hooks and other async things then I guess they won't need co . But can that actually happen? I don't really know if dialects would like that. Is implementing this feature feasible?

Ah yeah tin that case, it doesn't make any sense to add co . Users will just have to do let sql = yield User.findAll if the want something that looks synchronous. Making two 'different versions' (code paths) of each method depending on whether we are generating sql or not would be too error prone and too hard to maintain

Making two 'different versions' (code paths) of each method depending on whether we are generating sql or not would be too error prone and too hard to maintain

You do have a point there :D

I've implemented a function that will get the SQL for a findAll query. I deleted my previous comment because my newer implementation is better and doesn't rely on exception handling. Essentially, it returns a Promise within the beforeFindAfterOptions hook that will never be resolved, so it will never hit the database.
This function returns a Promise that resolves to a SQL string, minus the semicolon at the end.

function getSqlFromFindAll(Model, options) {
    const id = uuid.create();
    return new Promise((resolve, reject) => {
        Model.addHook('beforeFindAfterOptions', id, options => {
            Model.removeHook('beforeFindAfterOptions', id);
            resolve(Model.sequelize.dialect.QueryGenerator.selectQuery(Model.getTableName(), options, Model).slice(0, -1));
            return new Promise(() => {});
        return Model.findAll(options).catch(reject);
Doesn't work fine when there are parallel findAll at the same table, returning a Promise that never solves sounds to me like a memory leak.
I've managed to avoid Promise hanging as well as thrown exception by copying options when providing it { ...options }
          @jy95 maybe for select queries yes, but as I understand this feature request is for getting any sql query that would be generated by sequelize, also including but not limited to inserts, updates and deletes.
But that might be a good start, yes...
          Just wanted to chime in that I would love this feature as well, like others my use case is that I have a more complex query but would like to leverage some of my existing sequelize-object-query components to populate parts of the query instead of having duplicated where clause construction.
(e.g. we have a generateQueryParams method which returns a sequelize query params object, and I would like to use that to drive the complex query.)
          Started a PR here: https://github.com/sequelize/sequelize/pull/11881/files

Please let me know if you like it or what can be improved or what am I missing that might have issues.
Thanks
          This is actually a really important feature as sequelize can't build certain queries. For instance, if you are querying a model with 1:n association and then want to order by another nested association. After jumping through all the hoops of options and awkward order: [{ model: ModelName, as: 'associationName' }, { model: ModelTwo: as 'deep'} ], 'field', ASC] and subQuery option... you just can't get it.
What happens is you limit the primary model you're running the find on. Then you order the joined records ... On top of the limited set. So you can't sort by that nested association. Unless you make required: true then it works ...but... you then end up with another problem if that association is optional (plus a findAndCountAll is wrong because distinct doesn't work - though I think I saw a patch for that in beta v6, even still I use a separate count() anyway, it doesn't solve the first problem).
Maybe there's a creative way of making a virtual record with custom options with the include?
At any rate, before I pull another tool in here to help make queries...if you could pull the generated query from the functions, find, findAll, count, etc. without running them, we'd be in real good shape. Because you can then make subQuery: false and apply the limit and order to the outside of everything.
With ORMs, it's basically essential to always provide access into what's going on because it's virtually impossible to cover all bases. Though this kind of query should be common enough. Even if there was some crazy complex thing...I'd leave Sequelize's findAll (and I use scopes to help out too here, which are great) as my own sub query. Then I'd wrap that or join it even.
If I can use the queries Sequelize generates kinda like CTEs or something. As like composable chunks of SQL queries, then awesome. I'd probably always have everything I'd ever need.
Is it even possible already to build subqueries from normal finder options? AND scopes. I've used the generator before with selectQuery() to make sub queries for IN () and such, but couldn't figure out how to get it to work with scopes. So after I went through writing all these scopes, I'd have to basically re-write everything.
Thanks! I do appreciate the package and hard work that went into it all.
          Thanks for the link to that! It's pretty easy to just take that and replace the select function where ever you need it. Took me all of 5 minutes to get working. So that's cool. I'm ok with patching it like that. Though making sure the hooks aren't going to run will be next here. Pretty simple, hopefully that gets merged into the various versions (though ideally it would be for more than just select). I still need to upgrade to v5.
edit: well, you can just pass hooks: false in the same options when you pass getRawSql as well. Good enough for now.
Here, you can add a hook to your model even:
const forRawSql = () => {
    YourModel.QueryInterface.select = function (model, tableName, optionsArg) {
      const options = Object.assign({}, optionsArg, { type: this.sequelize.QueryTypes.SELECT, model });
      const query = this.QueryGenerator.selectQuery(tableName, options, model);
      if (options.getRawSql) {
        return Promise.resolve({query});
      return this.sequelize.query(
        query,
        options
    return { getRawSql: true };
  YourModel.addScope('forRawSql', forRawSql);
Still have use hooks: false outside the scope for whatever reason. I can look more into that, but even still not bad unless you use a lot of hooks on selects. I don't really so it won't matter much.
To use the hook above, just like any other:
const q = YourModel.scope(
    method: ['forRawSql'],
  // any others...
const sql = await q.findAll({ hooks: false }); // hooks: false is optional of course
// sql now should be { query: 'SELECT * FROM ...' }
Of course after this, you still need to glue them back together so to speak. It's going to return an array of flattened objects. So you'd need to loop or reduce to get the nested associations. I was trying the option for mapToModel but didn't seem to work. Ah, but the nest: true does. Sorta. You still need to combine records, but each one is at least transformed with dottie.
EDIT: This may lead to issues, and you may prefer to simply override select() at a higher point instead. Either way, the changes in that PR above can be used w/o it being merged in and they are fairly minor edits which is nice.
          @DanielWeinerBT set me on the right path with this. However, the approach doesn't work when generating multiple raw queries asynchronously, or when running findAll calls along with generating raw queries.

I've expanded on his code, and yes, it's one giant hack, might not work for future versions of sequelize, but hell, it might help someone in the same situation, so here it is: https://gist.github.com/slavivanov/29b9f479219b4e3a1b559a2468886e2f
Thank you! Will keep an eye on this...
But as per your use case, hashing the inputs would work as well.
Would you recommend just hashing & stringifying the options object?
          I think we should consider reworking QueryGenerator to fully make it a public API. Right now, it's too much of an internal API.
You should be able to call QueryGenerator#selectQuery(modelOrTable: ModelOrTable, options: FindOptions), with the exact same parameters you would pass to Model.findAll, and it would return the right SQL.
In order to do that, we'll need to move a lot of logic from Model.findAll & other model methods, to QueryGenerator, but I think it's worth it.
Ideally, Model methods should be limited to a single call to QueryInterface & hooks

QueryInterface should be limited to a call to QueryGenerator and a call to Sequelize#queryRaw.
@DanielWeinerBT set me on the right path with this. However, the approach doesn't work when generating multiple raw queries asynchronously, or when running findAll calls along with generating raw queries. I've expanded on his code, and yes, it's one giant hack, might not work for future versions of sequelize, but hell, it might help someone in the same situation, so here it is: https://gist.github.com/slavivanov/29b9f479219b4e3a1b559a2468886e2f
@slavivanov Is there a way to get count query too? The same approach is not working to get count raw query , I am using beforeCount hook and then calling selectQuery method , but this is giving error
I think we should consider reworking QueryGenerator to fully make it a public API. Right now, it's too much of an internal API.
You should be able to call QueryGenerator#selectQuery(modelOrTable: ModelOrTable, options: FindOptions), with the exact same parameters you would pass to Model.findAll, and it would return the right SQL.
In order to do that, we'll need to move a lot of logic from Model.findAll & other model methods, to QueryGenerator, but I think it's worth it.
Ideally, Model methods should be limited to a single call to QueryInterface & hooks QueryInterface should be limited to a call to QueryGenerator and a call to Sequelize#queryRaw.
Something that this allows is for developers to create custom caching strategies built on top of Sequelize. For example, in Ruby's ActiveRecord implementation, there's a low level model cache which works like this:
Before a query is executed as the developer intended, the model modifies it and sends a minified version of it to the database, which only selects a couple of the attributes, we'll use id and updated_at in this example.
So, developer fires await Product.findAll(), but through an interceptor, the query is modified initially, and instead of it, we fire
select md5(array_agg(id || '-' || updated_at)::text) from projects
This will return an unique hash based on the id and updated_at values that matched the query.
We will use this key as a version and we will try to get the result from the cache, like so cache.get(projects:${version}`).
If there's no hit, we will execute the original query and cache the result.
To generate the hash, though, the query must be aggregated, which is currently problematic, because from the interceptor you would need to modify the parameters passed by the developers and it gets very clunky with custom ordering/grouping.
A much simpler scenario would be to create a view or a subquery from the original sql, and wrap it with the query which generates the hash, so that it runs directly on the results of the original query.
This doesn't work, though, because there's no way to get the sql before it is executed.
This is probably a deal breaker for the majority of cache implementations. You could try to stringify the options but even that gets tricky, because symbols are not serialized properly, so that must be accounted for.
And if you want to execute arbitrary sql on top of the original query, you're pretty much out of luck.
I think having the ability to access the raw sql would make sequelize much more viable to be used at scale
          We really need this or expanded abilities of sequelize so that it can create more query types(probably harder?)
Right now there exists a trap of sorts - when you write a fairly large function, generating a query using sequelize with many options, test it, deploy it, and then later find out you want to use one option Sequelize doesn't support... Then you have the options of rewriting all of it to generate raw SQL (not trivial) or extract and modify the sql from sequelize.
    status: in discussion
  For issues and PRs. Community and maintainers are discussing the applicability of the issue.
    type: feature
  For issues and PRs. For new features. Never breaking changes.