4 ms·

better-sqlite3 is (was?) my favorite sqlite3 lib, it is incredibly fast. NodeJS has SQLITE3 support these days! https://nodejs.org/api/sqlite.html https://nod

better-sqlite3 is (was?) my favorite sqlite3 lib, it is incredibly fast. NodeJS has SQLITE3 support these days! https://nodejs.org/api/sqlite.html https://nodejs.org/api/sqlite.html Interestingly it is NOT async, like better-sqlite3. I wonder why. I've been looking for any public remarks about it, but found nothing.

postepowanieadm 2y ago
That's nice! I use bun, but it's lack of support for UDF makes me consider switching back to node.
- tehbeard 2y ago
  I'm starting to second guess my choice of deno. The options for sqlite in that are either via unstable APIs, or limited due to the particulars around WASM.
  - throwitaway1123 2y ago
    They're going to add it once it stabilizes in Node: https://github.com/denoland/deno/issues/24828#issuecomment-2409989753 https://github.com/denoland/deno/issues/24828#issuecomment-2...
darthShadow 2y ago
These may be useful reading: https://github.com/TryGhost/node-sqlite3/issues/408#issue-57842933 https://github.com/TryGhost/node-sqlite3/issues/408#issue-57... https://github.com/WiseLibs/better-sqlite3/issues/32#issuecomment-302164699 https://github.com/WiseLibs/better-sqlite3/issues/32#issueco... Copying a quote from the second: > The sqlite3 C API serializes all operations (even reads) within a single process. You can parallelize reads to the database but only by having multiple processes, in which case one process being blocked doesn't affect the other processes anyways. In other words, because sqlite3 serializes everything, doing things asynchronously won't speed up database access within a process. It would only free up time for your app to do other things (like HTTP requests to other servers). Unfortunately, the overhead imposed on sqlite3 to serialize asynchronous operations is quite high, making it disadvantageous 95% of the time.
- ncruces 2y ago
  The sqlite3 C API very much does not serialize "all operations within a single process." The way threading and concurrency work in SQLite may not mesh well with NodeJS's concurrency model. I dunno, I'm not an NodeJS/libuv expert. But at the C API level that statement is just wrong. Normally you cannot share a single connection across threads. If you compile SQLite to allow this, yes, it'll serialize operations using locks. The solution is to create additional database connections, not (necessarily) launch another process. With multiple database connections, you can have concurrency, with or without threads. https://sqlite.org/threadsafe.html https://sqlite.org/threadsafe.html Again, whether this is viable in NodeJS, I have no idea. But it's a Node issue, not a C API issue. BTW, we're commenting on a Ruby article, and SQLite in Ruby has seen "recent" advances that increase concurrency through implementing SQLite's BUSY handler in Ruby, which allows the GVL lock to be released, and other Ruby and SQLite code to run while waiting on a BUSY connection. https://fractaledmind.github.io/2023/12/11/sqlite-on-rails-improving-concurrency/ https://fractaledmind.github.io/2023/12/11/sqlite-on-rails-i...
  - julik 2y ago
    The user-supplied busy handler has been available for a long while, it's just that the Rails connection adapters did not quite use it right. Indeed, there is elevated interest in SQLite these days.
- tobyhinloopen 2y ago
  Tnx for the links!
- SCLeo 2y ago
  This explanation is extremely misleading. For many of my projects, vast majority of the operations do not use the database . And the few that does, contains long running huge joins/aggregates. Using the sync API is just straight up terrible because the task will block literally everything else that does not use the db in js, meaning generating a report in the background can literally prevent you from handling any requests. (I did end up using better sqlite 3 because they are personal projects and getting stuck for 2 seconds when the scheduled report generation happens is ok ish for the a few people using it. But I will not consider using better sqlite 3 for any future projects)

postepowanieadm 2y ago

That's nice! I use bun, but it's lack of support for UDF makes me consider switching back to node.

tehbeard 2y ago
I'm starting to second guess my choice of deno. The options for sqlite in that are either via unstable APIs, or limited due to the particulars around WASM.
- throwitaway1123 2y ago
  They're going to add it once it stabilizes in Node: https://github.com/denoland/deno/issues/24828#issuecomment-2409989753 https://github.com/denoland/deno/issues/24828#issuecomment-2...

darthShadow 2y ago

These may be useful reading: https://github.com/TryGhost/node-sqlite3/issues/408#issue-57842933 https://github.com/TryGhost/node-sqlite3/issues/408#issue-57... https://github.com/WiseLibs/better-sqlite3/issues/32#issuecomment-302164699 https://github.com/WiseLibs/better-sqlite3/issues/32#issueco... Copying a quote from the second: > The sqlite3 C API serializes all operations (even reads) within a single process. You can parallelize reads to the database but only by having multiple processes, in which case one process being blocked doesn't affect the other processes anyways. In other words, because sqlite3 serializes everything, doing things asynchronously won't speed up database access within a process. It would only free up time for your app to do other things (like HTTP requests to other servers). Unfortunately, the overhead imposed on sqlite3 to serialize asynchronous operations is quite high, making it disadvantageous 95% of the time.

ncruces 2y ago
The sqlite3 C API very much does not serialize "all operations within a single process." The way threading and concurrency work in SQLite may not mesh well with NodeJS's concurrency model. I dunno, I'm not an NodeJS/libuv expert. But at the C API level that statement is just wrong. Normally you cannot share a single connection across threads. If you compile SQLite to allow this, yes, it'll serialize operations using locks. The solution is to create additional database connections, not (necessarily) launch another process. With multiple database connections, you can have concurrency, with or without threads. https://sqlite.org/threadsafe.html https://sqlite.org/threadsafe.html Again, whether this is viable in NodeJS, I have no idea. But it's a Node issue, not a C API issue. BTW, we're commenting on a Ruby article, and SQLite in Ruby has seen "recent" advances that increase concurrency through implementing SQLite's BUSY handler in Ruby, which allows the GVL lock to be released, and other Ruby and SQLite code to run while waiting on a BUSY connection. https://fractaledmind.github.io/2023/12/11/sqlite-on-rails-improving-concurrency/ https://fractaledmind.github.io/2023/12/11/sqlite-on-rails-i...
- julik 2y ago
  The user-supplied busy handler has been available for a long while, it's just that the Rails connection adapters did not quite use it right. Indeed, there is elevated interest in SQLite these days.
tobyhinloopen 2y ago
Tnx for the links!
SCLeo 2y ago
This explanation is extremely misleading. For many of my projects, vast majority of the operations do not use the database . And the few that does, contains long running huge joins/aggregates. Using the sync API is just straight up terrible because the task will block literally everything else that does not use the db in js, meaning generating a report in the background can literally prevent you from handling any requests. (I did end up using better sqlite 3 because they are personal projects and getting stuck for 2 seconds when the scheduled report generation happens is ok ish for the a few people using it. But I will not consider using better sqlite 3 for any future projects)