Database.on not registering handles for callbacks

[johnnadratowski]

Issue Summary

I'm attempting to get my SQLite 3 node to log the queries it runs when in verbose mode. I registered the handles in the following way:

export const db = await open({
  filename: dbFile,
  driver: sqlite3.Database,
})

db.on('trace', (q) => console.log(`QUERY:\n\n${q}`))
db.on('profile', (_, t) => console.log(`TOOK ${t}ms`))

However, I'm not seeing the logs. When looking at the code that registers the handle, I don't see it actually passing my callback through. Though I'm not so familiar with that C API so maybe I'm misunderstanding the code. Regardless I'm not getting these logs even when expected.

Steps to Reproduce

1. Register the trace/profile events as shown above
2. Make SQL query

Expected: See log of query/profile
Result: No logs outputted

Version

5.1.6

Node.js Version

v20.8.1

How did you install the library?

yarn add on mac OSX

[dkamins]

This library currently uses a deprecated trace and profile approach: "These routines are deprecated. Use the sqlite3_trace_v2() interface instead of the routines described here."
-- https://www.sqlite.org/c3ref/profile.html

The new v2 version: "Each call to either sqlite3_trace(D,X,P) or sqlite3_trace_v2(D,M,X,P) overrides (cancels) all prior calls to sqlite3_trace(D,X,P) or sqlite3_trace_v2(D,M,X,P) for the database connection D. Each database connection may have at most one trace callback."
-- https://www.sqlite.org/c3ref/trace_v2.html

The v2 conceptual model differs slightly in that now a single callback is registered with a trace bitmask comprised of:

#define SQLITE_TRACE_STMT       0x01
#define SQLITE_TRACE_PROFILE    0x02
#define SQLITE_TRACE_ROW        0x04
#define SQLITE_TRACE_CLOSE      0x08

Presumably the underlying library is now pushing the currently used deprecated (apparently since 2016) versions through the v2 path, which means only the last one applies. This matches what people are seeing: only the last registered trace or profile currently triggers now, and all previously registered ones are discarded.

For reference, cpython made the change to v2 for themselves in 2020 here: python/cpython#84498

It's probably a good idea and time for this library to adapt to the v2 approach! I suspect this reported bug and many other confusions and frustrations exist as a result of continuing to use the deprecated trace/profile approach.

(Originally posted a version of this in the old closed thread #1474)