Include worker threads utility within better-sqlite3

[kentcdodds]

There are definitely some queries that I need to run that are quite slow and I cannot lock up my server for everyone while a user wants to run a slow query. So I'm looking at the worker threads option, but it seems like that's generic enough to be included as a utility within better-sqlite3. Something like this?

import { asyncQuery } from 'better-sqlite3'

asyncQuery('foobar.db', 'SELECT COUNT(DISTINCT "userId") FROM "PostRead" WHERE "userId" IS NOT NULL')

The main difference in what I'm suggesting from what's in the docs is that you'd pass the database file as the first parameter so the worker file could be made more generic. But I'm pretty sure that should work.

[Prinzhorn]

I doubt such a utility will be integrated. Joshua's philosophy has always been that better-sqlite3 is a thin wrapper around SQLite's core functionalities. Anything else is out-of-scope for the package and would increase repository noise and maintenance cost.

The proposed utility is also not really generic enough, nothing beats writing the actual worker code:

1. What about Database options such as timeout?
2. (What about bind params?) - Looks like this is part of the example in the docs already
3. What about variations such as pluck or raw?
4. What if you need different PRAGMA?
5. What about setting up user-defined functions and virtual tables for the new Database connection?
6. What if you need loadExtension for your queries to run?

What I personally use is https://github.com/piscinajs/piscina . It abstracts away all the details (e.g. maxThreads) and allows me to use the same setup code (for user-defined functions etc.) that I use in the main program. It also supports things like AbortController, which I make heavy use of (e.g. tied to the socket#close event). I wouldn't recommend re-inventing the wheel and use a well tested Worker Threads library. It's a pain to work with their vanilla API.

[kentcdodds]

Great points. I'll make a PR to the docs to recommend that worker lib 👍

[JoshuaWise]

@Prinzhorn hit the nail on the head