Sequelize: Feature Request: Ability to get raw sql query

[rfink]

Would be nice to be able to get the raw sql query generated when calling a method like 'find' or 'findAll'.

[janmeier]

Model.findAll().on('sql', function (sql) {});

_edit_ no longer possible

[rfink]

My apologies, I left out a caveat: it would be good to be able to get this without actually running the query first.

[janmeier]

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?

[rfink]

One example of where this would be useful is in "thumbprinting" the query, or hashing the string of the query to a key that could be used as a cache key.

[janmeier]

Perhaps you could cache it by stringifying the options object in some way .. Hmm dunno. I'll tag it as a feature request but as i said, proably low on the list. If you feel up to implementing it yourself feel free to ask any questions you might have

[zachguo]

My use case is to compose the generated SQL into a larger complex raw SQL.

[zachguo]

I got on is not a function error when trying to run Model.findAll().on('sql', function (sql) {...});

[mickhansen]

@zachguo on was removed, you'll have to use logging: fn now, as in Model.findAll({logging: fn})

[janmeier]

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)

[zachguo]

@janmeier { sql: true } looks very intuitive to me, not sure about what others think.

[luin]

+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?

[alitaheri]

{ 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);
});

?

[luin]

@subjectix That makes sense. I agree with you.

[alitaheri]

@luin Thanks ^^

[janmeier]

@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

[mickhansen]

@janmeier hooks probably should not be run in sql mode since they could have side effects.

[alitaheri]

@janmeier Well, if hooks have side-effects we'll have a lot more problems than just synchronicity. I believe the nature of this api is pure, and should have absolutely no side effects.

[alitaheri]

@mickhansen But what if hooks affect the emitted sql? :cold_sweat:

[janmeier]

They might affect it, but I believe that's just a limitation we'll have to live with

@mickhansen don't you agree, that we'd need to use co internally in order to make the API synchronous? Of course users could just use co as well, then it would be let sql = yield User.findAll

[mickhansen]

@janmeier co doesn't actually make anything synchronous tho, you can yield primitive values (generally you yield promise) but the method needs to be wrapped so that it returns a promise.

[alitaheri]

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?

[janmeier]

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

[alitaheri]

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

[Chris911]

Is there a way to do this now? Just like @rfink I'd like to use the hash of the generated query to create a cache key. Stringifying the options object is not an option when it has includes referencing other models as it causes Converting circular structure to JSON errors.

[hagabaka]

I've used QueryGenerator to create sub-queries with some success, but so far the limitations are:

• I can't easily use defined scopes
• I have to manually strip the trailing ; and wrap the query in ( ).
  (I tried the subQuery: true option, but it makes QueryGenerator return a query like SELECT table.* FROM (SELECT ... actual query I want ...);

Perhaps an alternative to adding this force-sql mode is to let QueryGenerator support scopes? I have no idea which is easier to implement.

[josser]

The other case (probably rare), is to get raw sql query to execute it later via native driver which support streams api.

[felixfbecker]

@josser If you just want to build the query, you can use the QueryGenerator

[josser]

@felixfbecker No ) I want query generated by sequelize via findAll(), but because it can't stream it I want to use my own way to execute such query

[windsome]

still not include this feature? get raw sql before do query.

[raapperez]

Thanks @felixfbecker the QueryGenerator works for me:

const seq = new sequelize('dbname', 'postgres', 'postgres', {dialect:'postgres'});
seq.dialect.QueryGenerator.selectQuery('users', {where: {id: 32}});
>> 'SELECT * FROM "users" WHERE "users"."id" = 32;'

[andreidiaconescu]

hello,
is this feature implemented ?
How could i get the raw sql for a find() ?

[DStt]

Hello, any update for this feature?

[DanielWeinerBT]

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);
    });
}

[damianobarbati]

Is any there a native way to get a sequelize statement as sql string without executing it today?

My use case: I need to create a temporary table from a "select statement"

[PatrickGeyer]

The QueryGenerator would work for my use case, however it's hard to format the options correctly, whereas with the findAll method it's done by sequelize. An exposed public method to format all the passed options would be great!

[alexcrack]

Another little question.
Thank to @DanielWeinerBT I can get generated query. Now I need to run this query directly with underlying mysql2 dialect library (because I need return query result as a Stream). Where is it instance reference stored in the Sequelize object?

[tzahiba]

@PatrickGeyer Hey, how would u reference it and use it? couldn't find docs regarding it

[JTBS]

@tzahiba If it helps, I was able to get the SQL with below line alone:
Model.sequelize.dialect.QueryGenerator.selectQuery(Model.getTableName(), options, Model);

Thanks to @DanielWeinerBT and @raapperez for sharing code snippets.. it really helped.

[mattyicce]

Nice work on using the QueryGenerator dialect. Would there be anyway to get the query string from a raw query after sequelize replaced all the variables? So Model.sequelize.query('Select * from Users where UserID = :userID', {type: Model.sequelize.QueryTypes.SELECT, replacements: {userID: 1}) should spit out Select * from Users where UserID = 1.

[mmiszy]

Is there a way to get generate SQL query out of Model.create(…), but without actually saving the data in the DB?

It sounds like a combination of this issue and dryRun: https://github.com/sequelize/cli/issues/219

My use case is: I want to generate a raw SQL dump of my DB. However, I need to scramble some sensitive data in it (names, insurance numbers etc.) — I'll be using Faker for it.

[damianobarbati]

any news on this?

[softmantk]

Any updates guys ? I have a complex query to write, but It will be better if parts of the query is generated nicely by using findAll and includes.. So I can append inside my complex query.

[perusopersonale]

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.

[harpcio]

I have a lot of problems with PostreSQL vs MySQL compatibility.
This is working for both.

let ordersCountSQL = sequelize.dialect.QueryGenerator.selectQuery('orders', {
    attributes: [
        [sequelize.fn('COUNT', sequelize.col('orders.id')), 'orders_count']
    ],
    where: {
        customer_id: {
            [Op.eq]: sequelize.col('Customer.id')
        }
    }
}).slice(0, -1);

let totalPurchaseSQL = sequelize.dialect.QueryGenerator.selectQuery('orders', {
    attributes: [
        [sequelize.fn('SUM', sequelize.col('orders.total')), 'total_purchase']
    ],
    where: {
        customer_id: {
            [Op.eq]: sequelize.col('Customer.id')
        }
    }
}).slice(0, -1);

const customers = await Customer.findAndCountAll({
    attributes: {
        include: [
            [sequelize.literal('(' + ordersCountSQL + ')'), 'orders_count'],
            [sequelize.literal('(' + totalPurchaseSQL + ')'), 'total_purchase'],
        ],
    },
    include: [
        {
            association: 'orders',
            model: Order,
            attributes: [],
        },
    ],
    subQuery: false,
    group: ['Customer.id']
});

[rfink]

I just gotta say, the label "hard" made me chuckle. I have a few of those piling up on my projects XD

[shacco]

Seriously guys, give us the feature, we really need that. please!

[sergeyjsg]

My use case for this feature - streaming. Since Sequelize does not support streaming I want to use the underlying pg library to return cursor and work with it. I need a generated query for this.

[papb]

@sergeyjsg This is being tracked on #10347

[CoaPsyFactor]

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 }

[bkitaec]

will be great to have this features

[jy95]

@papb isn't simply possible to make sequelize.dialect.QueryGenerator.selectQuery public ?

[papb]

@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...

[papb]

By the way, whoever wants to start a PR is very welcome (in fact this is true for any issue, not only this one)

[slifty]

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.)

[seromenho]

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

[andrejkaurin]

Would be great if @seromenho PR could be released as it works for me

[Nick-Riggs]

I have use for this also. Any chance of this being merged soon?

[tmaiaroto]

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.

[jy95]

@tmaiaroto Same opinion : features like CTE or raw query are really needed for common advanced stuff

[tmaiaroto]

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.

[slavivanov]

@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

[sezanzeb]

side note: the mentioned QueryGenerator seems to have been renamed to queryGenerator at some point during v6