Better-sqlite3: A faster Sqlite library for Node.js

https://github.com/JoshuaWise/better-sqlite3

by jeswin • 8 years ago

59 60 8 years ago

always_good11• 8 years ago

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.

hnarn3• 8 years ago

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.

reificator3• 8 years ago

If it's a package with 300 contributors you would use an organization's name, not a person's name.

always_good11• 8 years ago

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.

kryptiskt• 8 years ago

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.

scrollaway• 8 years ago

I think there's middle ground. Github manages somehow (by creating redirects).

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.

oblio• 8 years ago

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!

always_good11• 8 years ago

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.

Did you think nothing can be improved upon Java?

mirkonasato• 8 years ago

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.

[1] https://docs.npmjs.com/misc/scope

sebazzz• 8 years ago

Yes, but they don't enforce it for new packages.

always_good11• 8 years ago

Yeah, this is why I snuck "by default" in there.

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.

fnord123• 8 years ago

You can still do JoshuaWise-sqlite3.

always_good11• 8 years ago

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.

vog• 8 years ago

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.

staticelf• 8 years ago

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.

hnarn3• 8 years ago

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.

genezeta• 8 years ago

They could call it "synchronous-sqlite3" since that seems to be the main relevant difference.

wruza4• 8 years ago

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.

hliyan• 8 years ago

Asking out of ignorance: won't a synchronous API block the entire process while the I/O is going on?

masklinn2• 8 years ago

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).

wruza4• 8 years ago

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.

bpicolo• 8 years ago

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?

vorpalhex• 8 years ago

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.

wruza4• 8 years ago

“Which is bad”.

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?

always_good11• 8 years ago

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.

masklinn2• 8 years ago

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.

gwbas1c• 8 years ago

Uhhh, read the description.

node-sqlite3 uses an asynchronous API for CPU-bound functions.

An asynchronous API is only for IO-bound functions. CPU-bound functions should be synchronous.

Klathmon• 8 years ago

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

always_good11• 8 years ago

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.

sologub• 8 years ago

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.

Anyway great work!

matharmin5• 8 years ago

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.

matharmin5• 8 years ago

[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.

ivanfon• 8 years ago

I'd love to see a benchmark against Sequelize as well.

Not sure how I feel about it not being async...

matharmin5• 8 years ago

Sequelize just exposes additional APIs on top of node-sqlite3.

jlongster4• 8 years ago

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.

zbjornson5• 8 years ago

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/

tkone• 8 years ago

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.

always_good11• 8 years ago

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.

jlongster4• 8 years ago

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".

matharmin5• 8 years ago

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

jlongster4• 8 years ago

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.

zubairq• 8 years ago

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?

Better-sqlite3: A faster Sqlite library for Node.js

Related Stories