Raw SQL with sequelize?

dchester / epilogue

Create flexible REST endpoints and controllers from Sequelize models in your Express app

846 stars 116 forks source link

Raw SQL with sequelize? #187

Open omayhemo opened 8 years ago

omayhemo commented 8 years ago

Is it possible to use the raw sql options built into sequelize, through epilogue?

mbroadst commented 8 years ago

@omayhemo with milestones you can essentially do whatever you want, can you be more specific?

omayhemo commented 8 years ago

We have a very complex query, with wildcards. In our query we need to be able to use the sql underscore wild card.

https://msdn.microsoft.com/en-us/library/ms179859.aspx

Thus far, I haven't found a way to do that through the normal epilogue criteria filter. My first inclination is to use sequalize raw queries.

http://docs.sequelizejs.com/en/latest/docs/raw-queries/

If it's possible to filter in this manner without gaining direct access to sequelize (through epilogue), I'm very happy to implement it that way. Failing that option, I need to handle the query explicitly.

mbroadst commented 8 years ago

@omayhemo maybe this tip would help you: https://github.com/dchester/epilogue/issues/167#issuecomment-223343235 ?

omayhemo commented 8 years ago

Thanks for your response. Given that I hook into before list fetch and add the raw:true option to context.options, where then do I pass the raw sql, on the criteria?

mbroadst commented 8 years ago

oh right I see what you're saying. no, presently we don't have any way of setting some sort of option to execute a sequelize.query("raw data in here"). You have two options:

simply create a milestone before the implemented milestone for your controller (e.g. the milestone for Create is write), put your query into that milestone and then skip the rest of the action.
we could technically support some sort of option in context, but it would be tricky and controller specific.

The first option is the most flexible choice. If you'd like to experiment with the latter option we would need more information, maybe even a model definition and an example of what kinds of output you're looking for.

omayhemo commented 8 years ago

So I can't cast raw sequel to the context.query ? What is the raw option for, then?

And what do you mean skip the rest of the action? Can you give me an example?

mbroadst commented 8 years ago

no, in fact I don't believe context.query is a thing.

according to do the docs, the raw option does the following:

[options.raw=false] Boolean If set to true, values will ignore field and virtual setters.

for building, and:

[options.raw]   Boolean Return raw result. See sequelize.query for more information.

for querying