I'm running an Express JS web server with a SQLite database accessed through the node sqlite3 package. I'm finding that whenever one user requests a resource that takes a lot of time to query, all subsequent page loads from any other user have to wait for that query to complete. There are absolutely no writes occurring in the database.

Express will still serve pages, but will stall as soon as one of those pages attempts to access the database.

If I run a query in SQLite Studio while a page is running a long query, there is no delay, so separate applications don't seem to have a problem.

Creating multiple sqlite.Database instances doesn't solve the problem.

Switching to the better-sqlite3 package does not solve the problem either.

Am I missing something about how read concurrency works in SQLite, or is there some limitation of Node involved here?

Here is a simplified section of relevant code in my server.

db/connect.js

var sqlite = require('sqlite3').verbose();

var db = new sqlite.Database(dbPath, sqlite.OPEN_READWRITE|sqlite.OPEN_CREATE, function(err) {
    if (err) {
        return console.error(err.message);
    }
    console.log('Database connected successfully');
});

// Convenience wrapper
db.promise = function(command, query, params) {
    return new Promise(function(resolve, reject) {
        db[command](query, params, function(error, result) {
            if(error) { return reject(error); }
            resolve(result);
        });
    });
}

module.exports = db;

routeErrorHandler.js

var createError = require('http-errors');

function routeErrorHandler(fn) {
    return function(req, res, next) {
        Promise.resolve(fn(req, res, next))
            .catch(err => {
                next(createError(500));
            });
    }
}

module.exports = routeErrorHandler;

app.js

var app = require('express')();
var routeErrorHandler = require('./routeErrorHandler');
var router = require('./routes/myPage');
var unrelatedRouter = require('./routes/unrelatedPage');

app.use('/myPage', routeErrorHandler(router));
app.use('/unrelatedPage', routeErrorHandler(unrelatedRouter));

routes/myPage.js

var db = require('../db/connect');
var routeErrorHandler = require('../routeErrorHandler');

router.get('/', routeErrorHandler(async function(req, res, next) {
    var results = await db.promise('all', `
        SELECT * FROM ManyRecords JOIN HeavyJoin USING (shared_id)
    `);

    res.json(results);
}));

routes/unrelatedPage.js

var db = require('../db/connect');
var routeErrorHandler = require('../routeErrorHandler');

router.get('/', routeErrorHandler(async function(req, res, next) {
    var results = await db.promise('all', `
        SELECT * FROM UnrelatedTable JOIN NotTooHeavyJoin USING (shared_id)
    `);

    res.json(results);
}));