Consider adding a new plugin hook: "pre_query" or similar

[jamietanna]

As noted on my Fediverse-enabled blog, I was looking to build a Datasette plugin that allows for finding what commonly performed queries occur with the database, to attempt to learn what's regularly looked up:

Does anyone know if there's a good way of getting a historical storage of queries that users put into Datasette? Trying to get some stats around common queries and usage, couldn't see a plugin for it, but not sure if my searching just missed it

It appears that the plugin hooks right now don't make this available. Would this be something you're interested in adding? Or is it maybe something that could be achieved a better way?

I believe I could also look at using https://docs.datasette.io/en/stable/internals.html#request-object to pull out the querystring?

[jamietanna]

And of course, I look again, and see that https://datasette.io/plugins/datasette-query-history may be exactly what I want

It looks like this plugin does it for the current session, whereas I'd be looking for across any user interacting with the database

[simonw]

Yeah, the way to do this right now would be with a asgi_wrapper middleware that logs requests. I played with that a few years ago here: https://simonwillison.net/2019/Dec/16/logging-sqlite-asgi-middleware/

I've been thinking for a while that it might be good to have some kind of middleware at a slightly different level from that - a Datasette equivalent of the Django concept of "view" middleware, which acts after a view function has been selected by URL routing: https://docs.djangoproject.com/en/5.0/topics/http/middleware/#process-view

I don't think I'll do that before 1.0 though, as I need to be very confident that Datasette's own view internals are stable before encouraging people to customize them like that.

[asg017]

Maybe you can use the set_trace_callback() option on the SQLite connection? You can use the startup hook the access the db connection, then that callback will be ran for every SQL query.

Though it seems pretty limited. The SQLite C API has extensive tracing/profiling support, which gives you things like "how long did this statement take", but it doesn't look like the Python library supports it