Nodejs - How to kill a running SQLite query

Question

Possible similar question :

Hi everyone, I am actually using socket.io and sqlite3 modules to perform SELECT query on a SQLite database. When a user click on an OpenLayers Map, its sends a signal to the server (through socket.io) to gather informations by performing spatial request (like intersection, union... using Spatialite extension) and then finally send back data to the client (these are long-running queries (depending of the amount of geometries) ) to show a popup on the map where the user clicked to.

The problem is: if a user click many times on the map, sending many requests to the server, only the last one is important. Imagine that if a query takes 5 sec to be executed, and that a user click 3 times in a second on the map (he just wants the last location where he clicked to be used), then the server will do 3 queries, sending back 3 signals through socket.io (and opening 3 popup, we just need the last one to be opened) ! Is there any solution to kill/abort a running sqlite query with nodejs ?

Example code :

socket.on('askForInfo', function (data) {
    sendInfo(socket, data.latitude, data.longitude);
});

sendInfo definition :

function sendInfo(socket, lat, lng) {
    // Database connection
    var db = new sqlite.Database('some file.sqlite', sqlite.OPEN_READONLY);

    // Load Spatialite extension
    db.loadExtension('mod_spatialite', function(err) {
        // Query doing spatial request
        db.get("VERY LONG SQL QUERY", function(err, row) {
            // Send the data gathered from database
            socket.emit('sendData', row['some sql column']);
        });
    });
}

I want to do something like :

if ("sendInfo didn't finished to emit any signal through socket"
    AND "user did another resquest") 
then
    "kill all running sendInfo function execution and sql query"

I know that if there are many users connected this won't work like that (I may need to use session to know for which user the function is actually gathering data). But I don't find any solution even if there is only one user.

I tryed using AJAX(jquery) instead of socket.io. I can abort the xhr request, but the SQL query is still running even if the request is aborted until she is finish ( using lot of ressources uselessly )

Thanks in advance for your answer.

If you're not afraid of wasting resources and you just want to give the most updated output to the user, instead of "cancelling" requests (which can be complicated), just fire multiple requests in parallel, each one with a unique identifier, and then we you get the response discard all those with the id different from the one of the last request. — ItalyPaleAle, Dec 19 '14 at 18:16
you can use a `debounce` function to wait with execution until some time has passed between user interaction and the actual query being executed, might also be a partial solution — Mark van Straten, Feb 03 '22 at 14:43

score 4 · Accepted Answer · answered Dec 25 '14 at 20:06

Thanks for your answer Qualcuno.

I found a solution, using child_process to query database from another process, killing this one if user do another request.

var cp = require('child_process');
// more stuff ...

socket.on('askForInfo', function (data) {
    // if process is connected, kill it (query isn't terminated)
    if (child.connected) {
        child.kill();
    }

    // create a new process executing 'query_database.js'
    child = cp.fork('./query_database.js', [
        data.latitude, data.longitude
    ]);

    // when invoking [ process.send(some_data_here) ] in child process, fire this event to send data to the user
    child.on('message', function(d) {
        socket.emit('sendData', d)
    };
});

I can't think of anything better either, except that you wouldn't necessarily need a second `.js` script, instead you can also invoke the sqlite binary executable which is probably faster (?) — phil294, Jan 27 '22 at 21:02
@phil294 SQLite nodejs library has interrupt method (see sibling answer), meaning that you don’t need a child process. — Nick Zavaritsky, Feb 04 '22 at 12:52

score 0 · Answer 2 · answered Feb 03 '22 at 09:42

0

Nodejs’ SQLite has interrupt method, albeit currently undocumented: https://github.com/mapbox/node-sqlite3/issues/1205#issuecomment-559983976

answered Feb 03 '22 at 09:42

Nick Zavaritsky

1,429
8
19

Nodejs - How to kill a running SQLite query

2 Answers2