Is there a performance penalty for purely single-thread use cases?

If so, would it make sense to allow setting the threading mode at runtime?

I don't know for sure about any performance penalti. But as discusses in the issue, due to the node.js nature, this will work fine for sync. Better-sqlite uses it.

Understood. Thanks! This can be closed.

If anything I think this will speed up single-threaded use cases, because apparently by default SQLite uses internal serialization. Did not test it though.

(I was going to post this on the main thread, but this is the context that matters, so I'll add it here) (edit: I rewrote for brevity):

Synchronous node:sqlite operations can run while your new async operations are executing. This will cause concurrent access to the same SQLite connection from multiple threads. This violates SQLITE_THREADSAFE=2 requirements and can cause db corruption undefined behavior (I could have sworn I read about this on the how-to-corrupt page but I don't see it now.)

SQLITE_THREADSAFE=2 requires no connection be used by multiple threads simultaneously (docs). Currently, nothing prevents sync methods (main thread) from running while async operations are on the thread pool.

Suggested fix: throw from sync methods when has_running_task_ is true:

diff --git a/src/node_sqlite.cc b/src/node_sqlite.cc
--- a/src/node_sqlite.cc
+++ b/src/node_sqlite.cc
@@ -1257,6 +1257,8 @@ void Database::Prepare(const FunctionCallbackInfo<Value>& args) {
   ASSIGN_OR_RETURN_UNWRAP(&db, args.This());
   Environment* env = Environment::GetCurrent(args);
   THROW_AND_RETURN_ON_BAD_STATE(env, !db->IsOpen(), "database is not open");
+  THROW_AND_RETURN_ON_BAD_STATE(env, db->has_running_task_,
+    "Cannot call sync methods while async operation is running");

Same check needed in Exec() (sync path), Function(), Aggregate(), SetAuthorizer(), and LoadExtension(). Tested locally — works as expected.

(SQLITE_THREADSAFE=1 would also prevent undefined behavior, but the main thread would silently block on SQLite's internal mutex until the async op completes, defeating the purpose of async.)

Thank you for the in-depth examples. I'd like to mention another option (which I thought has been discussed in the issue, but maybe we were talking past each other):
Have one sqlite connection for synchronous access and maintain a pool of (seperate) sqlite3 db connections for asynchronous access. For each asynchronous access one connection from the pool would be exclusively used. The main difficulty with this approach is obviously db.prepare(). One would have to track which db connection owns the prepared statement instance and only dispatch operations from that prepared statement object to its owning db connection. Another drawback of that design is the inherent potential of SQLITE_BUSY failures for concurrent write access.

Yeah, if you don’t want to cross the streams, you need multiple database instances opened. Rather than transparently managing this magically begins the scenes in a pool, though, I think it may be less surprising to actually push the complexity of managing per -database handles to the user—they can figure out what makes sense in their app, and when one query wedges another, it’s their fault.

As mentioned before, setting the thread safety build flag to 1/serializable is pretty much guaranteed to be at least as efficient as building our own scheduling mechanism, so I'd remove this code and instead do that