Connection and execution hooks

[daenney]

I'm wondering if there's a way to have Pre/Post hooks for a number of things. For example, for long-lived applications SQLite recommends running with PRAGMA optimize=0x10002; when the connection opens and running PRAGMA optimize before closing the connection.

The first thing I can achieve by adding it as an _pragma in sqlite3.Open. However, I don't see how I could hook Conn.Close().

Similarly, for instrumenting the library it would be very handy to have Pre/Post hooks for Conn.Exec, the Conn.BeginXXX etc. so I can record a before/after time, start a span for the query etc.

It seems right now I would have to wrap the Conn myself and have my own Close() and Exec() methods that I could hook. But I'm wondering if we could add something like this to this library directly?

[ncruces]

This is something I have thought a bit about.

Being forthcoming, in 8d450f8 I dropped the Init field from the driver (hopefully this doesn't break you, I checked, but I'm never sure). I don't think forcing people to register a whole new driver for simple things is the way to go, and you're already wrapping the driver, which gives you even more leeway. This (small) incompatibility will land in the next version.

I support _pragma's in DSNs, because they're such a common need, and have tried pushing those to mattn. Have been met with resistance, but there was an interesting suggestion I hadn't thought off before.

I also added AutoExtension() to try to avoid much of the need in hooking Open(). But I tend to follow SQLite APIs, which also offers this, and nothing for hooking close (that I've noticed).

Neither _pragma, nor AutoExtension(), nor driver.Open(dsn, init), nor the "mattn solution" fix hooking Close(), which I had seen on GoToSocial, and wanted to offer, somehow. Nor do they allow hooking Exec/Query for telemetry/whatever.

To be perfectly honest, I don't use this much in "super serious business" 😉, and I have no idea what the common solutions are in this place? I haven't seen what other drivers (even outside SQLite, PostgreSQL, etc) offer in this space.

[ncruces]

Maybe I just need to wrap this?
https://sqlite.org/c3ref/trace_v2.html

[ncruces]

See #125. @daenney

[daenney]

So looking at trace, it talks a lot about tracing the connection. I'm not sure how that works for wrapping calls to Exec on the conn itself. I'm having a hard time grokking what the trace docs are saying.

[ncruces]

So, if you look at the things you can trace, it traces closing connections (I need to figure out if it's called before closing, which would allow running PRAGMA optimize), and it traces statement execution.

On statement execution, it's a way to gather performance statistics, because it catches three moments: start, each row, and end (with the nanoseconds it took the statement to run). With a bit of statefulness, you can collect how many rows, and how long it took, for each query to run.

What did you intend to collect?

[daenney]

So for statements what I usually want is collect some metrics (like time it took to run) and start/stop an OpenTelemetry span so I get them in my tracing systems. I run one of those locally because when debugging things I like having the diagrams to see what's taking time where over the lifecycle of a request.

Coming back to GtS as an example, the way that happens in Bun is using the bunotel package which installs a query hook. This means their main package/driver doesn't have to deal with it or know about it, which is kinda handy. You don't carry the cost of the opentelemetry code base etc. unless you actually use it.

So using their BeforeQuery hook they set the OpenTelemetry tracer on the context, https://github.com/uptrace/bun/blob/1573ae7c2fffad1a7f72fd2d205e924b2fd4043b/extra/bunotel/otel.go#L62, and then in AfterQuery they take care of creating the spans and using the QueryEvent get to the statistics like when the query started (to compute elapsed time) etc. That also allows outputting OpenTelemetry metrics because the query hook stores a histogram etc.

Bun's QueryEvent sounds sort of like the type of data we could get out of trace for a statement.

[daenney]

None of this is necessarily something that needs to be added here. Like. It's perfectly possible for me to write a small wrapper package around the current API that does all of this so I can keep eschewing the use of database/sql.

But a "query hook" mechanism of some sort seems generically useful enough, especially for instrumentation uses, that I was thinking it might be nice to have even on the low-level wrapper.

[ncruces]

I also found these:
https://github.com/luna-duclos/instrumentedsql
https://github.com/ngrok/sqlmw

[daenney]

Yeah, so that's kinda along the lines I was thinking too. But I wasn't thinking at it from database/sql, since I'm not using that.

[daenney]

I should've prefaced, this wasn't for GoTosocial, sorry about that. I'm also using this in some personal projects.

For GtS, our ORM (Bun) has pre/post hooks for just about any action so we can do telemetry and other stuff that way. And since we wrap it anyway then we end up being able to hook Close() all the same.

For my own projects, I'm using this library directly, no ORMs involved and no databasl/sql driver since I don't have any need for it. In that case there's no way to hook any of the actions right now. Best I can do is have something like a type DB struct { conn *sqlite3.Conn } and declare my own Open, Close, Exec, BeginTx etc. on there which I can hook in any way I want before I do the underlying *sqlite3.Conn.Func() call. I've mostly been wondering about a way to avoid that.

[ncruces]

For the "low level wrapper" I've really tried to keep it close to SQLite (with a little liberty around coercing things to idiomatic Go). SQLite has a relatively well thought out API, so this is a way for me to reuse their many years of experience with it.

This does raise some interesting questions. sqlite3_trace_v2 would be a second "constructor" for *sqlite3.Stmt, which raises the question of pointer identity. If I want two *sqlite3.Stmt to be equal, I need to dedup them, which means storing a list of them in *sqlite3.Conn (which incidentally SQLite also does). You'd also probably need identity, to make it easier to trace the start/row/end moments of a *sqlite3.Stmt run.

[daenney]

On the dedup/identity thing, there is https://tip.golang.org/doc/go1.23#new-unique-package coming up in Go 1.23. Not sure if that would help here.

[ncruces]

Not really. You need to carry the unique.Handle[sqlite3.Stmt] everywhere. It's great for internal stuff, not much else, IMO.

I don't even know if I could do type Stmt unique.Handle[stmt] and use that as a named type (I think so). It would still be a pretty hard API breakage, that would only work on 1.23 (go4.org/intern.Value is not generic, so can't be used that way).

I guess it'll be a while until the community figures out how/when to use unique.