9 ms·

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

59 points by jeswin 9y ago

always_good 9y 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.
- vog 9y 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.
- hnarn 9y 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.
  - reificator 9y ago
    If it's a package with 300 contributors you would use an organization's name, not a person's name.
    - hnarn 9y ago
      Ok, so what happens when the same package evolves from one person to a team of 300 people?
      - reificator 9y ago
        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.
        always_good 9y ago
        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.
        reificator 9y ago
        Yeah, like I said, if the platform provides that kind of tooling, take advantage of it.
  - always_good 9y 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 9y 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 9y 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 9y 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_good 9y 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 9y 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 https://docs.npmjs.com/misc/scope
  - sebazzz 9y ago
    Yes, but they don't enforce it for new packages.
  - always_good 9y 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 9y ago
  You can still do JoshuaWise-sqlite3.
  - always_good 9y 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.
staticelf 9y 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.
- hnarn 9y 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 9y ago
    They could call it "synchronous-sqlite3" since that seems to be the main relevant difference.
wruza 9y 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 9y ago
  Asking out of ignorance: won't a synchronous API block the entire process while the I/O is going on?
  - masklinn 9y 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).
    - zbentley 9y ago
      > 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.
masklinn 9y 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 9y 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.
  - deleted 9y ago
    [deleted]
  - Klathmon 9y 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_good 9y 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 9y 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!
matharmin 9y 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.
- matharmin 9y 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 9y ago
I'd love to see a benchmark against Sequelize as well. Not sure how I feel about it not being async...
- matharmin 9y ago
  Sequelize just exposes additional APIs on top of node-sqlite3.
jlongster 9y 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-electrons-main-process-351bf11a763c https://medium.com/actualbudget/the-horror-of-blocking-elect... I'm glad somebody finally made a robust synchronous API to sqlite.
- zbjornson 9y 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/ https://nodejs.org/en/docs/guides/dont-block-the-event-loop/
  - deleted 9y ago
    [deleted]
  - tkone 9y 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.
zubairq 9y 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?

59 points by jeswin 9y 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.

vog 9y 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.
hnarn 9y 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.
- reificator 9y ago
  If it's a package with 300 contributors you would use an organization's name, not a person's name.
  - hnarn 9y ago
    Ok, so what happens when the same package evolves from one person to a team of 300 people?
    - reificator 9y ago
      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.
      - always_good 9y ago
        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.
        reificator 9y ago
        Yeah, like I said, if the platform provides that kind of tooling, take advantage of it.
- always_good 9y 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 9y 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 9y 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 9y 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_good 9y 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 9y 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 https://docs.npmjs.com/misc/scope
- sebazzz 9y ago
  Yes, but they don't enforce it for new packages.
- always_good 9y 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 9y ago
You can still do JoshuaWise-sqlite3.
- always_good 9y 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.

staticelf 9y 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.

hnarn 9y 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 9y ago
  They could call it "synchronous-sqlite3" since that seems to be the main relevant difference.

wruza 9y 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 9y ago
Asking out of ignorance: won't a synchronous API block the entire process while the I/O is going on?
- masklinn 9y 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).
  - zbentley 9y ago
    > 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.

masklinn 9y 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 9y 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.
- deleted 9y ago
  [deleted]
- Klathmon 9y 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_good 9y 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 9y 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!

matharmin 9y 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.

matharmin 9y 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 9y ago

I'd love to see a benchmark against Sequelize as well. Not sure how I feel about it not being async...

matharmin 9y ago
Sequelize just exposes additional APIs on top of node-sqlite3.

jlongster 9y 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-electrons-main-process-351bf11a763c https://medium.com/actualbudget/the-horror-of-blocking-elect... I'm glad somebody finally made a robust synchronous API to sqlite.

zbjornson 9y 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/ https://nodejs.org/en/docs/guides/dont-block-the-event-loop/
- deleted 9y ago
  [deleted]
- tkone 9y 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.

zubairq 9y 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?