Comment by jlongster | Hacker News Reader

Comment by jlongster | Hacker News Reader

It's mentioned in other comments but I'm going to repeat it as a top-level comment because it still doesn't seem clear enough to most people: sqlite's C API is synchronous and doesn't use threading, and thus it will always block the node process no matter what. Throwing an async API on top of it is useless - all your doing is making it slower by deferring the computation by a few ticks but when it does run, it's still going to block everything!

It's definitely a good example of buying into the "hype" of async without really understanding what's going on. Async is helpful when you need to wait on something in the future (like new packets come in from a socket) but don't want to block the whole process while waiting. It doesn't make sense at all for CPU work that's going to block it regardless.

I've lightly contributed to both better-sqlite3 and node-sqlite3 and the latter's async implementation makes it much more confusing and difficult to work with. And it slows things down considerably.

I switched node-sqlite3 with better-sqlite3 in my Electron app and found non-trivial performance gains. Make sure to run it in a separate process - never run sqlite in the main thread or the same renderer process as your app. If you run it in the main thread, it still blocks the renderer process. I wrote an article about this: https://medium.com/actualbudget/the-horror-of-blocking-elect...

I'm glad somebody finally made a robust synchronous API to sqlite.

Just because SQLite's API is synchronous does not mean that it must block the node process. libuv manages a thread pool, which node uses to offload sync APIs and let the main event loop run. This is described here under "What code runs on the Worker Pool": https://nodejs.org/en/docs/guides/dont-block-the-event-loop/

You don't just get the threadpool by default when you write a native add-on. The native add-on needs to opt into the behavior. I can't find a single place in this source code where they're using the threadpool to schedule synchronous code to run in a separate thread.

Indeed, you have to write your native code to use it. My point is that you can probably write an even better sqlite library by using the thread pool, and that sync APIs can be made to act async.

The fear is justified that somebody would naively use this library on a network-heavy app and block the event loop. The readme should probably do a better job describing how this should be used. It's not too hard to run it in a separate process and create a "db service" that you query async. For many small apps or certain use cases the sync api shouldn't be a problem.

https://github.com/mapbox/node-sqlite3/search?utf8=%E2%9C%93...

Actually, it does mean a synchronous API will block the event loop. Look at the signatures of the worker pool examples.

What that article is saying is that libuv exposes a thread pool that you can opt in to with a native extension instead of bringing your own.

It doesn't mean a synchronous API won't block.

What signatures are you referring to? They all take async callbacks.

Yes, and that's not an synchronous API.

For example, https://nodejs.org/api/crypto.html#crypto_crypto_randombytes... without the callback blocks.

You said "Just because SQLite's API is synchronous does not mean that it must block the node process." I'm saying that's not true.

A function might use threads behind the scenes, but if you're going to synchronously wait for it, you're blocking the event loop.

The OP and I are talking about the C API, not the node.js API, being sync.

No you weren't. Look at your first comment. You're literally relating sync in the C API with Node's API.

My first post was talking about the C API. A sync C API can be made into an async node.js API using the thread pool. That's what node does internally for DNS resolution, fs and other APIs.

Thanks for pointing that out. I was not aware libuv provides facilities for running code in separate threads, since I haven't seen it actually used in any of the of native addons I've looked at yet. It's too bad that node-sqlite doesn't use it.

I retract my comment about "no matter what", but it still just boils down to running sqlite on a separate thread. In my opinion it's probably a lot easier to run sqlite in a separate thread (or process) yourself at the app-level. The code for writing async native bindings in node is very complicated (not even considering the thread pool), and you probably don't need every single operation to be async. That just adds a lot of overhead, when you probably just need a higher-level layer that says "execute these queries on the sqlite process and give me the results".

Would it work to use a Web Worker as the separate process? (Seems simpler than using a separate process with websockets).

Node doesn't have "web workers", so I'm not sure what you mean. Node doesn't come with threading support as far as I know; it's single-threaded by design, but there may be libraries that try to implement it.

Node does in fact come with "threading support", just in a non-obvious way (and not exactly exposed to the end-user). It depends on what module you're talking about, but libuv in general _does_ use a worker pool. For example, the "fs" module will, under the hood, make use of a thread pool. The "http" module use a different strategy depending on the operating system, etc.

"Asynchronous system APIs are used by Node.js whenever possible, but where they do not exist, libuv's threadpool is used to create asynchronous node APIs based on synchronous system APIs."

https://nodejs.org/api/cli.html#cli_uv_threadpool_size_size

Node doesn't by itself, but Electron does, and you may be able to use node modules such as a sqlite one inside it.

You actually can use child_process to make a worker thread, although it would make more sense to just have an async API.

Reply on news.ycombinator.com