Hacker News new | comments | ask | show | jobs | submit login
A SQLite Tutorial with Node.js (stackabuse.com)
70 points by ScottWRobinson 9 months ago | hide | past | web | favorite | 16 comments

I didn't see anything in the code to address database contention for inserts/updates. This is SQLites achilles heel, even in a single process, with multiple writing threads. But it's easily remedied by "serializing" writes with a queue or lock for example. Many claim WAL remedies this, but in my test, WAL still succumbs to contention. Time out and retry is another possible solution.

The other thing is setting the sync and journaling mode on the connection. You have to do that for every connection, if you do want to use WAL mode.

SQLite can be quite high performance if done right.

I don't consider it an Achilles' heel, more like the reason that you want postgres sometimes.

Similarly, I wouldn't say that postgres' Achilles' heel is that you can't just copy the database to a thumb drive.

Agreed that you have to treat SQLite as though you're writing to a single file with all conflict resolution essentially on-disk. Because you are.

You're not likely to run multiple threads in NodeJS. If you do need to scale up to multiple threads or processes, SQLite might not be the right choice, but then again it was never designed for that use case.

You're not likely to run multiple threads in NodeJS.

Nodejs itself won't but libuv will.

Why use bluebird promises instead of native ones? Are them faster or is it just old habits?

Bluebird's promise implementation is faster in the common case (single thenable chain) and also includes some non-standard convenience functions for composing groups of Promises.

The performance differences are meaningless for most people but if you're used to coding with the convenience functions it's annoying to not have them available.

I couldn't find a single use that had bluebird only functionality.

Not the author, but it seems as though Bluebird is indeed faster and more efficient (see [1]). Could also just be habit though.

[1]: https://softwareengineering.stackexchange.com/q/278778/30600...

I'd like to see new benchmarks for that.

If you recall from the V8 6.6 release notes, they talked about performance impromevents in promise and async benchmarks.

The Notes: (see Asynchronous performance improvements) https://v8project.blogspot.com/2018/03/v8-release-66.html

The Graph: https://2.bp.blogspot.com/-T_HWK2MlCOY/WrkGw1-ABJI/AAAAAAAAB...

Looks like about 20%, 30% for some benchmarks.

Note to release note authors: Please put IDs on your headers so people can cite you properly. Thank you.

Node 10.1, doxbee benchmark:

    file                                       time(ms)  memory(MB)
    promises-bluebird-generator.js                  282       39.13
    promises-native-async-await.js                  292       48.66
    promises-bluebird.js                            305       47.38
    promises-then-promise.js                        382       67.37
    promises-ecmascript6-native.js                  390       66.93

    file                                       time(ms)  memory(MB)
    promises-bluebird.js                           547       99.01
    promises-bluebird-generator.js                 574       98.68
    promises-native-async-await.js                1165      254.62
    promises-ecmascript6-native.js                1231      256.41
    promises-then-promise.js                      1569      302.88
Still a big gap for parallel, but not what it used to be.

Looks like native promise is faster at least in the latest chrome. https://jsperf.com/promise-vs-bluebird

And the speed difference on Firefox 60 is barely outside the confidence interval.

One thing I noted in looking at the bluebird benchmark is that native promises on Node 10 are only a hair slower than bluebird on Node 8.9. So there's a sales pitch for upgrading to node 10 so you can stop using bluebird.

it was just an old habit (author)

I came across a use case for sqlite recently for storing some data I was scraping, and went through hell with the sqlite3 package due to the seemingly unnecessary async calls for everything. I've heard better-sqlite3[1] solves a lot of the problems since it is synchronous, and ends up having better performance as a result.

That said, the sqlite3 package soured me on it so much I ended up rewriting my script in clojure instead which came with the benefits of being able to use other jdbc libraries like honeysql.

[1] https://github.com/JoshuaWise/better-sqlite3

While asynchronous programming seems to be simpler than some other concurrency models, it still is way complicated when compared to normal sequential programming; and thus should be opt-in, not inevitable by default. Some time ago I wanted to have a little firefox addon that would list bookmarks tagged unread, and IIRC I had to use promises and async calls, all in some ten lines program that essentially maps a function recursively over a tree (which I didn't have to do explicitly because that was what the promise API did for me).

I think that the DAO should abstract more away. It shouldn't simply allow SQL input - that defeats the purpose.

Applications are open for YC Summer 2019

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact