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/
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".
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.
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.
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."
> Easy-to-use synchronous API (faster than an asynchronous API... yes, you read that correctly)
Isn't that entirely unsurprising?
The point of async is not that it's faster, it's that other shit can run while you do your IO. Of course your sequence of operations is faster when you never yield to other tasks.
Well even CPU-bound async functions can have benefits in node.js as they can free up the single javascript thread to do other work while it does the CPU bound work on another thread in the native code.
It's definitely a bastardization of the terms a bit, but at its core it's basically a message-passing multi-process system that hides the implementation behind the well-understood (to most programmers that use node) async i/o
Well, CPU-bound work should be done off the event loop thread when means out of process in Node which introduces an I/O boundary and thus an async call.
I've considered this for an Electron app, but the synchronous API is actually an issue for a responsive UI. It it's probably possible to work around it by using a separate worker process for the database, but then you're just back to doing things asynchronously.
With async-await being common now, a synchronous API is also not required to have an easy-to-use API.
node-sqlite3 does have a massive performance problem when inserting 100 rows in a transaction - the overhead on the JS side can be around 10x more than the time required by SQLite. However, all that's needed to solve this is a batch API: a way to run 100 insert statements in a transaction with only a single callback at the end. The performance problem is really not about synchronous or asynchronous - it's the fact that we need the same overhead for every single insert in the transaction.
I've eventually worked around this issue by just combining multiple insert statements into a single one. For example, instead of doing 3 separate inserts, do one with `VALUES (?,?), (?, ?), (?, ?)`. While the code to achieve this is ugly, it gets similar performance to better-sqlite3, without being synchronous.
[UPDATE] Based on jlongster's comment, it may still be best practice to run this as a separate process, regardless of the module being used, since node-sqlite3 isn't truely asynchronous.
>Easy-to-use synchronous API (faster than an asynchronous API... yes, you read that correctly)
Who the hell decided that making sqlite asynchronous is a good idea in the first place? Node is so religious on async that creating everyday transactioned apps in it is PITA that never ends.
Because libuv (which Node uses for multi-tasking) depends on threads being yielded in order for multiple things to happen without blocking. This is way easier to think about and harder to screw up then traditional thread management with mutexes.
If your api is not asynchronous, then any expensive call is going to lock the entire application which is bad and also why I won't be using this library despite the actual underlying implementation being much faster.
Did you measure the impact, subtracting harder development and run- time? Do you like to code in all hard ways before you meet any problems? Is sqlite a key performance point in your app? (Why not async-pg then?) How many requests per second do you have at average peak hours?
Yes. Unless the underlying reactor/scheduler is multithreaded but that's… uncommon, as async code tends not to consider thread-safety.
It certainly isn't the case for nodejs.
One could wonder whether that's an issue when IO are fairly short & strongly interspersed with code being run though, the overhead of switching tasks could be higher than the IO you're synchronising. I guess whether this is a good idea would strongly depend on how you're using sqlite (aka do you tend to make lots of very simple requests with small output or a smaller number of more complex ones).
> One could wonder whether that's an issue when IO are fairly short & strongly interspersed with code being run though
IMO one of the core observations made by Dahl and the other Node.js folks was that this is very very rarely the case in modern web applications. Most webapps either a) spend the vast majority of their time waiting on IO, b) have non-IO code very regularly interspersed with IO operations (making event-loop based interleaving based on IO boundaries more useful) or c) both.
As a result, Node.js was built with the single-thread/IO-bounded reactor model. I think that this, along with (and perhaps even more than) its choice of language syntax, drove the platform's adoption. It means that code doesn't need to think about thread safety, and that there is only one IO model: asynchronous by default (yes there are exceptions, but they're a minority). That lets users get a lot of scale for free, provided their workloads meet those criteria.
This question implies a complexity of solutions and tradeoffs that I’m not ready to explain right now. In short: in node - yes, but this also simplifies everything by an order of magnitude until you’re too busy in sqlite. Node is not actually complex or well-designed [imo]. Other approaches could smoothen delays to ~zero. Other languages could even get rid of callbacks and promises.
The power of node’s approach is that it works well for everything except for processor intensive code at the cost of some syntactic overhead that has now been mostly resolved by async await. Much less mental burden than threads or coroutines with mutexes etc
I’m very interested in coroutine-based event loops. Can you please share your experience with these? Which framework did you use, programming language, etc. Why didn’t they “click” and what caused burden? Thanks in advance.
Definitely, but when working with a local sqlite instance where your OS is page caching pretty much the whole thing your IO cost isn't necessarily that high. The trivial case of query-by-id almost boils down to a btree lookup in RAM I would think?
Your comment suggests that you have a strong misunderstanding of the event loop concept.
For example, that almost everything in Node is asynchronous, like in Go, is one of its best attributes. Compare that with the state of things in Java, Rust, Python, and most other languages if you decide you want to use the reactor pattern.
If you don't want an event loop, then you don't use Node. But not blocking the event loop is not "religious." It's how they work.
It's probably a fantastic lib, and well done. But..
What if node-sqlite redesign it's library and gets even faster than this? Then it won't be better anymore? I think it's a bit of a bad name for a lib to have since stuff like this changes with time.
You're absolutely correct. I mean, they could argue that "best" is still not taken but objectively it's just bad naming practice to use relative terms. Almost anything else would be better, they could call it "Blue Whale Sqlite3" (not a good name) and it would be more iconic and neutral.
Maybe not a big deal for many, but this is the first sqlite lib for Node.js that supports User Defined Functions, would be nice to have an example though.
I tried switching from sqlite3 to better-sqlite3 but found that fts didn't work so it was a deal breaker for me, has anyone else got full text search to work with better-sqlite3 as that would make me switch?
The module name is a good example of why package systems should have qualified names by default. `npm install JoshuaWise/sqlite3`
Elm got this right. You shouldn't have to be clever in thinking of a package name just because the obvious one was taken and you have a different flavor to offer.
Rust didn't and it's another ecosystem where people feel the need to squat on names or suffix a '2' when they think they've written a better library, just like NPM.
npm does support "scoped packages"[1], since 2015. More projects are starting to use them, e.g. Angular publishes packages like @angular/core, @angular/forms, etc.
It's not that great unless it's required. For example, the bare "sqlite3" package name will always sound official/canonical next to "@JoshuaWise/sqlite3" and people will forever accidentally install it even though they want a scope. Just like how users type in domain.com when they really wanted domain.org.
And then you can't transfer a package to a new organization without stranding users. Java showed how misguided using the domain name in the package name was, organizations have far less permanence than software.
This can be managed in many ways, like identity immutability, package redirection, and solved from a UI standpoint like "a/foo was moved to b/foo" on your user's next package update because the package manager has first-class support for it.
Meh, nobody cares about package names, anyway. I've seen plenty of companies who had com.acquisition1, com.acquisition2, etc., all over the place.
The Java space, Maven, more precisely, did come up with the first namespaces package repository. For some reason other package managers don't do it, for simplicity I guess, and it always come back to bite them in the butt.
For reference, Maven 1 didn't have groupIds, Maven 2 added them. They did it for a reason and that was back in 2005!
I'm having the exact problem you're describing in a macOS app, where an app that was previously name.janedoe.app is now org.company.app. What a mess that caused.
On the other hand, for LaTeX (see CTAN) this works astonishingly well. Those also have a much stronger emphasis on backwards compatibility of existing packages. I suspect this is not just a coincidence.
I like this idea. Maybe you could even have a more fun variant where you add a random goofy adjective ("big" is unsuitable because it could mean something, but "sparkly" hardly has bearing on a software package), or something else that is randomly assigned rather than propagating someone's user name. Sometimes it's a one man package but for a package with 300 contributors it doesn't make sense why someone's name should be slapped onto it forever.
I think a package manager should have first-class support for those sorts of governance changes because these structures change. For example, packages direct to their latest state. Trying to upgrade a package with an org change will explain this during your next update.
Github has some good examples of this when repos change hands.
Then you migrate. If the platform provides tools to do that, you use them. Otherwise you make a new namespace, upload the project to the new one, and edit the old one with a link to the new one and a deprecation warning. Then if you have a trusted announcement platform like a blog or Twitter, announce it there.
If it's feasible to do so, you also push updates to both namespaces for a little while during the transition.
And I think the package manager can do so much more here than current ones from a UX level.
For example, instead of having to push updates to both packages, you push updates to the new one and, because you've annotated the old one with the move, users have a somewhat seamless experience when they run their update: they'll get a little heads up about the move, explain that oldpackage eol'd at 5.4 and newpackage picked up at 6.0, and would you like me auto-update your manifest?
And/or the package manager supports a rename flow that does this for you.
You can do scoped packages like @JoshuaWise/sqlite3 but it's like getting a crappy tld because the .com was taken -- the bare "sqlite3" package seems like the better/canonical/official package, people are going to try to install it anyways, etc.
It doesn't really work until FQN is required by all.
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.