Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Include worker threads utility within better-sqlite3 #914

Closed
kentcdodds opened this issue Nov 29, 2022 · 3 comments · May be fixed by #916
Closed

Include worker threads utility within better-sqlite3 #914

kentcdodds opened this issue Nov 29, 2022 · 3 comments · May be fixed by #916

Comments

@kentcdodds
Copy link

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
Copy link
Contributor

Prinzhorn commented Nov 30, 2022

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
Copy link
Author

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

kentcdodds added a commit to kentcdodds/better-sqlite3 that referenced this issue Nov 30, 2022
@JoshuaWise
Copy link
Member

@Prinzhorn hit the nail on the head

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Development

Successfully merging a pull request may close this issue.

3 participants