Re:Better-sqlite3: A faster Sqlite library for Node.js
by matharmin on Monday March 19, 2018 @11:33AM
(#16619162)
[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.
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.