Feature Request: Middleware

[nhumrich]

As discussed in #128, it might be a cool idea to have a "middleware" layer in asyncpg where people can register their own code to be ran pre or post query.

One usecase is asyncpgsa, which is a project to allow sqlalchemy objects to be passed to asyncpg connection methods. If there was a middleware layer, asyncpgsa could simply be a middleware that would run right before asyncpg prepared the query to convert the sqlalchemy object into a string. This would make asyncpgsa stupid simple, and just be a conversion layer, allowing it to get out of the way, and let asyncpg be the source of truth for everything else.

Another use case is metrics. My org is starting to track metrics per endpoint and per database query. If asyncpg had middleware support, we could simply inject a middleware that tracked the query and the time it took, and send that information into our metric platform. This could be done today by wrapping asyncpg and adding a method around it that tracks the metrics. It would be amazing if instead asyncpg could be used directly, and the middleware handles that portion for me.

Middlewares could potentially be "added" when the pool is created. (they can only exist on a pool maybe?)

Anyways, I am happy to try to implement this if you would like, but first wanted to get a general discussion going on the topic.

[vitaly-burovoy]

Yes, existence of a "middleware" layer would also allow to support feature discussed in #9.

[nhumrich]

I would like to re-ignite the conversation of this feature. With python 3.14 and the addition of PEP 750, it will soon be possible to use t-strings to define queries. If asyncpg supported middleware, you could add a middleware to enforce t-strings and safe queries. Without middleware, one would have to wrap the library or monkeypatch it if they wanted to enforce safe SQL.

With middlewares, people could plugin whatever tstring-to-sql library they wanted, and asyncpg doesn't need to handle t-strings directly.

Example:

name = "bobby"
values = await conn.fetch(t"SELECT * FROM mytable WHERE name = {name}")

[dnk8n]

I implemented a form of pre-query on the function I use to run queries. Admittedly we have started to favour psycopg3. But I think this still applies.

Although an out of the box solution for middlewars would be nice, it would have to be able to take in many parameters.

For example, should the pre-query affect the session or transaction, should something post-query have a different isolation. Or should the queries themselves dictate that?

E.g. many queries themelves can be set to affect a different isolation, in this case is_local as in

set_config ( setting_name text, new_value text, is_local boolean ) → text

Is there something this enhancement solves other than convenience and standardized way of solving this kind of (prequery/postquery) problem? To me, we already have all the building blocks to do what we need.

For context, the prequery solution is as simple as running on a different cursor to the main query so as not to bleed into the results of the main query. My use case was setting a config at transaction level, that would be picked up by a trigger function.

Maybe you could define a more important example that the lack of this enhancement blocks.