Support aborting queries

[gaurav-cointab]

Issue Creation Checklist

[x] I have read the contribution guidelines

Feature Description

Is your feature request related to a problem? Please describe.

Now I am trying to implement a query kill scenario, where in from code If I decide that the current running DB query is not required, then I would be able to cancel it (keeping mysql in mind). For this I have an idea, that before executing any query where I want it to be killable later, I will execute a "SELECT CONNECTION_ID()", it will return me the connection id of the thread where this query is running.. and then If I execute the query (which is to be made killable) inside that thread id, when what I can do is this, I can kill that query later with "KILL " and my job will be done.

Describe the solution you'd like

For this I think if I can return an ID/the thread ID/the thread object in the response of the query, and then I can resupply that in the next query, then what can be done is I can execute the query in that same thread (if possible)

var sql = "SELECT * FROM abc"; // some query which I can to kill later
db.raw("SELECT CONNECTION_ID()").then(function(data, params) {
// here data will be the connection id via which the query was executed...
// and params can have a object "thread" which can be reused later
db.findOne({thread: params.thread}).then()....
// This query will be executed in the thread which is supplied, if it is up and running, it is not up and running then it will throw an error.
// and later If i want to kill this query, then I can execute the "KILL <data (which contains the connection id)>"
});

Why should this be in Sequelize

Since threadpool are managed inside sequelize, I was thinking of implementing in inside sequelize itself.

Describe alternatives/workarounds you've considered

Additional context

Feature Request Checklist

Is this feature dialect-specific?

• No. This feature is relevant to Sequelize as a whole.
• Yes. This feature only applies to the following dialect(s): MySQL (that I have thought of till now)

Would you be willing to implement this feature by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I don't know how to start, I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

[ephys]

If I understand correctly, what you're trying to do is cancel/abort a query?

[gaurav-cointab]

Yes. I am in need of it. I also think that for this some portions of "sequelize-pool" and "retry-as-promised" will also have to be updated. But I think this is something which has to be looked at.

[ephys]

In my opinion, if we were to support aborting queries, it would be done using AbortSignal.

i.e.

const controller = new AbortController();

const promise = User.findAll({ signal: controller.signal });

controller.abort();

The setup to kill the query would be handled internally by Sequelize

There is a thread discussing aborting transactions, which includes a potential solution for postgres which seems to be based on the same idea. (#11395).

As far as I know it could be simple to implement, at least for pg & MySQL (more research needed though).
We need to pass the abort signal down to Query.run, and let each dialect handle killing the query.

In MySQL it could be something like:

signal.addEventListener('abort', () => {
  connection.query(`KILL QUERY ${connection.processID}`);
}, { once: true });

[gaurav-cointab]

I had read that recently it was introduced, but was under the impression that it would support only fetch calls. If it can be integrated inside sequelize... then that is exactly what I was wanting.

[ephys]

In theory it could work on any query type. But research & testing required to determine how safe it would be on non-SELECT queries.

Edit: Oh sorry you meant fetch as in the JS API fetch().

AbortController is generic, it's actually used in many new APIs, for instance: whatwg/dom#911 :)